Met de nieuwe versie van Excel 365 kunt u door middel van dynamische array of matrixfuncties eenvoudig dynamische overzichten en grafieken maken zonder daarvoor een draaitabel te hoeven bijwerken of een macro te schrijven.
We starten met een Excel-tabel genaamd DataOmzet met de volgende indeling:
Automatische rapporten
Het eerste rapport toont een overzicht per locatie met bijbehorende projecten en datums.
Daartoe maakt u een gegevensvalidatielijst waarmee u de locatie kunt selecteren. Hiervoor maakt u in een afzonderlijk blad genaamd Lijsten de volgende lijst:
In cel B3 voert u de volgende formule in:
=SORTEREN(UNIEK(DataOmzet[Locatie]))
Met de functie UNIEK worden alle unieke locaties uit de DataOmzet-tabel gehaald en met de functie SORTEREN worden ze op alfabet geplaatst.
U maakt het selectieveld voor de locatie aan door uit het Lint te kiezen: Gegevens > Hulpmiddelen voor gegevens > Gegevensvalidatie (Data > Data Tools > Data Validation), waarna een dialoogvenster verschijnt die u als volgt invoert:
Met het symbool # wordt de lijst automatisch naar beneden uitgevuld.
In het rapportblad voert u in cel B7 de volgende formule in, waarna de tabel automatisch wordt aangevuld.
=KIES.KOLOMMEN(FILTER(DataOmzet;DataOmzet[Locatie]=Locatie);1;2;3;4)
Met de functie FILTER worden alle records gefilterd die gelijk zijn aan de gekozen locatie. De functie KIES.KOLOMMEN stelt u in staat om de kolomnummers uit de basistabel te selecteren. Kolomnummer 1 heeft betrekking op de kolom Locatie en kolomnummer 2 heeft betrekking op de kolom Project in de basistabel DataOmzet et cetera.
Het tweede rapport dat u gaat maken ziet er als volgt uit:
De selectie van de locatie geschiedt hier op analoge wijze als beschreven in het vorige rapport. In cel B7 voert u de volgende formule in:
=SORTEREN(UNIEK(DataOmzet[Project]))
In cel C7 geeft u de volgende formule in:
=SOMMEN.ALS(DataOmzet[Omzet];DataOmzet[Project];B7#;DataOmzet[Locatie];$C$4)
Met het # symbool wordt de formule automatisch naar onder uitgebreid.
Het staafdiagram is gebaseerd op het bereik B6-C13. Als er naderhand nieuwe projecten bijkomen in de basistabel, dan worden het rapport en grafiek automatisch uitgebreid.
In het verleden diende u een draaitabel/grafiek aan te maken en deze handmatig te verversen of hiervoor een macro te schrijven. Dit behoort nu tot het verleden en u kunt uw rapporten en grafieken automatisch uitbreiden.
Auteur: Tony De Jonker
Dit artikel is verschenen in cm: 2023, afl. 1.
Hier leest u meer Excel tips en trucs door Tony De Jonker
