Splošna formula
(=SUM(--(FREQUENCY(IF(criteria,values),values)>0)))
Povzetek
Za štetje edinstvenih številskih vrednosti v obsegu lahko uporabite formulo, ki temelji na funkcijah FREQUENCY, SUM in IF. V prikazanem primeru so številke zaposlenih v območju B5: B14. Formula v G6 je:
=SUM(--(FREQUENCY(IF(C5:C14="A",B5:B14),B5:B14)>0))
ki vrne 2, saj sta v stavbi A dva edinstvena ID-ja zaposlenih
Opomba: to je matrična formula, ki jo je treba vnesti s tipko control + shift + enter, razen če uporabljate Excel 365.
Pojasnilo
Opomba: Pred Excel 365 Excel ni imel posebne funkcije za štetje edinstvenih vrednosti. Ta formula prikazuje en način za štetje edinstvenih vrednosti, če so številske. Če imate besedilne vrednosti ali kombinacijo besedila in številk, boste morali uporabiti bolj zapleteno formulo.
Funkcija Excel FREQUENCY vrne porazdelitev frekvence, ki je zbirna tabela, ki vsebuje pogostost številskih vrednosti, razvrščenih v "koše". Tu ga uporabljamo kot krožni način za štetje edinstvenih številskih vrednosti. Za uporabo meril uporabljamo funkcijo IF.
Če delamo od znotraj navzven, najprej filtriramo vrednosti s funkcijo IF:
IF(C5:C14="A",B5:B14) // filter on building A
Rezultat te operacije je matrika, kot je ta:
(905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE)
Upoštevajte, da so vsi ID-ji v zgradbi B LAŽNI. Ta matrika je dostavljena neposredno v funkcijo FREQUENCY kot matrika podatkov . Za bins_array dobavljamo same ID-je:
FREQUENCY((905;905;905;905;773;773;FALSE;FALSE;FALSE;FALSE),(905;905;905;905;773;773;801;963;963;963))
Pri tej konfiguraciji FREQUENCY vrne spodnjo matriko:
(4;0;0;0;2;0;0;0;0;0;0)
Rezultat je nekoliko skrivnosten, vendar je pomen 905 prikazan štirikrat, 773 pa dvakrat. Vrednosti FALSE se samodejno prezrejo.
FREQUENCY ima posebno funkcijo, ki samodejno vrne ničlo za vsa števila, ki so se že pojavila v podatkovni matriki, zato so vrednosti, ko je število naletelo, enake nič. To je lastnost, ki omogoča, da ta pristop deluje.
Nato se preskusi, da je vsaka od teh vrednosti večja od nič:
(4;0;0;0;2;0;0;0;0;0;0)>0
Rezultat je matrika, kot je ta:
(TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE)
Vsaka TRUE na seznamu predstavlja edinstveno številko na seznamu, vrednosti TRUE pa moramo samo sešteti s SUM. Vendar SUM v matriki ne bo sestavljal logičnih vrednosti, zato moramo vrednosti najprej prisiliti v 1 ali nič. To se naredi z dvojnim negativom (-). Rezultat je matrika samo 1 ali 0:
(1;0;0;0;1;0;0;0;0;0;0)
Na koncu SUM sešteje te vrednosti in vrne seštevek, ki je v tem primeru 2.
Več meril
Formulo lahko razširite na več kriterijev, kot je ta:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),values),values)>0)))
