✅ Förbereda källdata för pivottabell

Innehållsförteckning

Vad är en bra design för källdata för pivottabellen?
Vanliga fallgropar att undvika när du förbereder källdata
Exempel på dåliga källdatadesigner
[CASE STUDY] Konvertera en dåligt formaterad data till pivottabell -redo källdata

Att ha data i rätt format är ett avgörande steg för att skapa en robust och felfri pivottabell. Om du inte gör det på rätt sätt kan du få många problem med ditt svängbord.

Vad är en bra design för källdata för pivottabellen?

Låt oss titta på ett exempel på bra källdata för ett pivottabell.

Här är vad som gör det till en bra källdatadesign:

Den första raden innehåller rubriker som beskriver data i kolumnerna.
Varje kolumn representerar en unik datakategori. Till exempel har kolumn C endast produktdata och endast kolumn D och månadsdata.
Varje rad är en post som representerar en instans av transaktionen eller försäljningen.
Datahuvudena är unika och upprepas inte någonstans i datamängden. Om du till exempel har försäljningsnummer i fyra kvartal på ett år ska du INTE namnge alla dessa som försäljning. Ge istället dessa kolumnrubriker unika namn som försäljning Q1, försäljning Q2 och så vidare …
- Om du inte har unika titlar kan du fortfarande fortsätta och skapa en pivottabell och Excel skulle automatiskt göra dessa unika genom att lägga till ett suffix (till exempel Sales, Sales2, Sales3). Det skulle dock vara ett fruktansvärt sätt att förbereda och använda ett pivottabell.

Vanliga fallgropar att undvika när du förbereder källdata

Det ska inte finnas några tomma kolumner i källdatan. Den här är lätt att upptäcka. Om du har en tom kolumn i källdatan skulle du inte kunna skapa en pivottabell. Det visar ett fel som visas nedan.
Det ska inte finnas tomma celler/rader i källdatan. Även om du kan skapa ett pivottabell trots att du har tomma celler eller rader, finns det många biverkningar som kan bita dig senare på dagen.
- Låt oss till exempel säga att du har en tom cell i försäljningskolumnen. Om du skapar en pivottabell med hjälp av denna data och lägger in försäljningsfältet i kolumnområdet, visar det dig RÄTTAN och inte SUMMEN. Det beror på att Excel tolkar hela kolumnen med textdata (bara på grund av en enda tom cell).
Tillämpa relevant format på celler i källdata. Till exempel, om du har datum (som lagras som serienummer i backend i Excel), tillämpa ett av de acceptabla datumformaten. Detta skulle hjälpa dig att skapa pivottabellen och använda datum som ett av kriterierna för att sammanfatta, gruppera och sortera data.
- Om du har ett par sekunder, prova det här. Formatera datumen i din pivottabell som siffror och skapa sedan en pivottabell med dessa data. Nu i pivottabellen väljer du datumfältet och ser vad som händer. Den kommer automatiskt att placera den i värdeområdet. Det beror på att din pivottabell inte vet att det är datum. Det tolkar dessa som siffror.
Inkludera inga kolumntotaler, radtal, genomsnitt, etc., som en del av källdatan. När du väl har pivottabellen kan du enkelt få dessa senare.
Skapa alltid en Excel -tabell och använd den sedan som källa för ett pivottabell. Detta är mer en bra praxis och inte en fallgrop. Din pivottabell skulle fungera bra med en källdata som inte heller är en Excel -tabell. Fördelen med Excel -tabellen är att den kan justera expanderande data. Om du lägger till fler rader i datamängden behöver du inte justera källdata om och om igen. Du kan helt enkelt uppdatera pivottabellen och den kommer automatiskt att ta hänsyn till de nya raderna som läggs till källdata.

Exempel på dåliga källdatadesigner

Låt oss titta på några dåliga exempel på källdatadesigner.

Dålig källdatadesign - Exempel 1

Detta är ett vanligt sätt att underhålla data eftersom det är lätt att följa och förstå. Det finns två problem med detta dataarrangemang:

Du får inte hela bilden. Till exempel kan du se försäljningen för Mid West i kvartal 1 är 2924300. Men är det en enda försäljning, eller ett antal försäljningar. Om du har varje post tillgänglig i en separat rad kan du göra en bättre analys.
Om du fortsätter och skapar ett pivottabell med hjälp av detta (vilket du kan) får du olika fält för olika kvartal. Något som visas nedan:

Dålig källdatadesign - exempel 2

Denna datarepresentation kan tas väl emot av ledningen och publiken av PowerPoint -presentationer, men den är inte lämplig för att skapa en pivottabell.

Återigen, det här är den typ av sammanfattning som du enkelt kan skapa med ett pivottabell. Så även om du så småningom vill ha en sådan titt på dina data, behåll källdatan i ett Pivot -klart format och skapa den här vyn med hjälp av pivottabellen.

Dålig källdatadesign - exempel 3

Detta är återigen en utmatning som enkelt kan erhållas med ett pivottabell. Men den kan inte användas för att skapa ett pivottabell.

Det finns tomma celler i datamängden och kvartalen sprids som kolumnrubriker.

Dessutom anges regionen högst upp, medan den borde vara en del av varje post.

[CASE STUDY] Konvertera en dåligt formaterad data till pivottabell -redo källdata

Ibland kan du få en dataset som är olämplig att användas som källdata för pivottabellen. I ett sådant fall har du kanske inget annat val än att konvertera data till ett Pivot -vänligt dataformat.

Här är ett exempel på dålig datadesign:

Nu kan du använda Excel Functions eller Pivot Query för att konvertera dessa data till ett format som kan användas som källdata för pivottabellen.

Låt oss se hur båda dessa metoder fungerar.

Metod 1: Använda Excel -formler

Låt oss se hur du använder Excel -funktioner för att konvertera dessa data till ett pivottabell -klart format.

Skapa en unik kolumnrubrik för alla kategorier i den ursprungliga datamängden. I det här exemplet är det Region, Kvartal och Försäljning.
I cellen under Regionrubriken använder du följande formel: = INDEX ($ A $ 2: $ A $ 5, ROUNDUP (ROWS ($ A $ 2: A2)/COUNTA ($ B $ 1: $ E $ 1), 0))
- Dra formeln nedåt så upprepas alla regioner.
I cellen under kvartalsrubriken använder du följande formel: = INDEX ($ B $ 1: $ E $ 1, ROUNDUP (MOD (RADER ($ A $ 2: A2), COUNTA ($ B $ 1: $ E $ 1) +0,1) , 0))
- Dra formeln nedåt så upprepas alla kvartal.
I rubriken nedan Försäljning, använd följande formel: = INDEX ($ B $ 2: $ E $ 5, MATCH (G2, $ A $ 2: $ A $ 5,0), MATCH (H2, $ B $ 1: $ E $ 1,0 )))
- Dra ner den för att få alla värden. Denna formel använder region- och kvartaldata som uppslagsvärden och returnerar försäljningsvärdet från den ursprungliga datamängden.

Nu kan du använda dessa resulterande data som källdata för pivottabellen.

Klicka här för att ladda ner exempelfilen.

Metod 2: Använda Power Query

Power Query har en funktion som enkelt kan konvertera denna typ av data till Pivot -klart dataformat.

Om du använder Excel 2016 är Power Query -funktioner tillgängliga på fliken Data i gruppen Get & Transform. Om du använder Excel 2013 eller tidigare versioner kan du använda det som ett tillägg.

Här är en utmärkt guide om installation av Power Query av Jon från Excel Campus.

Återigen, med tanke på att du har data formaterad enligt nedan:

Här är stegen för att konvertera källdata till Pivot Table -klart format:

Konvertera data till en Excel -tabell. Välj datauppsättningen och gå till Infoga -> Tabeller -> Tabell.
Kontrollera att rätt intervall är valt i dialogrutan Infoga tabell och klicka på OK. Detta kommer att konvertera tabelldata till en Excel -tabell.
I Excel 2016 går du till Data -> Get & Transform -> From Table.
- Om du använder tillägget Power Query i en tidigare version går du till Power Query -> Externa data -> Från tabell.
Markera de kolumner som du vill ta bort i sökredigeraren. I det här fallet är dessa de fyra kvartalen. För att markera alla kolumner, håll ned Shift -tangenten och välj sedan den första kolumnen och sedan den sista kolumnen.
I frågeredigeraren går du till Transform -> Any Column -> Unpivot Columns. Detta kommer att konvertera kolumnens data till Pivot Table -vänligt format.
Power Query ger kolumner generiska namn. Ändra dessa namn till de du vill ha. I det här fallet ändrar du attribut till kvartal och värde till försäljning.
I frågeredigeraren går du till Arkiv -> Stäng och läs in. Detta stänger dialogrutan Power Query Editor och skapar ett separat kalkylblad med data med opartade kolumner.