Všeobecný vzorec
(=SUM(--(FREQUENCY(IF(criteria,MATCH(vals,vals,0)),ROW(vals)-ROW(vals.first)+1)>0)))
Zhrnutie
Ak chcete spočítať jedinečné textové hodnoty v rozsahu s kritériami, môžete použiť vzorec poľa založený na funkciách FREQUENCY a MATCH. V zobrazenom príklade je vzorec v G6:
(=SUM(--(FREQUENCY(IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0)),ROW(B5:B11)-ROW(B5)+1)>0)))
čo vráti 3, keďže na projekte Omega pracovali traja rôzni ľudia.
Poznámka: toto je vzorec poľa a musí sa zadávať pomocou klávesov Control + Shift + Enter.
Vysvetlenie
Toto je zložitý vzorec, ktorý používa FREKVENCIU na počítanie číselných hodnôt odvodených pomocou funkcie MATCH. Pri práci zvnútra von sa funkcia MATCH používa na získanie polohy každej hodnoty, ktorá sa zobrazuje v dátach:
MATCH(B5:B11,B5:B11,0)
Výsledkom MATCH je pole ako je toto:
(1;1;3;1;1;6;7)
Pretože MATCH vždy vráti pozíciu prvej zhody, hodnoty, ktoré sa v dátach objavia viackrát, vrátia rovnakú pozíciu. Napríklad preto, že „Jim“ sa v zozname nachádza štyrikrát, zobrazí sa v tomto poli štyrikrát ako číslo 1.
Mimo funkcie MATCH sa funkcia IF používa na uplatnenie kritérií, ktoré v tomto prípade zahŕňajú testovanie, či je projekt „omega“ (z bunky G5):
IF(C5:C11=G5 // filter on "omega"
Funkcia IF funguje ako filter a umožňuje iba prechod z hodnôt MATCH, ak sú spojené s výrazom „omega“. Výsledkom je pole ako je toto:
(FALSE;FALSE;FALSE;1;1;6;7) // after filtering
Filtrované pole sa dodáva priamo do funkcie FREQUENCY ako argument data_array . Ďalej sa funkcia ROW používa na vytvorenie postupného zoznamu čísel pre každú hodnotu v dátach:
ROW(B3:B12)-ROW(B3)+1
Takto sa vytvorí pole ako toto:
(1;2;3;4;5;6;7;8;9;10)
ktorý sa stane argumentom bins_array vo FILTERe . V tejto chvíli máme:
FREQUENCY((FALSE;FALSE;FALSE;1;1;6;7),(1;2;3;4;5;6;7))
FREQUENCY vráti pole čísel, ktoré označuje počet pre každú hodnotu v dátovom poli, usporiadané podľa koša. Ak už bolo číslo spočítané, FREQUENCY vráti nulu. Výsledkom z FREQUENCY je pole ako je toto:
(2;0;0;0;0;1;1;0) // result from FREQUENCY
Poznámka: FREQUENCY vždy vráti pole s jednou ďalšou položkou ako bins_array .
V tomto okamihu môžeme vzorec prepísať takto:
=SUM(--((2;0;0;0;0;1;1;0)>0))
Kontrolujeme hodnoty väčšie ako nula, ktoré prevádzajú čísla na TRUE alebo FALSE:
=SUM(--((TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE)))
Potom použijeme dvojitý zápor na vynútenie logických hodnôt na 1 s a 0 s:
=SUM((1;0;0;0;0;1;1;0))
Nakoniec funkcia SUM vráti 3 ako konečný výsledok.
Poznámka: toto je vzorec poľa a musí sa zadávať pomocou kombinácie klávesov Control + Shift + Enter.
Zaobchádzanie s prázdnymi bunkami v rozsahu
Ak sú niektoré bunky v rozsahu prázdne, budete musieť upraviť vzorec, aby ste zabránili prenosu prázdnych buniek do funkcie MATCH, čo spôsobí chybu. Môžete to urobiť pridaním ďalšej vnorenej funkcie IF na kontrolu prázdnych buniek:
(=SUM(--(FREQUENCY(IF(B5:B11"",IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0))),ROW(B5:B11)-ROW(B5)+1)>0)))
S dvoma kritériami
Ak máte dve kritériá, môžete logiku vzorca rozšíriť pridaním iného vnoreného IF:
(=SUM(--(FREQUENCY(IF(c1,IF(c2,MATCH(vals,vals,0))),ROW(vals)-ROW(vals.1st)+1)>0)))
Kde c1 = kritériá1, c2 = kritériá2 a vals = rozsah hodnôt.
S logickou logikou
Pomocou logickej logiky môžete znížiť vnorené IF:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),MATCH(vals,vals,0)),ROW(vals)-ROW(vals.1st)+1)>0)))
To uľahčuje pridávanie a správu ďalších kritérií.
