Generisk formel
(=SUM(--(FREQUENCY(IF(criteria,MATCH(vals,vals,0)),ROW(vals)-ROW(vals.first)+1)>0)))
Sammendrag
For å telle unike tekstverdier i et område med kriterier, kan du bruke en matriseformel basert på FREKVENS- og MATCH-funksjonene. I eksemplet vist er formelen i G6:
(=SUM(--(FREQUENCY(IF(C5:C11=G5,MATCH(B5:B11,B5:B11,0)),ROW(B5:B11)-ROW(B5)+1)>0)))
som returnerer 3, siden tre forskjellige personer jobbet med prosjektet Omega.
Merk: dette er en matriseformel og må angis med kontroll + skift + enter.
Forklaring
Dette er en kompleks formel som bruker FREKVENS til å telle numeriske verdier som er avledet med MATCH-funksjonen. MATCH-funksjonen fungerer fra innsiden og ut og brukes til å få posisjonen til hver verdi som vises i dataene:
MATCH(B5:B11,B5:B11,0)
Resultatet fra MATCH er en matrise som dette:
(1;1;3;1;1;6;7)
Fordi MATCH alltid returnerer posisjonen til den første kampen, gir verdier som vises mer enn en gang i dataene samme posisjon. For eksempel, fordi "Jim" vises 4 ganger i listen, dukker han opp i denne matrisen 4 ganger som nummer 1.
Utenfor MATCH-funksjonen brukes IF-funksjonen til å anvende kriterier, som i dette tilfellet innebærer testing av om prosjektet er "omega" (fra celle G5):
IF(C5:C11=G5 // filter on "omega"
IF-funksjonen fungerer som et filter, og lar bare verdiene fra MATCH passere hvis de er assosiert med "omega". Resultatet er en matrise som dette:
(FALSE;FALSE;FALSE;1;1;6;7) // after filtering
Den filtrerte matrisen leveres direkte til FREKVENS-funksjonen som argumentet data_array . Deretter brukes ROW-funksjonen til å lage en sekvensiell liste med tall for hver verdi i dataene:
ROW(B3:B12)-ROW(B3)+1
Dette skaper en matrise som dette:
(1;2;3;4;5;6;7;8;9;10)
som blir argumentet bins_array i FILTER. På dette punktet har vi:
FREQUENCY((FALSE;FALSE;FALSE;1;1;6;7),(1;2;3;4;5;6;7))
FREKVENS returnerer en rekke tall som indikerer en telling for hver verdi i dataarrayet, organisert etter bin. Når et tall allerede er talt, vil FREKVENS returnere null. Resultatet fra FREQUENCY er en matrise som dette:
(2;0;0;0;0;1;1;0) // result from FREQUENCY
Merk: FREKVENS returnerer alltid en matrise med ett mer element enn bins_array .
På dette punktet kan vi omskrive formelen slik:
=SUM(--((2;0;0;0;0;1;1;0)>0))
Vi ser etter verdier større enn null, som konverterer tallene til SANT eller FALSK:
=SUM(--((TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE)))
Deretter bruker vi en dobbeltnegativ for å tvinge de logiske verdiene til 1s og 0s:
=SUM((1;0;0;0;0;1;1;0))
Til slutt returnerer SUM-funksjonen 3 som det endelige resultatet.
Merk: dette er en matriseformel og må angis ved hjelp av Control + Shift + Enter.
Håndtering av tomme celler i området
Hvis noen celler i området er tomme, må du justere formelen for å forhindre at tomme celler overføres til MATCH-funksjonen, noe som vil føre til en feil. Du kan gjøre dette ved å legge til en annen nestet IF-funksjon for å se etter 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 utvide logikken til formelen ved å legge til en annen nestet 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 = verdiområdet.
Med boolsk logikk
Med boolsk logikk kan du redusere nestede IF-er:
(=SUM(--(FREQUENCY(IF((criteria1)*(criteria2),MATCH(vals,vals,0)),ROW(vals)-ROW(vals.1st)+1)>0)))
Dette gjør det lettere å legge til og administrere flere kriterier.
