Kako uporabljati več pogojev v Excelu COUNTIF in COUNTIFS

Excel ima veliko funkcij, kjer mora uporabnik določiti enega ali več pogojev, da dobi rezultat.Na primer, če želite šteti celice na podlagi več meril, lahko uporabite funkcije COUNTIF ali COUNTIFS v Excelu.

Ta vadnica zajema različne načine uporabe enega ali več kriterijev v Excelovih funkcijah COUNTIF in COUNTIFS.

Medtem ko se bom v tej vadnici osredotočil predvsem na funkcije COUNTIF in COUNTIFS, je vse te primere mogoče uporabiti tudi z drugimi Excelovimi funkcijami, ki kot vhod vzamejo več meril (kot so SUMIF, SUMIFS, AVERAGEIF in AVERAGEIFS).

Uvod v funkcije Excel COUNTIF in COUNTIFS

Najprej razumemo, kako uporabljati funkcije COUNTIF in COUNTIFS v Excelu.

Excelova funkcija COUNTIF (z enim merilom)

Excelova funkcija COUNTIF deluje najbolje, če želite šteti celice na podlagi enega samega kriterija.Če želite šteti na podlagi več kriterijev, uporabite funkcijo COUNTIFS.

sintaksa

=COUNTIF(obseg, pogoj)

Vhodni parametri

• Obseg -  Obseg celic, ki ga želite izračunati.
• stanje -Merila, ki jih je treba ovrednotiti glede na obseg celic, ki jih je treba prešteti.

Excelova funkcija COUNTIFS (z več merili)

Excelova funkcija COUNTIFS deluje najbolje, če želite šteti celice na podlagi več meril.

sintaksa

=COUNTIFS(razpon_meril1,merila1,[obseg_meril2,merila2]…)

Vhodni parametri

• obseg_obsega1 – Obseg celic, ki jih želite oceniti glede na merilo 1.
• merila1 – Merilo, ki ga želite oceniti, je obseg_razreda1, da določite, katere celice je treba ovrednotiti.
• [razpon_meril2] – Obseg celic, ki jih želite oceniti glede na merilo 2.
• [merila2] – Merilo, ki ga želite oceniti, je merilo_razpon2, da določite, katere celice je treba šteti.

Zdaj pa si poglejmo nekaj primerov uporabe več kriterijev v Excelovi funkciji COUNTIF.

Uporabite pogoj ŠTEVILKA v Excelovi funkciji COUNTIF

#1 Preštejte celice, ko je kriterij enak vrednosti

Če želite dobiti število celic, pri katerih je parameter pogoja enak navedeni vrednosti, lahko pogoj vnesete neposredno ali uporabite sklic na celico, ki vsebuje pogoj.

Spodaj je primer, kjer štejemo celice, ki vsebujejo številko 9 (kar pomeni, da je parameter pogoja enak 9).Tukaj je formula:

=COUNTIF($B$2:$B$11,D3)

V zgornjem primeru (na sliki) je kriterij v celici D3.Pogoje lahko vnesete tudi neposredno v formule.Na primer, lahko uporabite tudi:

=COUNTIF($B$2:$B$11,9)

#2 Preštejte celice, ko je pogoj večji od vrednosti

Za pridobitev števila celic z vrednostjo, ki je večja od določene vrednosti, uporabimo operator večje od (">").Lahko ga uporabimo neposredno v formulah ali pa uporabimo reference celic s pogoji.

Kadar koli uporabimo operator v pogoju v Excelu, ga moramo postaviti v dvojne narekovaje.Na primer, če je pogoj večji od 10, potem moramo kot pogoj vnesti ">10" (glej sliko spodaj):

Tukaj je formula:

=COUNTIF($B$2:$B$11,”>10″)

V celice lahko vključite tudi pogoje in kot pogoje uporabite reference celic.V tem primeru vam pogoja ni treba postaviti v dvojne narekovaje:

=COUNTIF($B$2:$B$11,D3)

To je lahko tudi v primeru, ko želite, da je pogoj v celici, vendar ga ne želite uporabiti z operaterjem.Na primer, morda želite, da je številka v celici D3 10 namesto >10.

V tem primeru morate ustvariti pogojni parameter, ki je kombinacija operaterja in reference celice (glejte spodnjo sliko):

=COUNTIF($B$2:$B$11,”>”&D3)

Opomba: Ko združite operatorje in reference celic, so operatorji vedno zaprti v dvojnih narekovajih.Operaterji in reference celic so povezani z znakom ampersanda (&).

#3 Preštejte celice, ko je kriterij manjši od vrednosti

Za pridobitev števila celic z vrednostjo, manjšo od določene vrednosti, uporabimo operator manj kot ("<").Lahko ga uporabimo neposredno v formulah ali pa uporabimo reference celic s pogoji.

Kadar koli uporabimo operator v pogoju v Excelu, ga moramo postaviti v dvojne narekovaje.Na primer, če je merilo, da mora biti količina manjša od 5, potem moramo kot merilo vnesti "<5" (glej sliko spodaj):

=COUNTIF($B$2:$B$11,”<5”)

V celice lahko vključite tudi pogoje in kot pogoje uporabite reference celic.V tem primeru vam standarda ni treba postaviti v dvojne narekovaje (glejte spodnjo sliko):

=COUNTIF($B$2:$B$11,D3)

Prav tako boste morda želeli, da je pogoj v celici, ne pa pri operaterju.Na primer, morda želite, da ima celica D3 številko 5 namesto <5.

V tem primeru morate ustvariti pogojni parameter, ki je kombinacija operaterja in reference celice:

=COUNTIF($B$2:$B$11,"<"&D3)

Opomba: Ko združite operatorje in reference celic, so operatorji vedno zaprti v dvojnih narekovajih.Operaterji in reference celic so povezani z znakom ampersanda (&).

#4 Preštejte celice z več merili – med dvema vrednostma

Da bi dobili število vrednosti med dvema vrednostma, moramo v funkciji COUNTIF uporabiti več pogojev.

To lahko storite na dva načina:

XNUMX. način: uporabite funkcijo COUNTIFS

Funkcija COUNTIFS lahko obravnava več pogojev kot argumente in šteje celice samo, če so vsi pogoji TRUE.Za štetje celic z vrednostmi med dvema določenima vrednostma, kot sta 5 in 10, lahko uporabimo naslednjo funkcijo COUNTIFS:

=COUNTIFS($B$2:$B$11,”>5″,$B$2:$B$11,”<10″)

Opomba: zgornja formula ne šteje celic, ki vsebujejo 5 ali 10.Če želite vključiti te celice, uporabite operatorje večje ali enako (>=) in manjše ali enako (<=).Tukaj je formula:

=COUNTIFS($B$2:$B$11,”>=5″,$B$2:$B$11,”<=10″) 

Te pogoje lahko tudi vključite v celice in uporabite reference celic kot pogoje.V tem primeru vam standarda ni treba postaviti v dvojne narekovaje (glejte spodnjo sliko):

Uporabite lahko tudi kombinacijo sklicevanj na celice in operatorjev (operatorje vnašate neposredno v formule).Pri kombiniranju operatorjev in sklicevanj na celice so operatorji vedno zaprti v dvojnih narekovajih.Operaterji in reference celic so povezani z znakom ampersanda (&).

2. način: uporabite dve funkciji COUNTIF

Če imate več pogojev, lahko uporabite COUNTIFS ali ustvarite kombinacijo funkcij COUNTIF.Naslednja formula bo naredila isto stvar:

=COUNTIF($B$2:$B$11,”>5″)-COUNTIF($B$2:$B$11,”>10″)

V zgornji formuli najprej poiščemo število celic z vrednostjo večjo od 5 in nato odštejemo število celic z vrednostjo večjo od 10.To nam bo dalo rezultat 5 (tj. z vrednostmi, ki so večje od 5 in manjše ali enake 10).

Če želite, da formula vsebuje 5 in 10, namesto tega uporabite naslednjo formulo:

=COUNTIF($B$2:$B$11,”>=5”)-COUNTIF($B$2:$B$11,”>10”)

Če želite, da formula izključi "5" in "10" iz štetja, uporabite naslednjo formulo:

=COUNTIF($B$2:$B$11,”>=5″)-COUNTIF($B$2:$B$11,”>10″)-COUNTIF($B$2:$B$11,10)

Te pogoje lahko vključite v celice in uporabite reference celic ali pa uporabite kombinacijo operaterjev in sklicevanj na celice.

Uporabite pogoje BESEDILO v Excelovih funkcijah

#1 Preštejte celice, ko je pogoj enak določenemu besedilu

Če želite prešteti celice, ki vsebujejo natančno ujemanje z določenim besedilom, lahko to besedilo preprosto uporabimo kot pogoj.Na primer, v nizu podatkov (prikazano spodaj), če bi želel prešteti vse celice z imenom Joe, bi lahko uporabil naslednjo formulo:

=COUNTIF($B$2:$B$11,"Joe")

Ker je to besedilni niz, moram pogoj besedila postaviti v dvojne narekovaje.

V celico lahko vključite tudi pogoj in nato uporabite to sklic na celico (kot je prikazano spodaj):

=COUNTIF($B$2:$B$11,E3)

Opomba: Morda boste dobili napačne rezultate, če so v razponu pogoja ali pogoja presledki na začetku/poslovanju.Pred uporabo teh formul očistite podatke.

#2 Preštejte celice, ko pogoj ni enak določenemu besedilu

Podobno kot smo videli v zgornjem primeru, lahko tudi preštejete celice, ki ne vsebujejo določenega besedila.Za to moramo uporabiti operator ni enako (<>).

Ob predpostavki, da želite prešteti vse celice, ki ne vsebujejo imena JOE, je tukaj formula, ki lahko to naredi:

=COUNTIF($B$2:$B$11,”<>Joe”)

V celice lahko vključite tudi pogoje in kot pogoje uporabite reference celic.V tem primeru vam standarda ni treba postaviti v dvojne narekovaje (glejte spodnjo sliko):

=COUNTIF($B$2:$B$11,E3)

To je lahko tudi v primeru, ko želite, da je pogoj v celici, vendar ga ne želite uporabiti z operaterjem.Na primer, morda želite, da ima celica D3 ime Joe namesto <>Joe.

V tem primeru morate ustvariti pogojni parameter, ki je kombinacija operaterja in reference celice (glejte spodnjo sliko):

=COUNTIF($B$2:$B$11,”<>”&E3)

Pri kombiniranju operatorjev in sklicevanj na celice so operatorji vedno zaprti v dvojnih narekovajih.Operaterji in reference celic so povezani z znakom ampersanda (&).

Uporaba pogojev DATE v Excelovih funkcijah COUNTIF in COUNTIFS

Excel shranjuje datume in ure kot številke.Torej ga lahko uporabimo kot številko.

#1 Šteje celice, ko je pogoj enak določenemu datumu

Za pridobitev števila celic, ki vsebujejo določen datum, bomo skupaj z datumom uporabili operator enako (=).

Za delo z datumi priporočam uporabo funkcije DATE, saj odpravlja vsako možnost, da bi bila vrednost datuma napačna.Torej, na primer, če bi želel uporabiti datum 2015. september 9, bi lahko uporabil funkcijo DATE takole:

= DATUM (2015,9,1)

Kljub regionalnim razlikam bo ta formula vrnila isti datum.Na primer, 01-09-2015 bi bil 2015. september 9 v sintakso datuma v ZDA in 1. januar 2015 pod sintakso datuma v Združenem kraljestvu.Vendar se bo ta formula vedno vrnila 2105. septembra 9.

Tukaj je formula za štetje števila celic, ki vsebujejo datum 02-09-2015:

=COUNTIF($A$2:$A$11,DATUM(2015,9,2))

#2 Preštejte celice, ko je pogoj pred ali po določenem datumu

Za štetje celic, ki vsebujejo datume pred ali po določenem datumu, lahko uporabimo operatorje manj kot/več.

Če bi na primer želel prešteti vse celice, ki vsebujejo datume po 2015. septembru 9, bi lahko uporabil naslednjo formulo:

=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))

Prav tako lahko preštejete število celic pred določenim datumom.Če želite v štetje vključiti datume, uporabite operator "enako" skupaj z operatorji "več kot/manj kot".

Uporabite lahko tudi reference celic, ki vsebujejo datume.V tem primeru morate uporabiti ampersand (&), da združite operator (v dvojnih narekovajih) z datumom.

Glejte spodnji primer:

=COUNTIF($A$2:$A$11,”>”&F3)

#3 Preštejte celice z več merili – med dvema datumoma

Da bi dobili število vrednosti med dvema vrednostma, moramo v funkciji COUNTIF uporabiti več pogojev.

To lahko storimo na dva načina - eno samo funkcijo COUNTIFS ali dve funkciji COUNTIF.

XNUMX. način: uporabite funkcijo COUNTIFS

Funkcija COUNTIFS lahko vzame več pogojev kot argumente in šteje celice samo, če so vsi pogoji TRUE.Za štetje celic z vrednostmi med dvema določenima datumoma, kot sta 9. september in 2. september, lahko uporabimo naslednjo funkcijo COUNTIFS:

=COUNTIFS($A$2:$A$11,”>”&DATE(2015,9,2),$A$2:$A$11,”<“&DATE(2015,9,7))

Zgornja formula ne šteje celic, ki vsebujejo določen datum.Če želite vključiti tudi te datume, uporabite operatorje večje ali enako (>=) in manjše ali enako (<=).Tukaj je formula:

=COUNTIFS($A$2:$A$11,”>=”&DATE(2015,9,2),$A$2:$A$11,”<=”&DATE(2015,9,7))

V celice lahko vključite tudi datume in uporabite reference celic kot merila.V tem primeru ne morete dovoliti, da operater vključi datum v celico.Formuli morate ročno dodati operatorje (v dvojnih narekovajih) in uporabiti ampersand (&), da dodate reference celic.Glej spodaj:

=COUNTIFS($A$2:$A$11,”>”&F3,$A$2:$A$11,”<“&G3)

2. način: uporabite funkcijo COUNTIF

Če imate več pogojev, lahko uporabite funkcijo COUNTIFS ali ustvarite kombinacijo dveh funkcij COUNTIF.Naslednja formula bo prav tako pomagala:

=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7))

V zgornji formuli najprej poiščemo število celic, katerih datum je po 9. septembru, nato pa odštejemo število celic, katerih datum je po 2. septembru.To bo dalo rezultat 9 (to je število celic z datumi po 7. septembru in na ali pred 7. septembrom).

Če ne želite, da formula izračuna tako 9. september kot 2. september, namesto tega uporabite naslednjo formulo:

=COUNTIF($A$2:$A$11,”>=”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7))

Če želite iz štetja izključiti dva datuma, uporabite naslednjo formulo:

=COUNTIF($A$2:$A$11,”>”&DATE(2015,9,2))-COUNTIF($A$2:$A$11,”>”&DATE(2015,9,7)-COUNTIF($A $2:$A$11,日期(2015,9,7)))

Poleg tega lahko v celici nastavite standardni datum in uporabite reference celic (in operaterje v dvojnih narekovajih z ampersandom).

Uporaba nadomestnih znakov v pogojih funkcij COUNTIF in COUNTIFS

V Excelu so trije nadomestni znaki:

1. *(zvezdica) - predstavlja poljubno število znakov.Na primer, ex* lahko pomeni excel, excels, primer, strokovnjak itd.
2. ? (vprašaj) - Predstavlja en sam znak.Trömp lahko na primer pomeni Trump ali Tramp.
3. ~ (tilda) - Uporablja se za prepoznavanje nadomestnih znakov (~, *, ?) v besedilu.

Funkcijo COUNTIF z nadomestnimi znaki lahko uporabite za štetje celic, ko druge vgrajene funkcije štetja ne uspejo.Recimo, da imate nabor podatkov, ki izgleda takole:

Zdaj pa vzemimo nekaj primerov:

#1 Preštejte celice, ki vsebujejo besedilo

Za štetje celic z besedilom v njih lahko uporabimo nadomestni znak * (zvezdica).Ker zvezdice predstavljajo poljubno število znakov, bo preštel vse celice, ki vsebujejo poljubno besedilo.Tukaj je formula:

=COUNTIFS($C$2:$C$11,"*")

Opomba: zgornja formula prezre celice, ki vsebujejo številke, prazne celice in logične vrednosti, vendar šteje celice, ki vsebujejo apostrofe (in so zato videti prazne) ali so bile morda vključene kot del formule.

Tukaj je podrobna vadnica o ravnanju s primeri s praznimi nizi ali apostrofi.

#2 Preštejte celice, ki niso prazne

Če razmišljate o uporabi funkcije COUNTA, premislite še enkrat.

Preizkusite, morda vas bo razočaral.COUNTA bo štel tudi celice, ki vsebujejo prazne nize (običajno jih vrnejo formule kot ="", ali ko ljudje samo vnesejo apostrofe v celice).Celice, ki vsebujejo prazne nize, se zdijo prazne, vendar niso, zato jih šteje funkcija COUNTA.

COUNTA bo štel tudi celice, ki vsebujejo prazne nize (običajno jih vrnejo formule kot ="", ali ko ljudje samo vnesejo apostrofe v celice).Celice, ki vsebujejo prazne nize, se zdijo prazne, vendar niso, zato jih šteje funkcija COUNTA.

Torej, če uporabite formulo = COUNTA (A1:A11), vrne 11, ko bi moral vrniti 10.

Tukaj je popravek:

=COUNTIF($A$1:$A$11,”?*”)+COUNT($A$1:$A$11)+SUMPRODUCT(–ISLOGICAL($A$1:$A$11))

To formulo razumemo tako, da jo razčlenimo:

#3 Preštejte celice, ki vsebujejo določeno besedilo

Recimo, da želimo prešteti vse celice, kjer se ime prodajnega predstavnika začne z J.To je mogoče enostavno doseči z uporabo nadomestnih znakov v funkciji COUNTIF.Tukaj je formula:

=COUNTIFS($C$2:$C$11,"J*") 

Pogoj J* določa, da se mora besedilo v celici začeti z J in lahko vsebuje poljubno število znakov.

Če želite šteti celice, ki vsebujejo črke kjer koli v besedilu, jih obkrožite z zvezdico.Na primer, če želite prešteti celice, ki vsebujejo črko "a", uporabite *a* kot pogoj.

V primerjavi z mojimi drugimi članki je ta nenavadno dolg.Upam, da vam je všeč.Sporočite mi, kaj mislite, tako da pustite komentar.