Tagarchief: Namen

Gegevensvalidatie

Excel, , , ,
Voorbeeldbestand: GegVal.xlsxDownload

In andere artikelen op deze site is al regelmatig gebruik gemaakt van Gegevensvalidatie. Meestal om er voor te zorgen dat er bij het invoeren van gegevens geen ‘vervuiling’ ontstaat (is de plaatsnaam nu Wanroy of Wanroij, moet de afdeling aangeduid worden met Marketing of met Verkoop, heet die medewerker nu Eric Jansen of Erik Janssen?)

Bij iedere Excel-sheet, waarin gegevens worden ingevoerd, is consistentie van groot belang om te zorgen dat bij analyses of rapporten de juiste zaken bij elkaar worden genomen.

In dit artikel kijken we naar de mogelijkheden van Gegevensvalidatie. Daarbij maken we gebruik van keuzelijsten. Niet echt ingewikkeld, maar het wordt wel leuk als we gebruik gaan maken van ‘meervoudige’ of afhankelijke keuzelijsten.

Gegevensvalidatie

In het Voorbeeldbestand op het tabblad Decl staat een ‘systeem’ waarmee binnen een bedrijf declaraties kunnen worden vastgelegd.

Niet alle invoer is toegestaan; aan alle 4 kolommen worden bepaalde eisen gesteld.

  1. Datum: in de cellen H2 en H3 (met de namen DtmVan en DtmTot) liggen een onder- en bovengrens vast.
    De ondergrens is een harde datum, de bovengrens is de datum van vandaag.
  1. Maand: dit is de maand waarop de declaratie betrekking heeft. Deze moet altijd kleiner of gelijk zijn aan de declaratiedatum
  2. voor Afd zijn maar 6 verschillende namen toegestaan
  3. het Bedrag moet altijd groter zijn dan 0 en kleiner dan 1000

Datum

  1. selecteer alle cellen uit de eerste kolom (behalve de kop natuurlijk)
  2. kies in de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie

  3. In de tab Instellingen zorgen we dat er alleen een datum is toegestaan.
    Bij Begindatum vullen we de naam van de betreffende cel in (druk op F3 en kies de naam), bij Einddatum verwijzen we uiteraard naar de betreffende cel.
    LET OP bij de validatie worden de twee grenzen als toegestaan gerekend
    NB1 vul je de naam handmatig in, vergeet dan het =-teken niet!
    NB2 in ons voorbeeld is het onderste vinkje niet nodig (we hebben alle betreffende cellen geselecteerd). Wil je later een wijziging aanbrengen, kies dan één cel en plaats het vinkje.
    NB3 de datum zal altijd ingevuld moeten worden; verwijder dus het vinkje bij Lege cellen negeren.
  1. Op de tweede tab kun je een Invoerbericht maken. Deze info wordt zichtbaar wanneer de betreffende cel is geselecteerd.
  1. Het is ook mogelijk om een eigen foutmelding te genereren die getoond wordt wanneer de invoer niet overeenkomt met de aangegeven grenzen.
    Wanneer je als Stijl de optie Stop kiest, zal Excel een juiste invoer afdwingen:

Maand

  1. selecteer alle cellen uit de tweede kolom
  2. kies in de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie
  1. Bij Toestaan kiezen we de optie Aangepast.
    Excel biedt dan de mogelijkheid om een formule in te tikken:
    =C3<=MAAND(B3)
    LET OP deze formule gaat er van uit dat een cel in regel 3 actief/geselecteerd is; anders een verwijzing naar de betreffende regel gebruiken.
  1. Ook voor deze kolom is een Invoerbericht aangemaakt.
  1. Een foutmelding geeft de gebruiker houvast wanneer de invoer niet juist is.
    Hier hebben we bij Stijl gekozen voor Waarschuwing; de gebruiker kan er dan altijd voor kiezen om toch door te gaan:

Afd

  1. selecteer alle cellen uit de derde kolom
  2. kies in de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie
  1. de toegestane afdelingsnamen staan in de cellen J3:J8.
    NB Zorg dat het vinkje aanstaat bij Vervolgkeuzelijst; de gebruiker kan dan kiezen uit de lijst en hoeft niets handmatig in te tikken.
  1. Op de tab Foutmelding is de Stijl Info gekozen. Dat betekent dat bij een ‘verkeerde’ invoer wel een waarschuwing op het scherm komt:

    maar door het klikken op OK, wordt de invoer toch geaccepteerd.

Bedrag

  1. selecteer alle cellen uit de vierde kolom
  2. kies in de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie
  1. voor het bedrag kiezen we bij Toestaan de optie Decimaal.
    Als grenzen wordt verwezen naar 2 cellen met de namen BedrVan en BedrTot.
    LET OP de waardes van de grenzen zijn allebei toegestaan.

Controle

Wil je achteraf weten of er toch nog fouten zitten in de invoer, kies dan in de menutab Gegevens in het blok Hulpmiddelen voor gegevens het vinkje onder de optie Gegevensvalidatie:

Excel omcirkelt alle foute invoer. De naam test2 hoort uiteraard daar niet thuis.

In de lege regel van de tabel tblDeclaraties wordt aangegeven, dat de Datum en de Afd verplicht zijn.

Wil je de ovalen rond de fouten weer verwijderen: kies de derde optie bij Gegevensvalidatie.

Afhankelijke keuzelijst

In het vorige voorbeeld hebben we gebruik gemaakt van Namen om te verwijzen naar bepaalde cellen. Bij de Afd bestaat de toegestane Lijst uit een bereik/range van cellen. Ook die hadden we met behulp van een naam kunnen aangeven.

Op het tabblad AfhKeuze van het Voorbeeldbestand is de kolom Afd van een gegevensvalidatie voorzien door als Bron te kiezen voor de naam Afd.
Dit is een verwijzing naar het bereik G2:I2

Maar ook de bereiken met persoonsnamen in de kolommen hebben een Naam gekregen; exact gelijk aan de omschrijvingen in regel 2.

NB dat is ook de reden dat er een underscore is gebruikt in plaats van een afbreek-streepje. In een Naam is dat laatste teken niet toegestaan. Maar ook een spatie, een /, een \ enzovoort kun je niet gebruiken in de definitie van een naam.

De cellen in de kolom Naam krijgen ook een gegevensvalidatie. Maar het zou natuurlijk mooi zijn als de reeks toegestane namen afhankelijk is van de invoer in de kolom Afd.

Uitgaande van cel D3 willen we dus alleen maar de namen zien die bij de afdeling Dir voorkomen.
Wanneer we bij de Bron van de gegevensvalidatie rechtstreeks zouden verwijzen naar cel C3, dan gaat het niet werken; dan is de naam Dir als enige toegestaan. Gebruiken we echter de functie Indirect dan zijn alle namen uit het bereik Dir toegestaan.

LET OP wanneer achteraf de afdeling wordt gewijzigd dan krijg je niet automatisch een foutmelding dat de persoonsnaam niet is toegestaan. Gebruik dus regelmatig de optie Ongeldige gegevens omcirkelen.
Met behulp van een klein beetje VBA is het makkelijk te regelen dat bij een wijziging van afdeling automatisch de persoon gewist wordt.

Afhankelijke keuzelijst 2

Er zijn (natuurlijk) nog wel meer manieren om een afhankelijke lijst te maken. In het tabblad Afh2 van het Voorbeeldbestand hebben we een meer dynamische methode geïmplementeerd.
Daar is een overzicht te vinden van de 50 gemeentes van Nederland met het grootste inwoner-aantal (op 1 jan 2021). In plaats van dat een gebruiker in de lijst moet gaan zoeken (dat is met 50 namen nog wel te doen, maar bij meer dan 300 is het wat lastiger) kan hij een keuze maken in cel F3, waarna in cel G3 het bijbehorende aantal inwoners wordt getoond.
De mogelijkheden voor de keuze in cel F3 zijn via Gegevensvalidatie beperkt; de toegestane lijst zijn alle namen uit kolom B.

Maar het is natuurlijk handiger om eerst de provincie te kiezen en afhankelijk daarvan pas de gemeentenaam; het aantal mogelijkheden is dan een stuk beperkter.

De validatie in cel G6 is recht toe recht aan.

Ook die van cel G7 komt bekend voor (zie hiernaast): we beperken de gemeentekeuze tot het bereik GemPerProv.

Maar in dat laatste zit nu net de crux: deze naam verwijst niet naar een bereik van cellen maar is via Formules/Namen beheren ingevoerd:
=VERSCHUIVING(
tblProvGem[[#Kopteksten];[Gem]];
VERGELIJKEN(ProvZoek;tblProvGem[Prov];0);
0;
AANTAL.ALS(tblProvGem[Prov];ProvZoek);
1)

In het tabblad Afh2 van het Voorbeeldbestand staat een Excel-tabel (met de naam tblProvGem) met alle betreffende provincies en bijbehorende gemeentes; gesorteerd eerst op Prov en dan op Gem.

Met behulp van de functie Verschuiving bepalen we de cellen met de gemeentes die bij een bepaalde provincie (in de cel met de naam ProvZoek) horen.

  • de functie Verschuiving start in de cel met de koptekst van de kolom Gem
  • maar het resultaat begint net zoveel regels lager als door de functie Vergelijken wordt gegenereerd
  • en 0 kolommen naar rechts of naar links, dus het resultaat staat in de kolom Gem
  • het resultaat bestaat uit zoveel regels als dat de gezochte provincie in de kolom Prov voor komt
  • en de breedte van het resultaat is 1 kolom

NB Wat een gedoe eigenlijk voor iets wat je met een draaitabel met een paar slicers in 1 minuut kunt bouwen. 😉 Zie het tabblad Afh2 in het Voorbeeldbestand.

Maar het gaat om het principe; op een ander moment is deze methode misschien dé oplossing voor uw probleem.

Alternatieve keuzelijsten

cel NIET geselecteerd

Een groot nadeel van gegevensvalidatie is dat je pas weet dat je een keuze kunt maken wanneer de betreffende cel is geselecteerd.

cel WEL gesecteerd

Gelukkig kent Excel nog andersoortige keuzelijsten (zie het tabblad KzLijst van het Voorbeeldbestand). Het kost wel wat meer moeite om dit te implementeren.

Keuzelijst met invoervak

We gaan eerst een Keuzelijst met invoervak maken:

  1. kies in de menutab Ontwikkelaars in het blok Besturingselementen de optie Invoegen
    NB ziet u de menutab niet: klik met de rechtermuisknop ergens in het lint, kies de optie Lint aanpassen en zet bij Hoofdtabbladen het vinkje aan bij Ontwikkelaars
  2. kies binnen de Formulierbesturingselementen de optie Keuzelijst met invoervak
    NB binnen de ActiveX-elementen vindt u een vergelijkbare keuzelijst. Deze is nog iets ingewikkelder te implementeren. Daar krijg je dan wel meer flexibiliteit voor terug. Bijvoorbeeld kan de inhoud qua lettertype en -grootte aangepast worden.
  3. ’teken met de muis’ waar je het invoervak wilt hebben en hoe groot deze moet zijn
  4. geef bij Invoerbereik de cellen op met de toegestane gegevens (in het voorbeeld de cellen B3:B14)
  5. kies bij Koppeling een lege cel (bijvoorbeeld J2)
  6. geef aan hoeveel regels er zichtbaar moeten worden wanneer het invoervak wordt geactiveerd

Maar we zijn nog niet klaar. Wanneer je een keuze maakt in de lijst wordt het overeenkomende volgnummer in de gekoppelde cel geplaatst (hier dus J2).
In het voorbeeld op het tabblad KzLijst hebben we de naam van de gekozen provincie nodig; die halen we in cel K2 op met de formule: =INDEX(tblProv2[Prov];J2)

In cel I3 halen we dan het totaal van het aantal inwoners van de gekozen provincie op:
=SOM.ALS(tblProvGem2[Prov];K2;tblProvGem2[InwAantal])

LET OP: het overzicht bevat alleen de 50 grootste steden, dus het totaal is niet echt het totaal van de provincie!

Keuzelijst

Als laatste kijken we nog naar een andere keuzelijst (zie tabblad KzLijst van het Voorbeeldbestand):

  1. kies in de menutab Ontwikkelaars in het blok Besturingselementen de optie Invoegen
  2. kies binnen de Formulierbesturingselementen de optie Keuzelijst
  3. ’teken met de muis’ waar je de lijst wilt hebben en hoe groot deze moet zijn
  4. geef bij Invoerbereik de cellen op met de toegestane gegevens (in het voorbeeld de cellen D3:D52)
  5. kies bij Koppeling een lege cel (bijvoorbeeld M8)
  6. zorg dat als Selectietype de optie Enkelvoudig is geselecteerd
    NB de andere 2 opties zijn alleen interessant als u deze keuzelijst met behulp van VBA wilt gebruiken

NB in het voorbeeld hebben we de gekoppelde cel ‘verstopt’ achter de keuzelijst.

In cel I8 zoeken we de gemeentenaam op die hoort bij het getal in cel M8.

Het ophalen van het inwoneraantal van die gemeente kan het handigst met de avz-methode (zie Zoeken: Index en vergelijken).

Om het resultaat van de keuze ook snel in de tabel te kunnen terugvinden hebben we daar een Voorwaardelijke opmaak aan toegevoegd.

Functies grafisch weergeven

Excel, , , ,
Voorbeeldbestand: FunctiesGraf.xlsxDownload

Excel is inzetbaar op veel gebieden; ook binnen het (wiskunde-)onderwijs is het goed bruikbaar.
Ik moest daar deze week aan denken, toen ik bedacht hoe ik 20 jaar geleden met iemand tijdens bijlessen heb zitten worstelen om hem het gevoel bij te brengen hoe 1e- en 2e-graads-functies er in grafiekvorm uitzien.

Eerstegraadsfunctie

Zoals we allemaal (?) weten is de algemene vorm van een eerstegraadsfunctie zoals hiernaast weergegeven.
Door voor a en b waarden in te vullen krijg je een specifieke functie.

In het Voorbeeldbestand in het tabblad 1e graads is de algemene functie ingevoerd door in de menutab Invoegen in het blok Symbolen de optie Vergelijking te kiezen.

De cellen C5 en C6 (naast de teksten a en b) hebben een naam gekregen:

  1. kies de menutab Formules
  2. dan in het blok Gedefinieerde namen de optie Naam definiëren
  3. voer een Naam in, in dit geval _a
  4. kies een Bereik. Standaard is de naam geldig voor de hele Werkmap, maar in dit geval moet het bereik beperkt worden tot het werkblad 1e graads
  5. pas zo nodig de verwijzing nog aan en klik op OK

NB Excel accepteert niet alle letters als Naam; ik heb er in dit geval voor gekozen om alle namen door een _ (underscore) te laten voorafgaan.

In cel B8 wordt de functie tekstueel opgebouwd:
=”ƒ(x) = ” & ALS(_a=0;””;ALS(_a=1;”x”;ALS(_a=-1;”-x”;_a&”x”))) & ALS(_b=0;””;ALS(_b<0;” – “;” + “) & ABS(_b))

De diverse tekstblokken worden m.b.v. het &-teken aan elkaar gekoppeld.

In het eerste blok is de ƒ ingevoerd via Invoegen/Symbool. Kies daar allereerst rechtsonder de optie ASCII (decimaal) en blader dan door de mogelijkheden.
Wiskundige tekens kunnen ook op de volgende site gevonden worden:  Lijst_van_wiskundige_symbolen; gebruik daar kopiëren en plak dan in Excel.

Met de diverse ALS-functies wordt de opmaak geregeld, zodat er bijvoorbeeld geen 0x of 1x in het resultaat komt te staan.

De ABS-functie levert de absolute waarde van een getal; het resultaat is dan altijd groter of gelijk aan 0.

Om de grafiek te kunnen tekenen hebben we een tabel nodig met de bij de functie horende x,y-waarden. In het tabblad 1e graads van het Voorbeeldbestand worden 100 combinaties berekend in de cellen vanaf B32. In cel C32 komt dan de formule =_a*B32+_b

NB Om het effect van veranderingen in a of b goed te kunnen zien zijn de assen ‘vastgezet’: ze lopen allebei van -5 tot +5.

Bij  eerstegraadsfuncties zijn de snijpunten met de assen altijd belangrijk. Die worden in de cellen C10 en C11 berekend met de formules =”(0 , “&_b&”)”, respectievelijk =ALS(_a=0;”geen”;”(“&-_b/_a&” , 0)”)

Omdat het resultaat van de laatste formule vaak geen geheel getal oplevert, is het inzichtelijker om het resultaat zo nodig als een breuk weer te geven (zie cel C15):
=ALS(_a=0;”geen”;”(“&TEKST(-_b/_a;”# ??/??”)&”, 0)”)

M.b.v. de functie TEKST wordt een opmaak meegegeven aan het resultaat -b/a: eventueel een geheel getal (#) en als het nodig is een breuk (met maximaal 2 cijfers in de teller en noemer).

Om alle overbodige spaties te verwijderen, kan de functie SUBSTITUEREN worden gebruikt (zie bijvoorbeeld cel C19 in het tabblad 1e graads).

In de grafiek is ook de functie weergegeven. Dit is als volgt gedaan:

  1. selecteer de cel met de functie en kies kopiëren (Ctrl-C)
  2. klik ergens anders op het werkblad met de rechter muistoets
  3. kies Plakken speciaal …
  4. en in het vervolgscherm de laatste optie van Andere plakopties (Gekoppelde afbeelding)

De inhoud van de oorspronkelijke cel wordt dan dynamisch in een grafisch object weergegeven. Dit object kan via slepen overal in de sheet geplaatst worden, dus ook in de grafiek.

Tweedegraadsfunctie

De opbouw van het tabblad 2e graads van het Voorbeeldbestand is vergelijkbaar met bovenstaande.

Het bepalen van de eventuele snijpunten met de x-as is echter wat ingewikkelder. Weet u nog?

Maar dit levert alleen maar een resultaat op als het getal onder de wortel (de discriminant) niet kleiner is dan nul; dus dat wordt eerste even gecontroleerd.

In C14 staat daarom de volgende formule:
=ALS(Oplos?=”geen”;”geen”;”(“&(-_b-WORTEL(Discr))/(2*_a)&” , 0)”)

Derdegraadsfunctie

In het tabblad 3e graads van het Voorbeeldbestand wordt de grafiek van een derdegraadsfunctie weergegeven. Het bepalen van de snijpunten met de x-as wordt nu wat lastiger; dat valt buiten de scope van dit artikel.

 

Unieke waarden

Excel, , , , , , , ,
Voorbeeldbestand: Uniek.xlsxDownload

Er bestaan nogal wat situaties, waarbij je wilt weten of items allemaal verschillend zijn of niet.
En, als ze niet allemaal verschillend zijn, welke unieke exemplaren komen er dan voor?

In Excel kennen we een dergelijk probleem ook: welke unieke waarden komen er in een rij of kolom voor?
 

Deze keer zullen we enkele methoden de revue laten passeren; van simpele tot complexe, van opmaak tot selectie.

Voorwaardelijke opmaak

In het Voorbeeldbestand heb ik op het tabblad Data1 een tabel opgenomen met in de kolommen oa de afdelingen en het soort bedrag.

Alle cellen in die kolommen hebben een voorwaardelijke opmaak gekregen:

  1. selecteer cel C3
  2. kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak
  3. klik op de optie Nieuwe regel
  4. in het vervolgscherm kiezen we als Type: Een formule gebruiken
  5. en de formule wordt:
    =AANTAL.ALS(C$3:C3;C3)=1
    ofwel: als in het bereik C$3:C3 de waarde uit cel C3 1 keer voor komt mag de cel een opmaak krijgen.
    Dat is natuurlijk altijd waar!
    Maar wat gebeurt er als we deze voorwaardelijke opmaak ook ‘loslaten’ op een cel lager? Excel interpreteert de formule dan als =AANTAL.ALS(C$3:C4;C4)=1 en kijkt of de waarde uit C4 slechts 1 keer voor komt in het bereik C$3:C4.
    Ook wanneer deze formule in kolom D wordt toegepast is de controle precies wat we zoeken.
    NB om de formule ook in andere kolommen te kunnen gebruiken is alleen de 3 absoluut gemaakt als begin van het bereik (vandaar het $-teken vóór de 3) en is niet het meer voor de hand liggende $C$3 gebruikt.
  6. voeg nog de gewenste opmaak toe (hier is voor een licht-groene opvulling van de cel gekozen)
  7. klik op OK
  8. zorg dat de opmaak op alle relevante cellen van kolom C en D wordt toegepast:
    * kies in de menutab Start in het blok Stijlen de optie Voorwaardelijke opmaak
    *     klik op de optie Regels beheren
    * vul bij Van Toepassing op het gewenste bereik in (in dit geval $C$3:$D$32)

NB1 het overzicht is een Excel-tabel. Daarom zal Excel, wanneer er een regel aan wordt toegevoegd, ook de Voorwaardelijke opmaak direct meenemen; het bereik wordt automatisch aangepast.

NB2 welke unieke waarden er in een tabel-kolom zitten ziet u ook direct door op het ‘vinkje’ achter een kolomkop te klikken.

NB3 het vorige ‘recept’ kan met 1 stap ingekort worden: selecteer in de eerste stap niet één cel, maar het totale bereik waar de opmaak voor moet gelden; stap 8 kan dan vervallen.

Via draaitabel

De vorige oplossing was een optische methode om unieke waarden te vinden; bij een grote tabel niet echt handig.

Het maken van een draaitabel is echter een simpele en doeltreffende manier om snel de unieke waarden in een kolom te vinden:

  1. selecteer een cel in de tabel met gegevens, bijvoorbeeld B2
  2. kies in de menutab Invoegen in het blok Tabellen de optie Draaitabel
  3. in het vervolgscherm is alles al goed ingevuld; de bron voor de draaitabel is de totale Excel-tabel (met de naam tblData1)
  4. klik OK
  5. sleep het veld Afd naar de Rijlabels en daar zijn alle unieke afdelingsnamen!
  6. hetzelfde kunt ook doen voor de Soort, maar u kunt beter de vorige draaitabel uitbreiden: sleep Soort naar Kolomlabels.
    Sleep ook nog Bedrag naar het Waarde-gebied en u hebt meteen een overzicht van de verdeling van de bedragen naar afdeling en soort!

    Zie ook het tabblad Data1 in het Voorbeeldbestand.

Even een uitstapje: wilt u weten wat het totaal van Srt1 is dan kunt u ook de volgende formule gebruiken: =SOM.ALS(tblData1[Soort];”Srt1″;tblData1[Bedrag]) maar beter is het volgende:

  1. plaats in een bepaalde cel de waarde Srt1, bijvoorbeeld in G14 (zie het tabblad Data1 in het Voorbeeldbestand)
  2. in de cel daarnaast gaan we een formule opbouwen:
    tik in =som.als(
  3. wijs met de cursor de bovenkant van de kolom Afd aan (de cursor wordt dan een zwarte pijl naar beneden) en klik.
    De formule wordt dan aangevuld met tblData1[Soort] ofwel alle cellen in de kolom Soort van de Excel-tabel tblData1.
  4. tik in ; (de punt-komma) en klik op cel G14 en plaats weer een ;
  5. als laatste voegen we de kolom Bedrag toe, die onder voorwaarden gesommeerd moet worden; dat gaat op dezelfde manier als in stap 3.
  6. nog een haakje sluiten en Enter

Door in G14 een andere soort in te typen, krijgt u daarvan het totaal.

Maar wat als er aan 2 (of meer voorwaarden moet worden voldaan?

U moet dan de functie SOMMEN.ALS gebruiken.
Deze werkt net iets anders: eerst geeft u het bereik op, waarvan u de som wilt bepalen, dan het eerste criterium-bereik met daarachter het criterium zelf, daarna een nieuw criterium-bereik met criterium etc.

Gegevensvalidatie

Maar in G14 kunnen nu willekeurige teksten worden ingevoerd. Dat is natuurlijk niet de bedoeling; we willen alleen bestaande soorten kunnen opgeven.

Dit gaat eenvoudig met gegevens-validatie:

  1. plaats de cursor in de cel die u wilt valideren (bijvoorbeeld cel G19 zoals in het tabblad Data1)
  2. kies in de menutab Gegevens in het blok Hulpmiddelen voor gegevens de optie Gegevensvalidatie
  3. kies bij Toestaan: de optie Lijst
  4. en bij Bron: kiest u de kolom Soort uit de Excel-tabel op de manier zoals hierboven aangegeven.
    NB Excel vertaalt de kolom direct naar daadwerkelijke cellen, maar als de tabel groter (of kleiner wordt) verandert dit bereik mee.
  5. klik op OK.

Oeps, het resultaat is niet helemaal wat we hoopten. In de keuzelijst staan alle voorkomens uit de kolom Soort, we willen natuurlijk alleen unieke waarden hebben!

Unieke waarden via filtering

Om een lijstje te maken van unieke waarden (maar u wilt geen draaitabel gebruiken) dan kent Excel nog een andere aanpak:

  1. kies in de menutab Gegevens in het blok Sorteren en filteren de optie Geavanceerd
  2. in het Uitgebreid filter kiezen we als Actie de optie Kopiëren naar andere locatie
  3. voor het Lijstbereik selecteert u alle gegevens uit de kolom Afd van de tabel, INCLUSIEF de kop
  4. bij Kopiëren naar vult u de cel in, waar de unieke waarden moeten komen (in het voorbeeld heeft deze cel de naam AfdKop gekregen)
  5. vergeet niet het vinkje te plaatsen bij Unieke records!

In het tabblad Data1 van het Voorbeeldbestand ziet u het resultaat van deze actie.

Ook voor de kolom Soort zijn de unieke waarden opgehaald.

NB1 Wat direct opvalt is dat er geen (automatische) sortering plaats vindt. De volgorde is gelijk aan de volgorde in de bron.

NB2 als er regels aan de bron-tabel worden toegevoegd dan moeten de ophaal-acties opnieuw worden uitgevoerd.
Excel ‘onthoudt’ de gegevens van de laatste actie. Dit is ook te zien in het overzicht Namen beheren in de menutab Formules.

LET OP1 de locatie, waar de unieke waarden moeten komen, moet op hetzelfde tabblad staan als waar de brongegevens zich bevinden.

LET OP2 vergeet niet om bij het Uitgebreid filter ook de kop van de kolom mee te nemen, anders krijgt u de eerste waarde dubbel. Dit is nergens in de Microsoft-documentatie terug te vinden!

Gegevensvalidatie

Op basis van deze unieke waarden kunnen nu makkelijk Gegevensvalidaties worden gemaakt.
Maar om het geheel dynamisch te houden (als er nieuwe unieke waarden bijkomen moet dit automatisch worden meegenomen bij de gegevensvalidatie) wordt als bron-lijst niet  een hard bereik genomen (bijvoorbeeld R3:R5).

Nee, we creëren een dynamisch bereik door een naam toe te voegen:

  1. kies in de menutab Formules in het blok Gedefinieerde namen de optie Naam definiëren
  2. vul een naam in (bijvoorbeeld AfdUniek)
  3. en plaats de formule
    =VERSCHUIVING(AfdKop;1;0;AANTALARG(Data1!$R:$R)-1)
    in het vak Verwijst naar.

Bij gegevensvalidatie (zie cel G20 in het tabblad Data1 van het Voorbeeldbestand) gebruikt u als Bron de naam AfdUniek (vergeet niet het =-teken daar voor te zetten!).

In de cellen G21 en H21 is op een vergelijkbare manier gegevensvalidatie toegepast, maar daarbij zijn als bron voor de verschuiving 2 simpele draaitabellen gebruikt.

LET OP worden er regels aan de oorspronkelijke tabel toegevoegd, vergeet dan niet de draaitabellen te vernieuwen anders bevatten de gegevensvalidaties niet de meest recente gegevens.

 

Formules, Namen, Eigen functies

Excel, VBA (Visual Basic for Applications), ,
Voorbeeldbestand: FormulesMac.xlsmDownload

De grote kracht van Excel (en ieder ander spreadsheet-programma) is dat je gebruik kunt maken van allerlei formules. Of het nu berekeningen zijn of logische constructies (bijvoorbeeld met behulp van de ALS-functie) of combinaties hiervan: ieder spreadsheet wordt met formules ‘aan elkaar geknoopt’.

Bij de opbouw van formules is het zeer aan te raden om Namen te gebruiken in plaats van cel-verwijzingen als C4 of bereik-aanduidingen als F5:F20. De leesbaarheid van formules gaat daarmee met sprongen vooruit.

In dit artikel daarom (nogmaals) aandacht voor het maken en gebruik van Namen. Ook het opslaan van formules in een Naam komt aan bod. Maar ik zal ook laten zien dat het gebruik van Eigen functies (met behulp van VBA) veel flexibeler is.

Namen definieren

Een naam vastleggen in Excel kan op diverse manieren:

  1. de eerste is een rechtstreekse handmatige invoer (zie tabblad VB1 in het Voorbeeldbestand): selecteer een cel (in dit voorbeeld C2), klik in het Naamvak, links van de Formulebalk, en tik de gewenste naam in (hier dus Korting1) en druk op Enter.
    LET OP een naam mag geen spaties bevatten en geen celverwijzing voorstellen zoals A1 of EXP10. Het is ook af te raden om bestaande functienamen in te voeren (zoals Som); dit is voor Excel geen echt probleem maar voor de gebruiker van het spreadsheet kan het zeer verwarrend zijn.
    NB1 gebruikelijk is om op de plaats waar je een spatie zou willen zetten het _-teken te plaatsen (de underscore; bijvoorbeeld Korting_1) of het tweede gedeelte met een hoofdletter te laten beginnen (bijvoorbeeld KortPerc).
    NB2 worden er meerdere cellen tegelijkertijd geselecteerd, dan krijgt het totale bereik de ingevoerde naam; de afzonderlijke cellen hebben dan geen naam.
  2. plaats een omschrijving links of boven de cel die een naam moet krijgen.
    Selecteer de omschrijving en de betreffende cel (in het voorbeeld B2:C2) en kies in de menutab Formules in het blok Gedefinieerde namen de optie Maken obv selectie.
    In het pop-up venster heeft Excel in dit geval al ‘gezien’ dat er links van de cel een omschrijving staat; deze zal als naam voor cel C2 worden gebruikt.
    NB1 als er spaties staan in de omschrijving dan vervangt Excel deze automatisch door een underscore.
    NB2 stelt de omschrijving een celverwijzing voor dan plaatst Excel een underscore achter de naam (Rij1 wordt dan Rij1_) .
    NB3 zijn er naast de omschrijving meerdere cellen geselecteerd dan krijgt het totale bereik van die cellen de tekst van de omschrijving als naam.
  3. selecteer de cel (of cellen) die een naam moet krijgen en kies in de menutab Formules in het blok Gedefinieerde namen de optie Naam definiëren.
    Voer een naam en eventueel een opmerking in. Controleer of de verwijzing klopt.
    Het Bereik voor een naam staat standaard op Werkmap; de naam kan overal gebruikt worden. In dit geval is deze korting alleen maar geldig op het tabblad Vb1, dus moet het bereik daaraan aangepast worden.
    NB klik je op het ‘vinkje’ achter het Naamvak, dan zie je alle namen die voor dit tabblad geldig zijn (inclusief de namen die voor de hele werkmap gelden).
  4. kies in de menutab Formules in het blok Gedefinieerde namen de optie Namen beheren en klik op de button Nieuw

NB wanneer op een van deze manieren een naam wordt ingevoerd zal Excel de verwijzing standaard ´absoluut´ maken; zie de $-tekens.

Namen beheren

Wil je kijken welke namen er in het Excel-bestand worden gebruikt en/of deze aanpassen kies dan in de menutab Formules in het blok Gedefinieerde namen de optie Namen beheren.

Hier is het mogelijk om een nieuwe naam toe te voegen of een bestaande te verwijderen.
Ook kan een naam bewerkt worden.

LET OP Bij bewerken kunnen de naam, opmerkingen en verwijzing aangepast worden, dus NIET het bereik. Wil je deze wijzigen dan moet je een nieuwe naam toevoegen met een ander bereik en de andere verwijderen.

Gebruik van Namen 1

In het tabblad Vb1 van het Voorbeeldbestand zijn netto-bedragen berekend met behulp van een zelf-gedefinieerde naam. In cel D7 staat de formule =C7*(1-Korting1).
Wanneer deze naar beneden gekopieerd wordt, wijzigt C7 automatisch in C8 etcetera. De korting is via de naam absoluut gedefinieerd en verwijst dus ook bij het kopiëren altijd naar cel C2.

Bij het invoeren van de formule laat Excel bij het intikken van de letters ko alle mogelijkheden zien, die voor dit tabblad gelden. De opties met ƒx zijn functies binnen Excel (standaard of eigen functies, zie hierna), de andere zijn namen.
Dubbelklik op de gewenste functie of naam of selecteer deze met de cursor en druk dan op de tab-toets.

Gebruik van Namen 2

Bij het definiëren van namen hoeft u zich niet te beperken tot cel-verwijzingen. Het is ook mogelijk om formules op te nemen.

Voor het tabblad Vb2 in het Voorbeeldbestand is voor de naam Kort2a als verwijzing de formule =1-Vb2!$C$2 ingevoerd. Dat houdt in dat de berekening van het netto-bedrag er dan iets anders kan uitzien:
het oorspronkelijke bedrag wordt vermenigvuldigd met Kort2a. Het mag duidelijk zijn dat vooral bij uitgebreide/complexe formules hierdoor het spreadsheet overzichtelijker blijft.

In kolom E staat een vergelijkbare formule, maar daar wordt gebruik gemaakt van Kort2b, die gedefinieerd is als =1-‘Vb2’!Korting2

LET OP wanneer binnen zo’n formule gebruik wordt gemaakt van een naam, die niet voor de hele werkmap geldig is, dan moet deze naam voorafgegaan worden door de naam van het tabblad, gevolgd door een !-teken. De aanhalingstekens zijn eigenlijk alleen maar nodig als de naam van het tabblad spaties bevat.

Gebruik van Namen 3

In het vorige voorbeeld is het kortingspercentage via een Naam omgezet in een kortingsfactor. Maar ook het netto-bedrag kan via zo’n constructie worden bepaald; zie het tabblad Vb3 in het Voorbeeldbestand.
Netto3a is gedefinieerd als
=’Vb3′!C7*(1-‘Vb3!Korting3)

LET OP C7 is relatief (zonder $-tekens) gedefinieerd. En dit geredeneerd vanuit cel D7. Dit betekent dat dezelfde naam Netto3a vanuit cel D8 verwijst naar cel C8. Selecteer maar eens een cel ergens in kolom D en kies dan Namen beheren. Of kies de optie Broncellen aanwijzen in het blok Formules controleren.

Maar wanneer we de naam Netto3a in een andere kolom plaatsen gaat het mis. Zie cel H21 in het tabblad Vb3. De naam Netto3a hanteert nu als bron de cellen uit kolom G.

Met de formule in Netto3b is dit opgelost: =’Vb3′!$C7*(1-!Korting3)
Dus de kolom is absoluut (altijd C), maar de rij relatief; zie cel H23.

NB de exacte locatie van de cel met de naam Korting3 is weggelaten; alleen het !-teken is voldoende. Een nadeel hiervan is dat Broncellen aanwijzen dan niet meer volledig is. In het Voorbeeldbestand is wel de naam van het tabblad opgenomen omdat Mac-gebruikers anders in de problemen komen.

In het tabblad Vb3 van het Voorbeeldbestand is nog een derde naam (Netto3c) opgenomen met als formule =’Vb3′!$C7:$C40*(1-!Korting3)

De werking is hetzelfde maar het voordeel is dat je nu het totaal van de netto-bedragen kunt bepalen met de formule =SOM(Netto3c). Deze formule moet wel ergens in rij 7 staan; zie het resultaat in de cellen H7 en H8.

LET OP Excel weet niet dat de inhoud van zo’n naam een formule is en zal het resultaat niet automatisch herbereken (bijvoorbeeld bij het wijzigen van het kortingspercentage). Daarom is de SOM nog aangevuld met +0*NU(); aangezien NU ieder moment van waarde verandert zal Excel de totale formule bij iedere wijziging van het spreadsheet wel opnieuw berekenen.

Gebruik van Namen 4

Wanneer gebruik wordt gemaakt van Excel-tabellen (en zoals we allemaal weten heeft dat grote voordelen) dan moet goed opgelet worden bij het definiëren van Namen.

In het tabblad Vb4 van het Voorbeeldbestand bestaat het systeem uit de tabel tblNetto. Binnen dat tabblad is de naam Netto4a gedefinieerd als =tblNetto[Bedrag]*(1-!Korting4)

De opzet van de formule is vergelijkbaar met de naam Netto3c. De werking is nu echter anders: in iedere rij komt nu hetzelfde resultaat, namelijk het nettobedrag dat hoort bij de eerste regel. De SOM-formule werkt wel goed en is nu onafhankelijk van de plaats.

Voor de juiste berekening in de tabel hebben we een andere naam nodig:
=’Vb4′!$C7*(1-!Korting4) of =tblNetto[@Bedrag]*(1-!Korting4)

NB de @ zorgt er voor dat met het bedrag in de betreffende regel wordt gerekend.

Eigen functie

Zoals hiervoor duidelijk mag zijn geworden kleven er aan het gebruik van formules in Namen wel nogal wat problemen of laten we zeggen aandachtspunten.
Wel is een voordeel van die systematiek dat beginnende gebruikers niet zomaar formules kunnen aanpassen.

Met een klein beetje VBA lossen we voorgaande aandachtspunten dat snel en vakkundig op:

  1. druk op Alt-F11 of kies de optie Visual Basic in het blok Programmacode van de menutab Ontwikkelaars
  2. zoek het betreffende project (in dit geval Formules.xlsm) en klik daarop
  3. als er nog geen Modules zijn, kies dan eerst Invoegen/Module
  4. dubbelklik op een module en plaats daar de volgende functie-definitie
    Function BerekenNetto(Bedr, KortPerc)
          BerekenNetto = Bedr * (1 – KortPerc)
    End Function
    De eigen functie BerekenNetto kent 2 parameters: een bedrag en een kortingspercentage. Het resultaat van de gewenste berekening wordt aan de functienaam gekoppeld.

In het tabblad Vb5 van het Voorbeeldbestand wordt deze eigen functie gebruikt. Omdat we binnen een Excel-tabel werken kunnen we weer naar de Bedrag-kolom wijzen (denk aan de @).

Maar de formule kan ook ergens anders in het tabblad staan, en wordt dan iets als =BerekenNetto(C7;Korting5)

Overzicht Namen

Zeker als er veel namen gebruikt worden in een Excel-systeem is het belangrijk om het systeem goed te documenteren.
Eén van de onderdelen van de documentatie moet een goede beschrijving van de verschillende Namen zijn (zie het tabblad Docu in het Voorbeeldbestand).

Gelukkig kunnen we een groot gedeelte automatisch laten genereren:

  1. selecteer een tabblad met één of meer geldige namen
  2. selecteer ergens een lege cel; zorg dat rechts en daaronder genoeg lege cellen zijn
  3. kies in de menutab Formules in het blok Gedefinieerde namen de optie Gebruiken in formule; in het vervolgscherm kiest u Namen plakken
  4. wanneer u dan op Lijst plakken klikt, wordt er een overzicht in het tabblad geplaatst
  5. kopieer de gegevens naar een documentatie-tabblad
  6. herhaal alle stappen voor ieder tabblad

NB alle namen die geldig zijn voor de hele werkmap komen dan meerdere keren voor; dus is er nog wat schoning vereist.

 

Grafische analyse in Excel

Excel, , , ,
Voorbeeldbestand: AEX.xlsxDownload

Onlangs kwam ik op de website www.exceluser.com enkele nuttige artikelen tegen: 3 Simple Tricks to Improve Analytical Charting in Excel, 3 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.