Tagarchief: Slicer

Verkiezingsuitslagen en dynamische draaitabellen



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:

  1. 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
  2. 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.
  3. de kopregel aanpassen, zodat de breedte van de kolommen wat beperkt blijft (via Ctrl-1/Uitlijning)
  4. even sorteren op CBS-code
  5. 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!)
  6. 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:

  1. selecteer een cel in het basis-bestand
  2. kies Invoegen/Draaitabel en klik OK (Excel kiest automatisch als bron de totale tabel tblBasis)
  3. 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
  4. 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):

  1. maak een kopie van het tabblad Ovz1 (‘met de muis vastpakken’ en, met de Ctrl-toets ingedrukt, verslepen)
  2. versleep het veld Provincie van Rijlabels naar het Rapportfilter
  3. sleep het veld Plaats naar Rijlabels (boven Waarden!)
  4. 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:

  1. maak eerst een nieuwe draaitabel aan vergelijkbaar met hiervoor (zie tabblad Ovz3 in het Voorbeeldbestand)
  2. cel E3 (waar een bepaalde provincie kan worden gekozen) heeft de naam Prov gekregen
  3. 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
  4. 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)
  5. 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:

  1. open (zonodig) het Voorbeeldbestand
  2. klik met de rechter muisknop op het tabblad Ovz3
  3. kies de optie Programmacode weergeven
  4. zorg dat (links) in het projectenoverzicht het blad Ovz3 is geselecteerd
  5. plak dan in het code-scherm de bovenstaande code
  6. sluit de VBA-editor
  7. Sla het bestand op als Excel-werkmap met macro’s; dus met de extensie xlsm
  8. wissel nu tussen provincies afzonderlijk en alle provincies

 


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

 

 

Trend-analyse

Ik was deze week nog eens naar Google-analytics aan het kijken hoe het met de bezoekersaantallen van G-Info gaat.

Er zit nog steeds een stijgende lijn in; altijd leuk!

Wel wat vreemde uitschieters:

  • eind april/begin mei 2015 heeft Google problemen gehad en is er niets geturfd
  • juli en aug van ieder jaar vertoont een dip; waarschijnlijk hebben mensen dan wat anders te doen
  • ook bij de dips van eind december kan ik me wat voorstellen
  • bij nadere bestudering blijken alle punten aan de onderkant op zaterdag en zondag te vallen
  • en de pieken naar boven vallen samen met de momenten dat ik een Nieuwsbrief uitstuur met de aankondiging dat er een nieuw artikel op de site is verschenen.

Maar laten we eens kijken hoe we de trend kunnen analyseren.

Trendlijn-1

Op basis van de gegevens van Google heb ik bovenstaande grafiek gemaakt (je kunt de data uit Analytics eenvoudig exporteren naar Excel):

  1. in het Voorbeeldbestand staan op het tabblad Gegevensset de aantallen bezoekers per dag (beter gezegd de aantallen sessies).
    Daar heb ik ‘natuurlijk’ direct een Excel-tabel van gemaakt (zie het artikel over de 10 voordelen van een tabel en Tabellen deel 2). De naam daarvan is Tabel1.
  2. selecteer de gegevens uit de 2 kolommen door de bovenkant van de kolommen aan te wijzen (de cursor is dan een zwart-pijltje naar beneden; de linker-muis-toets ingedrukt houden).
  3. kies in de menubalk de optie Invoegen/Grafieken/Lijn
  4. het resultaat vindt u terug in het tabblad Graf

In de voorbeeldgrafiek heb ik Excel ook een trendlijn laten toevoegen:

  1. klik ergens met de rechtermuisknop op de grafiek en kies de optie Trendlijn toevoegen
  2. laten we het simpel houden en voor een rechte lijn (de optie Lineair) kiezen
  3. vink ook de 2 onderste opties aan: Vergelijking en R-kwadraat

Excel heeft een mooie (stijgende) lijn getekend; de wiskundige formule die daar bij hoort is
y = 0,0956x – 3998,4

Het getal voor de x is de richtingscoëfficiënt (RC): hoeveel verandert de lijn als x (in dit geval de dag) met 1 toeneemt (dus ruwweg iedere 10 dagen komt er 1 bezoeker meer).
Het andere getal (-3998,4; het snijpunt met de y-as) geeft het aantal bezoekers weer als x gelijk is aan 0 (nul).

NB1 Omdat op de x-as datums staan en voor Excel een datum niets anders is dan het aantal dagen na 1-1-1900, zou het aantal bezoekers op die dag-nul dus flink negatief zijn geweest. De lijn naar het verleden doortrekken is dus onzinnig.

NB2 voor de liefhebbers: Excel gebruikt voor het bepalen van de lijn de zogenaamde kleinste-kwadratenmethode.

NB3 R² (R-kwadraat) is de zogenaamde determinatiecoëfficiënt. Deze geeft aan welk gedeelte van de variatie in de ene variabele door de andere wordt verklaard.
Ofwel: hoe ´betrouwbaar´ is de trendlijn. Is de R² kleiner dan 0,5 dan is het verband zwak tot matig, ligt die tussen 0,5 en 0,75 dan is het verband sterk en anders zeer sterk.

Hoeveel bezoekers mag G-Info op basis van deze trendlijn over een jaar verwachten? Als iedere 10 dagen er 1 extra bezoek bij komt dan zouden er over 365 dagen ongeveer 36 meer moeten zijn dan nu; dus ipv gemiddeld 95 zouden er dat ongeveer 130 zijn.
Dit kunnen we ook makkelijk grafisch laten zien:

  1. klik met de rechtermuisknop op de trendlijn in de grafiek
  2. kies Trendlijn opmaken
  3. en bij Voorspelling/Vooruit vullen we 365 in

Richting, Snijpunt, R.kwadraat en Lijnsch

Om in Excel met de trend te kunnen rekenen moeten we de richtingscoëfficiënt en het snijpunt met de y-as exact weten; we hebben niet genoeg aan een formule in de grafiek.

Uiteraard zijn daar functies voor (zie het tabblad Gegevensset in het Voorbeeldbestand):

  1. in cel H2 willen we de RC plaatsen.
    * tik in =Richting(
    * klik op de bovenrand van Sessies in kolom C (de y-waarden)
    * tik in ;
    * klik op de bovenrand van Dagindex in kolom B (de x-waarden)
    * druk op Enter (Excel zal automatisch de sluithaak van de formule toevoegen)
  2. in H3 is op dezelfde manier de functie SNIJPUNT ingevoerd
  3. en in H4 de functie R.KWADRAAT (denk aan de punt na de R)

Voor de liefhebbers: Excel kent nog een andere functie; deze levert meer statistische resultaten op, LIJNSCH.

In het kort (zie het tabblad Gegevensset in het Voorbeeldbestand):

  1. selecteer 6 lege cellen, 2 kolommen en 3 regels  (in het voorbeeld G12:H14)
  2. in de eerste cel komt de volgende formule:
    =LIJNSCH(Tabel1[Sessies];Tabel1[Dagindex];WAAR;WAAR)
    De y- en x-waarden zijn de bekende kolommen uit de tabel met Google-data.
  3. druk nu niet op Enter, maar Ctrl-Shift-Enter (de zogenaamde CSE-invoer)

De betekenis van de 6 cellen heb ik in de kolommen er naast aangegeven; de cellen G12:H14 hebben corresponderende namen gekregen, zodat formules die er naar verwijzen beter leesbaar zijn.

NB De 2 ‘boven’ de R krijg je als volgt: selecteer in de formulebalk de 2, klik Ctrl-1 (de sneltoets voor Celeigenschappen) en kies de optie Superscript. De m, b en y achter SD hebben de eigenschap Subscript gekregen.

LET OP Lijnsch is een zogenaamde array- of matrix-functie. Het resultaat bestaat niet uit 1 waarde maar uit meerdere, vandaar dat die met CSE wordt ingevoerd. Alle cellen bevatten dezelfde functie, omgeven door accolades.

NB Lijnsch levert meer dan 6 resultaten terug; probeer zelf maar uit door in de eerste stap meer kolommen en regels te selecteren.

De resultaten van bovenstaande functies kunnen we nu gebruiken om voorspellingen voor de toekomst te genereren (zie het tabblad Gegevensset in het Voorbeeldbestand).

Trendlijn-2

Omdat de bezoekersaantallen in de weekenden heel anders zijn dan op werkdagen, heb ik voor een nadere analyse op het tabblad Gegevensset in het Voorbeeldbestand nog een kolom toegevoegd:
=KIEZEN(WEEKDAG([@Dagindex];2);”ma”;”di”;”wo”;”do”;”vr”;”za”;”zo”)

Ofwel: bepaal de Weekdag van de corresponderende datum in kolom B (Dagindex; we willen dat de week op maandag begint, vandaar de parameter 2). Aangezien deze functie volgnummers oplevert (een maandag is 1, dinsdag 2 etc), heb ik de nummers nog vertaald in teksten mbv de functie Kiezen.

Op basis van deze tabel heb ik een draaitabel gemaakt (zie het tabblad Draai in het Voorbeeldbestand).
Door het veld Weekdag in het Rapportfilter te plaatsen kunnen we snel een overzicht per afzonderlijke dag maken.
Om ook analyses per jaar, kwartaal of maand te kunnen maken heb ik de datums in het veld Dagindex gegroepeerd (zie Groeperen in een draaitabel).

Nog een paar stappen en we kunnen met de resultaten gaan ‘spelen’:

  1. selecteer een cel in de draaitabel
  2. in de menutab Hulpmiddelen voor draaitabellen kiezen we het tabblad Opties
  3. klik dan op de optie Draaigrafiek in het blok Extra
  4. kies een Lijngrafiek en klik OK
  5. voeg een Trendlijn toe, inclusief Vergelijking en R-kwadraat

Na wat lay-outen en het toevoegen van 2 slicers (zie Slicers in Excel) krijgen we een resultaat zoals weergegeven in het tabblad DrGraf in het Voorbeeldbestand.

LET OP de b in de vergelijking van de trendlijn gebruikt voor x=0 niet meer de datum 1-1-1900, maar de eerste datum in de betreffende draaitabel.

Wanneer we in de Slicer Weekdag alleen de maandag kiezen (klikken op de button ma) dan zien we dat de R² al veel beter wordt (0,6381).

LET OP de m in de vergelijking van de trendlijn geeft nu niet de verandering per dag aan, maar de verandering naar de volgende maandag, dus na 1 week.
Wil je de trendlijn 1 jaar vooruit laten ‘kijken’, tik bij Voorspelling/Vooruit dan ook geen 365, maar 52 in (dus over 1 jaar 160 bezoekers op maandag?).

Wil je het resultaat over alle werkdagen zien:

  1. kies in de Slicer Weekdag de button ma
  2. houd Shift ingedrukt en klik op vr
  3. laat Shift los
  4. de draaitabel, draaigrafiek en trendlijn passen zich automatisch aan

Analyse van de jaren laat zien (gebruik de betreffende Slicer), dat de trendlijn voor 2015 ´betrouwbaarder´ is dan die van 2016. De resultaten van vorig jaar worden zwaar beïnvloed door de dips in vakantie-periodes.


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

Top2000 en draaitabellen

Eind 2016 stond voor menigeen, op muziekgebied, weer in het teken van de jaarlijkse Top-2000; één van de (Nederlandse) tradities naast Kerstmis, Oud en Nieuw, oliebollen etc.

Bovenaan de ranglijst is het normaal gesproken niet echt spannend; de top-3 ligt wel ongeveer vast. De vraag was of de vele in 2016 gestorven pop-helden daar verandering in zou brengen. Niet dus.

Tijd om wat overzichten te maken; uiteraard gebruiken we daar draaitabellen voor!

Basis-materiaal

De Top-2000 wordt uitgezonden door Radio-2; op de website daarvan is dan ook  altijd de rangschikking van het laatste jaar te vinden. Door nog wat verder te googelen heb ik ook de standen vanaf het begin van de Top-2000 kunnen vinden; de eerste uitzending was eind december 1999.

Wat heb ik vastgelegd? Per nummer het jaar van uitzenden, de notering/positie in dat jaar, de naam van de artiest/groep, de titel van het nummer en het jaar dat het nummer gepubliceerd/uitgebracht is (zie het tabblad Basis in het Voorbeeldbestand).

Alle gegevens heb ik in de vorm van een Excel-tabel vastgelegd met de naam tblTop2000 (heb je een aaneengesloten gebied met gevulde cellen, plaats de cursor ergens in dat gebied en kies in de menutab Invoegen de optie Tabel; kies dan in de nieuwe menutab Hulpmiddelen voor tabellen de optie Ontwerpen en wijzig de naam).
Eind 2017 zal de traditie wel worden voortgezet: kopieer de nieuwe gegevens en voeg deze onderaan de tabel toe. Nog even sorteren en alles staat klaar:

  1. plaats de cursor ergens in de tabel
  2. kies in de menutab Gegevens in de groep Sorteren en filteren de optie Sorteren
  3. in het tussenscherm staan de juiste instellingen al klaar:
    Klik dus op OK.

NB hebt u nieuwe data toegevoegd, vergeet dan niet de draaitabellen te Vernieuwen, anders worden de nieuwe gegevens niet meegenomen in de overzichten. Eén draaitabel vernieuwen is voldoende; aangezien alle overzichten aan dezelfde basis zijn gekoppeld worden dan alle draaitabellen bijgewerkt.

Filteren

Om de resultaten van een artiest te bekijken kunnen we de filter-eigenschappen van Excel gebruiken.
Aangezien het basis-materiaal als een tabel is opgeslagen krijgen we filter-opties er ‘gratis’ bij (zie de  achter iedere kolom-kop).
Wanneer we bijvoorbeeld alle noteringen van de Eagles willen weten:

  1. klik op het  achter Artiest
  2. zoals u ziet kunt u daar ook een sortering aanbrengen, maar we zijn nu meer geïnteresseerd in de filtermogelijkheden.
    Excel heeft ontdekt dat de kolom tekst bevat, dus we krijgen tekstfilters aangeboden.
    Onder het invulvak staan standaard alle mogelijkheden waaruit u kunt kiezen; u zou dus naar de Eagles kunnen scrollen (of eerst een E intikken en dan verder scrollen), maar dat is nogal tijdrovend. In plaats daarvan tikken we in het invulvak eagles (het zoeken is niet hoofdletter-gevoelig) en Excel geeft ons alleen die opties, die ergens het woord eagles bevatten.
  3. Ik bedoelde niet de Eagles of Death Metal, dus die vinken we uit en klikken op OK
  4. en we hebben het gewenste overzicht:

    Eventueel nog sorteren op Titel om het verloop in de jaren per nummer makkelijk te kunnen volgen.

Overzicht per artiest

We gaan eens analyseren hoe de diverse artiesten het in de loop van de tijd hebben gedaan:

  1. klik ergens in de tabel tblTop2000 in het tabblad Basis van het Voorbeeldbestand
  2. kies in de menutab Invoegen de optie Draaitabel en klik op OK
  3. sleep de velden UitzendJr, Artiest en Titel naar de gewenste plaats in de tabel zoals hiernaast weergegeven.
    In de rijen zal Excel nu alle (unieke!) artiesten tonen en in de kolommen komen de (12) verschillende uitzendjaren.
    In het waarden-gebied hebben we de Titel geplaatst; Excel weet dat dit een tekst is, dus zal direct het aantal voorkomens van een artiest in een jaar turven.
    NB Hadden we een numeriek-veld in het waarden-gebied geplaatst dan bepaalt Excel standaard de som van die waarden.
  4. kies in de nieuwe menutab Hulpmiddelen voor draaitabellen het tabblad Ontwerpen en dan bij Rapportindeling de optie Tabelweergave
  5. nog even een sortering aanpassen: kies het ‘vinkje’ achter UitzendJr en sorteer van hoog naar laag

Het resultaat is terug te vinden in het tabblad ovzArtiest van het Voorbeeldbestand:

Wil je weten welke 3 nummers van 10CC er in 2016 in de lijst stonden en op welke plaats: dubbelklik op de 3 in cel B5 en er verschijnt een nieuw tabblad met de drie betreffende records.
Dubbelklikken op de 101 zal alle noteringen van 10CC in de loop der jaren laten zien.

Welke artiest/groep heeft de meeste noteringen?

  1. klik ergens in de 2016-kolom, bijvoorbeeld cel B5
  2. kies in de menutab Gegevens in het blok Sorteren en filteren de optie .
  3. wil je de stand in een ander jaar weten? Herhaal de procedure met de cursor in een andere kolom.
  4. Hetzelfde als u de stand over alle jaren wilt weten: plaats de cursor in de laatste kolom en sorteer.

Overzicht per artiest en titel

Met nog een paar aanpassingen aan de draaitabel ontstaat een totaaloverzicht van de hele Top-2000 over alle jaren:

  1. Voeg nogmaals de Titel toe aan de Lijst met draaitabelvelden; deze keer in het Rijlabels-gebied.
    Let op zorg dat de titel ONDER de artiest komt! Probeer maar uit wat het verschil is.
  2. in het waarden-gebied halen we de Titel weg (versleep buiten het keuzegebied) en plaatsen daar de Notering.
    Aangezien dit een numeriek veld is stelt Excel voor om dan de Som daarvan te pakken. Aangezien iedere artiest/titel-combinatie in ieder uitzendjaar maar één keer voorkomt, zal Excel dus ‘gewoon’ de notering laten zien.

Een voorbeeld kunt u vinden in het tabblad ovzArtTitel van het Voorbeeldbestand.

NB uit het voorbeeld hier rechts mag blijken dat in het basis-materiaal de titels niet consistent gevuld zijn. Ook bij de artiesten was dit het geval; deze heb ik handmatig zo goed mogelijk ‘opgeschoond’.

Noteringen in de loop van de tijd

In het tabblad ovzJrVerloop van het Voorbeeldbestand ziet u een andere mogelijkheid om het verloop in de tijd te volgen:
De Artiest hebben we in het Rapportfilter geplaatst.

De titel heeft een ‘vreemde’ sortering gekregen:

  1. plaats de cursor in één van de titels
  2. klik in die cel met de rechter-muisknop en kies binnen de optie Sorteren het item Meer sorteeropties
  3. in het vervolgscherm kiezen we dan een oplopende sortering; maar niet op Titel maar op Som van Notering

NB1 omdat Excel naar het totaal over alle jaren heen kijkt, is de sortering niet vlekkeloos.

NB2 Ook door de inconsistente vulling van de database levert dit niet altijd het verwachte/gewenste resultaat op; kijk bijvoorbeeld wat er gebeurt als je ABBA kiest.

Grafiek

Wanneer we van de draaitabel een grafiek maken levert dit nog meer inzicht op in het verloop van de noteringen in de tijd (zie tabblad ovzJrVerloopGraf):

  1. klik ergens in de draaitabel
  2. kies in de menutab Hulpmiddelen voor draaitabellen de tab Opties
  3. in het blokje Extra vindt u de Draaigrafiek
  4. na de keuze van het gewenste type komt de grafiek op hetzelfde tabblad waar ook de draaitabel staat; kies  en zet de grafiek op een Nieuw blad
  5. aangezien de grafiek aan de draaitabel gekoppeld is kun je hier direct een andere artiest-keuze maken. Deze keuze wordt dan direct ook in de gekoppelde draaitabel toegepast, inclusief sorteringen.

Publicatiejaar

Ik was ook geïnteresseerd naar het ‘nostalgische’ karakter van de Top2000; ofwel uit welke jaren komen de meeste nummers?

Uit de grafiek over het uitzendjaar 1999 blijkt dat toen het zwaartepunt lag bij muziek uit de eind-60- en eind-70-jaren.
Wanneer we dit vergelijken met de grafiek over 2016 dan zien we dat er een veel gelijkmatigere verdeling over de tijd is! De Top-2000 is niet alleen meer voor ‘ouwe knarren’?

 

Hoe is deze grafiek gemaakt?

  1. de basis is weer een draaitabel.
    In het Rapportfilter is het UitzendJr geplaatst, waarmee we de hoofdselectie maken.
    Het PublJr plaatsen we in de rijen en we turven het aantal door de Titel in het waarden-gebied te laten ‘vallen’.
  2. daarna is van deze draaitabel een draaigrafiek gemaakt; klaar!

In het tabblad ovzPublJr van het Voorbeeldbestand is het resultaat terug te vinden.

Slicers

Om de keuze van het uitzendjaar makkelijker te kunnen maken is een slicer toegevoegd aan de draaitabel:

  1. klik ergens in de draaitabel
  2. kies in de menutab Hulpmiddelen voor draaitabellen de tab Opties
  3. kies in het blokje Sorteren en filteren de optie Slicer invoegen
  4. in het vervolgscherm kiezen we in dit geval de optie UitzendJr
  5. regel nog wat opmaak via de Opties in Hulpmiddelen voor Slicers

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

Slicers in Excel

SlicerEén van de weinige woorden, die in de Nederlandstalige versie van Excel niet zijn vertaald, is Slicer.
Ik merk in de praktijk, dat het niet voor iedereen meteen duidelijk is, wat er in dit geval mee wordt bedoeld. In ieder geval niet zo’n machine zoals hiernaast afgebeeld!

De functionaliteit Slicer vinden we sinds versie 2010 terug bij het onderdeel Draaitabellen en is bedoeld om sneller, makkelijker en overzichtelijker selecties te maken/filters te definiëren.

In dit artikel zal ik de meest gangbare toepassingen toelichten. Hoewel er een directe koppeling tussen een Slicer en een Draaitabel bestaat, zullen we zien, dat met een klein beetje VBA de inhoud van de slicers ook op andere plaatsen en op andere manieren gebruikt kan worden.

LET OP Slicers worden door Excel voor de Mac niet ondersteund; bijgaand Voorbeeldbestand kunt u op de Mac dan ook beter niet opstarten.

Basisgegevens

Slicer1Om het gebruik van een Slicer toe te lichten hebben we basisgegevens nodig aan de hand waarvan we een draaitabel kunnen maken.
In het Voorbeeldbestand zien we in het tabblad Basis een fictief omzet-overzicht, gesplitst naar Jaar, Maand, Regio en Soort product.
Slicer2De gegevens zijn opgeslagen in de vorm van een tabel met behulp van de optie Invoegen/Tabel. Excel kent daarbij automatisch de naam Tabel1 aan dit gebied toe.
Een groot voordeel hiervan is, dat als deze tabel wordt uitgebreid met nieuwe regels of kolommen, alle draaitabellen, die hierop gebaseerd zijn, automatisch rekening houden met die uitbreiding. Hetzelfde geldt ook, wanneer er gegevens uit de tabel worden verwijderd: bij het vernieuwen van de draaitabel zul je merken, dat de draaitabel zich daar automatisch aan aanpast.

Slicer3NB1 een ander voordeel van een tabel merk je, wanneer je in het bestand naar beneden scrolt: zonder dat er titels geblokkeerd hoeven te worden, blijven de kopjes van de tabel zichtbaar.

NB2 het voorbeeld is gemaakt door voor alle kolommen willekeurige resultaten te genereren met behulp van de functie ASELECTTUSSEN, eventueel aangevuld met de functie KIEZEN (zie de cellen H3 t/m L3)

Draaitabel

In het basisbestand zijn 100.000 regels/records opgenomen. Met behulp van een draaitabel kunnen deze razendsnel gerubriceerd worden: welke jaren komen voor, zijn alle maanden gevuld, welke regio’s worden gebruikt, welke producten hebben een omzet gegenereerd en wat zijn de bijbehorende bedragen?

Hoe maken we een draaitabel?

  1. Slicer4plaats de cursor ergens in de tabel met basisgegevens, bijvoorbeeld door in cel B4 te klikken
  2. kies in het menutabblad Invoegen in het blok Tabellen de optie Draaitabel
  3. u ziet dan een tussenscherm, waarin vrijwel altijd de voorgestelde keuzes zullen voldoen: Excel heeft de tabel waarin de cursor staat als basis geselecteerd en het resultaat zal in een nieuw werkblad komen
  4. klik op OK
  5. Slicer5er opent zich een nieuw werkblad met rechts een overzicht van alle velden/kolommen uit de geselecteerde tabel.
    Voor ons eerste overzicht vinken we het Jaar, de Regio en de Omzet aan.
    Excel zal proberen te bedenken waar we de gegevens willen hebben. In dit geval gaat hij de Som van de jaren bepalen, maar dat is natuurlijk niet de bedoeling: sleep het blokje “Som van Jaar” van -waarden naar de Kolomlabels en Excel gaat de omzet uitsplitsen naar jaar.

Slicer7“Sneller dan het geluid” zien we dat de verdeling van de omzet in dit geval redelijk evenredig over de regio’s en jaren heeft plaats gevonden (niet vreemd natuurlijk omdat we de gegevens aselect hebben gegenereerd!).

Om de omzet beter te kunnen lezen gaan we de opmaak aanpassen: dit doen we NIET door de opmaak van de cellen B2:E9 aan te passen; als er straks een jaar bijkomt moeten we die kolom dan weer opnieuw opmaken.
Nee, klik met de rechter muisknop op één van de cijfers (bijvoorbeeld cel B5), kies Getalnotatie en binnen de categorie Getal geen decimalen en een scheidingsteken voor duizendtallen.

Filters in Draaitabellen

In het Voorbeeldbestand is op het tabblad Draai1 een ander voorbeeld van een draaitabel gegenereerd: in de rijen is de omzet opgesplitst naar de maanden en in de kolommen naar product.
Slicer8Om een filtering naar Jaar en/of Regio mogelijk te maken zijn die 2 velden in het blok Rapportfilter geplaatst. Deze 2 velden zijn daardoor boven de draaitabel terecht gekomen; door middel van de vinkjes in de cellen B1 en B2 kan dan een jaar of regio geselecteerd worden.

Daarnaast zijn nog de volgende aanpassingen doorgevoerd:

  1. Slicer9via het tabblad Ontwerpen in Hulpmiddelen voor Draaitabellen hebben we binnen Rapportindeling de Tabelweergave gekozen.
    Welke optie het meest geschikt is, hangt zeer sterk af van de inhoud en opzet van de draaitabel; experimenteer hiermee!
    NB Hulpmiddelen voor draaitabellen is alleen zichtbaar wanneer de cursor ergens in de draaitabel staat.
  2. naast de Som van Omzet hebben we ook aantallen en gemiddelde omzet toegevoegd. Om dit te bereiken sleept u de Omzet uit de Lijst met draaitabelvelden opnieuw naar het waarden-gebied.
    Excel zal dan opnieuw de som bepalen; om dit te veranderen moet u in de draaitabel rechts klikken op een cel, die u wilt wijzigen; kies de optie Waarden samenvatten per en kies daar Aantal of Gemiddelde

Compacte draaitabellen

Slicer10Eén van de nadelen van een standaard-draaitabel is dat de kolommen nogal breed worden doordat Excel namen genereert als Som van Omzet etc.

Gelukkig is dit snel aan te passen: in het Voorbeeldbestand is in het tabblad Draai2 de kop in cel B6 aangepast door in die cel nieuwe tekst in te tikken (in dit geval Aantal).

LET OP u zult merken, dat cel C6 zich niet laat veranderen in Omzet; Excel weet dan niet meer of u nu het veld uit de basisgegevens bedoelt of de kolom in de draaitabel. In dit geval hebben we dat opgelost door een spatie achter het woord Omzet te plaatsen! Had er natuurlijk ook vóór mogen staan.

Slicers

Om het filteren (zoals in het plaatje hierboven voor Jaar en Regio) makkelijker te maken is in Excel de optie Slicer ontwikkeld.

Hoe maakt u een slicer?

  1. Activeer de draaitabel, waarvoor een slicer gemaakt moet worden. Klik daartoe met de muis op één van de cellen van de draaitabel.
  2. in het tabblad Hulpmiddelen voor draaitabellen, dat dan beschikbaar komt, kiest u Opties
  3. in het blok Sorteren en filteren kiest u de button Slicer invoegen
  4. alle velden uit de brongegegevens komen in aanmerking; in dit voorbeeld vinken we alleen Jaar en Regio aan. Klik op OK.
  5. Standaard worden de slicers verticaal weergegeven: alle opties onder elkaar.
    Omdat nu de slicers geselecteerd zijn, ziet u bovenaan Hulpmiddelen voor slicers. Kies daar eventueel een andere Slicerstijl en pas het aantal gewenste Kolommen aan (het aantal opties naast elkaar)


Het filteren doet u nu door de betreffende button aan te klikken.
Meerdere selecties binnen een categorie nodig? Hou Ctrl ingedrukt en klik op de gewenste buttons.
Alle items nodig? Klik op Slicer12 .

In het tabblad Draai3 van het Voorbeeldbestand ziet u, dat de velden Jaar en Regio in het Rapportfilter niet meer nodig zijn; deze zijn vervangen door de slicers.

LET OP zorg bij het printen van de draaitabel, dat ook de slicers zichtbaar zijn, anders is niet duidelijk welke filtering is toegepast.
In het tabblad Draai3 is een draaigrafiek weergegeven (in dit geval een weinig-zeggende, maar voor het voorbeeld is de inhoud niet relevant); deze is gebaseerd op de onderliggende draaitabel en dus ook op de slicers. Wanneer deze grafiek apart geprint zou worden is geen enkele informatie over een eventuele filtering zichtbaar.

Inhoud slicer

Om te voorkomen, dat bij bovenstaand probleem er iedere keer handmatig een tekst aan het overzicht of de grafiek moet worden toegevoegd, zou een dynamische referentie naar de inhoud van de slicers mooi zijn. Dit zou dan in een tekst kunnen worden opgenomen.

Helaas kent Excel deze mogelijkheid (nog) niet; er is een interne koppeling tussen de inhoud van de slicer en de filtering in de draaitabel, maar die is niet standaard met een functie uit te lezen.

slicer13Op internet heb ik echter een UDF (User Definied Function) gevonden op de site jkp-ads.com waarmee dit wel mogelijk is. Zo’n UDF wordt in een module van VBA vastgelegd (zie hieronder).

In het tabblad Draai4 van het Voorbeeldbestand is deze toegepast om de tekst in cel B7 op te kunnen bouwen:
=”Omzet voor ” & ALS(I2=”Alles”;”alle jaren”;I2) & ” en ” & ALS(J2=”Alles”;”alle regio’s”;J2)

Met behulp van het &-teken worden teksten aan elkaar gekoppeld: allereerst de tekst Omzet voor, dan (afhankelijk van de inhoud van cel I2) de tekst alle jaren of de inhoud van I2 etc.

Maar in I2 en J2 staat toch niets? Op het oog niet nee, maar de tekstkleur van die cellen is op Wit ingesteld!

Laten we I2 eens bekijken:
=GetSelectedSlicerItems(“Slicer_Jaar1”)

Hier wordt de UDF GetSelectedSlicerItems aangeroepen met de parameter Slicer_jaar1. Dit is de naam die we terugvinden als we rechtsklikken op de eerste slicer en dan de Slicerinstellingen bekijken.

slicer14

VBA gebruikt de omschrijving zoals vermeld achter Naam om in formules te gebruiken.

Grafiektitel

Dezelfde tekst die boven de draaitabel staat, willen we ook als grafiektitel:

  1. klik op de grafiek
  2. kies in Hulpmiddelen van Draaigrafieken, die dan zichtbaar wordt, de optie Indeling
  3. kies in het blok Labels, de optie Grafiektitel en kies Boven grafiek
  4. tik dan direct in de formulebalk ( dus achter de FunctieInvoeren) de formule: =Draai4!$B$7 of tik in = en klik met de muis op de betreffende cel
  5. druk de Enter-toets in

VBA

Deze keer geen uitleg van de VBA-routine; ik denk dat hij voor een beginnende VBA’er goed te volgen zal zijn.

Maar hoe voegt u een routine, die u ergens hebt gevonden (op internet, in een andere Excel-toepassing) aan uw eigen werkmap toe?

  1. kopieer de routine, die hebt gevonden (bijvoorbeeld met behulp van Ctrl-C)
  2. ga naar de werkmap waar u de routine wilt hebben
  3. klik Alt-F11, de Visual Basis Editor opent dan
  4. klik in de menu-tabs op Invoegen en dan Module
  5. in de zo nieuw gemaakte module plakt u de routine uit het  de eerste stap (bijvoorbeeld met behulp van Ctrl-V)

De UDF GetSelectedSlicerItems kan de volgende resultaten opleveren:

  1. Alles, als er geen filter is gedefinieerd
  2. Niets, als er door andere filtering voor deze slicer geen mogelijkheden zijn
  3. Geen slicer gevonden, als de tekst die als parameter wordt meegegeven geen bestaande slicer is
  4. het gekozen filter of bij meervoudige filtering de keuzes, gescheiden door een komma

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