Obecný vzorec
=SUMPRODUCT(1/COUNTIF(data,data))
souhrn
Chcete-li spočítat počet jedinečných hodnot v rozsahu buněk, můžete použít vzorec založený na funkcích COUNTIF a SUMPRODUCT. V ukázkové ukázce je vzorec v F6:
=SUMPRODUCT(1/COUNTIF(B5:B14,B5:B14))
Vysvětlení
Při práci zevnitř ven je COUNTIF nakonfigurován na hodnoty v rozsahu B5: B14, přičemž všechny tyto stejné hodnoty používá jako kritéria:
COUNTIF(B5:B14,B5:B14)
Protože poskytujeme 10 hodnot pro kritéria, dostaneme zpět pole s 10 výsledky, jako je tento:
(3;3;3;2;2;3;3;3;2;2)
Každé číslo představuje počet - „Jim“ se objeví třikrát, „Sue“ se objeví dvakrát a tak dále.
Toto pole je nakonfigurováno jako dělitel s 1 jako čitatelem. Po rozdělení získáme další pole:
(0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5)
Jakékoli hodnoty, které se v rozsahu vyskytnou pouze jednou, se zobrazí jako 1 s, ale hodnoty, které se vyskytnou vícekrát, se zobrazí jako zlomkové hodnoty, které odpovídají násobku. (tj. hodnota, která se v datech objeví 4krát, vygeneruje 4 hodnoty = 0,25).
Nakonec funkce SUMPRODUCT sečte všechny hodnoty v poli a vrátí výsledek.
Zpracování prázdných buněk
Jedním ze způsobů, jak zacházet s prázdnými nebo prázdnými buňkami, je upravit vzorec následujícím způsobem:
=SUMPRODUCT(1/COUNTIF(data,data&""))
Zřetězením prázdného řetězce ("") k datům zabráníme nulam, aby skončily v poli vytvořeném funkcí COUNTIF, když jsou v datech prázdné buňky. To je důležité, protože nula v děliteli způsobí, že vzorec vyvolá chybu # DIV / 0. Funguje to, protože použití prázdného řetězce ("") pro kritéria bude počítat prázdné buňky.
Přestože tato verze vzorce nevyvolá chybu # DIV / 0, když má prázdné buňky, do počtu zahrne prázdné buňky. Pokud chcete z počtu vyloučit prázdné buňky, použijte:
=SUMPRODUCT((data"")/COUNTIF(data,data&""))
To má za následek zrušení počtu prázdných buněk vynulováním nuly čitatele pro přidružené počty.
Pomalý výkon?
Jedná se o skvělý a elegantní vzorec, ale počítá se mnohem pomaleji než vzorce, které používají FREQUENCY k počítání jedinečných hodnot. U větších datových sad možná budete chtít přepnout na vzorec založený na funkci FREKVENCE. Zde je vzorec pro číselné hodnoty a jeden pro textové hodnoty.
