Obecný vzorec
=SUMPRODUCT(--(FREQUENCY(MATCH(data,data,0),ROW(data)-ROW(data.firstcell)+1)>0))
souhrn
Chcete-li spočítat jedinečné textové hodnoty v rozsahu, můžete použít vzorec, který používá několik funkcí: FREQUENCY, MATCH, ROW a SUMPRODUCT. V zobrazeném příkladu je vzorec v F5:
=SUMPRODUCT(--(FREQUENCY(MATCH(B5:B14,B5:B14,0),ROW(B5:B14)-ROW(B5)+1)>0))
který vrací 4, protože v B5 jsou 4 jedinečná jména: B14.
Poznámka: Dalším způsobem, jak spočítat jedinečné hodnoty, je použití funkce COUNTIF. Toto je mnohem jednodušší vzorec, ale na velkých souborech dat může běžet pomalu. S Excel 365 můžete použít jednodušší a rychlejší vzorec založený na UNIQUE.
Vysvětlení
Tento vzorec je složitější než podobný vzorec, který používá FREQUENCY k počítání jedinečných číselných hodnot, protože FREQUENCY nefunguje s nečíselnými hodnotami. Výsledkem je, že velká část vzorce jednoduše transformuje nečíselná data na numerická data, která FREQUENCY zvládne.
Při práci zevnitř ven se funkce MATCH používá k získání polohy každé položky, která se objeví v datech:
MATCH(B5:B14,B5:B14,0)
Výsledkem MATCH je pole jako toto:
(1;1;1;4;4;6;6;6;9;9)
Protože MATCH vždy vrátí pozici první shody, hodnoty, které se v datech objeví více než jednou, vrátí stejnou pozici. Například protože „Jim“ se v seznamu objeví třikrát, zobrazí se v tomto poli třikrát jako číslo 1.
Toto pole se přivádí do FREQUENCY jako argument data_array . Argument bins_array je sestaven z této části vzorce:
ROW(B5:B14)-ROW(B5)+1)
který vytváří sekvenční seznam čísel pro každou hodnotu v datech:
(1;2;3;4;5;6;7;8;9;10)
V tomto okamžiku je FREQUENCY nakonfigurována takto:
FREQUENCY((1;1;1;4;4;6;6;6;9;9),(1;2;3;4;5;6;7;8;9;10))
FREQUENCY vrací pole čísel, které označuje počet pro každé číslo v datovém poli, uspořádané podle bin. Pokud již bylo číslo spočítáno, FREQUENCY vrátí nulu. Toto je klíčová vlastnost fungování tohoto vzorce. Výsledkem FREQUENCY je pole jako toto:
(3;0;0;2;0;3;0;0;2;0;0) // output from FREQUENCY
Poznámka: FREQUENCY vždy vrátí pole s jednou další položkou než bins_array .
Nyní můžeme vzorec přepsat takto:
=SUMPRODUCT(--((3;0;0;2;0;3;0;0;2;0;0)>0))
Dále zkontrolujeme hodnoty větší než nula (> 0), které převede čísla na TRUE nebo FALSE, a pak pomocí dvojitého záporného čísla (-) převedeme hodnoty TRUE a FALSE na 1 s a 0 s. Nyní máme:
=SUMPRODUCT((1;0;0;1;0;1;0;0;1;0;0))
Nakonec SUMPRODUCT jednoduše sčítá čísla a vrátí součet, který je v tomto případě 4.
Zpracování prázdných buněk
Prázdné buňky v rozsahu způsobí, že vzorec vrátí chybu # N / A. Chcete-li zpracovat prázdné buňky, můžete použít složitější vzorec pole, který k odfiltrování prázdných hodnot používá funkci IF:
(=SUM(IF(FREQUENCY(IF(data"", MATCH(data,data,0)),ROW(data)-ROW(data.firstcell)+1),1)))
Poznámka: přidání IF způsobí, že toto bude do maticového vzorce, který vyžaduje control-shift-enter.
Další informace najdete na této stránce.
Jiné způsoby počítání jedinečných hodnot
Pokud máte Excel 365, můžete pomocí funkce JEDINEČNÉ spočítat jedinečné hodnoty s mnohem jednodušším vzorcem.
Kontingenční tabulka je také vynikající způsob, jak počítat jedinečné hodnoty.
