Generisk formel
=SUMPRODUCT(--(FREQUENCY(MATCH(data,data,0),ROW(data)-ROW(data.firstcell)+1)>0))
Sammendrag
For å telle unike tekstverdier i et område, kan du bruke en formel som bruker flere funksjoner: FREKVENS, MATCH, RAD og SUMPRODUCT. I eksemplet som vises er formelen i F5:
=SUMPRODUCT(--(FREQUENCY(MATCH(B5:B14,B5:B14,0),ROW(B5:B14)-ROW(B5)+1)>0))
som returnerer 4, siden det er 4 unike navn i B5: B14.
Merk: En annen måte å telle unike verdier på er å bruke COUNTIF-funksjonen. Dette er en mye enklere formel, men den kan kjøre sakte på store datasett. Med Excel 365 kan du bruke en enklere og raskere formel basert på UNIK.
Forklaring
Denne formelen er mer komplisert enn en lignende formel som bruker FREQUENCY til å telle unike numeriske verdier fordi FREQUENCY ikke fungerer med ikke-numeriske verdier. Som et resultat forvandler en stor del av formelen ganske enkelt ikke-numeriske data til numeriske data som FREKVENS kan håndtere.
MATCH-funksjonen arbeides fra innsiden og ut for å få posisjonen til hvert element som vises i dataene:
MATCH(B5:B14,B5:B14,0)
Resultatet fra MATCH er en matrise som dette:
(1;1;1;4;4;6;6;6;9;9)
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 3 ganger i listen, dukker han opp i denne matrisen 3 ganger som nummer 1.
Denne matrisen mates inn i FREKVENS som argumentet data_array . Den bins_array Argumentet er bygget opp av denne delen av formelen:
ROW(B5:B14)-ROW(B5)+1)
som bygger en sekvensiell liste med tall for hver verdi i dataene:
(1;2;3;4;5;6;7;8;9;10)
På dette punktet er FREQUENCY konfigurert slik:
FREQUENCY((1;1;1;4;4;6;6;6;9;9),(1;2;3;4;5;6;7;8;9;10))
FREQUENCY returnerer en matrise med tall som indikerer et antall for hvert tall i dataarrayet, organisert etter søppel. Når et tall allerede er talt, vil FREKVENS returnere null. Dette er en nøkkelfunksjon i driften av denne formelen. Resultatet fra FREQUENCY er en matrise som dette:
(3;0;0;2;0;3;0;0;2;0;0) // output from FREQUENCY
Merk: FREKVENS returnerer alltid en matrise med ett mer element enn bins_array .
Vi kan nå omskrive formelen slik:
=SUMPRODUCT(--((3;0;0;2;0;3;0;0;2;0;0)>0))
Deretter sjekker vi for verdier større enn null (> 0), som konverterer tallene til SANT eller FALSK, og deretter bruker vi en dobbeltnegativ (-) for å konvertere SANNE og FALSE verdiene til 1s og 0s. Nå har vi:
=SUMPRODUCT((1;0;0;1;0;1;0;0;1;0;0))
Til slutt legger SUMPRODUCT bare til tallene og returnerer totalen, som i dette tilfellet er 4.
Håndtering av blanke celler
Tomme celler i området vil føre til at formelen returnerer en # N / A-feil. For å håndtere tomme celler kan du bruke en mer komplisert matriseformel som bruker IF-funksjonen til å filtrere ut tomme verdier:
(=SUM(IF(FREQUENCY(IF(data"", MATCH(data,data,0)),ROW(data)-ROW(data.firstcell)+1),1)))
Merk: å legge til IF gjør dette til en matriseformel som krever kontroll-skift-enter.
For mer informasjon, se denne siden.
Andre måter å telle unike verdier på
Hvis du har Excel 365, kan du bruke UNIQUE-funksjonen til å telle unike verdier med en mye enklere formel.
En pivottabell er også en utmerket måte å telle unike verdier på.
