Ik kreeg onlangs een vraag van een lezer over het combineren van meerdere werkbladen in dezelfde werkmap in één enkel werkblad.
Ik vroeg hem Power Query te gebruiken om verschillende bladen te combineren, maar toen realiseerde ik me dat dit voor iemand die nieuw is bij Power Query moeilijk kan zijn.
Dus besloot ik deze tutorial te schrijven en de exacte stappen te laten zien om meerdere bladen te combineren in één enkele tabel met behulp van Power Query.
Hieronder een video waarin ik laat zien hoe je gegevens uit meerdere werkbladen/tabellen combineert met Power Query:
Hieronder vindt u schriftelijke instructies voor het combineren van meerdere bladen (voor het geval u de voorkeur geeft aan geschreven tekst boven video).
Opmerking: Power Query kan worden gebruikt als invoegtoepassing in Excel 2010 en 2013 en is een ingebouwde functie vanaf Excel 2016. Op basis van uw versie kunnen sommige afbeeldingen er anders uitzien (afbeeldingen die in deze zelfstudie worden gebruikt, zijn afkomstig uit Excel 2016).
Combineer gegevens uit meerdere werkbladen met Power Query
Wanneer u gegevens uit verschillende werkbladen combineert met Power Query, moeten de gegevens in een Excel-tabel staan (of in ieder geval in benoemde bereiken). Als de gegevens niet in een Excel-tabel staan, zou de hier getoonde methode niet werken.
Stel dat je vier verschillende bladen hebt - Oost, West, Noord en Zuid.
Elk van deze werkbladen heeft de gegevens in een Excel-tabel en de structuur van de tabel is consistent (d.w.z. de koppen zijn hetzelfde).
Klik hier om de gegevens te downloaden en volg mee.
Dit soort gegevens is uiterst eenvoudig te combineren met Power Query (wat heel goed werkt met gegevens in Excel-tabel).
Om deze techniek het beste te laten werken, is het beter om namen te hebben voor uw Excel-tabellen (werk ook zonder, maar het is gemakkelijker te gebruiken wanneer de tabellen een naam hebben).
Ik heb de tabellen de volgende namen gegeven: East_Data, West_Data, North_Data en South_Data.
Dit zijn de stappen om meerdere werkbladen te combineren met Excel-tabellen met Power Query:
- Ga naar het tabblad Gegevens.
- Klik in de groep Gegevens ophalen en transformeren op de optie 'Gegevens ophalen'.
- Ga naar de optie 'Van andere bronnen'.
- Klik op de optie 'Lege zoekopdracht'. Hiermee wordt de Power Query-editor geopend.
- Typ in de Query-editor de volgende formule in de formulebalk: =Excel.Huidig werkboek(). Houd er rekening mee dat de Power Query-formules hoofdlettergevoelig zijn, dus u moet de exacte formule gebruiken zoals vermeld (anders krijgt u een foutmelding).
- Druk op de Enter-toets. Dit toont u alle tabelnamen in de hele werkmap (het toont u ook de benoemde bereiken en/of verbindingen voor het geval deze in de werkmap voorkomen).
- [Optionele stap] In dit voorbeeld wil ik alle tabellen combineren. Als u alleen specifieke Excel-tabellen wilt combineren, kunt u op het vervolgkeuzepictogram in de naamkop klikken en degene selecteren die u wilt combineren. Evenzo, als u benoemde bereiken of verbindingen hebt en u alleen tabellen wilt combineren, kunt u die benoemde bereiken ook verwijderen.
- Klik in de cel Inhoudskop op de dubbele pijl.
- Selecteer de kolommen die u wilt combineren. Als u alle kolommen wilt combineren, zorg er dan voor dat (Alle kolommen selecteren) is aangevinkt.
- Schakel de optie 'Originele kolomnaam als voorvoegsel gebruiken' uit.
- Klik OK.
De bovenstaande stappen zouden de gegevens van alle werkbladen in één enkele tabel combineren.
Als u goed kijkt, ziet u dat de laatste kolom (meest rechts) de naam heeft van de Excel-tabellen (East_Data, West_Data, North_Data en South_Data). Dit is een identificatie die ons vertelt welk record uit welke Excel-tabel komt. Dit is ook de reden dat ik zei dat het beter is om beschrijvende namen voor de Excel-tabellen te hebben.
Hier volgen enkele wijzigingen die u kunt aanbrengen in de gecombineerde gegevens in Power Query zelf:
- Sleep en plaats de kolom Naam naar het begin.
- Verwijder de "_Data" uit de naamkolom (zodat u Oost, West, Noord en Zuid overhoudt in de naamkolom). Klik hiervoor met de rechtermuisknop op de kop Naam en klik op Waarden vervangen. Vervang in het dialoogvenster Waarden vervangen _Data door een spatie.
- Wijzig de kolom Gegevens om alleen datums weer te geven (en niet de tijd). Klik hiervoor op de kolomkop Datum, ga naar het tabblad 'Transformeren' en wijzig het gegevenstype in Datum.
- Hernoem de query naar ConsolidatedData.
Nu u de gecombineerde gegevens van alle werkbladen in Power Query hebt, kunt u deze in Excel laden - als een nieuwe tabel in een nieuw werkblad.
Om dit te doen. volg de onderstaande stappen:
- Klik op het tabblad 'Bestand'.
- Klik op Sluiten en laden naar.
- Selecteer in het dialoogvenster Gegevens importeren de optie Tabel en Nieuw werkblad.
- Klik OK.
De bovenstaande stappen zouden gegevens van alle werkbladen combineren en u die gecombineerde gegevens in een nieuw werkblad geven.
Eén probleem dat u moet oplossen bij het gebruik van deze methode
Als u de bovenstaande methode hebt gebruikt om alle tabellen in de werkmap te combineren, krijgt u waarschijnlijk een probleem.
Zie het aantal rijen van de gecombineerde gegevens - 1304 (wat juist is).
Als ik nu de query vernieuw, verandert het aantal rijen in 2607. Vernieuw opnieuw en het zal veranderen in 3910.
Hier is het probleem.
Elke keer dat u de query vernieuwt, worden alle records in de oorspronkelijke gegevens toegevoegd aan de gecombineerde gegevens.
Opmerking: u zult dit probleem alleen tegenkomen als u Power Query hebt gebruikt om te combineren ALLE EXCEL-TABELLEN in het werkboek. Als je specifieke tabellen hebt geselecteerd om te combineren, zul je dit probleem niet tegenkomen.Laten we de oorzaak van dit probleem begrijpen en hoe dit te verhelpen.
Wanneer u een query vernieuwt, gaat deze terug en volgt deze alle stappen die we hebben genomen om de gegevens te combineren.
In de stap waarin we de formule hebben gebruikt: =Excel.Huidige werkmap(), het gaf ons een lijst van alle tabellen. Dit werkte prima de eerste keer omdat er slechts vier tafels waren.
Maar wanneer u vernieuwt, zijn er vijf tabellen in de werkmap - inclusief de nieuwe tabel die Power Query heeft ingevoegd waar we de gecombineerde gegevens hebben.
Dus elke keer dat u de query vernieuwt, wordt naast de vier Excel-tabellen die we willen combineren, ook de bestaande querytabel aan de resulterende gegevens toegevoegd.
Dit wordt recursie genoemd.
Hier leest u hoe u dit probleem kunt oplossen.
Zodra u =Excel.CurrentWorkbook() in de Power Query-formulebalk invoegt en op enter drukt, krijgt u een lijst met Excel-tabellen. Om ervoor te zorgen dat u alleen de tabellen uit het werkblad kunt combineren, moet u op de een of andere manier alleen deze tabellen filteren die u wilt combineren en al het andere verwijderen.
Hier zijn de stappen om ervoor te zorgen dat u alleen over de vereiste tabellen beschikt:
- Klik op de vervolgkeuzelijst en plaats de cursor op Tekstfilters.
- Klik op de optie Bevat.
- Voer in het dialoogvenster Filterrijen _Data in het veld naast de optie 'bevat' in.
- Klik OK.
Mogelijk ziet u geen verandering in de gegevens, maar als u dit doet, wordt voorkomen dat de resulterende tabel opnieuw wordt toegevoegd wanneer de query wordt vernieuwd.
Merk op dat we in de bovenstaande stappen hebben gebruikt "_Gegevens” om te filteren zoals we tabellen op die manier noemden. Maar wat als uw tabellen niet consistent worden genoemd? Wat als alle tabelnamen willekeurig zijn en niets gemeen hebben.
Dit is de manier om dit op te lossen: gebruik het filter 'is niet gelijk aan' en voer de naam van de query in (in ons voorbeeld zou dit ConsolidatedData zijn). Dit zorgt ervoor dat alles hetzelfde blijft en dat de resulterende querytabel die wordt gemaakt, wordt uitgefilterd.
Afgezien van het feit dat Power Query dit hele proces van het combineren van gegevens van verschillende bladen (of zelfs hetzelfde blad) vrij eenvoudig maakt, is een ander voordeel van het gebruik ervan dat het het dynamisch maakt. Als u meer records aan een van de tabellen toevoegt en de Power Query vernieuwt, krijgt u automatisch de gecombineerde gegevens.Belangrijke opmerking: in het voorbeeld dat in deze zelfstudie wordt gebruikt, waren de koppen hetzelfde. Als de koppen verschillend zijn, combineert Power Query alle kolommen in de nieuwe tabel en worden deze gemaakt. Als de gegevens voor die kolom beschikbaar zijn, wordt deze weergegeven, anders wordt er null weergegeven.
Misschien vind je de volgende Power Query-zelfstudies misschien ook leuk:
- Combineer gegevens uit meerdere werkmappen in Excel (met Power Query).
- Gegevens in Excel ongedaan maken met Power Query (ook bekend als Get & Transform)
- Een lijst met bestandsnamen uit mappen en submappen ophalen (met Power Query)
- Tabellen samenvoegen in Excel met Power Query.
- Hoe twee Excel-bladen/bestanden te vergelijken
