Generisk formel
(=SUM(--(FREQUENCY(IF(criteria,MATCH(vals,vals,0)),ROW(vals)-ROW(vals.first)+1)>0)))
Sammanfattning
För att räkna unika textvärden i ett intervall med kriterier kan du använda en matrisformel baserad på FREKVENS- och MATCH-funktionerna. I exemplet som visas är formeln i G6:
(=SUM(--(FREQUENCY(IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0)),ROW(B5:B11)-ROW(B5)+1)>0)))
som returnerar 3, eftersom tre olika personer arbetade med projektet Omega.
Obs: detta är en matrisformel och måste anges med kontroll + skift + enter.
Förklaring
Detta är en komplex formel som använder FREQUENCY för att räkna numeriska värden som härleds med MATCH-funktionen. Arbetar inifrån och ut, MATCH-funktionen används för att få positionen för varje värde som visas i data:
MATCH(B5:B11,B5:B11,0)
Resultatet från MATCH är en matris så här:
(1;1;3;1;1;6;7)
Eftersom MATCH alltid returnerar positionen för den första matchningen returnerar värden som visas mer än en gång i data samma position. Till exempel, eftersom "Jim" dyker upp fyra gånger i listan, dyker han upp i denna array fyra gånger som nummer 1.
Utanför MATCH-funktionen används IF-funktionen för att tillämpa kriterier, vilket i detta fall innebär att testa om projektet är "omega" (från cell G5):
IF(C5:C11=G5 // filter on "omega"
IF-funktionen fungerar som ett filter och låter bara värdena från MATCH passera om de är associerade med "omega". Resultatet är en array som denna:
(FALSE;FALSE;FALSE;1;1;6;7) // after filtering
Den filtrerade matrisen levereras direkt till FREQUENCY-funktionen som argumentet data_array . Därefter används ROW-funktionen för att skapa en sekventiell lista med siffror för varje värde i data:
ROW(B3:B12)-ROW(B3)+1
Detta skapar en array som denna:
(1;2;3;4;5;6;7;8;9;10)
som blir den bins_array argumentet i FILTER. Vid denna tidpunkt har vi:
FREQUENCY((FALSE;FALSE;FALSE;1;1;6;7),(1;2;3;4;5;6;7))
FREQUENCY returnerar en rad med siffror som indikerar ett antal för varje värde i dataarrayen, organiserad efter bin. När ett nummer redan har räknats kommer FREQUENCY att returnera noll. Resultatet från FREQUENCY är en matris så här:
(2;0;0;0;0;1;1;0) // result from FREQUENCY
Obs! FREKVENS returnerar alltid en matris med ett objekt till än bins_array .
Vid den här tiden kan vi skriva om formeln så här:
=SUM(--((2;0;0;0;0;1;1;0)>0))
Vi söker efter värden större än noll, vilket omvandlar siffrorna till SANT eller FALSKT:
=SUM(--((TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE)))
Sedan använder vi en dubbelnegativ för att tvinga de logiska värdena till 1s och 0s:
=SUM((1;0;0;0;0;1;1;0))
Slutligen returnerar SUM-funktionen 3 som slutresultat.
Obs: detta är en matrisformel och måste anges med Control + Shift + Enter.
Hantera tomma celler inom intervallet
Om några celler i intervallet är tomma måste du justera formeln för att förhindra att tomma celler skickas in i MATCH-funktionen, vilket ger ett fel. Du kan göra detta genom att lägga till en annan kapslad IF-funktion för att söka efter tomma celler:
(=SUM(--(FREQUENCY(IF(B5:B11"",IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0))),ROW(B5:B11)-ROW(B5)+1)>0)))
Med två kriterier
Om du har två kriterier kan du utöka logiken för formeln genom att lägga till en annan kapslad IF:
(=SUM(--(FREQUENCY(IF(c1,IF(c2,MATCH(vals,vals,0))),ROW(vals)-ROW(vals.1st)+1)>0)))
Där c1 = kriterier1, c2 = kriterier2 och vals = värdena.
Med boolesk logik
Med boolesk logik kan du minska kapslade IF: er:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),MATCH(vals,vals,0)),ROW(vals)-ROW(vals.1st)+1)>0)))
Detta gör det lättare att lägga till och hantera ytterligare kriterier.
