Generička formula
=SUM(--(LEN(UNIQUE(FILTER(range,criteria,"")))>0))
Sažetak
Za brojanje jedinstvenih vrijednosti s jednim ili više uvjeta možete upotrijebiti formulu koja se temelji na UNIQUE i FILTER. U prikazanom primjeru formula u H7 je:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,C6:C15=H6,"")))>0))
koji vraća 3, jer u B6 postoje tri jedinstvena imena: B15 povezana s projektom Omega.
Napomena: ova formula zahtijeva dinamičke formule nizova, dostupne samo u Excelu 365. Sa starijom verzijom Excela možete koristiti složenije alternativne formule.
Obrazloženje
U osnovi, ova formula koristi funkciju UNIQUE za izdvajanje jedinstvenih vrijednosti, a funkcija FILTER primjenjuje kriterije.
Funkcionirajući iznutra prema van, funkcija FILTER koristi se za primjenu kriterija i izdvajanje samo imena koja su povezana s projektom "Omega":
FILTER(B6:B15,C6:C15=H6) // Omega names only
Rezultat iz FILTER-a je niz poput ovog:
("Jim";"Jim";"Carl";"Sue";"Carl")
Dalje, funkcija UNIQUE koristi se za uklanjanje duplikata:
UNIQUE(("Jim";"Jim";"Carl";"Sue";"Carl"))
što rezultira novim nizom poput ovog:
("Jim";"Carl";"Sue") // after UNIQUE
U ovom trenutku imamo jedinstveni popis imena povezanih s Omegom, i samo ih trebamo prebrojati. Iz dolje objašnjenih razloga to radimo s funkcijom LEN i funkcijom SUM. Da bismo stvari učinili jasnima, prvo ćemo prepisati formulu tako da uključuje jedinstveni popis:
=SUM(--(LEN(("Jim";"Carl";"Sue"))>0))
Funkcija LEN dobiva duljinu svake stavke na popisu i vraća niz duljina:
LEN(("Jim";"Carl";"Sue")) // returns (3;4;3)
Dalje provjeravamo jesu li duljine veće od nule:
LEN((3;4;3)>0 // returns (TRUE;TRUE;TRUE)
I upotrijebite dvostruki negativ da prisilite vrijednosti TRUE i FALSE na 1s i 0s:
--((TRUE;TRUE;TRUE)) // returns (1;1;1)
Na kraju zbrajamo rezultate s funkcijom SUM:
=SUM((1;1;1)) // returns 3
Ovaj se niz isporučuje izravno funkciji COUNTA, koja vraća konačni broj:
=COUNTA(("Jim";"Carl";"Sue")) // returns 3
Imajte na umu da se, jer provjeravamo duljinu svake stavke koju vraća UNIQUE, prazne ili prazne ćelije koje udovoljavaju kriterijima zanemaruju. Ova je formula dinamična i odmah će se preračunati ako se promijene izvorni podaci.
Broji jedinstveno s više kriterija
Da bi brojao jedinstvene vrijednosti na temelju više kriterija, može proširiti logiku "uključi" unutar FILTERA. Na primjer, za brojanje jedinstvenih imena za projekt Omega samo u lipnju, upotrijebite:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,(C6:C15=H6)*(D6:D15="june"))))>0))
Ovo je primjer korištenja logičke logike za primjenu više od jednog uvjeta. Pristup je ovdje detaljnije objašnjen.
Za više detalja pogledajte ovaj videozapis s treningom: Kako filtrirati s više kriterija.
TAČKA
Moguće je napisati jednostavniju formulu koja odgovara na funkciju COUNTA. Međutim, važno je upozorenje da će COUNTA vratiti 1 kada nema odgovarajućih vrijednosti. To je zato što funkcija FILTER vraća pogrešku kada se niti jedan podatak ne podudara s kriterijima, a funkcija COUNTA na kraju broji ovu pogrešku. Osnovna formula COUNTA izgleda ovako:
=COUNTA(UNIQUE(FILTER(B6:B15,C6:C15=H6)))
Opet, ova će formula vratiti 1 kada nema odgovarajućih podataka. Također će sadržavati prazne stanice koje udovoljavaju kriterijima. Formula koja se temelji na LEN i SUM bolja je opcija.
Nema dinamičkih nizova
Ako upotrebljavate stariju verziju Excela bez podrške za dinamički niz, možete upotrijebiti složeniju formulu. Za općenitiju raspravu o mogućnostima dinamičkih nizova, pogledajte: Alternative formulama dinamičkih nizova.
