Dynamische rapportages in Excel

Hoe bouwt u dynamische rapportages in Excel? Tony de Jonker van De Jonker Consultancy geeft u handige tips om uw financiële administratie te optimaliseren.

Als administrateur heeft u te maken met het bijwerken van verschillende soorten periodieke rapportages, waarbij de databron variabel is. Denk hierbij aan de maandelijkse financiële verslaglegging, waarbij u de actuele maand dient te rapporteren en de laatste twaalf maanden. Ook het bijwerken van de wekelijkse verkoopgegevens is een dynamisch gebeuren als u steeds de laatste data automatisch in de rapportage wilt betrekken. Verder kunt u dynamische rapportages bouwen als u zelf de rapportageperioden kunt uitkiezen. In Excel kunt u dit voor elkaar krijgen als u gebruik gaat maken van dynamische bereiknamen.

Functie ‘verschuiving’
Normaliter werkt u met statische bereiknamen in uw Excel-applicaties. Het toekennen van bereiknamen geschiedt op een de volgende twee manieren:

1. c, waarbij u namen kunt toekennen aan een cel of meerdere cellen;
2. via het naamvak, waarbij u het gemarkeerde gebied een naam geeft. Denk eraan de naam te bevestigen door op de Enter-toets te drukken.

Als u dynamische bereiknamen wilt maken, dient u via de eerstgenoemde methode een formule in te geven waarin de functie verschuiving (offset) voorkomt. Voor we de feitelijke formule maken, gaan we eerst uit de doeken doen hoe deze mysterieuze functie werkt. De functie ‘verschuiving’ ziet er als volgt uit:
=VERSCHUIVING(celreferentie; rijen; kolommen; hoogte; breedte)
De illustratie bij deze In Focus is een schematische weergave van de functie ‘verschuiving’.

Zoals u ziet bestaat de functie uit vijf argumenten:

1. Celreferentie: dit is de startcel van waaruit de functie start.
2. Rijen: geeft het aantal rijen naar onderen weer, gerekend vanuit de celreferentie. Negatieve getallen duiden het aantal rijen naar boven aan, vanuit de celreferentie.
3. Kolommen: geeft het aantal kolommen weer naar rechts. Negatieve getallen duiden het aantal kolommen naar links aan.
4. Hoogte: dit argument is optioneel. Als u het gebruikt, moet het een positief getal zijn en geeft het de hoogte van het gebied aan, uitgedrukt in rijen.
5. Breedte: ook dit argument is optioneel. Dient eveneens een positief getal te zijn en geeft de breedte van het gebied aan, uitgedrukt in kolommen.

We kunnen de werking van de ‘verschuiving’-functie controleren door deze te combineren met de som-functie (zie de illustratie).

De functie start vanuit cel B3, daarna vier rijen naar beneden, twee kolommen naar rechts, waarbij de som wordt bepaald vanuit het gebied dat vijf rijen hoog en drie kolommen breed is.

Dynamische bereiknaam
Nu terug naar het maken van een dynamische bereiknaam. Stel we hebben het voorbeeld zoals in onderstaande tabel (stelt u zich dit voor in Excel), waarbij zowel het aantal rijen als kolommen kan toenemen.

Voorbeeld voor het maken van een dynamische bereiknaam:

	A	B	C	D
—————————————————————
1	Maand	Omzet	Kosten	Aantal
2	jan	100	40	10
3	feb	150	55	15
4	mrt	180	90	20
5	apr	130	83	33

De dynamische bereiknaam wordt dan een combinatie van de functies ‘verschuiving’ en ‘aantalarg (counta)’ en ziet er als volgt uit:
=VERSCHUIVING(A1;0;0;AANTALARG($A:$A);AANTALARG($1:$1).
De functie start in cel A1 en gaat nul rijen en nul kolommen opschuiven (blijft dus staan) en vormt een dynamisch gebied dat wordt gevormd door het<

Volg CMWeb op LinkedIn!