Deze week las ik een artikel over dynamische draaitabellen gemaakt met PowerPivot (een invoegtoepassing waarmee u krachtige gegevensanalyses in Excel kunt uitvoeren).
De draaitabel liet een overzicht zien, gesplitst naar landen. Werd er echter (met behulp van een slicer) één land geselecteerd, dan veranderde de draaitabel automatisch in een overzicht, gesplitst naar steden.
In PowerPivot kunnen dit soort zaken geïmplementeerd worden met behulp van een eigen programmeertaal.
De uitdaging was om te kijken of dit ook ‘gewoon’ in Excel geregeld kan worden. Ik dacht de verkiezingsuitslagen van vorige week mooi als voorbeeld te kunnen gebruiken (overzicht per provincie verandert automatisch in een overzicht per gemeente), maar helaas . Omdat de uitslagen van 2017 nog niet (op detail-niveau) bekend zijn heb ik de data van 2012 gebruikt. Met een paar kleine aanpassingen kunnen de resultaten van 2017 ingekopieerd worden en kunnen er dan ook vergelijkingen tussen de jaren gemaakt worden. Dat doen we later nog een keer; beloofd is beloofd!
Basis
De gegevens heb ik kunnen downloaden van www.verkiezingsuitslagen.nl in de vorm van een CSV-bestand (Comma Seperated Values). Zie het tabblad Uitslagen 2e Kamer 2012 in het Voorbeeldbestand.
Zoals te zien is, krijg je een tekstbestand in Excel, dat nog wat aanpassingen verdient:
- allereerst moeten de teksten over verschillende kolommen verdeeld worden:
* selecteer alle cellen in kolom A, die omgezet moeten worden (A1 t/mA443)
* kies in de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Tekst naar kolommen
* aangezien de kolommen geen standaardbreedte hebben maar door een komma van elkaar zijn gescheiden, kiezen we in het vervolgscherm de optie Gescheiden en klikken op Volgende
* zorg dat in het volgende scherm de optie Komma staat aangevinkt en klik op Voltooien - even wat regels over de totalen per partij verwijderen (de eerste 22), de gegevens in de vorm van een tabel gieten (kies in de menutab Invoegen de optie Tabel) en de onderste (dreigende) regels verwijderen.
- de kopregel aanpassen, zodat de breedte van de kolommen wat beperkt blijft (via Ctrl-1/Uitlijning)
- even sorteren op CBS-code
- dan blijken veel plaatsen al per provincie bij elkaar te staan: een kolom Provincie toevoegen, aan ieder blok een Provincienaam toevoegen en dan ‘met de hand’ nog wat correcties doorvoeren (wat de systematiek van het CBS voor de codes is, is me niet helemaal duidelijk!)
- zo krijgen we een overzicht van de stemmen per gemeente (zie tabblad Uitslagen in het Voorbeeldbestand)
Verkiezingsuitslagen
In dit artikel gaan we alleen wat doen met de partij-uitslagen, dus allerlei kolommen over geldige stemmen enzovoorts kunnen weg.
Om de overzichten zo flexibel mogelijk te kunnen maken moet de bron nog in een zogenaamde database-structuur worden gegoten: niet alle partijen naast elkaar, maar per plaats een aparte regel voor iedere partij met daarbij het aantal stemmen.
Op deze manier ontstaat een basis-bestand met 8.778 regels; voor een mens niet zo handig maar voor Excel geen punt!
Zie het tabblad Basis in het Voorbeeldbestand.
Overzicht per provincie
Allereerst gaan we een overzicht per provincie maken:
- selecteer een cel in het basis-bestand
- kies Invoegen/Draaitabel en klik OK (Excel kiest automatisch als bron de totale tabel tblBasis)
op het nieuwe werkblad gaan we de draaitabel vormgeven:
* sleep het veld Partij naar Kolomlabels
* sleep het veld Provincie naar Rijlabels
* sleep Aantal naar het Waarde-gebied
* sleep nogmaals Aantal naar het Waarde-gebied
* zorg dat Waarden in de Rijlabels staat
* klik rechts op een van de cellen in een regel met Som van Aantal2, kies Waardeveldinstellingen en dan bij Waarden weergeven als de optie % van rijtotaal
* wijzig één van de namen Som van Aantal2 in Perc- en nog een sortering aanbrengen:
* klik met de muis rechts op één van de partijen
* kies Sorteren en dan de optie Meer sorteeropties
* in het vervolgvenster kiest u Aflopend en in het bijbehorende keuzeveld de optie Som van Aantal (of Perc; dat maakt in dit geval niets uit). Op deze manier zullen de partijen altijd gesorteerd worden van meeste stemmen naar minste; ook als de draaitabel van inhoud verandert.
Zo ontstaat het overzicht zoals weergegeven in het tabblad Ovz1 van het Voorbeeldbestand.
Overzicht per gemeente
Als we nu willen weten hoe bijvoorbeeld de uitslag van de provincie Groningen tot stand is gekomen, moeten we de draaitabel aanpassen (of een nieuwe maken):
- maak een kopie van het tabblad Ovz1 (‘met de muis vastpakken’ en, met de Ctrl-toets ingedrukt, verslepen)
- versleep het veld Provincie van Rijlabels naar het Rapportfilter
- sleep het veld Plaats naar Rijlabels (boven Waarden!)
- selecteer bij Provincie de keuze Groningen (in het tabblad Ovz2 is dat cel C2)
Dynamische draaitabel
Maar zou het niet mooi zijn, als de draaitabel eerst een overzicht van de provincies vertoont, maar dat deze automatisch de plaatsen laat zien als we slechts 1 provincie kiezen?
Om dit voor elkaar te krijgen, moeten we de volgende stappen doorlopen:
maak eerst een nieuwe draaitabel aan vergelijkbaar met hiervoor (zie tabblad Ovz3 in het Voorbeeldbestand)- cel E3 (waar een bepaalde provincie kan worden gekozen) heeft de naam Prov gekregen
- aan het basisbestand is een kolom PrPl toegevoegd met de volgende formule:
=ALS(LINKS(Prov;4)=”(All”;[@Provincie];[@Plaats])
Dus als in E3 de optie alle provincies is gekozen, dan komt er in de toegevoegde kolom de Provincie te staan, anders de Plaats.
NB1 @Provincie betekent in een tabel: haal de inhoud op uit de kolom Provincie in dezelfde regel
NB2 we controleren of de eerste 4 karakters van de cel Prov gelijk is aan (All, omdat Excel soms (Alles), soms (Alle) en in de Engelstalige versie (All) laat zien - sleep nu het veld PrPl naar de Rijlabels van de draaitabel in Ovz3 (eerst even de draaitabel Vernieuwen, omdat anders de nieuwe kolom nog niet in de bron is opgenomen)
- voeg nog een slicer toe voor de Provincie (zie het artikel Slicers in Excel)
Wanneer we nu ‘spelen’ met de opties in de cel Prov (of met de slicer) zien we, dat het systeem nog niet vlekkeloos werkt: bij het veranderen van 1 naar alle provincies of andersom moet de draaitabel eerst vernieuwd worden, voordat de gewenste resultaten zichtbaar zijn. Dit komt omdat dan wel de kolom PrPl in de bron wordt aangepast, maar de draaitabel nog de oorspronkelijke inhoud in het geheugen heeft.
Met VBA kunnen we dit automatiseren.
NB Vernieuwen van een draaitabel kan door ergens rechts te klikken in een draaitabel en dan Vernieuwen te kiezen of (als een cel van een draaitabel is geselecteerd) op Alt-F5 te drukken.
VBA
Met de volgende code is het actualiseren van de draaitabel te automatiseren:
Public ProvOud
Public Vernieuwen As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
If [Prov] <> ProvOud And Not Vernieuwen Then
Vernieuwen = True
ActiveSheet.PivotTables(“Draaitabel3”).PivotCache.Refresh
End If
ProvOud = [Prov]
Vernieuwen = False
End Sub
Doe nu het volgende:
- open (zonodig) het Voorbeeldbestand
- klik met de rechter muisknop op het tabblad Ovz3
- kies de optie Programmacode weergeven
- zorg dat (links) in het projectenoverzicht het blad Ovz3 is geselecteerd
- plak dan in het code-scherm de bovenstaande code
- sluit de VBA-editor
- Sla het bestand op als Excel-werkmap met macro’s; dus met de extensie xlsm
- wissel nu tussen provincies afzonderlijk en alle provincies