Å ha dataene i riktig format er et avgjørende trinn i å lage et robust og feilfritt pivottabell. Hvis du ikke gjør det på riktig måte, kan du ende opp med å ha mange problemer med svingbordet.
Hva er en god design for kildedataene for pivottabell?
La oss se på et eksempel på gode kildedata for et pivottabell.
Her er det som gjør det til et godt kildedesign:
- Den første raden inneholder overskrifter som beskriver dataene i kolonnene.
- Hver kolonne representerer en unik datakategori. For eksempel har kolonne C bare produktdata og kolonne D og månedsdata.
- Hver rad er en post som representerer en forekomst av transaksjonen eller salget.
- Dataoverskriftene er unike og gjentas ikke noe sted i datasettet. For eksempel, hvis du har salgstall i fire kvartaler på et år, bør du IKKE nevne alle disse som salg. Gi i stedet disse kolonneoverskriftene unike navn som salg Q1, salg Q2, og så videre …
- Hvis du ikke har unike titler, kan du fortsatt gå videre og lage et pivottabell, og Excel ville automatisk gjøre disse unike ved å legge til et suffiks (for eksempel Sales, Sales2, Sales3). Det ville imidlertid være en forferdelig måte å forberede og bruke et pivottabell på.
- Hvis du ikke har unike titler, kan du fortsatt gå videre og lage et pivottabell, og Excel ville automatisk gjøre disse unike ved å legge til et suffiks (for eksempel Sales, Sales2, Sales3). Det ville imidlertid være en forferdelig måte å forberede og bruke et pivottabell på.
Vanlige fallgruver å unngå når du utarbeider kildedata
- Det bør ikke være noen tomme kolonner i kildedataene. Denne er lett å få øye på. Hvis du har en tom kolonne i kildedataene, vil du ikke kunne opprette en pivottabell. Det vil vise en feil som vist nedenfor.
- Det bør ikke være tomme celler/rader i kildedataene. Selv om du kan opprette et pivottabell til tross for at du har tomme celler eller rader, er det mange bivirkninger som kan bite deg senere på dagen.
- La oss for eksempel si at du har en tom celle i salgskolonnen. Hvis du oppretter en pivottabell med disse dataene og legger salgsfeltet i kolonneområdet, viser det deg TELLINGEN og ikke SUMMEN. Det er fordi Excel tolker hele kolonnen som å ha tekstdata (bare på grunn av en enkelt tom celle).
- La oss for eksempel si at du har en tom celle i salgskolonnen. Hvis du oppretter en pivottabell med disse dataene og legger salgsfeltet i kolonneområdet, viser det deg TELLINGEN og ikke SUMMEN. Det er fordi Excel tolker hele kolonnen som å ha tekstdata (bare på grunn av en enkelt tom celle).
- Bruk relevant format på celler i kildedataene. Hvis du for eksempel har datoer (som er lagret som serienumre i backend i Excel), kan du bruke et av de akseptable datoformatene. Dette vil hjelpe deg med å lage pivottabellen og bruke dato som et av kriteriene for å oppsummere, gruppere og sortere dataene.
- Hvis du har et par sekunder, kan du prøve dette. Formater datoene i pivottabellen som tall, og lag deretter en pivottabell med disse dataene. Nå i pivottabellen, velg datofeltet og se hva som skjer. Den vil automatisk sette den i verdiområdet. Det er fordi pivottabellen din ikke vet at dette er datoer. Det tolker disse som tall.
- Hvis du har et par sekunder, kan du prøve dette. Formater datoene i pivottabellen som tall, og lag deretter en pivottabell med disse dataene. Nå i pivottabellen, velg datofeltet og se hva som skjer. Den vil automatisk sette den i verdiområdet. Det er fordi pivottabellen din ikke vet at dette er datoer. Det tolker disse som tall.
- Ikke ta med kolonnetotaler, radtotaler, gjennomsnitt, etc., som en del av kildedataene. Når du har pivottabellen, kan du enkelt få disse senere.
- Lag alltid en Excel -tabell og bruk den som kilde for et pivottabell. Dette er mer en god praksis og ikke en fallgruve. Pivottabellen din fungerer fint med kildedata som ikke også er en Excel -tabell. Fordelen med Excel -tabellen er at den kan justere de ekspanderende dataene. Hvis du legger til flere rader i datasettet, trenger du ikke å justere kildedataene igjen og igjen. Du kan bare oppdatere pivottabellen, og den vil automatisk ta hensyn til de nye radene som er lagt til kildedataene.
Eksempler på design av dårlige kildedata
La oss se på noen dårlige eksempler på design av kildedata.
Dårlig design av kildedata - eksempel 1
Dette er en vanlig måte å opprettholde data på, ettersom den er lett å følge og forstå. Det er to problemer med dette dataopplegget:
- Du får ikke hele bildet. For eksempel kan du se salget for Midtvest i kvartal 1 er 2924300. Men er det et enkelt salg, eller et antall salg. Hvis du har hver post tilgjengelig i en egen rad, kan du gjøre en bedre analyse.
- Hvis du fortsetter og oppretter et pivottabell ved hjelp av dette (som du kan), får du forskjellige felt for forskjellige kvartaler. Noe som vist nedenfor:
Dårlig design av kildedata - eksempel 2
Denne datarepresentasjonen kan bli godt mottatt av ledelsen og publikum av PowerPoint -presentasjoner, men den er ikke egnet for å lage et pivottabell.
Igjen, dette er den typen oppsummering du enkelt kan lage ved hjelp av et pivottabell. Så selv om du til slutt vil ha et slikt blikk for dataene dine, behold kildedataene i et Pivot -klart format og opprett denne visningen ved hjelp av pivottabellen.
Dårlig kildedatautforming - eksempel 3
Dette er igjen en utgang som enkelt kan oppnås ved hjelp av et pivottabell. Men den kan ikke brukes til å lage et pivottabell.
Det er tomme celler i datasettet, og kvartalene er spredt som kolonneoverskrifter.
Også regionen er spesifisert øverst, mens den bør være en del av hver post.
[CASE STUDY] Konvertering av dårlig formatert data til pivottabell -klare kildedata
Noen ganger kan du få et datasett som er uegnet til å brukes som kildedata for pivottabell. I et slikt tilfelle har du kanskje ikke noe annet valg enn å konvertere dataene til et Pivot -vennlig dataformat.
Her er et eksempel på dårlig datadesign:
Nå kan du bruke Excel Functions eller Pivot Query til å konvertere disse dataene til et format som kan brukes som kildedata for pivottabell.
La oss se hvordan begge disse metodene fungerer.
Metode 1: Bruke Excel -formler
La oss se hvordan du bruker Excel Functions til å konvertere disse dataene til et Pivot Table -klart format.
- Lag en unik kolonneoverskrift for alle kategoriene i det originale datasettet. I dette eksemplet vil det være region, kvartal og salg.
- I cellen under regionoverskriften bruker du følgende formel: = INDEX ($ A $ 2: $ A $ 5, ROUNDUP (ROWS ($ A $ 2: A2)/COUNTA ($ B $ 1: $ E $ 1), 0))
- Dra formelen nedover, og den vil gjenta alle regionene.
- Dra formelen nedover, og den vil gjenta alle regionene.
- I cellen under kvartaloverskriften bruker du følgende formel: = INDEX ($ B $ 1: $ E $ 1, ROUNDUP (MOD (ROWS ($ A $ 2: A2), COUNTA ($ B $ 1: $ E $ 1) +0.1) , 0))
- Dra formelen ned, så gjentas alle kvartalene.
- Dra formelen ned, så gjentas alle kvartalene.
- I overskriften under salg bruker du følgende formel: = INDEX ($ B $ 2: $ E $ 5, MATCH (G2, $ A $ 2: $ A $ 5,0), MATCH (H2, $ B $ 1: $ E $ 1,0 ))
- Dra den ned for å få alle verdiene. Denne formelen bruker dataene Region og Kvartal som oppslagsverdier og returnerer salgsverdien fra det opprinnelige datasettet.
- Dra den ned for å få alle verdiene. Denne formelen bruker dataene Region og Kvartal som oppslagsverdier og returnerer salgsverdien fra det opprinnelige datasettet.
Nå kan du bruke disse resulterende dataene som kildedata for pivottabell.
Klikk her for å laste ned eksempelfilen.
Metode 2: Bruke Power Query
Power Query har en funksjon som enkelt kan konvertere denne typen data til Pivot -klart dataformat.
Hvis du bruker Excel 2016, vil Power Query -funksjoner være tilgjengelige i Data -fanen i Get & Transform -gruppen. Hvis du bruker Excel 2013 eller tidligere versjoner, kan du bruke det som et tillegg.
Her er en utmerket guide til installering av Power Query av Jon fra Excel Campus.
Igjen, med tanke på at du har dataene formatert som vist nedenfor:
Her er trinnene for å konvertere kildedata til Pivot Table -klart format:
- Konverter dataene til en Excel -tabell. Velg datasettet og gå til Sett inn -> Tabeller -> Tabell.
- Kontroller at riktig område er valgt i dialogboksen Sett inn tabell, og klikk OK. Dette vil konvertere tabelldataene til en Excel -tabell.
- I Excel 2016, gå til Data -> Get & Transform -> From Table.
- Hvis du bruker tilleggsprogrammet Power Query i en tidligere versjon, går du til Power Query -> Eksterne data -> Fra tabell.
- Hvis du bruker tilleggsprogrammet Power Query i en tidligere versjon, går du til Power Query -> Eksterne data -> Fra tabell.
- I spørringsredigereren velger du kolonnene du vil fjerne pivotene. I dette tilfellet er dette de fire kvartalene. For å velge alle kolonnene, hold inne Shift -tasten og velg deretter den første kolonnen og deretter den siste kolonnen.
- I Query Editor går du til Transform -> Enhver kolonne -> Unpivot Columns. Dette vil konvertere kolonnens data til Pivot Table -vennlig format.
- Power Query gir generiske navn til kolonnene. Endre disse navnene til de du vil ha. I dette tilfellet endrer du attributt til kvartal og verdi til salg.
- Gå til Fil -> Lukk og Last i Spørredigerer. Dette lukker dialogboksen Power Query Editor og oppretter et eget regneark som inneholder dataene med ikke -dreide kolonner.
Nå som du vet hvordan du forbereder kildedataene for pivottabell, er du klar til Excel i verden med pivottabeller.
Her er noen andre Pivot -tabellopplæringer som du kan finne nyttige:
- Slik oppdaterer du pivottabell i Excel.
- Bruke skiver i Excel -pivottabell - En nybegynnerguide.
- Slik grupperer du datoer i pivottabeller i Excel.
- Hvordan gruppere tall i pivottabell i Excel.
- Pivot -cache i Excel - hva er det og hvordan bruker du det best.
- Slik filtrerer du data i en pivottabell i Excel.
- Slik legger du til og bruker et Excel -pivottabellberegnet felt.
- Slik bruker du betinget formatering i et pivottabell i Excel.
- Hvordan erstatte tomme celler med nuller i Excel -pivottabeller.
