Categorie archief: Excel

Sneltoetsen in Excel

Excel,
Voorbeeldbestand: Sneltoetsen-2003.xlsxDownload

Ik bedoel niet snel toetsen (als werkwoord dus), nee we gaan het hebben over het gebruik van het toetsenbord bij allerlei dagelijkse handelingen in Excel in plaats van het klikken met de muis.
De Engelstaligen hebben het in dit verband dan over shortcuts.

Lang geleden (bij de komst van versie 2003 van Excel) heb ik eens een overzicht gemaakt van (naar mijn idee) de belangrijkste sneltoetsen. Maar aangezien de dagelijkse praktijk er voor iedereen anders uitziet heb ik het overzicht zodanig in elkaar gezet, dat iedereen voor zichzelf snel de meest bruikbare items er uit kan lichten. Het zal de trouwe lezer van artikelen op deze site niet verbazen, dat ik daarbij gebruik heb gemaakt van een draaitabel.

NB ik heb niet de beschikking over de laatste Excel-versies, maar ik verwacht dat alle of zeker de meeste sneltoetsen ook in latere versies werken.

Belangrijkste sneltoetsen

In het Voorbeeldbestand in het tabblad SnelToets heb ik de naar mijn idee meest relevante sneltoetsen opgenomen.
Naast de gebruikte toetscombinaties in de kolom Toetsen ziet u daar ook een Omschrijving  en een indeling naar Onderdeel.
Om makkelijker overzichten te kunnen maken zijn de gebruikte toetsen ook nog verdeeld over een kolom Hoofdtoets (welke letter of functietoets wordt gebruikt) en een Hulptoets (welke toets moet er nog meer ingedrukt worden; Shift, Ctrl, Alt etcetera).
De laatste kolom geeft aan of de toetscombinaitie bruikbaar is bij MIJN dagelijkse Excel-activiteiten.

NB1 Ctrl + N betekent: hou de Ctrl-toets ingedrukt en druk dan op N

NB2 Windows is de toets met het Windows-‘vlaggetje’, zoals hiernaast; maar iedere hardware-leverancier geeft het anders weer.
De Menu-toets wordt meestal met het volgende symbool weergegeven: 

NB3 probeer de sneltoetsen uit en vul de laatste kolom in naar uw eigen inzicht.

Sneltoetsen aanvullen

Komt u nog nieuwe sneltoetsen tegen, vul deze dan onderaan toe. De gegevens liggen vast in een Excel-tabel (met de naam tblSnelToets); wanneer een nieuwe regel wordt toegevoegd wordt de tabel automatisch uitgebreid.

Overzicht

Om een mooi overzicht te krijgen van (voor mij) relevante sneltoetsen heb ik op basis van de tabel tblSnelToets een draaitabel gemaakt (zie het tabblad Ovz in het Voorbeeldbestand).

Maar ja, dit is hetzelfde wat we al hadden, iets mooier ingedeeld. Wanneer we nu echter alleen de bruikbare items kiezen (selecteer ja in cel C5) dan komt een aan uw wensen aangepast overzicht tevoorschijn. Printen en u hebt een eigen overzicht altijd bij de hand.

NB1 in het Waardengebied van de draaitabel wordt het aantal geturfd; dat is natuurlijk altijd 1, dus die kan ook worden weggelaten.

NB2 hebt u iets gewijzigd aan de basis in het tabblad SnelToets dan moet de draaitabel (via rechts klikken) vernieuwd worden.

Wilt u weten wat u allemaal in combinatie met de Ctrl-toets kunt doen? Kies Ctrl in cel C3.

 

Excel-functies N en T

Excel, , , , , , ,
Voorbeeldbestand: Functie-N-en-T.xlsxDownload

Excel kent 2 functies waarvan het nut niet helemaal duidelijk is: N() en T().
Het lijkt er op, dat het nog restanten zijn uit het verleden. Volgens Microsoft bestaan ze nog “in verband met compatibiliteit met andere spreadsheet-programma’s“.

Allebei de functies kennen maar 1 argument/parameter; de functie N() zet de parameter zo mogelijk om in een getal (Numeriek) en de functie T() levert als resultaat een Tekst, als het argument ook een tekst is.

NB de 2 haakjes in de tekst staan er bewust omdat iedere Excel-functie deze haakjes nodig heeft; bij verreweg de meeste functies moeten tussen de haakjes één of meer argumenten opgegeven worden.

Hieronder zullen we kijken hoe deze functies zich in de ‘normale’ praktijk gedragen, maar ik zal ook laten zien hoe de functies zich goed laten gebruiken in meer creatieve/excentrieke toepassingen.

De functie N()

Zoals al aangegeven zet deze functie het argument om in een getal; tenminste als dat (makkelijk) kan, anders levert de functie de waarde 0 terug; zie het tabblad Vb1 in het Voorbeeldbestand.

Uit de eerste 2 voorbeelden blijkt, dat de N-functie een combinatie van tekst en cijfers NIET kan omzetten naar een getal.
Een geheel getal of een getal met decimalen levert wel de juiste waarde op.
Wanneer de parameter een getal met een decimale punt bevat wordt dit door Excel als tekst gezien (de inhoud van de cel is dan ook automatisch links uitgelijnd) .

In cel B8 staat een formule, die als resultaat een getal oplevert; de N-functie neem deze waarde over.
Een datum is voor Excel een getal (het aantal dagen na 1-1-1900; zie het artikel Data (datums)). De N-functie zet een harde datum (in cel B9) dan ook ‘gewoon’ om naar het betreffende getal en ook het resultaat van een functie (VANDAAG() in cel B10). Ook als de cel een uitgebreide datum-opmaak heeft gekregen (cel B11) levert dit hetzelfde resultaat.

NB Excel heeft de cellen B8:B11 automatisch rechts uitgelijnd; daaraan is al te zien dat hij ze als numerieke waarden interpreteert.

De cellen B12:B14 bevatten teksten (automatisch links uitgelijnd) en de N-functie levert dan ook de waarde 0 terug.
Cel B12 bevat een ‘optelling’ (met het &-teken) van een tekst en een datum (dus een getal) en wordt daardoor vanzelf een tekst: =”Vandaag: “&VANDAAG()
In B13 is gebruik gemaakt van de functie TEKST om de datum een opmaak mee te geven: =”Vandaag: “&TEKST(VANDAAG();”dd-mm-jjjj”)
B14 bevat alleen de TEKST-functie, waardoor het resultaat (uiteraard) direct een tekst wordt: =TEKST(VANDAAG();”dddd d mmmm jjjj”)

De teksten Waar en Onwaar worden door de N-functie vertaald naar 1, respectievelijk 0. Ook als het het resultaat is van een formule (zie cel B17 met de formule =2>1).
De resultaten van formules die een foutwaarde opleveren (bijvoorbeeld =1/0 in cel B18) worden door de N-functie overgenomen.
Wanneer N() een lege parameter meekrijgt (zie cellen B19 en C19) dan wordt het resultaat 0.

NB let op de automatische uitlijning door Excel van de cellen B15:B18.

N-functie in plaats van ALS

De N-functie kan ook goed gebruikt worden ter vervanging van de ALS-functie. Dit levert een iets kortere en beter leesbare formule op.
Stel we hebben de situatie, dat een bedrag met een bepaalde provisie wordt verhoogd wanneer dat bedrag hoger of gelijk is aan een grenswaarde (zie tabblad Vb1 in het Voorbeeldbestand).
De bijbehorende formule is dan:
=ALS(G4>=G2;G4*(1+G3);G4)
of (als we de betreffende cellen een naam hebben gegeven):
=ALS(Bedrag>=Grenswaarde;Bedrag*(1+Provisie);Bedrag)

NB een blok cellen een naam geven kan makkelijk als volgt:

  1. selecteer de cellen F2 t/m G4
  2. kies in de menutab Formules in het blok Gedefinieerde namen de optie Maken o.b.v. selectie
  3. in dit geval moet alleen Linkerkolom aangevinkt staan
  4. klik op OK
  5. controleer even wat er gebeurd is: kies in de menutab Formules in het blok Gedefinieerde namen de optie Namen beheren

Maar de formule kun je ook op een andere manier (eenvoudiger?) schrijven:
=Bedrag*(1+ALS(Bedrag>=Grenswaarde;Provisie;0))
of
=Bedrag*(1+Provisie*ALS(Bedrag>=Grenswaarde;1;0))
of door de N-functie te gebruiken:
 =Bedrag*(1+Provisie*N(Bedrag>=Grenswaarde))

N-functie in plaats van een opmerking

Er kan niet genoeg benadrukt worden hoe belangrijk het is om een spreadsheet van voldoende documentatie te voorzien (voor uzelf en/of voor anderen).

Die documentatie kan vastliggen in Word-files, in een apart tabblad en/of door cellen met formules van opmerkingen te voorzien (zie cel G5 in het tabblad Vb1 van het Voorbeeldbestand).

Maar plaats u veel opmerkingen in een spreadsheet dan wordt het daardoor ook niet overzichtelijker. Commentaar kan ook met de N-functie aan een cel worden toegevoegd. In cel G9 wordt daartoe bij de formule de N() met als parameter het commentaar (als tekst!) opgeteld; die laatste levert toch de waarde 0 op.

De functie T()

De T-functie zal nu weinig verrassingen meer opleveren: als de parameter een tekst is (of via een formule een tekst bevat) dan zal de T-functie de tekst terugleveren en anders niets (een lege tekst).

Aangezien WAAR en ONWAAR intern Excel als numeriek worden gezien (1 of 0) levert T() dus een lege waarde op.

Bij een foutmelding als parameter wordt de foutmelding overgenomen en een leeg argument voor de T-functie geeft een leeg resultaat.

T() in de praktijk

In een bepaalde toepassing wilt u teksten uit verschillende kolommen aan elkaar koppelen. Zoals we gezien hebben kan dat gemakkelijk met behulp van het &-teken. In het tabblad Vb2 van het Voorbeeldbestand hebben we dat gedaan in cel E3; daar zijn tussen de teksten ook nog extra spaties toegevoegd.

In deze toepassing mogen de cellen echter allen gekoppeld worden als het een tekst bevat; hier komt de T-tunctie om de hoek kijken (zie cel E4).

Maar nou krijgen we problemen met de hoeveelheid tussenspaties. In cel E5 is dat als volgt opgelost:
=ALS(T(B5)=””;””;T(B5)&” “)&ALS(T(C5)=””;””;T(C5)&” “)&ALS(T(D5)=””;””;T(D5))

Als T(B5) een leeg resultaat oplevert, dan hoeft er niets te gebeuren, anders komt er de tekst T(B5) met een extra spatie.
Idem voor C5 en voor D5 bijna hetzelfde; de extra spatie vervalt.

In cel E6 staat een alternatief:
=ALS(N(B6);””;T(B6)&” “)&ALS(N(C6);””;T(C6)&” “)&ALS(N(D6);””;T(D6))

De twee laatste formules hebben als probleem dat als de laatste kolom een getal bevat (en ook als de tweede en de laatste kolom getallen bevatten) er nog een spatie op het einde van het resultaat staat.
Dat lossen we als volgt op: =SPATIES.WISSEN(E6)

NB maar we hadden natuurlijk dan ook niet zo ingewilled hoeven te doen met de ALS-functie, want SPATIES.WISSEN verwijdert niet alleen alle spaties aan de voor- en achterkant, maar wijzigt meerdere, tussenliggende spaties in één spatie. Dus de formule in E4 had ook kunnen zijn: =SPATIES.WISSEN(T(B4)&” “&T(C4)&” “&T(D4))

T-functie en CSE

In de dagelijkse praktijk hebben de T- en N-functies weinig toegevoegde waarde en dus ook weinig toepassingen.
Bij meer ingewikkelde (database-) problemen zie je toch nog wel eens opduiken.

In het Voorbeeldbestand heb ik een idee van Chandoo overgenomen in het tabblad Data.
Uit een HR-systeem is een overzicht gekomen van medewerkers, maar wel in een speciale vorm: in de oneven regels (met donkere achtergrond) staat de medewerker en direct daaronder (met lichtere achtergrond) zijn of haar leidinggevende (de tabel met medewerkers heeft de naam tblData gekregen).
Ook de leidinggevende kan weer een leidinggevende hebben, dus namen kunnen dubbel voorkomen (zie bijvoorbeeld A. Niesen; leidinggevende van A. Smit (regel 4) en als medewerker (regel 7)).

De bedoeling is om uit dit bestand bij een bepaalde medewerker zijn/haar leidinggevende op te zoeken. Het mag duidelijk zijn, dat een gewone VERT.ZOEKEN hier niet volstaat; we mogen alleen maar zoeken in de cellen met een donkere achtergrond.

Wanneer we dus A. Niesen zoeken moeten we op regel 7 uitkomen; ofwel in het derde blokje van de medewerker/baas-combinatie.

NB om de invoer van een naam te vergemakkelijken heb ik aan cel E2 een Validatie-regel toegevoegd: alleen maar namen uit de reeks met de naam KeuzeLijst zijn toegestaan en kunnen via de -button gekozen worden.

De formule in cel E3 ziet er zo uit:
=VERGELIJKEN(E2;T(VERSCHUIVING(tblData[[#Kopteksten];[Medewerkers]];RIJ($A$1:$A$100)*2-1;;1;1));0)

Wanneer we dit wat overzichtelijker laten zien:
01:=VERGELIJKEN(
02:     E2;
03:     T(
04:          VERSCHUIVING(
05:               tblData[[#Kopteksten];[Medewerkers]];
06:               RIJ($A$1:$A$100)*2-1;
07:               ;
08:               1;
09:               1
10:          )
11:     );
12:     0
13:)

Met Vergelijken (regel 1) gaan we de medewerker (regel 2) opzoeken.
Waar gaan we zoeken? Natuurlijk in de tabel tblData (regel 5). Maar niet in alle namen; alleen in de eerste, derde, vijfde etc. Met Verschuiving (regel 4) wordt een eigen reeks gemaakt door te verschuiven vanaf de koptekst Medewerkers (regel 5).
Hoeveel gaan we verschuiven? Als je weet dat RIJ(A1:A100) het rijnummer van de betreffende cellen genereert (regel 6; dus de getallen 1 t/m 100) dan is duidelijk dat door vermenigvuldiging met 2 en dan er 1 vanaf trekken de getallen 1, 3, 5 etc ontstaan. Met deze reeks gaan we naar beneden verschuiven vanaf de kopregel.
We blijven met de verschuiving in dezelfde kolom, dus de parameter in regel 7 is leeg.
Het resultaat van de verschuiving moet 1 cel hoog zijn (regel 8) en 1 cel breed (regel 9).
Om nu te zorgen, dat er een reeks van echte namen ontstaat komt de T-functie om de hoek kijken (regel 3).
Dus we gaan de naam uit E2 vergelijken met onze eigen reeks, waarbij een exacte match nodig is (vandaar de 0 in regel 12). Het resultaat is het volgnummer van het de combi medewerker/baas.

LET OP om te zorgen, dat de functie RIJ alle benodigde getallen genereert moet de formule in E3 als een zogenaamde array- of matrix-formule worden ingevoerd. Sluit de invoer NIET af met Enter, maar met Control-Shift-Enter (ofwel een CSE-invoer).

NB bekijk de werking van de diverse onderdelen van de formule:

  1. selecteer cel E3
  2. kies in de menutab Formules in het blok Formules controleren de optie Formule evalueren
  3. kijk welk gedeelte van de formule onderstreept is, druk dan op Evalueren en bekijk het resultaat
  4. herhaal stap 3 totdat de hele formule geëvalueerd is.

In cel E4 de naam van de baas opzoeken is nu een ‘peulenschil’:

=INDEX(tblData[Medewerkers];E3*2)

 

Verkiezingsuitslagen en dynamische draaitabellen

Excel, VBA (Visual Basic for Applications), , , ,
Voorbeeldbestand: DynDraai.xlsbDownload

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

Sparklines

Excel, , ,

Volgens Microsoft:

Een sparkline is een kleine grafiek in een cel van een werkblad met een visuele weergave van de gegevens. Gebruik sparklines om trends in een reeks waarden aan te geven, zoals seizoensmatige stijgingen of dalingen of economische cycli, of om maximum- en minimumwaarden te markeren.

Anders gezegd: de sparkline-grafiek is niet bedoeld om details te onderscheiden, maar vooral om snel bepaalde trends of uitschieters te signaleren.

Hieronder gaan we eerst kijken hoe je een sparkline in je werkmap opneemt en daarna worden enkele extra mogelijkheden belicht en ook een aandachtspunt bij het gebruik van draaitabellen.

Hoe maak je een sparkline?

Laten we een simpel voorbeeld nemen: de inkomsten en uitgaven per maand en het resultaat daarvan. Zie het tabblad InUit in het Voorbeeldbestand.

LET OP de getallen zullen afwijken van de figuur hierboven. Met de functie ASELECTTUSSEN worden iedere keer nieuwe cijfers gegenereerd (druk op F9); dit om de consequenties voor de grafieken makkelijk te kunnen laten zien.

  1. selecteer het blokje getallen, dat in grafieken moet worden weergegeven (in het voorbeeld de cellen C4:N6; dus zonder de verklarende teksten! Een sparkline geeft alleen getallen weer.)
  2. kies in de menutab Invoegen in het blok Sparklines de optie Lijn
  3. in het nieuwe venster, dat automatisch opent, is het gegevensbereik al ingevuld. De plaats van de sparklines moet nog ingevuld worden. In dit geval moeten die achter de gegevens (dus in kolom O) komen; we vullen bij Locatiebereik O4:O6 in.
  4. klik op OK
  5. maak de kolom met de sparklines (in dit geval O) zo breed als gewenst om de resultaten het beste tot hun recht te laten komen.

LET OP het aantal cellen in locatiebereik moet gelijk zijn aan het aantal regels in het Gegevensbereik anders krijg je een foutmelding.

NB1 De sparklines mogen overal in het werkblad staan en ze mogen onder of naast elkaar komen. Maar zoals op de Microsoft-website staat:  “Plaats een sparkline dichtbij de gegevens voor het optimale effect.“

NB2 Je kunt bij punt 1 ook beginnen met de selectie van de locatiecellen (waar moeten de sparklines komen). In stap 3 moet dan het gegevensbereik worden opgegeven.

Sparklines opmaken

Hoewel in bovenstaand voorbeeld het verloop in de tijd al goed zichtbaar is, kunnen we bepaalde kenmerken nog accentueren:

  1. selecteer één van de sparklines; klik bijvoorbeeld met de muis in cel O4
  2. kies in de nieuwe menutab Hulpmiddelen voor sparklines de optie Ontwerpen
  3. vink in het blok Weergeven de opties Hoge punt en Negatieve punten aan
  4. kies in het blok Stijl eventueel een andere voor-geprogrammeerde opmaak

Deze opmaak wordt automatisch tegelijkertijd toegepast op alle drie de sparklines. Dit komt omdat Excel deze drie grafieken in één groep heeft geplaatst (klik in cel O4, kies in de menutab Hulpmiddelen voor sparklines de optie Ontwerpen en kies de optie Gegevens bewerken; nu zie je dat alle drie de sparklines bij elkaar horen).

Bij de inkomsten is de hoogste waarde (Hoge punt) goed, maar bij de uitgaven is dit natuurlijk niet het geval. De toppen moeten een verschillende kleur krijgen, daarom mogen de grafieken niet meer als één groep geselecteerd worden :

  1. selecteer de cellen met sparklines (O4:O6) en kies in de menutab Hulpmiddelen voor sparklines binnen de optie Ontwerpen de optie Groep opheffen
  2. selecteer alleen cel O4; kies binnen de optie Markeringskleur bij Hoge punt een groene kleur
  3. selecteer alleen cel O5; kies binnen de optie Markeringskleur bij Hoge punt een rode kleur
  4. selecteer alleen cel O6; kies binnen het blok Groeperen de optie As/As weergeven

NB Klik in cel O4, kies in de menutab Hulpmiddelen voor sparklines de optie Ontwerpen en kies de optie Gegevens bewerken; nu zie je dat deze sparkline alleen bij gegevens uit regel 4 hoort.

In het tabblad InUit2 van het Voorbeeldbestand is nog een andere optie gebruikt; bij de eerste 2 grafieken is als Type de optie Kolom gekozen:

NB de sparklines worden in Excel als achtergrond van een cel opgenomen. Dat betekent dat we nog steeds andere informatie in de cel kunnen opnemen, bijvoorbeeld toelichtende tekst.

Sparklines en draaitabellen

Wanneer je sparklines achter een draaitabel plaatst kan het voorkomen, dat wanneer deze draaitabel ‘groeit’ door nieuwe informatie die aan de bron wordt toegevoegd, de draaitabel over de sparklines valt.

Een oplossing is om van te voren te zorgen dat de draaitabel al alle mogelijke items bevat, zodat deze nooit meer groter kan worden (tabblad Ovz in het Voorbeeldbestand):

Aan de bron zijn hiertoe lege regels voor Nov en Dec toegevoegd; zie tabblad Data in het Voorbeeldbestand.

NB Het Resultaat staat niet in de bron, maar wordt in de draaitabel berekend:

  1. selecteer in de draaitabel één van de soorten(In of Uit); klik bijvoorbeeld met de muis in cel A5
  2. kies in de menutab Hulpmiddelen voor draaitabellen binnen Opties in het blok Berek. de optie Velden, Items en sets

    Kies dan de optie Nieuw item.
    NB we gaan binnen het veld Soort een nieuw item maken gebaseerd op andere items binnen Soort. De optie Nieuw veld wordt gebruikt om een nieuw veld (naast Soort, Maand en Bedrag) te maken.
  3. in het vervolgvenster geven we het nieuwe item een Naam, bijvoorbeeld Res (Resultaat is al toegevoegd) en als Formule tikken we in: = In-Uit en klikken op Toevoegen en OK

Trend-analyse

Excel, , , , , , , , , , ,

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.