I denne vejledning lærer du, hvordan du tæller unikke værdier i Excel ved hjælp af formler (COUNTIF- og SUMPRODUCT -funktioner).
Sådan tæller du unikke værdier i Excel
Lad os sige, at vi har et datasæt som vist herunder:
I forbindelse med denne vejledning vil jeg navngive området A2: A10 som NAMES. Fremover vil vi bruge dette navngivne område i formlerne.
Se også: Sådan oprettes navngivne områder i Excel.
I dette datasæt er der en gentagelse i NAMES -området. For at få optællingen af unikke navne fra dette datasæt (A2: A10) kan vi bruge en kombination af COUNTIF- og SUMPRODUCT -funktioner som vist nedenfor:
= SUMPRODUCT (1/COUNTIF (NAME, NAMES))
Hvordan fungerer denne formel?
Lad os nedbryde denne formel for at få en bedre forståelse:
- COUNTIF (NAVN, NAVN)
- Denne del af formlen returnerer en matrix. I ovenstående eksempel ville det være {2; 2; 3; 1; 3; 1; 2; 3; 2}. Tallene her angiver, hvor mange gange en værdi forekommer i det givne celleområde.
For eksempel er navnet Bob, der forekommer to gange på listen, derfor ville det returnere tallet 2 for Bob. Tilsvarende sker Steve tre gange, og derfor returneres 3 for Steve.
- Denne del af formlen returnerer en matrix. I ovenstående eksempel ville det være {2; 2; 3; 1; 3; 1; 2; 3; 2}. Tallene her angiver, hvor mange gange en værdi forekommer i det givne celleområde.
- 1/COUNTIF (NAVN, NAVN)
- Denne del af formlen ville returnere en matrix - {0,5; 0,5; 0,333333333333333; 1; 0,333333333333333; 1; 0,5; 0,333333333333333; 0,5}
Da vi har delt 1 med arrayet, returnerer det dette array.
For eksempel var det første element i arrayet, der blev returneret ovenfor, 2. Når 1 er divideret med 2, returneres det .5.
- Denne del af formlen ville returnere en matrix - {0,5; 0,5; 0,333333333333333; 1; 0,333333333333333; 1; 0,5; 0,333333333333333; 0,5}
- SUMPRODUCT (1/COUNTIF (NAME, NAMES))
- SUMPRODUCT tilføjer simpelthen alle disse tal. Bemærk, at hvis Bob forekommer to gange på listen, returnerer ovenstående array .5, uanset hvor Bob -navn optrådte på listen. På samme måde, da Steve vises tre gange på listen, vender arrayet tilbage .3333333, når Steve -navnet vises. Når vi tilføjer tallene for hvert navn, returnerer det altid 1. Og hvis vi tilføjer alle tallene, returnerer det det samlede antal unikke navne på listen.
Denne formel fungerer fint, indtil du ikke har nogen tomme celler i området. Men hvis du har tomme celler, returnerer den en #DIV/0! fejl.
Hvordan håndteres BLANK celler?
Lad os først forstå, hvorfor den returnerer en fejl, når der er en tom celle i området. Antag, at vi har datasættet som vist herunder (med celle A3 er tom):
Nu hvis vi bruger den samme formel, som vi brugte ovenfor, returnerer COUNTIF -delen af formlen et array {2; 0; 3; 1; 3; 1; 2; 3; 1}. Da der ikke er nogen tekst i celle A3, returneres dens antal som 0.
Og da vi deler 1 med hele dette array, returnerer det en #DIV/0! fejl.
For at håndtere denne opdelingsfejl i tilfælde af tomme celler skal du bruge nedenstående formel:
= SUMPRODUCT ((1/COUNTIF (NAME, NAMES & ””)))
En ændring, vi har foretaget i denne formel, er kriteriedelen af funktionen TÆLLING. Vi har brugt NAME & ”” i stedet for NAMES. Ved at gøre dette ville formlen returnere antallet af tomme celler (tidligere returnerede den 0, hvor der var en tom celle).
BEMÆRK: Denne formel vil tælle tomme celler som en unik værdi og returnere den i resultatet.
I eksemplet ovenfor skal resultatet være 5, men det returnerer 6, da den tomme celle tælles som en af de unikke værdier.
Her er formlen, der tager sig af de tomme celler og ikke tæller det med i det endelige resultat:
= SUMPRODUCT ((NAMES ””)/COUNTIF (NAME, NAMES & ””))
I denne formel har vi i stedet for 1 som tæller brugt NAVN ””. Dette returnerer en matrix med SAND og FALSK. Den returnerer FALSK, når der er en tom celle. Da SAND svarer til 1 og FALSK svarer til 0 i beregninger, tælles tomme celler ikke, da tælleren er 0 (FALSK).
Nu hvor vi har det grundlæggende skelet af formlen klar, kan vi gå et skridt videre og tælle forskellige datatyper.
Sådan tælles unikke værdier i Excel, der er tekst
Vi vil bruge det samme koncept diskuteret ovenfor til at oprette formlen, der kun tæller tekstværdier, der er unikke.
Her er formlen, der tæller unikke tekstværdier i Excel:
= SUMPRODUCT ((ISTEXT (NAME)/COUNTIF (NAME, NAMES & ””)))
Alt vi har gjort er at bruge formlen ISTEXT (NAMES) som tæller. Den returnerer SAND, når cellen indeholder tekst, og FALSK hvis den ikke gør det. Det tæller ikke tomme celler, men tæller celler, der har en tom streng (“”).
Sådan tælles unikke værdier i Excel, der er numeriske
Her er formlen, der vil tælle unikke numeriske værdier i Excel
= SUMPRODUCT ((ISNUMBER (NAMES))/COUNTIF (NAME, NAMES & ””))
Her bruger vi ISNUMBER (NAME) som tæller. Den returnerer SAND, når cellen indeholder numerisk datatype, og FALSK hvis den ikke gør det. Det tæller ikke tomme celler.
