Generisk formel
=SUMPRODUCT(--(FREQUENCY(MATCH(data,data,0),ROW(data)-ROW(data.firstcell)+1)>0))
Resumé
For at tælle unikke tekstværdier i et interval kan du bruge en formel, der bruger flere funktioner: FREKVENS, MATCH, RÆDE og SUMPRODUCT. I det viste eksempel er formlen i F5:
=SUMPRODUCT(--(FREQUENCY(MATCH(B5:B14,B5:B14,0),ROW(B5:B14)-ROW(B5)+1)>0))
der returnerer 4, da der er 4 unikke navne i B5: B14.
Bemærk: En anden måde at tælle unikke værdier på er at bruge COUNTIF-funktionen. Dette er en meget enklere formel, men den kan køre langsomt på store datasæt. Med Excel 365 kan du bruge en enklere og hurtigere formel baseret på UNIK.
Forklaring
Denne formel er mere kompliceret end en lignende formel, der bruger FREKVENS til at tælle unikke numeriske værdier, fordi FREKVENS ikke fungerer med ikke-numeriske værdier. Som et resultat transformerer en stor del af formlen simpelthen de ikke-numeriske data til numeriske data, som FREKVENS kan håndtere.
Når man arbejder indefra og ud, bruges MATCH-funktionen til at få positionen for hvert element, der vises i dataene:
MATCH(B5:B14,B5:B14,0)
Resultatet fra MATCH er en matrix som denne:
(1;1;1;4;4;6;6;6;9;9)
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 3 gange på listen, vises han i dette array 3 gange som nummer 1.
Denne matrix tilføres FREKVENS som argumentet data_array . Den bins_array argument er konstrueret af denne del med formlen:
ROW(B5:B14)-ROW(B5)+1)
der bygger en sekventiel liste med tal for hver værdi i dataene:
(1;2;3;4;5;6;7;8;9;10)
På dette tidspunkt er FREQUENCY konfigureret således:
FREQUENCY((1;1;1;4;4;6;6;6;9;9),(1;2;3;4;5;6;7;8;9;10))
FREKVENS returnerer et array med tal, der angiver et antal for hvert nummer i dataarrayet, organiseret efter bin. Når et tal allerede er talt, returnerer FREQUENCY nul. Dette er en nøglefunktion i driften af denne formel. Resultatet fra FREQUENCY er en matrix som denne:
(3;0;0;2;0;3;0;0;2;0;0) // output from FREQUENCY
Bemærk: FREKVENS returnerer altid en matrix med et element mere end bins_array .
Vi kan nu omskrive formlen sådan:
=SUMPRODUCT(--((3;0;0;2;0;3;0;0;2;0;0)>0))
Dernæst kontrollerer vi for værdier større end nul (> 0), der konverterer tallene til SAND eller FALSK, og brug derefter en dobbelt negativ (-) til at konvertere de SANDE og FALSKE værdier til 1s og 0s. Nu har vi:
=SUMPRODUCT((1;0;0;1;0;1;0;0;1;0;0))
Endelig tilføjer SUMPRODUCT simpelthen tallene og returnerer det samlede antal, som i dette tilfælde er 4.
Håndtering af blanke celler
Tomme celler i området får formlen til at returnere en # N / A-fejl. For at håndtere tomme celler kan du bruge en mere kompliceret matrixformel, der bruger IF-funktionen til at filtrere tomme værdier ud:
(=SUM(IF(FREQUENCY(IF(data"", MATCH(data,data,0)),ROW(data)-ROW(data.firstcell)+1),1)))
Bemærk: tilføjelse af IF gør dette til en matrixformel, der kræver kontrol-skift-enter.
For mere information, se denne side.
Andre måder at tælle unikke værdier på
Hvis du har Excel 365, kan du bruge UNIQUE-funktionen til at tælle unikke værdier med en meget enklere formel.
En pivottabel er også en glimrende måde at tælle unikke værdier på.
