Ovaj vodič prikazuje kako se koristiExcel COUNTIF i COUNTIFS Functions u Excelu za brojanje podataka koji zadovoljavaju određene kriterije.
COUNTIF Pregled funkcija
Pomoću funkcije COUNTIF u Excelu možete brojati ćelije koje sadrže određenu vrijednost, brojati ćelije veće ili jednake vrijednosti itd.
(Obratite pažnju na to kako se pojavljuju unosi formule)
Sintaksa i argumenti funkcije COUNTIF:
= COUNTIF (raspon, kriteriji)
raspon - Raspon stanica za brojanje.
kriterijima - Kriteriji koji kontroliraju koje ćelije treba brojati.
Što je funkcija COUNTIF?
Funkcija COUNTIF jedna je od starijih funkcija koja se koristi u proračunskim tablicama. Jednostavno rečeno, izvrsno je skenirati raspon i reći vam koliko stanica zadovoljava taj uvjet. Pogledat ćemo kako funkcija radi s tekstom, brojevima i datumima; kao i neke druge situacije koje bi mogle nastati.
Osnovni primjer
Počnimo gledajući ovaj popis slučajnih stavki. Imamo neke brojeve, prazne ćelije i neke tekstualne nizove.
Ako želite znati koliko se stavki točno podudara s kriterijima, možete navesti što želite tražiti kao drugi argument. Primjer ove formule bi mogao izgledati
= COUNTIF (A2: A9, "Apple")
Ova bi formula vratila broj 3 jer u našem rasponu postoje 3 ćelije koje zadovoljavaju taj kriterij. Alternativno, možemo upotrijebiti referencu ćelije umjesto tvrdo kodiranja vrijednosti. Kad bismo u ćeliju G2 napisali "Apple", mogli bismo promijeniti formulu u
= COUNTIF (A2: A9, G2)
Kada se bavite brojevima, važno je razlikovati brojeve i brojeve koji su pohranjeni kao tekst. Općenito, ne stavljate navodnike oko brojeva pri pisanju formula. Dakle, da biste napisali formulu koja provjerava broj 5, napisali biste
= COUNTIF (A2: A9, 5)
Konačno, mogli bismo provjeriti i prazne ćelije pomoću niza nulte duljine. Tu bismo formulu zapisali kao
= COUNTIF (A2: A9, "")
Bilješka: Ova će formula brojati obje ćelije koje su doista prazne, kao i one koje su prazne kao rezultat formule, poput IF funkcije.
Djelomična podudaranja
Funkcija COUNTIF podržava korištenje zamjenskih znakova, “*” ili “?”, U kriterijima. Pogledajmo ovaj popis ukusnih pekarskih proizvoda:
Da bismo pronašli sve stavke koje počinju s Appleom, mogli bismo napisati "Apple*". Dakle, da bismo dobili odgovor 3, naša formula u D2 je
= COUNTIF (A2: A5, "Apple*")
Bilješka: Funkcija COUNTIF ne razlikuje velika i mala slova pa biste po želji mogli napisati i "jabuku*".
Vraćajući se na našu pečenu robu, možda bismo htjeli saznati i koliko pita imamo na popisu. To možemo otkriti stavljanjem zamjenskog znaka na početak pojma za pretraživanje i pisanjem
= COUNTIF (A2: A5, "*pita")
Ova formula daje rezultat 2.
Također možemo upotrijebiti zamjenske znakove za provjeru ima li ćelija s tekstom. Vratimo se na naš izvorni popis podataka.
Za prebrojavanje broja ćelija koje imaju barem neki tekst, dakle ne brojeći brojeve ili praznu ćeliju, možemo zapisati
= COUNTIF (A2: A9, "*")
Možete vidjeti da naša formula ispravno vraća rezultat 4.
Operatori usporedbe u COUNTIF
Prilikom dosadašnjeg pisanja kriterija implicirali smo da je naš operator usporedbe "=". Zapravo, ovo smo mogli napisati:
= COUNTIF (A2: A9, "= Apple")
To je ipak dodatni znak za ispisivanje, pa se obično izostavlja. Međutim, to znači da možete koristiti druge operatore kao što su veći od, manji od ili nisu jednaki. Pogledajmo ovaj popis zabilježenih dobi:
Ako želimo znati koliko djece ima najmanje 5 godina, možemo napisati usporedbu "veću ili jednaku" ovako:
= COUNTIF (A2: A8, "> = 5")
Bilješka: Operator usporedbe uvijek je naveden kao tekstualni niz, pa stoga mora biti unutar navodnika.
Slično, možete provjeriti i stavke koje su manje od zadane vrijednosti. Ako moramo saznati koliko je manje od 8, možemo ih zapisati
= COUNTIF (A2: A8, "<8")
To nam daje željeni rezultat od 5. Sada zamislimo da sva 6-godišnja djeca idu u izlet. Koliko će djece ostati? To možemo shvatiti koristeći usporedbu "nije jednaka" poput ove:
= COUNTIF (A2: A8, "6")
Sada možemo brzo vidjeti da imamo 6 djece koja nemaju 6 godina.
U dosadašnjim primjerima usporedbe teško smo kodirali željene vrijednosti. Također možete koristiti referencu ćelije. Trik je u tome što morate povezati operator usporedbe s referencom ćelije. Recimo da stavimo broj 7 u ćeliju C2 i želimo da naša formula u D2 pokaže koliko je djece mlađe od 7 godina.
Naša formula u D2 mora izgledati ovako:
= COUNTIF (A2: A8, "<" & C2)
Bilješka: Obratite posebnu pozornost pri pisanju ovih formula na to trebate li stavku staviti pod navodnike ili izvan nje. Operatori su uvijek unutar navodnika, reference ćelija uvijek su izvan navodnika. Brojevi su vani ako radite točno podudaranje, ali unutra ako radite operator usporedbe.
Rad s datumima
Vidjeli smo kako možete dati tekst ili broj kao kriterij, ali što kada trebamo raditi s datumima? Evo kratkog popisa primjera s kojima možemo raditi:
Kako bismo izbrojali koliko je datuma nakon 4. svibnja, moramo biti oprezni. Računala pohranjuju datume kao brojeve, pa se moramo pobrinuti da računalo koristi pravi broj. Da smo napisali ovu formulu, bismo li dobili točan rezultat?
= COUNTIF (A2: A9, "
Odgovor je "moguće". Budući da smo izbacili godinu iz naših kriterija, računalo će pretpostaviti da mislimo na tekuću godinu. Ako su svi datumi s kojima radimo odnosi se na tekuću godinu, tada ćemo dobiti točan odgovor. Međutim, ako postoje neki datumi u budućnosti, dobili bismo pogrešan odgovor. Također, kad počne sljedeća godina, ova će formula vratiti drugačiji rezultat. Stoga bi se ova sintaksa vjerojatno trebala izbjegavati.
Budući da može biti teško ispravno upisivati datume u formulu, najbolja je praksa da upišete datum koji želite koristiti u ćeliju, a zatim možete upotrijebiti tu referencu ćelije u formuli COUNTIF. Dakle, upišimo datum 7. svibnja 2020. u ćeliju C2, a zatim možemo formulu staviti u C4.
Formula u C4 je
= COUNTIF (A2: A9, "<" & C2)
Sada znamo da je rezultat 7 točan i da se odgovor neće neočekivano promijeniti ako otvorimo ovu proračunsku tablicu negdje u budućnosti.
Prije nego napustimo ovaj odjeljak, uobičajeno je koristiti funkciju TODAY pri radu s datumima. To možemo koristiti baš kao i referencu ćelije. Na primjer, prethodnu formulu bismo mogli promijeniti u sljedeću:
= COUNTIF (A2: A9, "<" & TODAY ())
Sada će se naša formula i dalje ažurirati kako vrijeme odmiče, a imat ćemo i manje stavki nego danas.
Više kriterija i COUNTIFS
Izvorna funkcija COUNTIF poboljšana je 2007. godine kada je izašao COUNTIFS. Sintaksa između njih je vrlo slična, a potonja vam omogućuje da date dodatne raspone i kriterije. COUNTIFS možete jednostavno koristiti u bilo kojoj situaciji u kojoj COUNTIF postoji. Bilo bi dobro znati da obje funkcije postoje.
Pogledajmo ovu tablicu podataka:
Da biste saznali koliko ljudi ima razine plaća 1 do 2, možete napisati sažetak COUNTIF funkcija ovako:
= COUNTIF (B2: B7, "> = 1")-COUNTIF (B2: B7, "> 2")
Ova će formula funkcionirati jer pronalazite sve što je iznad 1, ali tada oduzimate broj zapisa koji su izvan vaše granične točke. Alternativno, možete koristiti COUNTIFS ovako:
= COUNTIFS (B2: B7, "> = 1", B2: B7, "<= 2")
Potonji je intuitivniji za čitanje, pa biste možda htjeli koristiti taj put. Također, COUNTIFS je moćniji kada trebate uzeti u obzir više stupaca. Recimo da želimo znati koliko je ljudi u Upravi i na razini plaće 1. Ne možete to učiniti samo s COUNTIF; morate napisati
= COUNTIFS (A2: A7, "Upravljanje", B2: B7, 1)
Ova formula dala bi vam točan rezultat 2. Prije nego napustimo ovaj odjeljak, razmotrimo logiku tipa Or. Što ako želimo saznati koliko ljudi ima menadžment ili? Morali biste dodati nekoliko COUNTIFS -ova zajedno, ali postoje dva načina za to. Najjednostavniji način je da to napišete ovako:
= COUNTIF (A2: A7, "HR")+COUNTIF (A2: A7, "Upravljanje")
Također možete koristiti niz i napisati ovu formulu niza:
= ZBIR (COUNTIF (A2: A7, {"HR", "Upravljanje"}))
Bilješka: Formule niza moraju biti potvrđene pomoću `Ctrl+Shift+Enter`, a ne samo` Enter`.
Kako će ova formula funkcionirati, vidjet ćete da ste dali niz kao ulaz. Tako će izračunati rezultat na dvije različite funkcije COUNTIF i pohraniti ih u niz. Funkcija SUM tada će zbrajati sve rezultate u našem nizu kako bi napravila jedan izlaz. Stoga će se naša formula vrednovati ovako:
= ZBIR (COUNTIF (A2: A7, {"HR", "Upravljanje"})) = SUM ({2, 3}) = 5
Računajte jedinstvene vrijednosti
Sada kada smo vidjeli kako koristiti niz s funkcijom COUNTIF, možemo otići još jedan korak dalje kako bismo nam pomogli izračunati koliko je jedinstvenih vrijednosti u rasponu. Prvo, pogledajmo ponovno naš popis odjela.
= ZBIR (1/COUNTIF (A2: A7, A2: A7))
Možemo vidjeti da postoji 6 ćelija vrijednih podataka, ali postoje samo 3 različite stavke. Da bi matematika uspjela, trebat će nam da svaka stavka vrijedi 1/N, gdje je N broj ponavljanja stavke. Na primjer, ako je svaki HR vrijedio samo 1/2, tada biste ih zbrajali dobili broj 1 za 1 jedinstvenu vrijednost.
Natrag na naš COUNTIF, koji je osmišljen tako da utvrdi koliko se puta stavka pojavljuje u rasponu. U D2 ćemo napisati formulu niza
= ZBIR (1/COUNTIF (A2: A7, A2: A7))
Kako će ova formula funkcionirati, za svaku ćeliju u rasponu A2: A7 provjerit će se koliko se puta pojavljuje. S našim uzorkom ovo će proizvesti niz
{2, 2, 3, 3, 3, 1}
Zatim sve te brojeve pretvaramo u razlomke djeljenjem. Sada naš niz izgleda ovako
{1/2, 1/2, 1/3, 1/3, 1/3, 1/1}
Kad sve to zbrojimo, dobivamo željeni rezultat 3.
Countif s dva ili više uvjeta - funkcija Countifs
Do sada smo radili samo s funkcijom COUNTIF. Funkcija COUNTIF može istovremeno obraditi samo jedan kriterij. Za COUNTIF s više kriterija morate upotrijebiti funkciju COUNTIFS. COUNTIFS se ponaša točno kao COUNTIF. Samo dodate dodatne kriterije. Pogledajmo donji primjer.
= COUNTIFS (B2: B7, "= 130")
COUNTIF & COUNTIFS u Google tablicama
Funkcija COUNTIF & COUNTIFS radi potpuno isto u Google tablicama kao i u Excelu:
