Excel

Tæl unikke værdier i et område med COUNTIF

Count Unique Values Range With Countif

Excel -formel: Tæl unikke værdier i et område med COUNTIFGenerisk formel | _+_ | Resumé

For at tælle antallet af unikke værdier i et celleområde kan du bruge en formel baseret på TÆLLE og SUMPRODUKT funktioner. I eksempelvisningen er formlen i F6:





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

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

 
= SUMPRODUCT (1/ COUNTIF (B5:B14,B5:B14))

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





 
 COUNTIF (B5:B14,B5:B14)

Hvert nummer repræsenterer en optælling - 'Jim' vises 3 gange, 'Sue' vises 2 gange og så videre.

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



 
{3332233322}

Alle værdier, der forekommer kun én gang i intervallet, vises som 1s, men værdier, der forekommer flere gange, vises som brøkværdier, der svarer til multiplumet. (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.

ms excel vognretur i celle

Håndtering af tomme celler

En måde at håndtere tomme eller tomme celler på er at justere formlen som følger:

 
{0.3333333333333330.3333333333333330.3333333333333330.50.50.3333333333333330.3333333333333330.3333333333333330.50.5}

Ved sammenkædning en tom streng ('') til dataene, forhindrer vi, at nuller havner i den matrix, 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 vil tælle tomme celler.

Selvom denne version af formlen ikke vil kaste en #DIV/0 -fejl, når den er med tomme celler, er den vilje inkludere tomme celler i tællingen. Hvis du vil ekskludere tomme celler fra optællingen, skal du bruge:

 
= SUMPRODUCT (1/ 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 ydeevne?

Dette er en sej 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 vil du måske skifte til en formel baseret på FREKVENS -funktionen. Her er en formel for numeriske værdier , og en for tekstværdier .

Forfatter Dave Bruns


^