Excel har mange funksjoner der en bruker må angi ett eller flere kriterier for å få resultatet. Hvis du for eksempel vil telle celler basert på flere kriterier, kan du bruke COUNTIF- eller COUNTIFS -funksjonene i Excel.
Denne opplæringen dekker forskjellige måter å bruke ett eller flere kriterier i COUNTIF og COUNTIFS -funksjonen i Excel.
Selv om jeg først og fremst vil fokusere på COUNTIF og COUNTIFS -funksjoner i denne opplæringen, kan alle disse eksemplene også brukes i andre Excel -funksjoner som tar flere kriterier som innganger (for eksempel SUMIF, SUMIFS, AVERAGEIF og AVERAGEIFS).
En introduksjon til Excel COUNTIF og COUNTIFS -funksjoner
La oss først få grep om å bruke COUNTIF- og COUNTIFS -funksjoner i Excel.
Excel COUNTIF -funksjon (tar enkeltkriterier)
Excel COUNTIF -funksjonen er best egnet for situasjoner der du vil telle celler basert på et enkelt kriterium. Hvis du vil telle basert på flere kriterier, kan du bruke COUNTIFS -funksjonen.
Syntaks
= COUNTIF (område, kriterier)
Inngangsargumenter
- område - celleområdet du vil telle.
- kriterier - kriteriene som må evalueres opp mot celleområdet for at en celle skal telles.
Excel COUNTIFS -funksjon (tar flere kriterier)
Excel COUNTIFS -funksjonen er best egnet for situasjoner der du vil telle celler basert på flere kriterier.
Syntaks
= COUNTIFS (criteria_range1, criteria1, [criteria_range2, criteria2]…)
Inngangsargumenter
- kriterier_område1 - Celleområdet du vil evaluere mot kriterier1.
- kriterier 1 - kriteriene du vil evaluere for criteria_range1 for å bestemme hvilke celler som skal telles.
- [kriterier_område2] - Celleområdet du vil evaluere mot kriterier2.
- [kriterier2] - kriteriene du vil evaluere for criteria_range2 for å bestemme hvilke celler som skal telles.
La oss se på noen eksempler på bruk av flere kriterier i COUNTIF -funksjoner i Excel.
Bruke NUMBER kriterier i Excel COUNTIF -funksjoner
#1 Telle celler når kriterier er lik med en verdi
For å få antallet celler der kriterie -argumentet er lik en spesifisert verdi, kan du enten angi kriteriene direkte eller bruke cellereferansen som inneholder kriteriene.
Nedenfor er et eksempel hvor vi teller cellene som inneholder tallet 9 (som betyr at kriterie -argumentet er lik 9). Her er formelen:
= TELLING ($ B $ 2: $ B $ 11, D3)
I eksemplet ovenfor (på bildet) er kriteriene i celle D3. Du kan også skrive inn kriteriene direkte i formelen. For eksempel kan du også bruke:
= TELLING ($ B $ 2: $ B $ 11,9)
#2 Telle celler når kriterier er større enn en verdi
For å få tellingen av celler med en verdi som er større enn en spesifisert verdi, bruker vi operatøren større enn (">"). Vi kan enten bruke den direkte i formelen eller bruke en cellereferanse som har kriteriene.
Når vi bruker en operator i kriterier i Excel, må vi sette den i doble anførselstegn. For eksempel, hvis kriteriene er større enn 10, må vi skrive inn "> 10" som kriterier (se bilde nedenfor):
Her er formelen:
= TELLING ($ B $ 2: $ B $ 11, ”> 10 ″)
Du kan også ha kriteriene i en celle og bruke cellereferansen som kriterier. I dette tilfellet trenger du IKKE sette kriteriene i doble anførselstegn:
= TELLING ($ B $ 2: $ B $ 11, D3)
Det kan også være et tilfelle når du vil at kriteriene skal være i en celle, men ikke vil ha det med operatøren. For eksempel vil du kanskje at cellen D3 skal ha tallet 10 og ikke> 10.
I så fall må du opprette et kriterie -argument som er en kombinasjon av operator og cellereferanse (se bilde nedenfor):
= TELLING ($ B $ 2: $ B $ 11, ”>” og D3)
MERK: Når du kombinerer en operator og en cellereferanse, er operatøren alltid i doble anførselstegn. Operatøren og cellereferansen er forbundet med et ampersand (&).
#3 Telle celler når kriterier er mindre enn en verdi
For å få tellingen av celler med en verdi mindre enn en spesifisert verdi, bruker vi operatøren mindre enn (“<“). Vi kan enten bruke den direkte i formelen eller bruke en cellereferanse som har kriteriene.
Når vi bruker en operator i kriterier i Excel, må vi sette den i doble anførselstegn. For eksempel, hvis kriteriet er at tallet skal være mindre enn 5, må vi angi "<5" som kriterier (se bilde nedenfor):
= TELLING ($ B $ 2: $ B $ 11, ”<5 ″)
Du kan også ha kriteriene i en celle og bruke cellereferansen som kriterier. I dette tilfellet trenger du IKKE å sette kriteriene i doble anførselstegn (se bildet nedenfor):
= TELLING ($ B $ 2: $ B $ 11, D3)
Det kan også være et tilfelle når du vil at kriteriene skal være i en celle, men ikke vil ha det med operatøren. For eksempel vil du kanskje at cellen D3 skal ha tallet 5 og ikke <5.
I så fall må du opprette et kriterie -argument som er en kombinasjon av operator og cellereferanse:
= TELLING ($ B $ 2: $ B $ 11, ”<“ & D3)
MERK: Når du kombinerer en operator og en cellereferanse, er operatoren alltid i doble anførselstegn. Operatøren og cellereferansen er forbundet med et ampersand (&).
#4 Telle celler med flere kriterier - mellom to verdier
For å få et verditall mellom to verdier, må vi bruke flere kriterier i funksjonen TELLING.
Her er to metoder for å gjøre dette:
METODE 1: Bruke COUNTIFS -funksjonen
COUNTIFS -funksjonen kan håndtere flere kriterier som argumenter og teller cellene bare når alle kriteriene er SANN. For å telle celler med verdier mellom to spesifiserte verdier (si 5 og 10), kan vi bruke følgende COUNTIFS -funksjon:
= TELLINGER ($ B $ 2: $ B $ 11, ”> 5 ″, $ B $ 2: $ B $ 11,” <10 ″)
MERK: Formelen ovenfor teller ikke celler som inneholder 5 eller 10. Hvis du vil inkludere disse cellene, bruker du større enn lik (> =) og mindre enn lik (<=) operatører. Her er formelen:
= TELLINGER ($ B $ 2: $ B $ 11, ”> = 5 ″, $ B $ 2: $ B $ 11,” <= 10 ″)
Du kan også ha disse kriteriene i celler og bruke cellereferansen som kriterier. I dette tilfellet trenger du IKKE å sette kriteriene i doble anførselstegn (se bildet nedenfor):
Du kan også bruke en kombinasjon av cellereferanser og operatorer (der operatøren er angitt direkte i formelen). Når du kombinerer en operator og en cellereferanse, er operatøren alltid i doble anførselstegn. Operatøren og cellereferansen er forbundet med et ampersand (&).
METODE 2: Bruke to COUNTIF -funksjoner
Hvis du har flere kriterier, kan du enten bruke COUNTIFS eller opprette en kombinasjon av COUNTIF -funksjoner. Formelen nedenfor vil også gjøre det samme:
= COUNTIF ($ B $ 2: $ B $ 11, ”> 5 ″)-COUNTIF ($ B $ 2: $ B $ 11,”> 10 ″)
I formelen ovenfor finner vi først antall celler som har en verdi større enn 5, og vi trekker fra antall celler med en verdi større enn 10. Dette vil gi oss resultatet som 5 (som er antall celler som har verdier mer enn 5 og mindre enn lik 10).
Hvis du vil at formelen skal inneholde både 5 og 10, bruker du følgende formel i stedet:
= COUNTIF ($ B $ 2: $ B $ 11, ”> = 5 ″)-COUNTIF ($ B $ 2: $ B $ 11,”> 10 ″)
Hvis du vil at formelen skal ekskludere både '5' og '10' fra tellingen, bruker du følgende formel:
= COUNTIF ($ B $ 2: $ B $ 11, ”> = 5 ″)-COUNTIF ($ B $ 2: $ B $ 11,”> 10 ″)-COUNTIF ($ B $ 2: $ B $ 11,10)
Du kan ha disse kriteriene i celler og bruke cellereferansene, eller du kan bruke en kombinasjon av operatorer og cellereferanser.
Bruke TEKST -kriterier i Excel -funksjoner
#1 Telle celler når kriterier er like med en spesifisert tekst
For å telle celler som inneholder en eksakt samsvar med den angitte teksten, kan vi ganske enkelt bruke den teksten som kriterier. For eksempel, i datasettet (vist nedenfor på bildet), hvis jeg vil telle alle cellene med navnet Joe i, kan jeg bruke formelen nedenfor:
= TELLING ($ B $ 2: $ B $ 11, "Joe")
Siden dette er en tekststreng, må jeg sette tekstkriteriene i doble anførselstegn.
Du kan også ha kriteriene i en celle og deretter bruke den cellereferansen (som vist nedenfor):
= TELLING ($ B $ 2: $ B $ 11, E3)
MERK: Du kan få feil resultater hvis det er ledende/etterfølgende mellomrom i kriteriene eller kriteriene. Sørg for å rense dataene før du bruker disse formlene.
#2 Telle celler når kriterier IKKE er lik en spesifisert tekst
I likhet med det vi så i eksemplet ovenfor, kan du også telle celler som ikke inneholder en spesifisert tekst. For å gjøre dette må vi bruke ikke -lik operatøren ().
Anta at du vil telle alle cellene som ikke inneholder navnet JOE, her er formelen som vil gjøre det:
= TELLING ($ B $ 2: $ B $ 11, "Joe")
Du kan også ha kriteriene i en celle og bruke cellereferansen som kriterier. I dette tilfellet trenger du IKKE å sette kriteriene i doble anførselstegn (se bildet nedenfor):
= TELLING ($ B $ 2: $ B $ 11, E3)
Det kan også være et tilfelle når du vil at kriteriene skal være i en celle, men ikke vil ha det med operatøren. For eksempel vil du kanskje at cellen D3 skal ha navnet Joe og ikke Joe.
I så fall må du opprette et kriterie -argument som er en kombinasjon av operator og cellereferanse (se bilde nedenfor):
= TELLING ($ B $ 2: $ B $ 11, ”” og E3)
Når du kombinerer en operator og en cellereferanse, er operatøren alltid i doble anførselstegn. Operatøren og cellereferansen er forbundet med et ampersand (&).
Bruke DATE -kriterier i Excel COUNTIF og COUNTIFS -funksjoner
Dato og klokkeslett i Excel lagres som tall. Så vi kan bruke det på samme måte som vi bruker tall.
#1 Telle celler når kriterier er like til en spesifisert dato
For å få tellingen av celler som inneholder den angitte datoen, bruker vi lik operatøren (=) sammen med datoen.
For å bruke datoen anbefaler jeg å bruke DATE -funksjonen, da den blir kvitt enhver mulighet for feil i datoverdien. Så, for eksempel, hvis jeg vil bruke datoen 1. september 2015, kan jeg bruke DATE -funksjonen som vist nedenfor:
= DATO (2015,9,1)
Denne formelen vil returnere samme dato til tross for regionale forskjeller. For eksempel vil 01-09-2015 være 1. september 2015 i henhold til datosyntaksen i USA og 09. januar 2015 i henhold til datosyntaksen i Storbritannia. Denne formelen vil imidlertid alltid returnere 1. september 2105.
Her er formelen for å telle antall celler som inneholder datoen 02-09-2015:
= TELLING ($ A $ 2: $ A $ 11, DATO (2015,9,2))
#2 Telle celler når kriterier er FØR eller ETTER til en spesifisert dato
For å telle celler som inneholder dato før eller etter en spesifisert dato, kan vi bruke operatørene mindre enn/større enn.
For eksempel, hvis jeg vil telle alle cellene som inneholder en dato som er etter 02. september 2015, kan jeg bruke formelen:
= TELLING ($ A $ 2: $ A $ 11, ”>” & DATO (2015,9,2))
På samme måte kan du også telle antall celler før en bestemt dato. Hvis du vil inkludere en dato i tellingen, bruker du og 'lik med' operatoren sammen med 'større enn/mindre enn' operatoren.
Du kan også bruke en cellereferanse som inneholder en dato. I dette tilfellet må du kombinere operatøren (innen doble anførselstegn) med datoen ved hjelp av et ampersand (&).
Se eksempel nedenfor:
= TELLING ($ A $ 2: $ A $ 11, ”>” og F3)
#3 Telle celler med flere kriterier - mellom to datoer
For å få et verditall mellom to verdier, må vi bruke flere kriterier i funksjonen TELLING.
Vi kan gjøre dette ved å bruke to metoder - En enkelt COUNTIFS -funksjon eller to COUNTIF -funksjoner.
METODE 1: Bruke COUNTIFS -funksjonen
COUNTIFS -funksjonen kan ta flere kriterier som argumentene og teller cellene bare når alle kriteriene er Sanne. For å telle celler med verdier mellom to spesifiserte datoer (si 2. september og 7. september), kan vi bruke følgende COUNTIFS -funksjon:
= TELLINGER ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,2), $ A $ 2: $ A $ 11, ”<“ & DATE (2015,9,7))
Formelen ovenfor teller ikke celler som inneholder de angitte datoene. Hvis du også vil inkludere disse datoene, bruker du større enn lik (> =) og mindre enn lik (<=) operatører. Her er formelen:
= COUNTIFS ($ A $ 2: $ A $ 11, ”> =” & DATE (2015,9,2), $ A $ 2: $ A $ 11, ”<=” & DATE (2015,9,7))
Du kan også ha datoene i en celle og bruke cellereferansen som kriterier. I dette tilfellet kan du ikke ha operatøren med datoen i cellene. Du må manuelt legge til operatører i formelen (i doble anførselstegn) og legge til cellereferanse ved hjelp av et ampersand (&). Se bildet nedenfor:
= TELLER ($ A $ 2: $ A $ 11, ”>” & F3, $ A $ 2: $ A $ 11, ”<“ & G3)
METODE 2: Bruke COUNTIF -funksjoner
Hvis du har flere kriterier, kan du enten bruke én COUNTIFS -funksjon eller opprette en kombinasjon av to COUNTIF -funksjoner. Formelen nedenfor vil også gjøre susen:
= COUNTIF ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,2))-COUNTIF ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,7))
I formelen ovenfor finner vi først antall celler som har en dato etter 2. september, og vi trekker fra antall celler med datoer etter 7. september. Dette vil gi oss resultatet som 7 (som er antall celler som har datoer etter 2. september og før eller 7. september).
Hvis du ikke vil at formelen skal telle både 2. september og 7. september, bruker du 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 datoene fra å telle, bruker du 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å ha kriteriedatoene i celler og bruke cellereferansene (sammen med operatører i doble anførselstegn sammen med ampersand).
Bruke WILDCARD -TEGN i kriterier i COUNTIF & COUNTIFS -funksjoner
Det er tre jokertegn i Excel:
- * (stjerne) - Det representerer et hvilket som helst antall tegn. Eks* kan for eksempel bety excel, utmerker seg, eksempel, ekspert, etc.
- ? (spørsmålstegn) - Det representerer en enkelt karakter. For eksempel kan Tr? Mp bety Trump eller Tramp.
- ~ (tilde) - Den brukes til å identifisere et jokertegn (~, *,?) I teksten.
Du kan bruke COUNTIF -funksjonen med jokertegn for å telle celler når en annen innebygd tellefunksjon mislykkes. Anta for eksempel at du har et datasett som vist nedenfor:
La oss nå ta forskjellige eksempler:
#1 Tell celler som inneholder tekst
For å telle celler med tekst i, kan vi bruke jokertegnet * (stjerne). Siden stjernen representerer et hvilket som helst antall tegn, vil den telle alle cellene som har tekst i den. Her er formelen:
= COUNTIFS ($ C $ 2: $ C $ 11, ”*”)
Merk: Formelen ovenfor ignorerer celler som inneholder tall, tomme celler og logiske verdier, men vil telle cellene som inneholder en apostrof (og dermed vises tomme) eller celler som inneholder tom streng (= ””) som kan ha blitt returnert som en del av en formel.
Her er en detaljert opplæring om håndtering av saker der det er en tom streng eller apostrof.
Her er en detaljert opplæring om håndtering av saker der det er tomme strenger eller apostrofer.
Nedenfor er en video som forklarer forskjellige scenarier for å telle celler med tekst i.
#2 Count Ikke-tomme celler
Hvis du tenker på å bruke COUNTA -funksjonen, kan du tenke om igjen.
Prøv det, og du kan mislykkes. COUNTA vil også telle en celle som inneholder en tom streng (ofte returnert med formler som = ”” eller når folk bare skriver inn en apostrof i en celle). Celler som inneholder tomme strenger ser tomme ut, men er ikke, og telles dermed av COUNTA -funksjonen.
COUNTA vil også telle en celle som inneholder en tom streng (ofte returnert med formler som = ”” eller når folk bare skriver inn en apostrof i en celle). Celler som inneholder tomme strenger ser tomme ut, men er ikke, og telles dermed av COUNTA -funksjonen.
Så hvis du bruker formelen = COUNTA (A1: A11), returnerer den 11, mens den skal returnere 10.
Her er reparasjonen:
= COUNTIF ($ A $ 1: $ A $ 11, ”?*”)+COUNT ($ A $ 1: $ A $ 11)+SUMPRODUCT (-ISLOGISK ($ A $ 1: $ A $ 11))
La oss forstå denne formelen ved å bryte den ned:
- COUNTIF ($ N $ 8: $ N $ 18, ”?*”) - Denne delen av formelen returnerer 5. Dette inkluderer alle celler som har et teksttegn i seg. A? representerer ett tegn og * representerer et hvilket som helst antall tegn. Derfor tvinger kombinasjonen?* I kriteriene til å telle celler som har minst ett teksttegn i den.
- COUNT ($ A $ 1: $ A $ 11) - Dette teller alle cellene som inneholder tall. I eksemplet ovenfor returnerer det 3.
- SUMPRODUCT (-ISLOGISK ($ A $ 1: $ A $ 11) - Dette teller alle cellene som inneholder logiske verdier. I eksemplet ovenfor returnerer det 2.
#3 Tell celler som inneholder spesifikk tekst
La oss si at vi vil telle alle cellene der selgernavnet begynner med J. Dette kan enkelt oppnås ved å bruke et jokertegn i TELLING -funksjonen. Her er formelen:
= TELLER ($ C $ 2: $ C $ 11, "J*")
Kriteriene J* angir at teksten i en celle skal begynne med J og kan inneholde et hvilket som helst antall tegn.
Hvis du vil telle celler som inneholder alfabetet hvor som helst i teksten, flank det med en stjerne på begge sider. For eksempel, hvis du vil telle celler som inneholder alfabetet "a" i den, bruk * a * som kriterier.
Denne artikkelen er uvanlig lang i forhold til mine andre artikler. Håper du likte det. Gi meg beskjed om tankene dine ved å legge igjen en kommentar.
Du kan også finne følgende Excel -opplæringsprogrammer nyttige:
- Telle antall ord i Excel.
- Telle celler basert på bakgrunnsfarge i Excel.
- Slik summerer du en kolonne i Excel (5 virkelig enkle måter)
