Tagarchief: Tabel

Samengestelde interest



Samengestelde interest of rente op rente: een onderwerp, dat regelmatig weer vragen oproept. Zijn daar in Excel nu wel of niet functies voor?

De aanleiding om hier over te schrijven was de volgende vraag:

“Volgens Microsoft-informatie bestaat er geen functie voor samengestelde interest in Excel.
Vroeger, al weer 50 jaar geleden, heb ik op de middelbare school nog financiële rekenkunde gekregen.
Daarbij gebruikte je de diverse rentetabellen met de diverse percentages om een bepaald bedrag uit te kunnen rekenen. Dat waren de kleine en grote A- en S-tabellen, als ik het mij nog goed herinner.”

In dit artikel zal ik laten zien, dat Microsoft zichzelf iets tekort doet. Het hangt van de situatie af. Maar zo gauw het wat ingewikkelder wordt, zul je toch zelf een systeem in Excel moeten bouwen, een soort (complexe) rentetabellen.

Rente op rente, variant 1a

Voor samengestelde interest zijn allerlei varianten denkbaar.
De meest eenvoudige is de situatie, dat u een bedrag op een spaarrekening zet waar jaarlijks een rente wordt bijgeschreven (ja, dat bestaat nog!). U laat na het eerste jaar alles op de rekening staan en krijgt in het tweede jaar dus ook rente vergoed over de rente van het eerste jaar (vandaar de naam Rente op rente).

Nou dit is zo simpel, dat we daar ook eigenlijk geen functie voor nodig hebben.
Op het einde van het eerste jaar wordt er rente bijgeschreven; uw tegoed is dan Bedrag * (1 + Rente%).
Op het einde van het tweede jaar wordt dit resultaat weer vermenigvuldigd met (1 + Rente%). Het mag duidelijk zijn, dat op het einde van de looptijd de Toekomstige Waarde gelijk is aan het gestorte Bedrag maal (1 + Rente%) tot de macht Looptijd.

In het Voorbeeldbestand in het tabblad TW1 staat deze berekening van de Toekomstige Waarde in cel C8:
=Bedrag*(1+Rente)^Looptijd

In de formule staan geen celverwijzingen; de betreffende cellen C4 t/m C6 hebben een naam gekregen waardoor formules die daar naar verwijzen veel beter leesbaar zijn.

NB1 de eenvoudigste manier om een cel een naam te geven is door deze cel te selecteren en dan in het naamvak linksboven de gewenste naam in te tikken.

NB2 cellen met een groene achtergrond zijn invoer-cellen.

NB3 de vraag bovenaan het tabblad is dynamisch; in cel B1 staat de formule
=”Wat is “&Bedrag&” euro over “&Looptijd&” jaar waard bij “&TEKST(Rente;”0,0%”)&” rente?”
M.b.v. het &-teken worden teksten tussen aanhalingstekens samengevoegd met namen van cellen. Om te zorgen dat Rente een mooie opmaak krijgt is gebruik gemaakt van de functie TEKST.

NB4 misschien wat verwarrend: in dit geval wordt het woord Rente gebruikt waar een rentepercentage wordt bedoeld.

Rente op rente, variant 1b

Maar de berekening kan ook met de standaard Excel-functie TW uitgevoerd worden. De benodigde formule staat in cel C9 van het tabblad TW1 van het Voorbeeldbestand:
=TW(Rente;Looptijd;0;-Bedrag)

Zoals u ziet kent deze functie 4 parameters; de betekenis van de eerste 2 moge duidelijk zijn. Met de derde parameter kunt u aangeven of er nog vaste periodieke betalingen plaats vinden (in dit voorbeeld niet; hier komen we later nog op terug). De vierde parameter geeft aan wat de Huidige Waarde is; in dit voorbeeld ‘geven’ we  dit bedrag aan de bank, vandaar het minteken.

LET OP1 als het minteken wordt weggelaten wordt het resultaat van de formule negatief.

LET OP2 als na het invoeren van de TW-functie op Enter wordt gedrukt, zal Excel het resultaat van de formule weergeven. Maar dat niet alleen: ook wordt de opmaak van de cel gewijzigd in Valuta.

Rente op rente, variant 1c

In de vorige voorbeelden is gewerkt met een jaarlijkse bijschrijving van de rente. In het tabblad TW2 van het Voorbeeldbestand kunt u zien wat het effect is van de verrekening van een maandrente (jaarrente gedeeld door 12). Afhankelijk van de keuze in cel C6 (J of M is mogelijk) wordt de JrMndFactor in cel C7 gelijk aan 1 of 12.

NB als er met maandrente wordt gerekend moet de looptijd niet in jaren maar in maanden worden uitgedrukt.

Rente op rente, variant 2

Maar wat als er niet één storting plaats vindt, maar ieder jaar (aan het begin) een overboeking plaats vindt?
Over het eerste bedrag wordt het aantal keren, dat de Looptijd is, rente uitgekeerd; over de tweede storting 1 keer minder etc. Met behulp van een wiskundige afleiding kan ook hier de TW uitgedrukt worden in een formule.

NB voor de liefhebbers: TW = Bedrag*(1+r)^n + Bedrag*(1+r)^n-1 + .. + Bedrag*(1+r).
Vermenigvuldig deze vergelijking met (1+r) en trek ze van elkaar af. Allerlei termen vallen dan tegen elkaar weg. Nog wat reshuffelen en klaar is Kees.

In het Voorbeeldbestand is deze formule in cel C8 van het tabblad TW3 opgenomen.

Maar ook hier kunnen we de standaard Excel-functie TW gebruiken; zie cel C9 in het tabblad TW3:
=TW(Rente;Looptijd;-Bedrag;0;1)

Omdat het hier periodieke betalingen/stortingen betreft, wordt het bedrag in de derde parameter geplaatst (met een min-teken). De vierde parameter (de huidige of startwaarde) is in dit geval 0.

NB de functie TW kent een optionele vijfde parameter (Type). Als deze 1 is dan betreft het een storting aan het begin van iedere periode (prenumerando); wordt hier 0 ingevuld of wordt deze weggelaten dan stelt het een storting op het einde van de periodes weer (postnumerando).

Bij alle bovenstaande methodes is het nadeel, dat er telkens slechts het resultaat na één looptijd zichtbaar is.

In dit soort situaties is het vaak veel prettiger om een overzicht/systeem op te zetten, waar het verloop in de tijd zichtbaar is.

Op het tabblad TW3 in het Voorbeeldbestand staat zo’n systeem:

  1. het startbedrag van het eerste jaar is het stortingsbedrag uit cel C4: =Bedrag
  2. in de kolom daarnaast wordt de rente over jaar 1 berekend: =[@StartBedr]*Rente
    dus het startbedrag uit dezelfde regel (vandaar de @) maal de rente uit cel C5
  3. in de volgende kolom staat het resultaat op het einde van het jaar: =[@StartBedr]+[@Rente]
  4. het startbedrag voor jaar 2 wordt dan het eindresultaat van jaar 1 plus de nieuwe storting: =H5+Bedrag
  5. aangezien we alles in de vorm van een Excel-tabel hebben gezet, worden de formules in de rente- en eind-kolom automatisch ingevuld
  6. de formule uit punt 4 kan naar beneden gekopieerd worden

Bij wijziging van het stortingsbedrag en/of rente-percentage zien we nu in één oogopslag wat de consequentie daarvan is in de loop van de tijd.

Rente op rente, variant 3a

Maar wat als de stortingen en/of het rentepercentage nu niet meer constant zijn in de loop van de tijd?

In het tabblad TW4 van het Voorbeeldbestand is het systeem uit het tabblad TW3 iets aangepast: er zijn kolommen voor het stortingsbedrag en het rentepercentage bij gekomen.

NB in deze 2 nieuwe kolommen staan alleen in de eerste rij ‘harde’ waarden (namelijk 100, respectievelijk 3%); daaronder staan altijd verwijzingen naar de regel daarboven. Een wijziging in één van de jaren wordt dan automatisch naar de toekomst doorgevoerd.

Rente op rente, variant 3b

Op het tabblad TW4 staat nog een tweede systeem.
We weten natuurlijk niet exact welk bedrag we in de toekomst kunnen sparen en wat de rente zal zijn. Maar we kunnen wel schattingen maken: ieder jaar denken we 10% meer te kunnen sparen (in het voorbeeld staat deze schatting nog op 0%) en we verwachten (hopen?), dat de rente ieder jaar 2% zal stijgen (relatief dus, geen harde 2%. In dat geval hadden we gezegd, dat het percentage met 2 procent-punt zou stijgen).

Welke van bovenstaande varianten de voorkeur heeft, is natuurlijk afhankelijk van uw eigen wensen. Betreft het slechts een eenmalige berekening of is er veel flexibiliteit en inzicht gewenst etcetera.


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

 

 

 

Grafische analyse in Excel



Onlangs kwam ik op de website www.exceluser.com enkele nuttige artikelen tegen: 3 Simple Tricks to Improve Analytical Charting in Excel3 More Simple Tricks to Improve Excel Charts for Business, Introducing the Power of Year-Over-Year Performance Charts in Excel en What Inflation? How Excel Charts Can Help You Avoid ‘Anecdotal Economics’.

In dit artikel zal ik een paar ideeën van Charles W. Kyd de revue laten passeren en aan de hand van voorbeelden de principes daarachter uitleggen.

Wat laat je zien?

Hiernaast ziet u een simpel omzet-overzicht van een bedrijfje (zie het tabblad Vb1 in het Voorbeeldbestand).
In de maandrapportage kun je zo’n overzicht opnemen, maar je kunt er ook voor kiezen om een grafiek te tonen.

Directeur tevreden, iedereen tevreden?
Nee dus, de marketingmanager vindt dat zo niet goed zichtbaar is, dat de marketing-activiteiten hun vruchten afwerpen en wil een andere grafiek.

Dan blijkt dat de productie-manager weer andere wensen heeft (een collega heeft in de maand maart zijn werkzaamheden overgenomen) en vindt dat het beter is om de groei/krimp van de omzet in de loop van de tijd te laten zien.

Waar cijfer-overzichten veelal de interpretatie van de data aan de lezer overlaten, mag het duidelijk zijn dat grafische presentaties het gevaar van misleiding met zich mee brengen.

Onvergelijkbare gegevens vergelijken

Stel we hebben een maandoverzicht waarin 2 soorten bedragen voorkomen, die qua grootte ver bij elkaar uit de buurt liggen (zie het tabblad Vb2 in het Voorbeeldbestand).

Wanneer we deze in een standaard lijn-grafiek weergeven, dan zijn er weinig details per maand uit de grafiek af te leiden.

Eén van de meest gehanteerde oplossingen is om de twee reeksen aan verschillende assen toe te wijzen.
NB het kleurgebruik moet de lezer helpen om te zien welke reeks bij welke as hoort.

Als vooral het verloop van de tijd van belang is en niet de exacte hoogte van de bedragen, dan is een andere oplossing om de reeksen te indexeren.
Dan is er maar één verticale as nodig; dit voorkomt misverstanden.
Het betekent wel dat er aan de basisgegevens kolommen moeten worden toegevoegd met een berekening zoals =[@Bedr1]/$C$6

Verloop AEX

Eén van de artikelen op ExcelUser.com is geschreven naar aanleiding van het dalen van de koersen in Amerika (What Inflation?). Deze daling liep synchroon met de correctie van de bitcoin-koersen. Maar was/is hier sprake van een correctie of begint de inflatie weer toe te nemen? Of nog erger: zitten we weer aan het begin van een recessie?

En hoe zit dit in Nederland? Geeft de AEX ook aanleiding tot dit soort bespiegelingen?
De bedoeling van dit artikel is niet om hier nu een sluitend antwoord op te geven; wel zal ik aan de hand van enkele voorbeelden laten zien hoe grafieken het proces om te komen tot beantwoording kunnen ondersteunen.

In het tabblad AEX van het Voorbeeldbestand staat een overzicht van de AEX-resultaten vanaf januari 2000 (alleen koersen per werkdag zijn beschikbaar). We zullen alleen de kolom Datum en Close (koerswaarde op het moment van sluiten van de beurs) gebruiken.

De gegevens zijn opgeslagen in de vorm van een Excel-tabel met de naam tblAEX. Een van de grote voordelen van deze vorm is, dat wanneer er nieuwe cijfers beschikbaar zijn en deze onderaan toegevoegd worden, alle formules en grafieken automatisch aangepast worden. Een update van het analyse-systeem is dus een ‘fluitje van een cent’.

Om makkelijk te kunnen inzoomen op gedeeltes van het verloop van de AEX is deze grafiek dynamisch gemaakt. Dus wat er getoond wordt is afhankelijk van de inhoud van bepaalde cellen, in dit geval F3 en F4 (zie ook het artikel dynamische-grafieken).

In het tabblad AEX_Ovz van het Voorbeeldbestand staan daartoe 2 hulp-tabellen.
In cel C3 (met de naam MinDtm) wordt het minimum van de kolom Datum uit de tabel tblAEX bepaald; zo ook in cel C4 het maximum. Onder andere deze 2 waardes worden gebruikt om de mogelijke invoer in de cellen F3 (met de naam Start) en F4 (met de naam Eind) te begrenzen (met behulp van Gegevens-validatie).
In cel G3 (met de naam StartRij) wordt dan bepaald in welke rij van de AEX-tabel de datum uit F3 staat: =VERGELIJKEN(Start;tblAEX[Datum])

LET OP de derde parameter in de functie VERGELIJKEN is weggelaten; dat betekent dat de functie de grootste datum zoekt die kleiner of gelijk is aan Start. De kolom Datum in de tabel tblAEX moet dan wel oplopend gesorteerd zijn!

Zo wordt ook de rij van de laatste gewenste datum opgezocht in cel G4 (met de naam EindRij).

Namen definieren

In het voorgaande heb ik al regelmatig Namen gebruikt en dan vooral met het doel formules leesbaarder te maken. Ook hebben alle Excel-tabellen een naam gekregen, waardoor in formules direct duidelijk is naar welke tabel verwezen wordt. Het gebruik van tabellen heeft daarnaast als voordeel dat formules en grafieken verwijzen naar kolomnamen i.p.v. naar cel-bereiken (dus =tblVb1[Omzet] in plaats van =C3:C8)

Maar om dynamische grafieken te kunnen creëren hebben we ook iets ingewikkelder constructies als Naam nodig:

  1. kies in de menutab Formules in het blok Gedefinieerde namen de optie Namen beheren
  2. klik op de button Nieuw…
  3. geef een naam op; als eerste voorbeeld rngDtm
  4. de formule onder Verwijst naar wordt dan
    =VERSCHUIVING(tblAEX[[#Kopteksten];[Datum]];StartRij;0;EindRij-StartRij+1)
  5. klik Sluiten

NB de functie VERSCHUIVING geeft als resultaat de reeks cellen, die ontstaat als we vanaf de kop van de kolom Datum in de tabel tblAEX het aantal StartRij naar beneden gaan en 0 kolommen naar rechts/links, waarbij de lengte van de reeks bepaald wordt door de formule EindRij-StartRij+1.

De formule voor rngClose is dan gelijk aan =VERSCHUIVING(tblAEX[[#Kopteksten];[Close]];StartRij;0;EindRij-StartRij+1)

LET OP Deze rng-namen kunnen nu via Gegevens selecteren aan een grafiek worden gekoppeld; denk er wel aan om niet =rngDtm in te vullen, maar =AEX!rngDtm, dus voor alle namen een naam van een tabblad, aangevuld met een !-teken.

Vul je in F3 de waarde 1-1-2007 in en in cel F4 de waarde 31-12-2013 dan ontstaat automatisch de volgende grafiek:

AEX in perspectief

Eén van de tips van ExcelUser.com is om een grafiek van een (historisch) perspectief te voorzien. In dit geval kan het interessant zijn om te zien wat het verband is tussen het koersverloop en recessies.

Daartoe zijn in het tabblad Recessie van het Voorbeeldbestand de recessies vanaf 2000 vastgelegd met een begin en een einddatum. Dit in de vorm van een Excel-tabel met als naam tblRecessie.

De tabel tblAEX in het tabblad AEX is uitgebreid met een kolom F waarin de formule
=ALS([@Datum]<=INDEX(tblRecessie[Eind]; VERGELIJKEN([@Datum];tblRecessie[Start]));
1;NB())
staat.

Korte toelichting: zoek met de functie VERGELIJKEN de Datum uit dezelfde regel (vandaar de @) op in de Start-kolom van tblRecessie ; als dit niets oplevert dan is het resultaat van de totale formule #N/B.
Dan wordt met behulp van de functie INDEX gekeken of deze datum kleiner of gelijk is aan de overeenkomende regel in de Eind-kolom van tblRecessie.
Als dat zo is, dan wordt het resultaat 1 en anders ook weer #N/B.
Het resultaat #N/B wordt in een grafiek niet weergegeven.

Deze recessie-gegevens nemen we in de grafiek op; niet rechtstreeks maar ook weer via een naam-formule: rngRecessie
=VERSCHUIVING(tblAEX[[#Kopteksten];[Recessie]];StartRij;0;EindRij-StartRij+1)

Deze range krijgt een eigen type grafiek, namelijk een Vlak en we zetten deze uit op de secundaire as. Zorg dat het maximum van deze as altijd 1 is en verwijder alle kenmerken van de as.

Op een vergelijkbare manier zijn ook de minimum- en maximum-koersen in de grafiek opgenomen.

 

Alarmbellen?

Als we inzoomen op het koersverloop van dit jaar blijft de vraag of er alarmbellen moeten gaan rinkelen; bijna 50 punten verschil tussen het minimum en het maximum, is dat een teken dat het fout gaat?

Die vraag is natuurlijk niet zo maar te beantwoorden, maar zoals Charles W. Kyd in één van zijn artikelen aangeeft: probeer een andere invalshoek.

Daarom zijn de AEX-gegevens uitgebreid met een kolom, die aangeeft hoeveel procent de koers op een bepaalde datum is gestegen of gedaald ten opzichte van het jaar daarvoor:

=[@Close]/INDEX([Close];VERGELIJKEN(ZELFDE.DAG([@Datum];-12);[Datum]))-1

Deel de slotkoers van een dag door de overeenkomende dag 12 maanden terug en trek daar 1 vanaf.

Ook nu is de derde parameter van de functie VERGELIJKEN weggelaten; dus soms wordt de slotkoers niet vergeleken met de slotkoers exact 12 maanden terug, maar de dichtstbijliggende datum er voor.

De grafiek hiervan is via de naam rngCloseMutJr weergegeven op het tabblad AEX_Ovz2 van het Voorbeeldbestand.

In de grafiek is te zien, dat fikse negatieve resultaten veelal samen vallen met recessies. Begin 2018 scoort nog steeds flink positief.
Om toch maar een voorspelling te doen: de conclusie lijkt gerechtvaardigd dat de economie positieve perspectieven laat zien.


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

 

 

 

VBA-routines versnellen



Wanneer u net begint met het ontwikkelen van VBA-routines om werkzaamheden te automatiseren, zult u versteld staan van de tijdwinst die dit kan opleveren.

Maar ….  als dan de hoeveelheid gegevens, waarmee u werkt, gaat groeien dan kan het zo maar zijn dan zo’n routine tientallen secondes bezig is en dat u denkt: kan dat niet sneller!

Het antwoord is (hoogst waarschijnlijk): JA, dat kan zeker sneller.

In dit artikel enkele tips voor efficiëntere VBA-routines.

Gebruik van Select

Wanneer u bepaalde handelingen in Excel wilt automatiseren, is de macro-recorder van VBA een uiterst handig hulpmiddel. U neemt de toetsaanslagen op en volgende keer kunt u met één druk op een knop diezelfde toetsaanslagen herhalen. Vaak wordt zo’n recorder-opname dan gebruikt als basis voor een uitgebreidere routine.

In dit voorbeeld geldt dat ook (zie Blad1 in het Voorbeeldbestand):

  • we hebben een tabel (met de naam Tabel1) met 2 kolommen: in de eerste staan maanden en in de tweede worden willekeurige bedragen met 2 decimalen gegenereerd, liggend tussen 1 en 2:
    =ASELECTTUSSEN(100;200)/100
  • het eerste bedrag (cel C3) heeft de naam Bedrag1 gekregen, terwijl het bereik van alle bedragen de naam Bedr heeft.
  •  in kolom F worden het aantal regels, het totaalbedrag en het gemiddelde bepaald. Het tweede gemiddelde is gelijk aan het  totaalbedrag gedeeld door het aantal; een simpel voorbeeld van een ingebouwde controle.
  • als voorbeeld heb ik een VBA-routine gemaakt (gebaseerd op een macro-opname), die ook dezelfde berekeningen uitvoert.
    Deze routine selecteert cel Bedrag1  en “loopt” zolang er geen lege cel is naar beneden; ondertussen wordt het aantal geturfd en een lopend totaal bepaald:
  • op het einde van de VBA-routine zorgen we dat de cursor weer in cel A1 komt en worden de resultaten via een pop-up zichtbaar gemaakt.
    Ook de verstreken tijd is in de pop-up opgenomen.
  • klik op de button Select in het Voorbeeldbestand om de VBA-routine uit te voeren.

NB1 door de rechte haken rond een naam weet VBA, dat het een naam in de Excel-sheet is, dus niet de naam van een VBA-variabele.

NB2 zo ziet een info-scherm op de MAC er uit; de PC-versie oogt anders.

NB3 mijn MAC heeft dus ruim 11 seconden nodig voor de 1.000 verplaatsingen en berekeningen! Op een PC duurt het (uiteraard afhankelijk van de soort machine) nog geen seconde.

NB4 wil je de gebruikte routine zien: klik Alt-F11 of ga via de menutab Ontwikkelaars naar Visual Basic.

Gebruik van Select-2

Het grootste gedeelte van de benodigde tijd van de vorige VBA-routine gaat zitten in het fysiek verplaatsen van de cursor (via Offset).

Met behulp van het VBA-commando Application.ScreenUpdating = False kunnen we er voor zorgen, dat het verversen van het scherm niet meer plaats vindt. Het resultaat is, dat de MAC binnen een halve seconde klaar is!

Klik op de button Select2 om de alternatieve VBA-routine uit te voeren.

NB vergeet niet op het einde van zo’n routine de scherm-verversing weer aan te zetten: Application.ScreenUpdating = True

Gebruik van Range

Veel Excel-ontwikkelaars vinden het gebruik van de Select-methode uit den boze. Bij een prima alternatief wordt het belangrijkste gedeelte van de routine:

De berekeningen worden uitgevoerd voor iedere cel in het bereik Bedr. De computer gaat nu in sneltrein-verhaal langs de inhoud van de cellen zonder dat er een verplaatsing van de cursor nodig is.

De MAC is nu binnen 1/100 van een seconde klaar! Test de routine door op de button Range te klikken.

Gebruik van een array

In het vorige voorbeeld hebben we gebruik gemaakt van een Excel-bereik (in dit geval met de naam Bedr).
Bij heel grote bestanden kan er tijdwinst geboekt worden door dit bereik eerst in een aparte array (rij, reeks, matrix) te plaatsen:

  • het bereik met de naam Bedr (de truc met de rechte haken werkt nu niet meer) wordt cel voor cel in een array aBedr gestopt
  • voor ieder element van de array wordt het lopende totaal bepaald (Ubound=UpperBound=bovengrens)

Sneller dan het geluid!? De routine heeft minder dan 1/10.000 seconde nodig.

Gebruik van Tabel

Een alternatief voor de vorige oplossing is:

  • de kolom Bedrag uit Tabel1 wordt in de array aBedr gestopt
  • de bovengrens van de array plaatsen we in de variabele N (in het vorige voorbeeld moet de bovengrens iedere keer opnieuw worden bepaald; niet efficiënt)

Gebruik van Tabel-2

In een variant van de vorige oplossing schrijven we de resultaten ook nog weg in de Excel-sheet (in cellen van kolom G met de namen Aantal, Totaal en Gem1_):

NB1 cel G5 heeft NIET de naam Gem1. Er bestaat al een cel met die naam, namelijk de cel in kolom GEM, rij 1. Dit wordt vaak opgelost door een underscore er voor of er achter te zetten.

NB2 doordat er cellen in de sheet worden aangepast gaat Excel alle formules doorrekenen; dus alle bedragen in Bedr veranderen NADAT de VBA-berekening is uitgevoerd.

Effect van groter bestand op de berekeningen

Door Tabel1 uit te breiden naar bijvoorbeeld 100.000 regels is het verschil in benodigde tijd beter te beoordelen. Trek daartoe de vulgreep van de tabel rechtsonder naar beneden.


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

 

 

 

DB-functies



Data-analyse: een belangrijk item voor menig bedrijf en daarom zijn diverse artikelen op deze site geschreven met dat onderwerp in het achterhoofd.
De gereedschapskist van een data-analist moet flink gevuld zijn om zijn of haar werk goed, effectief en efficiënt uit te kunnen voeren.

Naar aanleiding van het artikel SOMPRODUCT: meer dan SOM en PRODUCT kwam de opmerking langs om in dat kader ook aandacht te besteden aan de DB-functies, ofwel de database-functies van Excel.

Hieronder zal ik enkele mogelijke tools in een analyse-traject naast elkaar zetten: de draaitabel, SOMPRODUCT en de DB-functies. Op die manier wordt vanzelf duidelijk wanneer je welk gereedschap het best kunt inzetten.

Database

In dit artikel gebruiken we een omzet-overzicht, gesplitst naar Regio, Product en Datum (zie het tabblad VbData in het Voorbeeldbestand).
De database is opgeslagen als Excel-tabel met als naam tblOmzet; voor de voordelen zie de artikelen over tabellen deel 1 en deel 2.

Deze database bevat 1.000 records (regels) en een kopregel, waarin de namen van de velden (kolommen) staan.

Analyse dmv draaitabellen

Wat mij betreft is dit in verreweg de meeste gevallen het handigste hulpmiddel voor data-analyse.

Selecteer één van de cellen in de database, klik op Invoegen en dan Draaitabel en bevestig met OK.

Sleep Regio naar Rijlabels, Product naar Kolomlabels en de Omzet naar het Waardegebied.
De laatste stap nog 2 keer herhalen en Som veranderen in Aantal, respectievelijk Gemiddelde.
Nog wat opmaak regelen  en in een mum van tijd hebben we een goed inzicht in de verdeling van de omzet naar de diverse categorieën (zie het tabblad Draai in het Voorbeeldbestand).

Een andere draaitabel laat de verdeling over de maanden zien. Daartoe is de Datum in het vak Rijlabels gesleept. Even rechtsklikken op één van de datums in het overzicht dat ontstaat, Groeperen kiezen (zie Groeperen in een draaitabel), alleen de Jaren en de Maanden selecteren en OK.

Via opmaak van de draaitabel kan deze veelal in de gewenste vorm gebracht worden. Is dat niet het geval dan kan de informatie met de functie DRAAITABEL.OPHALEN nog anders weergegeven worden (zie het gelijknamige artikel).

Analyse met SOMPRODUCT

In het artikel SOMPRODUCT: meer dan SOM en PRODUCT heb ik al laten zien hoe de functie Somproduct gebruikt kan worden om gegevens uit een database op te halen.

Deze methode is ontzettend flexibel is: op iedere willekeurige plaats, in iedere willekeurige volgorde kan informatie verzameld worden uit de database.
Maar … de gebruikte formules zijn niet altijd op het eerste gezicht duidelijk, zie bijvoorbeeld cel C4 in het tabblad SomProd van het Voorbeeldbestand:

=SOMPRODUCT((tblOmzet[Regio]=$C$2)*(tblOmzet[Product]=$C$3)*tblOmzet[Omzet])

NB een groot nadeel van deze methode is, dat je wel al moet weten welke Regio’s en welke Producten in de database voorkomen. Een draaitabel laat automatisch alle voorkomende namen zien.

Analyse met DB-functies

Excel kent diverse database-functies; de naam daarvan beginnen allemaal met DB (in de Engelstalige versie alleen D).
De naam geeft vaak goed weer waar de functie voor bedoeld is: DBSOM, DBAANTAL, DBGEMIDDELDE, DBMAX, DBMIN etc.

Alle database-functies verwachten 3 parameters/argumenten: allereerst waar de database te vinden is (het betreffende cel-bereik), dan het veld (kolomnaam) waarvan gegevens moeten worden verzameld en als derde een bereik waarin criteria zijn aangegeven waaraan de gezochte records moeten voldoen.

Op het tabblad DB1 van het Voorbeeldbestand staat in cel F3 een formule, waarmee de totale omzet wordt bepaald uit Regio Noord voor het Product met de naam Vier:
=DBSOM(tblOmzet[#Alle];tblOmzet[[#Kopteksten];[Omzet]];Criteria)

De 1e parameter (tblOmzet[#Alle]) verwijst naar alle cellen in de omzet-tabel. Voegt u records toe dan zal deze verwijzing de toevoegingen automatisch meenemen.

LET OP tblOmzet is als parameter niet voldoende; de kopregel met veldnamen wordt dan niet meegenomen.

Achter de eerste punt-komma komt de 2e parameter (tblOmzet[[#Kopteksten];[Omzet]]); een rechtstreekse verwijzing dus naar het veld Omzet.

NB in plaats van een verwijzing kun je ook een letterlijke tekst intikken, in dit geval “Omzet” (denk aan de aanhalingstekens). Maar let op: wanneer de veldnaam in de kopregel van de database wordt aangepast, dan werkt deze formule niet meer!
Ook is het toestaan om als 2e parameter een volgnummer van de betreffende kolom mee te geven (in dit geval zou dit 4 zijn). Maar ook hier opletten: komt er een kolom in de database bij, dan werkt de formule niet meer naar behoren.

Achteraan staat de 3e parameter (Criteria). Dit is een zelf-gedefinieerde bereiknaam,  een verwijzing naar de cellen B3:C4.

In het tabblad DB1 staan ook 2 gegevenstabellen, die gebaseerd op DB-functies een soort draaitabel nabootsen; informatie over gegevenstabellen vindt u in het betreffende artikel.

In het tabblad DB2 van het Voorbeeldbestand heb ik een methode uitgewerkt waarmee het ook mogelijk is om de totalen van een bepaalde maand te selecteren. Zoals u ziet kun je in de criteria ook twee keer hetzelfde veld opgeven.

De voordelen van de DB-methode liggen er vooral in, dat de criteria voor de selectie apart  en duidelijk herkenbaar vastliggen.

De selectiecriteria hoeven niet beperkt te blijven tot 1 regel.
In het tabblad DB3 van het Voorbeeldbestand staat een voorbeeld waarbij de criteria uit 2 regels bestaan (waarbij het bereik de naam Crit2 heeft gekregen). In dit geval worden alle records van de Regio Noord meegenomen, waarbij de Productnaam Een OF Twee is.

Bij het opgeven van criteria moet u het volgende altijd goed in het achterhoofd houden:

  • aan de voorwaarden in dezelfde regel  moet tegelijkertijd voldaan worden (in het voorbeeld Regio=Noord EN Product=Een)
  • voorwaarden in verschillende regels selecteren díe records, die aan de ene regel OF aan de andere OF aan allebei voldoen

LET OP1 in de 2e regel van het voorbeeld mag u niet Noord weglaten, want dan zal Excel alle records selecteren, waarbij (Regio gelijk is aan Noord EN Product gelijk aan Een) OF (Product gelijk is aan Twee).

LET OP2 als de criteria meer dan 1 regel beslaan vergeet dan niet alle regels te vullen; eventueel met dezelfde voorwaarden.

NB een leeg veld binnen de criteria betekent dat er geen voorwaarde aan dat veld wordt opgelegd (in die regel).

In dit voorbeeld vooral ziet u de flexibiliteit van het gebruik van de DB-functies.
Probeer dit maar eens snel in een draaitabel klaar te krijgen! Zeker als je later bedenkt dat de grens van de Omzet beter bij 1250 kan liggen.


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

 

 

 

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: