Laudame Financials Ontdek meer

Op sollicitatiegesprekken voor financials wordt weleens gevraagd: hoeveel procent van Excel heb je onder de knie? Als iemand antwoordt met “bijna alles” dan is hij of extreem ervaren of juist extreem onervaren, waarbij de kans op dat laatste groter is. De mogelijkheden in Excel zijn namelijk zo divers en breed dat er weinigen zijn die de volledige omvang overzien en beheersen. Belangrijker misschien nog wel is vak-specifieke kennis, waardoor je met je Excel vaardigheden echt waarde toe kunt voegen. Mede daarom delen we binnen Laudame kennis en ervaring, o.a. op het gebied van Excel. In twee artikelen delen we daarvan een aantal highlights. In dit eerste artikel komen PowerQuery en zoekfuncties aan bod.

PowerQuery

Ben je bekend met PowerBI en verliefd geworden op de mogelijkheden van PowerQuery? Ook als je geen PowerBI gebruikt, kan je alsnog genieten van PowerQuery. PowerQuery zit namelijk ook in Excel (Data -> New Query). Je kan daar alles doen met je dataset wat ook in Power BI kan. Je kan niet zulke mooie visuals maken, maar wel heerlijke draaitabellen. Die kan je in Excel gemakkelijk toevoegen aan je PowerQuery model (Data -> From Table).

In PowerQuery kan je met “Draaitabel voor andere kolommen opheffen” je tabel tot een meer platte datatabel maken. Hiermee kantel je als het ware een deel van de tabel, waardoor het eenvoudiger is om data te filteren of andere berekeningen te maken. Bijvoorbeeld:

Zoekfuncties

We gebruiken allemaal wel eens Vlookup() en Match(), maar hoe werkt TRUE/FALSE (WAAR/ONWAAR,1/0) in het laatste argument eigenlijk?

  • Bij FALSE zoekt Excel op volgorde van boven naar beneden: is de eerste cel exact de overeenkomst of niet? Dan naar cel 2 en zo door tot hij de zoekwaarde vindt.
  • Bij TRUE gaat hij naar de cel in het midden van de selectie. Is de zoekwaarde kleiner of groter? Indien kleiner, dan kijkt hij naar het midden van de bovenste helft van de selectie en bij groter de onderste helft. Hij herhaalt deze stappen tot hij op 1 cel uitkomt. Hij pakt uiteindelijk die cel die gelijk is of kleiner is dan de zoekwaarde. Dit kan bijvoorbeeld handig zijn als je zoekt op een afgerond aantal/bedrag.

 

Je kan je voorstellen dat deze exercitie behoorlijk wat rekenkracht vergt. Als je meerdere malen Vlookup()/Match() gebruikt op een hoop regels is het verstandiger om een kolom toe te voegen die met Match() de juiste regel van je bron bepaalt. Om vervolgens met Index() die regel te gebruiken. Hierdoor hoeft Excel maar eenmalig de hele dataset door in plaats van meerdere keren (ook minder foutgevoelig).

Heb je bij Vlookup() weleens gehad dat je een error krijgt omdat je zoekt naar een cijfer dat wordt weergegeven als tekst? I.p.v. in de bron de tekst om te zetten naar een getal kan je ook in je Vlookup() aan het eerste argument &”” toevoegen (Vlookup(A1&””;B2:B5;1;0)). Je maakt dan tekst van de zoekwaarde.

In het volgende artikel gaan we in het opsporen van fouten in Excel, het controleren van je sheets en delen we een aantal tips & tricks.