Herkent u de frustratie? Heb je net een grafiek ‘netjes’ gemaakt door de assen zodanig te schalen dat de gegevens mooi verdeeld zijn over de grafiek, klopt er niets meer van als er een andere of uitgebreidere reeks gegevens worden weergegeven.
Of je hebt ‘met de hand’ alle labels bij de punten aangepast (ja, ik gebruik nog een oude Excel-versie), dan gaan die bij een andere reeks de mist in.
Dus deze keer een anti-frustratie-artikel: 2 VBA-routines, die bovenstaande problemen oplossen.
Test-gegevens
Om een voorbeeld te kunnen maken had ik wat test-gegevens nodig. Meestal maak ik die door wat random-getallen of teksten te genereren.
Maar ik herinnerde me ineens dat ik ooit eens ergens gelezen had dat je ook test-gegevens op een site kon laten maken.
Na wat zoeken kwam ik de website mockaroo.com tegen.
Na een paar pogingen had ik een mooi lijstje met voorbeeld-gegevens (zie het tabblad Basis in het Voorbeeldbestand).
- bij Soort heb ik gekozen voor financiële markten (Mockaroo kiest dan willekeurig uit 2 mogelijkheden)
- de Datum ligt tussen 1 jan 2018 en 31 dec 2018
- bij Omschr heb ik voor willekeurige voornamen gekozen
- het Bedrag ligt tussen 1000 en 2000
Om de rest wat overzichtelijk te houden heb ik 20 regels daaruit geselecteerd (zie het tabblad Basis2) en een beetje aangepast. Deze test-gegevens staan klaar in een Excel-tabel met de naam Tabel1.
Grafiek
Om het verloop van de bedragen per Soort handig te kunnen weergeven, heb ik een draaitabel gemaakt op basis van de gegevens in Tabel1 (zie het tabblad Result in het Voorbeeldbestand).
Op basis van deze draaitabel kun je makkelijk een Draaigrafiek maken, maar dan komt Omschr automatisch op de x-as en ik wil deze gebruiken als Labels in de grafiek.
Dus maar een ‘gewone’ grafiek gemaakt met op de x-as de datums en op de y-as de bedragen.
Hiernaast staat de grafiek waarbij er geen Soort is geselecteerd (dus 20 punten). De x-as begint automatisch bij 19 januari omdat dat de kleinste datum is; iets vergelijkbaars geldt voor het einde van de as.
Excel heeft ook de y-as automatisch geschaald.
Assen handmatig aanpassen
Om de assen handmatig te schalen klik je met de rechtermuisknop op een as en pas je het minimum en het maximum aan (het vinkje daarvoor zal dan verdwijnen ten teken dat er geen automatische schaling hoeft plaats te vinden).
Maar iedere keer als er in de draaitabel een andere Soort wordt gekozen moeten er aanpassingen worden doorgevoerd.
Assen automatisch aanpassen
Om deze schaal-aanpassingen met VBA geautomatiseerd te kunnen doorvoeren moeten we ergens vastleggen wat het gewenste minimum en maximum is.
In het Voorbeeldbestand op het tabblad Result staat in cel G2 de formule =MIN(B:B)
Oftewel bepaal de kleinste waarde/datum in kolom B. In G3 wordt met de functie MAX de grootste waarde vastgelegd. Maar het is mooier als de x-as op de eerste van een maand begint: in cel H2 staat daartoe de formule =DATUM(JAAR(G2);MAAND(G2);1). Deze cel heeft de naam x_min gekregen.
Het maximum van de x-as moet de eerste dag van de volgende maand zijn: cel H3 (met de naam x_max) heeft daarom de formule =DATUM(JAAR(G3);MAAND(G3)+1;1)
NB ook als G3 een datum in december bevat, werkt deze formule goed; Excel zal automatisch het jaar ophogen en de maand gelijk maken aan januari.
In kolom J worden de minimum- en maximum-bedragen uit kolom D bepaald. Cel K2 bevat de formule =GEHEEL(J2/100)*100, zodat het minimum voor de y-as het eerste honderdtal is, kleiner dan de waarde in J2.
In cel K3 bepalen we de bovengrens voor de y-as: =(GEHEEL(J3/100)+1)*100
Nu hebben we nog een VBA-routine nodig, die de handelingen voor het aanpassen van de assen van ons overneemt.
In het tabblad Result is een knop met de naam Assen schalen toegevoegd waarmee deze macro/subroutine kan worden uitgevoerd.
Labels toevoegen
In de oudere versies van Excel is het niet mogelijk om automatisch extra informatie (zoals de Omschr uit het voorbeeld) als Labels aan een punt toe te voegen. Handmatig kan dit wel:
- klik rechts op de lijn in de grafiek
- kies de optie Gegevenslabels toevoegen
- klik op één van de labels; als dan alle labels zijn geselecteerd nogmaals op het label klikken
- de tekst van dit label kan nu aangepast worden
Maar als er nu in de draaitabel een andere Soort wordt gekozen dan kloppen de handmatig ingevoerde labels niet meer!
Daar komt VBA weer goed van pas:
Het spannende is hier wat er gebeurt met de variabele xVals.
Wanneer je in de grafiek op de lijn klikt verschijnt er in de formulebalk een formule die begint met =REEKS(
Daarna komt de cel, die de naam van de reeks bevat, dan de x-waarden en daarna de y-waarden.
Aangezien VBA Engelstalig is bevat xVals na het uitlezen van de formule van de Collection: =SERIES(Result!$B$4,Result!$B$6:$B$25,Result!$D$6:$D$25,1)
NB1 binnen Excel (met Nederlandse instellingen) worden de parameters gescheiden door een ; (punt-komma), maar binnen VBA is dit een , (komma).
NB2 de 1 op het einde geeft aan, dat deze lijn de eerste reeks is die in de grafiek geplot moet worden. We hebben maar één lijn, dus deze parameter heeft hier geen invloed.
We hebben in het vervolg van de routine alleen de reeks van de x-waarden nodig, de B6:B25.
Daarom nemen we eerst alles wat rechts van de eerste komma staat:
xVals = Mid(xVals, InStr(xVals, “,”) + 1)
en dan alles links van de volgende, dan weer eerste, komma:
xVals = Left(xVals, InStr(xVals, “,”) – 1)
xVals bevat nu het bereik Result!$B$6:$B$25
LET OP als de naam van het tabblad zelf ook een komma bevat dan wordt de formule wel wat ingewikkelder (zie VBA).
De For-Next-loop gaat dan alle elementen van dit bereik langs en plaatst de waarde rechts van de datum in het betreffende label.
Uiteraard is deze routine ook via een knop uit te voeren op het tabblad Result van het Voorbeeldbestand.
Titel van grafiek
De titel van de grafiek is dynamisch gemaakt:
- in cel N2 staat de formule: =”Bedragen van “&C2
Oftewel koppel de inhoud van cel C2 aan de tekst Bedragen van. - zorg dat de grafiek een of andere titel heeft
- klik ergens in de titel
- tik dan in de formulebalk =N2
- en druk op Enter
Hoi Gijs,
De assenschaalroutine heeft bij mij, en in jouw voorbeeld, ook invloed op pagina instelling ‘afdrukbereik’. Hoe is dit te voorkomen?
groet van Jeroen
Oeps!
De grafiek is nog geselecteerd, dus als je gaat afdrukken wordt alleen de grafiek geprint.
Voeg achter de laatste End With de volgende regel toe:
Sheets(“Result”).Range(“A1”).Select