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:
- allereerst de Legenda verwijderen
- rechts klikken op de draaitabel-knoppen (Som van .. en J..) en Verbergen kiezen
- rechts klikken op de linkeras en As opmaken kiezen
- Minimum vastzetten op 0 en Maximum op 21
- Waarde in omgekeerde volgorde aanvinken en OK klikken
- de linkeras verwijderen
- horizontale rasterlijnen verwijderen
- rechtsklikken op een lijn en Gegevensreeks opmaken kiezen
- bij Markeringsopties Standaard kiezen, als Type het rondje selecteren en de Grootte op 16 zetten
- Markeringsopvulling: een effen kleur kiezen en aanpassen aan de voorbeeldgrafiek (rood voor stijgers, blauw voor dalers etcetera)
- de Lijnkleur ook aanpassen via Ononderbroken streep en OK klikken
- nog een keer rechtsklikken op de lijn: Gegevenslabels toevoegen
- opnieuw rechtsklikken en dan Gegevenslabels opmaken kiezen
- de Labelpositie moet worden: Centreren en OK klikken
- dan op de rechtse markering van de lijn klikken (alleen dit punt van de grafiek is dan geselecteerd) en daar rechtsklikken. Kies Gegevenslabel opmaken
- Bij Label bevat ook de Reeksnaam aanvinken en als positie Rechts kiezen
- 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.