Excel ima mnoge funkcije u kojima korisnik mora navesti jedan ili više kriterija da bi dobio rezultat. Na primjer, ako želite brojati ćelije na temelju više kriterija, možete koristiti funkcije COUNTIF ili COUNTIFS u Excelu.
Ovaj vodič pokriva različite načine korištenja jednog ili više kriterija u funkciji COUNTIF i COUNTIFS u Excelu.
Iako ću se u ovom vodiču prvenstveno usredotočiti na funkcije COUNTIF i COUNTIFS, svi ovi primjeri mogu se koristiti i u drugim Excel funkcijama koje uzimaju više kriterija kao ulaze (kao što su SUMIF, SUMIFS, AVERAGEIF i AVERAGEIFS).
Uvod u Excel COUNTIF i COUNTIFS funkcije
Hajdemo se prvo upoznati s korištenjem funkcija COUNTIF i COUNTIFS u Excelu.
Excel COUNTIF funkcija (uzima pojedinačne kriterije)
Excel COUNTIF funkcija najprikladnija je za situacije kada želite prebrojati ćelije na temelju jednog kriterija. Ako želite računati na temelju više kriterija, upotrijebite funkciju COUNTIFS.
Sintaksa
= COUNTIF (raspon, kriteriji)
Ulazni argumenti
- raspon - raspon ćelija koje želite prebrojati.
- kriteriji - kriterije koje je potrebno ocijeniti u odnosu na raspon stanica da bi se ćelija prebrojila.
Excel COUNTIFS funkcija (uzima više kriterija)
Excel COUNTIFS funkcija najprikladnija je za situacije kada želite prebrojati ćelije na temelju više kriterija.
Sintaksa
= COUNTIFS (raspon_kriterija1, kriterij1, [raspon_kriterija2, kriterij2] …)
Ulazni argumenti
- raspon_kriterija1 - Raspon ćelija za koje želite ocijeniti prema kriterijima1.
- kriterij1 - kriterije koje želite procijeniti za raspon_kriterija1 kako biste odredili koje će se stanice brojati.
- [raspon_kriterija2] - Raspon ćelija za koje želite ocijeniti prema kriterijima2.
- [kriteriji2] - kriterije koje želite procijeniti za raspon_kriterija2 kako biste odredili koje će se stanice brojati.
Pogledajmo sada neke primjere korištenja više kriterija u COUNTIF funkcijama u Excelu.
Korištenje BROJNIH kriterija u Excel COUNTIF funkcijama
#1 Broji ćelije ako je kriterij JEDNAK do vrijednosti
Da biste dobili broj ćelija u kojima je argument kriterija jednak navedenoj vrijednosti, možete izravno unijeti kriterij ili upotrijebiti referencu ćelije koja sadrži kriterije.
Dolje je primjer gdje računamo ćelije koje sadrže broj 9 (što znači da je argument kriterija jednak 9). Evo formule:
= COUNTIF ($ B $ 2: $ B $ 11, D3)
U gornjem primjeru (na slici), kriterij je u ćeliji D3. Također možete unijeti kriterije izravno u formulu. Na primjer, možete koristiti i:
= COUNTIF ($ B $ 2: $ B $ 11,9)
#2 Broji ćelije ako je kriterij VEĆI OD vrijednosti
Da bismo dobili broj ćelija s vrijednošću većom od navedene vrijednosti, koristimo operator veći od (">"). Mogli bismo ga upotrijebiti izravno u formuli ili upotrijebiti referencu ćelije koja ima kriterije.
Kad god koristimo operator u kriterijima u Excelu, moramo ga staviti u dvostruke navodnike. Na primjer, ako je kriterij veći od 10, tada moramo unijeti "> 10" kao kriterij (vidi sliku ispod):
Evo formule:
= COUNTIF ($ B $ 2: $ B $ 11, ”> 10 ″)
Također možete imati kriterije u ćeliji i kao kriterij koristiti referencu ćelije. U ovom slučaju NE morate stavljati kriterije u dvostruke navodnike:
= COUNTIF ($ B $ 2: $ B $ 11, D3)
Može se dogoditi i slučaj da želite da kriteriji budu u ćeliji, ali ne želite to s operatorom. Na primjer, možda želite da ćelija D3 ima broj 10, a ne> 10.
U tom slučaju morate stvoriti argument kriterija koji je kombinacija reference operatora i ćelije (vidi sliku ispod):
= COUNTIF ($ B $ 2: $ B $ 11, ”>” & D3)
NAPOMENA: Kada kombinirate operator i referencu ćelije, operator je uvijek u dvostrukim navodnicima. Operater i referenca ćelije pridruženi su znakom & ().
#3 Broji ćelije ako je kriterij MANJI od vrijednosti
Da bismo dobili broj ćelija čija je vrijednost manja od navedene vrijednosti, koristimo operator less than ("<"). Mogli bismo ga upotrijebiti izravno u formuli ili upotrijebiti referencu ćelije koja ima kriterije.
Kad god koristimo operator u kriterijima u Excelu, moramo ga staviti u dvostruke navodnike. Na primjer, ako je kriterij da broj treba biti manji od 5, tada moramo unijeti "<5" kao kriterij (vidi sliku ispod):
= COUNTIF ($ B $ 2: $ B $ 11, "<5 ″)
Također možete imati kriterije u ćeliji i kao kriterij koristiti referencu ćelije. U ovom slučaju NE morate stavljati kriterije u dvostruke navodnike (vidi sliku ispod):
= COUNTIF ($ B $ 2: $ B $ 11, D3)
Također, mogao bi se dogoditi slučaj da želite da kriteriji budu u ćeliji, ali to ne želite s operatorom. Na primjer, možda želite da ćelija D3 ima broj 5, a ne <5.
U tom slučaju morate stvoriti argument kriterija koji je kombinacija reference operatora i ćelije:
= COUNTIF ($ B $ 2: $ B $ 11, ”<“ & D3)
NAPOMENA: Kada kombinirate operator i referencu ćelije, operator je uvijek u dvostrukim navodnicima. Operater i referenca ćelije pridruženi su znakom & ().
#4 Brojanje ćelija s više kriterija - između dvije vrijednosti
Da bismo dobili broj vrijednosti između dvije vrijednosti, moramo koristiti više kriterija u funkciji COUNTIF.
Evo dvije metode za to:
METODA 1: Korištenje funkcije COUNTIFS
Funkcija COUNTIFS može obrađivati više kriterija kao argumente i broji ćelije samo ako su svi kriteriji TRUE. Za brojanje ćelija s vrijednostima između dvije navedene vrijednosti (recimo 5 i 10), možemo upotrijebiti sljedeću funkciju COUNTIFS:
= COUNTIFS ($ B $ 2: $ B $ 11, ”> 5 ″, $ B $ 2: $ B $ 11,” <10 ″)
NAPOMENA: Gornja formula ne broji ćelije koje sadrže 5 ili 10. Ako želite uvrstiti te ćelije, koristite operatore veće od jednake (> =) i manje od jednake (<=). Evo formule:
= COUNTIFS ($ B $ 2: $ B $ 11, ”> = 5 ″, $ B $ 2: $ B $ 11,” <= 10 ″)
Ove kriterije možete imati i u ćelijama te kao kriterij koristiti referencu ćelije. U ovom slučaju NE morate stavljati kriterije u dvostruke navodnike (vidi sliku ispod):
Također možete koristiti kombinaciju referenci ćelija i operatora (gdje se operator unosi izravno u formulu). Kada kombinirate operator i referencu ćelije, operator je uvijek u dvostrukim navodnicima. Operater i referenca ćelije pridruženi su znakom & ().
METODA 2: Korištenje dvije funkcije COUNTIF
Ako imate više kriterija, možete upotrijebiti COUNTIFS ili stvoriti kombinaciju COUNTIF funkcija. Formula u nastavku također bi učinila istu stvar:
= COUNTIF ($ B $ 2: $ B $ 11, ”> 5 ″)-COUNTIF ($ B $ 2: $ B $ 11,”> 10 ″)
U gornjoj formuli prvo pronalazimo broj ćelija koje imaju vrijednost veću od 5 i oduzimamo broj ćelija s vrijednošću većom od 10. To bi nam dalo rezultat kao 5 (što je broj stanica koje imaju vrijednosti veće od 5 i manje od jednake 10).
Ako želite da formula sadrži i 5 i 10, umjesto toga upotrijebite sljedeću formulu:
= COUNTIF ($ B $ 2: $ B $ 11, ”> = 5 ″)-COUNTIF ($ B $ 2: $ B $ 11,”> 10 ″)
Ako želite da formula izuzme '5' i '10' iz brojanja, upotrijebite sljedeću formulu:
= COUNTIF ($ B $ 2: $ B $ 11, ”> = 5 ″)-COUNTIF ($ B $ 2: $ B $ 11,”> 10 ″)-COUNTIF ($ B $ 2: $ B $ 11,10)
Ove kriterije možete imati u ćelijama i koristiti reference ćelija ili možete koristiti kombinaciju operatora i referenci ćelija.
Korištenje kriterija TEKSTA u Excel funkcijama
#1 Broji ćelije ako je kriterij JEDNAK navedenom tekstu
Za prebrojavanje ćelija koje sadrže točno podudaranje navedenog teksta, možemo jednostavno koristiti taj tekst kao kriterij. Na primjer, u skupu podataka (prikazano ispod na slici), ako želim prebrojati sve ćelije s imenom Joe, mogu upotrijebiti donju formulu:
= COUNTIF ($ B $ 2: $ B $ 11, "Joe")
Budući da je ovo tekstualni niz, moram staviti kriterije teksta u dvostruke navodnike.
Također možete imati kriterije u ćeliji, a zatim upotrijebiti tu referencu ćelije (kao što je prikazano u nastavku):
= COUNTIF ($ B $ 2: $ B $ 11, E3)
NAPOMENA: Možete dobiti pogrešne rezultate ako postoje vodeći/krajnji razmaci u rasponu kriterija ili kriterija. Prije uporabe ovih formula obavezno očistite podatke.
#2 Broji ćelije ako kriterij NIJE JEDNAK navedenom tekstu
Slično onome što smo vidjeli u gornjem primjeru, također možete prebrojati ćelije koje ne sadrže navedeni tekst. Da bismo to učinili, moramo koristiti operator nije jednak operatoru ().
Pretpostavimo da želite prebrojiti sve ćelije koje ne sadrže naziv JOE, evo formule koja će to učiniti:
= COUNTIF ($ B $ 2: $ B $ 11, "Joe")
Također možete imati kriterije u ćeliji i kao kriterij koristiti referencu ćelije. U ovom slučaju NE morate stavljati kriterije u dvostruke navodnike (vidi sliku ispod):
= COUNTIF ($ B $ 2: $ B $ 11, E3)
Može se dogoditi i slučaj da želite da kriteriji budu u ćeliji, ali ne želite to s operatorom. Na primjer, možda želite da ćelija D3 nosi ime Joe, a ne Joe.
U tom slučaju morate stvoriti argument kriterija koji je kombinacija reference operatora i ćelije (vidi sliku ispod):
= COUNTIF ($ B $ 2: $ B $ 11, ”” i E3)
Kada kombinirate operator i referencu ćelije, operator je uvijek u dvostrukim navodnicima. Operater i referenca ćelije pridruženi su znakom & ().
Korištenje kriterija DATE u Excel funkcijama COUNTIF i COUNTIFS
Excel pohranjuje datum i vrijeme kao brojeve. Dakle, možemo ga koristiti na isti način na koji koristimo brojeve.
#1 Broji ćelije ako je kriterij JEDNAK naveden datum
Da bismo dobili broj ćelija koje sadrže navedeni datum, upotrijebili bismo operator jednak operatoru (=) zajedno s datumom.
Za korištenje datuma preporučujem upotrebu funkcije DATE jer se time rješava svake mogućnosti pogreške u vrijednosti datuma. Tako, na primjer, ako želim koristiti datum 1. rujna 2015., mogu koristiti funkciju DATE kao što je prikazano u nastavku:
= DATE (2015,9,1)
Ova bi formula vratila isti datum unatoč regionalnim razlikama. Na primjer, 01.-09. 2015 bio bi 1. rujna 2015. prema sintaksi datuma u SAD-u, a 9. siječnja 2015. prema sintaksi datuma u Velikoj Britaniji. Međutim, ova bi se formula uvijek vraćala 1. rujna 2105.
Evo formule za brojanje ćelija koje sadrže datum 02.09.2015.
= COUNTIF ($ A $ 2: $ A $ 11, DATE (2015,9,2))
#2 Broji ćelije ako je kriterij PRE ili POSLE određenog datuma
Za brojanje ćelija koje sadrže datum prije ili poslije navedenog datuma, možemo koristiti operatore manje/veće od.
Na primjer, ako želim prebrojati sve ćelije koje sadrže datum nakon 2. rujna 2015., mogu upotrijebiti formulu:
= COUNTIF ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,2))
Slično, možete i izbrojati broj ćelija prije navedenog datuma. Ako želite uključiti datum u brojanje, upotrijebite operator i "jednako" zajedno s operatorom "veće od/manje od".
Također možete koristiti referencu ćelije koja sadrži datum. U tom slučaju morate kombinirati operator (unutar dvostrukih navodnika) s datumom pomoću znaka & ().
Pogledajte primjer ispod:
= COUNTIF ($ A $ 2: $ A $ 11, ”>” & F3)
#3 Brojanje ćelija s više kriterija - između dva datuma
Da bismo dobili broj vrijednosti između dvije vrijednosti, moramo koristiti više kriterija u funkciji COUNTIF.
To možemo učiniti pomoću dvije metode - jedne funkcije COUNTIFS ili dvije funkcije COUNTIF.
METODA 1: Korištenje funkcije COUNTIFS
Funkcija COUNTIFS može uzeti više kriterija kao argumente i broji ćelije samo ako su svi kriteriji TRUE. Za brojanje ćelija s vrijednostima između dva navedena datuma (recimo 2. rujna i 7. rujna), možemo upotrijebiti sljedeću funkciju COUNTIFS:
= COUNTIFS ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,2), $ A $ 2: $ A $ 11, ”<“ & DATE (2015,9,7))
Gornja formula ne broji ćelije koje sadrže navedene datume. Ako želite uključiti i ove datume, koristite operatore veće od jednake (> =) i manje od jednake (<=). Evo formule:
= COUNTIFS ($ A $ 2: $ A $ 11, ”> =” & DATE (2015,9,2), $ A $ 2: $ A $ 11, ”<=” & DATE (2015,9,7))
Datume možete imati i u ćeliji, a kao kriterij koristiti referencu ćelije. U tom slučaju ne možete imati operator s datumom u ćelijama. Morate ručno dodati operatore u formulu (u dvostrukim navodnicima) i dodati referencu ćelije pomoću znaka & (). Pogledajte sliku ispod:
= COUNTIFS ($ A $ 2: $ A $ 11, ”>” & F3, $ A $ 2: $ A $ 11, ”<“ & G3)
METODA 2: Korištenje funkcija COUNTIF
Ako imate više kriterija, možete upotrijebiti jednu funkciju COUNTIFS ili stvoriti kombinaciju dvije funkcije COUNTIFS. Formula u nastavku također bi pomogla:
= COUNTIF ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,2))-COUNTIF ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,7))
U gornjoj formuli prvo pronalazimo broj ćelija koje imaju datum nakon 2. rujna i oduzimamo broj ćelija s datumima nakon 7. rujna. To bi nam dalo rezultat kao 7 (što je broj ćelija koje imaju datume nakon 2. rujna i na dan ili prije 7. rujna).
Ako ne želite da se formula računa i za 2. i za 7. rujna, umjesto toga upotrijebite sljedeću formulu:
= COUNTIF ($ A $ 2: $ A $ 11, ”> =” & DATE (2015,9,2))-COUNTIF ($ A $ 2: $ A $ 11, ”>” & DATE (2015,9,7))
Ako želite isključiti oba datuma iz brojanja, upotrijebite sljedeću formulu:
= 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)))
Također, možete postaviti datume kriterija u ćelije i koristiti reference ćelija (zajedno s operatorima u dvostrukim navodnicima spojenim znakom &).
Korištenje WILDCARD ZNAKOVA u kriterijima u COUNTIF & COUNTIFS funkcijama
U Excelu postoje tri zamjenska znaka:
- * (zvjezdica) - Predstavlja neograničeni broj znakova. Na primjer, ex* može značiti excel, excels, primjer, stručnjak itd.
- ? (upitnik) - Predstavlja jedan jedini znak. Na primjer, Tr? Mp bi mogao značiti Trump ili Tramp.
- ~ (tilda) - Koristi se za identifikaciju zamjenskog znaka (~, *,?) U tekstu.
Možete upotrijebiti funkciju COUNTIF sa zamjenskim znakovima za brojanje ćelija kada druga ugrađena funkcija brojanja ne uspije. Na primjer, pretpostavimo da imate skup podataka kako je prikazano u nastavku:
Uzmimo sada različite primjere:
#1 Brojanje ćelija koje sadrže tekst
Za brojanje ćelija s tekstom možemo upotrijebiti zamjenski znak * (zvjezdica). Budući da zvjezdica predstavlja bilo koji broj znakova, računat će sve ćelije koje sadrže bilo koji tekst. Evo formule:
= COUNTIFS ($ C $ 2: $ C $ 11, ”*”)
Napomena: Gornja formula zanemaruje ćelije koje sadrže brojeve, prazne ćelije i logičke vrijednosti, ali će računati da ćelije sadrže apostrof (i stoga izgledaju prazne) ili ćelije koje sadrže prazan niz (= ””) koje su možda vraćene kao dio formule.
Evo detaljnog vodiča o rukovanju slučajevima u kojima postoji prazan niz ili apostrof.
Evo detaljnog vodiča o rukovanju slučajevima u kojima postoje prazni nizovi ili apostrofe.
Ispod je videozapis koji objašnjava različite scenarije prebrojavanja ćelija s tekstom.
#2 Brojanje ćelija koje nisu prazne
Razmišljate li o upotrebi funkcije COUNTA, razmislite ponovno.
Pokušajte i možda vam ne uspije. COUNTA će također brojati ćeliju koja sadrži prazan niz (formule se često vraćaju kao = ”” ili kada ljudi unesu samo apostrof u ćeliju). Ćelije koje sadrže prazne nizove izgledaju prazne, ali nisu i stoga se broje pomoću funkcije COUNTA.
COUNTA će također brojati ćeliju koja sadrži prazan niz (često se formulama vraća kao = ”” ili kada ljudi unesu samo apostrof u ćeliju). Ćelije koje sadrže prazne nizove izgledaju prazne, ali nisu i stoga se broje pomoću funkcije COUNTA.
Dakle, ako koristite formulu = COUNTA (A1: A11), ona vraća 11, dok bi trebala vratiti 10.
Evo popravka:
= COUNTIF ($ A $ 1: $ A $ 11, ”?*”)+COUNT ($ A $ 1: $ A $ 11)+SUMPRODUCT (-ISLOGICAL ($ A $ 1: $ A $ 11))
Shvatimo ovu formulu razbijajući je:
- COUNTIF ($ N $ 8: $ N $ 18, ”?*”) - Ovaj dio formule vraća 5. To uključuje svaku ćeliju koja ima tekstualni znak u sebi. A? predstavlja jedan znak i * predstavlja bilo koji broj znakova. Dakle, kombinacija?* U kriterijima tjera Excel da broji ćelije koje imaju barem jedan tekstualni znak u sebi.
- COUNT ($ A $ 1: $ A $ 11) - Ovo broji sve ćelije koje sadrže brojeve. U gornjem primjeru vraća 3.
- SUMPRODUCT (-ISLOGICAL ($ A $ 1: $ A $ 11)) - Ovo broji sve ćelije koje sadrže logičke vrijednosti. U gornjem primjeru vraća 2.
#3 Brojanje ćelija koje sadrže određeni tekst
Recimo da želimo prebrojati sve ćelije gdje naziv prodajnog predstavnika počinje s J. To se lako može postići korištenjem zamjenskog znaka u funkciji COUNTIF. Evo formule:
= COUNTIFS ($ C $ 2: $ C $ 11, "J*")
Kriterij J* određuje da tekst u ćeliji počinje s J i može sadržavati bilo koji broj znakova.
Ako želite brojati ćelije koje sadrže abecedu bilo gdje u tekstu, obrubite je zvjezdicom s obje strane. Na primjer, ako želite brojati ćelije koje u sebi sadrže abecedu "a", upotrijebite * a * kao kriterij.
Ovaj je članak neobično dug u usporedbi s mojim drugim člancima. Nadam se da ste uživali. Javite mi svoja razmišljanja ostavljajući komentar.
Također bi vam mogli biti korisni sljedeći Excel vodiči:
- Izbrojite broj riječi u Excelu.
- Brojanje ćelija na temelju boje pozadine u Excelu.
- Kako zbrojiti stupac u Excelu (5 zaista lakih načina)
