Categorie archief: VBA (Visual Basic for Applications)

Functie Indirect


LET OP: na het downloaden de extensie van het bestand wijzigen in xlsb


De Excel-functie Indirect heb ik de afgelopen tijd weer in menig artikel en project gebruikt.

In de praktijk blijkt het lang niet voor iedereen (direct) helder wat de functie doet en waar die dus voor ingezet kan worden.

Aanleiding voor een artikel; inclusief wat VBA-programmering om het voorbeeld uit het vorige artikel gebruikersvriendelijker te maken.

In 2015 heb ik al eens over deze functie geschreven. Deze keer kiezen we een andere invalshoek voor de uitleg en komen andere facetten van de functie aan bod. Zoals het spreekwoord luidt: dubbel genaaid houdt beter!

Indirect (versie 1)

Op het tabblad Cel van het Voorbeeldbestand is cel B2 gevuld met een tekst die aangeeft welke dag het vandaag is:
=”Vandaag is het “&TEKST(VANDAAG();”d-mm-jjjj”)

Cel B4 bevat de tekst B2 en in cel D4 staat de verwijzing naar cel B4:
=B4

Het resultaat in cel D4 is uiteraard de inhoud van cel B4, dus de tekst B2.

In cel D5 staat de formule =INDIRECT(B4)

Het resultaat is geen directe verwijzing naar cel B4, maar naar de inhoud van cel B4, dus naar cel B2. Een Indirecte verwijzing dus, vandaar de naam van de functie.

Simpel toch!

Nog een (iets ‘intelligenter’) voorbeeld:
in kolom B staan 5 namen. In cel E11 staat een indirecte verwijzing naar cel B12, waardoor de derde naam als resultaat wordt weergegeven.
De tussenstap (cel E10) is enigszins dynamisch gemaakt:
=”B”&D11+9
Dus aan de tekst B wordt het resultaat van de optelling van de inhoud van cel D11 en het getal 9, gekoppeld.

Wijzigen we nu cel D11 dan zal ook het resultaat in cel E11 veranderen.
Maar echt fool-proof is deze constructie niet: voeg maar eens een regel in boven regel 10.

In cel F10 is het statische getal 9 vervangen door het dynamische RIJ(B10)-1.

NB voeg je vóór kolom B een kolom in, dan zal deze formule ook niet meer goed werken. Wil je dit voorkomen dan moet de tekst B in de formule ook dynamisch gemaakt worden.

Indirect (versie 2)

De tussenstap in het vorige voorbeeld (in cel E10 wordt een verwijzing naar een andere cel geconstrueerd) kunnen we overslaan: de parameter die aan de functie Indirect wordt meegegeven kan ook een tekst zijn.

In het tabblad Tekst van het Voorbeeldbestand bevat cel D6 de formule =INDIRECT(“B4”). Het resultaat is de inhoud van cel B4.
De functie zorgt nu voor een indirecte verwijzing door een tekst om te zetten naar een bereik/range en daar de inhoud van op te halen.
De tekst moet het A1-formaat hebben, één of twee letters voor de kolom- en een getal voor de rij-aanduiding.

Deze tekst-versie kent nog een andere variant: cel D7 bevat de formule =INDIRECT(“R4K2”;ONWAAR) en geeft hetzelfde resultaat als de formule in D6. Hierbij wordt het zogenaamde R1K1-formaat voor de tekst gebruikt: eerst een getal voor de rij en dan een getal voor de kolom. Wel moet aan de functie een tweede parameter meegegeven worden met de waarde ONWAAR.

NB de tweede parameter is niet verplicht als je het A1-formaat gebruikt. Maar deze mag wel altijd ingevuld worden; heeft de tekst het A1-formaat dan moet deze tweede parameter de waarde WAAR hebben.

LET OP wanneer deze spreadsheet in een andere taal-versie van Excel wordt ingeladen dan kan het zijn dat de formule niet goed werkt. Om het bijvoorbeeld in de Engelstalige versie te laten werken moet de formule in cel D7 gewijzigd worden in =INDIRECT(“R4C2”;ONWAAR)

In de cellen G3 en G11 worden de twee varianten nog eens in een ander voorbeeld gebruikt:

De kracht van de tekst-versie van de Indirect-functie ligt er uiteraard in, dat de tekst op allerlei manieren geconstrueerd kan worden (zoals in de vorige voorbeelden in eenvoudige vorm wordt getoond).

Cel G12 bevat de formule =INDIRECT(TEKST(G9*10+G10;”R0K0″);ONWAAR). Op deze manier verwijzen we ook naar cel F4. Daarbij is gebruik gemaakt van de functie Tekst waarmee we de weergave van een getal kunnen bepalen. Door de rij-waarde te vermenigvuldigen met 10 en daar de kolomwaarde bij op te tellen krijgen we (in dit geval) de waarde 46. Met de notatie-aanduiding R0K0 geven we aan dat het laatste cijfer van het getal 46 achter de K moet komen en de overige cijfers tussen de R en de K .

Dit gaat echter fout, als we een verwijzing maken naar een kolomwaarde groter dan 9 (probeer maar eens door in cel G10 de waarde 10 in te voeren).

In cel G13 is dit opgelost: =INDIRECT(TEKST(G9*100+G10;”R0K00″);ONWAAR)

Het gebruik van een harde tekst (dus geen samengestelde tekst zoals hiervoor) als parameter kan bijvoorbeeld handig zijn om te testen of een bepaalde cel niet is verplaatst (al dan niet door het invoegen of verwijderen van rijen of kolommen).

In cel G15 wordt getest of cel F17 wel of niet de waarde Maand bevat.
Zo niet dan krijg je een waarschuwing.

Uiteraard hebben we dat met voorwaardelijke opmaak nog wat duidelijker gemaakt (groen is de standaard-kleur, rood als de inhoud afwijkt van de waarde “Ok”).

Gegevensvalidatie 1

In het Voorbeeldbestand vindt u in het tabblad GegVal1 een ‘systeem’ waarmee we de maximumsnelheid van een vervoermiddel kunnen ophalen uit een tabel. Daarbij is gebruik gemaakt van een zogenaamde afhankelijke Gegevensvalidatie (zie het vorige artikel). De keuzelijst in cel F3 is afhankelijk van de keuze in cel F2.

In cel F4 wordt de maximale snelheid van het gekozen merk opgehaald met de functie Vert.Zoeken. Een (flexibeler) alternatief staat in cel F5: =INDEX(C9:C15;VERGELIJKEN(F3;B9:B15;0))

Maar dat ophalen kan ook met behulp van de functie Indirect. Zie cel F6:
=INDIRECT(“C”&RIJ(B8)+VERGELIJKEN(F3;B9:B15;0))

NB1 door niet hard te verwijzen naar rij 8, maar de functie RIJ(B8) te gebruiken werkt de formule ook nog als er regels worden toegevoegd of verwijderd.

NB2 als je rijen toevoegt of verwijdert (of kolommen) dan krijg je een VBA-foutmelding. Kies de optie Beëindigen. De reden van deze foutmelding zullen we hierna uitleggen (en wat daar aan te doen!)

Zoals in het vorige artikel aangegeven zou het mooi zijn als het systeem automatisch het Merk leeg maakt als je het type Vervoermiddel wijzigt.

Daarom hebben we aan het tabblad GegVal1 een VBA-routine gekoppeld (klik rechts op het betreffende tabje onderaan en kies de optie Programmacode weergeven):

Iedere keer, dat er iets is gewijzigd in dit tabblad, wordt deze routine automatisch aangeroepen (Worksheet_Change). Welke cel of cellen er zijn gewijzigd wordt door Excel meegegeven in de parameter Target. We willen alleen maar iets doen (namelijk het merk leeg maken) als het type vervoermiddel wijzigt (cel F2). Daarom wordt in de eerste regel van de sub-routine gecontroleerd of Target daar gelijk aan is. Als dat niet zo is, gaat de routine verder met de opdracht na de laatste End If (de subroutine beëindigen dus).
De variabele VervMiddel bevat de inhoud van cel F2, zoals die was toen de routine vorige keer is aangeroepen. Daarom kijken we of die veranderd is. Is de huidige inhoud niet gelijk aan VervMiddel dan wordt cel F3 leeg gemaakt en maken we de variabele VervMiddel gelijk aan de inhoud van F2.

NB1 ik weet het: de routine is niet helemaal netjes geprogrammeerd. Wanneer je de eerste keer cel F2 wijzigt weet het ‘systeem’ helemaal niet wat er vorige keer in F2 stond. Dus F3 zal dan altijd leeg gemaakt worden.

NB2 de declaratie van de variabele VervMiddel gebeurt buiten de routine. Zouden we dat niet doen dan wordt deze variabele bij iedere Change opnieuw gedeclareerd en is dan dus altijd leeg.

NB3 deze Excel-werkmap bevat VBA. Bij het opslaan van het bestand moet je dan kiezen voor de extensie xlsm of xlsb.

NB4 verschuiven de cellen F2 en F3 dan moet de VBA aangepast worden. In cel I2 is daarom een controle met een waarschuwing ingebouwd (voeg maar eens een kolom in vóór kolom B).

Maar wat gebeurt er als je een rij of kolom toevoegt of verwijdert? Uiteraard wordt de routine Worksheet_Change aangeroepen, maar de parameter Target bevat helemaal niets. VBA wil dit ‘niets’ vergelijken met een Range en dat kan niet; vandaar dat we een foutmelding krijgen. Dat moeten we natuurlijk wel oplossen.

Gegevensvalidatie 2

In het tabblad GegVal2 van het Voorbeeldbestand staat een simpelere versie van het vorige voorbeeld. In de VBA-routine zit een belangrijk verschil:

In de eerste regel wordt via de VBA-functie Intersect gekeken wat de overlap tussen Target en Range(“F2”) is. Als er geen overlap is (ook als Target leeg is) dan wordt de rest van de routine overgeslagen. Nu krijgen we die vervelende foutmelding van VBA niet meer.

Indirect en Namen

Bij de voorbeelden met gegevensvalidatie is al gebruik gemaakt van Excel-namen. Overal waar je verijst naar een cel of naar een bereik van cellen kun je ook namen gebruiken, dus ook bij de functie Indirect.

Hiernaast staat een voorbeeld (zie het tabblad Namen in het Voorbeeldbestand).

De cellen C3:C12 hebben de naam Een en de cellen C13:C22 de naam Twee.

In cel F2 kun je (met behulp van gegevensvalidatie) kiezen uit deze twee namen.

De formule =SOM(INDIRECT(F2)) in cel F3 telt die cellen bij elkaar op, die door de naam in cel F2 worden bepaald.

LET OP Deze formule werkt alleen als de gegevens netjes met blokken bij elkaar staan (dus soort Een bij elkaar en ook soort Twee). De functie Indirect kan alleen een bereik van cellen verwerken als die cellen op elkaar aansluiten.

In de cellen F4 t/m F7 staan formules die ook het juiste resultaat opleveren, wanneer de gegevens niet netjes gesorteerd zijn.

Voorbeeld van het R1K1-formaat

In cel I4 van het tabblad R1K1 van het Voorbeeldbestand staat een formule die gegevens uit een tabel ophaalt, wanneer de gewenste rij en kolom zijn opgegeven.

Daaronder staan twee varianten; bij eentje wordt de Excel-functie Adres gebruikt.

In het blokje daaronder kunt u zelf ‘spelen’ met de mogelijkheden van deze Adres-functie.

Unieke gegevens selecteren

Hiernaast ziet u een overzicht van 3 lijstjes met daarop verschillende soorten fruit (zie het tabblad Uniek in het Voorbeeldbestand).

Als we willen weten welke soorten voorkomen zoeken we dus de unieke elementen van dit overzicht. Dat is een behoorlijke uitdaging. Maar ook hier komt de functie Indirect van pas.

In cel F3 staat de formule:
=INDIRECT(
TEKST(
MIN(
ALS(
($B$3:$D$10<>””)*AANTAL.ALS($F$2:F2;$B$3:$D$10)=0);
RIJ($3:$10)*100+KOLOM($B:$D);7^8)
);”R0K00″);
ONWAAR)
&””
Wil je ‘zien’ hoe de formule werkt, gebruik dan in de menutab Formules de optie Formule evalueren.

Aangezien het hier een zogenaamde matrix- of array-formule betreft moet deze worden afgesloten door op Ctrl-Shift-Enter te drukken.
Kopieer de formule nu net zo lang naar beneden totdat een of meer lege cellen het resultaat is.

Indirect en bereik-verwijzing

Nog een laatste voorbeeld van het gebruik van de functie Indirect; nu om een bereik van cellen aan te geven (zie het tabblad Range van het Voorbeeldbestand).

In cel F3 halen we vanuit een maand-overzicht het totaal tot en met een bepaalde maand op. Deze maand kan in cel F2 ingevoerd worden.

NB1 cel F2 heeft een gegevensvalidatie, zodat er een keuzelijst ontstaat.
Ook al lijkt deze toegestane lijst absoluut ($B$3:$B$14), wanneer je een rij toevoegt aan de tabel dan wordt deze lijst ook automatisch uitgebreid.

NB2 in cel F3 wordt voor het vergelijken gemakshalve naar de hele kolom B gekeken; wanneer je een complex en/of groot systeem hebt gebouwd kan dit vertragend werken. Het is beter om alleen maar in de cellen te zoeken die relevant zijn. In cel F4 staat de formule
=SOM(INDIRECT(“C3:C”&VERGELIJKEN(F2;Tabel3[Maand];0)+RIJ(Tabel3[[#Kopteksten];[Maand]])))
Een variant daarop in cel F5:
=SOM(C3:INDIRECT(“C”&VERGELIJKEN(F2;Tabel3[Maand];0)+RIJ(Tabel3[[#Kopteksten];[Maand]])))

In cel I4 wordt het totaal van een groepje te kiezen maanden bepaald.

De gegevensvalidatie van de twee grens-maanden is anders dan bij het vorige voorbeeld.
Hier gebruiken we de functie Indirect om te verwijzen naar de kolom Maand van de Excel-tabel met de naam Tabel3. Zonder Indirect is dit niet mogelijk.

LET OP gebruik aanhalingstekens rond de kolom-aanduiding.


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


Excel en kaarten 2



In een vorig artikel (Excel en kaarten) hebben we laten zien hoe je gegevens op een landkaart kunt plotten; beter gezegd: op ieder willekeurig plaatje.

De daarbij gehanteerde methode maakt het mogelijk om op detailniveau gegevens op een kaart te plaatsen.
Microsoft heeft vanaf versie 2016 een ingebouwde grafiek-optie waarmee gegevens op land, provincie en gemeente-niveau als een landkaart kunnen worden weergegeven. Hoe dat in zijn werk gaat (en welke problemen je daarbij moet zien te tackelen) komt in dit artikel aan bod.

Basisgegevens

Bij het CBS zijn heel veel gegevens te vinden, zoals bijvoorbeeld een overzicht van alle gemeentes per provincie (zie het tabblad Gemeenten_alfabetisch_2020 van het Voorbeeldbestand waar de stand van 1 jan 2020 is opgenomen).

We zijn eigenlijk alleen maar geïnteresseerd in de provincie- en gemeentenamen. Met Power Query is dat zo omgezet. Het resultaat staat in de tabel tblGem op het tabblad Data van het Voorbeeldbestand.

Willen we weten hoeveel gemeentes Nederland telde op 1 jan 2020 dan tellen we het aantal niet-lege cellen in de kolom Gemeentenaam met behulp van de functie AANTALARG.

LET OP de functie AANTAL telt alleen gevulde cellen mee, als daar getallen in staan.

In cel C3 wordt op een zelfde manier het aantal cellen in de kolom Provincienaam geteld. Dat levert hetzelfde resultaat op!
Hoe bepalen we nu hoeveel provincies er in Nederland zijn (we weten natuurlijk allemaal wel dat het antwoord 12 moet zijn)? De tabel tblGem heeft daartoe een hulpkolom gekregen waarin het volgnummer van de gemeente binnen de provincie wordt geturfd. In cel D8 is daartoe de formule =AANTAL.ALS($B$8:B8;[@Provincienaam]) ingevoerd.

NB de formule wordt in de Excel-tabel automatisch in de hele kolom doorgevoerd. In D9 staat dezelfde formule, maar de laatste B8 is dan B9.

In cel C4 bepalen we het aantal provincies door te tellen hoe vaak het eerste volgnummer voorkomt: =AANTAL.ALS(tblGem[GemPerProv];1)

Voor de liefhebbers staat in cel C5 een formule die het aantal provincies bepaalt zonder gebruik te maken van een hulpkolom:
=SOM(ALS(
INTERVAL(
VERGELIJKEN(tblGem[Provincienaam];tblGem[Provincienaam];0);
VERGELIJKEN(tblGem[Provincienaam];tblGem[Provincienaam];0)
)>0;1))

In een van de volgende artikelen zal ik de functie INTERVAL eens onder de loep nemen.

Kaartgrafiek 1

Als eerste gaan we een kaartgrafiek maken, waarin per provincie is aangegeven hoeveel gemeentes het bevat.
Daartoe maken we op basis van de gegevens uit de Excel-tabel tblGem een draaitabel (zie het tabblad ProvGem van het Voorbeeldbestand).

We maken nu de grafiek als volgt:

  1. plaats de cursor op een van de provincienamen
  2. kies in de menutab Invoegen in het blok Grafieken de optie Kaarten
  3. op dit moment kent Excel daarbinnen maar één mogelijkheid, de zogenaamde Choropletenkaart

(“Thematische kaart waarop gemiddelde intensiteiten, dichtheden of relatieve waarden van verschijnselen binnen van te voren begrensde gebieden (meestal administratieve eenheden) zijn weergegeven door middel van vlaksymbolen die verschillen in grijswaarde.“)

En dan het resultaat:

Helaas dus, we zullen de gegevens van de draaitabel eerst moeten kopiëren. Dat hebben we in het tabblad ProvGem gedaan naar de kolommen F en G. De werkwijze wordt dan:

  1. selecteer alle cellen met gegevens, inclusief een kopregel. In het voorbeeld is dat het bereik F3:G15
  2. kies in de menutab Invoegen in het blok Grafieken de optie Kaarten
  3. klik op Choropletenkaart

Na de eerste euforie zien we ineens een ‘grijze’ vlek rechtsboven. Wanneer je met de muis over de kaart beweegt zie je per provincie het bijbehorende aantal, maar bij Groningen krijgen we dit:

Excel heeft zelf geen info over landen, gemeentes en dergelijke vastliggen. Om de kaart te kunnen maken worden gegevens van Bing en TomTom opgehaald wanneer je een grafiek probeert te maken. In de praktijk blijkt dit ophalen lang niet altijd vlekkeloos te gaan. In dit geval weet Excel (of Bing?) niet of we de gemeente of de stad Groningen bedoelen en laat deze gegevens dan weg. Maar waarom gaat het bij Utrecht dan wel goed??? Wijzig je cel F8 in: Provincie Groningen dan worden alle provincies ingetekend.

NB bij het experimenteren met de Kaartgrafiek blijkt dat deze ingebouwde optie nog niet geheel stabiel en betrouwbaar is. In het begin worden plaatsnamen of provincies of andere geografische indelingen soms niet direct herkend. Het blijkt te helpen om te blijven proberen met andere en meer namen tot het systeem deze herkend. Daarna wil het vaak ook met namen lukken, die eerst niet geplot werden.

Welke gegevens er gebruikt kunnen worden is ook niet helemaal duidelijk; het is mij gelukt om de regio’s van Frankrijk ‘in te kleuren’ maar niet de departementen (zie het tabblad Frankrijk van het Voorbeeldbestand). En er gebeuren nog meer ‘vreemde’ dingen: bijvoorbeeld de regio in het noord-oosten heb ik Alsace genoemd, maar Excel/Bing vertaalt dit naar Grand Est.

Een goede methode om Excel (of Bing) te helpen bij het analyseren van de gegevens is om bij de categorie ook het hogere niveau op te nemen. In dit voorbeeld is het hogere niveau van de provincies het land.
Klik rechts op een van de provincies in de kaartgrafiek en kies Gegevens selecteren:

Kies bij de aslabels Bewerken en zorg dat ook de kolom met landnamen wordt meegenomen. Ook op deze manier wordt de provincie Groningen herkend:

De standaard-grafiek is op de volgende manier aangepast:

  1. de grafiektitel is aangepast door er in te klikken en wijzigingen aan te brengen.
    LET OP een dynamische grafiektitel (een verwijzing naar een cel in de Excel-sheet) is hierbij niet mogelijk. Uiteraard kun je nog wel een Tekstvak toevoegen, waarin je een dynamische verwijzing maakt (zie het tabblad GemPerProv van het Voorbeeldbestand)
  2. door rechts te klikken op één van de provincies kun je de Gegevensreeks opmaken
  1. de kaartprojectie laten we voor wat het is; voor echte geografen is dit waarschijnlijk wel interessant
  2. normaal blijft het Kaartgebied ook op Automatisch staan.
    Soms is één van de overige instellingen wel handig:

    Experimenteren dus.
  1. bij Kaartlabels heb je 3 mogelijkheden: Geen (dit is de standaardwaarde), Alleen best passend en Alles weergeven. Bij de 2e optie wordt er alleen een label geplaatst wanneer er voldoende ruimte is; bij de 3e manier zul je af en toe een label afgekapt zien (dmv …..)
  2. bij Reekskleur heb ik gekozen voor 3 kleuren; de precieze kleuren en de grenswaarden kunnen daaronder gewijzigd worden
  3. als laatste: klik rechts op één van de provincies en kies Gegevenslabels toevoegen. Door Waarden aan te vinken wordt de Kaartgrafiek aangevuld met de exacte waardes.

Kaartgrafiek 2

Een overzicht van de provincies van Nederland is leuk, maar we willen ook graag per provincie inzoomen.

Het eerste wat we doen is (natuurlijk) een draaitabel maken waarmee we per provincie een overzicht van de gemeentes krijgen (zie het tabblad GemPerProv van het Voorbeeldbestand).

Zoals hiervoor al aangegeven moeten we nog een tussenstap maken voordat we naar de Kaartgrafiek kunnen:
daarom hebben we een paar hulpkolommen (F en G) ingevoegd met daarin een formule =ALS(OF(B5=””;B5=”Eindtotaal”);””;B5)

NB omdat een Kaartgrafiek ook niet kan omgaan met dynamische bereiken (waarbij we gebruik maken van de functie Verschuiving) is er in de hulpkolommen ruimte gemaakt voor 100 gemeentes.

In de hulpkolom E is een verwijzing gemaakt naar de gekozen provincie.

Op basis van deze hulpkolommen (met 100 regels) is de kaartgrafiek gemaakt. Kies je in cel C2 een andere provincie dan zal de kaart automatisch aangepast worden.

NB1 wel zal rechtsboven in de grafiek altijd een waarschuwingsteken staan Klik je daar op, dan zul je zien dat maar een (klein) gedeelte van de 100 regels in de grafiek kunnen worden weergegevens; maar dat is ook logisch.

NB2 helaas, niet voor alle provincies gaat het (direct) goed. Kies je bijvoorbeeld Drenthe dan zie je alleen de provincie en niet de gemeentes.
Dit is weer op te lossen door bij de aslabels de kolom met provincie NIET mee te nemen.
Bij Groningen wordt er helemaal niets getoond! In Noord-Brabant wordt de gemeente Best niet ingevuld, in Limburg hebben we een probleem met de naam Bergen (L).

In de draaitabel wordt in de 2e kolom het aantal gemeentes per gemeente geteld; dat levert natuurlijk altijd als resultaat een 1 op. Ook in de hulpkolom G komt dus een 1. Maar de gebruikte formule daar is iets aangepast: =ALS(OF(B5=””;B5=”Eindtotaal”);””;C5+ALS(RijCur=RIJ();10;0))
Bij de waarde uit kolom C wordt 10 opgeteld als de waarde in de cel met de naam RijCur (dat is hier cel F2) gelijk is aan de Rij waarin de formule staat.
De waarde in cel RijCur wordt aangepast door een kleine VBA-routine die aan dit werkblad is gekoppeld:

Zo gauw de selectie in dit tabblad wordt gewijzigd (door het verplaatsen van de cursor of het klikken met de muis) wordt de waarde van de geselecteerde rij in de cel RijCur geplaatst. Op dat moment komt in kolom G in die rij een hogere waarde te staan, die er automatisch voor zorgt dat deze gemeente in de grafiek een andere kleur krijgt.
Door met de muis op één van de gemeentenamen te klikken kunnen we snel onze topografische kennis opvijzelen!

Kaartgrafiek 3

Een kaartgrafiek kan ook gebruikt worden om een indeling te laten zien.
Als de waardes voor de ‘y-as’ geen getallen zijn maar teksten kiest Excel automatisch dit type grafiek (zie het tabblad Utrecht van het Voorbeeldbestand).

De keuze welk type moet worden gegenereerd zit wat verstopt:

  1. klik rechts op een van de gekleurde vlakken en kies Gegevens selecteren
  2. kies bij de Legendagegevens de optie Bewerken
  1. via de radio-buttons kun je dan een van de twee grafiektypen kiezen

Kaartgrafiek 4

Als voorbeeld staan in het tabblad Corona van het Voorbeeldbestand de aantallen Corona-besmettingen van 17 nov voor de verschillende gemeentes in Zuid-Limburg. Niet alleen de aantallen sec, maar ook het aantal besmettingen per 100.000 inwoners. Dit om de gemeentes met elkaar te kunnen vergelijken.

NB de brongegevens van de eerste grafiek worden gevormd door alle gegevens van de kolommen B, C en D. Een kaartgrafiek kan maar één legendawaarde weergeven, dat zal hier dus kolom C zijn. Wanneer je nu deze kolom verbergt (klik op het -teken boven de D) worden de gegevens van de volgende kolom gebruikt.
Kolom C is in een groep geplaatst: selecteer de hele kolom door op de letter C te klikken en kies dan Groeperen in de menutab Invoegen.

3D-kaartgrafiek

Dit is een combinatie van een 3D– en een Kaartgrafiek.
Microsoft heeft dit niet onder de kaartgrafieken gerubriceerd, althans het zit helemaal ergens anders in de menu-structuur.

  1. plaats de cursor ergens in het bereik met gegevens, die geplot moeten worden. In het voorbeeld is dat in de Excel-tabel tblCorona op het tabblad Corona van het Voorbeeldbestand.
  2. kies dan in de menutab Invoegen de optie 3Dkaart
  3. vul de diverse items als volgt in:
  4. ‘speel’ wat met de zoom- en navigeer-knoppen:

Het onderdeel Rondleidingen kent nog veel meer mogelijkheden, maar het voert voor dit artikel te ver om daar op in te gaan.


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


Verder zoeken 2



Er zijn op deze site al heel wat artikelen verschenen over het zoeken in Excel. Een artikel in november 2017 heeft de naam Verder-zoeken en gaat over het zoeken in niet-unieke items.

Onlangs kreeg ik een vraag van Dean, die in het verlengde van dat artikel ligt: om de rapportvoorbereidingen op een school beter te kunnen doen, zou het handig zijn om per leerling niet alleen gemiddeldes over onderdelen uit te rekenen, maar ook de maximum behaalde score en te zien voor welk onderdeel dit maximum is behaald. Dit laatste leverde hoofdbrekens op: uiteraard kan dat maximum bij meer dan één onderdeel voorkomen.
Met behulp van enkele hulpkolommen kwamen we snel tot een werkbare oplossing.

Maar: het zou natuurlijk veel mooier zijn, als dit met behulp van een Excel-functie zou kunnen. Helaas deze bestaat (nog) niet, dus tijd voor het bouwen van een Eigen functie in Visual Basic.

NB in de nieuwste versies van Excel bestaat de functie X.ZOEKEN. Ook al is deze functie veel flexibeler dan het bestaande VERT.ZOEKEN, maar ook deze functie levert altijd slechts maar één resultaat op.

Probleem

Dean had een mooie werkmap in elkaar gezet, waarbij uiteindelijk op een tabblad een cijfer-overzicht werd gegenereerd.

In het overzicht op het tabblad Rapport van het Voorbeeldbestand heb ik wat Voorwaardelijke opmaak toegevoegd om snel inzicht te krijgen in de spreiding van de resultaten.
Het bepalen van het gemiddelde per leerling of onderdeel leverde geen probleem op; ook het bepalen van de hoogste score niet. Maar wel: bij welk onderdeel (of leerling) komt dit maximum voor?

Oplossing 1

Dean was er al snel achter gekomen dat hij met de standaardfuncties HOR.ZOEKEN en VERT.ZOEKEN niet veel verder kwam. Maar zoeken op www.ginfo.nl leverde het begin van een oplossing in de vorm van de combinatie Index-Vergelijken.

In cel J3 staat de formule:
=INDEX($C$2:$G$2;VERGELIJKEN(I3;C3:G3;0))
De functie Index levert die waarde uit het bereik C2:G2, waarvan het volgnummer gelijk is aan het resultaat van de functie Vergelijken.
Deze laatste functie ‘kijkt’ op welke plaats de waarde uit cel I3 in het bereik C3:G3 staat; de derde parameter (0, nul) geeft aan dat er een exacte vergelijking moet worden uitgevoerd.

NB1 op een vergelijkbare manier wordt in cel C15 bekeken welke leerling de hoogste score op het eerste onderdeel heeft gehaald.

NB2 moeite om te onthouden hoe de combinatie van deze functies moet worden ingevoerd? Gebruik de avz-truc uit het artikel Zoeken: Index en vergelijken.

Helaas bij leerling 9 gaat dat fout: de maximale score 7,7 komt 2 keer voor maar de formule laat alleen Item 2 zien.

Oplossing 2

Gelukkig: de functie ZoekWaarde levert wel het gewenste resultaat!
Maar … deze functie is niet terug te vinden in de gereedschapskist van Excel. Dit is een zogenaamde Eigen functie (in het Engels UDF, User Defined Function). Hoe je deze in je spreadsheets kunt implementeren, zal ik hierna uitleggen.

De functie ziet er als volgt uit:

Deze functie kent 3 parameters (Waarde, Waarzoeken en Resultaat); de functie kijkt waar de Waarde in het bereik Waarzoeken voorkomt en levert de overeenkomende waarde uit het bereik Resultaat. Komt de Waarde vaker dan één keer voor dan worden de resultaten gescheiden door een /.

De functie kan op dezelfde manier in een Excel-cel ingevoerd worden als iedere andere (standaard-)functie. Weet je niet welke parameters er in welke volgorde nodig zijn, ga dan als volgt te werk:

  1. plaats de cursor in de cel waar de formule moet komen
  2. tik in =zoekwaarde(
    Of tik het eerste gedeelte in en klik dubbel op de functie in het lijstje dat automatisch tevoorschijn komt:
  3. klik op denet voor de formulebalk
  4. vul in de pop-up de parameters (argumenten) in:

NB voor Eigen functies is geen Help beschikbaar; zorg dat de namen van de parameters aangeven wat de bedoeling is (dus niet Bereik1, Bereik2 of iets dergelijks)

In het tabblad Rapport van het Voorbeeldbestand is deze functie gebruikt in kolom K en rij 16 met het gewenste resultaat.

NB zoals uit de formules in kolom L en rij 17 blijkt, is het voor deze functie niet nodig dat het hele resultaat-bereik wordt opgegeven; de begincel is voldoende.

In het tabblad Rap2 van het Voorbeeldbestand is te zien, dat wanneer de rapportcijfers gehele getallen zijn, het probleem van meervoudige resultaten nog veel vaker zal voorkomen.

NB de functie kent geen enkele fout-detectie; in de praktijk hoeft dit bij het (met verstand) toepassen geen probleem te zijn. In regel 19 staan de resultaten van ‘vreemd’ gebruik van de functie.

Oplossing 3

De hiervoor geschetste oplossing lijkt veel op de Index-Vergelijken-oplossing met dat verschil, dat meervoudige resultaten mogelijk zijn.
Om ook het probleem van foutdetectie aan te pakken (en de functie wat flexibeler te maken) bevat het Voorbeeldbestand nog een andere Eigen functie; deze heeft 4 parameters waarvan de laatste optioneel is (hoeft dus niet ingevoerd te worden; in dat geval zal Excel bij deze functie “/” gebruiken): IndexVergelijken(Result_matrix, Zoekwaarde, Zoeken_matrix, Optional Scheiding As String = “/”)

Na de declaratie van de functie (Public is de standaardinstelling; maak je de functie Private dan zul je bij het invoeren niet meer uit de lijst kunnen kiezen en is het niet mogelijk om het Functieargumenten-scherm op te roepen) staan diverse commentaar-regels, die uitleg geven over de functie en de randvoorwaarden.
Daarna volgen 3 regels waarin variabelen, die binnen de functie gebruikt worden, worden gedeclareerd (Boolean variabelen kunnen alleen de waarden False en True bevatten; variabelen van het type Long kunnen grote gehele getallen weergeven).
In het volgende gedeelte worden diverse testen uitgevoerd op de ingevoerde parameters. Als een test de waarde Waar/True oplevert, wordt een foutmelding als resultaat van de functie doorgegeven en stopt de verdere verwerking (Exit Function).

In het 2e gedeelte vindt de daadwerkelijke verwerking van de parameters plaats.
Eerste krijgen de 2 booleans een waarde.
Dan lopen we met 2 geneste For-Nextloops door alle cellen van de rijen en kolommen van Zoeken_matrix (door bovenstaande controles kan het maar 1 kolom of 1 rij zijn).
Als de Zoekwaarde in een cel voorkomt wordt de overeenkomende waarde uit de Result-matrix aan het resultaat van de functie toegevoegd, inclusief de standaard- of de in de 4e parameter opgegeven scheidingstekst. Als het zoek- en resultaat-gebied gelijk zijn wordt de betreffende rij of kolom als resultaat doorgegeven.
Als na de For-Next-loops blijkt dat er minstens één resultaat is gevonden wordt de laatste scheidingstekst verwijderd (Len bepaalt de lengte van een tekst). Als er geen enkel resultaat is gevonden, wordt als resultaat van de eigen functie de foutcode xlErrNA teruggegeven; in de Nederlandstalige versie van Excel krijg je dan de standaard-foutmelding #N/B.

In het tabblad Rap3 van het Voorbeeldbestand staan diverse voorbeelden van het gebruik van deze eigen functie IndexVergelijken.

NB1 omdat deze eigen functie heel veel lijkt op het gebruik van de combinatie Index-Vergelijken heb ik ook de volgorde van de parameters aangehouden, zoals ze daarbij worden ingevoerd.
De werking van de functie verandert niet, als de eerste regel wordt gewijzigd in het meer logische
Public Function IndexVergelijken(Zoekwaarde, Zoeken_matrix, Result_matrix, Optional Scheiding As String = “/”)

NB2 optionele parameters moeten altijd achteraan komen.

Extra voorbeelden 1

Hiernaast staat een afdelingsoverzicht met bijbehorende persoon; aan iedere persoon is een waarde toegekend (zie de Excel-tabel tblAfdPers in het tabblad Ovz1 in het Voorbeeldbestand).

Wil je weten bij welke afdeling of persoon een bepaalde waarde hoort, dan kun je geen gebruik maken van VERT.ZOEKEN (deze functie kan niet ‘naar links kijken’).

De combinatie Index en Vergelijken ligt hier meer voor de hand. Deze formule voldoet als je zeker weet dat de waardes uniek zijn. In dit geval lijkt dat zo, maar …

Het is veiliger om de nieuwe functie IndexVergelijken() te gebruiken:

De waarde 21 komt bij 2 afdelingen voor, telkens bij de persoon P1.
In cel I10 is met deze functie een persoonsoverzicht gemaakt voor Afd3, terwijl in cel I13 te zien is in welke afdelingen P4 voor komt.

Extra voorbeelden 2

Op het tabblad Ovz2 van het Voorbeeldbestand staan voorbeelden van het zoeken in een draaitabel met de functie IndexVergelijken().

Cel D19 bevat het resultaat van de functie wanneer de zoek- en resultaat-matrix gelijk zijn.

Implementatie van een Eigen functie

Er zijn ruwweg 2 methodes: koppel de functie aan de werkmap waar je hem nodig hebt (net als in het Voorbeeldbestand) of zorg er voor, dat de functie in iedere werkmap beschikbaar is. De functie moet dan opgenomen worden in een zogenaamde persoonlijke macro-werkmap (PERSONAL.XLSB). Helaas kent deze methode nogal wat haken en ogen. We gaan daar hier niet verder op in. Misschien een idee voor een apart artikel?

Visual Basic Editor

In allebei de gevallen hebben we de Visual Basic Editor nodig. Er zijn verschillende mogelijkheden om die te openen:

  1. druk op de toetscombinatie Alt-F11
  2. klik rechts op één van de tabs van de werkbladen onderaan (in het Voorbeeldbestand bijvoorbeeld op de tab Rapport) en kies de optie Programmacode weergeven
  1. klik in de menutab Ontwikkelaars in het blok Programmacode op de optie Visual Basic
    NB staat de menutab Ontwikkelaars niet in het ‘lint’, klik dan rechts op één van de menutabs, kies de optie Het lint aanpassen en zorg dat in het rechterblok de optie Ontwikkelaars is aangevinkt.

In aparte werkmap opnemen
  1. maak of open het bestand waar de eigen functie gebruikt zal gaan worden (in dit voorbeeld Map1).
    Open ook het Voorbeeldbestand.
  2. open de Visual Basic Editor. Linksboven (tenminste in de standaardinstelling van VBA) staat de project-verkenner, waar alle geopende Excel-projecten/werkmappen weergegeven worden.
  3. wanneer het nieuwe project geen modules heeft (zoals hiernaast Map1), zorg dan dat dit project is geselecteerd en kies Invoegen/Module
  1. dubbel-klik op Module1 van het project Meervoudig zoeken. Selecteer de hele functie IndexVergelijken (vanaf Public tot en met End Function) en kopieer (bijvoorbeeld door Ctrl-C)
  2. dubbel-klik op Module1 van het project waar de functie gebruikt zal gaan worden (hier dus Map1) en plak de eigen functie (Ctrl-V)
  3. sluit VBA af via Alt-Q of Bestand/Sluiten en terugkeren
  4. de eigen functie is nu te gebruiken in de nieuwe werkmap.

LET OP wanneer het bestand bewaard wordt moet je er wel voor zorgen dat je deze opslaat als werkblad met macro’s (dus extensie xlsm) of als binair (met de extensie xlsb).


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


Opslag-datum



Ofwel de datum waarop een bestand is opgeslagen; in het Engels vaak aangeduid als SaveDate.

Word kent een handige manier om in de kop- en voetregel de datum, waarop het betreffende bestand voor de laatste keer is opgeslagen, weer te geven (SaveDate, een apart Veld binnen de optie Info over document).
Op de vraag, waar dat in Excel terug te vinden is, moest ik het antwoord schuldig blijven: Excel kent een zodanige optie niet en die is ook niet met een of andere info-functie na te bouwen.
Uiteraard even Google geraadpleegd: geen resultaten voor SaveDate in Excel, wel allerlei VBA-routines waarmee dit enigszins na te bootsen is.
Daarom in dit artikel enkele voorbeelden daarvan.

Bij opslaan datum toevoegen 1

De eerste methode, die de Word-functionaliteit goed benadert, is om de datum (en tijd) aan de voetregel toe te voegen op het moment dat het Excel-bestand wordt opgeslagen.
Drie regels binnen VBA volstaan:

Dit is een zogenaamde Event-SUBroutine; net voordat de werkmap wordt opgeslagen (BeforeSave) wordt deze procedure uitgevoerd.
De parameter SaveAsUI is verplicht. Deze parameter kan binnen de routine gebruikt worden; de waarde daarvan is True als de gebruiker Opslaan als heeft gekozen en bij gewoon Opslaan is de waarde False.
De tweede parameter (Cancel) is ook verplicht en heeft standaard de waarde False. Krijgt ergens in de procedure deze parameter de waarde True dan zal er daarna geen opslag-actie plaats vinden (opslaan wordt gecanceled).
De tweede regel is waar het om draait: links in de voetregel van het actieve werkblad wordt de combinatie van de huidige datum en tijd geplaatst. De datum heeft een speciale notatie gekregen (minimaal 1 positie voor de dagaanduiding, een uitgebreide omschrijving voor de maand (bijvoorbeeld oktober), 4 cijfers voor het jaar en dan nog een komma en een spatie).

LET OP de routine plaatst de datum ALLEEN in de voetregel van het actieve werkblad (ActiveSheet). Dit is een groot verschil met de Word-optie, die de datum in de voetregel van het document plaatst en daarmee op ALLE pagina’s.

NB1 deze routine moet in een speciale module van de betreffende werkmap geplaatst worden, namelijk in ThisWorkbook.

NB2 om er voor te zorgen, dat deze routine samen met de Excel-sheet wordt opgeslagen, dient de extensie xlsm te zijn (macro-enabled).

NB3 in alle voorbeelden in dit artikel wordt de datum links in de voetregel geplaatst. Uiteraard kunnen ook de opties CenterFooter, RightFooter, LeftHeader, CenterHeader en RightHeader gebruikt worden.

Bij opslaan datum toevoegen 2

In het Voorbeeldbestand is de routine wat uitgebreid:

Hoe plaats je een dergelijke routine in je eigen werkmap:

  1. ga naar de VBA-editor: kies binnen de menutab Ontwikkelaars de optie Visual Basic of druk op Alt-F11.
  2. dubbelklik op de module ThisWorkbook van de betreffende werkmap; rechts kan dan de projectcode voor deze module ingevoerd worden.
  3. tik de bovenstaande code in of, beter, kopieer deze uit het Voorbeeldbestand.
  4. verlaat de VBA-editor door Alt-F11 te drukken
  5. sla het bestand op; zorg dat het de extensie xlsm krijgt.

Een korte uitleg van de belangrijkste elementen van de routine:

  • eerst wordt een message-box getoond met de vraag of de voetregel aangepast moet worden; alleen Ja en Nee zijn toegestaan (vbYesNo)
  • als op de Nee-knop wordt geklikt, gebeurt er in deze subroutine niets meer. Wel slaat Excel het bestand dan nog op.
  • anders wordt de inhoud van de linker-voetregel in de variabele a geplaatst en via een tweede message-box op het scherm getoond.
  • wordt daar gekozen om niet door te gaan, dan krijgt u de waarschuwing te zien, dat het bestand niet wordt opgeslagen (doordat Cancel op True wordt gezet)
  • anders wordt de voetregel aangepast, waarna Excel het bestand opslaat.

NB1 de eerste 2 keren wordt MsgBox als functie gebruikt, waarvan het resultaat in de variabele a wordt geplaatst.
In het derde geval voert MsgBox alleen een opdracht uit; omdat het dan geen functie is, moeten de haakjes weggelaten worden.

NB2 de voetregel is iets uitgebreid: naast de tekst Opslagdatum wordt met behulp van &6 ook de lettergrootte aangepast.

Alternatief 1

Een groot nadeel van bovenstaande methodes is, dat de VBA-routine eerst in het betreffende bestand moet worden geplaatst en het bestand als xlsm moet worden opgeslagen.

Handige en/of veel gebruikte routines kunnen echter ook in een module van uw persoonlijke werkmap geplaatst worden.
De vraag is: waar kunt u die map vinden?

Ziet u in de VBA-editor ergens het project PERSONAL.XLSB dan kunt u een van de bestaande modules openen door daarop te dubbelklikken (of een extra module Invoegen).

Bestaat de persoonlijke werkmap nog niet, doe dan het volgende:

  1. open de menutab Ontwikkelaars (ziet u die niet? Klik met de rechter muisknop ergens in de menubalk en kies de optie Lint aanpassen en vink in het rechtergedeelte van het nieuw geopende scherm de optie Ontwikkelaars aan).
  2. kies de optie Macro opnemen.
    Een macro opnemen (en stoppen) kan ook altijd via de knop linksonder in de statusbalk
  3. in het midden van de pop-up kiest u Persoonlijke macrowerkmap, klik op de OK-knop en kies dan direct in de menubalk de optie Opname stoppen.

Als de persoonlijke werkmap bestaat, dan kunnen routines daarin geplaatst worden; deze zijn dan in iedere werkmap direct te gebruiken.

Het eerste alternatief is dan (routine vergelijkbaar met hierboven; tekst hieronder is makkelijk te kopiëren):
Sub DatumVoetregel()
Dim a
a = MsgBox("Wilt u datum en tijd links in de voetregel plaatsen?", _
vbYesNo, "Voetregel aanpassen")
If a = vbYes Then
a = ActiveSheet.PageSetup.LeftFooter
If a <> "" Then
a = MsgBox("In de voetregel staat nu: " & _
vbCrLf & a & vbCrLf & "Doorgaan?", _
vbYesNo, "Voetregel aanpassen")
If a = vbNo Then Exit Sub
End If
ActiveSheet.PageSetup.LeftFooter = _
"&6 " & Format(Date, "d mmmm yyyy, ") & Time
End If
End Sub

LET OP het woord Private is op de eerste regel weggelaten, anders is deze routine niet voor andere werkbladen dan de persoonlijke ‘zichtbaar’.

Deze procedure zal NIET automatisch starten wanneer u een bestand opslaat. Wat moet u doen:

  1. maak een nieuwe werkmap aan
  2. kies in de menutab Ontwikkelaars de optie Macro’s (of druk op Alt-F8)
  3. kies in het vervolgscherm de gewenste routine; in dit geval PERSONAL.XLSB!DatumVoetregel en klik op Uitvoeren (of dubbelklik op de gewenste routine)
  4. sla direct daarna het bestand op.
    LET OP Doet u dat met het voorbeeldbestand, dan zal de automatische routine ook nog opgestart worden

NB doordat er twee handelingen verricht moeten worden (de routine uitvoeren en het bestand opslaan) zal de tijd in de voetregel niet exact overeenkomen met de tijd van het opgeslagen bestand.

Alternatief 2

Een verkorte routine, die alleen de datum en tijd in de voetregel zet, is:
Sub DatumVoetregelKort()
ActiveSheet.PageSetup.LeftFooter = Format(Date, "d mmmm yyyy, ") & Time
End Sub

Alternatief 3

Een derde alternatief, waarbij de SaveDate uit de eigenschappen van het document wordt opgehaald:
Sub DatumVoetregelKort2()
ActiveSheet.PageSetup.LeftFooter = Format( _
ActiveWorkbook.BuiltinDocumentProperties("Last Save Time"), "d mmmm yyyy, hh:mm:ss")
End Sub

De werkwijze is dan net andersom:

  1. sla de werkmap op
  2. voer de routine uit
  3. print de pagina

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

Werkdagen



Voor rapportages is het nog wel eens handig om te bepalen hoeveel dagen er zitten tussen een begin- en einddatum. In Excel is dat heel eenvoudig te berekenen door deze datums van elkaar af te trekken. In Access gaat dat op dezelfde manier.

Maar als je wilt weten hoeveel werkdagen er tussen zitten, wordt het wat lastiger. In Excel gaat dat nog vrij eenvoudig met behulp van de ingebouwde functie NETTO.WERKDAGEN, maar in Access komt er wat meer werk om de hoek kijken.

Hieronder een uitleg over de werkwijze in Excel en een voorbeeld hoe de berekening in Access kan worden uitgevoerd.

Dagen verschil in Excel

In het Voorbeeldbestand ziet u op het tabblad Kalender van de Excelsheet Werkdagen.xlsx een Excel-tabel met alle dagen van 2019. In de tweede kolom van die tabel (met de naam tblKalender) wordt het verschil bepaald tussen die dagen en een vaste begindatum (Refdatum). Simpel door ze van elkaar af te trekken via de formule =[@Datum]-RefDatum

NB alle datums in de voorbeelden hebben de volgende celopmaak meegekregen: ddd d mmmm jjjj (zie ook het artikel ginfo.nl/datums-data-dates).


Dus de cel uit de kolom Datum in dezelfde rij (vandaar de @) minus de referentiedatum (in dit geval de inhoud van cel C2).
Een negatief resultaat wil zeggen, dat de datum vóór de referentiedatum ligt.

NB de kleuren in de datum-kolom worden automatisch gegenereerd door middel van Voorwaardelijke opmaak.

Moet de dag zelf ook meetellen dan moet de berekening iets aangepast worden: =[@Datum]-RefDatum+ALS([@Datum]>=RefDatum;1;-1)
Als de dag op of na de referentiedatum ligt, dan moet het resultaat 1 groter worden, anders 1 meer negatief (zie kolom Verschil2).

Zonder weekenden in Excel

Maar als we willen weten hoeveel werkdagen er tussen 2 datums zitten, dan volstaat zo’n simpele berekening niet; zaterdagen en zondagen mogen niet meetellen.
Gelukkig heeft Excel daar een standaard-functie voor: NETTO.WERKDAGEN.

Geef je maar 2 parameters op (de begin- en einddatum), dan bepaalt deze functie het verschil in dagen, exclusief de weekenden.

NB ook hier telt de dag zelf mee; zie in het voorbeeld hierboven het resultaat bij 9 januari.

Echte werkdagen in Excel

Met echte werkdagen bedoelen we dat feestdagen/vrije dagen niet mee mogen tellen.

In het tabblad Feestdagen van het Excel-Voorbeeldbestand is daartoe een overzicht opgenomen met alle mogelijke feestdagen.
Waarschijnlijk zijn dit voor u niet allemaal vrije dagen (helaas?); in de praktijk zult u het overzicht dus moeten aanpassen.
De gegevens zijn opgenomen in de Excel-tabel met de naam tblFeestdagen.

De formule voor de werkdagen is in kolom F uitgebreid met een derde parameter, de verwijzing naar de feestdagen die, net als de weekenden, niet meegerekend moeten worden (alle dagen uit de kolom Datum van de tabel tblFeestdagen).

Dagen verschil in Access

In het Voorbeeldbestand zit ook een Access-database (Werkdagen.accdb). Deze heeft de volgende onderdelen: 2 tabellen (met dezelfde feestdagen en kalender als in de Excel-sheet), een query (VerschilBepalen) waarmee de verschillen tussen begin- en einddatums bepaald worden en een Module1, die een eigen functie bevat waarmee we het verschil in werkdagen kunnen bepalen.

Het eerste stuk van de query ziet er als volgt uit:

Voor alle datums uit de Access-tabel tblKalender wordt het verschil bepaald met een referentiedatum.
Deze referentie ligt nergens vast. Access kent het veld tussen rechte haken met de naam Wat is de referentiedatum? niet.

Bij de weergave van de query zal dan ook een pop-up komen, waarin gevraagd wordt om de inhoud van de parameter op te geven.

NB met de functie CDate zorgen we er voor, dat Access ‘weet’ dat de parameter een datum bevat.

Zonder weekenden in Access

Zoals gezegd kent Access geen functie om werkdagen te bepalen; die moeten we dus zelf programmeren.

De eerste regel definieert de functie met de naam Werkdagen. De functie kent 3 parameters: de eerste is de begindatum en de tweede de einddatum. De derde parameter is optioneel en kan alleen de waarde WAAR of ONWAAR bevatten (Boolean); moet er wel of niet met feestdagen rekening worden gehouden.
Na wat commentaarregels (groene tekst) krijgt de routine de opdracht om bij een mogelijk optredende fout naar het gedeelte te gaan dat begint met Err_Werkdagen.
Met het commando Dim worden diverse variabelen gedefinieerd, die we hierna nodig hebben.
De variabele Start wordt gevuld met het datum-gedeelte van de parameter StartDatum; iets vergelijkbaars gebeurt met de variabele Eind.
In de variabele StartKleinerEind wordt vastgelegd of de variabele Start kleiner of gelijk is aan Eind (WAAR of ONWAAR).
Wanneer de parameter Feestdagen de waarde WAAR bevat, dan worden alle datums uit de tabel tblFeestdagen in de variabele rst gestopt.

Nu kunnen we gaan rekenen:

De variabele intCount wordt op 0 gezet.
In het geval Start kleiner of gelijk is aan Eind dan wordt de eerste Do While-loop doorlopen; als dat niet zo is, de tweede.
Als binnen de loop de variabele Start geen zondag of zaterdag is dan wordt de variabele intCount met 1 opgehoogd.
Moet er rekening gehouden worden met feestdagen dan wordt er via de opdracht FirstFind in de variabele rst gekeken of Start daarin voorkomt.

LET OP Access verwacht #’s rond de datum en de FirstFind-opdracht werkt alleen goed als de datum in Amerikaans formaat wordt meegegeven.

Als de datum Start voor komt in rst (Not rst.NoMatch) dan moet de teller weer verlaagd worden.
De variabele Start wordt opgehoogd en via Loop wordt de berekening opnieuw uitgevoerd met de nieuwe startdatum (als de nieuwe Start tenminste nog kleiner of gelijk is aan Eind)

NB de tweede loop werkt ongeveer hetzelfde maar nu vanuit Start terugtellend naar Eind.

Als de berekening klaar is dan wordt de inhoud van de variabele intCount als resultaat aan de functie teruggeven. Daarbij wordt wel gekeken of het resultaat positief of negatief moet zijn.
Dan is onze functie klaar: Exit Function.

Onderaan staat nog wat de functie moet doen als er een fout optreedt.

Met behulp van deze functie kunnen we de query VerschilBepalen uitbreiden:

NB Omdat de onbekende parameter bij Verschil en ZonderWeekend exact hetzelfde is, zal Access slechts 1 keer vragen om hiervoor een referentiedatum in te voeren.

Het resultaat van de query is nu precies gelijk aan het resultaat van de Excel-functie NETTO.WERKDAGEN, waarbij de derde parameter niet is opgegeven.

Echte werkdagen in Access

Het zal u niet verbazen, dat de uitbreiding van de query met het veld EchteWerkdagen

… een vergelijkbaar resultaat oplevert als NETTO.WERKDAGEN, rekening houdend met allerlei feestdagen.


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