Räkna distinkta värden i Excel-pivottabell (enkel steg-för-steg-guide)

Excel -pivottabeller är fantastiska (jag vet att jag nämner detta varje gång jag skriver om pivottabeller, men det är sant).

Med en grundläggande förståelse och lite dra och släpp kan du få en massa jobb gjort på några sekunder.

Även om mycket kan göras med några klick i pivottabeller, finns det några saker som kräver några extra steg eller lite arbete.

Och en sådan sak är att räkna distinkta värden i ett pivottabell.

I den här självstudien visar jag dig hur du räknar distinkta värden samt unika värden i en Excel -pivottabell.

Men innan jag går in på hur man räknar distinkta värden är det viktigt att förstå skillnaden mellan "distinkt antal" och "unika räkningar"

Distinct Count Vs Unique Count

Även om dessa kan verka som samma sak, det är inte.

Nedan finns ett exempel där det finns en dataset med namn och jag har listat unika och distinkta namn separat.

Unika värden/namn är de som bara inträffar en gång. Det betyder att alla namn som upprepas och har dubbletter inte är unika. Unika namn listas i kolumn C i ovanstående datamängd

Särskilda värden/namn är de som förekommer minst en gång i datamängden. Så om ett namn visas tre gånger, räknas det fortfarande som ett distinkt namn. Detta kan uppnås genom att ta bort dubblettvärden/namn och behålla alla de olika. Distinkta namn listas i kolumn B i ovanstående datamängd.

Baserat på vad jag har sett, de flesta gånger när människor säger att de vill få det unika antalet i ett pivottabell, betyder de faktiskt distinkt antal, vilket är vad jag täcker i denna handledning.

Räkna distinkta värden i Excel -pivottabell

Antag att du har försäljningsdata enligt nedan:

Klicka här för att ladda ner exempelfilen och följa med

Med ovanstående dataset, låt oss säga att du vill hitta svaret på följande frågor:

1. Hur många säljare finns det i varje region (vilket inte är annat än det distinkta antalet säljare i varje region)?
2. Hur många säljare sålde skrivaren under 2020-2022?

Medan pivottabeller direkt kan sammanfatta data med några klick, måste du ta några ytterligare steg för att få antalet olika värden.

Om du använder Excel 2013 eller versioner efter det, det finns en inbyggd funktionalitet i pivottabellen som snabbt ger dig distinkt antal. Och om du använder Excel 2010 eller tidigare versioner, måste du ändra källdata genom att lägga till en hjälparkolumn.

Följande två metoder behandlas i denna handledning:

• Lägga till en hjälparkolumn i den ursprungliga datamängden för att räkna unika värden (fungerar i alla versioner).
• Lägga till data till en datamodell och använda alternativet Distinct Count (tillgängligt i Excel 2013 och versioner efter det).

Det finns en tredje metod som Roger visar i denna artikel (som han kallar Pivot the Pivot Table -metoden).

Låt oss börja!

Lägga till en hjälpkolumn i datauppsättningen

Obs! Om du använder Excel 2013 och högre versioner, hoppa över den här metoden och gå till nästa (eftersom den använder en inbyggd pivottabellfunktion - Distinkt räkna).

Detta är ett enkelt sätt att räkna distinkta värden i pivottabellen eftersom du bara behöver lägga till en hjälparkolumn i källdatan. När du har lagt till en hjälparkolumn kan du sedan använda den här nya datamängden för att beräkna det distinkta antalet.

Även om detta är en enkel lösning, finns det några nackdelar med den här metoden (täcks senare i denna handledning).

Låt mig först visa dig hur du lägger till en hjälparkolumn och får ett distinkt antal.

Antag att jag har datauppsättningen enligt nedan:

Lägg till följande formel i kolumn F och tillämpa den för alla celler som har data i de intilliggande kolumnerna.

= OM (RÄTTA ($ C $ 2: C2, C2, $ B $ 2: B2, B2)> 1,0,1)

Ovanstående formel använder COUNTIFS -funktionen för att räkna antalet gånger ett namn visas i den givna regionen. Observera också att kriterierna är $ C $ 2: C2 och $ B $ 2: B2. Det betyder att det fortsätter att expandera när du går ner i kolumnen.

Till exempel i cell E2 är kriterierna $ C $ 2: C2 och $ B $ 2: B2 och i cell E3 expanderar dessa intervall till $ C $ 2: C3 och $ B $ 2: B3.

Detta säkerställer att COUNTIFS -funktionen räknar den första förekomsten av ett namn som 1, den andra förekomsten av namnet som 2 och så vidare.

Eftersom vi bara vill få de distinkta namnen används IF -funktionen som returnerar 1 när ett namn visas för en region första gången och returnerar 0 när det visas igen. Detta säkerställer att endast distinkta namn räknas och inte upprepningar.

Nedan ser du hur din datamängd skulle se ut när du har lagt till hjälparkolumnen.

Nu när vi har modifierat källdata kan vi använda detta för att skapa en pivottabell och använda hjälparkolumnen för att få det distinkta antalet säljare i varje region.

Nedan följer stegen för att göra detta:

1. Välj vilken cell som helst i datauppsättningen.
2. Klicka på fliken Infoga.
3. Klicka på Pivot Table (eller använd kortkommandot - ALT + N + V)
4. I dialogrutan Skapa pivottabell, se till att tabellen/intervallet är korrekt (och innehåller hjälpkolumnen) och ”Nytt arbetsblad” i valt.
5. Klicka på OK.

Stegen ovan skulle infoga ett nytt ark som har pivottabellen.

Dra "Region" -fältet i raderna och "D Count" -fältet i området Värden.

Du får ett pivottabell enligt nedan:

Nu kan du ändra kolumnrubriken från "Summa av D -antal" till "Säljare".

Nackdelar med att använda en hjälpkolumn:

Även om denna metod är ganska rak, måste jag markera några nackdelar med att ändra källdata i en pivottabell:

• Datakällan med hjälpkolumnen är inte lika dynamisk som en pivottabell. Medan du kan skära och tärna data hur du vill med en pivottabell, förlorar du en del av den förmågan när du använder en hjälpkolumn. Låt oss säga att du lägger till en hjälparkolumn för att få en särskild säljare i varje region. Tänk om du också vill få en tydlig mängd säljare som säljer skrivare. Du måste gå tillbaka till källdata och ändra hjälpkolumnformeln (eller lägga till en ny hjälpkolumn).
• Eftersom du lägger till mer data till pivottabellkällan (som också läggs till i pivotcachen) kan detta leda till en större storlek på Excel -filen.
• Eftersom vi använder en Excel -formel kan det göra din Excel -arbetsbok långsam om du har tusentals rader med data.

Lägg till data i datamodellen och summera med distinkt antal

Pivottabell lade till ny funktionalitet i Excel 2013 som låter dig få det distinkta antalet samtidigt som du sammanfattar datauppsättningen.

Om du använder en tidigare version kan du inte använda den här metoden (som att försöka lägga till hjälpkolumnen som visas i metoden ovanför den här).

Anta att du har en dataset som visas nedan och du vill få antalet unika säljare i varje region.

Nedan följer stegen för att få ett distinkt räknevärde i pivottabellen:

1. Välj vilken cell som helst i datauppsättningen.
2. Klicka på fliken Infoga.
3. Klicka på Pivot Table (eller använd kortkommandot - ALT + N + V)
4. I dialogrutan Skapa pivottabell kontrollerar du att tabellen/intervallet är korrekt och att nytt kalkylblad är valt.
5. Markera rutan som säger - "Lägg till dessa data i datamodellen"
6. Klicka på OK.

Stegen ovan skulle infoga ett nytt ark som har det nya pivottabellen.

Dra regionen i området Rader och säljare i området Värden. Du får ett vridbord som visas nedan:

Ovanstående pivottabell ger det totala antalet säljare i varje region (och inte det distinkta antalet).

För att få det distinkta antalet i pivottabellen, följ stegen nedan:

1. Högerklicka på en cell i kolumnen "Antal säljare".
2. Klicka på värdefältinställningar
3. I dialogrutan Värdefältinställningar väljer du "Distinct Count" som beräkningstyp (du kan behöva rulla nedåt i listan för att hitta den).
4. Klicka på OK.

Du kommer att märka att kolumnens namn ändras från "Count of Sales Rep" till "Distinct Count of Sales Rep". Du kan ändra det till vad du vill.

Några saker du vet när du lägger till dina data i datamodellen:

• Om du sparar dina data i datamodellen och sedan öppnar i en äldre version av Excel, kommer det att visa dig en varning - "Vissa pivottabellfunktioner sparas inte". Du kanske inte ser den distinkta räkningen (och datamodellen) när den öppnas i en äldre version som inte stöder den.
• När du lägger till dina data i en datamodell och gör en pivottabell visas inte alternativen för att lägga till beräknade fält och beräknade kolumner.

Klicka här för att ladda ner exempelfilen

Vad händer om du vill räkna unika värden (och inte distinkta värden)?

Om du vill räkna unika värden har du ingen inbyggd funktionalitet i pivottabellen och behöver bara lita på hjälparkolumner.

Kom ihåg - Unika värden och distinkta värden är inte desamma. Klicka här för att veta skillnaden.

Ett exempel kan vara när du har nedanstående datamängd och du vill ta reda på hur många säljare som är unika för varje region. Det betyder att de bara verkar i en specifik region och inte de andra.

I sådana fall måste du skapa en av mer än en hjälparkolumner.

I det här fallet gör nedanstående formel tricket:

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

Ovanstående formel kontrollerar om ett säljare namn bara förekommer i en region eller i mer än en region. Det gör det genom att räkna antalet förekomster av ett namn i en region och dividera det med det totala antalet förekomster av namnet. Om värdet är mindre än 1 indikerar det att namnet förekommer i två eller fler än två regioner.

Om namnet förekommer i mer än en region returnerar det en 0 annars returnerar det en en.

Formeln kontrollerar också om namnet upprepas i samma region eller inte. Om namnet upprepas returnerar endast den första instansen av namnet värdet 1 och alla andra instanser returnerar 0.

Det kan tyckas lite komplext, men det beror igen på vad du försöker uppnå.

Så om du vill räkna unika värden i en pivottabell, använd hjälparkolumner och om du vill räkna distinkta värden kan du använda den inbyggda funktionen (i Excel 2013 och senare) eller använda en hjälparkolumn.

Klicka här för att ladda ner exempelfilen

Du kanske också gillar följande pivottabellguider:

• Så här filtrerar du data i en pivottabell i Excel
• Hur man grupperar datum i pivottabeller i Excel
• Hur man grupperar nummer i pivottabellen i Excel
• Så här använder du villkorlig formatering i en pivottabell i Excel
• Skivare i Excel -pivottabell
• Hur man uppdaterar pivottabellen i Excel
• Ta bort en pivottabell i Excel