Generisk formel
=SUMPRODUCT(--(FREQUENCY(MATCH(data,data,0),ROW(data)-ROW(data.firstcell)+1)>0))
Sammanfattning
För att räkna unika textvärden i ett intervall kan du använda en formel som använder flera funktioner: FREKVENS, MATCH, RAD och SUMPRODUCT. I exemplet som visas är formeln i F5:
=SUMPRODUCT(--(FREQUENCY(MATCH(B5:B14,B5:B14,0),ROW(B5:B14)-ROW(B5)+1)>0))
som returnerar 4, eftersom det finns 4 unika namn i B5: B14.
Obs: Ett annat sätt att räkna unika värden är att använda COUNTIF-funktionen. Detta är en mycket enklare formel, men den kan köras långsamt på stora datamängder. Med Excel 365 kan du använda en enklare och snabbare formel baserad på UNIK.
Förklaring
Den här formeln är mer komplicerad än en liknande formel som använder FREQUENCY för att räkna unika numeriska värden eftersom FREQUENCY inte fungerar med icke-numeriska värden. Som ett resultat omvandlar en stor del av formeln helt enkelt icke-numeriska data till numeriska data som FREQUENCY kan hantera.
Arbetar inifrån och ut, MATCH-funktionen används för att få positionen för varje objekt som visas i data:
MATCH(B5:B14,B5:B14,0)
Resultatet från MATCH är en matris så här:
(1;1;1;4;4;6;6;6;9;9)
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" visas tre gånger i listan, dyker han upp i denna array tre gånger som nummer 1.
Denna matris matas in i FREQUENCY som argumentet data_array . Den bins_array argumentet är konstruerad av denna del med formeln:
ROW(B5:B14)-ROW(B5)+1)
som bygger en sekventiell lista med siffror för varje värde i data:
(1;2;3;4;5;6;7;8;9;10)
Vid denna tidpunkt är FREQUENCY konfigurerad så här:
FREQUENCY((1;1;1;4;4;6;6;6;9;9),(1;2;3;4;5;6;7;8;9;10))
FREQUENCY returnerar en matris med siffror som indikerar ett antal för varje nummer i dataarrayen, organiserad efter bin. När ett nummer redan har räknats kommer FREQUENCY att returnera noll. Detta är en nyckelfunktion i funktionen för denna formel. Resultatet från FREQUENCY är en matris så här:
(3;0;0;2;0;3;0;0;2;0;0) // output from FREQUENCY
Obs! FREKVENS returnerar alltid en matris med ett objekt till än bins_array .
Vi kan nu skriva om formeln så här:
=SUMPRODUCT(--((3;0;0;2;0;3;0;0;2;0;0)>0))
Därefter kontrollerar vi om värden är större än noll (> 0), som omvandlar siffrorna till SANT eller FALSKT och använder sedan en dubbelnegativ (-) för att konvertera SANT och FALSKT till 1s och 0s. Nu har vi:
=SUMPRODUCT((1;0;0;1;0;1;0;0;1;0;0))
Slutligen lägger SUMPRODUCT helt enkelt upp siffrorna och returnerar summan, som i detta fall är 4.
Hantera tomma celler
Tomma celler i intervallet kommer att få formeln att returnera ett # N / A-fel. För att hantera tomma celler kan du använda en mer komplicerad matrisformel som använder funktionen IF för att filtrera bort tomma värden:
(=SUM(IF(FREQUENCY(IF(data"", MATCH(data,data,0)),ROW(data)-ROW(data.firstcell)+1),1)))
Obs: att lägga till IF gör detta till en matrisformel som kräver kontroll-skift-enter.
För mer information, se den här sidan.
Andra sätt att räkna unika värden
Om du har Excel 365 kan du använda funktionen UNIK för att räkna unika värden med en mycket enklare formel.
En pivottabell är också ett utmärkt sätt att räkna unika värden.
