Všeobecný vzorec
(=SUM(--(FREQUENCY(IF(criteria,values),values)>0)))
Zhrnutie
Ak chcete v rozsahu spočítať jedinečné číselné hodnoty, môžete použiť vzorec založený na funkciách FREQUENCY, SUM a IF. V zobrazenom príklade sa počty zamestnancov zobrazujú v rozmedzí B5: B14. Vzorec v G6 je:
=SUM(--(FREQUENCY(IF(C5:C14="A",B5:B14),B5:B14)>0))
ktorá vráti 2, pretože v budove A sú 2 jedinečné ID zamestnancov.
Poznámka: toto je vzorec poľa a musí sa zadávať pomocou klávesov Control + Shift + Enter, pokiaľ nepoužívate Excel 365.
Vysvetlenie
Poznámka: Pred aplikáciou Excel 365 nemal Excel vyhradenú funkciu na počítanie jedinečných hodnôt. Tento vzorec ukazuje jeden spôsob počítania jedinečných hodnôt, pokiaľ sú číselné. Ak máte textové hodnoty alebo kombináciu textu a čísel, budete musieť použiť zložitejší vzorec.
Funkcia Excel FREQUENCY vráti rozdelenie frekvencií, čo je súhrnná tabuľka obsahujúca frekvenciu číselných hodnôt usporiadaných do „zásobníkov“. Používame ho tu ako kruhový objazd na počítanie jedinečných číselných hodnôt. Na uplatnenie kritérií použijeme funkciu IF.
Pri práci zvnútra smerom von najskôr filtrujeme hodnoty pomocou funkcie IF:
IF(C5:C14="A",B5:B14) // filter on building A
Výsledkom tejto operácie je pole podobné tomuto:
(905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE)
Všimnite si, že všetky identifikátory v budove B sú teraz NEPRAVDA. Toto pole sa dodáva priamo do funkcie FREQUENCY ako dátová_súbor . Pre pole bins_array dodávame samotné identifikátory:
FREQUENCY((905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE),(905;905;905;905;773;773;801;963;963;963))
S touto konfiguráciou vráti FREQUENCY pole nižšie:
(4;0;0;0;2;0;0;0;0;0;0)
Výsledok je trochu tajomný, ale význam je, že 905 sa objaví štyrikrát a 773 sa objaví dvakrát. FALSE hodnoty sú automaticky ignorované.
FREKVENCIA má špeciálnu funkciu, ktorá automaticky vracia nulu pre všetky čísla, ktoré sa už v dátovom poli vyskytli, a preto sú hodnoty nulové, hneď ako dôjde k číslu. Toto je funkcia, ktorá umožňuje tento prístup fungovať.
Ďalej sa testuje každá z týchto hodnôt ako vyššia ako nula:
(4;0;0;0;2;0;0;0;0;0;0)>0
Výsledkom je pole ako je toto:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Každá hodnota TRUE v zozname predstavuje jedinečné číslo v zozname a stačí, keď sčítame hodnoty TRUE pomocou SUM. SUM však nepridá logické hodnoty do poľa, takže musíme najskôr vynútiť vynútenie hodnôt na 1 alebo nulu. To sa deje s dvojitým záporom (-). Výsledkom bude pole iba s 1 alebo 0:
(1;0;0;0;1;0;0;0;0;0;0)
Nakoniec SUM sčíta tieto hodnoty a vráti súčet, ktorý je v tomto prípade 2.
Viaceré kritériá
Vzorec môžete rozšíriť tak, aby obsahoval viac kritérií, napríklad toto:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),values),values)>0)))
