Excel

Tæl unikke tekstværdier i et interval

Count Unique Text Values Range

Excel-formel: Tæl unikke tekstværdier i et intervalGenerisk formel
= SUMPRODUCT (--( FREQUENCY ( MATCH (data,data,0), ROW (data)- ROW (data.firstcell)+1)>0))
Resumé

For at tælle unikke tekstværdier i et interval kan du bruge en formel, der bruger flere funktioner: FREKVENS , MATCH , RÆKKE og SUMPRODUKT I det viste eksempel er formlen i F5:

 
= SUMPRODUCT (--( FREQUENCY ( MATCH (B5:B14,B5:B14,0), ROW (B5:B14)- ROW (B5)+1)>0))

der returnerer 4, da der er 4 unikke navne i B5: B14.



Bemærk: En anden måde at tælle unikke værdier på er at brug COUNTIF-funktionen . Dette er en meget enklere formel, men den kan køre langsomt på store datasæt. Med Excel 365 , kan du bruge en enklere og hurtigere formel baseret på ENESTÅENDE .



Forklaring

Denne formel er mere kompliceret end en lignende formel, der bruger FREKVENS til tæl unikke numeriske værdier fordi FREKVENS ikke fungerer med ikke-numeriske værdier. Som et resultat omdanner en stor del af formlen de ikke-numeriske data til numeriske data, som FREKVENS kan håndtere.

Når man arbejder indefra og ud, bruges MATCH-funktionen til at få positionen for hvert element, der vises i dataene:



 
 MATCH (B5:B14,B5:B14,0)

Resultatet fra MATCH er et array sådan her:

 
{1114466699}

Fordi MATCH altid returnerer positionen for først match, værdier, der vises mere end én gang i dataene, returnerer den samme position. For eksempel, fordi 'Jim' vises 3 gange på listen, viser han sig i dette array 3 gange som nummer 1.

Denne matrix tilføres FREKVENS som data_array argument.Det bins_array argumentet er konstrueret ud fra denne del af formlen:



hvordan man fjerner plads i Excel før tal
 
 ROW (B5:B14)- ROW (B5)+1)

der bygger en sekventiel liste over numre for hver værdi i dataene:

 
{12345678910}

På dette tidspunkt er FREQUENCY konfigureret således:

 
 FREQUENCY ({1114466699},{12345678910})

FREKVENS returnerer et array med tal, der angiver et antal for hvert nummer i dataarrayet, organiseret efter bin. Når et tal allerede er talt, returnerer FREQUENCY nul. Dette er en nøglefunktion i driften af ​​denne formel. Resultatet fra FREQUENCY er en matrix som denne:

sumif mellem to datoer og et andet kriterium
 
{30020300200} // output from FREQUENCY

Bemærk: FREKVENS returnerer altid en matrix med et element mere end bins_array .

Vi kan nu omskrive formlen sådan:

 
= SUMPRODUCT (--({30020300200}>0))

Dernæst kontrollerer vi for værdier større end nul (> 0), der konverterer tallene til SAND eller FALSK, og brug derefter en dobbelt negativ (-) til at konvertere SAND og FALSK værdier til 1s og 0s. Nu har vi:

 
= SUMPRODUCT ({10010100100})

Endelig tilføjer SUMPRODUCT simpelthen tallene op og returnerer det samlede antal, som i dette tilfælde er 4.

Håndtering af blanke celler

Tomme celler i området får formlen til at returnere en # N / A-fejl. For at håndtere tomme celler kan du bruge en mere kompliceret matrixformel, der bruger IF-funktionen til at filtrere tomme værdier ud:

 
{= SUM ( IF ( FREQUENCY ( IF (data'',  MATCH (data,data,0)), ROW (data)- ROW (data.firstcell)+1),1))}

Bemærk: tilføjelse af IF gør dette til et matrixformel der kræver kontrol-skift-enter.

For mere information, se denne side .

Fra Mike Givins fremragende bog om matrixformler, Control-Shift-Enter.

Andre måder at tælle unikke værdier på

Hvis du har Excel 365, kan du bruge UNIK funktion til tæl unikke værdier med en meget enklere formel.

TIL drejetabel er også en glimrende måde at tælle unikke værdier på.

Forfatter Dave Bruns


^