Excel

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

Count Unique Values With Criteria

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

Ak chcete počítať jedinečné hodnoty s jednou alebo viacerými podmienkami, môžete použiť vzorec založený na JEDINEČNÉ a FILTER . V uvedenom príklade vzorec v H7 je:



= SUM (--( LEN ( UNIQUE ( FILTER (range,criteria,'')))>0))

ktorý vracia 3, pretože v B6: B15 sú tri jedinečné názvy spojené s projektom Omega.

Poznámka: tento vzorec vyžaduje Vzorce dynamického poľa , k dispozícii iba v Excel 365 . So staršou verziou Excelu môžete používať zložitejšie alternatívne vzorce .





Vysvetlenie

V jadre tento vzorec používa funkciu UNIQUE na extrahovanie jedinečných hodnôt a funkciu používa funkcia FILTER.

#num! chyba v exceli

Práca zvnútra von, Funkcia FILTER sa používa na aplikáciu kritérií a extrahovanie iba názvov, ktoré sú spojené s projektom „Omega“:



 
= SUM (--( LEN ( UNIQUE ( FILTER (B6:B15,C6:C15=H6,'')))>0))

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

 
 FILTER (B6:B15,C6:C15=H6) // Omega names only

Ďalej, JEDINEČNÁ funkcia používa sa na odstránenie duplikátov:

 
{'Jim''Jim''Carl''Sue''Carl'}

čo má za následok nové pole takto:

 
 UNIQUE ({'Jim''Jim''Carl''Sue''Carl'})

V tomto mieste máme jedinečný zoznam mien spojených s Omegou a musíme ich len spočítať. Z dôvodov vysvetlených nižšie to robíme s funkciou LEN a funkciou SUM. Aby bolo všetko jasné, najskôr prepíšeme vzorec, aby obsahoval jedinečný zoznam:

 
{'Jim''Carl''Sue'} // after UNIQUE

The LEN function získa dĺžku každej položky v zozname a vráti pole dĺžok:

 
= SUM (--( LEN ({'Jim''Carl''Sue'})>0))

Ďalej skontrolujeme, či sú dĺžky väčšie ako nula:

 
 LEN ({'Jim''Carl''Sue'}) // returns {343}

A použite a dvojitý negatívny na vynútenie hodnôt TRUE a FALSE na 1 s a 0 s:

 
 LEN ({343)>0 // returns {TRUETRUETRUE}

Nakoniec sčítame výsledky pomocou Funkcia SUM :

ako zdieľať súbor programu Excel
 
--({TRUETRUETRUE}) // returns {111}

Toto pole sa doručí priamo do funkcie COUNTA, ktorá vráti konečný počet:

 
= SUM ({111}) // returns 3

Upozorňujeme, že pretože kontrolujeme dĺžku každej položky vrátenej systémom UNIQUE, prázdne alebo prázdne bunky, ktoré spĺňajú kritériá, sa ignorujú. Tento vzorec je dynamický a pri zmene zdrojových údajov sa okamžite prepočíta.

Počítajte jedinečne s viacerými kritériami

Ak chcete počítať jedinečné hodnoty na základe viacerých kritérií, môžete rozšíriť logiku „zahrnúť“ do FILTRA. Ak napríklad chcete počítať jedinečné názvy projektu Omega iba v júni, použite:

 
= COUNTA ({'Jim''Carl''Sue'}) // returns 3

Toto je príklad použitia booleovská logika použiť viac ako jednu podmienku. Prístup je podrobnejšie vysvetlené tu .

funkcia if vyhodnotí (n) jedinú podmienku

Viac podrobností nájdete v tomto školiacom videu: Ako filtrovať podľa viacerých kritérií .

COUNTA

Je možné napísať jednoduchší vzorec, ktorý odpovie na Funkcia COUNTA . Dôležitou výhradou však je, že COUNTA vráti hodnotu 1, ak neexistujú žiadne zodpovedajúce hodnoty. Dôvodom je, že funkcia FILTER vráti chybu, ak kritériám nespĺňajú žiadne údaje, a táto chyba sa skončí započítaním funkciou COUNTA. Základný vzorec COUNTA vyzerá takto:

 
= SUM (--( LEN ( UNIQUE ( FILTER (B6:B15,(C6:C15=H6)*(D6:D15='june'))))>0))

Ak neexistujú žiadne zodpovedajúce údaje, tento vzorec opäť vráti hodnotu 1. Bude tiež zahŕňať prázdne bunky, ktoré spĺňajú kritériá. Vzorec založený na LEN a SUM je lepšou možnosťou.

Žiadne dynamické polia

Ak používate staršiu verziu programu Excel bez podpory dynamického poľa, môžete použiť súbor zložitejší vzorec . Obecnejšiu diskusiu o alternatívach dynamického poľa nájdete v: Alternatívy k vzorcom dynamického poľa .

Vzorce dynamického poľa sú k dispozícii v Office 365 iba. Autor Dave Bruns


^