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

29-07-2021 07:27

Goed personeel vinden en binden hoort ook op de radar van de controller.

29-07-2021 07:21

Voorbereiden op het einde van de coronacrisis, stap 1: liquiditeit op orde.

29-07-2021 07:18

Risico’s bij het herstarten van de economie zijn er genoeg. Ben je daar als financial al alert op?

07-07-2021 13:44

Wat houdt je als leider staande in alle hectiek van vandaag? Moed en lef zijn essentieel, bleek tijd...

07-07-2021 11:54

Waarom floreren sommige ideeën van meet af aan, terwijl andere razendsnel ter ziele gaan? Verbeter d...

07-07-2021 11:23

Zes tips om je invloed te vergroten door effectief stakeholdermanagement.

01-07-2021 14:31

Sijthoff Media heeft het cursusaanbod NBA Opleidingen overgenomen van de Koninklijke Nederlandse Ber...

11-06-2021 10:02

De rol van Power BI, nieuwe features en andere ontwikkelingen in het favoriete gereedschap van finan...

11-06-2021 09:56

De NBA heeft het onderwerp continuïteit verplicht gesteld als onderdeel van de Permanente Educatie i...

03-06-2021 11:12

Als je de gelegenheid krijgt om ergens te spreken, pak die kans dan. Spreken is één van de meest kra...

25-05-2021 10:25

Volgens Susan Swarte (oud-CFO Olympia) is een goed presterend bedrijf nog geen bedrijf dat genoeg op...

25-05-2021 09:59

‘M&A-kanonnen’ Ton van Veen (Jumbo), Brent Wissink (Just Eat TakeAway.com) en Stefan Weda (Aon) over...