Excel tip: Een draaitabel maken met het Excel Datamodel

Gepubliceerd op: 10 oktober 2017Geschreven door: Michiel van der Arend

Hoe kun je 2 (hele) grote tabellen samenvoegen in Excel 2016 zonder gebruik te maken van Power Pivot?

Een draaimodel maken; als voorbeeld gaan we uit van een Excel bestand met twee grote tabellen (verkoop1 en verkoop2 genaamd) die elk rond de 1 miljoen rijen bevatten. Beide tabellen zijn identiek wat kolommen/kolom kopteksten betreft. We willen van deze twee tabellen één tabel maken om van daaruit een draaitabel te maken. Maar er passen geen 2 miljoen rijen op een Excel blad, dus simpelweg tabel 2 onder tabel 1 plakken is geen optie…

Daarom gaan we beide tabellen in het datamodel van Excel laden. Daarin kun je tot wel 100 miljoen rijen kwijt. Elke Excel 2016 versie heeft het datamodel, ook als je geen Power Pivot hebt. Bijkomend voordeel is dat zo’n Excel bestand met een datamodel veel kleiner is dan het oorspronkelijke Excel bestand met de twee losse tabellen.

Klik één cel in de eerste tabel aan en ga naar de menutab Gegevens. Klik in de groep Ophalen en Transformeren op Van Tabel/Bereik. Dit laadt de rijen in de query-editor.

Dan klik je op het pijltje bij de knop Sluiten en Laden en kies Sluiten en laden naar…

In de pop-up selecteer je Alleen verbinding maken en klik je op OK.

Dat doe je ook voor de tweede tabel. Dit levert nu twee dataverbindingen op, hier Verkoop01 en Verkoop02 geheten.

Daarna ga je weer naar de menutab Gegevens en klik je op het pijltje onder Gegevens ophalen. Je selecteert Query’s combineren / Toevoegen.

In het vervolgscherm selecteer je beide tabellen in willekeurig volgorde en klik je OK.

Beide tabellen worden nu samengevoegd in de Query editor. Ik heb de tabel Allesbijelkaartabel genoemd.

Ook nu klik je weer op het pijltje onder de knop Sluiten en Laden en kies je Sluiten en laden naar…. In de pop-up selecteer je Alleen verbinding maken én je selecteert Deze gegevens toevoegen aan het Datamodel.

Je hebt nu een derde verbinding gemaakt met daarin alle 1.899.988 rijen!

Vervolgens ga je beide tabellen van het Excel blad verwijderen (je hebt immers de data aan het datamodel toegevoegd). Het Excel bestand zal daardoor ook veel kleiner worden (van 42 mb naar 9 mb).

Nu gaan we de draaitabel maken:
Ga naar menutab Invoegen, klik op de knop Draaitabel en selecteer in de pop-up Gegevensmodel van deze werkmap gebruiken. Zet de velden op de juiste plek en de draaitabel is klaar voor gebruik.

Het ‘Venster controle’ is met name handig als de formule gebruik maakt van cellen van meerdere werkbladen. Nadat je van die cellen controle hebt toegevoegd, kun je de voortgang van al deze cellen blijven volgen in dit venster.