Generička formula
(=SUM(--(FREQUENCY(IF(criteria,values),values)>0)))
Sažetak
Za brojanje jedinstvenih numeričkih vrijednosti u rasponu možete upotrijebiti formulu koja se temelji na funkcijama FREQUENCY, SUM i IF. U prikazanom primjeru brojevi zaposlenika pojavljuju se u rasponu B5: B14. Formula u G6 je:
=SUM(--(FREQUENCY(IF(C5:C14="A",B5:B14),B5:B14)>0))
koji vraća 2, jer postoje 2 jedinstvena ID-a zaposlenika u zgradi A.
Napomena: ovo je formula niza i mora se unijeti sa control + shift + enter, osim ako ne koristite Excel 365.
Obrazloženje
Napomena: Prije programa Excel 365, Excel nije imao namjensku funkciju za brojanje jedinstvenih vrijednosti. Ova formula pokazuje jedan način za brojanje jedinstvenih vrijednosti, pod uvjetom da su numeričke. Ako imate tekstualne vrijednosti ili kombinaciju teksta i brojeva, morat ćete upotrijebiti složeniju formulu.
Funkcija Excel FREQUENCY vraća frekvencijsku raspodjelu, koja je sažeta tablica koja sadrži učestalost numeričkih vrijednosti, organizirane u "kante". Ovdje ga koristimo kao zaobilazni način za brojanje jedinstvenih numeričkih vrijednosti. Za primjenu kriterija koristimo funkciju IF.
Radeći iznutra prema van, prvo filtriramo vrijednosti pomoću funkcije IF:
IF(C5:C14="A",B5:B14) // filter on building A
Rezultat ove operacije je niz poput ovog:
(905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE)
Primijetite da su svi ID-ovi u zgradi B sada LAŽNI. Taj se niz isporučuje izravno funkciji FREQUENCY kao niz podataka . Za bins_array dostavljamo same ID-ove:
FREQUENCY((905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE),(905;905;905;905;773;773;801;963;963;963))
S ovom konfiguracijom, FREQUENCY vraća niz ispod:
(4;0;0;0;2;0;0;0;0;0;0)
Rezultat je pomalo tajnovit, ali značenje 905 pojavljuje se četiri puta, a 773 pojavljuje se dva puta. Vrijednosti FALSE automatski se zanemaruju.
FREQUENCY ima posebnu značajku koja automatski vraća nulu za sve brojeve koji su se već pojavili u podatkovnom polju, zbog čega su vrijednosti nule nakon što se nađe broj. To je značajka koja omogućuje da ovaj pristup djeluje.
Zatim se testira da je svaka od ovih vrijednosti veća od nule:
(4;0;0;0;2;0;0;0;0;0;0)>0
Rezultat je niz poput ovog:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Svaka ISTINA na popisu predstavlja jedinstveni broj na popisu, a mi samo trebamo zbrojiti Vrijednosti TRUE s SUM. Međutim, SUM neće zbrajati logičke vrijednosti u nizu, pa moramo prvo prisiliti vrijednosti na 1 ili nulu. To se radi s dvostrukim negativom (-). Rezultat je niz od samo 1 ili 0:
(1;0;0;0;1;0;0;0;0;0;0)
Konačno, SUM zbraja te vrijednosti i vraća zbroj, koji je u ovom slučaju 2.
Više kriterija
Formulu možete proširiti tako da obrađuje više kriterija poput ovog:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),values),values)>0)))
