Vynikat

Počítejte jedinečné hodnoty s kritérii

Count Unique Values With Criteria

Vzorec aplikace Excel: Počítejte jedinečné hodnoty s kritériiObecný vzorec | _+_ | souhrn

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


^