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:
- rasterlijnen verwijderen
- de titel en legenda verwijderen
- alle Veldknoppen verwijderen
- wijzig het grafiektype in Combinatie, waarbij de overlijdens en de verwachting een lijn worden en de andere 2 een vlak
- wijzig de achtergrondkleur en de kleuren van de items in de grafiek (het vlak van de Ondergrens krijgt dezelfde kleur als de achtergrond)
- 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!):
- klik ergens in de draaitabel (of in de draaigrafiek)
- kies in de menutab Hulpmiddelen in het onderdeel Analyseren in het blok Filter de optie Slicer invoegen
- selecteer het veld (of velden) waarvoor je een slicer wilt
- 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:
- klik in de kop van de slicer; daarmee selecteer je deze.
- in de menutab Hulpmiddelen voor slicers in de Opties kies je de optie Rapportverbindingen
- 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)
- klik op de menutab Bestand
- kies Opties en daarbinnen Geavanceerd
- scrol helemaal naar beneden en daar ziet u onder het kopje Algemeen een button Aangepaste lijsten bewerken
- 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:
- klik met de rechter muisknop op de variant die het meest lijkt op hoe u het wilt hebben.
- kies de optie Dupliceren
- pas de naam aan
- kies een onderdeel (Volledige slicer, Koptekst et cetera) en klik op de button Opmaak
- herhaal het vorige punt voor alle onderdelen die u wilt aanpassen
- 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.
Deze week las ik een artikel over dynamische draaitabellen gemaakt met PowerPivot (een invoegtoepassing waarmee u krachtige gegevensanalyses in Excel kunt uitvoeren).
De gegevens heb ik kunnen downloaden van 
In dit artikel gaan we alleen wat doen met de partij-uitslagen, dus allerlei kolommen over geldige stemmen enzovoorts kunnen weg.
Op deze manier ontstaat een basis-bestand met 8.778 regels; voor een mens niet zo handig maar voor Excel geen punt!
op het nieuwe werkblad gaan we de draaitabel vormgeven:
* 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.
maak eerst een nieuwe draaitabel aan vergelijkbaar met hiervoor (zie tabblad Ovz3 in het 
Op basis van de gegevens van Google heb ik bovenstaande grafiek gemaakt (je kunt de data uit Analytics eenvoudig exporteren naar Excel):
In de voorbeeldgrafiek heb ik Excel ook een trendlijn laten toevoegen:
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.
Uiteraard zijn daar functies voor (zie het tabblad Gegevensset in het 
Voor de liefhebbers: Excel kent nog een andere functie; deze levert meer statistische resultaten op, LIJNSCH.
De resultaten van bovenstaande functies kunnen we nu gebruiken om voorspellingen voor de toekomst te genereren (zie het tabblad Gegevensset in het 
Na wat lay-outen en het toevoegen van 2 slicers (zie 
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).
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.
plaats de cursor ergens in de tabel
Klik dus op OK.
achter iedere kolom-kop).
zoals u ziet kunt u daar ook een sortering aanbrengen, maar we zijn nu meer geïnteresseerd in de filtermogelijkheden.
kies in de menutab Invoegen de optie Draaitabel en klik op OK
kies in de nieuwe menutab Hulpmiddelen voor draaitabellen het tabblad Ontwerpen en dan bij Rapportindeling de optie Tabelweergave
.
Met nog een paar aanpassingen aan de draaitabel ontstaat een totaaloverzicht van de hele Top-2000 over alle jaren:
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’.
De Artiest hebben we in het Rapportfilter geplaatst.
klik in die cel met de rechter-muisknop en kies binnen de optie Sorteren het item Meer sorteeropties
en zet de grafiek op een Nieuw blad
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’?
de basis is weer een draaitabel.
Om de keuze van het uitzendjaar makkelijker te kunnen maken is een slicer toegevoegd aan de draaitabel:
kies in het blokje Sorteren en filteren de optie Slicer invoegen
Eén van de weinige woorden, die in de Nederlandstalige versie van Excel niet zijn vertaald, is Slicer.
Om het gebruik van een Slicer toe te lichten hebben we basisgegevens nodig aan de hand waarvan we een draaitabel kunnen maken.
De 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.
NB1 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.
plaats de cursor ergens in de tabel met basisgegevens, bijvoorbeeld door in cel B4 te klikken
er opent zich een nieuw werkblad met rechts een overzicht van alle velden/kolommen uit de geselecteerde tabel.
“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 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.
via het tabblad Ontwerpen in Hulpmiddelen voor Draaitabellen hebben we binnen Rapportindeling de Tabelweergave gekozen.
Eén van de nadelen van een standaard-draaitabel is dat de kolommen nogal breed worden doordat Excel namen genereert als Som van Omzet etc.
.
Op internet heb ik echter een UDF (User Definied Function) gevonden op de site 
) de formule: =Draai4!$B$7 of tik in = en klik met de muis op de betreffende cel