U ovom ćete vodiču naučiti kako brojati jedinstvene vrijednosti u Excelu pomoću formula (funkcije COUNTIF i SUMPRODUCT).
Kako brojati jedinstvene vrijednosti u Excelu
Recimo da imamo skup podataka kako je prikazano u nastavku:
U svrhu ovog vodiča nazvat ću raspon A2: A10 kao IMENA. Ubuduće ćemo koristiti ovaj imenovani raspon u formulama.
Vidi također: Kako stvoriti imenovane raspone u Excelu.
U ovom skupu podataka postoji ponavljanje u rasponu NAMES. Da bismo dobili broj jedinstvenih imena iz ovog skupa podataka (A2: A10), možemo upotrijebiti kombinaciju funkcija COUNTIF i SUMPRODUCT kako je prikazano u nastavku:
= SUMPRODUCT (1/COUNTIF (IMENA, IMENA))
Kako funkcionira ova formula?
Razložimo ovu formulu da bismo bolje razumjeli:
- COUNTIF (IMENA, IMENA)
- Ovaj dio formule vraća niz. U gornjem primjeru to bi bilo {2; 2; 3; 1; 3; 1; 2; 3; 2}. Ovdje brojevi pokazuju koliko se puta vrijednost pojavljuje u danom rasponu ćelija.
Na primjer, ime je Bob, koje se dva puta pojavljuje na popisu, stoga bi vratilo broj 2 za Boba. Slično, Steve se javlja tri puta i stoga se Steve vraća 3.
- Ovaj dio formule vraća niz. U gornjem primjeru to bi bilo {2; 2; 3; 1; 3; 1; 2; 3; 2}. Ovdje brojevi pokazuju koliko se puta vrijednost pojavljuje u danom rasponu ćelija.
- 1/COUNTIF (IMENA, IMENA)
- Ovaj dio formule vratio bi niz - {0,5; 0,5; 0,333333333333333; 1; 0,333333333333333; 1; 0,5; 0,333333333333333; 0,5}
Budući da smo 1 podijelili nizom, on vraća ovaj niz.
Na primjer, prvi element niza koji je gore vraćen bio je 2. Kad se 1 podijeli s 2, vraća se .5.
- Ovaj dio formule vratio bi niz - {0,5; 0,5; 0,333333333333333; 1; 0,333333333333333; 1; 0,5; 0,333333333333333; 0,5}
- SUMPRODUCT (1/COUNTIF (IMENA, IMENA))
- SUMPRODUCT jednostavno zbraja sve ove brojeve. Imajte na umu da ako se Bob dva puta pojavi na popisu, gornji niz vraća .5 gdje god se ime Boba pojavilo na popisu. Slično, budući da se Steve tri puta pojavljuje na popisu, niz vraća .3333333 kad god se Steveovo ime pojavi. Kad dodamo brojeve za svako ime, uvijek bi se vratilo 1. A ako zbrojimo sve brojeve, vratilo bi se ukupan broj jedinstvenih imena na popisu.
Ova formula radi dobro dok nemate praznih ćelija u rasponu. Ali ako imate prazne ćelije, vratilo bi se #DIV/0! pogreška.
Kako postupati s BLANK ćelijama?
Shvatimo prvo zašto vraća pogrešku kada se u rasponu nalazi prazna ćelija. Pretpostavimo da imamo skup podataka kao što je prikazano u nastavku (s ćelijom A3 koja je prazna):
Ako sada koristimo istu formulu koju smo koristili gore, dio formule COUNTIF vraća niz {2; 0; 3; 1; 3; 1; 2; 3; 1}. Budući da u ćeliji A3 nema teksta, njegov se broj vraća kao 0.
A budući da 1 dijelimo cijelim nizom, vraća #DIV/0! pogreška.
Za rješavanje ove pogreške podjele u slučaju praznih ćelija, upotrijebite donju formulu:
= SUMPRODUCT ((1/COUNTIF (IMENA, IMENA & ””)))
Jedna promjena koju smo unijeli u ovu formulu je dio kriterija funkcije COUNTIF. Koristili smo NAMES & ”” umjesto NAMES. Time bi formula vratila broj praznih ćelija (ranije je vraćala 0 gdje je bila prazna ćelija).
NAPOMENA: Ova bi formula prazne ćelije brojala kao jedinstvenu vrijednost i vraćala u rezultat.
U gornjem primjeru rezultat bi trebao biti 5, ali vraća 6 jer se prazna ćelija računa kao jedna od jedinstvenih vrijednosti.
Evo formule koja vodi računa o praznim ćelijama i ne računa je u konačni rezultat:
= SUMPRODUCT ((NAMES ””)/COUNTIF (NAMES, NAMES & ””))
U ovoj formuli, umjesto 1 kao brojioca, koristili smo IMENA ””. Ovo vraća niz TRUE i FALSEs. Vraća FALSE kad god postoji prazna ćelija. Budući da je TRUE jednako 1, a FALSE jednako 0 u izračunima, prazne ćelije se ne broje kao brojnik 0 (FALSE).
Sada kada imamo pripremljen osnovni kostur formule, možemo otići korak dalje i prebrojati različite vrste podataka.
Kako prebrojati jedinstvene vrijednosti u Excelu koje su tekstualne
Koristit ćemo isti gore opisani koncept za stvaranje formule koja će brojati samo jedinstvene tekstualne vrijednosti.
Evo formule koja će brojati jedinstvene tekstualne vrijednosti u Excelu:
= SUMPRODUCT ((ISTEXT (NAMES)/COUNTIF (NAMES, NAMES & ””)))
Sve što smo učinili koristi formulu ISTEXT (NAMES) kao brojnik. Vraća TRUE kada ćelija sadrži tekst, a FALSE ako nema. Neće brojati prazne ćelije, nego će bilježiti ćelije koje imaju prazan niz ("").
Kako brojati jedinstvene vrijednosti u Excelu koje su numeričke
Ovdje je formula koja će brojati jedinstvene numeričke vrijednosti u Excelu
= SUMPRODUCT ((ISNUMBER (NAMES))/COUNTIF (NAMES, NAMES & ””))
Ovdje koristimo ISNUMBER (NAMES) kao brojnik. Vraća TRUE kada ćelija sadrži numeričku vrstu podataka, a FALSE ako ne sadrži. Ne broji prazne ćelije.
