Generička formula
=SUMPRODUCT(1/COUNTIF(data,data))
Sažetak
Da biste prebrojali broj jedinstvenih vrijednosti u rasponu ćelija, možete koristiti formulu koja se temelji na funkcijama COUNTIF i SUMPRODUCT. U primjeru emisije, formula u F6 je:
=SUMPRODUCT(1/COUNTIF(B5:B14,B5:B14))
Obrazloženje
Radeći iznutra prema van, COUNTIF je konfiguriran na vrijednosti u rasponu B5: B14, koristeći sve ove iste vrijednosti kao kriterije:
COUNTIF(B5:B14,B5:B14)
Budući da za kriterije dajemo 10 vrijednosti, vratit ćemo niz s 10 rezultata poput ovog:
(3;3;3;2;2;3;3;3;2;2)
Svaki broj predstavlja broj - "Jim" se pojavljuje 3 puta, "Sue" se pojavljuje 2 puta, i tako dalje.
Ovaj je niz konfiguriran kao djelitelj s 1 kao brojnikom. Nakon dijeljenja dobivamo još jedan niz:
(0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5)
Sve vrijednosti koje se pojave u samo jednom u rasponu prikazat će se kao 1s, ali vrijednosti koje se pojave više puta pojavit će se kao razlomljene vrijednosti koje odgovaraju višestrukom. (tj. vrijednost koja se u podacima pojavi 4 puta generirat će 4 vrijednosti = 0,25).
Konačno, funkcija SUMPRODUCT zbraja sve vrijednosti u polju i vraća rezultat.
Rukovanje praznim stanicama
Jedan od načina za rukovanje praznim ili praznim stanicama je prilagodba formule na sljedeći način:
=SUMPRODUCT(1/COUNTIF(data,data&""))
Spajanjem praznog niza ("") s podacima sprečavamo da nule završe u polju stvorenom od COUNTIF kada u podacima postoje prazne ćelije. To je važno, jer će nula u djelitelju uzrokovati da formula generira pogrešku # DIV / 0. Djeluje jer će upotreba praznog niza ("") za kriterije brojati prazne stanice.
Međutim, iako ova verzija formule neće stvoriti pogrešku # DIV / 0 kada je prazna stanica, ona će ubrojiti prazne stanice. Ako želite izuzeti prazne stanice iz broja, upotrijebite:
=SUMPRODUCT((data"")/COUNTIF(data,data&""))
To ima za posljedicu poništavanje broja praznih ćelija postavljanjem brojača na nulu za pridruženi broj.
Spore performanse?
Ovo je cool i elegantna formula, ali izračunava se puno sporije od formula koje koriste FREQUENCY za brojanje jedinstvenih vrijednosti. Za veće skupove podataka možda ćete htjeti prijeći na formulu koja se temelji na funkciji FREQUENCY. Evo formule za numeričke vrijednosti i jednu za tekstne vrijednosti.
