GEKLEURDE cellen tellen in Excel (stapsgewijze handleiding + VIDEO)

Bekijk video - Gekleurde cellen tellen in Excel

Zou het niet geweldig zijn als er een functie was die gekleurde cellen in Excel kon tellen?

Helaas is er geen ingebouwde functie om dit te doen.

MAAR…

Het kan gemakkelijk worden gedaan.

Gekleurde cellen tellen in Excel

In deze tutorial laat ik je drie manieren zien om gekleurde cellen in Excel te tellen (met en zonder VBA):

1. De functie Filter en SUBTOTAAL gebruiken
2. GET.CELL-functie gebruiken
3. Een aangepaste functie gebruiken die is gemaakt met VBA

#1 Tel gekleurde cellen met behulp van filter en SUBTOTAAL

Om gekleurde cellen in Excel te tellen, moet u de volgende twee stappen gebruiken:

• Filter gekleurde cellen
• Gebruik de functie SUBTOTAAL om gekleurde cellen te tellen die zichtbaar zijn (na filtering).

Stel dat u een dataset heeft zoals hieronder weergegeven:

Er zijn twee achtergrondkleuren gebruikt in deze dataset (groen en oranje).

Dit zijn de stappen die gekleurde cellen tellen in Excel:

1. Gebruik in elke cel onder de gegevensset de volgende formule: =SUBTOTAL(102,E1:E20)
   
2. Selecteer de koppen.
3. Ga naar Gegevens -> Sorteren en filteren -> Filter. Hiermee wordt een filter toegepast op alle koppen.
4. Klik op een van de vervolgkeuzemenu's voor filters.
5. Ga naar ‘Filteren op kleur’ en selecteer de kleur. Aangezien er in de bovenstaande dataset twee kleuren worden gebruikt om de cellen te markeren, toont het filter twee kleuren om deze cellen te filteren.

Zodra u de cellen filtert, zult u merken dat de waarde in de functie SUBTOTAAL verandert en alleen het aantal cellen retourneert dat zichtbaar is na het filteren.

Hoe werkt dit?

De functie SUBTOTAAL gebruikt 102 als het eerste argument, dat wordt gebruikt om zichtbare cellen te tellen (verborgen rijen worden niet meegeteld) in het opgegeven bereik.

Als de gegevens niet worden gefilterd, retourneert het 19, maar als het wordt gefilterd, retourneert het alleen het aantal zichtbare cellen.

Probeer het zelf… Download het voorbeeldbestand

#2 Tel gekleurde cellen met behulp van de GET.CELL-functie

GET.CELL is een Macro4-functie die is behouden vanwege compatibiliteitsredenen.

Het werkt niet als het als normale functie in het werkblad wordt gebruikt.

Het werkt echter in Excel benoemde bereiken.

Zie ook: Meer weten over de GET.CELL-functie.

Hier zijn de drie stappen om GET.CELL te gebruiken om gekleurde cellen in Excel te tellen:

• Maak een benoemd bereik met behulp van de GET.CELL-functie
• Gebruik het benoemde bereik om de kleurcode in een kolom te krijgen
• Het kleurnummer gebruiken om het aantal gekleurde cellen te tellen (op kleur)

Laten we eens diep duiken en kijken wat we moeten doen in elk van de drie genoemde stappen.

Een benoemd bereik maken

• Ga naar Formules -> Definieer naam.
• Typ in het dialoogvenster Nieuwe naam:
  • Naam: GetColor
  • Toepassingsgebied: werkboek
  • Verwijst naar: =GET.CELL(38,Blad1!$A2)
    In de bovenstaande formule heb ik gebruikt Blad1!$A2 als tweede argument. U moet de verwijzing gebruiken van de kolom waar u de cellen met de achtergrondkleur hebt.

De kleurcode voor elke cel verkrijgen

Gebruik in de cel naast de gegevens de formule =GetColor

Deze formule retourneert 0 als er GEEN achtergrondkleur in een cel is en zou een specifiek getal retourneren als er een achtergrondkleur is.

Dit nummer is specifiek voor een kleur, dus alle cellen met dezelfde achtergrondkleur krijgen hetzelfde nummer.

Tel gekleurde cellen met behulp van de kleurcode

Als u het bovenstaande proces volgt, zou u een kolom hebben met nummers die overeenkomen met de achtergrondkleur erin.

Om de telling van een specifieke kleur te krijgen:

• Geef ergens onder de dataset dezelfde achtergrondkleur aan een cel die u wilt tellen. Zorg ervoor dat u dit in dezelfde kolom doet die u hebt gebruikt bij het maken van het benoemde bereik. Ik heb bijvoorbeeld kolom A gebruikt en daarom zal ik alleen de cellen in kolom 'A' gebruiken.
• Gebruik in de aangrenzende cel de volgende formule:

=AANTAL.ALS($F$2:$F$20,GetColor)

Deze formule geeft u het aantal cellen met de opgegeven achtergrondkleur.

Hoe werkt het?

De AANTAL.ALS-functie gebruikt het benoemde bereik (GetColor) als de criteria. Het benoemde bereik in de formule verwijst naar de aangrenzende cel aan de linkerkant (in kolom A) en retourneert de kleurcode voor die cel. Daarom is dit kleurcodenummer het criterium.

De AANTAL.ALS-functie gebruikt het bereik ($F$2:$F$18) dat de kleurcodenummers van alle cellen bevat en de telling retourneert op basis van het criteriumnummer.

Probeer het zelf… Download het voorbeeldbestand

#3 Tel gekleurd met VBA (door een aangepaste functie te maken)

In de bovenstaande twee methoden hebt u geleerd hoe u gekleurde cellen kunt tellen zonder VBA te gebruiken.

Maar als u het goed vindt om VBA te gebruiken, is dit de gemakkelijkste van de drie methoden.

Met behulp van VBA zouden we een aangepaste functie maken, die zou werken als een AANTAL.ALS-functie en het aantal cellen met de specifieke achtergrondkleur zou retourneren.

Hier is de code:

'Code gemaakt door Sumit Bansal van https://trumpexcel.com Functie GetColorCount (CountRange As Range, CountColor As Range) Dim CountColorValue As Integer Dim TotalCount As Integer CountColorValue = CountColor.Interior.ColorIndex Stel rCell = CountRange voor elke rCell in CountRange If rCell.Interior.ColorIndex = CountColorValue Then TotalCount = TotalCount + 1 End If Next rCell GetColorCount = TotalCount End-functie

Deze aangepaste functie maken:

• Terwijl uw werkmap actief is, drukt u op Alt + F11 (of klikt u met de rechtermuisknop op het werkbladtabblad en selecteert u Code weergeven). Dit zou de VB-editor openen.
• Klik in het linkerdeelvenster, onder de werkmap waarin u werkt, met de rechtermuisknop op een van de werkbladen en selecteer Invoegen -> Module. Dit zou een nieuwe module invoegen. Kopieer en plak de code in het modulecodevenster.
• Dubbelklik op de modulenaam (standaard de naam van de module in Module1) en plak de code in het codevenster.
• Sluit de VB-editor.
• Dat is het! U hebt nu een aangepaste functie in het werkblad met de naam GetColorCount.

Om deze functie te gebruiken, gebruikt u deze gewoon als een gewone Excel-functie.

Syntaxis: =GetColorCount(CountRange, CountColor)

• Tellerbereik: het bereik waarin u de cellen met de opgegeven achtergrondkleur wilt tellen.
• TellenKleur: de kleur waarvoor u de cellen wilt tellen.

Om deze formule te gebruiken, gebruikt u dezelfde achtergrondkleur (die u wilt tellen) in een cel en gebruikt u de formule. Het argument CountColor zou dezelfde cel zijn waarin u de formule invoert (zoals hieronder weergegeven):

Opmerking: Aangezien er een code in de werkmap staat, moet u deze opslaan met de extensie .xls of .xlsm.

Probeer het zelf… Download het voorbeeldbestand

Kent u een andere manier om gekleurde cellen in Excel te tellen?

Zo ja, deel het dan met mij door een reactie achter te laten.