Tagarchief: Kiezen

Trend-analyse

9 februari 2017ExcelCSE, Draaigrafiek, Draaitabel, Kiezen, Lijnsch, R.Kwadraat, Richting, Slicer, Snijpunt, Tabel, Trendlijn, WeekdagGijs Verbruggen

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):

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.
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).
kies in de menubalk de optie Invoegen/Grafieken/Lijn
het resultaat vindt u terug in het tabblad Graf

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

klik ergens met de rechtermuisknop op de grafiek en kies de optie Trendlijn toevoegen
laten we het simpel houden en voor een rechte lijn (de optie Lineair) kiezen
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:

klik met de rechtermuisknop op de trendlijn in de grafiek
kies Trendlijn opmaken
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):

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)
in H3 is op dezelfde manier de functie SNIJPUNT ingevoerd
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):

selecteer 6 lege cellen, 2 kolommen en 3 regels (in het voorbeeld G12:H14)
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.
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’:

selecteer een cel in de draaitabel
in de menutab Hulpmiddelen voor draaitabellen kiezen we het tabblad Opties
klik dan op de optie Draaigrafiek in het blok Extra
kies een Lijngrafiek en klik OK
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:

kies in de Slicer Weekdag de button ma
houd Shift ingedrukt en klik op vr
laat Shift los
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.

Weekdagen en weeknummers

18 januari 2016Access, ExcelDatePart, Format, Gegevensvalidatie, Kiezen, Notatie, Opmaak, VERT.ZOEKEN, Weekdag, WeeknummerGijs Verbruggen

Eén van de eerste artikelen op de site van G-Info ging over datums en de mogelijkheden van de diverse opmaak-opties (klik hier voor het betreffende artikel).

Vorige week kreeg ik een vraag over weekdagen en weeknummers in Access. Toen bleek, dat het probleem niet met opmaak was op te lossen, viel het me weer op, hoe ingewikkeld het kan zijn om dit soort vraagstukken met behulp van formules te tackelen.
Ook de (soms) grote verschillen tussen Excel en Access maken het er niet makkelijker op (hoezo, allebei een onderdeel van Microsoft Office!) .

Daarom maar eens wat mogelijkheden (en onmogelijkheden) op een rijtje gezet.

Opmaak in Excel

Zoals gezegd heb ik daar in een eerder artikel al eens over geschreven.
In het Voorbeeldbestand heb ik een overzicht gemaakt met de dagen van de laatste week van 2016 en de eerste 2 van 2016.
De datums staan in kolom F; in de kolommen G t/m J staan verwijzingen naar de dagen in kolom F, maar met behulp van celopmaak (Ctrl-1 indrukken) is de lay-out van de cellen veranderd.

Voordeel hiervan is, dat makkelijk en snel de opmaak aangepast kan worden aan je wensen.
Nadeel is echter, dat je niet rechtstreeks formules of filters op deze cellen kunt los laten; de inhoud van die cellen blijft een datum (intern voor Excel een getal). Ook is het niet mogelijk om op deze cellen een Voorwaardelijke opmaak toe te passen.

NB wanneer je een Filter aanbrengt op het overzicht zul je zien, dat de filtermogelijkheden voor de kolommen F t/m J allemaal hetzelfde zijn: Excel ziet, dat het datums betreft en geeft daarom de mogelijkheden om te filteren op jaar, maand of dag. Het is NIET mogelijk om te filteren op bijvoorbeeld maandag.

Experimenteer met de aangepaste opmaak via Celeigenschappen (Ctrl-1). Gebruik combinaties van de letters d, m en j; 1 letter, 2 letters etc. Het resultaat zie je direct in het vak Voorbeeld.

Datum-formules in Excel

Wil je kunnen rekenen, filteren, sorteren met de diverse onderdelen van een datum dan voldoet de opmaak niet meer. Je zult dan formules moeten gebruiken, waarmee de datum ‘ontleed’ wordt.

In het Voorbeeldbestand zijn in de kolommen K, L en M formules gebruikt om het jaar, de maand en de dag van een datum op te halen; in cel K4 bijvoorbeeld staat de formule =JAAR(F4).

Niets bijzonders, handig, recht-toe-recht-aan.
Willen we echter de weekdag ophalen (is het een maandag, dinsdag?) , dan blijkt er in Excel geen functie te zijn, die dat rechtsreeks oplevert; wel kunnen we het volgnummer van de dag in de week opvragen met de functie Weekdag.
Daar zit echter een addertje onder het gras: er is in de wereld geen overeenstemming over welk nummer bijvoorbeeld de maandag moet krijgen: 1 zoals in Europa gebruikelijk is of moet het 2 zijn (een nieuwe week begint op zondag)?
Aan de functie Weekdag moet dus altijd kenbaar worden gemaakt wat de eerste dag van de week is (als je dat niet meegeeft, dan wordt de standaard genomen en krijgt zondag volgnummer 1).
In cel N4 staat de formule =WEEKDAG(F4;KzWkDag)

Er wordt dus gezocht naar de weekdag, die hoort bij de datum in cel F4, waarbij als Type_getal de waarde 2 wordt meegegeven (maandag krijgt volgnummer 1).
Type_getal verwijst naar de cel met de naam KzWkDag (cel C3). Via Gegevensvalidatie kunnen daar alleen de voor deze functie toegestane waarden 1, 2, 3 en 11 t/m 17 ingevoerd worden.
Met VERT.ZOEKEN is in cel D3 te zien wat een bepaalde waarde inhoudt. Op het tabblad Parameters is de bron voor de Gegevensvalidatie en het verticaal zoeken te vinden in de kolommen B en C.

NB met een formule kunnen we dus het volgnummer van een weekdag afleiden, willen we echter een omschrijving dan wordt het iets ingewikkelder.
In cel Q4 staat een voorbeeld:
=KIEZEN(N4;”ma”;”di”;”wo”;”do”;”vr”;”za”;”zo”)
Let wel op: de volgorde van de dagen is afhankelijk van het Type_getal, dat in cel N4 is gebruikt!

Met opmaak is het niet mogelijk om in Excel het weeknummer te zien; wel is daar een functie voor. In het Voorbeeldbestand wordt die in kolom O gebruikt.
In cel O4 komt dan de formule =WEEKNUMMER(F4;KzWkNr)

Ook deze functie kent een 2e paramater (het Type_resultaat). Welke keuze hier gemaakt moet worden is nog iets ingewikkelder dan bij Weekdag. Want niet alleen is het van belang op welke dag een nieuwe week begint, ook moeten we aangeven wanneer we met weeknummer 1 beginnen.
Voor dat laatste onderscheidt Excel 2 systemen: bij Systeem 1 is Nieuwjaar het begin van week 1, bij Systeem 2 (het Europese systeem) begint week 1 met die week, waarin de eerste donderdag valt (en de week begint dan automatisch op maandag).

Om Systeem 2 te kiezen moet als 2e paramater voor Weeknummer het getal 21 meegegeven worden.

Opmaak in Access

Net als in Excel kunnen we in Access de datum “in stukken knippen” door een opmaak daaraan mee te geven.
In een query, die gebaseerd is op een tabel met een veld Datum, kunnen we bijvoorbeeld een nieuw veld toevoegen (met de naam JrF) om het jaar af te leiden:
JrF: Format([datum];”yyyy”)

LET OP Access verwacht Engelse functienamen en ook de Engelse afkorting voor het jaar.

We kunnen ook minder letters aan de functie meegeven, maar het resultaat is soms iets anders dan in Excel:
y: dit levert het volgnummer van de dag in het jaar
yy: het jaar in 2 cijfers
yyy: het jaar in 2 cijfers en direct daarachter het volgnummer van de dag in het jaar (dus een combinatie van yy en y)

Voor het maandnummer kan een vergelijkbare formule gebruikt worden:
MndF: Format([datum];”m”)
mm: het maandnummer altijd in 2 cijfers
mmm: de afkorting voor de maand (bijvoorbeeld jan)
mmmm: een volledige maand-aanduiding (bijvoorbeeld januari)

Voor de dag van de maand gebruiken we dan:
DagF: Format([datum];”d”)
dd: het dagnummer altijd in 2 cijfers
ddd: de afkorting voor de dag van de week (bijvoorbeeld ma)
dddd: een volledige dag-aanduiding (bijvoorbeeld maandag)

Binnen Access is het op deze manier ook mogelijk om het kwartaalnummer automatisch te genereren:
KwF: Format([datum];”q”)

Ook kunnen we de w als parameter gebruiken, maar ook nu dienen we nog extra parameters mee te geven:
WkF: Format([datum];”w”;2)
Deze formule levert het volgnummer van de dag in de week op. Maar net als in Excel moeten we dan wel opgeven op welke dag een nieuwe week begint. Geven we als 3e parameter een 0 (nul) mee, dan worden systeeminstellingen gebruikt, 1 dan begint de week op zondag etc.

Als laatste nog:
Wk2F: Format([datum];”ww”;2;2)
Dit levert het weeknummer in het jaar; net als in Excel is het wel van belang welk systeem daarbij gebruikt moet worden.
Als de 4e parameter 0 (nul) is dan worden de systeeminstellingen gebruikt, 1 dan begint de eerste week op Nieuwjaar, 2 dan is de eerste week afhankelijk van de eerste donderdag en 3 dan is de eerste week die week, die volledig in het nieuwe jaar ligt.

LET OP de functie Format retourneert een tekst; wil je rekenen met de uitkomst hiervan neem dan de waarde van het resultaat: Val(Format([datum];”d”))

Datum-formules in Access

Willen we direct rekenen met de dagen, maanden, weken etc (zonder gebruik te maken van de Val-functie) dan kent Access nog de functie DatePart.

Als we bijvoorbeeld in een query als veld opgeven
JrNr: DatePart([datum];”yyyy”)
dan resulteert dit in een getal met 4 cijfers, dat het jaar aangeeft.

Alle instellingen, die hiervoor bij Access-opmaak zijn toegelicht, zijn hier toepasbaar.
Dus bijvoorbeeld DatePart([datum];”ww”;1;2) levert het weeknummer (als getal) op, waarbij een week begint op zondag en de eerste donderdag bepalend is voor de weeknummer 1.

Slicers in Excel

21 december 2015Excel, VBA (Visual Basic for Applications)Aselect, Draaigrafiek, Draaitabel, Kiezen, SlicerGijs Verbruggen

Eé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

Om 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.
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.
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.

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.

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?

plaats de cursor ergens in de tabel met basisgegevens, bijvoorbeeld door in cel B4 te klikken
kies in het menutabblad Invoegen in het blok Tabellen de optie Draaitabel
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
klik op OK
er 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.

“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.
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.

Daarnaast zijn nog de volgende aanpassingen doorgevoerd:

via 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.
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

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.

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?

Activeer de draaitabel, waarvoor een slicer gemaakt moet worden. Klik daartoe met de muis op één van de cellen van de draaitabel.
in het tabblad Hulpmiddelen voor draaitabellen, dat dan beschikbaar komt, kiest u Opties
in het blok Sorteren en filteren kiest u de button Slicer invoegen
alle velden uit de brongegegevens komen in aanmerking; in dit voorbeeld vinken we alleen Jaar en Regio aan. Klik op OK.
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 .

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.

Op 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.

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:

klik op de grafiek
kies in Hulpmiddelen van Draaigrafieken, die dan zichtbaar wordt, de optie Indeling
kies in het blok Labels, de optie Grafiektitel en kies Boven grafiek
tik dan direct in de formulebalk ( dus achter de ) de formule: =Draai4!$B$7 of tik in = en klik met de muis op de betreffende cel
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?

kopieer de routine, die hebt gevonden (bijvoorbeeld met behulp van Ctrl-C)
ga naar de werkmap waar u de routine wilt hebben
klik Alt-F11, de Visual Basis Editor opent dan
klik in de menu-tabs op Invoegen en dan Module
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:

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