Totalen in een grafiek weergeven, dat is simpel: voeg de betreffende reeks toe aan de grafiek-gegevens. Misschien dat je deze reeks nog aan de secundaire as moet koppelen, maar daarmee ben je klaar.
Maar bij een draai-grafiek (een grafiek gebaseerd op een draaitabel) zul je merken dat je wel wat flexibiliteit inlevert; dan is een lijntje met totalen toevoegen niet mogelijk.
Met VBA kun je een heel eind komen, maar zonder programmeren niet. In dit artikel wat alternatieve mogelijkheden.
Basis-gegevens
We gaan uit van een tabel (tblData2 op het tabblad Data van het Voorbeeldbestand) met daarin Datums en het Soort artikel met een bijbehorend Aantal.
Wil je zien hoe dit overzicht tot stand is gekomen: kijk op het tabblad Basis van het Voorbeeldbestand. Daar worden telkens random nieuwe data gecreëerd.
Draaitabel
Om deze gegevens snel te kunnen analyseren maken we een draaitabel:
- selecteer een cel in de brongegevens
- klik in de menutab Invoegen in het blok Tabellen op de optie Draaitabel en dan op de button OK
- sleep de Datum naar het Rijen-gebied, Soort naar de Kolommen en Aantal naar het Waarden-gebied
- standaard zal Excel de datums direct groeperen (in Jaren, Kwartalen en Maanden)
LET OP één van de namen in de Rijen is nog steeds Datum, maar deze bevat nu de maanden.
Wil je een andere groepering? Zie het artikel Groeperen in een draaitabel. - sleep Jaren naar het Filters-gebied en verwijder Kwartalen uit de Rijen.
- de Soort is alfabetisch gesorteerd: versleep de kolom met de waarde Drie naar rechts (door de rand met de muis ‘vast te pakken’).
Het resultaat staat in het tabblad Ovz1 van het Voorbeeldbestand.
Draaigrafiek
Een grafiek maakt de onderlinge verhoudingen tussen de cijfers vaak een stuk duidelijker:
- selecteer een cel van de draaitabel
- kies in de menutab Hulpmiddelen voor draaitabellen/Analyseren in het blok Extra de optie Draaigrafiek
- kies de optie Gegroepeerde kolom en klik op OK
Verander je nu iets in de draaitabel (filter je bijvoorbeeld een bepaald jaar uit) dan past de grafiek zich automatisch aan.
Draaigrafiek aanpassen 1
De standaard-grafiek kent wel wat nadelen.
Allereerst willen we van die ‘lelijke’ veldknoppen af: klik rechts op één van de knoppen en kies de optie Alle veldknoppen verbergen in grafiek.
Op het tabblad Ovz2 van het Voorbeeldbestand staat het resultaat:
LET OP Excel geeft alle kolommen automatisch een kleur; normaal wijzig ik deze handmatig in vaste kleuren zodat ook bij filtering iedere Soort zijn eigen kleur houdt. Helaas: bij draaigrafieken worden deze instellingen door Excel niet vastgehouden.
Draaigrafiek aanpassen 2
Ook al is een grafiek bedoeld om intuïtief inzicht in de onderliggende cijfers te krijgen, dan nog werkt het goed (of is het zelfs noodzakelijk) om in een grafiek de waarde(s) van de belangrijkste gegeven(s) te laten zien.
In het tabblad Ovz3 van het Voorbeeldbestand heeft de grafiek een veelzeggende titel meegekregen:
- in een lege cel creëren we daartoe eerst de volgende formule:
=ALS(C2=”(Alle)”; “Totaal alle jaren: “;
“Totaal voor ” & C2 &”: “) & TEKST(DRAAITABEL.OPHALEN(“Aantal”;$B$4);”#.##0″)
Als in cel C2 alle jaren zijn gekozen dan nemen we een overeenkomende tekst, anders wordt de tekst Totaal voor met daarachter de inhoud van cel C2 (gekoppeld door het &-teken). Achter de zo gemaakte tekst plaatsen we met behulp van de functie DRAAITABEL.OPHALEN (zie het betreffende artikel) het totaal van Aantal. Met de functie Tekst geven we een opmaak mee (een punt voor de duizendtallen en geen decimalen).
- zorg dat de grafiek een Grafiektitel heeft; bijvoorbeeld op de volgende manier: selecteer de grafiek en klik op de + rechts daarvan en vink de betreffende optie aan.
- klik op de Grafiektitel en daarna in de formulebalk. Tik in = en klik dan op de cel uit de eerste stap (in Ovz3 is dat cel I2).
In de formulebalk komt dan automatisch de formule:
=’Ovz3′!$I$2. Druk op Enter.
Nog een paar slicers toevoegen (zie Slicers in Excel) en we hebben (het begin van) een interactief dashboard.
Bij het filteren in de draaitabel (hier mer behulp van slicers) kan het gebeuren dat het scherm ‘verspringt’; door een paar aanpassingen aan de draaitabel blijft de opmaak stabiel:
- om te zorgen dat in jaren waar (nog) niet alle maanden gevuld zijn (in het voorbeeld 2020), toch alle maanden zichtbaar zijn (en dus ook in de grafiek): klik rechts op een van de maanden in de draaitabel, kies Veldinstellingen, vink op het tabblad Indeling&afdrukken de optie Items zonder gegevens weergeven aan
- klik rechts op een van de cellen in het Waarden-gebied, kies Opties voor draaitabel en vink de optie Kolombreedte automatisch aanpassen uit
Draaigrafiek aanpassen 3
Maar we zijn niet gauw tevreden: we hebben nu het totaal aantal in de titel staan, maar wat zijn de totalen per maand? In de draaitabel staan de betreffende getallen netjes op het einde van iedere rij, maar we zien die niet terug in de grafiek.
Nog erger: er is ook geen optie om dat klaar te krijgen!
Uiteraard kunnen we met VBA aan de slag, maar daar is wel wat programmeer-arbeid voor nodig. Eens even kijken of het ook zonder kan: ja natuurlijk, we zorgen dat één reeks labels heeft en laten de inhoud van die labels wijzen naar de totalen per maand.
- zorg dat ergens in het tabblad een reeks cellen gevuld is met de rij-totalen. In het tabblad Ovz4 staan in de cellen C24:C35 verwijzingen naar de draaitabel met behulp van de functie DRAAITABEL.OPHALEN.
- klik op één van de kolommen in de grafiek
- klik op de + rechts van de grafiek en zet de Gegevenslabels aan, inclusief de optie Basis, binnenkant zodat alle labels op dezelfde hoogte komen
- klik rechts op een van de labels en kies de optie Gegevenslabels opmaken
- bij Labelopties kun je de waarden opgeven die weergegeven moeten worden (Waarde uit cellen). Maak hier een verwijzing naar de cellen C24:C35; vink de optie bij Waarde uit en dan Waarde uit cellen aan.
Helaas, deze methode heeft 2 (?) tekortkomingen (zie het tabblad Ovz4 van het Voorbeeldbestand):
- de labels worden gecentreerd op de overeenkomende kolom
- als de betreffende soort uitgefilterd wordt zijn ook de labels weg!
Draaigrafiek aanpassen 4
In het tabblad Ovz5 van het Voorbeeldbestand staat een grafiek die de totalen per maand toont, inclusief de procentuele verdeling over het jaar.
De volgende aanpassingen zijn doorgevoerd:
- allereerst is er ruimte gemaakt onder aan de grafiek: klik rechts op de linkeras, kies As opmaken en zorg dat de minimumgrens niet meer automatisch wordt bepaald maar (in dit geval) -200 is
- de notatie van de as is zodanig aangepast, dat de negatieve getallen niet worden weergegeven: #.##0;
- vanaf cel C24 worden de maandtotalen opgehaald:
=DRAAITABEL.OPHALEN(“Aantal”;$B$4;”Datum”;B24) - vanaf cel D24 bepalen we de inhoud van de teksten die we gaan toevoegen:
=ALS(C24=0;””;
TEKST(C24;”#.##0″)&
TEKEN(13)&
TEKST(C24/DRAAITABEL.OPHALEN(“Aantal”;$B$4);”0%”))
Als C24 nul is dan hoeft er niets weergegeven te worden, anders de inhoud van cel C24 samen met het resultaat van het maandresultaat (C24) gedeeld door het totale resultaat (weergegeven als percentage zonder decimalen); tussen de twee elementen staat een code 13 (‘naar de volgende regel’).
NB in cel D24 en verder is het resultaat van code 13 niet te zien, maar dadelijk in de grafiek wel. - klik ergens in de grafiek en kies dan in de menutab Invoegen in het blok Illustraties de optie Vormen en daarna bij Basisvormen de optie Tekstvak
en ’teken’ met de muis waar je het tekstvak wilt hebben
LET OP als je niet eerst ergens in de grafiek klikt wordt het tekstvak niet aan de grafiek gekoppeld maar aan het tabblad; bij het verplaatsen van de grafiek gaat het tekstvak dan niet mee! - klik in de formulebalk, tik het =-teken en klik met de muis op de cel met de tekst die weergegeven moet worden
- pas de grootte van de tekst en/of het tekstvak aan en verplaats het tekstvak naar de juiste plaats (pak met de muis de ‘rand vast’)
- herhaal de stappen 5 t/m 7 voor alle maanden
NB je kunt ook het eerste tekstvak kopiëren (Ctrl-C) en dan via Ctrl-V zoveel tekstvakken maken als nodig zijn. Die moeten dan nog verplaatst worden en de inhoud aangepast.