Excel ima številne funkcije, pri katerih mora uporabnik določiti eno ali več meril, da dobi rezultat. Če na primer ž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č meril v funkcijah COUNTIF in COUNTIFS v Excelu.
Čeprav se bom v tej vadnici osredotočil predvsem na funkcije COUNTIF in COUNTIFS, lahko vse te primere uporabimo tudi v drugih Excelovih funkcijah, ki za vhodne podatke vzamejo več meril (na primer SUMIF, SUMIFS, AVERAGEIF in AVERAGEIFS).
Uvod v Excelove funkcije COUNTIF in COUNTIFS
Najprej se lotimo uporabe funkcij COUNTIF in COUNTIFS v Excelu.
Excel COUNTIF funkcija (upošteva enotna merila)
Excel COUNTIF funkcija je najbolj primerna za situacije, ko želite šteti celice na podlagi enega samega merila. Če želite šteti na podlagi več meril, uporabite funkcijo COUNTIFS.
Sintaksa
= COUNTIF (obseg, merila)
Argumenti vnosa
- obseg - obseg celic, ki jih želite prešteti.
- merila - merila, ki jih je treba ovrednotiti glede na obseg celic, da se celica šteje.
Excel COUNTIFS funkcija (upošteva več meril)
Excel COUNTIFS funkcija je najbolj primerna za situacije, ko želite šteti celice na podlagi več meril.
Sintaksa
= COUNTIFS (merilo_razpon1, merilo1, [območje_kriterijev2, merilo2] …)
Argumenti vnosa
- obseg_merij1 - Obseg celic, za katere želite oceniti glede na merila1.
- merila1 - merila, ki jih želite ovrednotiti za obseg_obsega1, da določite, katere celice želite šteti.
- [obseg_kriterijev2] - Obseg celic, za katere želite oceniti glede na merila2.
- [merila2] - merila, ki jih želite ovrednotiti za obseg_obsega2, da določite, katere celice želite šteti.
Zdaj pa poglejmo nekaj primerov uporabe več meril v funkcijah COUNTIF v Excelu.
Uporaba ŠTEVILNIH meril v Excelovih funkcijah COUNTIF
#1 Štej celice, če je merilo EQUAL vrednosti
Če želite pridobiti število celic, pri katerih je argument kriterija enak določeni vrednosti, lahko neposredno vnesete merila ali uporabite sklic na celico, ki vsebuje merila.
Spodaj je primer, kjer štejemo celice, ki vsebujejo številko 9 (kar pomeni, da je argument kriterija enak 9). Tukaj je formula:
= COUNTIF ($ B $ 2: $ B $ 11, D3)
V zgornjem primeru (na sliki) je merilo v celici D3. Merila lahko vnesete tudi neposredno v formulo. Na primer, lahko uporabite tudi:
= COUNTIF ($ B $ 2: $ B $ 11,9)
#2 Štej celice, če je merilo VEČJE OD Vrednosti
Če želimo pridobiti število celic z vrednostjo, ki je večja od podane vrednosti, uporabimo operator večji od (»>«). Lahko bi ga uporabili neposredno v formuli ali uporabili sklic na celico, ki ima merila.
Kadar koli uporabimo operator v merilih v Excelu, ga moramo postaviti v dvojne narekovaje. Na primer, če je merilo večje od 10, moramo kot merila vnesti »> 10« (glej sliko spodaj):
Tukaj je formula:
= COUNTIF ($ B $ 2: $ B $ 11, ”> 10 ″)
Merila imate lahko tudi v celici in kot merilo uporabite referenco celice. V tem primeru kriterijev NE podrejte v dvojnih narekovajih:
= COUNTIF ($ B $ 2: $ B $ 11, D3)
Lahko se zgodi tudi primer, ko želite, da so merila v celici, vendar tega ne želite pri operaterju. Na primer, morda želite, da ima celica D3 številko 10 in ne> 10.
V tem primeru morate ustvariti argument kriterija, ki je kombinacija sklica operaterja in celice (glej sliko spodaj):
= COUNTIF ($ B $ 2: $ B $ 11, ”>” & D3)
OPOMBA: Ko združite operator in sklic na celico, je operator vedno v dvojnih narekovajih. Operaterju in sklicu celice se pridruži znak ampersand (&).
#3 Štej celice, če je merilo manj kot vrednost
Če želimo dobiti število celic z vrednostjo, manjšo od podane vrednosti, uporabimo operator manj kot ("<"). Lahko bi ga uporabili neposredno v formuli ali uporabili sklic na celico, ki ima merila.
Kadar koli uporabimo operator v merilih v Excelu, ga moramo postaviti v dvojne narekovaje. Na primer, če je merilo, da mora biti število manjše od 5, moramo kot merilo vnesti "<5" (glej sliko spodaj):
= COUNTIF ($ B $ 2: $ B $ 11, «<5 ″)
Merila imate lahko tudi v celici in kot merilo uporabite referenco celice. V tem primeru kriterijev NE podate v dvojne narekovaje (glej sliko spodaj):
= COUNTIF ($ B $ 2: $ B $ 11, D3)
Lahko se zgodi tudi primer, ko želite, da so merila v celici, vendar tega ne želite pri operaterju. Na primer, morda želite, da ima celica D3 številko 5 in ne <5.
V tem primeru morate ustvariti argument kriterija, ki je kombinacija sklica operaterja in celice:
= COUNTIF ($ B $ 2: $ B $ 11, ”<“ & D3)
OPOMBA: Ko združite operator in sklic na celico, je operator vedno v dvojnih narekovajih. Operaterju in sklicu celice se pridruži znak ampersand (&).
#4 Štej celice z več merili - med dvema vrednostma
Če želimo prešteti vrednosti med dvema vrednostma, moramo v funkciji COUNTIF uporabiti več meril.
Tukaj sta dva načina za to:
METODA 1: Uporaba funkcije COUNTIFS
Funkcija COUNTIFS lahko obravnava več meril kot argumente in šteje celice le, če so vsi kriteriji TRUE. Za štetje celic z vrednostmi med dvema določenima vrednostma (recimo 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 več kot enako (> =) in manjše od (<=) operatorjev. Tukaj je formula:
= COUNTIFS ($ B $ 2: $ B $ 11, ”> = 5 ″, $ B $ 2: $ B $ 11,” <= 10 ″)
Ta merila lahko imate tudi v celicah in kot merilo uporabite sklic na celico. V tem primeru kriterijev NE podate v dvojne narekovaje (glej sliko spodaj):
Uporabite lahko tudi kombinacijo sklicev na celice in operatorjev (kjer je operator vnesen neposredno v formulo). Ko združite operator in sklic na celico, je operator vedno v dvojnih narekovajih. Operaterju in sklicu celice se pridruži znak ampersand (&).
METODA 2: Uporaba dveh funkcij COUNTIF
Če imate več meril, lahko uporabite COUNTIFS ali ustvarite kombinacijo COUNTIF funkcij. Enaka bi bila tudi spodnja formula:
= COUNTIF ($ B $ 2: $ B $ 11, ”> 5 ″)-COUNTIF ($ B $ 2: $ B $ 11,”> 10 ″)
V zgornji formuli najprej najdemo število celic z vrednostjo večjo od 5 in odštejemo število celic z vrednostjo večjo od 10. Tako bi dobili rezultat kot 5 (to je število celic, ki imajo vrednosti več kot 5 in manjše od 10).
Če želite, da formula vsebuje tako 5 kot 10, namesto tega uporabite naslednjo formulo:
= COUNTIF ($ B $ 2: $ B $ 11, ”> = 5 ″)-COUNTIF ($ B $ 2: $ B $ 11,”> 10 ″)
Če želite, da formula iz štetja izključi '5' in '10', uporabite naslednjo formulo:
= COUNTIF ($ B $ 2: $ B $ 11, ”> = 5 ″)-COUNTIF ($ B $ 2: $ B $ 11,”> 10 ″)-COUNTIF ($ B $ 2: $ B $ 11,10)
Ta merila lahko imate v celicah in uporabite sklice na celice ali pa uporabite kombinacijo operatorjev in sklicev na celice.
Uporaba meril BESEDILO v funkcijah Excel
#1 Štej celice, če je merilo EQUAL določenem besedilu
Za štetje celic, ki vsebujejo natančno ujemanje navedenega besedila, lahko to besedilo preprosto uporabimo kot merilo. Na primer, v naboru podatkov (prikazanem spodaj na sliki), če želim prešteti vse celice z imenom Joe, lahko uporabim spodnjo formulo:
= COUNTIF ($ B $ 2: $ B $ 11, "Joe")
Ker je to besedilni niz, moram besedilne kriterije postaviti v dvojne narekovaje.
Merila imate lahko tudi v celici in nato uporabite sklic na to celico (kot je prikazano spodaj):
= COUNTIF ($ B $ 2: $ B $ 11, E3)
OPOMBA: Če so v razdelku meril ali meril vodilni/končni presledki, lahko dobite napačne rezultate. Pred uporabo teh formul obvezno očistite podatke.
#2 Preštej celice, če merila NI enaka določenemu besedilu
Podobno kot smo videli v zgornjem primeru, lahko preštejete tudi celice, ki ne vsebujejo določenega besedila. Če želite to narediti, moramo uporabiti operator, ki ni enak operaterju ().
Recimo, da želite prešteti vse celice, ki ne vsebujejo imena JOE, tukaj je formula, ki bo to naredila:
= COUNTIF ($ B $ 2: $ B $ 11, "Joe")
Merila imate lahko tudi v celici in kot merilo uporabite referenco celice. V tem primeru kriterijev NE podate v dvojne narekovaje (glej sliko spodaj):
= COUNTIF ($ B $ 2: $ B $ 11, E3)
Lahko se zgodi tudi primer, ko želite, da so merila v celici, vendar tega ne želite pri operaterju. Na primer, morda želite, da celica D3 nosi ime Joe in ne Joe.
V tem primeru morate ustvariti argument kriterija, ki je kombinacija sklica operaterja in celice (glej sliko spodaj):
= COUNTIF ($ B $ 2: $ B $ 11, ”” in E3)
Ko združite operator in sklic na celico, je operator vedno v dvojnih narekovajih. Operaterju in sklicu celice se pridruži znak ampersand (&).
Uporaba meril DATE v funkcijah COUNTIF in COUNTIFS v Excelu
Excel shrani datum in čas kot številke. Zato ga lahko uporabimo na enak način kot številke.
#1 Štej celice, če je merilo EQUAL na določen datum
Da bi dobili število celic, ki vsebujejo določen datum, bi skupaj z datumom uporabili operator enak (=).
Za uporabo datuma priporočam uporabo funkcije DATE, saj se tako znebite vsakršne napake pri vrednosti datuma. Torej, na primer, če želim uporabiti datum 1. september 2015, lahko uporabim funkcijo DATE, kot je prikazano spodaj:
= DATE (2015,9,1)
Ta formula bi kljub regionalnim razlikam vrnila isti datum. Na primer, 01-09-2015 bi bil 1. september 2015 v skladu s sintakso datuma v ZDA in 09. januar 2015 v skladu s sintakso datuma v Združenem kraljestvu. Vendar bi se ta formula vedno vrnila 1. septembra 2105.
Tu je formula za štetje števila celic, ki vsebujejo datum 02-09-2015:
= COUNTIF ($ A $ 2: $ A $ 11, DATE (2015,9,2))
#2 Štej celice, če je merilo PRED ali PO določenem datumu
Za štetje celic, ki vsebujejo datum pred ali po določenem datumu, lahko uporabimo operatorje manjše od/večje od.
Na primer, če želim prešteti vse celice, ki vsebujejo datum po 2. septembru 2015, lahko uporabim formulo:
= COUNTIF ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,2))
Podobno lahko preštejete tudi število celic pred določenim datumom. Če želite v štetje vključiti datum, uporabite operator "enako" skupaj z operatorjem "več kot/manj kot".
Uporabite lahko tudi sklic na celico, ki vsebuje datum. V tem primeru morate združiti operator (v dvojnih narekovajih) z datumom z uporabo znaka (&).
Glej spodnji primer:
= COUNTIF ($ A $ 2: $ A $ 11, ”>” & F3)
#3 Štej celice z več merili - med dvema datumoma
Če želimo prešteti vrednosti med dvema vrednostma, moramo v funkciji COUNTIF uporabiti več meril.
To lahko storimo na dva načina - eno samo funkcijo COUNTIFS ali dve funkciji COUNTIF.
METODA 1: Uporaba funkcije COUNTIFS
Funkcija COUNTIFS lahko za argumente vzame več meril in šteje celice le, če so vsi kriteriji TRUE. Za štetje celic z vrednostmi med dvema določenima datumoma (recimo 2. septembra in 7. septembra) 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čene datume. Če želite vključiti tudi te datume, uporabite operatorje, ki so večji od enakega (> =) in manjši od enakega (<=). Tukaj je formula:
= COUNTIFS ($ A $ 2: $ A $ 11, ”> =” & DATE (2015,9,2), $ A $ 2: $ A $ 11, ”<=” & DATE (2015,9,7))
Datume imate lahko tudi v celici in kot merilo uporabite referenco celice. V tem primeru ne morete imeti operaterja z datumom v celicah. V formuli morate ročno dodati operaterje (v dvojnih narekovajih) in sklic na celico dodati z znakom (&). Oglejte si spodnjo sliko:
= COUNTIFS ($ A $ 2: $ A $ 11, ”>” & F3, $ A $ 2: $ A $ 11, ”<“ & G3)
METODA 2: Uporaba funkcij COUNTIF
Če imate več meril, lahko uporabite eno funkcijo COUNTIFS ali ustvarite kombinacijo dveh funkcij COUNTIF. Spodnja formula bi 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 najdemo število celic, ki imajo datum po 2. septembru, in odštejemo število celic z datumi po 7. septembru. Tako bi dobili rezultat 7 (kar je število celic, ki imajo datume po 2. septembru in 7. septembra ali pred tem).
Če ne želite, da formula šteje 2. in 7. 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 oba datuma izključiti iz štetja, uporabite naslednjo formulo:
= COUNTIF ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,2))-COUNTIF ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,7) -COUNTIF ($ A 2 USD: 11 USD, DATE (2015,9,7)))
Prav tako imate lahko datume meril v celicah in uporabite sklice na celice (skupaj z operatorji v dvojnih narekovajih, ki so združeni z znakom črke).
Uporaba WILDCARD ZNAKOV v kriterijih v funkcijah COUNTIF & COUNTIFS
V Excelu so trije nadomestni znaki:
- * (zvezdica) - Predstavlja poljubno število znakov. Na primer, ex* lahko pomeni excel, excels, na primer strokovnjak itd.
- ? (vprašaj) - Predstavlja en sam znak. Tr? Mp lahko na primer pomeni Trump ali Tramp.
- ~ (tilda) - Uporablja se za identifikacijo nadomestnega znaka (~, *,?) V besedilu.
Funkcijo COUNTIF z nadomestnimi znaki lahko uporabite za štetje celic, ko druga vgrajena funkcija štetja ne uspe. Recimo, da imate nabor podatkov, kot je prikazano spodaj:
Zdaj pa vzemimo različne primere:
#1 Štej celice, ki vsebujejo besedilo
Za štetje celic z besedilom lahko uporabimo nadomestni znak * (zvezdica). Ker zvezdica predstavlja poljubno število znakov, bi štela vse celice, ki vsebujejo besedilo. Tukaj je formula:
= COUNTIFS ($ C $ 2: $ C $ 11, ”*”)
Opomba: Zgornja formula ne upošteva celic, ki vsebujejo številke, prazne celice in logične vrednosti, vendar bi štela celice, ki vsebujejo apostrof (in so zato prazne) ali celice, ki vsebujejo prazen niz (= ””), ki je bil morda vrnjen kot del formule.
Tukaj je podroben vodič o ravnanju v primerih, ko je prazen niz ali apostrof.
Tukaj je podroben vodič o ravnanju v primerih, ko so prazni nizovi ali apostrofe.
Spodaj je videoposnetek, ki pojasnjuje različne scenarije štetja celic z besedilom.
#2 Štej neprazne celice
Če razmišljate o uporabi funkcije COUNTA, premislite še enkrat.
Poskusite in morda vam ne bo uspelo. COUNTA bo prešteval tudi celico, ki vsebuje prazen niz (pogosto ga formule vrnejo kot = ”” ali ko ljudje v celico vnesejo samo apostrof). Celice, ki vsebujejo prazne nize, so videti prazne, vendar niso in jih tako šteje funkcija COUNTA.
COUNTA bo prešteval tudi celico, ki vsebuje prazen niz (pogosto ga formule vrnejo kot = ”” ali ko ljudje v celico vnesejo samo apostrof). Celice, ki vsebujejo prazne nize, so videti prazne, vendar niso in jih tako šteje funkcija COUNTA.
Torej, če uporabite formulo = COUNTA (A1: A11), vrne 11, medtem ko mora vrniti 10.
Tukaj je popravek:
= COUNTIF ($ A $ 1: $ A $ 11, ”?*”)+COUNT ($ A $ 1: $ A $ 11)+SUMPRODUCT (-ISLOGICAL ($ A $ 1: $ A $ 11))
Razumejmo to formulo tako, da jo razčlenimo:
- COUNTIF ($ N $ 8: $ N $ 18, ”?*”) - Ta del formule vrne 5. To vključuje katero koli celico, ki vsebuje besedilni znak. A? predstavlja en znak in * predstavlja poljubno število znakov. Zato kombinacija?* V merilih primanjkuje Excelu, da šteje celice, ki imajo v sebi vsaj en besedilni znak.
- COUNT ($ A $ 1: $ A $ 11) - Ta šteje vse celice, ki vsebujejo številke. V zgornjem primeru vrne 3.
- SUMPRODUCT (-ISLOGICAL ($ A $ 1: $ A $ 11)) - Ta šteje vse celice, ki vsebujejo logične vrednosti. V zgornjem primeru vrne 2.
#3 Štej celice, ki vsebujejo določeno besedilo
Recimo, da želimo prešteti vse celice, kjer se ime prodajnega predstavnika začne z J. To lahko enostavno dosežemo z uporabo nadomestnega znaka v funkciji COUNTIF. Tukaj je formula:
= COUNTIFS ($ C $ 2: $ C $ 11, "J*")
Merilo 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 abecedo kjer koli v besedilu, jo obrobite z zvezdico na obeh straneh. Na primer, če želite šteti celice, ki vsebujejo abecedo »a«, uporabite * a * kot merilo.
Ta članek je nenavadno dolg v primerjavi z mojimi drugimi članki. Upam, da ste uživali. Sporočite mi svoje misli tako, da pustite komentar.
Koristne bodo tudi naslednje vaje programa Excel:
- Preštejte število besed v Excelu.
- Štetje celic na podlagi barve ozadja v Excelu.
- Kako sešteti stolpec v Excelu (5 res enostavnih načinov)
