Obecný vzorec
(=SUM(--(FREQUENCY(IF(criteria,MATCH(vals,vals,0)),ROW(vals)-ROW(vals.first)+1)>0)))
souhrn
Chcete-li spočítat jedinečné textové hodnoty v rozsahu s kritérii, můžete použít maticový vzorec založený na funkcích FREQUENCY a MATCH. V zobrazeném příkladu je vzorec v G6:
(=SUM(--(FREQUENCY(IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0)),ROW(B5:B11)-ROW(B5)+1)>0)))
což vrací 3, protože na projektu Omega pracovali tři různí lidé.
Poznámka: toto je maticový vzorec a musí být zadán pomocí kláves Control + Shift + Enter.
Vysvětlení
Toto je složitý vzorec, který používá FREQUENCY k počítání číselných hodnot, které jsou odvozeny pomocí funkce MATCH. Při práci zevnitř ven se funkce MATCH používá k získání polohy každé hodnoty, která se objeví v datech:
MATCH(B5:B11,B5:B11,0)
Výsledkem MATCH je pole jako toto:
(1;1;3;1;1;6;7)
Protože MATCH vždy vrátí pozici první shody, hodnoty, které se v datech objeví více než jednou, vrátí stejnou pozici. Například protože se „Jim“ v seznamu objeví čtyřikrát, zobrazí se v tomto poli čtyřikrát jako číslo 1.
Mimo funkci MATCH se funkce IF používá k uplatnění kritérií, což v tomto případě zahrnuje testování, zda je projekt „omega“ (z buňky G5):
IF(C5:C11=G5 // filter on "omega"
Funkce IF funguje jako filtr, pouze umožňuje průchod hodnot z MATCH, pokud jsou spojeny s „omega“. Výsledkem je pole jako toto:
(FALSE;FALSE;FALSE;1;1;6;7) // after filtering
Filtrované pole je dodáváno přímo do funkce FREQUENCY jako argument data_array . Funkce ROW se dále používá k sestavení sekvenčního seznamu čísel pro každou hodnotu v datech:
ROW(B3:B12)-ROW(B3)+1
Tím se vytvoří pole takto:
(1;2;3;4;5;6;7;8;9;10)
který se stane argumentem bins_array ve FILTERU. V tomto okamžiku máme:
FREQUENCY((FALSE;FALSE;FALSE;1;1;6;7),(1;2;3;4;5;6;7))
FREQUENCY vrací pole čísel, které označuje počet pro každou hodnotu v datovém poli, uspořádané podle bin. Pokud již bylo číslo spočítáno, FREQUENCY vrátí nulu. Výsledkem FREQUENCY je pole jako toto:
(2;0;0;0;0;1;1;0) // result from FREQUENCY
Poznámka: FREQUENCY vždy vrátí pole s jednou další položkou než bins_array .
V tomto okamžiku můžeme vzorec přepsat takto:
=SUM(--((2;0;0;0;0;1;1;0)>0))
Zkontrolujeme hodnoty větší než nula, což převede čísla na TRUE nebo FALSE:
=SUM(--((TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE)))
Pak použijeme dvojitý zápor k vynucení logických hodnot na 1 s a 0 s:
=SUM((1;0;0;0;0;1;1;0))
Nakonec funkce SUM vrátí 3 jako konečný výsledek.
Poznámka: toto je maticový vzorec a musí být zadán pomocí kláves Control + Shift + Enter.
Zpracování prázdných buněk v rozsahu
Pokud jsou některé buňky v rozsahu prázdné, budete muset upravit vzorec, aby se zabránilo přenosu prázdných buněk do funkce MATCH, což způsobí chybu. Můžete to udělat přidáním další vnořené funkce IF, abyste zkontrolovali prázdné buňky:
(=SUM(--(FREQUENCY(IF(B5:B11"",IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0))),ROW(B5:B11)-ROW(B5)+1)>0)))
Se dvěma kritérii
Pokud máte dvě kritéria, můžete rozšířit logiku vzorce přidáním dalšího vnořeného IF:
(=SUM(--(FREQUENCY(IF(c1,IF(c2,MATCH(vals,vals,0))),ROW(vals)-ROW(vals.1st)+1)>0)))
Kde c1 = kritéria1, c2 = kritéria2 a vals = rozsah hodnot.
S logickou logikou
S logickou logikou můžete snížit vnořené IF:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),MATCH(vals,vals,0)),ROW(vals)-ROW(vals.1st)+1)>0)))
To usnadňuje přidávání a správu dalších kritérií.
