Generisk formel
=SUMPRODUCT(1/COUNTIF(data,data))
Sammendrag
For å telle antall unike verdier i et celleområde, kan du bruke en formel basert på COUNTIF og SUMPRODUCT-funksjonene. I eksemplet viser er formelen i F6:
=SUMPRODUCT(1/COUNTIF(B5:B14,B5:B14))
Forklaring
COUNTIF fungerer fra innsiden og ut, og er konfigurert til verdier i området B5: B14, ved å bruke alle disse samme verdiene som kriterier:
COUNTIF(B5:B14,B5:B14)
Fordi vi gir 10 verdier for kriterier, får vi tilbake en matrise med 10 resultater som dette:
(3;3;3;2;2;3;3;3;2;2)
Hvert tall representerer et antall - "Jim" vises 3 ganger, "Sue" vises 2 ganger, og så videre.
Denne matrisen er konfigurert som en divisor med 1 som teller. Etter divisjon får vi et annet utvalg:
(0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5)
Eventuelle verdier som forekommer i bare en gang i området vil vises som 1s, men verdier som forekommer flere ganger vil vises som brøkverdier som tilsvarer multiple. (dvs. en verdi som vises 4 ganger i data vil generere 4 verdier = 0,25).
Til slutt summerer SUMPRODUCT-funksjonen alle verdiene i matrisen og returnerer resultatet.
Håndtering av blanke celler
En måte å håndtere tomme eller tomme celler på er å justere formelen som følger:
=SUMPRODUCT(1/COUNTIF(data,data&""))
Ved å sammenkoble en tom streng ("") til dataene, forhindrer vi at nuller havner i matrisen opprettet av COUNTIF når det er tomme celler i dataene. Dette er viktig, fordi et null i deleren vil føre til at formelen kaster en # DIV / 0-feil. Det fungerer fordi bruk av en tom streng ("") for kriterier teller tomme celler.
Imidlertid, selv om denne versjonen av formelen ikke vil kaste en # DIV / 0-feil når det er med tomme celler, vil den inkludere tomme celler i tellingen. Hvis du vil ekskludere blanke celler fra tellingen, bruker du:
=SUMPRODUCT((data"")/COUNTIF(data,data&""))
Dette har den effekten at antallet tomme celler fjernes ved å gjøre telleren null for tilknyttede tellinger.
Treg ytelse?
Dette er en kul og elegant formel, men den beregner mye saktere enn formler som bruker FREKVENS for å telle unike verdier. For større datasett kan det være lurt å bytte til en formel basert på FREKVENS-funksjonen. Her er en formel for numeriske verdier, og en for tekstverdier.
