Obecný vzorec
(=SUM(--(FREQUENCY(IF(criteria,values),values)>0)))
souhrn
Chcete-li spočítat jedinečné číselné hodnoty v rozsahu, můžete použít vzorec založený na funkcích FREQUENCY, SUM a IF. V zobrazeném příkladu se čísla zaměstnanců objevují v rozsahu B5: B14. Vzorec v G6 je:
=SUM(--(FREQUENCY(IF(C5:C14="A",B5:B14),B5:B14)>0))
který vrací 2, protože v budově A jsou 2 jedinečná ID zaměstnanců.
Poznámka: Toto je maticový vzorec a pokud nepoužíváte Excel 365, musíte jej zadat pomocí kláves Control + Shift + Enter.
Vysvětlení
Poznámka: Před Excel 365 neměl Excel vyhrazenou funkci pro počítání jedinečných hodnot. Tento vzorec ukazuje jeden způsob, jak počítat jedinečné hodnoty, pokud jsou číselné. Pokud máte textové hodnoty nebo kombinaci textu a čísel, budete muset použít složitější vzorec.
Funkce Excel FREQUENCY vrací rozdělení kmitočtů, což je souhrnná tabulka, která obsahuje četnost číselných hodnot uspořádaných do „přihrádek“. Zde jej používáme jako kruhový objezd k počítání jedinečných číselných hodnot. K uplatnění kritérií používáme funkci IF.
Při práci zevnitř ven nejprve filtrujeme hodnoty pomocí funkce IF:
IF(C5:C14="A",B5:B14) // filter on building A
Výsledkem této operace je pole jako toto:
(905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE)
Všimněte si, že všechna ID v budově B jsou nyní NEPRAVDA. Toto pole je dodáváno přímo do funkce FREQUENCY jako data_array . Pro pole bins_array dodáváme samotná ID:
FREQUENCY((905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE),(905;905;905;905;773;773;801;963;963;963))
S touto konfigurací vrátí FREQUENCY pole níže:
(4;0;0;0;2;0;0;0;0;0;0)
Výsledek je trochu záhadný, ale význam je 905 se objeví čtyřikrát a 773 se objeví dvakrát. FALSE hodnoty jsou automaticky ignorovány.
FREKVENCE má speciální funkci, která automaticky vrací nulu pro všechna čísla, která se již objevila v datovém poli, a proto jsou hodnoty nulové, jakmile dojde k číslu. Toto je funkce, která umožňuje tento přístup fungovat.
Dále je každá z těchto hodnot testována na hodnotu větší než nula:
(4;0;0;0;2;0;0;0;0;0;0)>0
Výsledkem je pole jako toto:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Každý PRAVDA v seznamu představuje jedinečné číslo v seznamu a my jen musíme přidat hodnoty PRAVDA s SUM. SUM však nesčítá logické hodnoty v poli, takže musíme nejprve vynutit vynucení hodnot na 1 nebo nulu. To se provádí dvojitým záporem (-). Výsledkem je pole pouze 1 nebo 0:
(1;0;0;0;1;0;0;0;0;0;0)
Nakonec SUM sečte tyto hodnoty a vrátí součet, který je v tomto případě 2.
Několik kritérií
Vzorec můžete rozšířit tak, aby zpracovával několik kritérií, jako je tato:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),values),values)>0)))
