Excel

Tæl unikke værdier i et interval med COUNTIF

Count Unique Values Range With Countif

Excel-formel: Tæl unikke værdier i et interval med COUNTIFGenerisk 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å TÆLHVIS og SUMPRODUKT funktioner. I eksemplet viser er formlen i F6:

 
= SUMPRODUCT (1/ COUNTIF (B5:B14,B5:B14))
Med Excel 365 , kan du bruge en enklere og hurtigere formel baseret på ENESTÅENDE . Forklaring

Arbejder indefra og ud, COUNTIF er konfigureret til værdier i området B5: B14 ved hjælp af alle disse samme værdier som kriterier:



 
 COUNTIF (B5:B14,B5:B14)

Fordi vi giver 10 værdier for kriterier, får vi tilbage en array med 10 resultater som denne:



 
{3332233322}

Hvert tal repræsenterer et antal - 'Jim' vises 3 gange, 'Sue' vises 2 gange osv.

Denne matrix er konfigureret som en skillevæg med 1 som tæller. Efter division får vi et andet array:



 
{0.3333333333333330.3333333333333330.3333333333333330.50.50.3333333333333330.3333333333333330.3333333333333330.50.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 opsummerer SUMPRODUCT-funktionen alle værdier i arrayet og returnerer resultatet.

ms excel vognretur i celle

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 sammenkædning 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, er den dog vilje 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 effekt, 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 FREKVENS til at tælle unikke værdier. For større datasæt kan du skifte til en formel baseret på FREKVENS-funktionen. Her er en formel til numeriske værdier og en til tekstværdier .

Forfatter Dave Bruns


^