✅ Brongegevens voorbereiden voor draaitabel

Inhoudsopgave

Wat is een goed ontwerp voor de brongegevens voor draaitabel?
Veelvoorkomende valkuilen die u moet vermijden bij het voorbereiden van de brongegevens
Voorbeelden van ontwerpen voor slechte brongegevens
[CASE STUDY] Slecht opgemaakte gegevens converteren naar brongegevens die gereed zijn voor draaitabels

Het hebben van de gegevens in het juiste formaat is een cruciale stap in het creëren van een robuuste en foutloze draaitabel. Als u dit niet op de juiste manier doet, kunt u veel problemen krijgen met uw draaitabel.

Wat is een goed ontwerp voor de brongegevens voor draaitabel?

Laten we eens kijken naar een voorbeeld van goede brongegevens voor een draaitabel.

Dit is wat het tot een goed brongegevensontwerp maakt:

De eerste rij bevat koppen die de gegevens in de kolommen beschrijven.
Elke kolom vertegenwoordigt een unieke gegevenscategorie. Kolom C heeft bijvoorbeeld alleen productgegevens en kolom D en maandgegevens.
Elke rij is een record dat één exemplaar van de transactie of verkoop zou vertegenwoordigen.
De dataheaders zijn uniek en worden nergens in de dataset herhaald. Als u bijvoorbeeld vier kwartalen per jaar Verkoopnummers heeft, moet u deze NIET allemaal als Verkoop noemen. Geef in plaats daarvan deze kolomkoppen unieke namen, zoals Verkoop Q1, Verkoop Q2, enzovoort…
- Als u geen unieke titels heeft, kunt u toch doorgaan en een draaitabel maken en Excel maakt deze automatisch uniek door een achtervoegsel toe te voegen (zoals Sales, Sales2, Sales3). Dat zou echter een vreselijke manier zijn om een draaitabel voor te bereiden en te gebruiken.

Veelvoorkomende valkuilen die u moet vermijden bij het voorbereiden van de brongegevens

Er mogen geen lege kolommen in de brongegevens staan. Deze is gemakkelijk te herkennen. Als u een lege kolom in de brongegevens heeft, kunt u geen draaitabel maken. Het zal een fout tonen zoals hieronder getoond.
Er mogen geen lege cellen/rijen in de brongegevens staan. Hoewel u met succes een draaitabel kunt maken ondanks lege cellen of rijen, zijn er veel bijwerkingen die u later op de dag kunnen bijten.
- Stel dat u bijvoorbeeld een lege cel in de verkoopkolom heeft. Als u een draaitabel maakt met deze gegevens en het verkoopveld in het kolommengebied plaatst, ziet u de AANTAL en niet de SOM. Dat komt omdat Excel de hele kolom interpreteert als tekstgegevens (alleen vanwege een enkele lege cel).
Relevante opmaak toepassen op cellen in de brongegevens. Als u bijvoorbeeld datums heeft (die als serienummers zijn opgeslagen in de backend in Excel), past u een van de acceptabele datumnotaties toe. Dit zou u helpen bij het maken van de draaitabel en het gebruik van datum als een van de criteria om de gegevens samen te vatten, te groeperen en te sorteren.
- Als je een paar seconden hebt, probeer dit dan. Maak de datums in uw draaitabel op als getallen en maak vervolgens een draaitabel met deze gegevens. Selecteer nu in de draaitabel het datumveld en kijk wat er gebeurt. Het zal het automatisch in het waardengebied plaatsen. Dat komt omdat uw draaitabel niet weet dat dit datums zijn. Het interpreteert deze als getallen.
Neem geen kolomtotalen, rijtotalen, gemiddelden, enz. op als onderdeel van de brongegevens. Als je eenmaal de draaitabel hebt, kun je deze later gemakkelijk ophalen.
Maak altijd een Excel-tabel en gebruik deze vervolgens als bron voor een draaitabel. Dit is meer een goede gewoonte en geen valkuil. Uw draaitabel zou prima werken met brongegevens die ook geen Excel-tabel zijn. Het voordeel van Excel Table is dat het de uitbreidende gegevens kan aanpassen. Als u meer rijen aan de dataset toevoegt, hoeft u de brongegevens niet steeds opnieuw aan te passen. U kunt de draaitabel eenvoudig vernieuwen en deze zou automatisch rekening houden met de nieuwe rijen die aan de brongegevens zijn toegevoegd.

Voorbeelden van ontwerpen voor slechte brongegevens

Laten we eens kijken naar enkele slechte voorbeelden van ontwerpen van brongegevens.

Slecht ontwerp van brongegevens - voorbeeld 1

Dit is een gebruikelijke manier om gegevens bij te houden, omdat deze gemakkelijk te volgen en te begrijpen zijn. Er zijn twee problemen met deze gegevensindeling:

Je krijgt niet het volledige plaatje. U kunt bijvoorbeeld zien dat de verkoop voor Mid West in kwartaal 1 2924300 is. Maar is het een enkele verkoop of een aantal verkopen. Als u elk record in een aparte rij beschikbaar heeft, kunt u een betere analyse maken.
Als u hiermee doorgaat en hiermee een draaitabel maakt (wat u kunt), krijgt u verschillende velden voor verschillende kwartalen. Iets zoals hieronder weergegeven:

Slecht ontwerp van brongegevens - voorbeeld 2

Deze gegevensrepresentatie kan goed worden ontvangen door het management en het publiek van PowerPoint-presentaties, maar is niet geschikt voor het maken van een draaitabel.

Nogmaals, dit is het soort samenvatting dat u eenvoudig kunt maken met behulp van een draaitabel. Dus zelfs als u uiteindelijk zo'n look voor uw gegevens wilt, moet u de brongegevens in een Pivot-ready formaat onderhouden en deze weergave maken met behulp van de draaitabel.

Slecht ontwerp van brongegevens - voorbeeld 3

Dit is weer een output die gemakkelijk kan worden verkregen met behulp van een draaitabel. Maar het kan niet worden gebruikt om een draaitabel te maken.

Er zijn lege cellen in de dataset en de kwartalen zijn verspreid als kolomkoppen.

Ook wordt de regio bovenaan gespecificeerd, terwijl deze onderdeel zou moeten zijn van elk record.

[CASE STUDY] Slecht opgemaakte gegevens converteren naar brongegevens die gereed zijn voor draaitabels

Soms krijgt u een gegevensset die niet geschikt is om te worden gebruikt als brongegevens voor draaitabel. In een dergelijk geval heeft u misschien geen andere keuze dan de gegevens om te zetten in een Pivot-vriendelijk gegevensformaat.

Hier is een voorbeeld van een slecht gegevensontwerp:

Nu kunt u Excel-functies of draaitabel gebruiken om deze gegevens om te zetten in een indeling die kan worden gebruikt als brongegevens voor draaitabel.

Laten we eens kijken hoe beide methoden werken.

Methode 1: Excel-formules gebruiken

Laten we eens kijken hoe u Excel-functies kunt gebruiken om deze gegevens om te zetten in een draaitabel-ready formaat.

Maak een unieke kolomkop voor alle categorieën in de oorspronkelijke dataset. In dit voorbeeld zijn dat Regio, Kwartaal en Verkoop.
Gebruik in de cel onder de kop Regio de volgende formule: =INDEX($A$2:$A$5,ROUNDUP(ROWS($A$2:A2)/COUNTA($B$1:$E$1),0))
- Sleep de formule naar beneden en alle regio's worden herhaald.
Gebruik in de cel onder de kop van het kwartaal de volgende formule: =INDEX($B$1:$E$1,ROUNDUP(MOD(ROWS($A$2:A2),COUNTA($B$1:$E$1)+0.1) ,0))
- Sleep de formule naar beneden en het herhaalt alle kwartalen.
Gebruik in de kop onder Verkoop de volgende formule: =INDEX($B$2:$E$5,MATCH(G2,$A$2:$A$5,0),MATCH(H2,$B$1:$E$1,0 ))
- Sleep het naar beneden om alle waarden te krijgen. Deze formule gebruikt de gegevens Regio en Kwartaal als opzoekwaarden en retourneert de verkoopwaarde uit de oorspronkelijke gegevensset.

Nu kunt u deze resulterende gegevens gebruiken als brongegevens voor draaitabel.

Klik hier om het voorbeeldbestand te downloaden.

Methode 2: Power Query gebruiken

Power Query heeft een functie die dit soort gegevens eenvoudig kan converteren naar Pivot-ready gegevensindeling.

Als u Excel 2016 gebruikt, zijn Power Query-functies beschikbaar op het tabblad Gegevens in de groep Ophalen en transformeren. Als u Excel 2013 of eerdere versies gebruikt, kunt u deze als invoegtoepassing gebruiken.

Hier is een uitstekende gids over het installeren van Power Query door Jon van Excel Campus.

Nogmaals, aangezien u de gegevens hebt opgemaakt zoals hieronder weergegeven:

Hier zijn de stappen om de brongegevens te converteren naar een draaitabel-ready formaat:

Converteer de gegevens naar een Excel-tabel. Selecteer de dataset en ga naar Invoegen -> Tabellen -> Tabel.
Controleer in het dialoogvenster Tabel invoegen of het juiste bereik is geselecteerd en klik op OK. Hiermee worden de tabelgegevens omgezet in een Excel-tabel.
Ga in Excel 2016 naar Gegevens -> Ophalen en transformeren -> Van tabel.
- Als u de Power Query-invoegtoepassing in een eerdere versie gebruikt, gaat u naar Power Query -> Externe gegevens -> Van tabel.
Selecteer in de Query-editor de kolommen waarvan u het draaien ongedaan wilt maken. In dit geval zijn dit die voor de vier kwartalen. Om alle kolommen te selecteren, houdt u de Shift-toets ingedrukt en selecteert u vervolgens de eerste kolom en vervolgens de laatste kolom.
Ga in de Query-editor naar Transformeren -> Elke kolom -> Draaikolommen ongedaan maken. Hierdoor worden de gegevens van de kolom geconverteerd naar een draaitabelvriendelijk formaat.
Power Query geeft generieke namen aan de kolommen. Verander deze namen in de namen die je wilt. Wijzig in dit geval Kenmerk in Kwartaal en Waarde in Verkoop.
Ga in de Query-editor naar Bestand -> Sluiten en laden. Hiermee wordt het dialoogvenster Power Query-editor gesloten en wordt een afzonderlijk werkblad gemaakt met de gegevens met niet-gedraaide kolommen.