Denne opplæringen viser hvordan du brukerEksklel COUNTIF og COUNTIFS Functioner i Excel for å telle data som oppfyller visse kriterier.
COUNTIF Funksjonsoversikt
Du kan bruke COUNTIF -funksjonen i Excel til å telle celler som inneholder en bestemt verdi, telle celler som er større enn eller lik en verdi, etc.
(Legg merke til hvordan formelinngangene vises)
COUNTIF Funksjonssyntaks og argumenter:
= COUNTIF (område, kriterier)
område - Rekkevidden av celler å telle.
kriterier - Kriteriene som styrer hvilke celler som skal telles.
Hva er COUNTIF -funksjonen?
COUNTIF -funksjonen er en av de eldre funksjonene som brukes i regneark. Enkelt sagt er det flott å skanne et område og fortelle deg hvor mange av cellene som oppfyller den betingelsen. Vi skal se på hvordan funksjonen fungerer med tekst, tall og datoer; samt noen av de andre situasjonene som kan oppstå.
Grunnleggende eksempel
La oss starte med å se på denne listen over tilfeldige elementer. Vi har noen tall, tomme celler og noen tekststrenger.
Hvis du ville vite hvor mange elementer en eksakt samsvar med kriteriene er, kan du angi hva du vil se etter som det andre argumentet. Et eksempel på denne formelen kan se ut
= TELLING (A2: A9, "Apple")
Denne formelen vil returnere tallet 3, siden det er 3 celler i vårt område som oppfyller kriteriene. Alternativt kan vi bruke en cellereferanse i stedet for å kode en verdi hardt. Hvis vi skrev "Apple" i celle G2, kunne vi endre formelen til
= TELLING (A2: A9, G2)
Når du arbeider med tall, er det viktig å skille mellom tall og tall som er lagret som tekst. Vanligvis setter du ikke anførselstegn rundt tall når du skriver formler. Så, for å skrive en formel som kontrollerer for tallet 5, ville du skrive
= TELLING (A2: A9, 5)
Til slutt kan vi også se etter tomme celler ved å bruke en nulllengde streng. Vi ville skrive den formelen som
= TELLING (A2: A9, "")
Merk: Denne formelen vil telle både celler som virkelig er tomme, så vel som de som er tomme som et resultat av en formel, som en IF -funksjon.
Delvise kamper
TELLING -funksjonen støtter bruk av jokertegn, "*" eller "?", I kriteriene. La oss se på denne listen over smakfulle bakervarer:
For å finne alle elementene som starter med Apple, kan vi skrive "Apple*". Så, for å få et svar på 3, er formelen vår i D2
= TELLING (A2: A5, "Apple*")
Merk: TELLING-funksjonen er ikke skift mellom store og små bokstaver, så du kan også skrive "eple*" hvis du vil.
Tilbake til våre bakevarer, kan det også være lurt å finne ut hvor mange paier vi har på listen. Vi kan finne det ved å plassere jokertegnet i begynnelsen av søkeordet, og skrive
= TELLING (A2: A5, "*paj")
Denne formelen gir resultatet av 2.
Vi kan også bruke jokertegn for å se etter celler med tekst. La oss gå tilbake til vår opprinnelige dataliste.
For å telle antall celler som har minst tekst, og dermed ikke telle tall eller tom celle, kan vi skrive
= TELLING (A2: A9, "*")
Du kan se at formelen vår gir et korrekt resultat av 4.
Sammenligningsoperatorer i COUNTIF
Når vi skrev kriteriene så langt, har vi antydet at sammenligningsoperatoren vår er "=". Faktisk kunne vi ha skrevet dette:
= TELLING (A2: A9, "= Apple")
Det er imidlertid et ekstra tegn å skrive ut, så det blir vanligvis utelatt. Dette betyr imidlertid at du kan bruke de andre operatørene, for eksempel større enn, mindre enn eller ikke lik. La oss se på denne listen over registrerte aldre:
Hvis vi ønsket å vite hvor mange barn som er minst 5 år gamle, kan vi skrive ut en "større enn eller lik" sammenligning slik:
= TELLING (A2: A8, "> = 5")
Merk: Sammenligningsoperatoren er alltid gitt som en tekststreng, og må derfor stå innenfor anførselstegn.
På samme måte kan du også se etter varer som er mindre enn en gitt verdi. Hvis vi trenger å finne ut hvor mange som er under 8, kan vi skrive ut
= TELLING (A2: A8, "<8")
Dette gir oss ønsket resultat av 5. La oss nå forestille oss at alle de 6 år gamle barna skal på en utflukt. Hvor mange barn blir det igjen? Vi kan finne ut dette ved å bruke en "ikke lik" sammenligning som denne:
= TELLING (A2: A8, "6")
Nå kan vi raskt se at vi har 6 barn som ikke er 6 år.
I disse sammenligningseksemplene så langt har vi hardt kodet verdiene vi ønsket. Du kan også bruke en cellereferanse. Trikset er at du må sammenkoble sammenligningsoperatoren med cellereferansen. La oss si at vi setter tallet 7 i celle C2, og vi vil at formelen vår i D2 skal vise hvor mange barn som er mindre enn 7 år.
Formelen vår i D2 må se slik ut:
= TELLING (A2: A8, "<" og C2)
Merk: Vær spesielt oppmerksom når du skriver disse formlene til om du må sette et element innenfor anførselstegn eller utenfor. Operatørene er alltid inne i sitater, cellereferanser er alltid utenfor sitater. Tall er utenfor hvis du gjør en eksakt match, men inne hvis du gjør en sammenligningsoperator.
Jobber med datoer
Vi har sett hvordan du kan gi en tekst eller et nummer som et kriterium, men hva med når vi må jobbe med datoer? Her er en rask eksempelliste vi kan jobbe med:
For å telle hvor mange datoer som er etter 4. mai, må vi være litt forsiktige. Datamaskiner lagrer datoer som tall, så vi må sørge for at datamaskinen bruker riktig nummer. Hvis vi skrev denne formelen, ville vi få det riktige resultatet?
= TELLING (A2: A9, "
Svaret er "muligens". Fordi vi utelot året fra våre kriterier, vil datamaskinen anta at vi mener inneværende år. Hvis alle datoene vi jobber med er for inneværende år, får vi det riktige svaret. Hvis det imidlertid er noen datoer i fremtiden, får vi feil svar. Når det neste året begynner, vil denne formelen også gi et annet resultat. Som sådan bør denne syntaksen sannsynligvis unngås.
Fordi det kan være vanskelig å skrive datoer riktig i en formel, er det best å skrive datoen du vil bruke i en celle, og deretter kan du bruke den cellereferansen i COUNTIF -formelen. Så la oss skrive datoen 7. mai-2020 i celle C2, og så kan vi sette formelen vår i C4.
Formelen i C4 er
= TELLING (A2: A9, "<" og C2)
Nå vet vi at resultatet av 7 er riktig, og svaret kommer ikke til å endre seg uventet hvis vi åpner dette regnearket en gang i fremtiden.
Før vi forlater denne delen, er det vanlig å bruke TODAY -funksjonen når du arbeider med datoer. Vi kan bruke det akkurat som vi ville gjort med en cellereferanse. For eksempel kan vi endre den forrige formelen til å være denne:
= TELLING (A2: A9, "<" & I DAG ())
Nå vil formelen vår fortsatt oppdateres etter hvert som sanntid utvikler seg, og vi vil ha et antall varer som er færre enn i dag.
Flere kriterier og COUNTIFS
Den opprinnelige COUNTIF -funksjonen ble forbedret i 2007 da COUNTIFS kom ut. Syntaksen mellom de to er veldig lik, og sistnevnte lar deg gi flere områder og kriterier. Du kan enkelt bruke COUNTIFS i enhver situasjon som COUNTIF eksisterer. Det er bare en god idé å vite at begge funksjonene eksisterer.
La oss se på denne datatabellen:
For å finne ut hvor mange som er i lønnivå 1 til 2, kan du skrive en summering av COUNTIF -funksjoner som dette:
= TELLING (B2: B7, "> = 1")-TELLING (B2: B7, "> 2")
Denne formelen vil fungere, ettersom du finner alt som er over 1, men deretter trekker fra antall poster som er utenfor grensepunktet ditt. Alternativt kan du bruke COUNTIFS slik:
= TELLINGER (B2: B7, "> = 1", B2: B7, "<= 2")
Sistnevnte er mer intuitiv å lese, så du vil kanskje bruke den ruten. COUNTIFS er også kraftigere når du må vurdere flere kolonner. La oss si at vi vil vite hvor mange som er i ledelsen og på lønnivå 1. Du kan ikke gjøre det med bare en TELLING; du må skrive ut
= TELLER (A2: A7, "Management", B2: B7, 1)
Denne formelen vil gi deg det riktige resultatet av 2. Før vi forlater denne delen, la oss vurdere en Or -type logikk. Hva om vi ville finne ut hvor mange som er i ledelsen eller? Du må legge til noen COUNTIFS sammen, men det er to måter å gjøre dette på. Den enkleste måten er å skrive det slik:
= COUNTIF (A2: A7, "HR")+COUNTIF (A2: A7, "Management")
Du kan også bruke en matrise og skrive denne matriseformelen:
= SUMME (TELLING (A2: A7, {"HR", "Management"}))
Merk: Arrayformler må bekreftes med `Ctrl+Shift+Enter`, ikke bare` Enter`.
Hvordan denne formelen vil fungere er at den vil se at du har gitt en matrise som input. Det vil dermed beregne resultatet til to forskjellige COUNTIF -funksjoner og lagre dem i en matrise. SUM -funksjonen vil deretter legge til alle resultatene i matrisen vår for å lage en enkelt utgang. Dermed vil formelen vår bli vurdert slik:
= SUMME (COUNTIF (A2: A7, {"HR", "Management"})) = SUM ({2, 3}) = 5
Tell unike verdier
Nå som vi har sett hvordan vi bruker en matrise med COUNTIF -funksjonen, kan vi ta det et skritt videre for å hjelpe oss med å telle hvor mange unike verdier som er i et område. La oss først se på listen over avdelinger igjen.
= SUMME (1/TELLING (A2: A7, A2: A7))
Vi kan se at det er 6 celler verdt med data, men det er bare 3 forskjellige elementer. For å få regnestykket til å trene, trenger vi hvert element til å være verdt 1/N, hvor N er antall ganger et element gjentas. For eksempel, hvis hver HR bare var verdt 1/2, så når du la dem sammen, ville du få et tall på 1, for 1 unik verdi.
Tilbake til vårt COUNTIF, som er designet for å finne ut hvor mange ganger et element vises i et område. I D2 skriver vi matriseformelen
= SUMME (1/TELLING (A2: A7, A2: A7))
Hvordan denne formelen vil fungere, er for hver celle i området A2: A7, den vil sjekke for å se hvor mange ganger den vises. Med vårt utvalg kommer dette til å produsere en rekke
{2, 2, 3, 3, 3, 1}
Deretter gjør vi alle disse tallene til brøk ved å gjøre noen divisjon. Nå ser utvalget vårt ut
{1/2, 1/2, 1/3, 1/3, 1/3, 1/1}
Når vi legger alt sammen, får vi ønsket resultat på 3.
Countif med to eller flere betingelser - Countifs -funksjonen
Så langt har vi bare jobbet med COUNTIF -funksjonen. COUNTIF -funksjonen kan bare håndtere ett kriterium om gangen. For å COUNTIF med flere kriterier må du bruke COUNTIFS -funksjonen. COUNTIFS oppfører seg akkurat som COUNTIF. Du legger bare til ekstra kriterier. La oss se på eksemplet nedenfor.
= TELLER (B2: B7, "= 130")
COUNTIF og COUNTIFS i Google Regneark
COUNTIF & COUNTIFS -funksjonen fungerer nøyaktig det samme i Google Regneark som i Excel:
