De grote sprong voorwaarts: relationeel werken met Excel 2013

Wie binnen Excel relationeel wilde werken met gegevens uit meerdere tabellen, was tot voor kort veroordeeld tot de niet erg gebruiksvriendelijke invoegtoepassing MS Query.

En hoewel deze invoegtoepassing nog steeds beschikbaar is binnen Excel 2013 lijkt het er – gezien de inmiddels gedateerde opzet welke vanaf Excel 2003 niet meer is gewijzigd – hard op dat dit onderdeel voor Microsoft niet meer de hoogste ontwikkelprioriteit heeft.

Wel is vanaf versie 2010 (althans voor bezitters van de professionele MS Office versie)  Excel uitgebreid met de invoegtoepassing Powerpivot. Ook met Powerpivot is het mogelijk om op basis van een draaitabel en meerdere gekoppelde tabellen  gegevens te filteren en te selecteren.

Het nadeel van Powerpivot is echter dat het qua menu en formulestructuur niet altijd even eenvoudig te begrijpen is voor Excel gebruikers.

Met de komst van Excel 2013 wordt het – op basis van een vernieuwd gegevensmodel – mogelijk om relationeel te werken binnen Excel zelf.

Excel 2013 werkt met een vernieuwd gegevensmodel waarmee het mogelijk is om – op basis van een draaitabel – met meerdere tabellen tegelijk te werken. In het vernieuwde gegevensmodel worden daartoe eerst relaties gelegd tussen verschillende geïmporteerde tabellen, of tabellen die binnen Excel zelf (op tabblad niveau) zijn gedefinieerd. 

Hierna kan binnen de vertrouwde draaitabelomgeving van Excel – maar nu op basis van meerdere tabellen tegelijk – volwaardig relationeel gewerkt worden.

Deze vernieuwing in Excel 2013 mag zonder veel overdrijving als een revolutie worden bestempeld, zeker voor de grote groep gebruikers die – om verbanden te kunnen leggen tussen tabellen op tabbladniveau – tot op heden veroordeeld is tot de niet erg transparante functie “Verticaal Zoeken”.

Bij deze vernieuwing past wel de opmerking dat enig begrip vereist is van de opbouw van relationele databases. En zeker zo belangrijk: je moet al wel bekend zijn met draaitabellen.

Om mogelijke kennislacunes op het gebied van relationele databases te dichten werken we in dit artikel een praktische case uit, die we van de grond af aan opbouwen in Excel.

Binnen onze casus gaan we aan de slag met een fictief meubelbedrijf en houden het simpel in de vorm van een vijftal tabellen.

Het bouwen van een database in Excel

Om te beginnen bouwen we in Excel de basis voor de relationele database.

Daarbij plaatsen we de volgende tabellen op vijf verschillende tabbladen:

• Klanten

• Orders

• Producten

• Verkopers

• Regio

Deze tabellen declareren we binnen Excel – via Invoegen -> Tabel – expliciet als tabel. Dit doen we (als voorbeeld voor de tabel “Klanten”) als volgt:

Door de tabellen expliciet als tabel in te voegen worden deze flexibel en worden rijtoevoegingen (in de vorm van eventuele extra records) automatisch in de relationele database betrokken.

Onderstaand beschrijven we kort de inhoud van de individuele tabellen plus de onderlinge relationele samenhang in de vorm van zogenaamde verwijssleutels.

De tabel “Klanten” is opgebouwd uit klantrecords en bevat de volgende 4 velden:

• Klantnummer;

• Klantnaam;

• Regio_ID;

• Betaalwijze

 

Binnen de tabel “Klanten” vormt het veld “Klantnr” de primaire (identificerende) sleutel en vormt het veld “Regio_ID” de verwijssleutel naar de tabel “Regio”. 

De tabel “Orders” vormt het hart van de database en is opgebouwd uit orderrecords met de volgende 6 velden:

• Ordernr;

• Orderdatum;

• Afleverdatum;

• Klantnr;

• WerknemersID;

• ProductID;

• Aantal

 

Binnen de tabel “Orders” is het veld “Ordernr” de primaire (identificerende) sleutel, en vormen de velden “Klantnr”, “WerknemersID” en “ProductID” de verwijssleutels naar respectievelijk de tabellen “Klanten”, “Verkopers” en “Producten”.

De tabel producten is opgebouwd uit productrecords, waarbij we de volgende 3 velden onderscheiden:

• ProductId

• Productomschrijving

• Prijs

 

Binnen deze tabel is het veld “ProductID” de primaire (identificerende) sleutel, welke tevens verwijst naar de tabel “Orders”.

De tabel “Verkopers” is opgebouwd uit records met verkopers en bevat 3 velden met de volgende werknemerskenmerken:

• WerknemersID;

• Werknemersnaam;

• Bonus %;

 

Binnen deze tabel is het veld “WerknemersID” de primaire (identificerende) sleutel, welke  tevens verwijst naar de tabel “Orders”.

De tabel “Regio” is opgebouwd uit records met regiogegevens en bevat de volgende velden:

• RegioID;

• Regionaam;

• Inwoneraantal;

 

Binnen deze tabel is het veld “RegioID” de primaire (identificerende) sleutel, en vormt tevens de verwijssleutel naar de tabel “Klanten”. 

Om een relationele database te creëren brengen we per tabel de beschreven relaties (“verwijssleutels”) in binnen het vernieuwde gegevensmodel van Excel.

Dit doe via we de knop “Relaties” welke vanaf Excel 2013 vast onderdeel uitmaakt van de tab “Gegevens”

Als we binnen het scherm “Relaties beheren” op “Nieuw” klikken kunnen we de relaties één voor één inbrengen:

 

Nadat we op deze wijze alle relaties gelegd hebben verschijnen deze allen in beeld als we tot slot nogmaals op de knop “Relaties” drukken.

Met onderliggend (met dank aan MS Query voor de weergave) het volgende structuurdiagram inclusief tabellen en (verbindende) velden:

 

Het benaderen van de database 

Om ook daadwerkelijke aan de slag te kunnen met de zojuist gecreëerde database gaan we naar de tab “Invoegen” en kiezen we voor “Draaitabel”.

Vervolgens kiezen we binnen het dialoogvenster “Draaitabel maken” voor het onderdeel “een externe gegevensbron gebruiken” en “Verbinding kiezen”.

Bovendien zetten we een vinkje bij het onderdeel “Deze gegevens toevoegen aan het gegevensmodel”.

 

Als we op OK drukken verschijnt het dialoogvenster “Bestaande verbindingen”.

Omdat we bovendien de tabellen willen verbinden binnen de draaitabel kiezen we in dit  dialoogvenster voor de tab “Tabellen” en vullen we “Alle Tabellen” in binnen het keuzemenu.

Na een druk op de knop “Openen” verschijnt een nog lege draaitabel met ter rechterzijde het Draaitabelvelden menu inclusief een weergave van de vijf bekende tabellen en alle onderliggende velden. 

 

 

Bij het vullen van onze eerste draaitabel kiezen we uit alle tabellen een of meerdere velden. Onder de sectie “Rijlabels” maken we achtereenvolgens de velden “Orderdatum”, “Ordernr”, “Klantnaam”, “Regionaam”, Productomschrijving” en “Werknamersnaam” aan.

 

In het Waardenveld van de draaitabel nemen we tot slot het veld “Aantal” op. Dit heeft de volgende (gerelateerde) draaitabel tot resultaat:

 

We zien in de draaitabel (waarbij we vanwege ruimtegebrek alleen het onderste en bovenste deel tonen en de rest verbergen) dat in het bereik C191:D201 de klant- en regionamen lijken te ontbreken.

Nadere bestudering in de onderliggende tabellen leert dat dit veroorzaakt wordt door het feit dat voor de ordernummers 10210 t/m 10220 klantnummers zijn gebruikt die in de tabel “klanten” (nog) niet zijn opgenomen. In relationele taal betekent dit dat sprake is van een zogenaamde “outer join”.  Maar deze term mag je direct weer vergeten. Herkenbaarder in dit verband is waarschijnlijk de foutmelding #NB# die in dergelijke gevallen verschijnt bij het verbinden van velden met de functie “Verticaal zoeken”.

Omdat we bij nader inzien niet alleen de aantallen, maar ook de omzet willen zien, besluiten we om de onderliggende tabel “Orders” eerst uit te breiden met de kolommen “Prijs” (H) en “Omzet”(I).

De toe te voegen kolom “Prijs” vullen we door via “Verticaal.zoeken” te verwijzen naar de tabel “producten” 

=VERT.ZOEKEN([@ProductID];Producten!A:C;3;0).

Na ingave in de eerste cel (H2) vult de gehele kolom zich vervolgens automatisch met bijbehorende prijzen.

De kolom “Omzet” vullen we door de bestaande kolommen “Aantal” en de nieuwe kolom “Prijs” met elkaar te vermenigvuldigen.

=[@Aantal]*[@Prijs]

Ook hier vult – na ingave in de eerste cel (I2) – de gehele kolom zich automatisch. 

Na invulling ziet de uitgebreide tabel “Orders” er dan als volgt uit:

 

Als we vervolgens weer overschakelen naar de draaitabel herkennen we onder de tabel “Orders” onmiddellijk de nieuw aangemaakte velden “Prijs” en “Omzet”.

Het toegevoegde veld “Omzet” vinken we aan en nemen we daarmee op in de draaitabel, als extra waardeveld.

In de draaitabel verschijnt nu ter rechterzijde – naast de al bestaande kolom “Som van aantal” – een extra kolom “Som van Omzet”. Deze bedraagt in totaal € 523.150 voor alle orderrecords.

 

Deze draaitabel vormt de uitvalsbasis waarmee we verder gaan experimenteren.

Dat doen we voornamelijk door binnen de “hoogste” draaitabelsectie “Filters”” aan de knoppen te gaan draaien. 

We starten bij de draaitabel welke we zojuist gecreëerd hebben en welke alle afgesloten orders bevat. 

Deze draaitabel vatten we op als “Feittabel” (in relationele taal ook wel aangeduid als “view” of “query”) waaruit we informatie willen filteren welke samenhangt met gerelateerde tabellen die we aanduiden als dimensie- of filtertabellen. 

Om te kunnen filteren met de dimensietabellen plaatsen we eerst de sleutelvelden “RegioID”, “ProductID” en “WerknemersID” van de dimensietabellen “Regio’s”, “Producten” en “Verkopen” onder de sectie “Filter”.

De bijbehorende filtering, waarbij in eerste aanleg nog steeds alle orders zijn geselecteerd, verschijnt linksboven in het scherm. Nu gaan we verder filteren op basis van de dimensietabellen en dat doe we binnen ons voorbeeld in drie stappen.

We willen allereerst weten wat de verkopen zijn voor uitsluitend de regio’s Noord, Zuid, West en Oost.

 

Vervolgens laten we er een tweede filter op los waarbij we enkel de verkopen voor Stoelen en Tafels willen zien.

 

Tot slot zoomen we nog verder in op het deel van de verkopen dat gerealiseerd is door Ine (X1) en Jan (X2). 

 

Na toepassing van deze drie filters resteert een omzet van € 43.800 bij 400 verkochte tafels en stoelen, verkocht door Ine of Jan

Willen we binnen dit resultaat nog verder filteren dan kan dit door toepassing van waardefilters per kolom. We kunnen bijvoorbeeld nog een “Labelfilter”, “Waardefilter” of een “Top 10 filter” loslaten op een kolom. Dit doen we door te rechtsklikken op de betreffende kolom en te kiezen uit het contextmenu.

Daarnaast bestaat tot slot de mogelijkheid om via de menukeuze “Waarden weergeven als” de gegevens in de waardekolommen alternatief te presenteren.

In onze casus hebben we, om filtering op dimensieniveau te verkrijgen, filters binnen de sectie “Filters” gedefinieerd. 

We kunnen exact hetzelfde resultaat verkrijgen als we gebruik maken van slicers. Het slicermenu is te benaderen via de Tab invoegen en de gelijknamige knop: 

 

Als we vervolgens de bijbehorende filters ingeven voor de dimensietabellen Verschijnt inderdaad een identiek resultaat:

 

Het vernieuwde gegevensmodel van Excel 2013 lijkt een prima opstap om relationeel te gaan werken. Daardoor hoef je geen worstelingen meer te ondergaan met de gebruikersonvriendelijke invoegtoepassing MS Query of voor veel Excel gebruikers toch moeilijk te doorgronden specifieke databaseprogramma’s zoals MS Access. 

Merkwaardig is wel dat deze vernieuwing door Microsoft erg low-profile wordt gepresenteerd, zeker in vergelijking met het trompetgeschal waarmee Powerpivot enige jaren terug nog werd binnengehaald. Of Powerpivot voor de gemiddelde gebruiker binnen de Excel context veel meer te bieden heeft is nog maar de vraag.

In de praktijk kun je binnen Excel 2013 – door velden slim te filteren en te positioneren (zie de casus) – praktisch alle selecties maken. Daarvoor heb je de relatief  ingewikkelde DAX formules, waarmee binnen Powerpivot geschermd wordt echt niet nodig.

En mocht je binnen de draaitabel toch verder willen rekenen dan kun je nog altijd terugvallen op de bekende functies als berekende velden en -items.