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!

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.

Excelleer met Excel

Je krijgt als financieel manager of accountant steeds meer gegevens te verwerken. Excel biedt uitmuntende mogelijkheden om in korte tijd relevante analyses te maken, een brij aan gegevens te ordenen en sluitende rapportages aan te leveren (inclusief 2 E-learning modules). 

Bekijk het programma

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.

 

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 voor financials

13-04-2021 11:38

De lessen van Joost Gietelink, CFO Enza Zaden, die alweer 4 jaar meeloopt in het innovatieve familie...

13-04-2021 11:32

De Financehub-module ‘Wat is Big Data’ geeft inzicht over de inzet van (big) data binnen bedrijven.

13-04-2021 11:23

CFO TV, de tweede uitzending. Over de overeenkomsten tussen CFO én Formule 1-coureur. Gezamenlijk do...

13-04-2021 11:18

In de allereerste aflevering van CFO TV zijn Paul Verhagen, Ingrid Tigchelaar en Roel Haverland te g...

13-04-2021 10:01

In dit tweede deel van de serie gaan we verder met vragen en antwoorden omtrent het maken van liquid...

08-04-2021 09:18

Risicomanagement negatief? Zeker niet, maar alleen als risicomanagers naar een geïntegreerde benader...

18-03-2021 10:29

Zeker 70 op de 100 fusies en overnames mislukken. Na de transactie moeten tenminste HR en de financi...

17-03-2021 14:09

P&C-expert Marco van Alfen deelt 4 stappen die nodig zijn om tot een data driven cultuur in forecast...

17-03-2021 13:55

Als gevolg van Covid zijn veel organisaties in zwaar weer gekomen, waarmee het bestuur met allerlei...

17-03-2021 13:47

Met stip op nummer 1: infectieziekten. Op langere termijn zal klimaatverandering grote impact hebben...

17-03-2021 13:01

Risicomanagement negatief? Zeker niet, maar alleen als risicomanagers naar een geïntegreerde benader...

12-03-2021 11:56

IFRS en US GAAP worden hoe langer hoe meer gelijk getrokken. Overeenkomsten, verschillen, en tips.