Excel -pivottabeller er fantastiske (jeg ved, at jeg nævner dette hver gang, jeg skriver om pivottabeller, men det er sandt).
Med en grundlæggende forståelse og lidt træk og slip, kan du få en spand-belastning af arbejde udført på få sekunder.
Selvom der kan gøres meget med et par klik i pivottabeller, er der nogle ting, der kræver et par ekstra trin eller en lille smule arbejde.
Og en sådan ting er at tælle forskellige værdier i et pivottabel.
I denne vejledning viser jeg dig, hvordan du tæller forskellige værdier samt unikke værdier i en Excel -pivottabel.
Men før jeg springer ind i, hvordan man tæller forskellige værdier, er det vigtigt at forstå forskellen mellem 'distinkt tælling' og 'unik tælling'
Distinct Count Vs Unique Count
Selvom disse kan virke som det samme, det er ikke.
Nedenfor er et eksempel, hvor der er et datasæt med navne, og jeg har angivet unikke og forskellige navne separat.
Unikke værdier/navne er dem, der kun forekommer én gang. Det betyder, at alle de navne, der gentages og har dubletter, ikke er unikke. Unikke navne er angivet i kolonne C i ovenstående datasæt
Særlige værdier/navne er dem, der forekommer mindst én gang i datasættet. Så hvis et navn vises tre gange, tælles det stadig som ét særskilt navn. Dette kan opnås ved at fjerne de dublerede værdier/navne og beholde alle de forskellige. Særskilte navne er angivet i kolonne B i ovenstående datasæt.
Baseret på det, jeg har set, de fleste gange, hvor folk siger, at de vil have den unikke optælling i et pivottabel, betyder de faktisk forskellig tælling, hvilket er, hvad jeg dækker i denne vejledning.Tæl distinkte værdier i Excel -pivottabel
Antag, at du har salgsdataene som vist herunder:
Klik her for at downloade eksempelfilen og følge med
Lad os med ovenstående datasæt sige, at du vil finde svaret på følgende spørgsmål:
- Hvor mange salgsrepræsentanter er der i hver region (hvilket ikke er andet end det tydelige antal salgsrepræsentanter i hver region)?
- Hvor mange sælgere solgte printeren i2021-2022?
Mens pivottabeller øjeblikkeligt kan opsummere dataene med et par klik, skal du tage et par flere trin for at få optælling af forskellige værdier.
Hvis du bruger Excel 2013 eller versioner efter det, der er en indbygget funktionalitet i pivottabellen, der hurtigt giver dig det tydelige antal. Og hvis du bruger Excel 2010 eller tidligere versioner, bliver du nødt til at ændre kildedataene ved at tilføje en hjælperkolonne.Følgende to metoder er dækket i denne vejledning:
- Tilføjelse af en hjælperkolonne i det originale datasæt for at tælle unikke værdier (fungerer i alle versioner).
- Tilføjelse af data til en datamodel og brug af Distinct Count -indstilling (tilgængelig i Excel 2013 og versioner efter det).
Der er en tredje metode, som Roger viser i denne artikel (som han kalder Pivot the Pivot Table -metoden).
Lad os komme igang!
Tilføjelse af en hjælperkolonne i datasættet
Bemærk: Hvis du bruger Excel 2013 og højere versioner, skal du springe denne metode over og gå til den næste (da den bruger en indbygget pivottabel -funktionalitet - Tydelig tælling).Dette er en let måde at tælle forskellige værdier i pivottabellen, da du kun skal tilføje en hjælperkolonne til kildedataene. Når du har tilføjet en hjælperkolonne, kan du derefter bruge dette nye datasæt til at beregne det forskellige antal.
Selvom dette er en let løsning, er der nogle ulemper ved denne metode (dækket senere i denne vejledning).
Lad mig først vise dig, hvordan du tilføjer en hjælperkolonne og får et tydeligt antal.
Antag, at jeg har datasættet som vist herunder:
Tilføj følgende formel i kolonne F, og anvend den for alle de celler, der har data i de tilstødende kolonner.
= HVIS (COUNTIFS ($ C $ 2: C2, C2, $ B $ 2: B2, B2)> 1,0,1)
Ovenstående formel bruger funktionen TÆLLEFÆRD til at tælle det antal gange, et navn vises i den givne region. Bemærk også, at kriterierne er $ C $ 2: C2 og $ B $ 2: B2. Det betyder, at det bliver ved med at ekspandere, når du går ned i kolonnen.
I celle E2 er kriterierne for eksempel $ C $ 2: C2 og $ B $ 2: B2, og i celle E3 udvides disse områder til $ C $ 2: C3 og $ B $ 2: B3.
Dette sikrer, at COUNTIFS -funktionen tæller den første forekomst af et navn som 1, den anden forekomst af navnet som 2 og så videre.
Da vi kun ønsker at få de forskellige navne, bruges IF -funktionen, der returnerer 1, når et navn vises for en region første gang og returnerer 0, når det vises igen. Dette sikrer, at kun forskellige navne tælles og ikke gentagelserne.
Nedenfor er, hvordan dit datasæt ville se ud, når du har tilføjet hjælperkolonnen.
Nu hvor vi har ændret kildedataene, kan vi bruge dette til at oprette en pivottabel og bruge hjælpekolonnen til at få det tydelige antal salgsrepræsentanter i hver region.
Nedenfor er trinene for at gøre dette:
- Vælg en celle i datasættet.
- Klik på fanen Indsæt.
- Klik på pivottabel (eller brug tastaturgenvejen - ALT + N + V)
- I dialogboksen Opret pivottabel skal du sørge for, at tabellen/området er korrekt (og indeholder hjælperkolonnen) og 'Nyt regneark' i markeret.
- Klik på OK.
Ovenstående trin ville indsætte et nyt ark, der har pivottabellen.
Træk feltet 'Region' i området Rækker og 'D -tælling' i området Værdier.
Du får en pivottabel som vist herunder:
Nu kan du ændre kolonneoverskriften fra 'Sum of D count' til 'Sales Rep'.
Ulemper ved at bruge en hjælperkolonne:
Selvom denne metode er ret ligetil, skal jeg fremhæve et par ulemper ved at ændre kildedataene i en pivottabel:
- Datakilden med hjælperkolonnen er ikke så dynamisk som en pivottabel. Selvom du kan skære og skære dataene, som du vil med en pivottabel, mister du en del af denne evne, når du bruger en hjælperkolonne. Lad os sige, at du tilføjer en hjælperkolonne for at få optællingen af en særskilt salgsrepræsentant i hver region. Hvad nu, hvis du også ønsker at få det tydelige antal salgsrepræsentanter, der sælger printere. Du bliver nødt til at gå tilbage til kildedataene og ændre hjælpekolonneformlen (eller tilføje en ny hjælperkolonne).
- Da du tilføjer flere data til pivottabellens kilde (som også tilføjes til Pivot -cachen), kan dette føre til en større Excel -fil.
- Da vi bruger en Excel -formel, kan det gøre din Excel -projektmappe langsom, hvis du har tusindvis af rækker med data.
Føj data til datamodellen, og opsummer ved hjælp af distinkt tælling
Pivottabel tilføjede ny funktionalitet i Excel 2013, der giver dig mulighed for at få det tydelige antal, mens du opsummerer datasættet.
Hvis du bruger en tidligere version, kan du ikke bruge denne metode (som du burde prøve at tilføje hjælperkolonnen som vist i metoden over denne).
Antag, at du har et datasæt som vist nedenfor, og du vil få optællingen af den unikke salgsrepræsentant i hver region.
Nedenfor er trinene til at få en tydelig tælleværdi i pivottabellen:
- Vælg en celle i datasættet.
- Klik på fanen Indsæt.
- Klik på pivottabel (eller brug tastaturgenvejen - ALT + N + V)
- I dialogboksen Opret pivottabel skal du kontrollere, at tabellen/området er korrekt, og at nyt regneark er valgt.
- Marker afkrydsningsfeltet - "Føj disse data til datamodellen"
- Klik på OK.
Ovenstående trin ville indsætte et nyt ark, der har det nye pivottabel.
Træk regionen i området Rækker og Salgsrepræsentant i området Værdier. Du får en pivottabel som vist herunder:
Ovenstående pivottabel giver det samlede antal salgsrepræsentanter i hver region (og ikke det forskellige antal).
Følg nedenstående trin for at få det tydelige antal i pivottabellen:
- Højreklik på en vilkårlig celle i kolonnen "Antal salgsrepræsentanter".
- Klik på Værdifeltindstillinger
- I dialogboksen Værdifeltindstillinger skal du vælge 'Distinct Count' som beregningstype (du skal muligvis rulle ned på listen for at finde den).
- Klik på OK.
Du vil bemærke, at kolonnens navn ændres fra 'Count of Sales Rep' til 'Distinct Count of Sales Rep'. Du kan ændre det til hvad du vil.
Nogle ting, du ved, når du føjer dine data til datamodellen:
- Hvis du gemmer dine data i datamodellen og derefter åbner i en ældre version af Excel, viser det dig en advarsel - 'Nogle pivottabelfunktioner gemmes ikke'. Du kan muligvis ikke se det forskellige antal (og datamodellen), når den åbnes i en ældre version, der ikke understøtter den.
- Når du tilføjer dine data til en datamodel og laver en pivottabel, viser det ikke mulighederne for at tilføje beregnede felter og beregnede kolonner.
Klik her for at downloade eksempelfilen
Hvad hvis du vil tælle unikke værdier (og ikke forskellige værdier)?
Hvis du vil tælle unikke værdier, har du ikke nogen indbygget funktionalitet i pivottabellen og skal kun stole på hjælperkolonner.
Husk - Unikke værdier og forskellige værdier er ikke det samme. Klik her for at kende forskellen.Et eksempel kan være, når du har nedenstående datasæt, og du vil finde ud af, hvor mange sælgere, der er unikke for hver region. Det betyder, at de kun opererer i en bestemt region og ikke de andre.
I sådanne tilfælde skal du oprette en af mere end en hjælperkolonne.
I dette tilfælde gør nedenstående formel tricket:
= HVIS (HVIS (COUNTIFS ($ C $ 2: $ C $ 1001, C2, $ B $ 2: $ B $ 1001, B2)/COUNTIF ($ C $ 2: $ C $ 1001, C2) 1,0,1), 0)
Ovenstående formel kontrollerer, om et sælgernavn kun forekommer i én region eller i mere end én region. Det gør det ved at tælle antallet af forekomster af et navn i en region og dividere det med det samlede antal forekomster af navnet. Hvis værdien er mindre end 1, angiver det, at navnet forekommer i to eller flere end to områder.
Hvis navnet forekommer i mere end én region, returnerer det en 0 ellers returnerer det en en.
Formlen kontrollerer også, om navnet gentages i den samme region eller ej. Hvis navnet gentages, returnerer kun den første forekomst af navnet værdien 1, og alle andre forekomster returnerer 0.
Dette kan virke lidt komplekst, men det afhænger igen af, hvad du forsøger at opnå.
Så hvis du vil tælle unikke værdier i en pivottabel, skal du bruge hjælperkolonner, og hvis du vil tælle forskellige værdier, kan du bruge den indbyggede funktionalitet (i Excel 2013 og nyere) eller kan bruge en hjælperkolonne.
Klik her for at downloade eksempelfilen
Du kan også lide følgende pivottabeller:
- Sådan filtreres data i en pivottabel i Excel
- Sådan grupperes datoer i pivottabeller i Excel
- Sådan grupperes tal i pivottabel i Excel
- Sådan anvendes betinget formatering i en pivottabel i Excel
- Udsnit i Excel -pivottabel
- Sådan opdateres pivottabel i Excel
- Slet en pivottabel i Excel
