Excel har mange funktioner, hvor en bruger skal angive et enkelt eller flere kriterier for at få resultatet. Hvis du f.eks. Vil tælle celler baseret på flere kriterier, kan du bruge funktionerne TÆLLE eller TÆLLE i Excel.
Denne vejledning dækker forskellige måder at bruge et eller flere kriterier i COUNTIF og COUNTIFS -funktionen i Excel.
Selvom jeg primært vil fokusere på COUNTIF og COUNTIFS -funktioner i denne vejledning, kan alle disse eksempler også bruges i andre Excel -funktioner, der tager flere kriterier som input (f.eks. SUMIF, SUMIFS, AVERAGEIF og AVERAGEIFS).
En introduktion til Excel COUNTIF og COUNTIFS funktioner
Lad os først få greb om at bruge COUNTIF- og COUNTIFS -funktioner i Excel.
Excel COUNTIF -funktion (tager enkeltkriterier)
Excel COUNTIF -funktionen er bedst egnet til situationer, hvor du vil tælle celler baseret på et enkelt kriterium. Hvis du vil tælle baseret på flere kriterier, skal du bruge funktionen TÆLLER.
Syntaks
= COUNTIF (område, kriterier)
Input argumenter
- rækkevidde - det område af celler, du vil tælle.
- kriterier - de kriterier, der skal evalueres i forhold til celleområdet for en celle, der skal tælles.
Excel COUNTIFS -funktion (tager flere kriterier)
Excel COUNTIFS -funktionen er bedst egnet til situationer, hvor du vil tælle celler baseret på flere kriterier.
Syntaks
= COUNTIFS (criteria_range1, criteria1, [criteria_range2, criteria2]…)
Input argumenter
- kriterier_område1 - Celleområdet, som du vil evaluere i forhold til kriterier1.
- kriterier 1 - de kriterier, du vil evaluere for criteria_range1 for at bestemme, hvilke celler der skal tælles.
- [criteria_range2] - Celleområdet, som du vil evaluere i forhold til kriterier2.
- [kriterier2] - de kriterier, du vil evaluere for criteria_range2 for at bestemme, hvilke celler der skal tælles.
Lad os nu se på nogle eksempler på brug af flere kriterier i COUNTIF -funktioner i Excel.
Brug af NUMBER -kriterier i Excel COUNTIF -funktioner
#1 Tæl celler, når kriterier er lig med en værdi
For at få antallet af celler, hvor kriterie -argumentet er lig med en bestemt værdi, kan du enten indtaste kriterierne direkte eller bruge den cellereference, der indeholder kriterierne.
Nedenfor er et eksempel, hvor vi tæller de celler, der indeholder tallet 9 (hvilket betyder, at kriterier argumentet er lig med 9). Her er formlen:
= COUNTIF ($ B $ 2: $ B $ 11, D3)
I ovenstående eksempel (på billedet) er kriterierne i celle D3. Du kan også indtaste kriterierne direkte i formlen. For eksempel kan du også bruge:
= COUNTIF ($ B $ 2: $ B $ 11,9)
#2 Tæl celler, når kriterierne er større end en værdi
For at få antallet af celler med en værdi, der er større end en angivet værdi, bruger vi operatoren større end (">"). Vi kunne enten bruge det direkte i formlen eller bruge en cellereference, der har kriterierne.
Når vi bruger en operator i kriterier i Excel, skal vi sætte den inden for dobbelt anførselstegn. For eksempel, hvis kriterierne er større end 10, skal vi indtaste "> 10" som kriterier (se billede nedenfor):
Her er formlen:
= COUNTIF ($ B $ 2: $ B $ 11, ”> 10 ″)
Du kan også have kriterierne i en celle og bruge cellereferencen som kriterier. I dette tilfælde behøver du IKKE at sætte kriterierne i dobbelte anførselstegn:
= COUNTIF ($ B $ 2: $ B $ 11, D3)
Der kan også være et tilfælde, hvor du vil have kriterierne til at være i en celle, men ikke vil have det med operatøren. For eksempel vil du måske have, at cellen D3 skal have tallet 10 og ikke> 10.
I så fald skal du oprette et kriterie -argument, som er en kombination af operator og cellereference (se billede nedenfor):
= COUNTIF ($ B $ 2: $ B $ 11, ”>” og D3)
BEMÆRK: Når du kombinerer en operator og en cellereference, er operatoren altid i dobbelte anførselstegn. Operatøren og cellereferencen er forbundet med et ampersand (&).
#3 Tæl celler, når kriterier er mindre end en værdi
For at få antallet af celler med en værdi mindre end en angivet værdi, bruger vi operatøren mindre end (“<“). Vi kunne enten bruge det direkte i formlen eller bruge en cellereference, der har kriterierne.
Når vi bruger en operator i kriterier i Excel, skal vi sætte den inden for dobbelt anførselstegn. For eksempel, hvis kriteriet er, at tallet skal være mindre end 5, skal vi indtaste “<5” som kriterier (se billede herunder):
= COUNTIF ($ B $ 2: $ B $ 11, ”<5 ″)
Du kan også have kriterierne i en celle og bruge cellereferencen som kriterier. I dette tilfælde behøver du IKKE at sætte kriterierne i dobbelte citater (se billede herunder):
= COUNTIF ($ B $ 2: $ B $ 11, D3)
Der kan også være et tilfælde, hvor du vil have kriterierne til at være i en celle, men ikke vil have det med operatøren. For eksempel vil du måske have, at cellen D3 skal have tallet 5 og ikke <5.
I så fald skal du oprette et kriterie -argument, som er en kombination af operator og cellereference:
= COUNTIF ($ B $ 2: $ B $ 11, ”<“ & D3)
BEMÆRK: Når du kombinerer en operator og en cellereference, er operatoren altid i dobbelte anførselstegn. Operatøren og cellereferencen er forbundet med et ampersand (&).
#4 Tæl celler med flere kriterier - mellem to værdier
For at få et antal værdier mellem to værdier skal vi bruge flere kriterier i funktionen TÆLLE.
Her er to metoder til at gøre dette:
METODE 1: Brug af COUNTIFS -funktionen
COUNTIFS -funktionen kan håndtere flere kriterier som argumenter og tæller cellerne, når alle kriterierne er SANDE. For at tælle celler med værdier mellem to angivne værdier (f.eks. 5 og 10) kan vi bruge følgende COUNTIFS -funktion:
= COUNTIFS ($ B $ 2: $ B $ 11, ”> 5 ″, $ B $ 2: $ B $ 11,” <10 ″)
BEMÆRK: Ovenstående formel tæller ikke celler, der indeholder 5 eller 10. Hvis du vil inkludere disse celler, skal du bruge større end lig med (> =) og mindre end lig med (<=) operatorer. Her er formlen:
= COUNTIFS ($ B $ 2: $ B $ 11, ”> = 5 ″, $ B $ 2: $ B $ 11,” <= 10 ″)
Du kan også have disse kriterier i celler og bruge cellereferencen som kriterier. I dette tilfælde behøver du IKKE at sætte kriterierne i dobbelte citater (se billede herunder):
Du kan også bruge en kombination af cellereferencer og operatorer (hvor operatoren er angivet direkte i formlen). Når du kombinerer en operator og en cellereference, er operatoren altid i dobbelte anførselstegn. Operatøren og cellereferencen er forbundet med et ampersand (&).
METODE 2: Brug af to COUNTIF -funktioner
Hvis du har flere kriterier, kan du enten bruge COUNTIFS eller oprette en kombination af COUNTIF -funktioner. Nedenstående formel ville også gøre det samme:
= COUNTIF ($ B $ 2: $ B $ 11, ”> 5 ″)-COUNTIF ($ B $ 2: $ B $ 11,”> 10 ″)
I ovenstående formel finder vi først antallet af celler, der har en værdi større end 5, og vi trækker antallet af celler med en værdi større end 10. Dette ville give os resultatet som 5 (hvilket er antallet af celler, der har værdier mere end 5 og mindre end lig med 10).
Hvis du vil have formlen til at indeholde både 5 og 10, skal du bruge følgende formel i stedet:
= COUNTIF ($ B $ 2: $ B $ 11, ”> = 5 ″)-COUNTIF ($ B $ 2: $ B $ 11,”> 10 ″)
Hvis du vil have formlen til at ekskludere både '5' og '10' fra optællingen, skal du bruge følgende formel:
= COUNTIF ($ B $ 2: $ B $ 11, ”> = 5 ″)-COUNTIF ($ B $ 2: $ B $ 11,”> 10 ″)-COUNTIF ($ B $ 2: $ B $ 11,10)
Du kan have disse kriterier i celler og bruge cellereferencerne, eller du kan bruge en kombination af operatorer og cellereferencer.
Brug af TEKST -kriterier i Excel -funktioner
#1 Tæl celler, når kriterierne er lig med en specificeret tekst
For at tælle celler, der indeholder et nøjagtigt match med den angivne tekst, kan vi simpelthen bruge den tekst som kriterier. For eksempel i datasættet (vist nedenfor på billedet), hvis jeg vil tælle alle cellerne med navnet Joe i, kan jeg bruge nedenstående formel:
= COUNTIF ($ B $ 2: $ B $ 11, ”Joe”)
Da dette er en tekststreng, skal jeg sætte tekstkriterierne i dobbelte anførselstegn.
Du kan også have kriterierne i en celle og derefter bruge denne cellereference (som vist herunder):
= COUNTIF ($ B $ 2: $ B $ 11, E3)
BEMÆRK: Du kan få forkerte resultater, hvis der er ledende/efterfølgende mellemrum i kriterierne eller kriterierne. Sørg for at rense dataene, før du bruger disse formler.
#2 Tæl celler, når kriterier IKKE er lig med en specificeret tekst
I lighed med det, vi så i ovenstående eksempel, kan du også tælle celler, der ikke indeholder en specificeret tekst. For at gøre dette skal vi bruge operatoren (), der ikke er lig med.
Antag, at du vil tælle alle de celler, der ikke indeholder navnet JOE, her er formlen, der vil gøre det:
= COUNTIF ($ B $ 2: $ B $ 11, ”Joe”)
Du kan også have kriterierne i en celle og bruge cellereferencen som kriterier. I dette tilfælde behøver du IKKE at sætte kriterierne i dobbelte citater (se billede herunder):
= COUNTIF ($ B $ 2: $ B $ 11, E3)
Der kan også være et tilfælde, hvor du vil have kriterierne til at være i en celle, men ikke vil have det med operatøren. For eksempel vil du måske have, at cellen D3 skal have navnet Joe og ikke Joe.
I så fald skal du oprette et kriterie -argument, som er en kombination af operator og cellereference (se billede nedenfor):
= COUNTIF ($ B $ 2: $ B $ 11, ”” ”og E3)
Når du kombinerer en operator og en cellereference, er operatoren altid i dobbelte anførselstegn. Operatøren og cellereferencen er forbundet med et ampersand (&).
Brug af DATE -kriterier i Excel COUNTIF- og COUNTIFS -funktioner
Dato og klokkeslæt i Excel gemmes som tal. Så vi kan bruge det på samme måde som vi bruger tal.
#1 Tæl celler, når kriterierne er lig med en bestemt dato
For at få antallet af celler, der indeholder den angivne dato, ville vi bruge ligværdien til operatoren (=) sammen med datoen.
For at bruge datoen anbefaler jeg at bruge funktionen DATE, da den slipper for enhver mulighed for fejl i datoværdien. Så hvis jeg f.eks. Vil bruge datoen 1. september 2015, kan jeg bruge funktionen DATE som vist herunder:
= DATO (2015,9,1)
Denne formel ville returnere den samme dato på trods af regionale forskelle. For eksempel vil 01-09-2015 være 1. september 2015 i henhold til den amerikanske datosyntaks og 09. januar 2015 i henhold til den britiske datosyntaks. Denne formel ville dog altid vende tilbage 1. september 2105.
Her er formlen til at tælle antallet af celler, der indeholder datoen 02-09-2015:
= COUNTIF ($ A $ 2: $ A $ 11, DATE (2015,9,2))
#2 Tæl celler, når kriterier er FØR eller EFTER til en bestemt dato
For at tælle celler, der indeholder dato før eller efter en bestemt dato, kan vi bruge operatørerne mindre end/større end.
For eksempel, hvis jeg vil tælle alle cellerne, der indeholder en dato, der er efter 02. september 2015, kan jeg bruge formlen:
= COUNTIF ($ A $ 2: $ A $ 11, ”>” & DATO (2015,9,2))
På samme måde kan du også tælle antallet af celler før en bestemt dato. Hvis du vil medtage en dato i optællingen, skal du bruge og 'lig med' operatoren sammen med 'større end/mindre end' operatoren.
Du kan også bruge en cellereference, der indeholder en dato. I dette tilfælde skal du kombinere operatoren (inden for dobbelte anførselstegn) med datoen ved hjælp af et ampersand (&).
Se eksempel herunder:
= COUNTIF ($ A $ 2: $ A $ 11, ”>” og F3)
#3 Tæl celler med flere kriterier - mellem to datoer
For at få et antal værdier mellem to værdier skal vi bruge flere kriterier i funktionen TÆLLE.
Vi kan gøre dette ved hjælp af to metoder - En enkelt COUNTIFS -funktion eller to COUNTIF -funktioner.
METODE 1: Brug af COUNTIFS -funktionen
COUNTIFS -funktionen kan tage flere kriterier som argumenter og tæller cellerne, når alle kriterierne er SANDE. For at tælle celler med værdier mellem to angivne datoer (f.eks. 2. september og 7. september) kan vi bruge følgende COUNTIFS -funktion:
= COUNTIFS ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,2), $ A $ 2: $ A $ 11, ”<“ & DATE (2015,9,7))
Ovenstående formel tæller ikke celler, der indeholder de angivne datoer. Hvis du også vil medtage disse datoer, skal du bruge større end lig med (> =) og mindre end lig med (<=) operatorer. Her er formlen:
= COUNTIFS ($ A $ 2: $ A $ 11, ”> =” & DATE (2015,9,2), $ A $ 2: $ A $ 11, ”<=” & DATE (2015,9,7))
Du kan også have datoerne i en celle og bruge cellereferencen som kriterier. I dette tilfælde kan du ikke have operatøren med datoen i cellerne. Du skal manuelt tilføje operatører i formlen (i dobbelte anførselstegn) og tilføje cellereference ved hjælp af et ampersand (&). Se billedet herunder:
= COUNTIFS ($ A $ 2: $ A $ 11, ”>” & F3, $ A $ 2: $ A $ 11, ”<“ & G3)
METODE 2: Brug af COUNTIF -funktioner
Hvis du har flere kriterier, kan du enten bruge en COUNTIFS -funktion eller oprette en kombination af to COUNTIF -funktioner. Nedenstående formel ville også gøre tricket:
= COUNTIF ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,2))-COUNTIF ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,7))
I ovenstående formel finder vi først antallet af celler, der har en dato efter 2. september, og vi trækker antallet af celler med datoer efter 7. september. Dette ville give os resultatet som 7 (hvilket er antallet af celler, der har datoer efter 2. september og senest den 7. september).
Hvis du ikke vil have formlen til at tælle både 2. september og 7. september, skal du bruge følgende formel i stedet:
= COUNTIF ($ A $ 2: $ A $ 11, ”> =” & DATE (2015,9,2))-COUNTIF ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,7))
Hvis du vil ekskludere begge datoer fra at tælle, skal du bruge følgende formel:
= COUNTIF ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,2))-COUNTIF ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,7) -COUNTIF ($ A $ 2: $ A $ 11, DATO (2015,9,7)))
Du kan også have kriteriedatoerne i celler og bruge cellereferencerne (sammen med operatorer i dobbelte anførselstegn sammenføjet ved hjælp af ampersand).
Brug af WILDCARD -KARAKTERER i kriterier i COUNTIF & COUNTIFS -funktioner
Der er tre jokertegn i Excel:
- * (stjerne) - Det repræsenterer et vilkårligt antal tegn. For eksempel kan ex* betyde excel, excels, eksempel, ekspert osv.
- ? (spørgsmålstegn) - Det repræsenterer en enkelt karakter. F.eks. Kan Tr? Mp betyde Trump eller Tramp.
- ~ (tilde) - Det bruges til at identificere et jokertegn (~, *,?) I teksten.
Du kan bruge COUNTIF -funktionen med jokertegn til at tælle celler, når anden indbygget tællefunktion mislykkes. Antag f.eks., At du har et datasæt som vist herunder:
Lad os nu tage forskellige eksempler:
#1 Tæl celler, der indeholder tekst
For at tælle celler med tekst i kan vi bruge jokertegnet * (stjerne). Da stjernen repræsenterer et vilkårligt antal tegn, vil den tælle alle celler, der har tekst i den. Her er formlen:
= COUNTIFS ($ C $ 2: $ C $ 11, ”*”)
Bemærk: Formlen ovenfor ignorerer celler, der indeholder tal, tomme celler og logiske værdier, men vil tælle, at cellerne indeholder en apostrof (og derfor fremstår tomme) eller celler, der indeholder tom streng (= ””), som muligvis er blevet returneret som en del af en formel.
Her er en detaljeret vejledning i håndtering af sager, hvor der er en tom streng eller apostrof.
Her er en detaljeret vejledning i håndtering af sager, hvor der er tomme strenge eller apostrofer.
Nedenfor er en video, der forklarer forskellige scenarier for at tælle celler med tekst i.
#2 Tæl ikke-tomme celler
Hvis du overvejer at bruge COUNTA -funktionen, så tænk igen.
Prøv det, og det kan mislykkes dig. COUNTA vil også tælle en celle, der indeholder en tom streng (ofte returneret med formler som = ”” eller når folk kun indtaster en apostrof i en celle). Celler, der indeholder tomme strenge, ser tomme ud, men er ikke, og tælles således af funktionen COUNTA.
COUNTA vil også tælle en celle, der indeholder en tom streng (ofte returneret med formler som = ”” eller når folk kun indtaster en apostrof i en celle). Celler, der indeholder tomme strenge, ser tomme ud, men er ikke, og tælles således af funktionen COUNTA.
Så hvis du bruger formlen = COUNTA (A1: A11), returnerer den 11, mens den skal returnere 10.
Her er rettelsen:
= COUNTIF ($ A $ 1: $ A $ 11, ”?*”)+COUNT ($ A $ 1: $ A $ 11)+SUMPRODUCT (-ISLOGISK ($ A $ 1: $ A $ 11))
Lad os forstå denne formel ved at nedbryde den:
- COUNTIF ($ N $ 8: $ N $ 18, ”?*”) - Denne del af formlen returnerer 5. Dette inkluderer enhver celle, der har et teksttegn i sig. A? repræsenterer et tegn og * repræsenterer et vilkårligt antal tegn. Derfor tvinger kombinationen?* I kriterierne sig til at tælle celler, der har mindst et teksttegn i sig.
- COUNT ($ A $ 1: $ A $ 11) - Dette tæller alle de celler, der indeholder tal. I eksemplet ovenfor returnerer det 3.
- SUMPRODUCT (-ISLOGISK ($ A $ 1: $ A $ 11) - Dette tæller alle de celler, der indeholder logiske værdier. I ovenstående eksempel returnerer det 2.
#3 Tæl celler, der indeholder specifik tekst
Lad os sige, at vi vil tælle alle de celler, hvor sælgernavnet begynder med J. Dette kan let opnås ved at bruge et jokertegn i funktionen TÆLLING. Her er formlen:
= COUNTIFS ($ C $ 2: $ C $ 11, ”J*”)
Kriterierne J* angiver, at teksten i en celle skal begynde med J og kan indeholde et vilkårligt antal tegn.
Hvis du vil tælle celler, der indeholder alfabetet hvor som helst i teksten, skal du flanke det med en stjerne på begge sider. Hvis du f.eks. Vil tælle celler, der indeholder alfabetet "a", skal du bruge * a * som kriterier.
Denne artikel er usædvanlig lang i forhold til mine andre artikler. Håber du har nydt det. Fortæl mig dine tanker ved at efterlade en kommentar.
Du kan også finde følgende Excel -selvstudier nyttige:
- Tæl antallet af ord i Excel.
- Tæl celler baseret på baggrundsfarve i Excel.
- Sådan summeres en kolonne i Excel (5 virkelig nemme måder)
