Excel modellen: Power up jouw bereiken

Waarschijnlijk weet je het al, maar bereiken zijn de bouwstenen van professionele Excel modellen. De definitie van een bereik is simpel: een bereik wordt begrensd door de eerste lege rij boven en onder en de eerste lege kolom rechts en links van een aangesloten rechthoek van cellen. Hoe simpel het ook klinkt, de praktijk is soms echt verschrikkelijk. Zelfs ICT-specialisten weten vaak niet hoe ze een fatsoenlijke Excel bereik moeten maken.

Bereiken zijn altijd hetzelfde, maar de inhoud verschilt. Excel bereiken zijn te verdelen in drie typen. Je hebt bereiken waarin de invoer staat. Voor de hand liggend zijn dit de bereiken waarin alleen invoer en dus geen berekeningen staat. Je hebt ook bereiken, die je gebruikt voor de berekeningen, waarin de opgehaalde informatie in de vorm van linken gebroederlijk naast de berekeningen staat. De laatste soort is een uitvoer bereik. Deze bereiken bevatten alleen het beeld van de (berekende) bereiken.

De kunst van bereiken is dat je ze op de juiste wijze structureert. Dat vergt een logisch gedachtegoed en daar gaat het vaak fout. Ik toon je een praktijkvoorbeeld van een slecht doordacht bereik. In afbeelding 1 zie je een (invoer) bereik met projecten en in afbeelding 2 zie je een (invoer) bereik met personeelsleden. De uiteindelijke bedoeling is dat beide bereiken worden gecombineerd in een nieuwe tabel. Beide zijn opgemaakt met de Excel tabelfunctie, zodat beide bereiken eenvoudig in Power Query worden geïmporteerd.

Analyseer je het bereik met personeelsinformatie, dan lijkt daar op het eerste gezicht weinig mis mee. Alleen de twee laatste kolommen zijn onduidelijk. Er staat namelijk niets in. Navraag blijkt dat het de bedoeling is dat in deze kolommen de informatie uit de projectentabel wordt gevoegd.

Ook het bereik met de projecten ziet er op het eerste oog uit als een technisch valide bereik. Maar dit bereik heeft een addertje onder het gras. Informatie, die je in de rijen wilt hebben, staat in de kolommen. Voor het invullen lijkt het voor de hand om de personeelsnamen te gebruiken als kolomnaam, maar wil je de beide bereiken combineren, dan is het juist noodzakelijk om de namen in een kolom te hebben. 

De oplossing van dit probleem vind je in Power Query; de plek waar je alle problemen met bereiken oplost. Je laadt beide tabellen in Power Query, door in het Excel tabblad Gegevens te kiezen voor de secties Gegevens ophalen en transformeren de van knop van Tabel/bereik (afbeelding 3). Het editor venster van Power Query wordt geopend. In de projectentabel kies je ervoor om de kolommen Evelien en Henk te selecteren en in het tabblad Transformeren, sectie Alle kolommen, te kiezen voor de knop Draaitabel opheffen voor kolommen (afbeelding 4). Zoals je ziet in afbeelding 5 staan plotseling de namen van de medewerkers in rijen!

 UPLOAD

In het bereik van het personeel voeg je de informatie vanuit de projecten bij. In Excel leidt dit tot het gebruik van functie VERT.ZOEKEN (VLOOKUP) om de informatie van het projectenbereik bij het personeelsbereik te zoeken. En daarmee loop je in Excel tegen een levensgroot probleem aan: je hebt een dubbele zoeksleutel. Je zoekt namelijk de informatie door middel van de werknemersnaam én het projectnummer. In Excel vind je de oplossing door in iedere tabel een extra kolom te creëren, die van de beide sleutels een nieuwe combinatie sleutel maakt, die je gebruikt om te zoeken. Maar niet in Power Query; daar hoeft dat soort kunstgrepen niet. Daarmee wordt het Excel leven toch een stuk eenvoudiger.

Vanuit de tabel Personeel kies je in het tabblad Start voor de sectie Combineren en voor de knop Query’s combineren (afbeelding 6). In het venster Samenvoegen kies je gewoon voor meerdere kolommen! Je houdt daarvoor de CONTROL-toets ingedrukt, terwijl je in de juiste volgorde kolommen aanklikt. Een kind kan de was doen, zoals je ziet in afbeelding 7. Nu staat je nog twee stappen te wachten. Je moet de tabel uitklappen en de tabel laden in Excel. In afbeelding 8 zie je het resultaat in Excel.

 UPLOAD

De moraal van dit verhaal is dat je alles (maar dan ook alles) kunt oplossen in Excel. Daar is het programma bekend om. Maar vaak zijn de klassieke oplossingen lastig. Zoek je de gemakkelijkste weg in Excel bereiken, dan is je grote vriend Power Query. Daarmee zet je ieder onjuist geformuleerd bereik in enkele simpele muisklikken om naar een goed werkend bereik. En daarmee kan je weer meer oplossen in Excel.

Henk Vlootman is founder en CEO van Quanto. Hij geeft sinds 1994 Excel en Power BI trainingen aan managers over de gehele wereld. Vanaf 2013 ontving Henk jaarlijks de Microsoft MVP (Most Valuable Professional) award. Henk is de auteur van ‘Excelmodellen voor financiële economische informatie’ en ‘ExcelLeren, de Vlootman methode in de praktijk’ Tijdens Excel en Power BI congressen en conferenties is hij een gewaardeerd spreker. Henk is voorzitter van de stichting Power BI gebruikersgroep Nederland.