Tagarchief: Tekst

Totalen in draai-grafiek



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.

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


Corona-data



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.


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


Datumverschil ofwel ouderdom ofwel leeftijd



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.


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

Voetbalcompetities



Je hoeft niet per se van voetballen te houden om dit artikel te kunnen waarderen.
Ik kreeg van Nico van der Meer een mooi Excel-systeem toegestuurd, dat hij graag met anderen wil delen: het verwerken van voetbaluitslagen tot overzichten en standen, inclusief consequenties voor nacompetities, Champions League etcetera.

In eerste instantie dacht ik: deze sheet bevat wel veel formules, daar zullen we snel wat fouten of mogelijke problemen uit kunnen halen.
Maar niets was minder waar; dus ik geef de werkmap door met maar een paar kleine aanpassingen (verbeteringen?).

Met het systeem van Nico kun je de hele competitie 2018-2019 plezier hebben; alles is al voorbereid, inclusief speeldata en zelfs tijden!
Maar, zoals uit het plaatje hierboven mag blijken, zijn dit soort competities behoorlijk onderhevig aan wijzigingen, dus voor volgend jaar zul je wel weer flink wat aanpassingen moeten doen.

Hieronder zal ik de werking van het systeem kort toelichten en ook een handreiking geven over de aanpassingen voor een volgende competitie-ronde.
Uiteraard zal ik ook de meest gebruikte Excel-functies en de gebruikte VBA-routines toelichten.

Voetbal-competitie

Voordat er zo’n mooi overzicht ontstaat moet er natuurlijk wat voorwerk verricht worden.
In het tabblad Info van het Voorbeeldbestand heeft Nico al diverse aanwijzingen gegeven.

Het systeem omvat 3 competities, Ere-, Eerste – en Tweede Divisie (de EersteDivisie heeft nu de naam KeukenKampioen).
In het tabblad Data liggen de gegevens van de competitieronde en van de teams per divisie vast. Ook kan daar aangegeven worden of de beveiliging (ter voorkoming van het overschrijven van formules) wel of niet moet worden aangezet.

Het tabblad Speelschema bevat per divisie een overzicht van alle rondes en de wedstrijden die daarbinnen gespeeld moeten worden; dit alles inclusief de betreffende datum en tijd. Deze gegevens zijn op internet terug te vinden; via de hyperlink boven iedere divisie zijn de relevante zaken te benaderen.

Klik rechts op een Hyperlink en dan Hyperlink bewerken om te zien hoe deze opgebouwd is.

LET OP om een hyperlink te bekijken moet de beveiliging van het tabblad verwijderd zijn. Dit kan via rechtsklikken op de tab onderaan of door in het tabblad Data aan te geven dat alle beveiligingen verwijderd moeten worden.

Wilt u met dit systeem de resultaten van een competitie volgen dan moeten op dit tabblad Speelschema de uitslagen handmatig ingevoerd worden (voor de Eredivisie in de kolommen C en D).

Resultaten en stand Eredivisie

In het tabblad Eredivisie van het Voorbeeldbestand worden de ingevulde wedstrijdresultaten automatisch verwerkt.

In cel G6 wordt bijvoorbeeld het aantal doelpunten opgehaald voor de thuisclub van de wedstrijd PSV – AZ Alkmaar mbv de formule
=ALS(ISLEEG(VERT.ZOEKEN(TEKST($B6;)&” – ” & TEKST($G$5;);SchemaED;2;0)); “”;VERT.ZOEKEN(TEKST($B6;)&” – “&TEKST($G$5;);SchemaED;2;0))

Via Verticaal Zoeken wordt de wedstrijd (B6, dus PSV, tegen G5, dus AZ) opgezocht in het bereik met de naam SchemaED. De inhoud van de 2e kolom wordt als resultaat teruggegeven. De derde parameter (een 0, nul) geeft aan dat een exacte zoekactie naar de wedstrijdnaam moet worden uitgevoerd.
Als de zoekactie niets oplevert (er is nog niets ingevuld) dan komt er in dit schema ook niets te staan (de dubbele aanhalingstekens), anders wordt het resultaat van de zoekactie in de cel geplaatst.

NB1 de namen van de teams worden door een spatie, een min-teken en opnieuw een spatie van elkaar gescheiden. Bij de opvoer in Speelschema moet dit ook exact zo zijn gedaan.

NB2 Nico heeft de team-namen niet rechtstreeks aan elkaar gekoppeld, maar heeft de functie Tekst gebruikt. Waarschijnlijk om er zeker van te zijn dat Excel deze betreffende cellen zeker als tekst zal zien. Naar mijn idee overbodig.

De overige formules onder en naast het schema wijzen zichzelf.

Ook de standentabel wordt automatisch ingevuld.
Via Vergelijken wordt gekeken op welke positie het betreffende team in de vorige tabel staat en dan wordt via Index het gewenste aantal winst-punten opgehaald (uit en thuis opgeteld).

De formules in de overige kolommen zijn vergelijkbaar of ‘rechttoe-rechtaan’.

In kolom BW staat een code, die gebruikt wordt om de teams in de juiste volgorde te kunnen sorteren en de Plaats in de stand te bepalen.
Wanneer namelijk nieuwe resultaten worden toegevoegd in Speelschema, dan worden wel alle berekeningen direct uitgevoerd en ook de Plaats wordt bepaald, maar de volgorde in de standenlijst verandert niet. Wil je ook de volgorde juist hebben, klik dan op de knop Standen bijwerken. De betreffende VBA-routine (zie hierna) zal dan de sortering uitvoeren.

In cel BW6 staat de formule =WAARDE( TEKST(BS6;”000″)&TEKST(100+BT6;”000″)&TEKST(BU6;”000″)&TEKST(100-BN6;”00″)&TEKST(AANTAL.ALS($BM$6:$BM$23;”>=”&BM6);”00″))

Ook hier wordt de functie Tekst gebruikt; in dit geval om zeker te weten dat het betreffende getal altijd met exact hetzelfde aantal cijfers wordt weergegeven.
Met de functie Waarde wordt van de resulterende tekst weer een getal gemaakt.

Nico heeft in een opmerking bij Code netjes vermeld hoe de code is opgebouwd.

NB bij de Eredivisie zijn na 3 speelrondes al geen gelijke standen meer; bij de KK-divisie na 2 speelrondes nog wel.

In kolom BL wordt de plaats van het team in de stand bepaald: =RANG(BW6;$BW$6:$BW$23)
Met behulp van Voorwaardelijke opmaak krijgen bepaalde Plaatsen ook nog een kleurcode; dit is dus onafhankelijk van de sortering.

Overige divisies en nacompetitie

Bij de KK-divisie heeft Nico ook nog overzichten gemaakt ten behoeve van de bepaling van de periode-kampioenen. Zover ik nu heb kunnen zien heeft hij alle lastige afwijkingen en problemen daarmee uitstekend opgelost.

Wat de nacompetitie betreft: de maker van het systeem is nog niet helemaal zeker over de juistheid daarvan.

“De nacompetitie is voor mij nog experimenteel gezien de complexe regels.
Ik had niet verwacht, dat ik tot iets zou kunnen komen, maar al werkende daaraan ontstond er toch iets”.

Nieuwe competitieronde

Wilt u dit systeem ook een volgend seizoen gebruiken, dat moet er nogal wat aangepast worden:

  1. in het tabblad Data van het Voorbeeldbestand het seizoen aanpassen
  2. ook in Data de teams in de drie divisies aanpassen (hoeven niet per se gesorteerd te zijn)
  3. op het tabblad Data de teams van de Eredivisie selecteren en Ctrl-C (kopiëren) drukken; in het tabblad Eredivisie op de cel BM6 rechts klikken en Plakken speciaal/Waarden kiezen
  4. dezelfde werkwijze hanteren voor de teams van de eerste en tweede divisie
    LET OP bij de eerste divisie moeten de teams vijf keer geplakt worden.
  5. in het tabblad Speelschema alle uitslagen wissen en de wedstrijden die gespeeld zullen gaan worden overschrijven.
    LET OP zorg dat de namen van de clubs exact gelijk zijn aan die in het tabblad Data en dat de 2 ploegen gescheiden worden door een spatie, min-teken en nog een spatie

VBA

Met behulp van de knoppen Standen bijwerken worden bijbehorende VBA-routines uitgevoerd.
Voor de Eredivisie ziet die er als volgt uit:
Het bereik met de naam StandEredivisie wordt gesorteerd op kolom BW  en wel in Aflopende volgorde (grootste getal eerst) en het bereik heeft geen Kop.

Alle andere sorteringen gaan op dezelfde manier. Bij de KK-divisie worden 5 sorteringen achter elkaar uitgevoerd; dus voor alle schema’s van het betreffende tabblad.

In het Excel-object ThisWorkbook staat de volgende routine:

De For-Next-loop loopt alle tabbladen langs en afhankelijk van de inhoud van de cel met de naam BevJN (deze staat in het tabblad Data) wordt de beveiliging aan of uit gezet (zonder wachtwoord).

Application.ScreenUpdating zorgt er voor, dat het scherm niet gaat ‘flikkeren’ bij het selecteren van een tabblad.


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

Functies grafisch weergeven



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

Eerstegraadsfunctie

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Tweedegraadsfunctie

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

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

 

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

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

 

Derdegraadsfunctie

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


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