Tell distinkte verdier i Excel-pivottabell (Enkel trinn-for-trinn-guide)

Excel -pivottabeller er fantastiske (jeg vet at jeg nevner dette hver gang jeg skriver om pivottabeller, men det er sant).

Med en grunnleggende forståelse og litt dra og slipp, kan du få en masse arbeid gjort på få sekunder.

Selv om mye kan gjøres med noen få klikk i pivottabeller, er det noen ting som trenger noen ekstra trinn eller litt arbeid.

Og en slik ting er å telle distinkte verdier i et pivottabell.

I denne opplæringen vil jeg vise deg hvordan du teller distinkte verdier samt unike verdier i en Excel -pivottabell.

Men før jeg hopper inn i hvordan du teller distinkte verdier, er det viktig å forstå forskjellen mellom "distinkt antall" og "unik telling"

Distinct Count Vs Unique Count

Selv om disse kan virke som det samme, det er ikke.

Nedenfor er et eksempel der det er et datasett med navn, og jeg har listet unike og forskjellige navn separat.

Unike verdier/navn er de som bare skjer en gang. Dette betyr at alle navnene som gjentas og har duplikater ikke er unike. Unike navn er oppført i kolonne C i datasettet ovenfor

Tydelige verdier/navn er de som forekommer minst én gang i datasettet. Så hvis et navn vises tre ganger, regnes det fremdeles som et tydelig navn. Dette kan oppnås ved å fjerne dupliserte verdier/navn og beholde alle de forskjellige. Særskilte navn er oppført i kolonne B i datasettet ovenfor.

Basert på det jeg har sett, de fleste ganger når folk sier at de ønsker å få den unike tellingen i et pivottabell, betyr de faktisk distinkt antall, det er det jeg dekker i denne opplæringen.

Tell distinkte verdier i Excel -pivottabell

Anta at du har salgsdataene som vist nedenfor:

Klikk her for å laste ned eksempelfilen og følge med

Med datasettet ovenfor, la oss si at du vil finne svaret på følgende spørsmål:

1. Hvor mange salgsrepresentanter er det i hver region (som ikke er annet enn det forskjellige antallet salgsrepresentanter i hver region)?
2. Hvor mange selgere solgte skriveren i 2020-2022?

Selv om pivottabeller umiddelbart kan oppsummere dataene med noen få klikk, må du ta noen flere trinn for å få tellingen av forskjellige verdier.

Hvis du bruker Excel 2013 eller versjoner etter det, det er en innebygd funksjonalitet i pivottabellen som raskt gir deg den forskjellige tellingen. Og hvis du bruker Excel 2010 eller tidligere versjoner, må du endre kildedataene ved å legge til en hjelperkolonne.

Følgende to metoder er dekket i denne opplæringen:

• Legge til en hjelperkolonne i det originale datasettet for å telle unike verdier (fungerer i alle versjoner).
• Legge til dataene i en datamodell og bruke alternativet Distinct Count (tilgjengelig i Excel 2013 og versjoner etter det).

Det er en tredje metode som Roger viser i denne artikkelen (som han kaller Pivot the Pivot Table -metoden).

La oss komme i gang!

Legge til en hjelperkolonne i datasettet

Merk: Hvis du bruker Excel 2013 og nyere versjoner, hopper du over denne metoden og går til neste (da den bruker en innebygd pivottabellfunksjonalitet - Utpreget Count).

Dette er en enkel måte å telle distinkte verdier i pivottabellen på, ettersom du bare trenger å legge til en hjelperkolonne i kildedataene. Når du har lagt til en hjelperkolonne, kan du deretter bruke dette nye datasettet til å beregne det forskjellige antallet.

Selv om dette er en enkel løsning, er det noen ulemper med denne metoden (dekket senere i denne opplæringen).

La meg først vise deg hvordan du legger til en hjelperkolonne og får en tydelig telling.

Anta at jeg har datasettet som vist nedenfor:

Legg til følgende formel i kolonne F og bruk den for alle cellene som har data i de tilstøtende kolonnene.

= HVIS (COUNTIFS ($ C $ 2: C2, C2, $ B $ 2: B2, B2)> 1,0,1)

Formelen ovenfor bruker COUNTIFS -funksjonen til å telle antall ganger et navn vises i den gitte regionen. Vær også oppmerksom på at kriterieområdet er $ C $ 2: C2 og $ B $ 2: B2. Dette betyr at den fortsetter å ekspandere når du går nedover kolonnen.

For eksempel, i celle E2, er kriterieområdene $ C $ 2: C2 og $ B $ 2: B2, og i celle E3 utvides disse områdene til $ C $ 2: C3 og $ B $ 2: B3.

Dette sikrer at COUNTIFS -funksjonen teller den første forekomsten av et navn som 1, den andre forekomsten av navnet som 2, og så videre.

Siden vi bare ønsker å få de forskjellige navnene, brukes IF -funksjonen som returnerer 1 når et navn vises for en region første gang og returnerer 0 når det vises igjen. Dette sikrer at bare forskjellige navn telles og ikke gjentakelsene.

Nedenfor ser du hvordan datasettet ditt vil se ut når du har lagt til hjelperkolonnen.

Nå som vi har endret kildedataene, kan vi bruke dette til å lage et pivottabell og bruke hjelpekolonnen for å få det forskjellige antallet salgsrepresentanter i hver region.

Nedenfor er trinnene for å gjøre dette:

1. Velg en celle i datasettet.
2. Klikk på Sett inn -fanen.
3. Klikk på Pivot Table (eller bruk hurtigtasten - ALT + N + V)
4. I dialogboksen Opprett pivottabell må du kontrollere at tabellen/området er riktig (og inneholder hjelperkolonnen) og ‘Nytt regneark’ i valgt.
5. Klikk OK.

Trinnene ovenfor vil sette inn et nytt ark som har pivottabellen.

Dra "Region" -feltet i rader -området og "D -telle" -feltet i verdier -området.

Du får et pivottabell som vist nedenfor:

Nå kan du endre kolonneoverskriften fra 'Sum of D count' til 'Sales Rep'.

Ulemper ved å bruke en hjelperkolonne:

Selv om denne metoden er ganske rett frem, må jeg markere noen få ulemper ved å endre kildedataene i et pivottabell:

• Datakilden med hjelperkolonnen er ikke like dynamisk som en pivottabell. Selv om du kan kutte og kutte dataene på en hvilken som helst måte med en pivottabell, mister du en del av den evnen når du bruker en hjelperkolonne. La oss si at du legger til en hjelperkolonne for å få tellingen av en distinkt salgsrepresentant i hver region. Hva om du også ønsker å få et tydelig antall salgsrepresentanter som selger skrivere. Du må gå tilbake til kildedataene og endre hjelpekolonneformelen (eller legge til en ny hjelperkolonne).
• Siden du legger til flere data i pivottabellkilden (som også blir lagt til i pivotbufferen), kan dette føre til en større størrelse på Excel -filen.
• Siden vi bruker en Excel -formel, kan det gjøre Excel -arbeidsboken treg hvis du har tusenvis av rader med data.

Legg til data i datamodellen og oppsummer ved hjelp av distinkt antall

Pivottabell la til ny funksjonalitet i Excel 2013 som lar deg få det forskjellige antallet mens du oppsummerer datasettet.

Hvis du bruker en tidligere versjon, kan du ikke bruke denne metoden (som du burde prøve å legge til hjelpekolonnen som vist i metoden ovenfor denne).

Anta at du har et datasett som vist nedenfor, og du vil få tellingen av den unike salgsrepresentanten i hver region.

Nedenfor er trinnene for å få en distinkt tellingsverdi i pivottabellen:

1. Velg en celle i datasettet.
2. Klikk på Sett inn -fanen.
3. Klikk på Pivot Table (eller bruk hurtigtasten - ALT + N + V)
4. I dialogboksen Opprett pivottabell må du kontrollere at tabellen/området er riktig og at nytt regneark er valgt.
5. Merk av i boksen som sier "Legg til disse dataene i datamodellen"
6. Klikk OK.

Trinnene ovenfor vil sette inn et nytt ark som har det nye pivottabellen.

Dra regionen i rader -området og salgsrepresentant i området Verdier. Du får et pivottabell som vist nedenfor:

Ovenstående pivottabell gir det totale antallet salgsrepresentanter i hver region (og ikke det forskjellige antallet).

For å få den distinkte tellingen i pivottabellen, følg trinnene nedenfor:

1. Høyreklikk på en celle i kolonnen "Antall salgsrepresentanter".
2. Klikk på Verdifeltinnstillinger
3. I dialogboksen Verdifeltinnstillinger velger du "Distinct Count" som beregningstype (du må kanskje rulle nedover listen for å finne den).
4. Klikk OK.

Du vil legge merke til at navnet på kolonnen endres fra "Count of Sales Rep" til "Distinct Count of Sales Rep". Du kan endre det til hva du vil.

Noen ting du vet når du legger til dataene dine i datamodellen:

• Hvis du lagrer dataene dine i datamodellen og deretter åpner i en eldre versjon av Excel, viser den deg en advarsel - "Noen pivottabellfunksjoner blir ikke lagret". Det er ikke sikkert du ser det forskjellige antallet (og datamodellen) når den åpnes i en eldre versjon som ikke støtter den.
• Når du legger til dataene dine i en datamodell og lager en pivottabell, vil det ikke vise alternativene for å legge til beregnede felt og beregnede kolonner.

Klikk her for å laste ned eksempelfilen

Hva om du vil telle unike verdier (og ikke forskjellige verdier)?

Hvis du vil telle unike verdier, har du ingen innebygd funksjonalitet i pivottabellen, og du må bare stole på hjelperkolonner.

Husk - Unike verdier og distinkte verdier er ikke det samme. Klikk her for å vite forskjellen.

Et eksempel kan være når du har datasettet nedenfor, og du vil finne ut hvor mange salgsrepresentanter som er unike for hver region. Dette betyr at de opererer bare i en bestemt region og ikke de andre.

I slike tilfeller må du opprette en av mer enn én hjelperkolonner.

For dette tilfellet gjør formelen nedenfor trikset:

= IF (IF (COUNTIFS ($ C $ 2: $ C $ 1001, C2, $ B $ 2: $ B $ 1001, B2)/COUNTIF ($ C $ 2: $ C $ 1001, C2) 1,0,1), 0)

Formelen ovenfor kontrollerer om et selgernavn bare forekommer i én region eller i mer enn én region. Det gjør det ved å telle antallet forekomster av et navn i en region og dele det med det totale antallet forekomster av navnet. Hvis verdien er mindre enn 1, indikerer det at navnet forekommer i to eller flere enn to regioner.

Hvis navnet forekommer i mer enn én region, returnerer det en 0 ellers returnerer den en.

Formelen sjekker også om navnet gjentas i samme region eller ikke. Hvis navnet gjentas, returnerer bare den første forekomsten av navnet verdien 1, og alle andre forekomster returnerer 0.

Dette kan virke litt komplisert, men det avhenger igjen av hva du prøver å oppnå.

Så hvis du vil telle unike verdier i en pivottabell, bruker hjelperkolonner, og hvis du vil telle distinkte verdier, kan du bruke den innebygde funksjonaliteten (i Excel 2013 og nyere) eller bruke en hjelperkolonne.

Klikk her for å laste ned eksempelfilen

Du kan også like følgende opplæringsprogrammer for pivottabeller:

• Slik filtrerer du data i et pivottabell i Excel
• Slik grupperer du datoer i pivottabeller i Excel
• Hvordan gruppere tall i pivottabell i Excel
• Slik bruker du betinget formatering i et pivottabell i Excel
• Skiver i Excel -pivottabell
• Slik oppdaterer du pivottabell i Excel
• Slett et pivottabell i Excel