Cookies helpen ons onze services te leveren. Door gebruik te maken van onze diensten, gaat u akkoord met ons gebruik van cookies.
Tip: andere talen zijn Google-Vertaald. Je kunt het English versie van deze link.
Log in
x
or
x
x
Registreren
x

or

Hoe cellen tellen en optellen op basis van achtergrondkleur in Excel?

Stel dat je een cellenbereik hebt met verschillende achtergrondkleuren, zoals rood, groen, blauw enzovoort, maar nu moet je tellen hoeveel cellen in dat bereik een bepaalde achtergrondkleur hebben en de gekleurde cellen optellen met dezelfde bepaalde kleur . In Excel bestaat er geen directe formule om som en aantal kleurcellen te berekenen, hier zal ik u enkele manieren introduceren om dit probleem op te lossen.

Tellen en Som cellen op basis van specifieke opvulkleur door Filter en SUBTOTAL

Tellen en Som cellen op basis van specifieke vulkleur door GET.CELL-functie

Tel en som cellen op basis van specifieke opvulkleur met door de gebruiker gedefinieerde functie

Tellen en aantal cellen gebaseerd op specifieke opvulkleur (of voorwaardelijke opmaakkleur) met Kutools voor Excel

Eén klik om cellen in Excel te tellen, op te tellen en te gemiddelde te maken

Met het uitstekende Count by Color kenmerk van Kutools for Excel, u kunt snel cellen tellen, sommeren en gemiddelde berekenen met de opgegeven vulkleur of letterkleur met slechts één klik in Excel. Bovendien zal deze functie ook de max en min waarden van cellen te weten komen door de vulkleur of de letterkleur. Volledige functie Gratis proef 60-dag!
advertentietelling op kleur 2


Tellen en Som gekleurde cellen per filter en SUBTOTAAL

Stel dat we een tabel met fruitverkoop hebben zoals hieronder afgebeeld, en we tellen of tellen de gekleurde cellen in de kolom Bedrag op. In deze situatie kunnen we de kolom Bedrag op kleur filteren en vervolgens de gefilterde cellen met de functie SUBTOTAAL eenvoudig tellen of optellen in Excel.

1. Selecteer lege cellen om de SUBTOTAAL-functie in te voeren.

  1. Als u alle cellen met dezelfde achtergrondkleur wilt tellen, voert u de formule in = SUBTOTAAL (102, E2: E20);
  2. Als u alle cellen met dezelfde achtergrondkleur wilt optellen, voert u de formule in = SUBTOTAAL (109, E2: E20);


Notes: In beide formules is E2: E20 de kolom Bedrag met de gekleurde cellen en kunt u deze naar behoefte wijzigen.

2. Selecteer de kop van de tabel en klik op Data > filters. Zie screenshot:

3. Klik op het filterpictogram in de koptekstcel van de kolom Bedrag en klik op Filter op kleur en de opgegeven kleur meet je achtereenvolgens. Zie screenshot:

Na het filteren tellen beide SUBTOTAL-formules automatisch alle gefilterde kleurcellen in de kolom Bedrag. Zie screenshot:

Notes: Voor deze methode moeten de gekleurde cellen die u wilt tellen of de som zich in dezelfde kolom bevinden.


Tellen of Som gekleurde cellen door GET.CELL-functie

In deze methode maken we een benoemd bereik met de functie GET.CELL, krijgen we de kleurcode van cellen en tellen of tellen we eenvoudig op basis van de kleurcode in Excel. Ga als volgt te werk:

1. Klikken Formules > Definieer Naam. Zie screenshot:

2. Voer in het dialoogvenster Nieuwe naam de onderstaande schermafbeelding uit:
(1) Typ een naam in het vak Naam;
(2) Voer de formule in = CEL.LEZEN (38, Sheet4! $ E2) in het vak Verwijst naar (nota: in de formule, 38 betekent dat de celcode wordt geretourneerd, en Sheet4! $ E2 is de eerste cel in de kolom Aantal behalve de kolomkop die u moet wijzigen op basis van uw tabelgegevens.)
(3) Klik op de OK knop.

3. Voeg nu een nieuwe kleurkolom toe aan de oorspronkelijke tabel. Typ vervolgens de formule = numcolor en sleep de knop Automatisch aanvullen om de formule toe te passen op andere cellen in de kolom Kleur. Zie screenshot:
Notes: In de formule, numcolor is het benoemde bereik dat we hebben opgegeven in de eerste 2-stappen. U moet dit wijzigen in de opgegeven naam die u hebt ingesteld.

Nu keert de kleurcode van elke cel in de kolom Bedrag terug in de kolom Kleur. Zie screenshot:

4. Kopieer en geef de opvulkleur in een leeg bereik op in het actieve werkblad en typ formules ernaast zoals hieronder getoond screenshot:
A. Voer de formule in om cellen per kleur te tellen = AANTAL.ALS ($ F $ 2: $ F $ 20, numcolor);
B. Voer de formule in als u cellen per kleur wilt optellen = SOM.ALS ($ F $ 2: $ F $ 20, numcolor, $ E $ 2: $ E $ 20).

Notes: In beide formules, $ F $ 2: $ F $ 20 is de kolom Kleur, numcolor is het gespecificeerde benoemde bereik, $ E $ 2: $ E $ 20 is de kolom Bedrag, en u kunt deze naar behoefte wijzigen.

Nu ziet u dat de cellen in de kolom Bedrag worden geteld en optellen aan de hand van hun opvulkleuren.


Tel en som cellen op basis van specifieke opvulkleur met door de gebruiker gedefinieerde functie


Stel dat de gekleurde cellen zich verspreiden in een bereik zoals onderstaand screenshot, kunnen beide bovenstaande methoden de gekleurde cellen niet tellen of optellen. Hier introduceert deze methode een VBA om het probleem op te lossen.

1. Houd de toets ingedrukt ALT + F11 toetsen, en het opent de Microsoft Visual Basic voor toepassingen venster.

2. Klikken bijvoegsel > moduleen plak de volgende code in het modulevenster.

VBA: aantal- en somcellen op basis van achtergrondkleur:

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function

3. Sla vervolgens de code op en pas de volgende formule toe:
A. Tel de gekleurde cellen: = Colorfunction (A, B: C, FALSE)
B. Som de gekleurde cellen op: = Colorfunction (A, B: C, TRUE)

Opmerking: in bovenstaande formules, A is de cel met de specifieke achtergrondkleur die u het aantal en de som wilt berekenen, en B: C is het celbereik waar u het aantal en de som wilt berekenen.

4. Neem de volgende schermafbeelding, voer bijvoorbeeld de formule in= Colorfunction (A1, A1: D11, FALSE) om de gele cellen te tellen. En gebruik de formule = Colorfunction (A1, A1: D11, TRUE) om de gele cellen bij elkaar te tellen. Zie screenshot:

5. Als u andere gekleurde cellen wilt tellen en optellen, herhaalt u de stap 4. Dan krijg je de volgende resultaten:


Tellen en aantal cellen op basis van specifieke opvulkleur met Kutools voor Excel

Met de bovenstaande door de gebruiker gedefinieerde functie moet u de formule één voor één invoeren. Als er veel verschillende kleuren zijn, is deze methode vervelend en tijdrovend. Maar als je dat hebt gedaan Kutools for Excel's Count by Color hulpprogramma, kunt u snel een rapport van de gekleurde cellen genereren. Je kunt niet alleen de gekleurde cellen tellen en optellen, maar ook de gemiddelde, max en min waarden van het gekleurde bereik.

1. Selecteer het bereik dat u wilt gebruiken en klik op Kutools Plus > Count by Colorzie screenshot:

2. En in de Count by Color dialoogvenster, gelieve te doen zoals onder screenshot getoond:
(1) Selecteer Standaard opmaak van de Kleur methode keuzelijst;
(2) Selecteer Achtergrond van de Tel type keuzelijst.
(3) Klik op de knop Rapport genereren.

Kutools for Excel - Bevat meer dan handige Excel-gereedschappen van 300. Volledige functie gratis proef 60-dag, geen creditcard vereist! Nu aanmelden!

Notes: Selecteer om cellen te tellen en op te tellen volgens specifieke voorwaardelijke opmaakkleur Conditionele opmaak van de Kleur methode vervolgkeuzelijst in het bovenstaande dialoogvenster, of selecteer Standaard en voorwaardelijke opmaak uit de vervolgkeuzelijst om alle cellen te tellen die zijn gevuld met de opgegeven kleur.

Nu krijgt u een nieuwe werkmap met de statistieken. Zie screenshot:


Gerelateerd artikel:

Hoe cellen tellen / optellen op basis van de lettertypekleuren in Excel?


Demo: aantal- en somcellen op basis van achtergrond, voorwaardelijke opmaakkleur:

In deze video, de Kutools tab en de Kutools Plus tabblad is toegevoegd door Kutools for Excel. Klik indien nodig op hier om een ​​gratis 60-dag uit te proberen zonder beperking!



Aanbevolen productiviteitstools

Office Tab

gouden ster1 Breng handige tabbladen naar Excel en andere Office-software, net zoals Chrome, Firefox en nieuwe Internet Explorer.

Kutools for Excel

gouden ster1 Verbazingwekkend! Verhoog uw productiviteit in 5 minuten. Heb geen speciale vaardigheden nodig, bespaar elke dag twee uur!

gouden ster1 300 Nieuwe functies voor Excel, maak Excel veel gemakkelijk en krachtig:

  • Cel / rijen / kolommen samenvoegen zonder gegevens te verliezen.
  • Combineer en consolideer meerdere bladen en werkmappen.
  • Bereiken vergelijken, meerdere bereiken kopiëren, tekst converteren naar datum, eenheid en valuta omrekenen.
  • Count by Colors, paging-subtotalen, geavanceerde sortering en superfilter,
  • Meer Selecteren / Invoegen / Wissen / Tekst / Formaat / Link / Commentaar / Werkmappen / Werkbladen Hulpmiddelen ...

Schermopname van Kutools voor Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Paul · 1 years ago
    I had the #NAME Error but I realised that it was because I was spelling COLOR as Colour (UK English Spelling). It worked after I change the spelling.
  • To post as a guest, your comment is unpublished.
    Oana · 1 years ago
    I tried to use this formula in a pivot table (and in a normal excel table) where I've applied conditional formatting to color the cells and it's not working. Do you know why and how can I fix this?
    Thanks!
  • To post as a guest, your comment is unpublished.
    Will · 1 years ago
    I copied and pasted this code into the module screen but when I went back to the spreadsheet, the ColorFunction only returned the #NAME? error. What am I doing wrong? I have Microsoft Excel for Mac version 15.41.
    • To post as a guest, your comment is unpublished.
      Arjun · 1 years ago
      I used the Code and it was working fine till now. Suddenly it has stopped working. This is a very useful tool for me. Kindly help.
    • To post as a guest, your comment is unpublished.
      amohuanna · 1 years ago
      Did you check the range value and cell that contains the desired color?
    • To post as a guest, your comment is unpublished.
      kasim · 1 years ago
      Please this VBA with the name of MY FUNCTION save as add in format(type). Then you can solve that problem.
      • To post as a guest, your comment is unpublished.
        Andrew Carpenter · 1 years ago
        I'm having this same problem, and unfortunately this answer makes absolutely zero sense in the English language. If anyone is able to suggest a translation, I'd be very interested in hearing it. Thanks.
        • To post as a guest, your comment is unpublished.
          Erin · 1 years ago
          The module & function can't have the same name. Change your module name to Color_Function instead of ColorFunction.
  • To post as a guest, your comment is unpublished.
    Jelena · 1 years ago
    Hi


    after applying range we need it jums back to VBA and says Ambiguous name detected: ColourFunction

    can you please help?
  • To post as a guest, your comment is unpublished.
    Kcc C · 1 years ago
    this doesn't work if you change the colors in the cells: so if you change yellow to red or yellow to blank, it doesn't change the count. if you add/create new colored cells then yes it updates the count, but if you change the color of a cell that has already been counted, then the count will not update.
    • To post as a guest, your comment is unpublished.
      Penny · 1 years ago
      if you go back in the cell text with the formula for summing or counting and hit enter, it will recalculate. Just have to remember to do that when changing colors on the range cells.
      • To post as a guest, your comment is unpublished.
        Tim · 1 years ago
        Is there a way to auto-calculate? going into each cell and hitting enter each time colors change is extremely tedious.