Maak een draaitabel van verschillende werkbladen

Met een draaitabel vat u uw gegevens in enkele seconden samen. U kunt rapporten maken, gegevens analyseren en deze eenvoudig met anderen delen.

Dat is het mooie van draaitabellen.

Maar hier is een wending:

Normaal gesproken kunt u bij het maken van een draaitabel brongegevens uit één tabel in een werkblad selecteren, omdat u in Excel niet naar verschillende werkbladen kunt verwijzen.

Maar soms komt het voor dat we brongegevens uit meerdere werkbladen moeten gebruiken om een draaitabel te maken.

Daarom wil ik vandaag eenvoudige stappen met u delen om meerdere werkbladen in een draaitabel te gebruiken.

Het probleem!

Stel dat u de verkoopgegevens van uw bedrijf wilt analyseren en daar de jaarlijkse gegevens van de afgelopen vier jaar uit wilt halen.

Zo krijg je de datadump in Excel.

Houd er rekening mee dat de gegevens in de 4 bladen verschillend zijn, maar dat de structuur exact hetzelfde is, dat wil zeggen hetzelfde aantal kolommen en koppen.

Om deze gegevens te analyseren, moet u één draaitabelrapport maken van deze meerdere bladen.

Enkele veel voorkomende methoden zijn:

1. Kopieer en plak de gegevens van elk blad handmatig en maak een enkele gegevensset op een nieuw blad.
2. Gebruik VBA-code om gegevens uit meerdere werkbladen automatisch te consolideren.
3. Of u kunt meerdere werkbladen consolideren in één werkblad met behulp van de consolidatieoptie van Excel.

Maar feit is dat deze methoden codering, kopiëren en plakken vereisen of repetitief zijn.

De oplossing

Hier zal ik een nieuwe methode bespreken met behulp van Microsoft-query die dynamisch, robuust en eenvoudig is.

Geloof me, je zult het geweldig vinden!

Met Microsoft Query kunt u een draaitabel maken van meerdere werkbladen.

Download dit gegevensbestand om mee te volgen.

Stappen voor het maken van een draaitabel op basis van meerdere werkbladen

Hier hebben we eenvoudige stappen die u kunt volgen. Download eerst dit bestand om het te volgen.

• Selecteer eerst alle gegevens op elk blad en geef deze een naam.
  • Gegevens uit 2005 met de naam – Jaar 2005
  • Gegevens uit 2006 genaamd – Jaar 2006
  • Gegevens uit 2007 genaamd – Jaar 2007
  • Gegevens uit 2007 genaamd – Jaar 2007

• Pas daarna gegevenstabellen toe op alle gegevens in vier werkbladen.
  • Selecteer een cel in het gegevensbereik.
  • Gebruik Ctrl + T om de gegevens op elk blad naar een tabel te converteren.
  • Zorg ervoor dat “Mijn tabel heeft kopteksten” elke keer is aangevinkt.
  • Herhaal deze handeling gedurende de 4 jaar (bladeren).

• Laten we beginnen met het consolideren van deze gegevens op een nieuw blad (snelkoppeling om een nieuw blad toe te voegen: Shift + F11).
• Klik op het tabblad Gegevens op ‘Uit andere bronnen’ -> Kies ‘Uit Microsoft Query’.

• In het vak Gegevensbron kiezen:
  • Klik op Excel-bestanden en druk vervolgens op OK.
  • Selecteer het pad van uw Excel-bestand en selecteer vervolgens uw bestand en klik op OK.
  • De benoemde bereiken verschijnen in het vak ‘Querywizard – Kolommen kiezen’.

• In de querywizard:
  • Sleep elk benoemd bereik (met behulp van de pijlknop) naar de “Kolommen in uw zoekvak”.
  • Klik op Volgende – Vergeet de fout.
  • En klik op OK

• Nu we alle gegevens in onze query-editor hebben, hoeft u alleen nog maar de gegevens uit alle benoemde bereiken te combineren. Om dit te doen, klikt u op de kleine SQL-knop.

• Verwijder in het SQL-vak alle tekst, schrijf een nieuwe query en klik op OK.

Selecteer * uit het jaar 2005Union allSelect * uit het jaar 2006Union allSelect * uit het jaar 2007Union allSelect * uit het jaar 2008

• Nu bevat de tabel die op het scherm verschijnt de gegevens van alle vier de bladen.
• Het enige wat we moeten doen is naar het tabblad Bestand gaan en deze tabel in Excel importeren.
• Importeer aan het einde de gegevens terug in Excel als een draaitabel.
  • In het menu Bestand -> klik op Gegevens retourneren naar Microsoft Excel.
  • U kunt zien dat we in totaal (over de 4 vellen) 592 records hebben.
  • Als u bovendien meer gegevens aan een van de vier bladen toevoegt, wordt de draaitabel bijgewerkt zodra u deze vernieuwt.