I denne opplæringen lærer du hvordan du teller unike verdier i Excel ved hjelp av formler (COUNTIF og SUMPRODUCT -funksjoner).
Hvordan telle unike verdier i Excel
La oss si at vi har et datasett som vist nedenfor:
I forbindelse med denne opplæringen vil jeg nevne området A2: A10 som NAMES. Fremover vil vi bruke dette navngitte området i formlene.
Se også: Hvordan lage navngitte områder i Excel.
I dette datasettet er det en repetisjon i NAMES -området. For å få antallet unike navn fra dette datasettet (A2: A10), kan vi bruke en kombinasjon av funksjonene COUNTIF og SUMPRODUCT som vist nedenfor:
= SUMPRODUCT (1/COUNTIF (NAMES, NAMES))
Hvordan fungerer denne formelen?
La oss bryte ned denne formelen for å få en bedre forståelse:
- TELLING (NAVN, NAVN)
- Denne delen av formelen returnerer en matrise. I eksemplet ovenfor vil det være {2; 2; 3; 1; 3; 1; 2; 3; 2}. Tallene her angir hvor mange ganger en verdi oppstår i det gitte celleområdet.
For eksempel er navnet Bob, som forekommer to ganger i listen, og derfor vil det returnere tallet 2 for Bob. På samme måte skjer Steve tre ganger, og derfor returneres 3 for Steve.
- Denne delen av formelen returnerer en matrise. I eksemplet ovenfor vil det være {2; 2; 3; 1; 3; 1; 2; 3; 2}. Tallene her angir hvor mange ganger en verdi oppstår i det gitte celleområdet.
- 1/COUNTIF (NAMES, NAMES)
- Denne delen av formelen vil returnere en matrise - {0,5; 0,5; 0,333333333333333; 1; 0,333333333333333; 1; 0,5; 0,333333333333333; 0,5}
Siden vi har delt 1 med matrisen, returnerer den denne matrisen.
For eksempel var det første elementet i matrisen som ble returnert ovenfor 2. Når 1 er delt på 2, returnerer det .5.
- Denne delen av formelen vil returnere en matrise - {0,5; 0,5; 0,333333333333333; 1; 0,333333333333333; 1; 0,5; 0,333333333333333; 0,5}
- SUMPRODUCT (1/COUNTIF (NAMES, NAMES))
- SUMPRODUCT legger ganske enkelt til alle disse tallene. Vær oppmerksom på at hvis Bob forekommer to ganger i listen, returnerer matrisen ovenfor .5 uansett hvor Bob -navnet dukket opp i listen. På samme måte, siden Steve vises tre ganger på listen, returnerer matrisen .3333333 når Steve -navnet vises. Når vi legger til tallene for hvert navn, vil det alltid returnere 1. Og hvis vi legger til alle tallene, vil det returnere det totale antallet unike navn på listen.
Denne formelen fungerer fint til du ikke har noen tomme celler i området. Men hvis du har noen tomme celler, vil den returnere en #DIV/0! feil.
Hvordan håndtere BLANKE celler?
La oss først forstå hvorfor den returnerer en feil når det er en tom celle i området. Anta at vi har datasettet som vist nedenfor (med celle A3 er tom):
Hvis vi bruker den samme formelen som vi brukte ovenfor, returnerer COUNTIF -delen av formelen en matrise {2; 0; 3; 1; 3; 1; 2; 3; 1}. Siden det ikke er tekst i celle A3, returneres antallet som 0.
Og siden vi deler 1 med hele denne matrisen, returnerer den en #DIV/0! feil.
For å håndtere denne delingsfeilen ved tomme celler, bruk formelen nedenfor:
= SUMPRODUCT ((1/COUNTIF (NAMES, NAMES & ””)))
En endring vi har gjort i denne formelen er kriteriedelen av COUNTIF -funksjonen. Vi har brukt NAMES & ”” i stedet for NAMES. Ved å gjøre dette, ville formelen returnere antall tomme celler (tidligere returnerte den 0 der det var en tom celle).
MERK: Denne formelen vil telle tomme celler som en unik verdi og returnere den i resultatet.
I eksemplet ovenfor skal resultatet være 5, men det returnerer 6 ettersom den tomme cellen regnes som en av de unike verdiene.
Her er formelen som tar seg av de tomme cellene og ikke teller det med i det endelige resultatet:
= SUMPRODUCT ((NAMES ””)/COUNTIF (NAMES, NAMES & ””))
I denne formelen, i stedet for 1 som teller, har vi brukt NAMES ””. Dette returnerer en rekke SANN og FALSK. Den returnerer FALSK når det er en tom celle. Siden TRUE tilsvarer 1 og FALSE tilsvarer 0 i beregninger, telles ikke tomme celler som telleren er 0 (FALSE).
Nå som vi har grunnskjelettet til formelen klart, kan vi gå et skritt videre og telle forskjellige datatyper.
Hvordan telle unike verdier i Excel som er tekst
Vi vil bruke det samme konseptet som er diskutert ovenfor for å lage formelen som bare vil telle tekstverdier som er unike.
Her er formelen som vil telle unike tekstverdier i Excel:
= SUMPRODUCT ((ISTEXT (NAMES)/COUNTIF (NAMES, NAMES & ””)))
Alt vi har gjort er å bruke formelen ISTEXT (NAMES) som teller. Den returnerer SANN når cellen inneholder tekst, og FALSK hvis den ikke gjør det. Det teller ikke tomme celler, men teller celler som har en tom streng (“”).
Hvordan telle unike verdier i Excel som er numeriske
Her er formelen som vil telle unike numeriske verdier i Excel
= SUMPRODUCT ((ISNUMBER (NAMES))/COUNTIF (NAMES, NAMES & ””))
Her bruker vi ISNUMBER (NAMES) som teller. Den returnerer SANN når cellen inneholder numerisk datatype, og FALSK hvis den ikke gjør det. Det teller ikke tomme celler.
