Tagarchief: Tekst

Rapportage-stramien

Excel, , , , ,
Voorbeeldbestand: RapportageStramien.xlsx

Binnen ieder bedrijf is het van belang om zoveel mogelijk processen te standaardiseren en vaak ook te automatiseren.

Ook wanneer we Excel als hulpmiddel bij ons werk gebruiken is het belangrijk om zoveel mogelijk handmatige handelingen, die regelmatig terugkomen, te vermijden. Power Query, draaitabellen, en VBA kunnen daarbij een grote rol spelen.

Voor diegene, die vaak verschillende rapportages moeten maken, is een andere invalshoek belangrijk. Ga niet voor iedere nieuwe rapportage weer opnieuw het wiel uitvinden, maar gebruik een flexibel stramien. Hoe dat in zijn werk gaat zullen we in dit artikel aan de hand van een direct inzetbaar Voorbeeldbestand laten zien.

Bedrijf

Het eerste tabblad van het Voorbeeldbestand, Bedrijf, bevat diverse bedrijfsspecifieke gegevens, inclusief een logo.

NB1 wanneer je dit logo vervangt (door er rechts op te klikken) zorg dan dat het nieuwe plaatje een transparante achtergrond heeft.

NB2 diverse cellen in dit tabblad (en ook op andere plaatsen in de werkmap) hebben een naam gekregen, die in de rest van het rapportage-stramien gebruikt worden. Cel C2 bijvoorbeeld heeft de naam BedrNaam.

In cel C8 wordt de meest recente datum uit het Data-bestand (zie hierna) opgehaald met behulp van de functie MAX. De inhoud van cel C9 bepaalt of deze datum als referentie dient voor de rapportage of een andere (cel C10).

NB3 op diverse plaatsen in dit stramien zul je zien dat de invoer in cellen beperkt is met behulp van Gegevensvalidatie; zie bijvoorbeeld de cel C9 (alleen Ja en Nee zijn toegestaan).

In de regels 13 tot en met 22 kun je zelf de diverse onderdelen (en teksten) kiezen die in de kop- en voetregel van een rapportage moeten komen.

NB4 in cel G19 staat een spatie om er voor te zorgen dat de voetregel aan de rechterkant niet tegen de rand aan komt.

Experimenteer met de mogelijkheden en beoordeel het effect op de rapportages in de tabbladen Ovz1 en Ovz2.

Basis-instellingen

Het tabblad Basis van het Voorbeeldbestand begint met een standaardopmaak voor datums (in dit geval hebben we gekozen voor 3 letters voor de maand, een spatie en 4 cijfers voor het jaar).
Daaronder staan 6 items die als onderdeel voor de kop- en voetregels kunnen dienen.
In het blok in de kolommen E en F staan diverse opmaak-omschrijvingen en -codes die bij het weergeven van gegevens in de rapportage gebruikt kunnen worden.

NB aangezien alle keuze-items in Excel-tabellen zijn opgenomen zal een gewenste uitbreiding van de opties direct overal in de werkmap geëffectueerd worden.

Overige instellingen

In het eerste blok van de Overige instellingen (zie het tabblad Instel van het Voorbeeldbestand) wordt van maximaal 10 items vastgelegd wat de inhoud is, welke opmaak uit de lijst van het tabblad Basis deze moeten hebben en eventuele opmerkingen er in de rapportage moeten worden weergegeven.
Voor uw eigen rapportage moeten/kunnen de kolommen Naam, Opmaak en Opmerkingen aangepast worden.

Daaronder staat een Excel-tabel met daarin de omschrijvingen van de berekeningen die op de gegevens kunnen worden toegepast. De berekeningen zelf staan op het tabblad Berekeningen. Ook hier zult u voor uw eigen rapportage aanpassingen moeten doorvoeren en wel in de tweede en derde kolom.

NB in het voorbeeld-stramien staan 10 berekeningen, maar dit mogen er ook meer (of minder) zijn.

LET OP de opmaak van berekende items is standaard gelijk aan de opmaak van de onderliggende data; in het voorbeeld hierboven zal de opmaak van Waarde deze maand in het geval van Verkoop-cijfers gelijk zijn aan Valuta, maar die van Klanten heeft dus de opmaak Getal.
Maar bij berekening 7 (in dit geval) geven we aan dat alle items hiervan de opmaak % met 1 decimaal zullen hebben.

Als laatste geven we nog aan wat voor een soort rapportage het hier betreft.
Er zijn 2 mogelijkheden: alleen de data van het lopende jaar worden getoond (YtD = Year to Date) of altijd de laatste 12 maanden (YoY = Year on Year).

Iedere rapportage-pagina heeft bovenaan 3 blokjes met de belangrijkste gegevens/berekeningen.
Bij Indeling overzichtsblokken wordt bij DataNr allereerst aangegeven welke 3 van de 10 items moeten worden getoond; bij Berekening kun je 5 opties kiezen die voor deze items moeten worden weergegeven.

De sparkline daaronder geeft het verloop in de tijd weer van het betreffende item (YtD of YoY). Met de instellingen zoals hierboven is het resultaat:

Data

In het Voorbeeldbestand is het tabblad Data gevuld met fictieve gegevens. Het systeem is beperkt tot 10 kolommen met gegevens waarvan de namen vastliggen in het tabblad Instel.

Per maand dienen alle gewenste kolommen met de juiste data gevuld te worden. Gebruik je ook berekeningen waarbij de actuele gegevens afgezet worden tegen de beoogde resultaten dan dienen ook de Doel-kolommen gevuld te worden.

Berekeningen

In het tabblad Berekeningen van het Voorbeeldbestand worden allereerst van alle 10 items (en de daarbij behorende doelen) de YoY-data opgehaald.

Daaronder wordt de opmaak van (nu) maximaal 16 berekeningen per item bepaald.
De betreffende formule is uitdagend te noemen:
=ALS(INDEX(tblBerekOpties[Opmaak];[@Nr])=0;INDEX(tblOpmaak[OpmCd];VERGELIJKEN(INDEX(tblDataInd[Opmaak];VERGELIJKEN(tblYoY[[#Kopteksten];[Kol1]];tblDataInd[Kolom];0));tblOpmaak[Naam];0));INDEX(tblOpmaak[OpmCd];VERGELIJKEN(INDEX(tblBerekOpties[Opmaak];[@Nr]);tblOpmaak[Naam];0)))

In het derde blok worden de benodigde berekeningen gedefinieerd. In cel D41 staat een simpele verwijzing naar een cel uit het eerste blok.

De formule waarmee berekening 8 (het gemiddelde van de laatste 3 maanden) wordt uitgevoerd, is:
=GEMIDDELDE(VERSCHUIVING(D16;-2;0;3;1))
ofwel we gaan vanaf cel D16 2 regels omhoog en 0 kolommen naar rechts/links en kiezen dan een blok cellen, 3 hoog en 1 breed; van deze range wordt het gemiddelde bepaald.

Nog even de vorige 2 stappen combineren met (in cel D61) de formule:
=ALS.FOUT(TEKST(D41;D21);0)

Dus met de functie TEKST wordt de inhoud van cel D41 opgemaakt met de inhoud van cel D21. Als dit onverhoopt een probleem oplevert, dan wordt de waarde 0 weergegeven.

Met al dat voorwerk kunnen we nu de blokjes in de kop van de rapportages samenstellen. Ook de cijfers voor de sparklines worden klaar gezet.

Omdat we ook een grafiek in de rapportage willen opnemen, moeten we de benodigde gegevens nog even klaar zetten.

Onderaan staat een draaitabel. Een Slicer in de rapportage bepaalt welk item we willen weergeven. Het betreffende nummer gebruiken we in het grafiekblok.

Rapportage-pagina’s

In het tabblad Ovz1 van het Voorbeeldbestand staat een eerste voorbeeld van een rapportage gebaseerd op alle berekeningen uit de rest van het werkblad. Wijzigingen in de diverse tabbladen hebben direct effect op het resultaat. Kies in de slicer aan de rechterkant van de grafiek een ander item en je krijgt de bijbehorende grafiek.

Wil je naast de drie belangrijkste items met de berekeningen in de blokjes aan de bovenkant van de pagina alle info in één oogopslag: zie het tabblad Ovz2 van het Voorbeeldbestand.

Uiteraard is dit nog geen totaal rapportage; ongetwijfeld kunt u nog andere invalshoeken vinden die van belang zijn. Kopieer een Ovz-tabblad en ga aan de slag!

Functie Indirect

Excel, VBA (Visual Basic for Applications), , , ,
Voorbeeldbestand: Indirect.xlsxDownload

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.

Totalen in draai-grafiek

Excel, , , , , , ,
Voorbeeldbestand: DrGrafTotaal.xlsmDownload

Totalen in een grafiek weergeven, dat is simpel: voeg de betreffende reeks toe aan de grafiek-gegevens. Misschien dat je deze reeks nog aan de secundaire as moet koppelen, maar daarmee ben je klaar.

Maar bij een draai-grafiek (een grafiek gebaseerd op een draaitabel) zul je merken dat je wel wat flexibiliteit inlevert; dan is een lijntje met totalen toevoegen niet mogelijk.
Met VBA kun je een heel eind komen, maar zonder programmeren niet. In dit artikel wat alternatieve mogelijkheden.

Basis-gegevens

We gaan uit van een tabel (tblData2 op het tabblad Data van het Voorbeeldbestand) met daarin Datums en het Soort artikel met een bijbehorend Aantal.

Wil je zien hoe dit overzicht tot stand is gekomen: kijk op het tabblad Basis van het Voorbeeldbestand. Daar worden telkens random nieuwe data gecreëerd.

Draaitabel

Om deze gegevens snel te kunnen analyseren maken we een draaitabel:

  1. selecteer een cel in de brongegevens
  2. klik in de menutab Invoegen in het blok Tabellen op de optie Draaitabel en dan op de button OK
  3. sleep de Datum naar het Rijen-gebied, Soort naar de Kolommen en Aantal naar het Waarden-gebied
  4. standaard zal Excel de datums direct groeperen (in Jaren, Kwartalen en Maanden)
    LET OP één van de namen in de Rijen is nog steeds Datum, maar deze bevat nu de maanden.
    Wil je een andere groepering? Zie het artikel Groeperen in een draaitabel.
  5. sleep Jaren naar het Filters-gebied en verwijder Kwartalen uit de Rijen.
  6. de Soort is alfabetisch gesorteerd: versleep de kolom met de waarde Drie naar rechts (door de rand met de muis ‘vast te pakken’).
    Het resultaat staat in het tabblad Ovz1 van het Voorbeeldbestand.

Draaigrafiek

Een grafiek maakt de onderlinge verhoudingen tussen de cijfers vaak een stuk duidelijker:

  1. selecteer een cel van de draaitabel
  2. kies in de menutab Hulpmiddelen voor draaitabellen/Analyseren in het blok Extra de optie Draaigrafiek
  3. kies de optie Gegroepeerde kolom en klik op OK

Verander je nu iets in de draaitabel (filter je bijvoorbeeld een bepaald jaar uit) dan past de grafiek zich automatisch aan.

Draaigrafiek aanpassen 1

De standaard-grafiek kent wel wat nadelen.
Allereerst willen we van die ‘lelijke’ veldknoppen af: klik rechts op één van de knoppen en kies de optie Alle veldknoppen verbergen in grafiek.

Op het tabblad Ovz2 van het Voorbeeldbestand staat het resultaat:

LET OP Excel geeft alle kolommen automatisch een kleur; normaal wijzig ik deze handmatig in vaste kleuren zodat ook bij filtering iedere Soort zijn eigen kleur houdt. Helaas: bij draaigrafieken worden deze instellingen door Excel niet vastgehouden.

Draaigrafiek aanpassen 2

Ook al is een grafiek bedoeld om intuïtief inzicht in de onderliggende cijfers te krijgen, dan nog werkt het goed (of is het zelfs noodzakelijk) om in een grafiek de waarde(s) van de belangrijkste gegeven(s) te laten zien.

In het tabblad Ovz3 van het Voorbeeldbestand heeft de grafiek een veelzeggende titel meegekregen:

  1. in een lege cel creëren we daartoe eerst de volgende formule:
    =ALS(C2=”(Alle)”; “Totaal alle jaren: “;
    “Totaal voor ” & C2 &”: “) & TEKST(DRAAITABEL.OPHALEN(“Aantal”;$B$4);”#.##0″)
    Als in cel C2 alle jaren zijn gekozen dan nemen we een overeenkomende tekst, anders wordt de tekst Totaal voor met daarachter de inhoud van cel C2 (gekoppeld door het &-teken). Achter de zo gemaakte tekst plaatsen we met behulp van de functie DRAAITABEL.OPHALEN (zie het betreffende artikel) het totaal van Aantal. Met de functie Tekst geven we een opmaak mee (een punt voor de duizendtallen en geen decimalen).
  1. zorg dat de grafiek een Grafiektitel heeft; bijvoorbeeld op de volgende manier: selecteer de grafiek en klik op de + rechts daarvan en vink de betreffende optie aan.
  1. klik op de Grafiektitel en daarna in de formulebalk. Tik in = en klik dan op de cel uit de eerste stap (in Ovz3 is dat cel I2).
    In de formulebalk komt dan automatisch de formule:
    =’Ovz3′!$I$2. Druk op Enter.

Nog een paar slicers toevoegen (zie Slicers in Excel) en we hebben (het begin van) een interactief dashboard.

Bij het filteren in de draaitabel (hier mer behulp van slicers) kan het gebeuren dat het scherm ‘verspringt’; door een paar aanpassingen aan de draaitabel blijft de opmaak stabiel:

  • om te zorgen dat in jaren waar (nog) niet alle maanden gevuld zijn (in het voorbeeld 2020), toch alle maanden zichtbaar zijn (en dus ook in de grafiek): klik rechts op een van de maanden in de draaitabel, kies Veldinstellingen, vink op het tabblad Indeling&afdrukken de optie Items zonder gegevens weergeven aan
  • klik rechts op een van de cellen in het Waarden-gebied, kies Opties voor draaitabel en vink de optie Kolombreedte automatisch aanpassen uit

Draaigrafiek aanpassen 3

Maar we zijn niet gauw tevreden: we hebben nu het totaal aantal in de titel staan, maar wat zijn de totalen per maand? In de draaitabel staan de betreffende getallen netjes op het einde van iedere rij, maar we zien die niet terug in de grafiek.

Nog erger: er is ook geen optie om dat klaar te krijgen!
Uiteraard kunnen we met VBA aan de slag, maar daar is wel wat programmeer-arbeid voor nodig. Eens even kijken of het ook zonder kan: ja natuurlijk, we zorgen dat één reeks labels heeft en laten de inhoud van die labels wijzen naar de totalen per maand.

  1. zorg dat ergens in het tabblad een reeks cellen gevuld is met de rij-totalen. In het tabblad Ovz4 staan in de cellen C24:C35 verwijzingen naar de draaitabel met behulp van de functie DRAAITABEL.OPHALEN.
  2. klik op één van de kolommen in de grafiek
  1. klik op de + rechts van de grafiek en zet de Gegevenslabels aan, inclusief de optie Basis, binnenkant zodat alle labels op dezelfde hoogte komen

  1. klik rechts op een van de labels en kies de optie Gegevenslabels opmaken
  2. bij Labelopties kun je de waarden opgeven die weergegeven moeten worden (Waarde uit cellen). Maak hier een verwijzing naar de cellen C24:C35; vink de optie bij Waarde uit en dan Waarde uit cellen aan.

Helaas, deze methode heeft 2 (?) tekortkomingen (zie het tabblad Ovz4 van het Voorbeeldbestand):

  • de labels worden gecentreerd op de overeenkomende kolom
  • als de betreffende soort uitgefilterd wordt zijn ook de labels weg!

Draaigrafiek aanpassen 4

In het tabblad Ovz5 van het Voorbeeldbestand staat een grafiek die de totalen per maand toont, inclusief de procentuele verdeling over het jaar.

De volgende aanpassingen zijn doorgevoerd:

  1. allereerst is er ruimte gemaakt onder aan de grafiek: klik rechts op de linkeras, kies As opmaken en zorg dat de minimumgrens niet meer automatisch wordt bepaald maar (in dit geval) -200 is
  2. de notatie van de as is zodanig aangepast, dat de negatieve getallen niet worden weergegeven: #.##0;
  3. vanaf cel C24 worden de maandtotalen opgehaald:
    =DRAAITABEL.OPHALEN(“Aantal”;$B$4;”Datum”;B24)
  4. vanaf cel D24 bepalen we de inhoud van de teksten die we gaan toevoegen:
    =ALS(C24=0;””;
    TEKST(C24;”#.##0″)&
    TEKEN(13)&
    TEKST(C24/DRAAITABEL.OPHALEN(“Aantal”;$B$4);”0%”))
    Als C24 nul is dan hoeft er niets weergegeven te worden, anders de inhoud van cel C24 samen met het resultaat van het maandresultaat (C24) gedeeld door het totale resultaat (weergegeven als percentage zonder decimalen); tussen de twee elementen staat een code 13 (‘naar de volgende regel’).
    NB in cel D24 en verder is het resultaat van code 13 niet te zien, maar dadelijk in de grafiek wel.
  5. klik ergens in de grafiek en kies dan in de menutab Invoegen in het blok Illustraties de optie Vormen en daarna bij Basisvormen de optie Tekstvak en ’teken’ met de muis waar je het tekstvak wilt hebben
    LET OP als je niet eerst ergens in de grafiek klikt wordt het tekstvak niet aan de grafiek gekoppeld maar aan het tabblad; bij het verplaatsen van de grafiek gaat het tekstvak dan niet mee!
  6. klik in de formulebalk, tik het =-teken en klik met de muis op de cel met de tekst die weergegeven moet worden
  7. pas de grootte van de tekst en/of het tekstvak aan en verplaats het tekstvak naar de juiste plaats (pak met de muis de ‘rand vast’)
  8. herhaal de stappen 5 t/m 7 voor alle maanden
    NB je kunt ook het eerste tekstvak kopiëren (Ctrl-C) en dan via Ctrl-V zoveel tekstvakken maken als nodig zijn. Die moeten dan nog verplaatst worden en de inhoud aangepast.

Corona-data

Excel, , , ,
Voorbeeldbestand van 29-3: Corona.xlsxDownload

Vanaf 31 maart publiceert het RIVM andere gegevens dan daarvoor. Helaas is het dus niet meer mogelijk om via het voorbeeldbestand de (ver)spreiding van Corona op een consistente manier te volgen.
Op de site https://nlcovid-19-esrinl-content.hub.arcgis.com/ zijn wel nog diverse overzichten en kaarten te vinden.

Corona: de laatste weken beheerst deze crisis niet alleen het nieuws maar ook ons leven. Het einde is nog niet in zicht.
Het zou mooi zijn als we met Excel een bijdrage zouden kunnen leveren aan de oplossing er van.
Helaas, maar wat we wel kunnen, is proberen inzicht te geven in de omvang en voortgang van de besmettingen en overlevenden.

Het RIVM publiceert dagelijkse nieuwe gegevens en ook een kaartje dat de verspreiding van de besmettingen laat zien.

Voor G-Info is dit een goede aanleiding om eens te kijken hoe we van gegevens, die we over Corona kunnen vinden, informatie kunnen maken. Niet voor niets is onze hoofddoelstelling: van Gegevens naar Informatie.

In dit artikel gaan we eerst op zoek naar gegevens rond Corona. Daarna kijken we welke informatie we daaraan kunnen ontlenen. We zullen daarom diverse (vaak met behulp van draaitabellen gemaakte) overzichten bekijken. Als laatste zult u zien dat Voorwaardelijke opmaak heel handig is om snel binnen een grote hoeveelheid gegevens de uitschieters te signaleren.

Brongegevens

In dit artikel focussen we ons op de situatie in Nederland. Het is dan ook logisch dat we terecht komen bij het RIVM. Dit instituut publiceert iedere dag een update van de situatie op hun website rivm.nl.

Op deze site hebben we vanaf de uitbraak in Nederland kunnen terugvinden hoeveel mensen er besmet zijn geraakt en hoeveel daarvan er ondertussen zijn overleden (zie het tabblad DataNed van het Voorbeeldbestand).

Deze gegevens zijn dagelijks handmatig ingevoerd in een Excel-tabel met de naam tblNed. De kolom Cum bevat een formule, die een lopend cumulatief bepaalt (in cel D9 staat bijvoorbeeld =D8+C9); op een vergelijkbare manier worden ook de 5e en 7e kolom gevuld. In de laatste kolom (Actief) berekenen we het aantal personen, dat op dit moment nog besmet is: =[@Cum]-[@CumHerst]-[@CumOvl].

NB1 het RIVM geeft aan, dat de door hen gehanteerde cijfers geen exacte waarheid weergeven:
“Het werkelijke aantal besmettingen met het nieuwe coronavirus ligt hoger dan het aantal dat hier genoemd wordt. Dit komt omdat niet iedereen met mogelijke besmetting getest wordt, maar vooral patiënten die zo ziek zijn dat ze in het ziekenhuis opgenomen worden en zorgverleners.
Het aantal gemelde patiënten en overleden patiënten kan per dag verschillen om verschillende redenen. Zo zien we dat overleden patiënten niet altijd op dezelfde dag gemeld worden.”
Voor inzicht in de verspreiding lijken ze mij echter significant genoeg.

NB2 gegevens over personen, die hersteld zijn, zijn niet bekend bij het RIVM of worden niet geregistreerd. Op de website van de Johns Hopkins University over Corona worden wel aantal vermeld. Of deze op dit moment de werkelijkheid benaderen betwijfel ik.

NB3 dit artikel is in de loop van enkele dagen geschreven. Aangezien de werkmap in die dagen continu is bijgewerkt kunnen data in de afbeeldingen afwijken van die in het Voorbeeldbestand.

NB4 boven de tabel staat een dynamische kopregel; de inhoud past zich aan aan de datums in de tabel:
=”Corona in Nederland van “&TEKST(MIN(tblNed[Datum]);”d mmm jjjj”)&” t/m “&TEKST(MAX(tblNed[Datum]);”d mmm jjjj”)
Teksten worden aan elkaar gekoppeld met behulp van het &-teken; de minimum- en maximum-datum wordt met behulp van de functie Tekst van het gewenste formaat voorzien.

Als tweede bron is dagelijks een bestand gedownload van de RIVM-site (via het pijltje naast de kaart met gemelde Corona-gevallen).

In het Voorbeeldbestand is dit geautomatiseerd via de Power Query-tabel op het tabblad DagInput. Het bestand bevat het totaal aantal bekende Corona-gevallen per gemeente. Om de resultaten van de gemeentes met elkaar te kunnen vergelijken is dit aantal genormaliseerd (de kolom Aantal per 100.000 inwoners), namelijk door te delen door het aantal inwoners van die gemeente (en met 100.000 te vermenigvuldigen).

Deze gegevens (zonder de kopregel) zijn iedere dag gekopieerd naar de Excel-tabel tblGem op het tabblad DataGem van het Voorbeeldbestand.

LET OP vanaf 27 maart bevat het bestand van het RIVM 4 namen van gemeenten, die anders gespeld worden dan daarvoor:
Bergen (L.) moet zijn: Bergen (L)
Bergen (NH.) moet zijn: Bergen (NH)
Hengelo moet zijn: Hengelo (O)
s-Gravenhage moet zijn: ‘s-Gravenhage (in de Excel-cel moet dus een dubbele ‘ komen)
De Power Query routine is hier op aangepast, anders met de hand wijzigen in de tabel tblGem.

Een ander bronbestand (zie het tabblad ProvGemeente) is een overzicht van alle gemeentes in Nederland met daarachter het inwoneraantal, een indeling naar klein, middelgroot en groot en de bijbehorende provincie.

NB De gegevens zijn ontleend aan de website van het Ministerie van Sociale Zaken en Werkgelegenheid (stand van 1 jan 2019).

Overzicht stand van zaken Nederland

Het overzicht in het tabblad DataNed van het Voorbeeldbestand laat het verloop in de tijd zien van het aantal besmettingen, overledenen en herstelden. Maar hoe de getallen zich tot elkaar verhouden is moeilijk te onderscheiden. Een grafiek is geschikter om dit te laten zien:

De eerste grafiek (staafdiagram) laat het aantal nieuwe geregistreerde besmettingen, herstelden en overledenen per dag zien (tabblad GrafNed1).
De lijngrafiek (tabblad GrafNed2) toont de cumulatieven daarvan in de tijd.
In de derde grafiek (tabblad GrafNed3) wordt door het gebruik van vlakken de opdeling van het aantal cumulatieve besmettingen zichtbaar gemaakt.

NB1 bij het schrijven van dit artikel was het aantal herstelden nog gering (volgens de gehanteerde bronnen); resultaten daarvan zijn in de grafieken dan ook nauwelijks/niet zichtbaar.

NB2 in de laatste grafiek moet het oranje vlak (de actieve besmettingen) in de loop van de tijd steeds meer de veel besproken ‘uitgevlakte’ curve laten zien. Het groene vlak (herstelden) zal steeds groter moeten worden. Een fictief voorbeeld staat hiernaast.

Overzicht per gemeente, basis

Zoals bij de bronnen aangegeven bevat het tabblad DataGem van het Voorbeeldbestand de totaal-aantallen geregistreerde besmettingen per gemeente per dag.

Om hierna diverse analyses op de gegevens uit te kunnen voeren zijn de brongegevens in deze tabel verrijkt:

  1. in kolom F wordt de dagelijkse groei van het aantal voor een gemeente berekend:
    =[@Aantal]-SOMMEN.ALS([Aantal];[Datum];[@Datum]-1;[Plaats];[@Plaats])
    Trek van het Aantal in een bepaalde rij het Aantal af van de vorige dag bij die gemeente.
    NB SOMMEN.ALS telt alle Aantallen op waarvan de Datum gelijk is aan de Datum in die regel minus 1 en waar de Plaats gelijk is aan de Plaats in die regel. In principe is er altijd maar 1 geval, die aan deze voorwaarden voldoet.
  2. de indeling van de gemeente naar grootte wordt in kolom G bepaald door dat gegeven op te zoeken in tblGemProv in het tabblad ProvGemeente:
    =INDEX(tblGemProv[Grootte];VERGELIJKEN([@Plaats];tblGemProv[Gemeentenaam];0))
  3. op een vergelijkbare manier worden in de kolommen H en I respectievelijk de provincie en het aantal inwoners van een gemeente opgehaald.
  4. de genormaliseerde aantallen per gemeente (uitgedrukt in Aantal besmettingen per 100.000 inwoners) wordt door het RIVM aangeleverd; kolom D.
    Om provincies goed met elkaar te kunnen vergelijken moeten ook de aantallen per provincie worden genormaliseerd. In kolom J wordt de benodigde berekening uitgevoerd:
    =[@Aantal]/ INDEX($N$5:$N$16;VERGELIJKEN([@Prov];$M$5:$M$16;0)) *100000
    NB de kolommen M en N bevatten een draaitabel die het aantal inwoners per provincie bepaald.

Overzicht per provincie (1)

Waar in Nederland zitten de meeste Corona-gevallen? Met behulp van een draaitabel kunnen we snel een overzicht per provincie maken (gerangschikt van noord naar zuid; zie tabblad OvzProv1 van het Voorbeeldbestand):

Uit de bijbehorende draaigrafiek kunnen makkelijker conclusies getrokken worden (tabblad GrafProv1):

  • in absolute zin is het aantal besmettingen vanaf het begin (van de registratie in dit Excel-bestand, 19 maart) het grootst in Noord-Brabant, gevolgd door Noord- en Zuid-Holland en Gelderland en Limburg.
  • Deze laatste provincie kende een relatief grote groei op 24 maart.
  • De hoop dat de groei in Brabant zou gaan afvlakken zien we nog niet terug (dan zouden de lijnen daar steeds dichter bij elkaar moeten gaan liggen; op 27 maart zien we zelfs weer een groei) .
  • Ook neemt de groei in Noord- en Zuid-Holland toe.
  • In Limburg blijft de groei de laatste dagen gelijk.
  • De noordelijke provincies en Flevoland en Zeeland lijken nog weinig ‘geraakt’.

LET OP wanneer er gegevens voor een nieuwe datum zijn toegevoegd dan moet de draaitabel vernieuwd worden. Aangezien alle draaitabellen in deze werkmap dezelfde bron gebruiken (tblGem) worden al deze draaitabellen dan tegelijkertijd ververst.

Overzicht per provincie (2)

Wanneer we eenzelfde draaitabel sorteren van ‘hoog naar laag’ op de gegevens van de laatste dag (tabblad OvzProv2) dan ziet de bijbehorende draaigrafiek er als volgt uit (tabblad GrafProv2):

  • Noord- en Zuid-Holland gaan ongeveer gelijk op
  • Gelderland ‘haalt Limburg langzaamaan in’

Overzicht per provincie (3)

Maar als je provincies echt met elkaar wilt vergelijken moet je ook de verschillen in grootte daarbij betrekken. Op het tabblad OvzProv3 is daarom een draaitabel gemaakt van de genormaliseerde aantallen:

  • relatief zijn er dus in Brabant en Limburg de meeste besmettingen (toch gerelateerd aan Carnaval?)
  • we horen weinig over Utrecht, maar die komt in dit overzicht op de 3e plaats
  • Noord-Holland heeft relatief duidelijk meer besmettingen dan Zuid-Holland
  • de provincie Zeeland kende in het begin relatief evenveel besmettingen als Zuid-Holland; deze laatste vertoont echter in de loop van de tijd een grotere groei.
  • maar het virus moet ook in de ‘kleinere’ provincies niet onderschat worden

Overzicht per gemeente (1)

Voor diegene die nog wat ‘dieper willen kijken’ kunnen we op basis van de RIVM-cijfers op gemeenteniveau inzoomen. Het mooiste is natuurlijk om dit op een kaart zichtbaar te maken. De stand van 24 maart hebben we met behulp van Datawrapper.de ‘vertaald’:

Nieuwsgierig naar details? Klik op de afbeelding.

‘Uiteraard’ komen daarmee de grote plaatsen boven drijven. Dat is ook de reden, dat het RIVM vanaf het begin een genormaliseerde kaart heeft getoond. Dat hebben we voor 24 maart ook gedaan:

Klik op de afbeelding.

Op deze manier is beter te onderscheiden waar het virus zich vooral heeft verspreid. Naast de bekende plaatsen in Oost-Brabant zien we ook mogelijke haarden in Alphen-Chaam en Peel en Maas.

Aangezien bovenstaande methode nogal bewerkelijk is, zullen we voor een verdere detaillering gebruik maken van draaitabellen.
Op het tabblad OvzGem1 van het Voorbeeldbestand ziet u per provincie de verdeling naar gemeente. Om goed zicht te krijgen op de groei per dag kunt u (handmatig) een sortering aanbrengen; in het voorbeeld is dit op de laatste kolom uitgevoerd.

Vooral in de plaatsen aan de oost-kant van Brabant is het aantal besmettingen weer fors toegenomen. Op 27 maart kent ook Boekel weer 9 nieuwe gevallen, terwijl de dagen daarvoor de groei nul was.

NB1 in het voorbeeld hierboven zijn enkele dagen niet zichtbaar; via Beeld/Blokkeren is in cel E7 een Titelblokkering geplaatst.

NB2 wilt u een andere provincie zien? Maak een keuze in cel C2.

Overzicht per gemeente (2)

Grote steden kennen in absolute zin al snel veel besmettingen. Het effect daarvan kunnen we bekijken op het tabblad OvzGem2 van het Voorbeeldbestand.

Overzicht per gemeente (3)

Maar pieken zijn duidelijker te zien wanneer we de genormaliseerde aantallen in een draaitabel zetten (zie tabblad OvzGem3 in het Voorbeeldbestand):

In deze draaitabel is 2 keer het veld Aantal per 100.000 inwoners geplaatst.

Door rechts te klikken op de 2e kolom kunnen de Waardeveldinstellingen aangepast worden. Zoals u hiernaast kunt zien, zal Excel het Verschil met de Vorige Datum berekenen.

NB De kolomnamen zijn handmatig aangepast.

Voor allebei de waardevelden is een Voorwaardelijke opmaak ingesteld zodat uitschieters snel zichtbaar worden. Kijk in de menutab Start in het blok Stijlen bij Voorwaardelijke opmaak/Regels beheren.

LET OP wanneer er gegevens van een nieuwe datum bij zijn gekomen dan moet ook voor de nieuwe kolommen de Voorwaardelijke opmaak ingeregeld worden in de kolom Van toepassing op.

Datumverschil ofwel ouderdom ofwel leeftijd

Excel, ,
Voorbeeldbestand: DatumVerschil.xlsxDownload

Ik kreeg onlangs de vraag hoe met behulp van voorwaardelijke opmaak bepaalde machines in een Excel-overzicht ‘gekleurd’ konden worden.
De achterliggende vraag was hoe aan de hand van een in-productie-name-datum de ‘ouderdom’ bepaald kon worden.

Algemener geformuleerd: hoe kunnen we de leeftijd van iets of iemand bepalen?
In dit artikel een simpele rechttoe-rechtaan-berekening, maar ook een methode waarbij gebruik gemaakt wordt van de ‘geheime’ Excel-formule Datumverschil.

Standaard leeftijd-berekening

Mijn eerste antwoord op de vraag was om het jaar van de startdatum af te trekken van het huidige jaar (zie cel C5 in het tabblad Standaard van het Voorbeeldbestand).

Om het systeem flexibeler te maken gebruiken we geen harde waarde voor het huidige jaar, maar nemen we het jaar van Vandaag() (cel C3; denk aan de ‘loze’ haakjes op het einde).

Direct kwam als reactie: Dank, maar ….
Het bekende leeftijd-probleem: het laatste jaar mag niet meetellen als de huidige dag-maand-combinatie vóór die van de startdatum ligt.

Snel opgelost (zie cel C6 in het voorbeeld):
=JAAR(C3)-JAAR(C2)-ALS(TEKST(C3;”mm-dd”)<TEKST(C2;”mm-dd”);1;0)

De berekening van cel C5 corrigeren we door te kijken of de maand-dag-combinatie van de einddatum daar aanleiding toe geeft.
Zo ja, dan trekken we 1 af van het resultaat en anders 0.

NB1 het isoleren van de dag en maand doen we in deze formule door van de datum teksten te maken, waarbij we alleen de maand en dag meenemen (zie het artikel Data (datums)).

NB2 let op de volgorde van dag en maand in de Tekst-functie.

NB3 de formule kan nog wat korter (zie cel C7):
=JAAR(C3)-JAAR(C2)-(TEKST(C3;”mm-dd”)<TEKST(C2;”mm-dd”))
We maken hierbij gebruik van het feit, dat, wanneer een vergelijking (in dit geval: kleiner dan) WAAR is, Excel intern hier de waarde 1 aan toekent en anders de waarde 0.

NB4 de werking van een formule kun je altijd bekijken met behulp van de optie Formule evalueren; te vinden in de menutab Formules in het blok Formules controleren.

Leeftijd

Maar nu we toch bezig zijn: de leeftijd kun je natuurlijk ook in dagen, weken of maanden uitdrukken. Hoe bereken je die?

  • de leeftijd in dagen is in Excel heel gemakkelijk: =C3-C2
    Hierbij maken we gebruik van de eigenschap dat in Excel een datum intern wordt vertaald naar het aantal dagen dat verstreken is sinds 1 jan 1900.
  • het aantal weken is het voorgaande resultaat gedeeld door 7.
    Alleen geïnteresseerd in hele weken? In cel D11 van het Voorbeeldbestand, tabblad Standaard, staat de formule =GEHEEL(C10/7).
  • voor het aantal maanden hanteren we een benadering: de leeftijd in dagen gedeeld door het gemiddeld aantal dagen per maand.
    In cel C12 staat de formule: =C10/DagenMnd.
    NB1 deze benadering werkt goed als er flink wat jaren tussen begin- en einddatum zitten.
    NB2 een exacte berekening staat in cel F12: =12*(JAAR(C3)-JAAR(C2)-1)+(12-MAAND(C2))+(MAAND(C3)-1)
    NB3 DagenMnd is een verwijzing naar cel C3 in het tabblad BasisGeg van het Voorbeeldbestand .
  • het aantal jaren is in cel C13 benaderd door het aantal leeftijd-dagen te delen door een gemiddeld aantal dagen per jaar (365,25 in verband met de schrikkeljaren).

Hiervoor hebben we gezien hoe we de leeftijd in jaren kunnen bepalen.

Wil je ook nog weten hoeveel maanden of dagen er ondertussen in het laatste (onvolledige) jaar respectievelijk maand zijn verstreken dan hebben we nog 2 extra berekeningen nodig:

  • Restmaanden: =D12-D13*12
    ofwel de leeftijd in maanden minus de leeftijd in jaren maal 12
  • Restdagen: =C10-D12*DagenMnd
    ofwel de leeftijd in dagen minus de leeftijd in maanden maal het gemiddeld aantal dagen per maand
    LET OP de laatste berekening levert een getal op met decimalen. Door de benadering kan het resultaat afwijken van de exacte waarde.

Niet iedereen kan het resultaat van zo’n berekening in de vorm van een tabel goed lezen.
Daarom kan het soms handig zijn om de resultaten op te nemen in een tekst.
Een voorbeeld is te vinden in cel B18:
=”Leeftijd is “&D13&” jaar, “&D15&” maanden en “&D16&” dagen”
Teksten en cel-verwijzingen worden aan elkaar gekoppeld met het &-teken.

LET OP om teksten en de waardes uit de cellen van elkaar te scheiden zijn aan de teksten op diverse plaatsen spaties toegevoegd.

Wil je geen decimalen bij de dagen weergeven:
=”Leeftijd is “&D13&” jaar, “&D15&” maanden en “&TEKST(D16;”0″)&” dagen”
LET OP met de Tekst-functie wordt het resultaat afgerond niet ‘afgehakt’.

Een iets andere vorm van deze formule:
=”Leeftijd is ” & D13 & ” jaar, ” & D15 & ” maanden en ” &
TEKST(D16;”0 “”dagen”””)
De tekst dagen is in de opmaak van cel D16 opgenomen.
LET OP Tekst binnen de opmaak moet tussen “-tekens staan; omdat dit binnen een andere set aanhalingstekens komt moeten er zelfs dubbele “-tekens gebruikt worden.

Functie Datumverschil

Waar we in het voorgaande alle berekeningen uitvoerden met eigen formules blijkt Excel ‘onder de motorkap’ een functie te hebben waarmee de resultaten sneller kunnen worden geproduceerd

(en exacter omdat er geen benaderingen nodig zijn voor de dagen per jaar en per maand).

Excel bevat namelijk de functie Datumverschil. Deze kent drie parameters/argumenten: een begindatum, een einddatum en een aanduiding welk resultaat berekend moet worden (het aantal dagen, maanden of jaren).
De functie wordt door Microsoft niet getoond in het overzicht bij Functie invoegen. Dus de functie moet handmatig ingevoerd worden: =datumverschil(arg1;arg2;arg3).

Ook zul je in het scherm Functieargumenten geen toelichting op de verschillende argumenten vinden.

NB Microsoft houdt wat slagen om de arm:

Als we nu het aantal dagen tussen een begin- en einddatum willen weten kunnen we de volgende formule gebruiken: =datumverschil(C2;C3;”d”), waarbij we er vanuit gaan dat cel C2 de begindatum bevat en C3 de einddatum.
Willen we het aantal maanden weten, dan vervangen we “d” door “m”. Om het aantal jaren te weten te komen, moet de derde parameter “y” zijn.

LET OP gebruik de y en niet de j. Hoofdletters zijn wel toegestaan. Het is niet duidelijk of dit vertaal-probleem in iedere versie van Excel optreedt. Experimenteren dus.

NB in het Voorbeeldbestand in het tabblad DatumVerschil staat overal als derde argument een celverwijzing. Dan worden de “-tekens weggelaten.

Maar … de functie kent nog meer opties voor de derde parameter: gebruik je yd, dan worden de resterende dagen berekend (dus het aantal dagen na de vorige “verjaardag”); gebruik je ym, dan is het resultaat het aantal resterende maanden en bij het invoeren van md wordt het aantal resterende dagen bepaald, die niet in een volledige maand zitten.

Dan nog een waarschuwing van Microsoft:

LET OP de oplossing die hier aangedragen wordt levert niet hetzelfde op als bedoeld met MD. Hier worden slechts de dagen geturfd in de lopende maand op het einde; de ‘losse’ dagen aan het begin worden niet meegenomen.