Všeobecný vzorec
=SUM(--(LEN(UNIQUE(FILTER(range,criteria,"")))>0))
Zhrnutie
Ak chcete počítať jedinečné hodnoty s jednou alebo viacerými podmienkami, môžete použiť vzorec založený na JEDINEČNOM a FILTRE. V zobrazenom príklade je vzorec v H7:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,C6:C15=H6,"")))>0))
ktorá vracia 3, pretože v projekte Omega sú v B6: B15 spojené tri jedinečné názvy.
Poznámka: Tento vzorec vyžaduje vzorce pre dynamické pole, ktoré sú k dispozícii iba v Exceli 365. V staršej verzii Excelu môžete používať zložitejšie alternatívne vzorce.
Vysvetlenie
Tento vzorec v jadre používa funkciu UNIQUE na extrahovanie jedinečných hodnôt a funkcia FILTER použije kritériá.
Pri práci zvnútra sa funkcia FILTER používa na použitie kritérií a extrakciu iba mien, ktoré sú spojené s projektom „Omega“:
FILTER(B6:B15,C6:C15=H6) // Omega names only
Výsledok z FILTERA je pole podobné tomuto:
("Jim";"Jim";"Carl";"Sue";"Carl")
Ďalej sa na odstránenie duplikátov používa funkcia UNIQUE:
UNIQUE(("Jim";"Jim";"Carl";"Sue";"Carl"))
ktorého výsledkom je nové pole, ako je tento:
("Jim";"Carl";"Sue") // after UNIQUE
V tejto chvíli máme jedinečný zoznam mien spojených s Omegou a je treba ich len spočítať. Z dôvodov vysvetlených nižšie to robíme pomocou funkcií LEN a SUM. Aby sme to objasnili, najskôr prepíšeme vzorec tak, aby obsahoval jedinečný zoznam:
=SUM(--(LEN(("Jim";"Carl";"Sue"))>0))
Funkcia LEN získa dĺžku každej položky v zozname a vráti pole dĺžok:
LEN(("Jim";"Carl";"Sue")) // returns (3;4;3)
Ďalej skontrolujeme, či sú dĺžky väčšie ako nula:
LEN((3;4;3)>0 // returns (TRUE;TRUE;TRUE)
A pomocou dvojitého záporu vynútite hodnoty TRUE a FALSE na 1 s a 0 s:
--((TRUE;TRUE;TRUE)) // returns (1;1;1)
Nakoniec výsledky spočítame funkciou SUMA:
=SUM((1;1;1)) // returns 3
Toto pole sa dodáva priamo do funkcie COUNTA, ktorá vracia konečný počet:
=COUNTA(("Jim";"Carl";"Sue")) // returns 3
Upozorňujeme, že pretože kontrolujeme dĺžku každej položky vrátenej UNIQUE, prázdne alebo prázdne bunky, ktoré spĺňajú kritériá, sú ignorované. Tento vzorec je dynamický a pri zmene zdrojových údajov sa okamžite prepočíta.
Počet jedinečných podľa viacerých kritérií
Ak chcete počítať jedinečné hodnoty na základe viacerých kritérií, môžete rozšíriť logiku zahrnutia do filtra FILTER. Napríklad na spočítanie jedinečných mien pre projekt Omega iba v júni použite:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,(C6:C15=H6)*(D6:D15="june"))))>0))
Toto je príklad použitia logickej logiky na použitie viac ako jednej podmienky. Tento prístup je podrobnejšie vysvetlený tu.
Viac podrobností nájdete v tomto tréningovom videu: Ako filtrovať podľa viacerých kritérií.
COUNTA
Je možné napísať jednoduchší vzorec, ktorý odpovie na funkciu COUNTA. Dôležitou výhradou však je, že COUNTA vráti hodnotu 1, ak neexistujú zodpovedajúce hodnoty. Je to tak preto, lebo funkcia FILTER vráti chybu, keď žiadne údaje nezodpovedajú kritériám, a táto chyba sa nakoniec započíta funkciou COUNTA. Základný vzorec COUNTA vyzerá takto:
=COUNTA(UNIQUE(FILTER(B6:B15,C6:C15=H6)))
Tento vzorec opäť vráti hodnotu 1, ak neexistujú žiadne zodpovedajúce údaje. Bude tiež obsahovať prázdne bunky, ktoré spĺňajú kritériá. Vzorec založený na LEN a SUM je lepšou voľbou.
Žiadne dynamické polia
Ak používate staršiu verziu programu Excel bez podpory dynamických polí, môžete použiť zložitejší vzorec. Všeobecnejšiu diskusiu o alternatívach dynamického poľa nájdete v téme: Alternatívy k vzorcom dynamického poľa.
