Excel

Spočítajte jedinečné textové hodnoty s kritériami

Count Unique Text Values With Criteria

Vzorec programu Excel: Spočítajte jedinečné textové hodnoty s kritériamiGenerický vzorec | _+_ | Zhrnutie

Ak chcete počítať jedinečné textové hodnoty v rozsahu s kritériami, môžete použiť vzorec poľa na základe FREKVENCIA a ZÁPAS funkcie. V uvedenom príklade vzorec v G6 je:





{= SUM (--( FREQUENCY ( IF (criteria, MATCH (vals,vals,0)), ROW (vals)- ROW (vals.first)+1)>0))}

ktorý vracia 3, pretože na projekte Omega pracovali traja rôzni ľudia.

Poznámka: toto je an maticový vzorec a je potrebné ho zadať kombináciou klávesov Ctrl + Shift + Enter.





S Excel 365 , môžete použiť a oveľa jednoduchší vzorec založený na JEDINEČNÁ funkcia . Vysvetlenie

Toto je komplexný vzorec, ktorý používa FREQUENCY na počítanie číselných hodnôt odvodených od funkcie MATCH. Funkcia MATCH, ktorá funguje zvnútra von, sa používa na získanie polohy každej hodnoty, ktorá sa zobrazuje v dátach:

 
{= SUM (--( FREQUENCY ( IF (C5:C11=G5, MATCH (B5:B11,B5:B11,0)), ROW (B5:B11)- ROW (B5)+1)>0))}

Výsledkom MATCH je pole ako toto:



 
 MATCH (B5:B11,B5:B11,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 štyrikrát, zobrazí sa v tomto poli štyrikrát ako číslo 1.

Mimo funkcie MATCH, IF funkcia sa používa na uplatňovanie kritérií, ktoré v tomto prípade zahŕňajú testovanie, či je projekt „omega“ (z bunky G5):

 
{1131167}

Funkcia IF funguje ako filter a umožňuje prechod hodnotám z MATCH iba vtedy, ak sú spojené s „omega“. Výsledkom je pole ako toto:

 
 IF (C5:C11=G5 // filter on 'omega'

Filtrované pole sa dodáva priamo do funkcie FREQUENCY ako data_array argument. Ďalej, Funkcia ROW sa používa na stavbu a postupný zoznam čísel pre každú hodnotu v údajoch:

 
{FALSEFALSEFALSE1167} // after filtering

Tým sa vytvorí pole takto:

 
 ROW (B3:B12)- ROW (B3)+1

ktorá sa stáva bins_array argument vo FILTRE. V tomto bode máme:

 
{12345678910}

FREQUENCY vráti pole čísel, ktoré udávajú počet pre každú hodnotu v poli údajov, usporiadanú podľa bin. Keď je číslo už spočítané, FREQUENCY vráti nulu. Výsledkom z FREQUENCY je pole ako toto:

 
 FREQUENCY ({FALSEFALSEFALSE1167},{1234567})

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

ako používať absolútny odkaz na bunku

V tomto mieste môžeme vzorec prepísať takto:

 
{20000110} // result from FREQUENCY

Skontrolujeme hodnoty väčšie ako nula, čím sa čísla prevedú na PRAVDU alebo NEPRAVDU:

 
= SUM (--({20000110}>0))

Potom použijeme a dvojito negatívny vynútiť logické hodnoty na 1 s a 0 s:

 
= SUM (--({TRUEFALSEFALSEFALSEFALSETRUETRUEFALSE}))

Nakoniec, Funkcia SUM vráti 3 ako konečný výsledok.

Poznámka: toto je vzorec poľa a musíte ho zadať pomocou klávesov Ctrl + Shift + Enter.

Správa prázdnych buniek v rozsahu

Ak sú niektoré bunky v rozsahu prázdne, budete musieť upraviť vzorec, aby ste zabránili prenosu prázdnych buniek do funkcie MATCH, ktorá spôsobí chybu. Môžete to urobiť pridaním ďalšej vnorenej funkcie IF na kontrolu prázdnych buniek:

 
= SUM ({10000110})

S dvoma kritériami

Ak máte dve kritériá, môžete logiku vzorca rozšíriť pridaním ďalšieho vnoreného IF:

 
{= SUM (--( FREQUENCY ( IF (B5:B11'', IF (C5:C11=G5, MATCH (B5:B11,B5:B11,0))), ROW (B5:B11)- ROW (B5)+1)>0))}

Kde c1 = kritérium1, c2 = kritériá2 a valčík = rozsah hodnôt.

S booleovskou logikou

S booleovská logika , môžete znížiť vnorené IF :

 
{= SUM (--( FREQUENCY ( IF (c1, IF (c2, MATCH (vals,vals,0))), ROW (vals)- ROW (vals.1st)+1)>0))}

To uľahčuje pridávanie a správu ďalších kritérií.

Prevzaté z Mike Givin vynikajúca kniha o vzorcoch poľa, Ctrl-Shift-Enter. Autor Dave Bruns


^