✅ Hvordan telle fargede celler i Excel (trinn-for-trinn-guide + VIDEO)

Innholdsfortegnelse

Hvordan telle fargede celler i Excel

Se video - Slik teller du fargede celler i Excel

Ville det ikke vært flott hvis det var en funksjon som kunne telle fargede celler i Excel?

Dessverre er det ingen innebygd funksjon for å gjøre dette.

MEN…

Det kan enkelt gjøres.

Hvordan telle fargede celler i Excel

I denne opplæringen vil jeg vise deg tre måter å telle fargede celler i Excel (med og uten VBA):

Bruke filter og SUBTOTAL -funksjon
Bruker GET.CELL -funksjonen
Bruke en egendefinert funksjon opprettet med VBA

#1 Telle fargede celler ved hjelp av filter og SUBTOTAL

For å telle fargede celler i Excel må du bruke følgende to trinn:

Filtrer fargede celler
Bruk SUBTOTAL -funksjonen til å telle fargede celler som er synlige (etter filtrering).

Anta at du har et datasett som vist nedenfor:

Det er to bakgrunnsfarger som brukes i dette datasettet (grønt og oransje).

Her er trinnene som teller fargede celler i Excel:

I en hvilken som helst celle under datasettet bruker du følgende formel: = SUBTOTAL (102, E1: E20)
Velg overskriftene.
Gå til Data -> Sorter og filtrer -> Filter. Dette vil bruke et filter på alle overskriftene.
Klikk på hvilken som helst av filterrullegardinene.
Gå til "Filtrer etter farge" og velg fargen. I datasettet ovenfor, siden det er to farger som brukes til å markere cellene, viser filteret to farger for å filtrere disse cellene.

Så snart du filtrerer cellene, vil du legge merke til at verdien i SUBTOTAL -funksjonen endres og bare returnerer antallet celler som er synlige etter filtrering.

Hvordan virker dette?

SUBTOTAL -funksjonen bruker 102 som det første argumentet, som brukes til å telle synlige celler (skjulte rader telles ikke) i det angitte området.

Hvis dataene ikke filtreres, returnerer de 19, men hvis de filtreres, returnerer den bare antallet av de synlige cellene.

Prøv det selv … Last ned eksempelfilen

#2 Teller fargede celler ved hjelp av GET.CELL -funksjonen

GET.CELL er en Macro4 -funksjon som har blitt beholdt på grunn av kompatibilitetshensyn.

Det fungerer ikke hvis det brukes som vanlige funksjoner i regnearket.

Det fungerer imidlertid i Excel -navngitte områder.

Se også: Finn ut mer om GET.CELL -funksjonen.

Her er de tre trinnene for å bruke GET.CELL til å telle fargede celler i Excel:

Lag et navngitt område med GET.CELL -funksjonen
Bruk det navngitte området for å få fargekode i en kolonne
Bruke fargenummeret til å telle antall fargede celler (etter farge)

La oss dykke dypt og se hva vi skal gjøre i hvert av de tre nevnte trinnene.

Opprette et navngitt område

Gå til Formler -> Definer navn.
I dialogboksen Nytt navn skriver du inn:
- Navn: GetColor
- Omfang: Arbeidsbok
- Henviser til: = GET.CELL (38, Sheet1! $ A2)
  I formelen ovenfor har jeg brukt Ark1! $ A2 som det andre argumentet. Du må bruke referansen til kolonnen der du har cellene med bakgrunnsfargen.

Få fargekoden for hver celle

I cellen ved siden av dataene bruker du formelen = GetColor

Denne formelen vil returnere 0 hvis det INGEN bakgrunnsfarge er i en celle, og vil returnere et bestemt tall hvis det er en bakgrunnsfarge.

Dette tallet er spesifikt for en farge, så alle cellene med samme bakgrunnsfarge får samme nummer.

Tell fargede celler ved hjelp av fargekoden

Hvis du følger prosessen ovenfor, vil du ha en kolonne med tall som tilsvarer bakgrunnsfargen i den.

For å få tellingen av en bestemt farge:

Et sted under datasettet gir du den samme bakgrunnsfargen til en celle du vil telle. Sørg for at du gjør dette i samme kolonne som du brukte til å lage det navngitte området. For eksempel brukte jeg kolonne A, og derfor vil jeg bare bruke cellene i kolonne 'A'.
I den tilstøtende cellen bruker du følgende formel:

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

Denne formelen gir deg tellingen av alle cellene med den angitte bakgrunnsfargen.

Hvordan virker det?

COUNTIF -funksjonen bruker det navngitte området (GetColor) som kriterier. Det navngitte området i formelen refererer til den tilstøtende cellen til venstre (i kolonne A) og returnerer fargekoden for den cellen. Derfor er dette fargekodnummeret kriteriene.

COUNTIF -funksjonen bruker området ($ F $ 2: $ F $ 18) som inneholder fargekodetallene til alle cellene og returnerer tellingen basert på kriterietallet.

Prøv det selv … Last ned eksempelfilen

#3 Antall fargede ved hjelp av VBA (ved å lage en egendefinert funksjon)

I de to metodene ovenfor lærte du hvordan du teller fargede celler uten å bruke VBA.

Men hvis du har det bra med å bruke VBA, er dette den enkleste av de tre metodene.

Ved å bruke VBA ville vi opprette en egendefinert funksjon som fungerer som en COUNTIF -funksjon og returnerer antall celler med den spesifikke bakgrunnsfargen.

Her er koden:

'Kode opprettet av Sumit Bansal fra https://trumpexcel.com Funksjon GetColorCount (CountRange As Range, CountColor As Range) Dim CountColorValue As Integer Dim TotalCount As Integer CountColorValue = CountColor.Interior.ColorIndex Sett rCell = CountRange For hver rCell In CountRange If rCell.Interior.ColorIndex = CountColorValue Deretter TotalCount = TotalCount + 1 End If Next rCell GetColorCount = TotalCount Sluttfunksjon

Slik oppretter du denne egendefinerte funksjonen:

Når arbeidsboken er aktiv, trykker du Alt + F11 (eller høyreklikker på regnearkfanen og velger Vis kode). Dette ville åpne VB Editor.
I den venstre ruten, under arbeidsboken du jobber med, høyreklikker du på et av regnearkene og velger Sett inn -> Modul. Dette vil sette inn en ny modul. Kopier og lim inn koden i modulkodevinduet.
Dobbeltklikk på modulnavnet (som standard navnet på modulen i modul1) og lim inn koden i kodevinduet.
Lukk VB Editor.
Det er det! Du har nå en egendefinert funksjon i regnearket som heter GetColorCount.

For å bruke denne funksjonen, bare bruk den som en vanlig Excel -funksjon.

Syntaks: = GetColorCount (CountRange, CountColor)

CountRange: området du vil telle cellene i med den angitte bakgrunnsfargen.
CountColor: fargen du vil telle cellene for.

Hvis du vil bruke denne formelen, bruker du den samme bakgrunnsfargen (som du vil telle) i en celle og bruker formelen. CountColor -argumentet vil være den samme cellen der du angir formelen (som vist nedenfor):