Generisk formel
=SUMPRODUCT(1/COUNTIF(data,data))
Resumé
For at tælle antallet af unikke værdier i et celleområde kan du bruge en formel baseret på COUNTIF- og SUMPRODUCT-funktionerne. I eksemplet viser er formlen i F6:
=SUMPRODUCT(1/COUNTIF(B5:B14,B5:B14))
Forklaring
Arbejder indefra og ud, COUNTIF er konfigureret til værdier i området B5: B14 ved at bruge alle disse samme værdier som kriterier:
COUNTIF(B5:B14,B5:B14)
Da vi giver 10 værdier for kriterier, får vi tilbage en matrix med 10 resultater som denne:
(3;3;3;2;2;3;3;3;2;2)
Hvert tal repræsenterer et antal - "Jim" vises 3 gange, "Sue" vises 2 gange og så videre.
Denne matrix er konfigureret som en skillevæg med 1 som tæller. Efter division får vi et andet array:
(0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5;0.333333333333333;0.333333333333333;0.333333333333333;0.5;0.5)
Eventuelle værdier, der forekommer kun en gang i området, vises som 1s, men værdier, der forekommer flere gange, vises som brøkværdier, der svarer til multiple. (dvs. en værdi, der vises 4 gange i data, genererer 4 værdier = 0,25).
Endelig summerer SUMPRODUCT-funktionen alle værdier i arrayet og returnerer resultatet.
Håndtering af blanke celler
En måde at håndtere tomme eller tomme celler på er at justere formlen som følger:
=SUMPRODUCT(1/COUNTIF(data,data&""))
Ved at sammenkæde en tom streng ("") til dataene forhindrer vi, at nuller ender i det array, der er oprettet af COUNTIF, når der er tomme celler i dataene. Dette er vigtigt, fordi et nul i divisoren får formlen til at kaste en # DIV / 0-fejl. Det fungerer, fordi brug af en tom streng ("") til kriterier tæller tomme celler.
Men selvom denne version af formlen ikke kaster en # DIV / 0-fejl, når den er med tomme celler, vil den inkludere tomme celler i optællingen. Hvis du vil ekskludere tomme celler fra optællingen, skal du bruge:
=SUMPRODUCT((data"")/COUNTIF(data,data&""))
Dette har den virkning, at antallet af tomme celler annulleres ved at gøre tælleren nul for tilknyttede tællinger.
Langsom præstation?
Dette er en cool og elegant formel, men den beregner meget langsommere end formler, der bruger FREQUENCY til at tælle unikke værdier. For større datasæt kan det være en god idé at skifte til en formel baseret på FREKVENS-funktionen. Her er en formel for numeriske værdier og en til tekstværdier.
