1. Home
  2. Artikelen
  3. Excel modellen: Power up jouw bereiken

Excel modellen: Power up jouw bereiken


Hoe ga je om met de bereiken van jouw Excel modellen? Het lijkt simpel, maar de werkelijkheid blijkt complexer te zijn. Excel-goeroe Henk Vlootman legt het uit.

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.


Gerelateerde artikelen

Vier pijlers voor betere besluiten

29-07-2020 07:46

Hoe zorg je ervoor dat jij en je team komen tot de best mogelijke beslissingen?

5 stappen in Power BI: Stap 2 Modelleren en bereke...

28-07-2020 08:15

Modelleren en berekenen, Power BI en DAX: webinar van Excel MVP Tony de Jonker.

Tips om jouw Excel-model hufterproof te maken

27-07-2020 14:48

Wil je hufterproof Excel-modellen maken dan moet je een andere mindsetting van werken in Excel aanme...

Explosieve kracht in een cel: aggregaat berekening...

27-07-2020 14:38

Data is het nieuwe goud, maar aggregaat berekeningen in Excel laten dat goud glanzen.

Excel: de kunst van rij in kolom berekeningen

27-07-2020 14:24

Ik heb Excel nog nooit op een rekenfoutje betrapt. Het is de onkunde van de gebruiker die zorgt voor...

Hoe laat je Excel leven?

27-07-2020 13:27

Kijk voorbij de dagelijkse eisen en ontdek de onbekende meerwaarde van Excel.

Hoe Power BI je helpt op koers te blijven

27-07-2020 13:12

Hoe Power BI je helpt op koers te blijven

Overstappen van Excel naar Power BI?

27-07-2020 13:05

Power BI is hot. Voor nog geen tientje per gebruiker per maand, state of the art rapportage software...

De zeven pijlers van control voor morgen

23-07-2020 13:29

Wat moet je echt beheersen om als controller mee te kunnen blijven doen? Digitalisering, globaliser...

Hoe krijg je grip op verbonden partijen?

10-07-2020 08:14

Nieuwe methode om tijdig te signaleren hoe ernstig de situatie is: risicogestuurd grip. Een serie...

Veerkracht control, voor echte meerwaarde

10-07-2020 07:58

Als wáre businesspartner van het management biedt de controller veerkracht: wendbaarheid plus weerba...

Overname? Emoties kosten geld

08-07-2020 08:48

"Na de transactie moet er worden gewerkt om de krachten te bundelen." Evert Oosterhuis, partner Inte...