Onderwerp


Toon de laatste N records in een grafiek




Informatie


Stel dat je van een tabel met koersgegevens een grafiek hebt gemaakt. Elke handelsdag wordt een nieuw record aan de tabel toegevoegd zodat je de grafiek dus elke handelsdag moet aanpassen. Dat laatste is niet wat je wilt. Met Excel kun je dit proces echter automatiseren door middel van een "dynamisch bereik".

Download een
voorbeeldbestand en volg de volgende stappen om ook als een professional te werk te kunnen gaan.

• Maak een tabel met minimaal twee kolommen. (Dit voorbeeld gebruikt drie kolommen)
• Definieer dynamische bereiken
• Maak de grafiek

A) Maak de tabel
Een tabel met 3 kolommen en 10 records is voldoende. Gebruik hiervoor de kolommen B, C en D en start op rij 4.
Vul voor de veldnamen bij B4, "Tijdstip", bij C4, "Prijs" en bij D4, "Aantal" in. De rijen, of records, daaronder kun je naar eigen inzicht invullen. In dit voorbeeld wordt G4 gebruikt om het aantal records in de grafiek te tonen.

B) Definieer de dynamische bereiken
Om een dynamische grafiek 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.

Het dynamisch (of benoemd) bereik in dit voorbeeld bepaalt aan de hand van het aantal gevulde waarden in een kolom het aantal records van de grafiek.

Een dynamisch bereik is als volgt te maken.
- 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:

-
chtNR
Bij "verwijst naar" voer je in:
=Blad1!$G$4
Dit is het aantal records dat in de grafiek getoond wordt.

-
chtData
Bij "verwijst naar" voer je de volgende formule in:
=VERSCHUIVING(Blad1!$B$4;AANTAL(Blad1!$B:$B);0;-MIN(chtNr;AANTAL(Blad1!$B:$B));1)
Dit is het verschoven bereik dat de laatste "N" records van kolom B uit de database in de grafiek toont.

-
chtPrijs
Bij "verwijst naar" voer je de volgende functie in:
=VERSCHUIVING(chtData;0;1)
Deze naam verwijst naar kolom C: "Prijs".

-
chtAantal
Bij "verwijst naar" voer je de volgende functie in:
=VERSCHUIVING(chtData;0;2)
Deze naam verwijst naar kolom D: "Aantal".

C) Maak de grafiek
Maak een grafiek zoals je dat normaal gesproken doet. Een lijn grafiek is in dit geval de beste keus. Verwijder eventuele series en voeg twee series toe, te weten:
- chtPrijs
- chtAantal

Hiermee heb je een dynamische grafiek gemaakt. Wat er nu precies gebeurt is zal je wellicht niet helemaal duidelijk zijn. Vandaar een uitleg.




Hoe het werkt

Aan de basis van de grafiek staat het dynamische bereik
chtData. Dit bereik wordt bepaald via de functie:
=VERSCHUIVING(Blad1!$B$4;AANTAL(Blad1!$B:$B);0;-MIN(chtNr;AANTAL(Blad1!$B:$B));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.
Blad1!$B$4

- Rijen
Het aantal rijen dat het bereik verschoven ligt tov het startpunt.
AANTAL(Blad1!$B:$B)
Dit aantal is het aantal numerieke waarden in kolom B.

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

- Hoogte
Het aantal rijen dat het verschoven bereik telt.
-MIN(chtNr;AANTAL(Blad1!$B:$B))
Dit is het minimum van de waarde die is ingevuld in chtNr (G4), of het aantal numeriek waarden in kolom B. Aangezien je begint boven het laatste record in de database, vul je voor de hele formule een minteken (-) in.

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

Het dynamisch bereik
chtData vormt de categorie van de grafiek (de tijdstippen waarop gehandeld werd) en bepaald de laatste "N" records van de database die je in de grafiek wilt zien.

De dynamisch bereiken
chtPrijs en chtAantal vormen de series van de grafiek en worden afgeleid van chtData via de functie VERSCHUIVING. Het uitgangspunt van beide bereiken wordt namelijk gevormd door het dynamische bereik chtData. De te tonen waarden liggen vervolgens 1 en 2 kolommen rechts van dit uitgangspunt.