✅ Klargøring af kildedata til pivottabel

Indholdsfortegnelse

Hvad er et godt design til kildedataene til pivottabellen?
Almindelige faldgruber, der skal undgås, når kildedataene udarbejdes
Eksempler på design af dårlige kildedata
[CASE STUDY] Konvertering af dårligt formaterede data til pivottabeller, der er klar til kildedata

At have dataene i det rigtige format er et afgørende trin i at skabe en robust og fejlfri pivottabel. Hvis du ikke gør det på den rigtige måde, kan du ende med at have mange problemer med dit pivottabel.

Hvad er et godt design til kildedataene til pivottabellen?

Lad os se på et eksempel på gode kildedata til en pivottabel.

Her er, hvad der gør det til et godt kildedata -design:

Den første række indeholder overskrifter, der beskriver dataene i kolonnerne.
Hver kolonne repræsenterer en unik datakategori. F.eks. Har kolonne C kun produktdata og kolonne D og månedsdata.
Hver række er en post, der repræsenterer et eksempel på transaktionen eller salget.
Dataoverskrifterne er unikke og gentages ikke nogen steder i datasættet. Hvis du f.eks. Har salgstal i fire kvartaler på et år, skal du IKKE navngive alle disse som salg. Giv i stedet disse kolonneoverskrifter unikke navne som salg Q1, salg Q2 og så videre …
- Hvis du ikke har unikke titler, kan du stadig gå videre og oprette en pivottabel, og Excel ville automatisk gøre disse unikke ved at tilføje et suffiks (f.eks. Salg, salg2, salg3). Det ville imidlertid være en frygtelig måde at forberede og bruge et pivottabel på.

Almindelige faldgruber, der skal undgås, når kildedataene udarbejdes

Der bør ikke være tomme kolonner i kildedataene. Denne er let at få øje på. Hvis du har en tom kolonne i kildedataene, ville du ikke kunne oprette en pivottabel. Det viser en fejl som vist nedenfor.
Der bør ikke være tomme celler/rækker i kildedataene. Selvom du med succes kan oprette et pivottabel på trods af at du har tomme celler eller rækker, er der mange bivirkninger, der kan bide dig senere på dagen.
- Lad os f.eks. Sige, at du har en tom celle i salgskolonnen. Hvis du opretter en pivottabel ved hjælp af disse data og sætter salgsfeltet i kolonneområdet, viser det dig TÆLLINGEN og ikke SUMMEN. Det er fordi Excel fortolker hele kolonnen som tekstdata (bare på grund af en enkelt tom celle).
Anvend relevant format på celler i kildedataene. Hvis du f.eks. Har datoer (som er gemt som serienumre i backend i Excel), skal du anvende et af de acceptable datoformater. Dette ville hjælpe dig med at oprette pivottabellen og bruge dato som et af kriterierne til at opsummere, gruppere og sortere dataene.
- Hvis du har et par sekunder, kan du prøve dette. Formater datoerne i din pivottabel som tal, og opret derefter en pivottabel ved hjælp af disse data. Nu i pivottabellen skal du vælge datofeltet og se, hvad der sker. Det vil automatisk sætte det i værdiområdet. Det er fordi din pivottabel ikke ved, at det er datoer. Det fortolker disse som tal.
Medtag ikke kolonnetotaler, rækker i alt, gennemsnit osv. Som en del af kildedataene. Når du har pivottabellen, kan du nemt få disse senere.
Opret altid en Excel -tabel, og brug den derefter som kilde til en pivottabel. Dette er mere en god praksis og ikke en faldgrube. Din pivottabel ville fungere fint med kildedata, der ikke også er en Excel -tabel. Fordelen med Excel Table er, at den kan justere de ekspanderende data. Hvis du tilføjer flere rækker til datasættet, behøver du ikke at justere kildedataene igen og igen. Du kan simpelthen opdatere pivottabellen, og den vil automatisk tage højde for de nye rækker, der er føjet til kildedataene.

Eksempler på design af dårlige kildedata

Lad os se på nogle dårlige eksempler på design af kildedata.

Design af dårlige kildedata - eksempel 1

Dette er en almindelig måde at vedligeholde data, da det er let at følge og forstå. Der er to problemer med dette dataarrangement:

Du får ikke det fulde billede. For eksempel kan du se salget for Mid West i kvartal 1 er 2924300. Men er det et enkelt salg, eller et antal salg. Hvis du har hver post tilgængelig i en separat række, kan du foretage en bedre analyse.
Hvis du går videre og opretter en pivottabel ved hjælp af denne (som du kan), får du forskellige felter til forskellige kvartaler. Noget som vist herunder:

Design af dårlige kildedata - eksempel 2

Denne datarepræsentation kan blive modtaget godt af ledelsen og publikum af PowerPoint -præsentationer, men det er ikke egnet til at oprette et pivottabel.

Igen er dette den slags oversigt, som du nemt kan oprette ved hjælp af en pivottabel. Så selvom du til sidst vil have et sådant kig efter dine data, skal du vedligeholde kildedataene i et Pivot -klart format og oprette denne visning ved hjælp af pivottabellen.

Design af dårlige kildedata - eksempel 3

Dette er igen en output, der let kan opnås ved hjælp af en pivottabel. Men det kan ikke bruges til at oprette et pivottabel.

Der er tomme celler i datasættet, og kvartalerne spredes som kolonneoverskrifter.

Regionen er også angivet øverst, mens den bør være en del af hver post.

[CASE STUDY] Konvertering af dårligt formaterede data til pivottabeller, der er klar til kildedata

Nogle gange kan du få et datasæt, der er uegnet til at blive brugt som kildedata for pivottabel. I et sådant tilfælde har du muligvis ikke andet valg end at konvertere dataene til et Pivot -venligt dataformat.

Her er et eksempel på dårligt datadesign:

Nu kan du bruge Excel -funktioner eller Pivot -forespørgsel til at konvertere disse data til et format, der kan bruges som kildedata for pivottabel.

Lad os se, hvordan begge disse metoder fungerer.

Metode 1: Brug af Excel -formler

Lad os se, hvordan du bruger Excel -funktioner til at konvertere disse data til et Pivot Table -klart format.

Opret et unikt kolonneoverskrift for alle kategorierne i det originale datasæt. I dette eksempel ville det være Region, Kvartal og Salg.
I cellen under regionoverskriften skal du bruge følgende formel: = INDEX ($ A $ 2: $ A $ 5, ROUNDUP (ROWS ($ A $ 2: A2)/COUNTA ($ B $ 1: $ E $ 1), 0))
- Træk formlen ned, og den vil gentage alle regionerne.
I cellen under kvartaloverskriften skal du bruge følgende formel: = INDEX ($ B $ 1: $ E $ 1, ROUNDUP (MOD (ROWS ($ A $ 2: A2), COUNTA ($ B $ 1: $ E $ 1) +0,1) , 0))
- Træk formlen ned, og den vil gentage alle kvartaler.
I overskriften under salg skal du bruge følgende formel: = INDEX ($ B $ 2: $ E $ 5, MATCH (G2, $ A $ 2: $ A $ 5,0), MATCH (H2, $ B $ 1: $ E $ 1,0 ))
- Træk det ned for at få alle værdierne. Denne formel bruger data fra regionen og kvartalet som opslagsværdier og returnerer salgsværdien fra det originale datasæt.

Nu kan du bruge disse resulterende data som kildedata for pivottabel.

Klik her for at downloade eksempelfilen.

Metode 2: Brug af Power Query

Power Query har en funktion, der let kan konvertere denne slags data til Pivot -klar dataformat.

Hvis du bruger Excel 2016, ville Power Query -funktioner være tilgængelige på fanen Data i gruppen Get & Transform. Hvis du bruger Excel 2013 eller tidligere versioner, kan du bruge det som et tilføjelsesprogram.

Her er en glimrende guide til Installation af Power Query af Jon fra Excel Campus.

Igen, i betragtning af at du har data formateret som vist nedenfor:

Her er trinene til at konvertere kildedata til Pivot Table -klar format:

Konverter dataene til en Excel -tabel. Vælg datasættet og gå til Indsæt -> Tabeller -> Tabel.
Kontroller, at det korrekte område er valgt i dialogboksen Indsæt tabel, og klik på OK. Dette vil konvertere de tabelformede data til en Excel -tabel.
I Excel 2016 skal du gå til Data -> Hent og transformer -> Fra tabel.
- Hvis du bruger Power Query -tilføjelsen i en tidligere version, skal du gå til Power Query -> Eksterne data -> Fra tabel.
I forespørgselseditoren skal du vælge de kolonner, du vil fjerne. I dette tilfælde er det dem i de fire kvartaler. Hvis du vil markere alle kolonnerne, skal du holde Shift -tasten nede og derefter vælge den første kolonne og derefter den sidste kolonne.
I forespørgselseditoren skal du gå til Transform -> Enhver kolonne -> Fjern kolonner. Dette vil konvertere kolonnens data til Pivot Table -venligt format.
Power Query giver generiske navne til kolonnerne. Skift disse navne til dem, du ønsker. I dette tilfælde skal du ændre attribut til kvartal og værdi til salg.
I forespørgselseditoren skal du gå til Filer -> Luk og indlæse. Dette lukker dialogboksen Power Query Editor og opretter et separat regneark, der vil have dataene med unpivoted kolonner.