In het onderstaande voorbeeld zie je een eenvoudige grafiek die de omzet per maand weergeeft.
Indien je deze grafiek eenmalig moet maken, is dit een prima werkende oplossing. Het wordt anders als je deze grafiek iedere maand moet bijwerken zodat hij de cijfers van de laatste 12 maanden laat zien. Alle maanden moeten dan één rij opschuiven, de eerste rij mag weg en de nieuwe maand moet je toevoegen. Het zou handig zijn als Excel dit automatisch doet. In de rest van dit artikel zal ik uitleggen hoe je dit kunt bereiken.
Zelf vind ik het handig om bij grafieken die ik regelmatig moet bijwerken een scheiding te maken tussen de brongegevens, de grafiekinstellingen en de grafiek zelf. Voor dit voorbeeld begin ik daarom met het opzetten van de brongegevens op een apart tabblad met de volgende structuur.
De kolom waardeveld lijkt wellicht wat overdreven. Echter op deze manier kan ik de database makkelijk uitbreiden met bijvoorbeeld winstcijfers over dezelfde periode. Iedere kolom in deze tabel geef ik een naam (een zgn. benoemd bereik). De verwijzing die aan de naam hangt moet automatisch worden aangepast als ik een extra regel toevoeg. Hoe je dit kunt doen heb ik al eerder beschreven in het artikel: Een benoemd bereik dynamisch maken mbv VERSCHUIVING. In dit voorbeeld heb ik de volgende namen met bijbehorende formules gedefinieerd:
- Periode:
=VERSCHUIVING(data!$B$2;1;0;AANTALARG(data!$B:$B)-1;1) - Waardeveld:
=VERSCHUIVING(data!$C$2;1;0;AANTALARG(data!$C:$C)-1;1) - Bedrag:
=VERSCHUIVING(data!$D$2;1;0;AANTALARG(data!$D:$D)-1;1)
Vervolgens heb ik op een ander tabblad de instellingen voor de grafiek gemaakt. De structuur van dit blad ziet er als volgt uit:
De enige inputvelden in dit werkblad zijn de cellen C2 t/m C5. Alle andere velden zijn labels of formules. De input bestaat uit:
- de grafiektitel;
- de huidige maand;
- het huidige jaar;
- het waardeveld (als ik in de database ook een reeks winstcijfers had toegevoegd, dan had ik hier winst in plaats van omzet kunnen kiezen).
Vervolgens bepaal ik in de cellen C7 t/m N7 de periodereeks voor de grafiek. Cel N7 is gelijk aan C3. Daarna trek ik er steeds een maand af. Bij de waarde 1 moet Excel overspringen naar 12. Hiervoor gebruik ik de functie ALS (Engels: IF). De formule voor cel M7 is:
=ALS(N7>1;N7-1;12)
In rij 8 doe ik iets vergelijkbaars. Zodra de maand in de linker kolom hoger is dan de rechter (als deze van 1 naar 12 springt), moet het jaar met één worden verminderd. De formule in M8 is:
=ALS(M7>N7;N8-1;N8)
In rij 9 voeg ik maand en jaar samen om te zorgen dat de periode hetzelfde formaat heeft als in de database. Ik gebruik in cel M7 de volgende formule:
=TEKST(M7;”00″)&”.”&M8
Met de functie TEKST (Engels: TEXT) kan ik een getal omzetten naar tekst en zelf bepalen in welk formaat het getal moet worden afgebeeld. In dit voorbeeld wil ik graag dat het getal 1 wordt afgebeeld met een voorloop 0 (01). Dit is mogelijk met de getalnotatie “00”. Het & teken doet hetzelfde als de functie TEKST.SAMENVOEGEN (Engels: CONCATENATE). Het plakt de tekst in de verschillende cellen aan elkaar.
Ten slotte zoek ik in rij 10 de bijbehorende omzet op in de database. Dit doe ik met behulp van de functie SOMPRODUCT (Engels: SUMPRODUCT). Daarbij kan ik handig gebruik maken van het feit dat ik van iedere kolom in de database een benoemd bereik heb gemaakt. De formule voor cel C10 is:
=SOMPRODUCT(–(Periode=C9);–(Waardeveld=$C$5);–(Bedrag))
De exacte werking van SOMPRODUCT heb ik beschreven in het artikel ‘Getallen optellen op basis van meerdere voorwaarden’. Dit is een ander voorbeeld waarbij deze formule mooi van pas komt. Door nu in cel C3 de huidige maand aan te passen (bijvoorbeeld van 10 naar 11), verandert Excel automatisch de grafiek.
Doordat ik in de database gebruik heb gemaakt van dynamisch benoemde bereiken kan ik makkelijk regels toevoegen. Zonder dat verdere aanpassing nodig is worden deze automatisch meegenomen in de grafiek.
Als je zelf nog eens wilt experimenteren dan kun je het Excelbestand, dat ik in dit voorbeeld heb gebruikt, [drain file 5 url hier] downloaden. Heb je nog vragen / opmerkingen over dit artikel? Heeft het je geholpen? Heb je een vergelijkbare vraag? Ik hoor het graag! Laat wat van je horen door hieronder een ‘comment’ in te vullen.