Excel

Tæl unikke tekstværdier med kriterier

Count Unique Text Values With Criteria

Excel-formel: Tæl unikke tekstværdier med kriterierGenerisk formel
{= SUM (--( FREQUENCY ( IF (criteria, MATCH (vals,vals,0)), ROW (vals)- ROW (vals.first)+1)>0))}
Resumé

For at tælle unikke tekstværdier i et interval med kriterier kan du bruge en matrixformel baseret på FREKVENS og MATCH funktioner. I det viste eksempel er formlen i G6:

 
{= SUM (--( FREQUENCY ( IF (C5:C11=G5, MATCH (B5:B11,B5:B11,0)), ROW (B5:B11)- ROW (B5)+1)>0))}

der returnerer 3, da tre forskellige mennesker arbejdede på projektet Omega.



Bemærk: dette er en matrixformel og skal indtastes med kontrol + skift + enter.



Med Excel 365 , kan du bruge en meget enklere formel baseret på UNIK funktion . Forklaring

Dette er en kompleks formel, der bruger FREKVENS til at tælle numeriske værdier, der er afledt med MATCH-funktionen. Når man arbejder indefra og ud, bruges MATCH-funktionen til at få positionen for hver værdi, der vises i dataene:

 
 MATCH (B5:B11,B5:B11,0)

Resultatet fra MATCH er en matrix som denne:



 
{1131167}

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 4 gange på listen, vises han i denne matrix 4 gange som nummer 1.

Uden for MATCH-funktionen er HVIS-funktion bruges til at anvende kriterier, som i dette tilfælde involverer testning af, om projektet er 'omega' (fra celle G5):

 
 IF (C5:C11=G5 // filter on 'omega'

IF-funktionen fungerer som et filter, der kun tillader værdierne fra MATCH at passere, hvis de er forbundet med 'omega'. Resultatet er en matrix som denne:



 
{FALSEFALSEFALSE1167} // after filtering

Det filtrerede array leveres direkte til FREQUENCY-funktionen som data_array argument. Dernæst ROW-funktion bruges til at bygge en sekventiel liste over numre for hver værdi i dataene:

 
 ROW (B3:B12)- ROW (B3)+1

Dette skaber en matrix som denne:

 
{12345678910}

som bliver den bins_array argument i FILTER. På dette tidspunkt har vi:

 
 FREQUENCY ({FALSEFALSEFALSE1167},{1234567})

FREQUENCY returnerer et array med tal, der angiver et antal for hver værdi i dataarrayet, organiseret efter bin. Når et tal allerede er talt, returnerer FREQUENCY nul. Resultatet fra FREQUENCY er en matrix som denne:

 
{20000110} // result from FREQUENCY

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

hvordan man beregner nettonuværdien i excel

På dette tidspunkt kan vi omskrive formlen sådan:

 
= SUM (--({20000110}>0))

Vi kontrollerer for værdier større end nul, som konverterer tallene til SAND eller FALSK:

 
= SUM (--({TRUEFALSEFALSEFALSEFALSETRUETRUEFALSE}))

Så bruger vi en dobbelt-negativ for at tvinge de logiske værdier til 1s og 0s:

 
= SUM ({10000110})

Endelig blev SUM-funktion returnerer 3 som det endelige resultat.

Bemærk: dette er en matrixformel og skal indtastes ved hjælp af Control + Shift + Enter.

Håndtering af tomme celler i området

Hvis nogen celler i området er tomme, skal du justere formlen for at forhindre, at tomme celler overføres til MATCH-funktionen, hvilket vil give en fejl. Du kan gøre dette ved at tilføje en anden indlejret IF-funktion for at kontrollere, om der er tomme celler:

 
{= SUM (--( FREQUENCY ( IF (B5:B11'', IF (C5:C11=G5, MATCH (B5:B11,B5:B11,0))), ROW (B5:B11)- ROW (B5)+1)>0))}

Med to kriterier

Hvis du har to kriterier, kan du udvide formelens logik ved at tilføje en anden indlejret IF:

 
{= SUM (--( FREQUENCY ( IF (c1, IF (c2, MATCH (vals,vals,0))), ROW (vals)- ROW (vals.1st)+1)>0))}

Hvor c1 = kriterier1, c2 = kriterier2 og vals = værdiområdet.

Med boolsk logik

Med boolsk logik , kan du reducere nestede IF'er :

 
{= SUM (--( FREQUENCY ( IF ((criteria1)*(criteria2), MATCH (vals,vals,0)), ROW (vals)- ROW (vals.1st)+1)>0))}

Dette gør det lettere at tilføje og administrere yderligere kriterier.

Tilpasset fra Mike Givins fremragende bog om matrixformler, Control-Shift-Enter. Forfatter Dave Bruns


^