Generisk 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-funktionerne. 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.
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:
(1;1;3;1;1;6;7)
Da MATCH altid returnerer positionen for det første match, returnerer værdier, der vises mere end en gang i dataene, 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 bruges IF-funktionen 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:
(FALSE;FALSE;FALSE;1;1;6;7) // after filtering
Det filtrerede array leveres direkte til FREQUENCY-funktionen som data_array- argumentet. Dernæst bruges ROW-funktionen til at oprette en sekventiel liste med tal for hver værdi i dataene:
ROW(B3:B12)-ROW(B3)+1
Dette skaber en matrix som denne:
(1;2;3;4;5;6;7;8;9;10)
som bliver bins_array- argumentet i FILTER. På dette tidspunkt har vi:
FREQUENCY((FALSE;FALSE;FALSE;1;1;6;7),(1;2;3;4;5;6;7))
FREQUENCY returnerer en matrix 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:
(2;0;0;0;0;1;1;0) // result from FREQUENCY
Bemærk: FREKVENS returnerer altid en matrix med et element mere end bins_array .
På dette tidspunkt kan vi omskrive formlen sådan:
=SUM(--((2;0;0;0;0;1;1;0)>0))
Vi kontrollerer for værdier større end nul, som konverterer tallene til SAND eller FALSK:
=SUM(--((TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE)))
Derefter bruger vi en dobbelt-negativ til at tvinge de logiske værdier til 1s og 0s:
=SUM((1;0;0;0;0;1;1;0))
Endelig returnerer SUM-funktionen 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ærdierne.
Med boolsk logik
Med boolsk logik kan du reducere indlejrede 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.
