Cijferbeoordeling komt regelmatig om de hoek kijken als een van taken van de administrateur of controller. Cijfers worden geanalyseerd op mogelijke afwijkingen of trends.

Uit het ERP-pakket of boekhoudprogramma is het veelal mogelijk om twee perioden met elkaar te vergelijken, bijvoorbeeld huidige versus vorige periode. Het is echter meestal niet mogelijk om kolommenbalansen per maand naast elkaar te presenteren en minigrafieken ernaast te plaatsen. Hoe krijgt u dat voor elkaar?

Downloaden kolommenbalansen

U dient eerst na te gaan hoe u de kolommenbalans van een maand naar Excel kunt downloaden. U zorgt ervoor dat u voor elke maand een apart tabblad aanmaakt met de namen van de maanden, te weten: jan, feb, mrt enzovoorts.

De indeling ziet er (vereenvoudigd) als volgt uit:

In werkelijkheid zijn er meerdere kolommen die u kunt inlezen zoals omschrijving, rapportniveau, type rekening en wat dies meer zij.

Alle tabbladen hebben dezelfde indeling.

Aanmaken totaal-verzamelblad

Nu gaat u een totaal-verzamelblad aanmaken met de volgende indeling:

Om alle rekeningnummers compleet te hebben is het aan te bevelen om de grootboekrekeningen van het meest recente rekeningschema in kolom A te plaatsen (actieve en niet-actieve rekeningen).

De namen van de maanden in rij 1 moeten identiek zijn aan de namen van de tabbladen.

Opzoeken van de waarden

We willen nu de cellen vullen met één dynamische formule die kopieerbaar is in alle cellen.

In cel B2 geeft u de volgende formule in:

Met behulp van de functie INDIRECT kunnen we naam van het tabblad variabel maken. In ons voorbeeld wordt verwezen naar de variabele cel B1. In deze cel staat “jan”. Dit is dezelfde naam als de tabbladnaam “jan”. Het variabel deel wordt gekoppeld via het ampersand-teken (&) met de rest, te weten !$B:$B. Aangezien dit deel bij elkaar hoort, dient u aanhalingstekens eromheen te plaatsen.

In feite staat er dan: Jan!$B:$B. Dit kunt u ook via de functiewizard controleren.

De INDEX-functie zoekt als volgt de gewenste waarde:

• Zoek in de bronkolom waarnaar u op zoek bent. In dit geval bent u op zoek naar het bedrag, derhalve kolom B.
• In welke rij u dan moet kijken in deze kolom wordt bepaald met de functie VERGELIJKEN. U vergelijkt het rekeningnummer in het totaalblad met de rekeningnummers in de bronkolom, te weten kolom A. De functie VERGELIJKEN resulteert in een positienummer.
• Aangezien we precies willen zoeken is het laatste argument in de VERGELIJKEN functie een nul.

• Het laatste argument in de INDEX-functie geeft de kolompositie weer. Aangezien de bronkolom slechts uit een kolom bestaat, zien we hier de waarde 1 verschijnen.

Om mogelijke foutmeldingen tegen te gaan, heb ik de INDEX-functie nog voorzien van de ALS.FOUT functie.

Om de formule in B2 snel naar de overige cellen te kopiëren, gaat u als volgt te werk:

• Plaats de cursor in cel B2
• Met ingedrukte Shift-toets klikt u op de laatst gelegen cel rechts onderaan in de tabel.
• U klikt in de formulebalk
• Dan voert in de formule gelijktijdig in door te drukken op de toetsen Control-Enter.

Toevoegen van sparklines

U kunt nu aan elke regel een mini-grafiek toevoegen:

• Plaats de cursor in cel H2
• Kies uit het Lint: Invoegen > Sparklines > Lijn (Insert > Sparklines > Line)
• U vult het dialoogvenster als volgt aan:

• Klik op OK
• Kies uit het Lint: Hulpmiddelen voor sparklines > Weergeven (Sparkline > Show)
• Aanvinken: Hoogste punt en Laagste punt (High point en Low point)
• Vervolgens kopieert u cel H2 naar de ondergelegen cellen.

Het resultaat ziet er als volgt uit:

U kunt nu snel het verloop van diverse rekeningen zien over de afgelopen maanden. Dit is een zeer handig middel om cijferbeoordelingen uit te voeren.

Auteur: Tony De Jonker

Volg CMWeb op LinkedIn!