Chcete -li počítat jedinečné hodnoty s jednou nebo více podmínkami, můžete použít vzorec založený na UNIKÁTNÍ a FILTR . V uvedeném příkladu je vzorec v H7:
= SUM (--( LEN ( UNIQUE ( FILTER (range,criteria,'')))>0))
který vrací 3, protože v B6 jsou tři unikátní jména: B15 spojená s projektem Omega.
Poznámka: tento vzorec vyžaduje Vzorce dynamického pole , k dispozici pouze v Excel 365 . Se starší verzí Excelu můžete použít složitější alternativní vzorce .
Vysvětlení
V jádru tento vzorec používá funkci UNIQUE k extrahování jedinečných hodnot a funkce FILTER aplikuje kritéria.
vzorec pro výpočet hypotečních splátek v aplikaci Excel
Práce zevnitř ven, Funkce FILTER se používá k aplikaci kritérií a extrahování pouze názvů, které jsou spojeny s projektem 'Omega':
= SUM (--( LEN ( UNIQUE ( FILTER (B6:B15,C6:C15=H6,'')))>0))
Výsledkem FILTER je pole takhle:
FILTER (B6:B15,C6:C15=H6) // Omega names only
Dále, JEDINEČNÁ funkce slouží k odstranění duplikátů:
{'Jim''Jim''Carl''Sue''Carl'}
což má za následek nové pole takto:
UNIQUE ({'Jim''Jim''Carl''Sue''Carl'})
V tuto chvíli 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 funkcí SUM. Aby bylo vše jasné, nejprve přepíšeme vzorec tak, aby zahrnoval jedinečný seznam:
{'Jim''Carl''Sue'} // after UNIQUE
The LEN function získá délku každé položky v seznamu a vrátí řadu délek:
= SUM (--( LEN ({'Jim''Carl''Sue'})>0))
Dále zkontrolujeme, zda jsou délky větší než nula:
LEN ({'Jim''Carl''Sue'}) // returns {343}
A použijte a dvojitý negativní k vynucení hodnot PRAVDA a NEPRAVDA na 1 s a 0 s:
LEN ({343)>0 // returns {TRUETRUETRUE}
Nakonec sečteme výsledky pomocí Funkce SUM :
jak používat histogram v aplikaci Excel
--({TRUETRUETRUE}) // returns {111}
Toto pole je dodáváno přímo do funkce COUNTA, která vrací konečný počet:
= SUM ({111}) // returns 3
Všimněte si toho, 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čítejte 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“ do FILTRU. Chcete -li například počítat jedinečná jména pro projekt Omega pouze v červnu, použijte:
= COUNTA ({'Jim''Carl''Sue'}) // returns 3
Toto je příklad použití booleovská logika použít více než jednu podmínku. Přístup je zde podrobněji vysvětleno .
jak upravovat hodnoty os v aplikaci Excel
Další podrobnosti najdete v tomto školícím videu: Jak filtrovat podle více kritérií .
COUNTA
Je možné napsat jednodušší vzorec, který odpoví na Funkce COUNTA . Důležitou výhradou 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 skončí započítávána funkcí COUNTA. Základní vzorec COUNTA vypadá takto:
= SUM (--( LEN ( UNIQUE ( FILTER (B6:B15,(C6:C15=H6)*(D6:D15='june'))))>0))
Pokud neexistují žádná odpovídající data, tento vzorec vrátí hodnotu 1. Bude také obsahovat 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 Excelu bez podpory dynamického pole, můžete použít složitější vzorec . Obecnější diskusi o alternativách dynamického pole naleznete v: Alternativy k dynamickým maticovým vzorcům .
Vzorce dynamického pole jsou k dispozici v Office 365 pouze. Autor Dave Bruns