Denne vejledning viser, hvordan du brugerEksklel COUNTIF og COUNTIFS Functioner i Excel for at tælle data, der opfylder visse kriterier.
COUNTIF Funktionsoversigt
Du kan bruge funktionen TÆLLE i Excel til at tælle celler, der indeholder en bestemt værdi, tælle celler, der er større end eller lig med en værdi osv.
(Bemærk hvordan formelindgangene vises)
COUNTIF Funktionssyntaks og argumenter:
= COUNTIF (område, kriterier)
rækkevidde - Cellernes rækkevidde at tælle.
kriterier - Kriterierne, der styrer, hvilke celler der skal tælles.
Hvad er COUNTIF -funktionen?
COUNTIF -funktionen er en af de ældre funktioner, der bruges i regneark. Enkelt sagt er det fantastisk at scanne et område og fortælle dig, hvor mange af cellerne, der opfylder denne betingelse. Vi ser på, hvordan funktionen fungerer med tekst, tal og datoer; samt nogle af de andre situationer, der kan opstå.
Grundlæggende eksempel
Lad os starte med at se på denne liste over tilfældige emner. Vi har nogle tal, tomme celler og nogle tekststrenge.
Hvis du ville vide, hvor mange varer en nøjagtig matchning til kriterierne er, kan du angive, hvad du vil se efter som det andet argument. Et eksempel på denne formel kan se ud
= COUNTIF (A2: A9, "Apple")
Denne formel ville returnere tallet 3, da der er 3 celler i vores område, der opfylder disse kriterier. Alternativt kan vi bruge en cellereference i stedet for at hardcoding en værdi. Hvis vi skrev "Apple" i celle G2, kunne vi ændre formlen til
= COUNTIF (A2: A9, G2)
Når man handler med tal, er det vigtigt at skelne mellem tal og tal, der er gemt som tekst. Generelt sætter du ikke anførselstegn omkring tal, når du skriver formler. Så for at skrive en formel, der tjekker for tallet 5, ville du skrive
= COUNTIF (A2: A9, 5)
Endelig kunne vi også kontrollere for tomme celler ved hjælp af en streng med nul længde. Vi ville skrive den formel som
= COUNTIF (A2: A9, "")
Bemærk: Denne formel tæller både celler, der virkelig er tomme, såvel som dem, der er tomme som følge af en formel, ligesom en IF -funktion.
Delvise kampe
COUNTIF -funktionen understøtter brugen af jokertegn, “*” eller “?”, I kriterierne. Lad os se på denne liste over velsmagende bagværk:
For at finde alle de elementer, der starter med Apple, kunne vi skrive "Apple*". Så for at få et svar på 3 er vores formel i D2
= COUNTIF (A2: A5, "Apple*")
Bemærk: COUNTIF-funktionen er ikke store og små bogstaver, så du kan også skrive “æble*”, hvis du vil.
Tilbage til vores bagværk, vil vi måske også finde ud af, hvor mange tærter vi har på vores liste. Det kan vi finde ved at placere jokertegnet i begyndelsen af vores søgeterm og skrive
= COUNTIF (A2: A5, "*pie")
Denne formel giver resultatet af 2.
Vi kan også bruge jokertegn til at kontrollere, om der er celler med tekst. Lad os gå tilbage til vores originale dataliste.
For at tælle antallet af celler, der har mindst noget tekst, og dermed ikke tælle tal eller tom celle, kan vi skrive
= COUNTIF (A2: A9, "*")
Du kan se, at vores formel korrekt returnerer et resultat på 4.
Sammenligningsoperatorer i COUNTIF
Når vi skrev kriterierne hidtil, har vi antydet, at vores sammenligningsoperator er "=". Faktisk kunne vi have skrevet dette:
= COUNTIF (A2: A9, "= Apple")
Det er dog en ekstra karakter at skrive ud, så det er normalt udeladt. Dette betyder dog, at du kan bruge de andre operatører, f.eks. Større end, mindre end eller ikke lig med. Lad os se på denne liste over registrerede aldre:
Hvis vi ville vide, hvor mange børn der er mindst 5 år, kan vi skrive en sammenligning på "større end eller lig med" sådan:
= COUNTIF (A2: A8, "> = 5")
Bemærk: Sammenligningsoperatoren er altid angivet som en tekststreng og skal derfor være inden for anførselstegn.
På samme måde kan du også kontrollere for varer, der er mindre end en given værdi. Hvis vi skal finde ud af, hvor mange der er mindre end 8, kan vi skrive ud
= COUNTIF (A2: A8, "<8")
Dette giver os det ønskede resultat af 5. Lad os nu forestille os, at alle de 6-årige børn skal på udflugt. Hvor mange børn bliver der tilbage? Vi kan finde ud af dette ved at bruge en "ikke lig med" sammenligning som denne:
= COUNTIF (A2: A8, "6")
Nu kan vi hurtigt se, at vi har 6 børn, der ikke er 6 år.
I disse sammenligningseksempler hidtil har vi svært ved at kode de værdier, vi ønskede. Du kan også bruge en cellereference. Tricket er, at du skal sammenkoble sammenligningsoperatoren med cellereferencen. Lad os sige, at vi satte tallet 7 i celle C2, og vi vil have vores formel i D2 til at vise, hvor mange børn der er mindre end 7 år.
Vores formel i D2 skal se sådan ud:
= COUNTIF (A2: A8, "<" og C2)
Bemærk: Vær særlig opmærksom, når du skriver disse formler, om du skal sætte et element inden for anførselstegn eller udenfor. Operatørerne er altid inde i citater, cellereferencer er altid uden for citater. Tal er udenfor, hvis du laver en nøjagtig match, men indeni, hvis du foretager en sammenligningsoperator.
Arbejde med datoer
Vi har set, hvordan du kan angive en tekst eller et nummer som kriterier, men hvad med når vi skal arbejde med datoer? Her er en hurtig eksempleliste, vi kan arbejde med:
For at tælle, hvor mange datoer der er efter den 4. maj, skal vi være lidt forsigtige. Computere gemmer datoer som tal, så vi skal sørge for, at computeren bruger det rigtige nummer. Hvis vi skrev denne formel, ville vi få det korrekte resultat?
= COUNTIF (A2: A9, "
Svaret er "muligvis". Fordi vi udelod året fra vores kriterier, antager computeren, at vi mener det aktuelle år. Hvis alle de datoer, vi arbejder med, er for det indeværende år, får vi det korrekte svar. Hvis der dog er nogle datoer i fremtiden, får vi det forkerte svar. Når det næste år begynder, returnerer denne formel også et andet resultat. Som sådan bør denne syntaks sandsynligvis undgås.
Fordi det kan være svært at skrive datoer korrekt inden for en formel, er det den bedste praksis at skrive den dato, du vil bruge i en celle, og derefter kan du bruge denne cellereference i din COUNTIF -formel. Så lad os skrive datoen den 7. maj-2020 i celle C2, og så kan vi sætte vores formel i C4.
Formlen i C4 er
= COUNTIF (A2: A9, "<" og C2)
Nu ved vi, at resultatet af 7 er korrekt, og svaret ændrer ikke uventet, hvis vi åbner dette regneark engang i fremtiden.
Inden vi forlader dette afsnit, er det almindeligt at bruge funktionen TODAY, når vi arbejder med datoer. Vi kan bruge det, ligesom vi ville bruge en cellereference. For eksempel kunne vi ændre den tidligere formel til at være denne:
= COUNTIF (A2: A9, "<" & I DAG ())
Nu vil vores formel stadig opdateres efterhånden som realtid skrider frem, og vi vil have et antal varer, der er mindre end i dag.
Flere kriterier og COUNTIFS
Den oprindelige COUNTIF -funktion fik en forbedring i 2007, da COUNTIFS kom ud. Syntaksen mellem de to er meget ens, idet sidstnævnte giver dig mulighed for at give yderligere intervaller og kriterier. Du kan nemt bruge COUNTIFS i enhver situation, hvor COUNTIF findes. Det er bare en god idé at vide, at begge funktioner findes.
Lad os se på denne datatabel:
For at finde ud af, hvor mange mennesker der er i lønniveauer 1 til 2, kan du skrive en summering af COUNTIF -funktioner som denne:
= COUNTIF (B2: B7, "> = 1")-COUNTIF (B2: B7, "> 2")
Denne formel fungerer, da du finder alt, hvad der er over 1, men derefter trækker fra antallet af poster, der ligger uden for dit afskæringspunkt. Alternativt kan du bruge COUNTIFS som denne:
= ANTALS (B2: B7, "> = 1", B2: B7, "<= 2")
Sidstnævnte er mere intuitiv at læse, så du vil måske bruge den rute. COUNTIFS er også mere kraftfuld, når du skal overveje flere kolonner. Lad os sige, at vi vil vide, hvor mange mennesker der er i ledelse og i lønniveau 1. Du kan ikke gøre det med kun et TÆLL; du skulle skrive ud
= COUNTIFS (A2: A7, "Management", B2: B7, 1)
Denne formel ville give dig det korrekte resultat af 2. Inden vi forlader dette afsnit, lad os overveje en Or -type -logik. Hvad hvis vi ville finde ud af, hvor mange mennesker der er i ledelsen eller? Du skal tilføje nogle COUNTIFS sammen, men der er to måder at gøre dette på. Den enkleste måde er at skrive det sådan:
= COUNTIF (A2: A7, "HR")+COUNTIF (A2: A7, "Management")
Du kan også gøre brug af en matrix og skrive denne matrixformel:
= SUM (COUNTIF (A2: A7, {"HR", "Management"}))
Bemærk: Arrayformler skal bekræftes ved hjælp af 'Ctrl+Shift+Enter' ikke kun 'Enter'.
Hvordan denne formel fungerer, er, at det vil se, at du har givet et array som input. Det vil således beregne resultatet til to forskellige COUNTIF -funktioner og gemme dem i en matrix. SUM -funktionen tilføjer derefter alle resultaterne i vores array til en enkelt output. Således vil vores formel blive vurderet således:
= SUM (COUNTIF (A2: A7, {"HR", "Management"})) = SUM ({2, 3}) = 5
Tæl unikke værdier
Nu hvor vi har set, hvordan man bruger en matrix med COUNTIF -funktionen, kan vi tage det et skridt videre for at hjælpe os med at tælle, hvor mange unikke værdier der er i et område. Lad os først se på vores liste over afdelinger igen.
= SUM (1/COUNTIF (A2: A7, A2: A7))
Vi kan se, at der er 6 celler til data, men der er kun 3 forskellige elementer. For at få matematikken til at fungere, skal vi bruge hvert element til at være 1/N værd, hvor N er antallet af gange, et element gentages. For eksempel, hvis hver HR kun var 1/2 værd, ville du, når du tilføjede dem, få et tal på 1 for 1 unik værdi.
Tilbage til vores COUNTIF, som er designet til at finde ud af, hvor mange gange en vare vises i et område. I D2 skriver vi matrixformlen
= SUM (1/COUNTIF (A2: A7, A2: A7))
Hvordan denne formel fungerer, er for hver celle i intervallet A2: A7, den vil kontrollere, hvor mange gange den vises. Med vores prøve vil dette producere en række
{2, 2, 3, 3, 3, 1}
Derefter vender vi alle disse tal til brøker ved at foretage en deling. Nu ser vores array ud
{1/2, 1/2, 1/3, 1/3, 1/3, 1/1}
Når vi tilføjer alt dette, får vi det ønskede resultat på 3.
Countif med to eller flere betingelser - Countifs -funktionen
Indtil videre har vi kun arbejdet med COUNTIF -funktionen. COUNTIF -funktionen kan kun håndtere ét kriterium ad gangen. For at COUNTIF med flere kriterier skal du bruge COUNTIFS -funktionen. COUNTIFS opfører sig nøjagtigt som COUNTIF. Du tilføjer bare ekstra kriterier. Lad os se på eksemplet herunder.
= ANTALS (B2: B7, "= 130")
COUNTIF & COUNTIFS i Google Sheets
COUNTIF & COUNTIFS -funktionen fungerer nøjagtig det samme i Google Sheets som i Excel:
