Obecný vzorec
=SUM(--(FREQUENCY(data,data)>0))
souhrn
Chcete-li spočítat jedinečné číselné hodnoty v rozsahu, můžete použít vzorec založený na funkcích FREQUENCY a SUM. V zobrazeném příkladu se čísla zaměstnanců objevují v rozsahu B5: B14. Vzorec v F5 je:
=SUM(--(FREQUENCY(B5:B14,B5:B14)>0))
který vrací 4, protože v seznamu jsou 4 jedinečná ID zaměstnanců.
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á zobrazuje frekvenci čí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.
Při práci zevnitř ven dodáváme FREQUENCY stejnou sadu čísel pro datové pole i pole přihrádek:
FREQUENCY(B5:B14,B5:B14)
FREQUENCY vrací pole s počtem jednotlivých číselných hodnot v rozsahu:
(4;0;0;0;2;0;1;3;0;0;0)
Výsledek je trochu záhadný, ale význam je 905 se objeví čtyřikrát, 773 se objeví dvakrát, 801 se objeví jednou a 963 se objeví třikrát.
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.
Dále je každá z těchto hodnot testována na hodnotu větší než nula:
(4;0;0;0;2;0;1;3;0;0;0)>0
Výsledkem je pole jako toto:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE)
Každá PRAVDA představuje jedinečné číslo v seznamu. SUM ve výchozím nastavení ignoruje logické hodnoty, takže vynucujeme hodnoty PRAVDA a NEPRAVDA na 1 s a 0 s dvojitým záporem (-), čímž se získá:
=SUM((1;0;0;0;1;0;1;1;0;0;0))
Nakonec SUM přidá tyto hodnoty a vrátí součet, který je v tomto případě 4.
Poznámka: SUMPRODUCT můžete také použít k součtu položek v poli.
Pomocí COUNTIF namísto FREQUENCY počítáte jedinečné hodnoty
Dalším způsobem, jak počítat jedinečné číselné hodnoty, je použít COUNTIF namísto FREQUENCY. Toto je jednodušší vzorec, ale mějte na paměti, že použití COUNTIF na větších souborech dat k počítání jedinečných hodnot může způsobit problémy s výkonem. Vzorec FREQUENCY, i když je složitější, se počítá mnohem rychleji.
