I denne opplæringen vil du lære hvordan å telle 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:
For det formål av denne opplæringen, jeg vil navnet området A2:A10 som NAVN. Fremover vil vi bruke denne navngitt område i formler.
See Also: How to create Named Ranges in Excel.
I dette datasettet er det en repetisjon i NAVN utvalg., For å få antallet unike navn fra dette datasettet (A2:A10), kan vi bruke en kombinasjon av COUNTIF og SUMPRODUCT funksjoner som vist nedenfor:
=SUMPRODUCT(1/COUNTIF(NAVN,NAVN))
Hvordan gjør denne formelen fungerer?
La oss bryte ned denne formelen for å få en bedre forståelse:
- COUNTIF(NAVN,NAVN)
- Denne delen av formelen returnerer en matrise. I ovennevnte eksempel ville det være {2;2;3;1;3;1;2;3;2}. Tallene her viser hvor mange ganger en verdi oppstår i det angitte celleområdet.,
For eksempel, navnet er Bob, som forekommer to ganger i listen, og derfor ville det gå tilbake tallet 2 for Bob. På samme måte, Steve oppstår tre ganger og dermed 3 er tilbake for Steve.
- Denne delen av formelen returnerer en matrise. I ovennevnte eksempel ville det være {2;2;3;1;3;1;2;3;2}. Tallene her viser hvor mange ganger en verdi oppstår i det angitte celleområdet.,
- 1/COUNTIF(NAVN,NAVN)
- Denne delen av formelen returnerer en matrise– {0.5;0.5;0.333333333333333;1;0.333333333333333;1;0.5;0.333333333333333;0.5}
Siden vi har delt 1 av tabellen, gir denne tabellen.
For eksempel, det første elementet i matrisen tilbake over var 2. Når 1 er delt på 2, går den tilbake .5.,
- Denne delen av formelen returnerer en matrise– {0.5;0.5;0.333333333333333;1;0.333333333333333;1;0.5;0.333333333333333;0.5}
- SUMPRODUCT(1/COUNTIF(NAVN,NAVN))
- SUMPRODUCT legger bare alle disse tallene. Vær oppmerksom på at hvis Bob skjer to ganger i listen ovenfor matrise returnerer .5 hvor Bir navn dukket opp i listen. På samme måte, siden Steve vises tre ganger i liste, matrise returnerer .3333333 når Steve navn vises. Når vi legger til tallene for hvert navn, det ville alltid gå tilbake 1. Og hvis vi legger til alle tallene, det ville returnere det totale antallet unike navnene i listen.
Denne formelen fungerer fint inntil du ikke har noen tomme celler i området., Men hvis du har noen tomme celler, det ville returnere a #DIV/0! feil.
Hvordan å Håndtere TOMME celler?
La oss først forstå hvorfor den returnerer en feilmelding når det er en tom celle i området. Anta at vi har datasettet som er vist under (med celle A3 å være tom):
Og siden vi å dele 1 av dette hele matrisen, det gir en #DIV/0! feil.,
for Å håndtere denne divisjonen feil i tilfelle av tomme celler, kan du bruke følgende formel:
=SUMPRODUCT((1/COUNTIF(NAVN,NAVN&»»)))
En endring som vi har gjort i denne formelen er kriteriene som er en del av COUNTIF funksjon. Vi har brukt NAVN&»» i stedet for NAVN. Ved å gjøre dette, formelen returnerer antall tomme celler (tidligere det returneres 0 hvor det var en tom celle).
MERK: Denne formelen ville telle tomme celler som en unik verdi og returnere det i resultatet.,
I eksempelet ovenfor, kan resultatet bør være 5, men det returnerer 6 som den tomme cellen er regnet som en av de unike verdiene.
Her er formelen som tar vare på tomme celler og teller ikke det i det endelige resultatet:
=SUMPRODUCT((NAVN<>»»)/COUNTIF(NAVN,NAVN&»»))
I denne formelen, i stedet for 1 som teller, vi har brukt NAVN<>»»., Denne returnerer en matrise av TRUEs og FALSEs. Den returnerer FALSE hvis det er en tom celle. Siden TRUE tilsvarer 1 og FALSKE tilsvarer 0 i beregningene, tomme celler er ikke telles som teller er 0 (USANN).
Nå som vi har den grunnleggende skjelett av formelen klar, og vi kan gå et skritt videre og telle ulike datatyper.
Hvordan å Telle Unike Verdier i Excel som er Tekst
Vi vil bruke det samme konseptet som er diskutert ovenfor til å lage formelen som bare vil telle tekst verdier som er unike.,
Her er formelen som vil telle antallet unike tekst-verdier i Excel:
=SUMPRODUCT((ISTEXT(NAVN)/COUNTIF(NAVN,NAVN&»»)))
Alt vi har gjort er brukt formelen ISTEXT(NAVN) som teller. Den returnerer TRUE når cellen inneholder tekst, og FALSE hvis det ikke. Den vil ikke telle tomme celler, men vil telle celler som har en tom streng («»).,
Hvordan å Telle Unike Verdier i Excel som er Numerisk
Her er formelen som vil telle antallet unike numeriske verdier i Excel –
=SUMPRODUCT((ISNUMBER(NAVN))/COUNTIF(NAVN,NAVN&»»))
Her er vi ved hjelp av ISNUMBER(NAVN) som teller. Den returnerer TRUE når cellen inneholder numeriske data type, og FALSE hvis det ikke. Det teller ikke tomme celler.
Du Kan Også Gjerne Følgende Excel-Tutorials:
- Hvordan å Telle Celler som Inneholder Tekst-Strenger.
- Hvordan å Telle Antall Ord i Excel.,
- Telle Celler Basert på bakgrunnsfargen i Excel.
- ved Hjelp av Flere Kriterier i Excel COUNTIF og COUNTIFS Funksjon.