Generička formula
=SUMPRODUCT(--(FREQUENCY(MATCH(data,data,0),ROW(data)-ROW(data.firstcell)+1)>0))
Sažetak
Za brojanje jedinstvenih tekstualnih vrijednosti u rasponu možete upotrijebiti formulu koja koristi nekoliko funkcija: FREQUENCY, MATCH, ROW i SUMPRODUCT. U prikazanom primjeru formula u F5 je:
=SUMPRODUCT(--(FREQUENCY(MATCH(B5:B14,B5:B14,0),ROW(B5:B14)-ROW(B5)+1)>0))
koji vraća 4, jer u B5 postoje 4 jedinstvena imena: B14.
Napomena: Drugi način za brojanje jedinstvenih vrijednosti je upotreba funkcije COUNTIF. Ovo je puno jednostavnija formula, ali može sporo raditi na velikim skupovima podataka. Uz Excel 365 možete koristiti jednostavniju i bržu formulu koja se temelji na JEDINSTVENOJ.
Obrazloženje
Ova je formula složenija od slične formule koja koristi FREQUENCY za brojanje jedinstvenih numeričkih vrijednosti jer FREQUENCY ne radi s ne-numeričkim vrijednostima. Kao rezultat toga, velik dio formule jednostavno pretvara nenumeričke podatke u numeričke podatke s kojima FREQUENCY može obraditi.
Funkcionirajući iznutra prema van, funkcija MATCH koristi se za dobivanje položaja svake stavke koja se pojavljuje u podacima:
MATCH(B5:B14,B5:B14,0)
Rezultat iz MATCH-a je niz poput ovog:
(1;1;1;4;4;6;6;6;9;9)
Budući da MATCH uvijek vraća položaj prvog podudaranja, vrijednosti koje se pojavljuju više puta u podacima vraćaju isto mjesto. Na primjer, jer se "Jim" na popisu pojavljuje 3 puta, u ovom se nizu prikazuje 3 puta kao broj 1.
Ovaj se niz uvodi u FREQUENCY kao argument data_array . Bins_array argument konstruiran iz ovog dijela formule:
ROW(B5:B14)-ROW(B5)+1)
koja gradi sekvencijalni popis brojeva za svaku vrijednost u podacima:
(1;2;3;4;5;6;7;8;9;10)
U ovom trenutku, FREQUENCY je konfiguriran ovako:
FREQUENCY((1;1;1;4;4;6;6;6;9;9),(1;2;3;4;5;6;7;8;9;10))
FREQUENCY vraća niz brojeva koji označavaju broj za svaki broj u podatkovnom polju, organiziran od strane bin-a. Kada je broj već izbrojan, FREQUENCY će vratiti nulu. To je ključna značajka u radu ove formule. Rezultat iz FREQUENCY je niz poput ovog:
(3;0;0;2;0;3;0;0;2;0;0) // output from FREQUENCY
Napomena: FREQUENCY uvijek vraća niz s još jednom stavkom od polja bins_array .
Sada formulu možemo prepisati ovako:
=SUMPRODUCT(--((3;0;0;2;0;3;0;0;2;0;0)>0))
Zatim provjeravamo ima li vrijednosti veće od nule (> 0), što brojeve pretvara u TRUE ili FALSE, a zatim dvostrukim negativom (-) pretvaramo vrijednosti TRUE i FALSE u 1s i 0s. Sad imamo:
=SUMPRODUCT((1;0;0;1;0;1;0;0;1;0;0))
Konačno, SUMPRODUCT jednostavno zbraja brojeve i vraća zbroj, koji je u ovom slučaju 4.
Rukovanje praznim stanicama
Prazne ćelije u rasponu natjerat će formulu na pogrešku # N / A. Za rukovanje praznim stanicama možete koristiti složeniju formulu niza koja koristi funkciju IF za filtriranje praznih vrijednosti:
(=SUM(IF(FREQUENCY(IF(data"", MATCH(data,data,0)),ROW(data)-ROW(data.firstcell)+1),1)))
Napomena: dodavanjem IF ovo se pretvara u formulu niza koja zahtijeva control-shift-enter.
Za više informacija pogledajte ovu stranicu.
Ostali načini za brojanje jedinstvenih vrijednosti
Ako imate Excel 365, možete koristiti funkciju UNIQUE za brojanje jedinstvenih vrijednosti s puno jednostavnijom formulom.
Pivot tablica također je izvrstan način za brojanje jedinstvenih vrijednosti.
