Všeobecný vzorec
=SUMPRODUCT(--(FREQUENCY(MATCH(data,data,0),ROW(data)-ROW(data.firstcell)+1)>0))
Zhrnutie
Na spočítanie jedinečných textových hodnôt v rozsahu môžete použiť vzorec, ktorý používa niekoľko funkcií: FREQUENCY, MATCH, ROW a SUMPRODUCT. V zobrazenom príklade je vzorec v F5:
=SUMPRODUCT(--(FREQUENCY(MATCH(B5:B14,B5:B14,0),ROW(B5:B14)-ROW(B5)+1)>0))
ktorá vracia 4, pretože v B5 sú 4 jedinečné názvy.
Poznámka: Ďalším spôsobom, ako spočítať jedinečné hodnoty, je použitie funkcie COUNTIF. Toto je oveľa jednoduchší vzorec, ale na veľkých množinách údajov môže bežať pomaly. S programom Excel 365 môžete použiť jednoduchší a rýchlejší vzorec založený na JEDINEČNOM.
Vysvetlenie
Tento vzorec je komplikovanejší ako podobný vzorec, ktorý používa FREQUENCY na spočítanie jedinečných číselných hodnôt, pretože FREQUENCY nefunguje s nečíselnými hodnotami. Vďaka tomu veľká časť vzorca jednoducho transformuje nečíselné údaje na číselné údaje, ktoré dokáže FREKVENCIA spracovať.
Pri práci zvnútra von sa funkcia MATCH používa na získanie polohy každej položky, ktorá sa zobrazuje v dátach:
MATCH(B5:B14,B5:B14,0)
Výsledkom MATCH je pole ako je toto:
(1;1;1;4;4;6;6;6;9;9)
Pretože MATCH vždy vráti pozíciu prvej zhody, hodnoty, ktoré sa v dátach objavia viackrát, vrátia rovnakú pozíciu. Napríklad preto, že „Jim“ sa v zozname nachádza trikrát, zobrazí sa v tomto poli trikrát ako číslo 1.
Toto pole je vkladané do FREQUENCY ako argument data_array . Argument bins_array je zostavený z tejto časti vzorca:
ROW(B5:B14)-ROW(B5)+1)
ktorý vytvára postupný zoznam čísel pre každú hodnotu v dátach:
(1;2;3;4;5;6;7;8;9;10)
V tomto okamihu je FREQUENCY nakonfigurovaná takto:
FREQUENCY((1;1;1;4;4;6;6;6;9;9),(1;2;3;4;5;6;7;8;9;10))
FREQUENCY vráti pole čísel, ktoré označuje počet pre každé číslo v dátovom poli, usporiadané podľa koša. Ak už bolo číslo spočítané, FREQUENCY vráti nulu. Toto je kľúčová vlastnosť fungovania tohto vzorca. Výsledkom z FREQUENCY je pole ako je toto:
(3;0;0;2;0;3;0;0;2;0;0) // output from FREQUENCY
Poznámka: FREQUENCY vždy vráti pole s jednou ďalšou položkou ako bins_array .
Vzorec teraz môžeme prepísať takto:
=SUMPRODUCT(--((3;0;0;2;0;3;0;0;2;0;0)>0))
Ďalej skontrolujeme hodnoty väčšie ako nula (> 0), ktoré prevedú čísla na TRUE alebo FALSE, potom pomocou dvojitého záporného čísla (-) prevedieme hodnoty TRUE a FALSE na 1 s a 0 s. Teraz máme:
=SUMPRODUCT((1;0;0;1;0;1;0;0;1;0;0))
Nakoniec SUMPRODUCT jednoducho sčíta čísla a vráti celkovú hodnotu, ktorá je v tomto prípade 4.
Zaobchádzanie s prázdnymi bunkami
Prázdne bunky v rozsahu spôsobia, že vzorec vráti chybu # N / A. Na spracovanie prázdnych buniek môžete použiť komplikovanejší vzorec poľa, ktorý na filtrovanie prázdnych hodnôt používa funkciu IF:
(=SUM(IF(FREQUENCY(IF(data"", MATCH(data,data,0)),ROW(data)-ROW(data.firstcell)+1),1)))
Poznámka: Pridanie IF to urobí do vzorca poľa, ktorý vyžaduje control-shift-enter.
Ďalšie informácie nájdete na tejto stránke.
Iné spôsoby počítania jedinečných hodnôt
Ak máte Excel 365, môžete pomocou funkcie JEDINEČNÉ počítať jedinečné hodnoty s oveľa jednoduchším vzorcom.
Kontingenčná tabuľka je tiež vynikajúcim spôsobom spočítania jedinečných hodnôt.
