Excel har mange funksjoner der brukeren trenger for å spesifisere en enkelt eller flere kriterier for å få resultatet. For eksempel, hvis du ønsker å telle celler basert på flere kriterier, kan du bruke COUNTIF eller COUNTIFS funksjoner i Excel.
Denne opplæringen dekker ulike måter ved hjelp av et enkelt eller flere kriterier i COUNTIF og COUNTIFS funksjon i Excel.,
Mens jeg først og fremst vil være å fokusere på COUNTIF og COUNTIFS funksjoner i denne opplæringen, alle disse eksemplene kan også brukes i andre Excel-funksjoner som tar flere kriterier, som innganger (for eksempel SUMIF, SUMIFS, AVERAGEIF, og AVERAGEIFS).
Denne Opplæringen Dekker:
En Introduksjon til Excel COUNTIF og COUNTIFS Funksjoner
La oss først få et grep om hvordan du bruker COUNTIF og COUNTIFS funksjoner i Excel.,
Excel COUNTIF Funksjon (finner Enkelt Kriterier)
Excel COUNTIF funksjon er best egnet for situasjoner når du ønsker å telle celler basert på et enkelt kriterium. Hvis du ønsker å telle basert på flere kriterier, bruk COUNTIFS funksjon.
Syntaks
=COUNTIF(utvalg, kriterier)
inndataargumenter
- spekter – området med celler som du vil telle.
- kriterier – kriterier som må vurderes opp mot omfanget av celler for en celle å bli regnet med.,
Excel COUNTIFS Funksjon (tar Flere Kriterier)
Excel COUNTIFS funksjon er best egnet for situasjoner når du ønsker å telle celler basert på flere kriterier.
Syntaks
=COUNTIFS(criteria_range1, criteria1, …)
inndataargumenter
- criteria_range1 – celleområdet som du ønsker å evaluere mot criteria1.
- criteria1 – kriteriene som du ønsker å evaluere for criteria_range1 å finne ut hvilke celler til å telle.
- – celleområdet som du ønsker å evaluere mot criteria2.,
- – hvilke kriterier som du ønsker å evaluere for criteria_range2 å finne ut hvilke celler til å telle.
la oss Nå se på noen eksempler på bruk av flere kriterier i COUNTIF funksjoner i Excel.
ved Hjelp av ANTALL Kriterier i Excel COUNTIF Funksjoner
#1 Telle Celler når Kriteriene er LIK en Verdi
for Å få antall celler der criteria er lik en bestemt verdi, kan du enten direkte inn i kriteriene, eller bruk cellereferanse som inneholder vilkåret.,
Nedenfor er et eksempel hvor vi teller celler som inneholder tall 9 (noe som betyr at criteria er lik 9). Her er formelen:
=COUNTIF($B$2:$B$11,D3)
I eksempelet over (i pic), og kriteriene er i celle D3. Du kan også angi kriteriene direkte inn i formelen., For eksempel, du kan også bruke:
=COUNTIF($B$2:$B$11,9)
#2 Telle Celler når Kriteriene er STØRRE ENN Verdien
for Å få antall celler med en verdi som er større enn en bestemt verdi, vil vi bruke større enn operatøren («>»). Vi kan enten bruke den direkte i formelen eller bruke en cellereferanse som har kriterier.
Når vi bruker en operatør i kriteriene i Excel, vi trenger for å sette det i anførselstegn., For eksempel, hvis kriteriene er større enn 10, så vi trenger å skrive «>10» som kriteriene (se bilde under):
Her er formelen:
=COUNTIF($B$2:$B$11,»>10″)
Du kan også ha kriterier i en celle og bruk cellereferanse som kriteriene. I dette tilfellet, du trenger IKKE sette kriterier i doble anførselstegn:
=COUNTIF($B$2:$B$11,D3)
Det kan også være en sak når du vil kriteriene for å være i en celle, men ikke vil ha den med operatøren., Du kan For eksempel vil den celle D3 å ha nummer 10 og ikke >10.
I tilfelle du trenger for å lage en criteria som er en kombinasjon av operatør og cellereferanse (se bilde under):
=COUNTIF($B$2:$B$11,»>»&D3)
MERK: Når du kombinerer en operator og en cellereferanse, operatør er alltid i doble anførselstegn. Operatøren og cellereferanse er sammen med en ligatur (&).,
#3 Telle Celler når Kriteriene er MINDRE ENN Verdien
for Å få antall celler med en verdi som er mindre enn en bestemt verdi, vil vi bruke mindre enn operatøren («<«). Vi kan enten bruke den direkte i formelen eller bruke en cellereferanse som har kriterier.
Når vi bruker en operatør i kriteriene i Excel, vi trenger for å sette det i anførselstegn., For eksempel, hvis kriteriet er at antallet skal være mindre enn 5, så vi trenger å skrive «<5» som kriteriene (se bilde under):
=COUNTIF($B$2:$B$11,»<5″)
Du kan også ha kriterier i en celle og bruk cellereferanse som kriteriene., I dette tilfellet, du trenger IKKE sette kriterier i doble anførselstegn (se bilde under):
=COUNTIF($B$2:$B$11,D3)
Også, det kan være en sak når du vil kriteriene for å være i en celle, men ikke vil ha den med operatøren. Du kan For eksempel vil den celle D3 å ha nummer 5 og ikke <5.,
I tilfelle du trenger for å lage en criteria som er en kombinasjon av operatør og cellereferanse:
=COUNTIF($B$2:$B$11,»<«&D3)
MERK: Når du kombinerer en operator og en cellereferanse, operatør er alltid i doble anførselstegn. Operatøren og cellereferanse er sammen med en ligatur (&).,
#4 Telle Celler med Flere Kriterier – Mellom To Verdier
for Å få en oversikt over verdiene mellom to verdier, må vi bruke flere kriterier i COUNTIF funksjon.
Her er to metoder å gjøre dette på:
METODE 1: Bruke COUNTIFS funksjon
COUNTIFS funksjonen kan håndtere flere kriterier som argumenter og teller celler bare når alle kriterier er OPPFYLT., Å telle celler med verdier mellom to gitte verdier (si 5 og 10), kan vi bruke følgende COUNTIFS funksjon:
=COUNTIFS($B$2:$B$11,»>5″,$B$2:$B$11,»<10″)
MERK: ovennevnte formel ikke teller celler som inneholder 5 eller 10. Hvis du vil inkludere disse celler, kan du bruke større enn lik (>=) og mindre enn lik (<=) operatører., Her er formelen:
=COUNTIFS($B$2:$B$11,»>=5″,$B$2:$B$11,»<=10″)
Du kan også ha disse kriteriene i celler og bruke cellereferanse som kriteriene. I dette tilfellet, du trenger IKKE sette kriterier i doble anførselstegn (se bilde under):
Du kan også bruke en kombinasjon av celler referanser og operatører (hvor operatøren er ført direkte i formelen). Når du kombinerer en operator og en cellereferanse, operatør er alltid i doble anførselstegn., Operatøren og cellereferanse er sammen med en ligatur (&).
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 den ovennevnte formel, vi først finne antall celler som har en verdi som er større enn 5, og vi trekker fra antall celler med en verdi som er større enn 10. Dette ville gi oss et resultat som 5 (som er antall celler som har verdier mer enn 5 og mindre enn tilsvarende 10).,3c219a21b4″>
Hvis du vil formelen for å inkludere både 5 og 10, kan du bruke følgende formel i stedet:
=COUNTIF($B$2:$B$11,»>=5″)-COUNTIF($B$2:$B$11,»>10″)
Hvis du vil formelen for å ekskludere både ‘5’ og ’10’ fra telling, bruke 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 cellene referanser, eller du kan bruke en kombinasjon av operatører og celler referanser.,
ved Hjelp av TEKST Kriterier i Excel-Funksjoner
#1 Telle Celler når Kriteriene er LIK en bestemt tekst
for Å telle celler som inneholder en eksakt match av den angitte teksten, vi kan bare bruke den teksten som kriteriene. For eksempel, i datasettet (vist nedenfor i bildet), hvis jeg ønsker å telle alle celler med navn Joe i det, kan jeg bruke formelen nedenfor:
=COUNTIF($B$2:$B$11,»Ole»)
Siden dette er en tekst-streng, jeg trenger å sette tekst kriterier i doble anførselstegn.,
Du kan også ha kriterier i en celle, og deretter bruke cellereferanse (som vist nedenfor):
=COUNTIF($B$2:$B$11,E3)
MERK: Du kan få feil resultat hvis det er fører/etterfølgende mellomrom i kriterier eller vilkårsområdet. Sørg for at du rengjør data før du bruker disse formlene.
#2 Telle Celler når Kriteriene IKKE er LIK en bestemt tekst
i likhet med hva vi så i eksempelet ovenfor, kan du også teller celler som ikke inneholder en bestemt tekst., For å gjøre dette trenger vi å bruke ikke lik for å operatøren (<>).
Tenk deg at du ønsker å telle alle cellene som inneholder ikke navn JOE, her er formelen som vil gjøre det:
=COUNTIF($B$2:$B$11,»<>Joe»)
Du kan også ha kriterier i en celle og bruk cellereferanse som kriteriene., I dette tilfellet, du trenger IKKE sette kriterier i doble anførselstegn (se bilde under):
=COUNTIF($B$2:$B$11,E3)
Det kan også være en sak når du vil kriteriene for å være i en celle, men ikke vil ha den med operatøren. Du kan For eksempel vil den celle D3 har navn som Joe og ikke <>Joe.,
I tilfelle du trenger for å lage en criteria som er en kombinasjon av operatør og cellereferanse (se bilde under):
=COUNTIF($B$2:$B$11,»<>»&E3)
Når du kombinerer en operator og en cellereferanse, operatør er alltid i doble anførselstegn. Operatøren og cellereferanse er sammen med en ligatur (&).
ved Hjelp av DATO-kriteriet i Excel COUNTIF og COUNTIFS Funksjoner
Excel lagre dato og klokkeslett som tall., Så vi kan bruke det på samme måte som vi bruker tall.
#1 Telle Celler når Kriteriene er LIK en bestemt Dato
for Å få det antall celler som inneholder den angitte dato, ville vi bruke lik operatøren (=) sammen med dato.
for Å bruke dato, anbefaler jeg at du bruker DATO-funksjonen, som det blir kvitt noen mulighet for feil i dato verdi. Så, for eksempel, hvis jeg ønsker å bruke dato-September 1, 2015, jeg kan bruke DATO-funksjonen, som vist nedenfor:
=DATO(2015,9,1)
Denne formelen vil gå tilbake samme dato til tross for regionale forskjeller., For eksempel, 01-09-2015 ville være 1. September 2015 i henhold til den AMERIKANSKE dato syntaks og januar 09, 2015 i henhold til STORBRITANNIA dato syntaks. Imidlertid, denne formelen vil alltid returnere September 1, 2105.
Her er formelen for å telle antall celler som inneholder dato 02-09-2015:
=COUNTIF($A$2:$A$11,DATO(2015,9,2))
#2 Telle Celler når Kriteriene er FØR eller ETTER en Angitt Dato
for Å telle celler som inneholder datoen før eller etter en bestemt dato, kan vi bruke mindre enn/større enn operatører.,
For eksempel, hvis jeg ønsker å telle alle cellene som inneholder en dato som er etter September 02, 2015, jeg kan bruke formelen:
=COUNTIF($A$2:$A$11,»>»&DATO(2015,9,2))
du kan også telle antall celler før en angitt dato. Hvis du vil inkludere en dato i å telle, bruk og » lik » – operatoren sammen med ‘større enn/mindre enn-operatør.
Du kan også bruke en cellereferanse som inneholder en dato., I dette tilfellet, du trenger for å kombinere operatøren (i anførselstegn) med dato ved hjelp av en ampersand (&).
Se eksempel nedenfor:
=COUNTIF($A$2:$A$11,»>»&F3)
#3 Telle Celler med Flere Kriterier – Mellom To Datoer
for Å få en oversikt over verdiene mellom to verdier, må vi bruke flere kriterier i COUNTIF funksjon.
Vi kan gjøre dette ved hjelp av to metoder – Ett COUNTIFS funksjon eller to COUNTIF funksjoner.,
METODE 1: Bruke COUNTIFS funksjon
COUNTIFS funksjonen kan ta flere kriterier som argumenter og teller celler bare når alle kriterier er OPPFYLT. Å telle celler med verdier mellom to angitte datoer (si September 2 og 7 September), kan vi bruke følgende COUNTIFS funksjon:
=COUNTIFS($A$2:$A$11,»>»&DATO(2015,9,2),$A$2:$A$11,»<«&DATO(2015,9,7))
Den ovennevnte formel ikke teller celler som inneholder det angitte datoer., Hvis du vil inkludere disse datoer som godt, bruk større enn lik (>=) og mindre enn lik (<=) operatører. Her er formelen:
=COUNTIFS($A$2:$A$11,»>=»&DATO(2015,9,2),$A$2:$A$11,»<=»&DATO(2015,9,7))
Du kan også få datoer i en celle og bruk cellereferanse som kriteriene. I dette tilfellet, kan du ikke ha operatør med dato i cellene., Du må manuelt legge til operatører i formel (i anførselstegn), og legg til cellereferanse ved hjelp av en ampersand (&). Se på bilde under:
=COUNTIFS($A$2:$A$11,»>»&F3,$A$2:$A$11,»<«&G3)
METODE 2: Bruke COUNTIF funksjoner
Hvis du har flere kriterier, kan du enten bruke en COUNTIFS funksjon eller opprette en kombinasjon av to COUNTIF funksjoner., Formelen nedenfor vil også gjøre susen:
=COUNTIF($A$2:$A$11,»>»&DATO(2015,9,2))-COUNTIF($A$2:$A$11,»>»&DATO(2015,9,7))
I den ovennevnte formel, må vi først finne antall celler som har en dato etter September 2, og vi trekker fra antall celler med datoer etter 7. September. Dette ville gi oss et resultat som 7 (som er antall celler som har datoer etter September 2 og på eller før 7. September).,>DATO(2015,9,2))-COUNTIF($A$2:$A$11,»>»&DATO(2015,9,7))
Hvis du ønsker å ekskludere både datoer fra telling, bruke følgende formel:
=COUNTIF($A$2:$A$11,»>»&DATO(2015,9,2))-COUNTIF($A$2:$A$11,»>»&DATO(2015,9,7)-COUNTIF($A$2:$A$11,DATO(2015,9,7)))
du kan Også ha kriterier datoer i celler og bruke cellene referanser (sammen med operatører i doble anførselstegn sluttet å bruke ampersand).,
Bruke JOKERTEGN i Kriteriene i COUNTIF & COUNTIFS Funksjoner
Det er tre jokertegn i Excel:
Du kan bruke COUNTIF funksjon med jokertegn til å telle celler når andre innebygde count-funksjonen mislykkes. Anta For eksempel at du har et datasett som vist nedenfor:
la oss Nå ta ulike eksempler:
#1 Teller Celler som inneholder Tekst
for Å telle celler med tekst i det, kan vi bruke jokertegnet * (stjerne)., Siden stjerne representerer et hvilket som helst antall tegn, det ville telle alle celler som har noen tekst i det. Her er formelen:
=COUNTIFS($C$2:$C$11,»*»)
Merk: formelen ovenfor ignorerer celler som inneholder tall, tomme celler, og logiske verdier, men ville telle celler inneholder en apostrof (og dermed vises tomme) eller cellene som inneholder en tom streng (=»») som kan ha blitt returnert som en del av en formel.
Her er en detaljert veiledning om behandling av saker der det er en tom streng eller apostrofer.,
Her er en detaljert veiledning om behandling av saker der det er tomme strenger eller apostrofer.
Nedenfor er en video som forklarer forskjellige scenarier for å telle celler med tekst i det.
#2 Teller Ikke-tomme Celler
Hvis du tenker på å bruke COUNTA funksjon, tro om igjen.
Prøv det og det kan svikte deg. COUNTA vil også telle en celle som inneholder en tom streng (ofte tilbake av formler som =»» eller når folk skriver du bare inn en apostrof i en celle). Celler som inneholder tomme strenger se blank, men ikke er det, og dermed telles ved COUNTA funksjon.,
COUNTA vil også telle en celle som inneholder en tom streng (ofte tilbake av formler som =»» eller når folk skriver du bare inn en apostrof i en celle). Celler som inneholder tomme strenger se blank, men ikke er det, og dermed telles ved COUNTA funksjon.
Så hvis du bruker formelen =COUNTA(A1:A11), returnerer 11, mens det skal gå 10.
Her er løsningen:
=COUNTIF($A$1:$A$11,»?*»)+COUNT($A$1:$A$11)+SUMPRODUCT(–ISLOGICAL($A$1:$A$11))
La oss forstå denne formelen ved å bryte det ned:
- COUNTIF($N$8:$N$18,»?,*») – Denne delen av formelen returnerer 5. Dette inkluderer en hvilken som helst celle som har et tegn i det. En ? representerer ett tegn, og * representerer et hvilket som helst antall tegn. Derfor, den kombinasjonen ?* i kriteriene tvinger excel til å telle celler som har minst ett tegn i det.
- COUNT($A$1:$A$11) – Dette teller alle celler som inneholder tall. I eksempelet ovenfor, returnerer 3.
- SUMPRODUCT(–ISLOGICAL($A$1:$A$11) – Dette teller alle cellene som inneholder logiske verdier. I eksempelet ovenfor, går den tilbake 2.,
#3 Teller Celler som inneholder bestemt tekst
La oss si at vi ønsker å telle alle cellene der sales rep navn begynner med J. Dette kan enkelt oppnås ved å bruke jokertegn i COUNTIF funksjon. Her er formelen:
=COUNTIFS($C$2:$C$11,»J*»)
kriteriene J* angir at teksten i en celle, bør du begynne med J og kan inneholde et hvilket som helst antall tegn.
Hvis du ønsker å telle celler som inneholder alfabetet hvor som helst i teksten, flanke det med en stjerne på begge sider., For eksempel, hvis du ønsker å telle celler som inneholder bokstavene «a», bruker *en* som kriteriene.