Se video - Sådan tælles farvede celler i Excel
Ville det ikke være fantastisk, hvis der var en funktion, der kunne tælle farvede celler i Excel?
Desværre er der ikke nogen indbygget funktion til at gøre dette.
MEN…
Det kan sagtens lade sig gøre.
Sådan tæller du farvede celler i Excel
I denne vejledning viser jeg dig tre måder at tælle farvede celler i Excel (med og uden VBA):
- Brug af filter og SUBTOTAL funktion
- Brug af GET.CELL -funktionen
- Brug af en brugerdefineret funktion oprettet ved hjælp af VBA
#1 Tæl farvede celler ved hjælp af filter og SUBTOTAL
For at tælle farvede celler i Excel skal du bruge følgende to trin:
- Filtrer farvede celler
- Brug funktionen SUBTOTAL til at tælle farvede celler, der er synlige (efter filtrering).
Antag, at du har et datasæt som vist herunder:
Der er to baggrundsfarver, der bruges i dette datasæt (grøn og orange).
Her er trinene, der tæller farvede celler i Excel:
- I enhver celle under datasættet skal du bruge følgende formel: = SUBTOTAL (102, E1: E20)
- Vælg overskrifterne.
- Gå til Data -> Sorter og filtrer -> Filter. Dette vil anvende et filter på alle overskrifterne.
- Klik på en af filtrets rullemenuer.
- Gå til ‘Filtrer efter farve’, og vælg farven. I ovenstående datasæt, da der er to farver, der bruges til at fremhæve cellerne, viser filteret to farver til filtrering af disse celler.
Så snart du filtrerer cellerne, vil du bemærke, at værdien i funktionen SUBTOTAL ændres og kun returnerer det antal celler, der er synlige efter filtrering.
Hvordan virker det?
SUBTOTAL -funktionen bruger 102 som det første argument, som bruges til at tælle synlige celler (skjulte rækker tælles ikke) i det angivne område.
Hvis dataene ikke filtreres, returnerer de 19, men hvis de filtreres, returnerer de kun antallet af de synlige celler.
Prøv det selv … Download eksempelfilen
#2 Tæl farvede celler ved hjælp af GET.CELL -funktion
GET.CELL er en Macro4 -funktion, der er blevet bevaret på grund af kompatibilitetshensyn.
Det virker ikke, hvis det bruges som almindelige funktioner i regnearket.
Det fungerer dog i Excel navngivne områder.
Se også: Få mere at vide om GET.CELL -funktionen.
Her er de tre trin til at bruge GET.CELL til at tælle farvede celler i Excel:
- Opret et navngivet område ved hjælp af GET.CELL -funktionen
- Brug det navngivne område til at få farvekode i en kolonne
- Brug af farvetallet til at tælle antallet af farvede celler (efter farve)
Lad os dykke dybt og se, hvad vi skal gøre i hvert af de tre nævnte trin.
Oprettelse af et navngivet område
- Gå til formler -> Definer navn.
- I dialogboksen Nyt navn skal du indtaste:
- Navn: GetColor
- Anvendelsesområde: Arbejdsbog
- Henviser til: = GET.CELL (38, Sheet1! $ A2)
I ovenstående formel har jeg brugt Ark1! $ A2 som det andet argument. Du skal bruge referencen til den kolonne, hvor du har cellerne med baggrundsfarven.
Få farvekoden for hver celle
I cellen ved siden af dataene skal du bruge formlen = GetColor
Denne formel ville returnere 0, hvis der ikke er nogen baggrundsfarve i en celle og ville returnere et specifikt tal, hvis der er en baggrundsfarve.
Dette tal er specifikt for en farve, så alle cellerne med den samme baggrundsfarve får det samme tal.
Tæl farvede celler ved hjælp af farvekoden
Hvis du følger ovenstående proces, ville du have en kolonne med tal, der svarer til baggrundsfarven.
For at få optællingen af en bestemt farve:
- Et sted under datasættet skal du give den samme baggrundsfarve til en celle, som du vil tælle. Sørg for, at du gør dette i den samme kolonne, som du brugte til at oprette det navngivne område. For eksempel brugte jeg kolonne A, og derfor vil jeg kun bruge cellerne i kolonne 'A'.
- I den tilstødende celle skal du bruge følgende formel:
= COUNTIF ($ F $ 2: $ F $ 20, GetColor)
Denne formel giver dig optællingen af alle cellerne med den angivne baggrundsfarve.
Hvordan virker det?
COUNTIF -funktionen bruger det navngivne område (GetColor) som kriterier. Det navngivne område i formlen refererer til den tilstødende celle til venstre (i kolonne A) og returnerer farvekoden for den pågældende celle. Derfor er dette farvekodnummer kriterierne.
COUNTIF -funktionen bruger området ($ F $ 2: $ F $ 18), der indeholder farvekodnumrene på alle cellerne og returnerer tællingen baseret på kriterietallet.
Prøv det selv … Download eksempelfilen
#3 Farvet tælling ved hjælp af VBA (ved at oprette en brugerdefineret funktion)
I de to ovenstående metoder lærte du, hvordan man tæller farvede celler uden at bruge VBA.
Men hvis du har det godt med at bruge VBA, er dette den nemmeste af de tre metoder.
Ved hjælp af VBA ville vi oprette en brugerdefineret funktion, der ville fungere som en COUNTIF -funktion og returnere antallet af celler med den specifikke baggrundsfarve.
Her er koden:
'Kode oprettet af Sumit Bansal fra 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 Herefter TotalCount = TotalCount + 1 End Hvis Næste rCell GetColorCount = TotalCount Slutfunktion
Sådan opretter du denne brugerdefinerede funktion:
- Med din projektmappe aktiv skal du trykke på Alt + F11 (eller højreklik på regnearkfanen og vælg Vis kode). Dette ville åbne VB Editor.
- I venstre rude under den projektmappe, du arbejder i, skal du højreklikke på et hvilket som helst af regnearkene og vælge Indsæt -> Modul. Dette ville indsætte et nyt modul. Kopier og indsæt koden i modulkodevinduet.
- Dobbeltklik på modulnavnet (som standard navnet på modulet i modul1) og indsæt koden i kodevinduet.
- Luk VB Editor.
- Det er det! Du har nu en tilpasset funktion i regnearket kaldet GetColorCount.
For at bruge denne funktion skal du blot bruge den som enhver almindelig Excel -funktion.
Syntaks: = GetColorCount (CountRange, CountColor)
- CountRange: det område, hvor du vil tælle cellerne med den angivne baggrundsfarve.
- CountColor: den farve, du vil tælle cellerne for.
Hvis du vil bruge denne formel, skal du bruge den samme baggrundsfarve (som du vil tælle) i en celle og bruge formlen. CountColor -argumentet ville være den samme celle, hvor du indtaster formlen (som vist nedenfor):
Bemærk: Da der er en kode i projektmappen, skal du gemme den med en .xls- eller .xlsm -udvidelse.
Prøv det selv … Download eksempelfilen
Kender du nogen anden måde at tælle farvede celler i Excel?
Hvis ja, del det med mig ved at efterlade en kommentar.
