Tagarchief: Label

Slicers voor gevorderden 😉



In veel artikelen op de website van G-Info worden slicers gebruikt om snel gegevens in een draaitabel te filteren. In het artikel Slicers in Excel hebben we de grondbeginselen laten zien; een soort Slicers voor Dummies dus.

Het werd tijd voor een vervolg, een artikel voor gevorderden.

Om wat materiaal te hebben om het gebruik van slicers toe te lichten, ging ik (uiteraard) weer eens rondkijken op de site van het CBS.
Daar trof ik een interessante pagina aan, waar geprobeerd wordt de ontwikkeling van de welvaart in Coronatijd zichtbaar te maken.
Dit aan de hand van 10 verschillende invalshoeken, zoals Gezondheid (sic!), Economie en Mobiliteit.

Je kunt daar niet alleen grafieken vinden, maar ook doorklikken naar onderliggende data. Dat is voor ons natuurlijk altijd fijn. Kunnen we kijken of we de grafieken na kunnen bouwen of misschien wel verbeteren.

In dit artikel laten we zien hoe de basis-gegevens er uitzien, hoe je daar snel en makkelijk grafieken van maakt en hoe Slicers een rol kunnen spelen bij de presentatie.

Daarnaast zullen we aan de hand van wat fictieve gegevens kijken hoe je de vormgeving van de slicers helemaal naar je hand kunt zetten.

Bron-gegevens

Zoals gezegd hebben we bron-gegevens ontleend aan een pagina op de CBS-website: cbs.nl/nl-nl/visualisaties/welvaart-in-coronatijd. Alle 10 de grafieken hebben een eigen bronbestand; wat opvalt is dat de structuur van de bestanden flink verschillen. Het ene bestand bevat gegevens over diverse jaren, een ander slechts de data van 1 jaar; de detaillering is soms een kwartaal, in een ander geval worden de gegevens getoond op weekbasis etc.

In de eerste 10 werkbladen van het voorbeeldbestand zijn de gegevens overgenomen (soms is de structuur daarvan iets aangepast om het creëren van een grafiek makkelijker te maken).

Gezondheid

Op het tabblad Sterfte van het voorbeeldbestand ziet u de grafiek van het CBS en de daarbij behorende gegevens. Als gezondheids-indicator hebben ze ervoor gekozen om de eventuele oversterfte te laten zien.

In de grafiek zijn de weken van 2021 weergegeven, terwijl de bron veel meer gegevens bevat. We hebben daarom aan de tabel tblOverlijden een kolom Grafiek toegevoegd, waarmee we kunnen aangeven of een bepaalde week in de grafiek zichtbaar moet zijn of niet:

=ALS(OF(
[@Jaar]*100+[@Week]<($C$4*100+$D$4);
[@Jaar]*100+[@Week]>($C$5*100+$D$5));
“Nee”;”Ja”)

Op basis van deze gegevens is een draaitabel gemaakt waarin per Jaar en Week de overlijdens etc. worden geturfd. In het Filter-blok staat het veld Grafiek:

Binnen de menutab Hulpmiddelen voor draaitabellen, in de tab Analyse staat de optie Draaigrafiek in het blok Extra.

Een paar aanpassingen:

  1. rasterlijnen verwijderen
  2. de titel en legenda verwijderen
  3. alle Veldknoppen verwijderen
  4. wijzig het grafiektype in Combinatie, waarbij de overlijdens en de verwachting een lijn worden en de andere 2 een vlak
  1. wijzig de achtergrondkleur en de kleuren van de items in de grafiek (het vlak van de Ondergrens krijgt dezelfde kleur als de achtergrond)
  2. pas de opmaak van de assen aan

NB1 je kunt aan punten van een grafiek ook labels koppelen. In dit geval hebben we alleen aan het laatste punt van de grafiek een label gehangen: klik op een lijn in de grafiek, dan nogmaals op het punt waar een label moet komen en klik dan met de rechtermuisknop op dat punt en kies de optie Gegevenslabel toevoegen (om eerlijk te zijn: het label is gekoppeld aan het punt van week 51 van 2021. Wanneer de bovengrens in het tabblad wordt gewijzigd, zal het label wel de juiste inhoud bevatten, maar niet naar het juiste punt wijzen! Uiteraard is dat aan te passen, maar dat gaat ver buiten de scope van dit artikel).

NB2 de inhoud van het zichtbare label wordt via een formule in het tabblad bepaald

NB3 de achtergrondkleur van de grafiek match exact met het origineel. Ik heb de Google Chrome-extensie Eye Dropper gebruikt om de juiste RGB-codes te achterhalen.

NB4 de grafiek is (nog) niet helemaal gelijk aan die van het CBS. Wat meer toelichting van de grafiek en de cijfers zou op zijn plaats zijn, maar ook dat valt buiten de scope van dit artikel.

LET OP wijzig je de grenzen voor de items die in de grafiek moeten worden weergegeven, vergeet dan niet de draaitabel te Vernieuwen (of rechts te klikken op het Grafiekgebied en de optie Gegevens vernieuwen te kiezen).

Welzijn

Op het tabblad Welzijn van het voorbeeldbestand staan de betreffende grafiek van het CBS, de brongegevens, een daarop gebaseerde draaitabel en een daarmee gekoppelde draaigrafiek.

In de bron zijn alleen kwartaalgegevens vastgelegd; de getallen op de horizontale as lijken me dat duidelijk genoeg aan te geven.

Het gemiddelde is aan de brongegevens als een aparte kolom toegevoegd.
Hetzelfde geldt voor de percentages die de wijziging t.o.v. de vorige maand weergeven.

NB de tekst Gemiddeld is in dit geval als een label aan het eerste punt van de horizontale stippellijn toegevoegd. Door de Notatie van het label aan te passen wordt niet de waarde weergegeven maar in dit geval een letterlijke tekst.

Samenleving

In het tabblad Huw van het voorbeeldbestand ziet u de grafiek van het CBS over Samenleving. Ze hebben daar het verloop van het aantal huwelijken en partnerschappen van de laatste jaren weergegeven.

De brongegevens bevatten daar slecht de gegevens vanaf 2019, dus onze grafiek is wat ‘kleiner’.

De gehanteerde methode is weer hetzelfde: maak een draaitabel op basis van de brongegevens, kies bij Analyse een daarop gebaseerde Draaigrafiek en maak nog wat lay-out-aanpassingen.

De tekst Gemiddeld is weer als een label aan de horizontale lijn gekoppeld. Bij het verplaatsen van dit label naar een plaats waar deze de grafiek niet overlapt, krijgen we ‘gratis’ een koppel-lijntje.

Veiligheid etc.

Ook voor Veiligheid en de andere items vindt u in diverse tabbladen de brongegevens zoals het CBS die beschikbaar stelt.

In het tabblad Veiligheid ziet u onder de CBS-grafiek ook weer een vergelijkbare Excel-grafiek. De manier waarop die is gemaakt bevat geen verrassingen. Misschien de inhoud wel: het aantal misdrijven is dalende, en dat niet alleen in de Corona-periode.

Aan u om voor de overige onderdelen een grafiek te maken. Juist ja: het valt buiten de scope!

Combinatie van data en slicers

In het tabblad DataTotaal van het voorbeeldbestand zijn de belangrijkste gegevens uit de 10 tabbladen in één tabel opgenomen.

Het soort gegeven staat in de kolom Item, het Jaar is logisch, in de kolom Periode kunnen zowel kwartaal-, maand- als weekaanduidingen voorkomen. In de kolom Waarde staan de bijbehorende percentages, aantallen, indexen etc.

Maken we op basis van deze gegevens een draaitabel dan wordt het een zootje: is die 3 bij periode nu een week, maand of kwartaal, wat stelt die 103 voor?

Plaatsen we Item nu in het Filter-gebied en kiezen we als filter het BBP dan wordt alles duidelijk:

De periodes zijn natuurlijk kwartalen en de waarde stelt een index voor (zie de indicatie in de item-naam).

Nog even een draaigrafiek maken via Hulpmiddelen voor draaitabellen/ Analyseren (en wat opmaak) en we zien de ontwikkeling van het BBP over de jaren.

Kies je een ander Item in het Filter-gebied dan krijgen we direct de daarbij behorende grafiek:

Het zou natuurlijk mooi zijn als de achtergrondkleur zich zou aanpassen aan het gekozen item, maar dat is niet de scope ….

Wat we wel kunnen verbeteren is de manier van filteren, namelijk met behulp van een slicer (wel de scope!):

  1. klik ergens in de draaitabel (of in de draaigrafiek)
  2. kies in de menutab Hulpmiddelen in het onderdeel Analyseren in het blok Filter de optie Slicer invoegen
  3. selecteer het veld (of velden) waarvoor je een slicer wilt
  4. en klik OK

In dit voorbeeld hebben we (uiteraard) gekozen om te filteren op Item.
Er ontstaat dan een slicer waarbij alle beschikbare item-namen onder elkaar staan.

Klik je in de kop van een slicer dan komt er een menutab Hulpmiddelen voor slicers tevoorschijn. Binnen de Opties hebben we het aantal Kolommen op 2 ingesteld en een Slicerstijl gekozen die qua kleur bij de grafiek past.
Nog even de breedte en hoogte aanpassen (via de grepen aan de randen) en klaar!

Slicer met meerdere koppelingen

In het tabblad SlicerDubbel van het voorbeeldbestand staat een simpel voorbeeldje met een tabel van Inkopen en Verkopen. Op deze tabel zijn twee draaitabellen gebaseerd.

Aan de eerste draaitabel is een slicer gekoppeld, waarbij de Soort Een is gefilterd.
Het zou natuurlijk mooi zijn als ook tegelijkertijd de tweede draaitabel wordt gefilterd.

NB als je een slicer toevoegt is het niet noodzakelijk dat dat onderdeel ook in het Filter-gebied van de draaitabel is geplaatst. In dit geval zou je dus Soort uit de kop van de draaitabellen kunnen verwijderen. De slicer maakt al duidelijk wat er gefilterd wordt.

Een slicer koppelen aan meerdere draaitabellen:

  1. klik in de kop van de slicer; daarmee selecteer je deze.
  2. in de menutab Hulpmiddelen voor slicers in de Opties kies je de optie Rapportverbindingen
  3. selecteer de draaitabellen die met behulp van deze slicer gefilterd moeten worden:

LET OP dit koppelen aan meerdere draaitabellen kan alleen wanneer de draaitabellen op dezelfde brongegevens zijn gebaseerd.

Soorten filters

Excel kent (op dit moment) 2 soorten filters: de Slicer zoals we die hiervoor al hebben gezien en een Tijdlijn. Deze laatste kan alleen maar gebruikt worden wanneer de brongegevens een kolom met datums bevat.

Als voorbeeld hebben we aan de tabel met misdrijven een kolom JrMnd toegevoegd, waarin de Jaar– en Maand-kolommen worden gecombineerd (zie het tabblad Veiligheid2 in het voorbeeldbestand).

Op basis van deze gegevens zijn weer een draaitabel en een draaigrafiek gemaakt. Door aan de grafiek ook nog een (lineaire) trendlijn toe te voegen is de tendens in de tijd goed te zien.

NB bij het maken van de draaitabel is het veld JrMnd naar het Rij-gebied gesleept. Excel ‘ziet’ dat het om een datum gaat en maakt direct meerdere velden aan in dat gebied. In dit geval, omdat de reeks meerdere jaren bevat, Jaar en daarnaast ook nog Kwartaal en de Maand. Geen Dag omdat Excel denkt, dat het niet zinvol is om zaken op dagbasis te rubriceren. Wil je dat wel dan moet je zelf de groepering van de datum aanpassen (zie Groeperen in een draaitabel).

In plaats van een Slicer voegen we nu een Tijdlijn in.

Dit is een heel handig hulpmiddel als je snel de resultaten van verschillende periodes wilt bekijken.

In dit geval hebben we gegevens van 5 jaren tot onze beschikking (en nog een maand in 2016, dus voor Excel 6 jaar); willen we een selectie maken dan moeten we met de schuifbalk onderaan aan de gang.

Het tijdsbestek kunnen we inperken door aan de zijkanten de begrenzing te verschuiven (zie het rode pijltje hierboven). Ook kun je maanden selecteren door op het blokje onder een maand te klikken, eventueel met Shift ingedrukt om een reeks maanden te kiezen.

NB je kunt geen ‘losse’ maanden selecteren, alleen een reeks aaneengesloten perioden.

In de Tijdlijn linksboven zie je altijd de huidige selectie; aan de rechterkant kun je de soort periode kiezen.

In het voorbeeld hebben we de Tijdlijn 2 keer gekopieerd met ieder een andere periodesoort.

Duidelijk is te zien dat een keuze in de ene Tijdlijn invloed heeft op de anderen.

Slicer-instellingen

Op allerlei manieren zijn Slicers (en Tijdlijnen) nog te fine-tunen.

De kopregel van de slicer is lang niet altijd nodig en misschien wel ongewenst wanneer die bijvoorbeeld de lay-out van je dashboard verstoort.

In het voorbeeld op het tabblad Huw2 van het voorbeeldbestand is het ook duidelijk zonder kop wat de 2 keuzemogelijkheden inhouden.

Klik met de rechtermuisknop op een slicer, kies de optie Slicerinstellingen en verwijder het vinkje bij Koptekst weergeven:

NB om alle filteringen in één keer ongedaan te maken is nu iets moeilijker; je kunt niet meer de -button in de slicerkop gebruiken. Klik rechts op de slicer en kies Filter wissen uit.

Om nog enkele andere slicer-instellingen te laten zien bevat het tabblad Instellingen van het voorbeeldbestand een tabelletje met per Datum en Soort een Aantal.

Op de bekende manier laten we daar een draaitabel op los en maken dan een draaigrafiek.

Nog een slicer voor de Soort en we kunnen analyseren.

Vervelend: de sortering in de slicer is niet zoals we zouden willen.

Maar in de Slicerinstellingen kunnen we alleen voor oplopend of aflopend kiezen en deze sortering is niet, zoals bij een draaitabel, handmatig aan te passen.

Wel ziet u bij de instellingen staan dat Excel rekening kan houden met Aangepaste lijsten.

Hoe maakt u zo’n Aangepaste lijst? (zie bijvoorbeeld ook Doorvoeren en sorteren)

  1. klik op de menutab Bestand
  2. kies Opties en daarbinnen Geavanceerd
  3. scrol helemaal naar beneden en daar ziet u onder het kopje Algemeen een button Aangepaste lijsten bewerken
  4. tik de lijst in, in de gewenste volgorde, en kies Toevoegen

NB1 hier zie je ook waarom Excel de dagen van de week en maanden altijd in de juiste volgorde plaatst bij een sortering.

NB2 je moet nog wel de draaitabel (of -grafiek) vernieuwen om de sortering in de slicer daadwerkelijk uit te laten voeren.

NB3 deze lijst wordt in het Excel-systeem vastgelegd en zal dus ook in andere werkmappen toegepast kunnen worden. Ook in nog nieuw te ontwikkelen systemen.

Voor deze draaitabel is ook een slicer voor de Maanden gemaakt. In de slicer zijn diverse periodes ‘grijs’.

Dat betekent dat die niet meer in de bron voorkomen of door een andere filtering nu niet meer actief zijn in de draaitabel.
In Slicerinstellingen kunt u de optie Items zonder gegevens verbergen aanvinken.

Opmaak van slicers

Door middel van de Slicerinstellingen, zoals hiervoor aangegeven, kunt u de lay-out van een slicer al behoorlijk aanpassen.

Via de menutab Hulpmiddelen voor slicers/Opties kun je ook een Slicerstijl kiezen, 8 lichtgekleurde varianten en 6 donkere.

Op het tabblad Opmaak van het voorbeeldbestand hebben we een groen/grijze grafiek gecombineerd met een slicer met als stijl Licht6.

Wilt u de slicer helemaal naar uw eigen hand zetten:

  1. klik met de rechter muisknop op de variant die het meest lijkt op hoe u het wilt hebben.
  2. kies de optie Dupliceren
  3. pas de naam aan
  4. kies een onderdeel (Volledige slicer, Koptekst et cetera) en klik op de button Opmaak
  5. herhaal het vorige punt voor alle onderdelen die u wilt aanpassen
  6. klik op OK

    In dit voorbeeld is de kop flink onder handen genomen, hebben we de rand van de slicer weggelaten en hebben we ook de kleuren van de niet-geselecteerde items aangepast.

Deze opmaak wordt bij de Slicerstijlen onder het kopje Aangepast opgeslagen; zie het voorbeeld KopGroen in het voorbeeldbestand.

NB1 zoals u ziet kunt u deze stijl ook als standaard voor alle NIEUWE slicers instellen

NB2 experimenteer met de diverse opties om te kijken wat het resultaat is.

LET OP de draaitabel op het tabblad Opmaak is onder de grafiek verborgen.

Slicer gekoppeld aan tabel

Tot nu toe hebben we slicers alleen gebruikt om gegevens in een draaitabel te filteren.
Maar slicers kunnen ook toegepast worden op Excel-tabellen (zie het tabblad Huw van het voorbeeldbestand). Een groot nadeel is, dat de filtering in de tabel die daardoor wordt uitgevoerd, er voor zorgt dat sommige gedeelten van het tabblad niet meer zichtbaar zijn (kies maar eens Partner als filtering).

Wanneer je een slicer aan de draaitabel koppelt, heb je dat probleem niet. En het gewenste resultaat (de grafiek in dit geval) wordt automatisch aangepast.


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


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?