Consolideren Excel-bestanden tijdrovende klus? Niet met deze truc

Het handmatig kopiëren en plakken kost de nodige tijd en kan tot fouten leiden. Het is mogelijk om voor een dergelijke klus een VBA-macro te schrijven, maar deze vaardigheid is niet voor iedereen weggelegd.

Gelukkig hebben we tegenwoordig Power Query waarmee je interactief de consolidatie met enkele stappen kunt uitvoeren. In dit artikel demonstreer ik deze mogelijkheid.

Uitgangspunt

Je dient voor elk bestand een uniforme lay-out aan te maken waarin de cijfers worden ingevoerd of berekend. Een dergelijk bestand kan er als volgt uitzien:

In dit bestand worden de omzetrecords per dag bijgehouden. Je dient de gegevens als tabel op te maken via het commando: Invoegen à Tabel.

Je dient de tabel de naam DataSales toe te kennen. Dat gaat via het Lint: Hulpmiddelen voor Tabellen à Ontwerpen à Eigenschappen à Tabelnaam.

Let erop dat je na het intypen van de naam de Entertoets indrukt om de invoer te bevestigen. De naam mag geen spaties bevatten. Om de leesbaarheid te verhogen maak ik gebruik van de Camel case notatie, waarbij hoofdletters afgewisseld worden met kleine letters. Het tabblad zelf heb ik data genoemd. Je mag daarvoor een willekeurig andere naam voor gebruiken. In ons voorbeeld hebben we vier bestanden: Oost, West, Zuid en Noord.

Deze vier bestanden dienen in een afzonderlijke map te worden opgeslagen waar geen andere Excel bestanden in voorkomen.

Werken met Power Query

Om de bestanden te consolideren ga je als volgt te werk:

• Open een nieuw bestand
• Uit het Lint kies je het commando: Gegevens à Gegevens ophalen en transformeren à Gegevens ophalen à Uit bestand à Uit map

• Er verschijnt een dialoogvenster waarmee je via de Bladeren knop de betreffende map kunt selecteren waar de afzonderlijke bestanden zijn opgeslagen. Daarna klik je op OK.

• Het volgende scherm verschijnt waarin je alle bestanden in de gekozen map kunt zien:

• Als je op de knop Combineren klikt zie je de volgende mogelijkheden:

• Met de eerste optie kun je de query aanpassen. De derde optie stelt je in staat om de gegevens naar het Datamodel (PowerPivot) te laden. We kiezen de tweede optie waardoor alle gegevens naar een nieuw Excel-blad worden overgebracht.
• In het volgende dialoogscherm klik je bij Weergave-opties op de tabel DataSales.

• Als je op OK-knop klikt zal het enige seconden duren voordat alle gegevens in een tabel bij elkaar zijn gevoegd. Het resultaat ziet er als volgt uit:

• Naast de reeds bekende kolommen maakt het systeem een eerste nieuwe kolom aan waarin de naam van het bestand wordt vermeld, zodat je altijd kunt traceren waar de gegevens vandaan komen. De tabel krijgt automatisch de naam toegekend van de sub map, in casu Filialen.

Aan de rechterkant ziet u de stappen die de Query Editor heeft gebouwd.

Als u de muis boven Filialen beweegt, zie je de daarbij behorende gegevens, waaronder de laatst vernieuwde datum.

Toevoegen nieuwe bestanden

Stel je voegt een nieuw bestand genaamd Midden toe in de map, dan kun je deze gegevens direct zien als je de Query gaat vernieuwen. Dat kan op 3 manieren:

• In de tabel kun je met de rechtermuisknop klikken en uit het verkorte menu kiest je Vernieuwen.
• Uit het Lint selecteer je: Gegevens à Query’s en verbindingen à Alles vernieuwen à Vernieuwen
• Uit het rechterpaneel Query’s en verbindingen klik je met de muis op het symbool in de laatste regel

Bij grote bestanden is het aan te bevelen om de geconsolideerde set niet als een Excel tabel weer te geven maar als een draaitabel met bijbehorende filters annex slicers.

Het is overigens niet een vereiste om de bronbestanden als Excel-tabel op te slaan. Het is ook mogelijk om de bladen in te lezen.

LEES OOK: Excel: Automatisch sorteren en rangschikken met één formule