Dynamisch rapport met automatische top 3 waarden

Naast de standaardrapporten die uit het ERP (Enterprise Resource Planning) beschikbaar zijn, worden diverse ad hoc rapportages en analyses gevraagd door het management. In deze bijdrage laten we een voorbeeld zien hoe u twee locaties met elkaar kunt vergelijken en grafisch kunt afbeelden.

Basistabellen

We beschikken over de volgende 4 omzettabellen met de standaardindeling: Locatie, Datum en Bedrag.

De tabellen zijn als een Exceltabel opgemaakt met respectievelijk de volgende tabelnamen: Amsterdam, Rotterdam, Utrecht en Maastricht.

Gewenste output

Er dient een dynamisch rapport te worden ontworpen waarmee het mogelijk is om twee locaties te selecteren en automatisch een grafiek af te beelden waarin de waarden van de locaties worden afgebeeld.

Oplossing

U dient een plaatshouder te maken met de volgende indeling:

De plaatshouder bevat de volgende dynamische matrixformules, die u kunt herkennen door de blauwe lijnomkadering:

• B4=Amsterdam[Datum]
• C4=SOMMEN.ALS(INDIRECT(C$3&”[Bedrag]”);INDIRECT(C$3&”[Datum]”);$B4#)
• D4=SOMMEN.ALS(INDIRECT(D$3&”[Bedrag]”);INDIRECT(D$3&”[Datum]”);$B4#)

De datums worden automatisch opgehaald uit de omzettabel Amsterdam. De formule in C4 is doorgekopieerd naar cel D4. Met de functie INDIRECT wordt het mogelijk om de naam van gekozen omzettabel variabel te maken.

Aangezien de datums als dynamische matrix worden afgebeeld, worden de SOMMEN.ALS formules zelf ook dynamisch omdat ze verwijzen naar de datumkolom.

Cellen C3 en D3 zijn voorzien gegevensvalidatie waardoor u de locaties door middel van een keuzelijst kunt selecteren. Via Gegevens à Hulpmiddelen voor gegevens à Gegevensvalidatie kunt u de volgende instellingen invoeren:

LEES OOK: Consolideren Excel-bestanden tijdrovende klus? Niet met deze truc