Všeobecný vzorec
=SUMPRODUCT(1/COUNTIF(data,data))
Zhrnutie
Ak chcete spočítať počet jedinečných hodnôt v rozsahu buniek, môžete použiť vzorec založený na funkciách COUNTIF a SUMPRODUCT. V ukážke je vzorec v F6:
=SUMPRODUCT(1/COUNTIF(B5:B14,B5:B14))
Vysvetlenie
Pri práci zvnútra von je COUNTIF nakonfigurovaný na hodnoty v rozsahu B5: B14, pričom všetky tieto rovnaké hodnoty používa ako kritériá:
COUNTIF(B5:B14,B5:B14)
Pretože poskytujeme 10 hodnôt pre kritériá, dostaneme späť pole s 10 výsledkami, ako je tento:
(3;3;3;2;2;3;3;3;2;2)
Každé číslo predstavuje počet - „Jim“ sa objaví trikrát, „Sue“ sa objaví dvakrát a podobne.
Toto pole je nakonfigurované ako deliteľ s 1 ako čitateľom. Po rozdelení dostaneme ďalšie pole:
(0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5)
Všetky hodnoty, ktoré sa v rozsahu vyskytnú iba raz, sa zobrazia ako 1 s, ale hodnoty, ktoré sa vyskytnú viackrát, sa zobrazia ako zlomkové hodnoty, ktoré zodpovedajú násobku. (tj hodnota, ktorá sa v dátach objaví 4-krát, vygeneruje 4 hodnoty = 0,25).
Nakoniec funkcia SUMPRODUCT sčíta všetky hodnoty v poli a vráti výsledok.
Zaobchádzanie s prázdnymi bunkami
Jedným zo spôsobov, ako zaobchádzať s prázdnymi alebo prázdnymi bunkami, je upraviť vzorec takto:
=SUMPRODUCT(1/COUNTIF(data,data&""))
Zreťazením prázdneho reťazca ("") k údajom zabránime tomu, aby nuly skončili v poli vytvorenom COUNTIF, keď sú v dátach prázdne bunky. To je dôležité, pretože nula v deliteľovi spôsobí, že vzorec vyvolá chybu # DIV / 0. Funguje to, pretože použitie prázdneho reťazca ("") pre kritériá bude počítať prázdne bunky.
Aj keď táto verzia vzorca nevyvolá chybu # DIV / 0, ak má prázdne bunky, do počtu zahrnie prázdne bunky. Ak chcete vylúčiť prázdne bunky z počtu, použite:
=SUMPRODUCT((data"")/COUNTIF(data,data&""))
To má za následok zrušenie počtu prázdnych buniek vynulovaním nuly čitateľa pre príslušné počty.
Pomalý výkon?
Toto je cool a elegantný vzorec, ale počíta sa oveľa pomalšie ako vzorce, ktoré na výpočet jedinečných hodnôt používajú FREKVENCIU. V prípade väčších množín údajov môžete prepnúť na vzorec založený na funkcii FREQUENCY. Tu je vzorec pre číselné hodnoty a jeden pre textové hodnoty.
