Splošna formula
=SUMPRODUCT(1/COUNTIF(data,data))
Povzetek
Če želite prešteti število enoličnih vrednosti v obsegu celic, lahko uporabite formulo, ki temelji na funkcijah COUNTIF in SUMPRODUCT. V primeru oddaje je formula v F6:
=SUMPRODUCT(1/COUNTIF(B5:B14,B5:B14))
Pojasnilo
Delo od znotraj navzven je COUNTIF konfiguriran na vrednosti v obsegu B5: B14, pri čemer se uporabljajo vse te iste vrednosti kot merila:
COUNTIF(B5:B14,B5:B14)
Ker za kriterije podajamo 10 vrednosti, dobimo nazaj matriko z 10 rezultati, kot je ta:
(3;3;3;2;2;3;3;3;2;2)
Vsaka številka predstavlja štetje - "Jim" se prikaže 3-krat, "Sue" se prikaže 2-krat itd.
Ta matrika je konfigurirana kot delitelj z števcem 1. Po delitvi dobimo še eno matriko:
(0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5)
Vse vrednosti, ki se pojavijo le enkrat v območju, bodo prikazane kot 1s, vrednosti, ki se pojavijo večkrat, pa kot delne vrednosti, ki ustrezajo večkratniku. (tj. vrednost, ki se 4-krat pojavi v podatkih, bo ustvarila 4 vrednosti = 0,25).
Na koncu funkcija SUMPRODUCT sešteje vse vrednosti v matriki in vrne rezultat.
Ravnanje s praznimi celicami
Eden od načinov za obdelavo praznih ali praznih celic je prilagoditev formule na naslednji način:
=SUMPRODUCT(1/COUNTIF(data,data&""))
S povezovanjem praznega niza ("") s podatki preprečimo, da bi ničle končale v polju, ki ga je ustvaril COUNTIF, če so v podatkih prazne celice. To je pomembno, ker bo ničla v delilniku povzročila, da bo formula povzročila napako # DIV / 0. Deluje, ker bo uporaba praznih nizov ("") za merila štela prazne celice.
Čeprav ta različica formule ne bo povzročila napake # DIV / 0, če ima prazne celice, bo v štetje vključila prazne celice. Če želite iz štetja izključiti prazne celice, uporabite:
=SUMPRODUCT((data"")/COUNTIF(data,data&""))
To ima za posledico izničenje števila praznih celic, tako da je števec enačen številu nič.
Počasno delovanje?
To je kul in elegantna formula, vendar izračuna veliko počasneje kot formule, ki uporabljajo FREQUENCY za štetje edinstvenih vrednosti. Za večje nabore podatkov boste morda želeli preklopiti na formulo, ki temelji na funkciji FREQUENCY. Tu je formula za številske vrednosti in ena za besedilne vrednosti.
