Tagarchief: Label

Totalen in draai-grafiek



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:

  1. selecteer een cel in de brongegevens
  2. klik in de menutab Invoegen in het blok Tabellen op de optie Draaitabel en dan op de button OK
  3. sleep de Datum naar het Rijen-gebied, Soort naar de Kolommen en Aantal naar het Waarden-gebied
  4. 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.
  5. sleep Jaren naar het Filters-gebied en verwijder Kwartalen uit de Rijen.
  6. 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:

  1. selecteer een cel van de draaitabel
  2. kies in de menutab Hulpmiddelen voor draaitabellen/Analyseren in het blok Extra de optie Draaigrafiek
  3. 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:

  1. 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).
  1. 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.
  1. 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.

  1. 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.
  2. klik op één van de kolommen in de grafiek
  1. klik op de + rechts van de grafiek en zet de Gegevenslabels aan, inclusief de optie Basis, binnenkant zodat alle labels op dezelfde hoogte komen

  1. klik rechts op een van de labels en kies de optie Gegevenslabels opmaken
  2. 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:

  1. 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
  2. de notatie van de as is zodanig aangepast, dat de negatieve getallen niet worden weergegeven: #.##0;
  3. vanaf cel C24 worden de maandtotalen opgehaald:
    =DRAAITABEL.OPHALEN(“Aantal”;$B$4;”Datum”;B24)
  4. 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.
  5. 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!
  6. klik in de formulebalk, tik het =-teken en klik met de muis op de cel met de tekst die weergegeven moet worden
  7. 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’)
  8. 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.

Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd:


Interactieve grafiek



Deze week zag ik in de Volkskrant een mooie grafiek; daarin kon je niet alleen zien, welke 20 personen in 2018 in Nederland het meeste invloed hebben gehad, maar ook hoe ze in voorgaande jaren scoorden.

Door het veel verschillende kleurgebruik wel een beetje een kerstboom; maar dat zal wel beïnvloed zijn door de tijd van het jaar.

NB voor de systematiek en andere achtergronden, zie volkskrant.nl/hoe-de-top-200-van-invloedrijkste-nederlanders-wordt-samengesteld/

Al met al een aanleiding om eens te kijken of de grafiek (makkelijk) na te bouwen is in Excel en of we deze wat interactiever kunnen maken.

Basis-gegevens

Op de site van de Volkskrant zijn de basisgegevens niet terug te vinden; dus dan moeten we ze maar zelf uit de grafiek afleiden (zie het tabblad Data in het Voorbeeldbestand).

Wel ontbreken er dan nogal wat namen (de grijze lijnen in de grafiek); die zijn gecodeerd met Onbekend.  Deze personen komen niet meer in 2018 in de top-20 voor. De 1e onbekende zien we alleen in 2014 en 2015, terwijl de 2e onbekende ook nog in 2016 en 2017 voorkomt.

Alle data zijn opgeslagen in een Excel-tabel met de naam tblData. Bij uitbreiding van de gegevens zal Excel hier automatisch rekening mee houden.

Overzicht

Om de grafiek te kunnen maken genereren we eerst op basis van de bovenstaande gegevens een draaitabel; in de rijen komt het Jaar, in de kolommen de Naam van de personen en in het waardegebied het VolgNr (als we de gegevens goed in de tabel hebben ingevuld, komt iedereen maar 1 keer per jaar voor. Dus het is geen probleem om Som van VolgNr te gebruiken).

Deze draaitabel vormt op zijn beurt weer de basis voor een lijngrafiek (met markeringen).

Er moet nog wel wat aan de grafiek aangepast worden:

  1. allereerst de Legenda verwijderen
  2. rechts klikken op de draaitabel-knoppen (Som van .. en J..) en Verbergen kiezen
  3. rechts klikken op de linkeras en As opmaken kiezen
  4. Minimum vastzetten op 0 en Maximum op 21
  5. Waarde in omgekeerde volgorde aanvinken en OK klikken
  6. de linkeras verwijderen
  7. horizontale rasterlijnen verwijderen
  8. rechtsklikken op een lijn en Gegevensreeks opmaken kiezen
  9. bij Markeringsopties Standaard kiezen, als Type het rondje selecteren en de Grootte op 16 zetten
  10. Markeringsopvulling: een effen kleur kiezen en aanpassen aan de voorbeeldgrafiek (rood voor stijgers, blauw voor dalers etcetera)
  11. de Lijnkleur ook aanpassen via Ononderbroken streep en OK klikken
  12. nog een keer rechtsklikken op de lijn: Gegevenslabels toevoegen
  13. opnieuw rechtsklikken en dan Gegevenslabels opmaken kiezen
  14. de Labelpositie moet worden: Centreren en OK klikken
  15. dan op de rechtse markering van de lijn klikken (alleen dit punt van de grafiek is dan geselecteerd) en daar rechtsklikken. Kies Gegevenslabel opmaken
  16. Bij Label bevat ook de Reeksnaam aanvinken en als positie Rechts kiezen
  17. alle punten vanaf 8 opnieuw uitvoeren voor de overige lijnen; kies telkens de juiste kleur

Het resultaat mag er zijn (zie het tabblad Ovz1 van het Voorbeeldbestand):

NB het aanpassen van de grafiek is een flink karwei. Huiswerk voor een volgend artikel: het automatisch aanpassen van een grafiek met behulp van VBA.

Interactief overzicht

In de vorige grafiek staan zoveel lijntjes dat het soms niet meevalt om “de bomen door het bos te zien”.

Het zou natuurlijk mooi zijn als we een persoon zouden kunnen selecteren en dat dan de daarbij behorende “scores” in de grafiek automatisch benadrukt worden.

In het tabblad Ovz2 van het Voorbeeldbestand is via Gegevensvalidatie in cel S2 de invoer van een persoonsnaam geautomatiseerd (alleen namen uit de kop van de draaitabel van Ovz1 zijn toegestaan).
In cel S3 staat de formule: 
=INDEX(‘Ovz1’!$C$5:$AL$9;
VERGELIJKEN(R3;’Ovz1′!$B$5:$B$9;0);
VERGELIJKEN($S$2;’Ovz1′!$C$4:$AL$4;0))
Ofwel: zoek met behulp van Index in het blok C5:AL9 van het tabblad Ovz1 naar de rij die overeenkomt met de waarde in R3 en de kolom die overeenkomt met de naam in S2.

Omdat niet iedereen in elk jaar voorkomt kan deze formule ook soms de waarde 0 opleveren; deze waardes willen we niet in de grafiek zien, vandaar dat in T3 de volgende formule staat: =ALS(S3=0;NB();S3)

NB de functie NB() levert als resultaat de waarde #N/B. Deze waardes worden in een grafiek genegeerd. Zie ook het artikel grafiek-zonder-nullen.

In het tabblad Ovz2 van het Voorbeeldbestand is een kopie van de grafiek uit Ovz1 geplakt.
Aan deze grafiek is een nieuwe reeks toegevoegd, de cellen T3:T7.
Deze lijn van deze reeks heeft een rode kleur gekregen. Nog wat andere aanpassingen: Vloeiende lijn aanvinken en een pijl als Eindtype bij Lijnstijl.

Om het geheel rustig te laten ogen hebben alle overige lijnen een grijze kleur gekregen.

Kies in cel S2 een andere naam en de grafiek zal zich automatisch aanpassen!

NB een optie met slicers is handiger, maar dit wordt niet door iedere Excel-versie ondersteund.


Wil je op de hoogte gebracht worden, wanneer er weer een nieuw item aan Tips & trucs is toegevoegd: