Excel

Spočítajte jedinečné textové hodnoty v rozsahu

Count Unique Text Values Range

Vzorec programu Excel: Spočítajte jedinečné textové hodnoty v rozsahuGenerický vzorec | _+_ | Zhrnutie

Na počítanie jedinečných textových hodnôt v rozsahu môžete použiť vzorec, ktorý používa niekoľko funkcií: FREKVENCIA , ZÁPAS , RIADOK a SUMPRODUCT .V uvedenom príklade vzorec v F5 je:





= SUMPRODUCT (--( FREQUENCY ( MATCH (data,data,0), ROW (data)- ROW (data.firstcell)+1)>0))

ktorý vráti 4, pretože v B5: B14 sú 4 jedinečné mená.

Poznámka: Ďalším spôsobom, ako počítať jedinečné hodnoty, je použite funkciu COUNTIF . Toto je oveľa jednoduchší vzorec, ale vo veľkých množinách údajov môže bežať pomaly. S Excel 365 , môžete použiť a jednoduchší a rýchlejší vzorec založené na JEDINEČNÉ .





Vysvetlenie

Tento vzorec je komplikovanejší ako podobný vzorec, ktorý používa na FREQUENCY počítať jedinečné číselné hodnoty pretože FREQUENCY nefunguje s inými ako číselnými hodnotami. Výsledkom je, že veľká časť vzorca jednoducho transformuje nečíselné údaje na číselné údaje, ktoré FREQUENCY zvládne.

Funkcia MATCH, ktorá funguje zvnútra von, sa používa na získanie polohy každej položky, ktorá sa zobrazuje v dátach:



 
= SUMPRODUCT (--( FREQUENCY ( MATCH (B5:B14,B5:B14,0), ROW (B5:B14)- ROW (B5)+1)>0))

Výsledkom z MATCH je pole Páči sa ti to:

 
 MATCH (B5:B14,B5:B14,0)

Pretože MATCH vždy vráti pozíciu súboru najprv zhoda, hodnoty, ktoré sa v údajoch zobrazujú viac ako raz, vracajú 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 privádzané do FREQUENCY ako data_array argument.The bins_array argument je zostavený z tejto časti vzorca:

ako vnoriť, ak funguje v programe Excel
 
{1114466699}

ktorá stavia a postupný zoznam čísel pre každú hodnotu v údajoch:

 
 ROW (B5:B14)- ROW (B5)+1)

V tomto bode je FREQUENCY nakonfigurovaná takto:

 
{12345678910}

FREQUENCY vráti pole čísel, ktoré udávajú počet pre každé číslo v dátovom poli, usporiadané podľa bin. Keď je číslo už spočítané, FREQUENCY vráti nulu. Toto je kľúčová vlastnosť fungovania tohto vzorca. Výsledkom z FREQUENCY je pole ako toto:

ako urobiť číslo v programe Excel absolútnym
 
 FREQUENCY ({1114466699},{12345678910})

Poznámka: FREQUENCY vždy vráti pole s jednou ďalšou položkou ako bins_array .

Teraz môžeme vzorec prepísať takto:

 
{30020300200} // output from FREQUENCY

Ďalej skontrolujeme hodnoty vyššie ako nula (> 0), ktoré prevedú čísla na TRUE alebo FALSE, a potom použijeme dvojitý zápor (-) na konverziu hodnôt TRUE a FALSE na 1 s a 0 s. Teraz tu máme:

 
= SUMPRODUCT (--({30020300200}>0))

Nakoniec SUMPRODUCT jednoducho spočíta čísla a vráti súčet, ktorý je v tomto prípade 4.

Manipulácia 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ý používa funkciu IF na odfiltrovanie prázdnych hodnôt:

 
= SUMPRODUCT ({10010100100})

Poznámka: pridaním IF sa toto zmení na súbor maticový vzorec to vyžaduje ovládanie-posun-zadanie.

Pre viac informácií, pozri túto stránku .

Od Mike Givin vynikajúca kniha o vzorcoch poľa, Ctrl-Shift-Enter.

Ďalšie spôsoby počítania jedinečných hodnôt

Ak máte Excel 365, môžete použiť JEDINEČNÁ funkcia do počítať jedinečné hodnoty s oveľa jednoduchším vzorcom.

TO kontingenčná tabuľka je tiež vynikajúcim spôsobom, ako počítať jedinečné hodnoty.

Autor Dave Bruns


^