Binnenkort (nou ja, over een paar maanden) hebben we weer verkiezingen. In de aanloop daar naartoe zien we in de media steeds vaker de resultaten van diverse peilingen.

Op de site peilingwijzer.tomlouwerse.nl is het meest recente resultaat van een wetenschappelijk onderbouwde combinatie van 5 peilers (nee Maurice, geen pijlers!) te vinden.

Een grafiek op die site laat van 13 politieke partijen het verloop van de peilingen in de tijd zien; naast het gemiddelde van de 5 peilers zien we in de grafiek ook een 95%-onzekerheidsmarge (het licht gekleurde gebied).

Hieronder laat ik zien, hoe je in Excel een dergelijke grafiek kunt maken; we gebruiken daar een hoog-laag-slot-grafiek voor.
Daarbij moet wel aangetekend worden, dat de grafiek op de site van Tom Louwerse veel meer interactieve elementen bevat!

Basisgegevens

Zoals voor iedere grafiek hebben we basisgegevens nodig; deze zijn in dit geval op te halen vanaf de download-pagina van de Peilingwijzer.

In het Voorbeeldbestand heb ik op het tabblad Peilingwijzer deze gegevens geplaatst (op dit moment van 12 september 2012 tot en met 27 november 2016); zoals in eerdere voorbeelden ‘gieten’ we deze in de vorm van een tabel (Invoegen/Tabel, kopregels aanvinken) en geven die tabel de naam tblpeilingWijzer.

NB1 komen er nieuwe peiling-data beschikbaar, voeg deze dan onderaan de tabel als nieuwe regels toe.

NB2 komen er nieuwe partijen in het overzicht bij, voeg deze dan rechts aan de tabel als nieuwe kolommen toe.

Parameters

Hierna gaan we een grafiek maken van de peiling-resultaten. Hierbij kan echter maar één partij tegelijkertijd worden weergegeven. Om de invoer daarvan straks te vergemakkelijken leggen we de keuzemogelijkheden in een aparte tabel vast (zie tabblad Param in het Voorbeeldbestand).

NB wordt er in de peilingen een extra politieke partij meegenomen, dan moet deze onderaan toegevoegd worden; aangezien het ook hier een tabel betreft, zal Excel de nieuwe regel automatisch in tblPartijen meenemen.

Het is ook wel handig om te weten wat de eerste en laatste datum is, die in het peilingoverzicht voorkomt.
In cel E2 van het tabblad Param uit het Voorbeeldbestand bepalen we de eerste/kleinste datum door het minimum van die kolom op te zoeken.
Normaal gesproken zou dat kunnen met de formule =MIN(tblPeilingWijzer[Datum]), maar helaas levert dat de waarde 0 op. Dat komt omdat in de datum-kolom geen echte datum staat maar een tekst, dus het wordt wat ingewikkelder:
{=MIN(DATUM(
LINKS(tblPeilingWijzer[Datum];4);
DEEL(tblPeilingWijzer[Datum];6;2);
RECHTS(tblPeilingWijzer[Datum];2)))}

Met behulp van de functie LINKS zoeken we het jaar op, DEEL haalt 2 tekens op vanaf positie 6 (de maand dus) en RECHTS levert de dag. Deze 3 resultaten geven we door aan de functie DATUM, die er een datum van maakt. De functie MIN zoekt dan de kleinste datum op.

LET OP de 3 tekst-functies kijken eigenlijk alleen naar de datum uit de corresponderende rij (de 2e dus). Door na het intikken van de formule niet op Enter te drukken maar op Ctrl-Shift-Enter wordt het een zogenaamde array- of matrix-formule en worden de 3 tekst-functies op alle datums ‘losgelaten’. Vaak wordt dit ook CSE-invoer genoemd; Excel plaatst automatisch accolades om de formule (niet handmatig intypen!).

De formule in E3 mag dan geen verrassingen meer bevatten.

Voor het gemak hebben we de cellen E2 en E3 een naam gegeven MinDatum, resp. MaxDatum.

Grafiek met onzekerheidsmarge

Voordat we een dergelijke grafiek kunnen maken moeten we eerst een methode hebben om de gegevens van één partij uit de basis te destilleren.

Cel C3 (met de naam invPartij) in het tabblad Ovz van het Voorbeeldbestand gebruiken we als keuzevak:

1. kies in de menutab Gegevens de optie Gegevensvalidatie
2. in het pop-up-scherm kiezen we bij Toestaan: de optie Lijst
3. bij Bron: zouden we alle keuzemogelijkheden kunnen intikken gescheiden door een ; (punt-komma), dus bijvoorbeeld VVD;PvdA;GL etc.
   Maar dat is niet zo handig. Die lijst kunnen we ook uit het tabblad Param halen: kolom Partijen in de tabel tblPartijen.
   Helaas kan gegevensvalidatie niet goed omgaan met de nieuwere tabellen (geïntroduceerd in versie 2007), dus de formule =tblPartijen([Partijen]) werkt niet, maar wel als we de functie INDIRECT gebruiken (zie ook het artikel Tabellen (deel 2); denk aan de aanhalingstekens!!).
4. Eventueel nog een Invoerbericht en/of Foutmelding toevoegen (zie Voorbeeldbestand) en we zijn klaar: klik op OK.

De voorbereidingen zijn klaar, nu nog de gegevens ophalen van de gekozen partij:

1. in rij 5 van het tabblad Ovz van het Voorbeeldbestand staat een kopregel
2. vanaf B6 naar beneden staan alle datums, waarvoor er peilingen zijn
3. in cel C6 moet de eerste peiling van de gekozen partij komen:
   =INDEX(
   INDIRECT(“tblPeilingWijzer[“&invPartij&”]”);
   VERGELIJKEN($B6;tblPeilingWijzer[Datum];0))
   Weet u niet (meer) hoe de functie INDEX werkt? Kijk in het artikel Zoeken: Index en Vergelijken; daar vindt u ook een truc hoe u de de functie makkelijk implementeert.
   Weer gebruiken we INDIRECT om de kolom, waarin gezocht moet worden, afhankelijk te maken van de cel C3 (met de naam invPartij).
4. de formules in D6 en E6 zijn vergelijkbaar, alleen worden daar de partij-gegevens uit de low- en high-kolom opgehaald.
5. de drie formules uit C6:E6 worden naar beneden gekopieerd, zodat bij iedere datum de gegevens tevoorschijn komen.
6. via de menutab Invoegen en de optie Tabel maken we van dit overzicht een tabel (met de naam tblGrafBasis). Wanneer er nieuwe peilingen beschikbaar zijn is het dan voldoende om onderaan de datum toe te voegen. Alle formules worden dan automatisch door Excel ingevuld.

En nu de grafiek:

1. plaats de cursor ergens in tblGrafBasis
2. in de menutab Invoegen kiest u in het blok Grafieken de optie Overige grafieken.
   In het uitklapmenu nemen we de eerste grafieksoort in het blokje Hoog/Laag/Slot.
   Dit soort grafieken wordt vaak in de financiële wereld gebruikt om een overzicht van de dagkoersen weer te geven: per dag ziet u dan de hoogste, laagste en slotkoers.
   NB Excel beoordeelt automatisch in welke kolom de hoge, lage of slot-waarde (of in ons geval het gemiddelde) staat.
3. Nog even wat opmaak regelen:
   * kies in de nieuwe menutab Hulpmiddelen voor grafieken het tabblad Indeling
   * kies in het blokje Huidige selectie de Reeks Gemiddeld 
   * daarna de optie Selectie opmaken
   * en kies bij Lijnkleur een gewenste kleur
   * kies in het blokje Huidige selectie de Hoog/laag-lijnen
   * daarna de optie Selectie opmaken
   * en kies bij Lijnkleur dezelfde kleur, maar met een Transparantie van 75%
   * kies in het blokje Huidige selectie de Reeks Hoog
   * daarna de optie Selectie opmaken
   * en kies bij Markeringsopties Geen
   * Grafiektitel laten verwijzen naar cel E3
   * aan de linker-as een Titel toevoegen, de notatie wijzigen in een percentage zonder decimalen en zorgen dat er het bereik altijd van 0% tot 30% loopt.

Wijzig cel C3 en bekijk het resultaat!

---