Onderwerp


Dynamiek in omzet, per merk en periode




Informatie


Dit voorbeeld toont een fictieve database met autoverkopen. Het is de bedoeling om hier een grafiek uit af te leiden die de verkopen van 4 willekeurige automerken in een bepaalde periode toont. Hoe werkt dat ? Dit voorbeeld maakt gebruik van benoemde bereiken, gegevens validatie, de functie SOMPRODUCT en een kolommen grafiek.

Download een
voorbeeldbestand en bekijk aan de hand van de volgende stappen hoe deze dynamische grafiek wordt gemaakt.

A) Maak de tabel
Op het werkblad Data is een tabel opgenomen die 350 records (rijen) en 6 velden (kolommen) bevat.

B) Definieer de dynamische bereiken
Om een grafiek dynamische te maken, heb je een dynamisch bereik nodig. Een dynamisch bereik is het resultaat van een functie of formule en wordt daardoor steeds opnieuw bepaald. Aangezien de uitkomst van de berekening een bereik is en geen waarde, kun je het resultaat niet als waarde in een cel opnemen. Om deze reden moet je een dynamisch bereik koppelen aan een naam. Deze naam slaat Excel in het geheugen op en het bereik waar de naam naar verwijst kun je gebruiken in je grafiek. Omdat de naam verwijst naar een bereik dat het resultaat is van een berekening, wordt dus ook vaak de term dynamisch benoemd bereik gebruikt.

Dit voorbeeld gebruikt 3 dynamische bereiken die aan de basis staan van de waarden die de grafiek in beeld brengt. Je kunt een naam als volgt aan een dynamisch bereik toewijzen.
- Kies via het menu voor: Formules > Naam Definiƫren
- In de dialoog "Nieuwe Naam" voer je een naam in en geef je de verwijzing naar het bereik aan

Maak de volgende benoemde bereiken aan:

-
rngDatums
Bij "verwijst naar" voer je de volgende formule in:
=VERSCHUIVING(Data!$C$6;0;0;AANTAL(Data!$C:$C);1)
Dit is het bereik dat de datums van alle records uit de database bevat.

-
rngMerk
Bij "verwijst naar" voer je de volgende functie in:
=VERSCHUIVING(rngDatums;0;1)
Deze naam verwijst naar kolom D: "Merk".

-
rngPrijs
Bij "verwijst naar" voer je de volgende functie in:
=VERSCHUIVING(rngDatums;0;4)
Deze naam verwijst naar kolom G: "Prijs".

C) Gebruik gegevens validatie bij 6 cellen die de brongegevens van de grafiek bepalen
Op het werkblad Grafiek is bij zes cellen gegevens validatie toegepast. In B6 en D6 kun je een datum selecteren. De lijst met datums kun je met gegevens validatie opnemen. Dit werkt als volgt.
- Selecteer
B6 en kies tabblad Gegevens > Gegevensvalidatie > Gegevensvalidatie... .
- Kies bij Toestaan: voor
Lijst en vul bij Bron: de volgende formule in: =rngDatums.
- Herhaal dit voor
D6

In
B9 t/m B12 kun je een automerk selecteren. Die lijst kun je ook met gegevens validatie opnemen. Dit werkt als volgt.
- Selecteer B9 t/m B12 en kies tabblad Gegevens > Gegevensvalidatie > Gegevensvalidatie... .
- Kies bij Toestaan: voor
Lijst en vul bij Bron: de volgende formule in: =rngMerk.

D) Gebruik de functie SOMPRODUCT om de totale omzet van een automerk in een bepaalde periode te berekenen
Op het werkblad Grafiek berekent de functie SOMPRODUCT in de cellen D9 t/m D12 de totale omzet van een automerk in een bepaalde periode. Dit werkt als volgt.
- Selecteer D9 en voer de volgende formule in:
=SOMPRODUCT((rngMerk=B9)*(rngPrijs)*(rngDatums>=$B$6)*(rngDatums<=$D$6))
Het resultaat is de totale omzet van het automerk in B9 in de periode die wordt bepaald in B6 en D6
- Herhaal dit voor
D10 t/m D12

E) Maak de grafiek
Maak een grafiek zoals je dat normaal gesproken doet. De gegevens voor de kolommen grafiek worden gevormd door de omzetten in D9:D12




Hoe het werkt

Aan de basis van de grafiek staat het dynamische bereik
rngDatums. Dit bereik wordt bepaald via de functie:
=VERSCHUIVING(Data!$C$6;0;0;AANTAL(Data!$C:$C);1)

De functie VERSCHUIVING verzorgt de dynamiek en kent een vijftal argumenten die allemaal worden gebruikt.
=VERSCHUIVING(Verwijzing;Rijen;Kolommen;[Hoogte];[Breedte])

- Verwijzing
Dit is het startpunt van de functie.
Data!$C$6

- Rijen
Het aantal rijen dat het bereik verschoven ligt tov het startpunt.
0

- Kolommen
Het aantal kolommen dat het bereik verschoven ligt tov het startpunt.
0

- Hoogte
Het aantal rijen dat het verschoven bereik telt. Dit is het aantal numeriek waarden in kolom C.
AANTAL(Data!$C:$C)

- Breedte
Het aantal kolommen dat het verschoven bereik telt.
1

Het dynamisch bereik
rngDatums vormt de basis van de andere bereiken. Het bereik rngMerk (=VERSCHUIVING(rngDatums;0;1)) bevindt zich 1 kolom naar rechts verschoven tov bereik rngDatums.
Het bereik
rngPrijs (=VERSCHUIVING(rngDatums;0;4)) bevindt zich 4 kolommen naar rechts verschoven tov bereik rngDatums.
Ook vormt het bereik
rngDatums de basis van de periode waarover de omzet wordt bepaald doordat het de lijst is waaruit je kunt kiezen in B6 en D6.

Het dynamisch bereik
rngMerk is de lijst waaruit je kunt kiezen in B9:B12.

De functie SOMPRODUCT
(=SOMPRODUCT((rngMerk=B9)*(rngPrijs)*(rngDatums>=$B$6)*(rngDatums<=$D$6))) werkt als Matrixfunctie. Het gekozen merk in B9 wordt gekoppeld aan de overeenkomende prijs in de database. Valt de datum binnen de gekozen periode (rngDatums>=$B$6)*(rngDatums<=$D$6), dan heb je een toegestane prijs die mag worden opgeteld bij het toaal van dat merk. Op deze wijze worden van 4 merken alle "treffers" uit de database opgeteld. Het eindresultaat vormt de 4 waarden die de grafiek bepalen.


!! De Engelstalige variant

De Engelse termen voor deze functies zijn:
=OFFSET(Data!$C$6;0;0;COUNT(Data!$C:$C);1)
=SOMPRODUCT((rngMerk=B9)*(rngPrijs)*(rngDatums>=$B$6)*(rngDatums<=$D$6))