✅ Hur man räknar färgade celler i Excel (steg-för-steg-guide + VIDEO)

Innehållsförteckning

Hur man räknar färgade celler i Excel

Titta på video - Hur man räknar färgade celler i Excel

Vore det inte bra om det fanns en funktion som kunde räkna färgade celler i Excel?

Tyvärr finns det ingen inbyggd funktion för att göra detta.

MEN…

Det kan enkelt göras.

Hur man räknar färgade celler i Excel

I den här självstudien visar jag dig tre sätt att räkna färgade celler i Excel (med och utan VBA):

Använda filter och SUBTOTAL -funktion
Använd GET.CELL -funktionen
Använda en anpassad funktion skapad med VBA

#1 Räkna färgade celler med filter och SUBTOTAL

För att räkna färgade celler i Excel måste du använda följande två steg:

Filtrera färgade celler
Använd funktionen SUBTOTAL för att räkna färgade celler som är synliga (efter filtrering).

Anta att du har en dataset som visas nedan:

Det finns två bakgrundsfärger som används i denna datamängd (grön och orange).

Här är stegen som räknar färgade celler i Excel:

I någon cell under datamängden använder du följande formel: = SUBTOTAL (102, E1: E20)
Välj rubriker.
Gå till Data -> Sortera och filtrera -> Filter. Detta kommer att tillämpa ett filter på alla rubriker.
Klicka på någon av filtrets listrutor.
Gå till "Filtrera efter färg" och välj färgen. I ovanstående dataset, eftersom det finns två färger som används för att markera cellerna, visar filtret två färger för att filtrera dessa celler.

Så snart du filtrerar cellerna kommer du att märka att värdet i funktionen SUBTOTAL ändras och endast returnerar antalet celler som är synliga efter filtrering.

Hur fungerar detta?

SUBTOTAL -funktionen använder 102 som det första argumentet, som används för att räkna synliga celler (dolda rader räknas inte) i det angivna intervallet.

Om data om den inte filtreras returnerar den 19, men om den filtreras, returnerar den bara antalet synliga celler.

Prova själv … Ladda ner exempelfilen

#2 Räkna färgade celler med funktionen GET.CELL

GET.CELL är en Macro4 -funktion som har bevarats på grund av kompatibilitetsskäl.

Det fungerar inte om det används som vanliga funktioner i kalkylbladet.

Det fungerar dock i Excel -namnintervall.

Se även: Läs mer om GET.CELL -funktionen.

Här är de tre stegen för att använda GET.CELL för att räkna färgade celler i Excel:

Skapa ett namngivet intervall med funktionen GET.CELL
Använd Named Range för att få färgkod i en kolumn
Använda färgnumret för att räkna antalet färgade celler (efter färg)

Låt oss djupdyka och se vad vi ska göra i vart och ett av de tre nämnda stegen.

Skapa ett namngivet intervall

Gå till Formler -> Definiera namn.
I dialogrutan Nytt namn anger du:
- Namn: GetColor
- Omfattning: Arbetsbok
- Avser: = GET.CELL (38, Sheet1! $ A2)
  I ovanstående formel har jag använt Blad1! $ A2 som det andra argumentet. Du måste använda referensen till kolumnen där du har cellerna med bakgrundsfärgen.

Få färgkoden för varje cell

I cellen intill data använder du formeln = GetColor

Denna formel skulle returnera 0 om det inte finns någon bakgrundsfärg i en cell och skulle returnera ett specifikt tal om det finns en bakgrundsfärg.

Detta nummer är specifikt för en färg, så alla celler med samma bakgrundsfärg får samma nummer.

Räkna färgade celler med färgkoden

Om du följer processen ovan skulle du ha en kolumn med siffror som motsvarar bakgrundsfärgen i den.

För att få räkningen av en specifik färg:

Någonstans under datamängden ger du samma bakgrundsfärg till en cell som du vill räkna. Se till att du gör detta i samma kolumn som du använde när du skapade det namngivna intervallet. Till exempel använde jag kolumn A, och därför kommer jag bara att använda cellerna i kolumnen 'A'.
Använd följande formel i den intilliggande cellen:

= COUNTIF ($ F $ 2: $ F $ 20, GetColor)

Denna formel ger dig räkningen av alla celler med den angivna bakgrundsfärgen.

Hur fungerar det?

COUNTIF -funktionen använder det angivna intervallet (GetColor) som kriterier. Det angivna intervallet i formeln hänvisar till den intilliggande cellen till vänster (i kolumn A) och returnerar färgkoden för den cellen. Därför är detta färgkodnummer kriterierna.

COUNTIF -funktionen använder intervallet ($ F $ 2: $ F $ 18) som innehåller färgkodnumren för alla celler och returnerar antalet baserat på kriterietalet.

Prova själv … Ladda ner exempelfilen

#3 Antal färgade med VBA (genom att skapa en anpassad funktion)

I de två ovanstående metoderna lärde du dig att räkna färgade celler utan att använda VBA.

Men om du har det bra med att använda VBA är det här den enklaste av de tre metoderna.

Med VBA skulle vi skapa en anpassad funktion som skulle fungera som en COUNTIF -funktion och returnera antalet celler med den specifika bakgrundsfärgen.

Här är koden:

'Kod skapad av Sumit Bansal från https://trumpexcel.com Funktion GetColorCount (CountRange As Range, CountColor As Range) Dim CountColorValue As Integer Dim TotalCount As Integer CountColorValue = CountColor.Interior.ColorIndex Set rCell = CountRange For Every rCell In CountRange If rCell.Interior.ColorIndex = CountColorValue Sedan TotalCount = TotalCount + 1 End If Next rCell GetColorCount = TotalCount End -funktion

Så här skapar du den här anpassade funktionen:

Med din arbetsbok aktiv, tryck på Alt + F11 (eller högerklicka på fliken kalkylblad och välj Visa kod). Detta skulle öppna VB Editor.
I den vänstra rutan, under arbetsboken där du arbetar, högerklickar du på något av kalkylbladet och väljer Infoga -> Modul. Detta skulle infoga en ny modul. Kopiera och klistra in koden i modulkodfönstret.
Dubbelklicka på modulnamnet (som standard namnet på modulen i modul1) och klistra in koden i kodfönstret.
Stäng VB -redigeraren.
Det är allt! Du har nu en anpassad funktion i kalkylbladet som heter GetColorCount.

För att använda denna funktion, använd den helt enkelt som en vanlig Excel -funktion.

Syntax: = GetColorCount (CountRange, CountColor)

CountRange: intervallet där du vill räkna cellerna med den angivna bakgrundsfärgen.
CountColor: färgen som du vill räkna cellerna för.

Om du vill använda denna formel använder du samma bakgrundsfärg (som du vill räkna) i en cell och använder formeln. CountColor -argumentet skulle vara samma cell som du anger formeln (som visas nedan):