Bekijk video - Snelste manier om gegevens in Excel ongedaan te maken
Draaitabellen zijn geweldig als u binnen enkele seconden een enorme hoeveelheid gegevens wilt analyseren. Het stelt u ook in staat om snel verschillende weergaven van gegevens te creëren door simpelweg te slepen en neer te zetten.
En om een draaitabel te maken, moet u de gegevens in een specifieke draaitabel-ready formaat hebben.
In veel gevallen krijgt u de gegevens waarschijnlijk in indelingen die niet geschikt zijn voor draaitabellen.
Dit is vaak het geval wanneer iemand handmatig gegevens verzamelt en een indeling maakt die beter leesbaar is voor mensen (geen draaitabellen).
Iets zoals hieronder weergegeven:
Het bovenstaande gegevensformaat is iets dat u verwacht te krijgen als uitvoer van een draaitabelanalyse.
Wat als u nu dezelfde gegevens wilt analyseren en wilt zien wat de totale verkoop per regio of per maand was.
Hoewel dit eenvoudig kan worden gedaan met draaitabellen, kunt u de bovenstaande gegevens helaas niet in een draaitabel invoeren.
U moet dus gegevens ongedaan maken en het draaitabelvriendelijk maken.
Hoewel er enkele manieren zijn om dit te doen met behulp van de Excel-formule of VBA, is Power Query (Get & Transform in Excel 2016) de beste tool om de draaiing van gegevens ongedaan te maken.
Draai gegevens ongedaan maken met Power Query
Dit zijn de stappen om het draaien van gegevens ongedaan te maken met Power Query:
(Als uw gegevens al in een Excel-tabel staan, begin dan vanaf stap 6)
- Selecteer een cel in de gegevensset.
- Ga naar het tabblad Invoegen.
- Klik op het pictogram Tabel.
- Controleer in het dialoogvenster 'Tabel maken' of het bereik correct is. U kunt het bereik indien nodig wijzigen.
- Klik OK. Hiermee worden uw tabelgegevens omgezet in een Excel-tabel.
- Klik met een willekeurige cel in de Excel-tabel op het tabblad Gegevens.
- Klik in de groep Gegevens ophalen en transformeren op het pictogram 'Van tabel/bereik'.
- In het dialoogvenster Tabel maken dat wordt geopend (als het wordt geopend), klikt u op OK. Hiermee wordt de Query-editor geopend met behulp van de Excel-tabelgegevens.
- Klik in de Query-editor met de rechtermuisknop op de kolom Regio.
- Klik op de optie 'Andere kolommen ongedaan maken'. Hiermee worden uw gegevens onmiddellijk ongedaan gemaakt.
- Wijzig de naam van de kolom 'Attribuut' in een meer betekenisvolle naam, zoals 'Maanden'.
- Zodra u de Unpivoted-gegevens hebt, is het een goede gewoonte om ervoor te zorgen dat de gegevenstypen allemaal correct zijn. Klik in dit voorbeeld op één cel voor elke kolom en bekijk het gegevenstype op het tabblad Transformeren. Indien nodig kunt u ook het gegevenstype wijzigen.
- (Optioneel) Wijzig de naam van uw zoekopdracht in 'Verkoop'.
- Ga naar het tabblad Start (in de query-editor).
- Klik op Sluiten en laden.
Met de bovenstaande stappen wordt de draaiing van uw gegevensset ongedaan gemaakt met Power Query en teruggeplaatst in Excel als een tabel in een nieuw werkblad.
Nu kunt u deze gegevens gebruiken om verschillende weergaven te maken met behulp van een draaitabel. U kunt bijvoorbeeld de totale verkoopwaarde per maand of per regio bekijken.
De query vernieuwen wanneer nieuwe gegevens worden toegevoegd
Dit werkt allemaal prima.
Maar wat gebeurt er als er nieuwe data wordt toegevoegd aan onze originele dataset?
Laten we zeggen dat u gegevens voor juli ontvangt in hetzelfde formaat als waarmee we zijn begonnen.
Moet ik alle stappen opnieuw herhalen om deze gegevens in mijn niet-gedraaide dataset op te nemen?
Het antwoord is nee.
En dat is zo geweldig aan Power Query. U kunt doorgaan met het toevoegen van nieuwe gegevens (of het wijzigen van bestaande gegevens), en Power Query zou deze onmiddellijk bijwerken zodra u deze vernieuwt.
Laat me je laten zien hoe.
Stel dat hieronder de nieuwe dataset is die ik krijg (die aanvullende gegevens heeft voor juli):
Dit zijn de stappen om de reeds gemaakte query te vernieuwen en de draaiing van deze gegevens ongedaan te maken:
- Voeg deze nieuwe gegevens toe aan uw oorspronkelijke gegevens die u hebt gebruikt om de query te maken.
- Aangezien u gegevens toevoegt aan de aangrenzende kolom van een Excel-tabel, wordt de Excel-tabel uitgebreid om deze gegevens erin op te nemen. Als dit niet het geval is, doe het dan handmatig door het kleine omgekeerde 'L'-pictogram rechtsonder in de Excel-tabel te slepen.
- Ga naar het tabblad Gegevens en klik op Query's en verbindingen. Dit toont een paneel met alle bestaande query's erin.
- Klik met de rechtermuisknop op de verkoopquery in het deelvenster Query's.
- Klik op Vernieuwen.
Dat is het! Uw nieuwe gegevens worden onmiddellijk ongedaan gemaakt en toegevoegd aan de bestaande gegevens.
U zou merken dat het aantal rijen dat in de Query wordt weergegeven, wordt bijgewerkt om u de nieuwe nummers te laten zien. In dit voorbeeld was het 24 vóór de vernieuwing en werd het 28 na de vernieuwing.
Dit betekent ook dat als u draaitabellen hebt gemaakt met de gegevens die u uit Power Query hebt gehaald, die draaitabellen ook worden vernieuwd om u de bijgewerkte resultaten te tonen.
