Obecný vzorec
=SUM(--(LEN(UNIQUE(FILTER(range,criteria,"")))>0))
souhrn
Chcete-li počítat jedinečné hodnoty s jednou nebo více podmínkami, můžete použít vzorec založený na UNIQUE a FILTER. V zobrazeném příkladu je vzorec v H7:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,C6:C15=H6,"")))>0))
který vrací 3, protože v projektu B6 jsou tři jedinečná jména spojená s B6: B15 spojená s projektem Omega.
Poznámka: Tento vzorec vyžaduje vzorce pro dynamické pole, které jsou k dispozici pouze v aplikaci Excel 365. U starší verze aplikace Excel můžete použít složitější alternativní vzorce.
Vysvětlení
Tento vzorec v jádru používá funkci UNIQUE k extrahování jedinečných hodnot a kritéria použití funkce FILTER.
Při práci zevnitř ven se funkce FILTER používá k použití kritérií a k extrakci pouze názvů, které jsou přidruženy k projektu „Omega“:
FILTER(B6:B15,C6:C15=H6) // Omega names only
Výsledek z FILTER je pole jako toto:
("Jim";"Jim";"Carl";"Sue";"Carl")
Dále se k odstranění duplikátů používá funkce UNIQUE:
UNIQUE(("Jim";"Jim";"Carl";"Sue";"Carl"))
což má za následek nové pole, jako je tento:
("Jim";"Carl";"Sue") // after UNIQUE
V tomto okamžiku máme jedinečný seznam jmen spojených s Omegou a musíme je jen spočítat. Z níže vysvětlených důvodů to děláme s funkcí LEN a SUM. Aby bylo vše jasné, nejdříve přepíšeme vzorec tak, aby obsahoval jedinečný seznam:
=SUM(--(LEN(("Jim";"Carl";"Sue"))>0))
Funkce LEN získá délku každé položky v seznamu a vrátí pole délek:
LEN(("Jim";"Carl";"Sue")) // returns (3;4;3)
Dále zkontrolujeme, zda jsou délky větší než nula:
LEN((3;4;3)>0 // returns (TRUE;TRUE;TRUE)
A použijte dvojitý zápor k vynucení hodnot PRAVDA a NEPRAVDA na 1 s a 0 s:
--((TRUE;TRUE;TRUE)) // returns (1;1;1)
Nakonec výsledky sčítáme pomocí funkce SUMA:
=SUM((1;1;1)) // returns 3
Toto pole je dodáváno přímo do funkce COUNTA, která vrací konečný počet:
=COUNTA(("Jim";"Carl";"Sue")) // returns 3
Všimněte si, že protože kontrolujeme délku každé položky vrácené UNIQUE, prázdné nebo prázdné buňky, které splňují kritéria, jsou ignorovány. Tento vzorec je dynamický a při změně zdrojových dat se okamžitě přepočítá.
Počítat jedinečný s více kritérii
Chcete-li počítat jedinečné hodnoty na základě více kritérií, můžete rozšířit logiku „zahrnout“ uvnitř FILTRU. Chcete-li například spočítat jedinečné názvy projektu Omega pouze v červnu, použijte:
=SUM(--(LEN(UNIQUE(FILTER(B6:B15,(C6:C15=H6)*(D6:D15="june"))))>0))
Toto je příklad použití logické logiky k použití více než jedné podmínky. Tento přístup je podrobněji vysvětlen zde.
Další podrobnosti najdete v tomto výukovém videu: Jak filtrovat podle více kritérií.
COUNTA
Je možné napsat jednodušší vzorec, který odpovídá na funkci COUNTA. Důležité však je, že COUNTA vrátí 1, pokud neexistují žádné odpovídající hodnoty. Důvodem je, že funkce FILTER vrací chybu, když žádná data neodpovídají kritériím, a tato chyba končí počítáním funkcí COUNTA. Základní vzorec COUNTA vypadá takto:
=COUNTA(UNIQUE(FILTER(B6:B15,C6:C15=H6)))
Tento vzorec vrátí 1, pokud neexistují žádná odpovídající data. Bude také zahrnovat prázdné buňky, které splňují kritéria. Vzorec založený na LEN a SUM je lepší volbou.
Žádná dynamická pole
Pokud používáte starší verzi aplikace Excel bez podpory dynamického pole, můžete použít složitější vzorec. Pro obecnější diskusi o alternativách dynamického pole viz: Alternativy k vzorcům dynamického pole.
