Overlappende datumperiodes, deel I

Voorbeeldbestanden bij dit artikel:
  • Overlappende datumperiodes
  • Inleiding



    Voor alle voetballiefhebbers daar... we zijn allemaal razend benieuwd naar het Wereldkampioenschap voetbal gaat winnen! Morgen is er de finale van wat een interessant kampioenschap geworden is. In dit artikel (het eerste van 2 artikels over hetzelfde onderwerp met hetzelfde voorbeeldbestand) zal ik schrijven over overlappende datumperiodes, waarbij ik de Wereldbeker als een praktisch voorbeeld neem. Eigenlijk staan heel veel studenten voor een moeilijk dilemma dezer dagen: studeren of voetbal kijken? :-) In België, het grootste gedeelte van de maand juni is bestemd voor de examens aan universiteiten en hogescholen. Ook in juni worden een groot aantal wedstrijden van het Wereldkampioenschap voetbal gespeeld. Examenperiodes kunnen van jaar tot jaar verschillen, en ook de kalender van het WK voetbal niet vast. Bijvoorbeeld, tijdens de oneven jaren, is er geen groot voetbaltornooi. Over 2 jaar zal er het Europees Kampioenschap voetbal zijn in Frankrijk, wat weer tot een aantal conflicten zal leiden in de agenda's van veel studenten.

    Het huidige artikel en voorbeeldbestand tonen jullie een mooie manier om de periodes van voetbal en examens grafisch voor te stellen met behulp van (enkel) Voorwaardelijke opmaak. Hierbij zullen we zeker een aantal datumfuncties in Excel nodig hebben om datumperiodes en hun overlap te berekenen.

    Voorwaardelijke opmaak

    Laat ons eens kijken naar de overlap tussen examenperiodes en voetbaltornooien. Examenperiodes worden gedefinieerd (door mezelf) als startende op de eerste maandag van juni, en dat gedurende 4 volle weken;, behalve de laatste zondag - dus we veronderstellen dat ze stoppen op de 4de zaterdag na het begin. Dit is goed genoeg als benadering, exacte datums zijn niet nodig voor deze oefening. In feite kan je zelf bepaalde datums overtypen in het werkblad en verschillende periodes hebben. Of je kan zelfs het hele thema van "examens" veranderen in iets dat meer zin heeft voor jou. De andere datumperiode is het groot voetbaltornooi dat elke 2 jaar gehouden wordt: een Europees Kampioenschap in de even jaren die deelbaar zijn door 4, een Wereldkampioenschap in de overige even jaren. In de oneven jaren wordt er geen groot voetbaltornooi georganiseerd. Voor de tornooien die reeds voorbij zijn kennen we de exacte datums wanneer de ploegen hun wedstrijden speelden, maar voor toekomstige tornooien zijn de exacte datums nog niet bekend. In dat geval zal ik een vraagteken zetten in plaats van de datum.

      In de afbeelding hierboven kan je 3 kleuren zien, die het grafische resultaat zijn van bepaalde berekeningen:
    1. Rood is de ergste periode: er zijn examens maar geen voetbaltornooi! The overvloed aan rood wordt uiteraard verklaard door de oneven jaren.
    2. Oranje is wat better: examens maar voetbal tegelijkertijd
    3. Groen is de beste situatie: voetbal zonder examens !
    4. Tot slot, witte cellen betekenen geen voetbal en geen examens

    Bovendien voeg ik een grafisch element toe voor de weken in juni en juli. De vertical lijntjes markeren de weken (maandag tot en met zondag). Dit doe ik ook met Voorwaardelijke opmaak. Sinds elke cel in het bereik van 1 juni tot 31 juli een formule bevat (die ik jullie zeker zal uitleggen), kunnen we de cellen opmaken op basis van het resultaat van de formule. Aangezien Excel 2007 en hoger aanzienlijk meer opties toelaten voor Voorwaardelijke opmaak in vergelijking met Excel 2003 en vroeger, kan je het voorbeeldbestand enkel in de meer recente versies van Excel gebruiken. De cellen kunnen voldoen aan tot wel 8 voorwaarden, dus de maximaal 3 voorwaarden die we in Excel 2003 hadden zullen ontoereikend zijn! Gelukkig beschik ik al een tijdje over Excel 2013!

    Ik stel voor om in dit artikel de formules te bespreken, behalve de formule in de gekleurde cellen. Die zijn niet uitermate complex, maar simpel zou ik ze ook niet noemen. Kijkende naar het (downloadbare) bestand, zien we:

    Na de jaren in kolom A, hebben we een eerste deel in het geel ove rde examens, en een tweede deel in het grijs over de voetbaltornooien. Merk op dat de woorden EXAMENS en VOETBAL niet in samengevoegde cellen, aangezien ik samengevoegde cellen hartsgrondig haat :-) Hetzelfde visuele effect wordt gepresenteerd met de Uitlijning "Center Across Selection". Veel beter naar mijn mening. Voor examens, hebben we de volgende formules:

    1. Kolom B: De eerste juni voor dat jaar. Simpel, de DATUM functie.
    2. Kolom C: De datum van de eerste maandag in juni. Dit is al lastiger. We nemen de vorige cel in kolom B (1 juni) en voegen er een aantal dagen aan toe. We gebruiken de WEEKDAG functie om te kijken op welke dag in de week (1 tot en met 7) 1 juni valt/viel/zal vallen. Als het een vrijdag is, voegen we 3 toe om te compenseren voor zaterdag, zondag, maandag. Analoog voor de andere dagen van de week. Als 1 juni toevallig een maandag blijkt te zijn, moeten we geen dagen toevoegen, in plaats van 7 dagen toe te voegen tot maandag de week erop. Daarom neemt de REST functie de overschot na deling door 7.
    3. Kolom D: 4 weken later. We voegen 4 volledige weken van 7 dagen toe en zouden zo uitkomen op een maandag, maar daarvan trekken we weer 2 dagen af om op de zaterdag voorheen uit te komen.
    4. Kolom E: duur. Simpel voor de formule, maar kijk ook eventjes bij de getalopmaak in de cel.

    Het tweede deel is voetbal.

    1. Kolom F: Kampioenschap. Een formule gebaseerd op wat ik eerder schreef over jaren die deelbaar zijn door 4 of niet. De functie ISONEVEN is wellicht niet bekend bij iedereen, dit is enkel een simpelere formule dan te checken of de REST bij deling door 2 als resultaat 1 geeft of 0.
    2. Kolommen G tot J: data input.
    3. Kolom K: een interessante formule, waarbij de N functie aangewend wordt om een tekst zoals "?" te converteren naar 0. Getallen zullen getalleNumbers blijven bij de N functie. De MIN functie berekent het minimum van (in dit geval) 2 getallen.

    Filteren op sommige kolommen

    Als een randbemerking bij dit artikel, kijk eens naar kolommen A, F, I, J voor rij 2. Je zal daar de pijlen terugvinden voor de Autofilter. In het standaardgeval zal bij het inschakelen van een Autofilter, elke kolom een pijltje krijgen. Wil je dat niet, dan is er gelukkig een manier om de pijltjes te verbergen waar je niet wil dat ze verschijnen. Dat is alvast goed nieuws! Het slechte nieuws is dat je een stukje VBA-code nodig hebt. Maar het slechte nieuws wordt onmiddellijk goed nieuws aangezien ik jou hier voorbeeldcode geef.

    VBA procedure

    Hier is mijn code:

    Sub SelectieveAutofilterPijlen()
    ' Wim Gielis ' http://www.wimgielis.com
    ''''' ' VBA-code om de Autofilter keuzepijlen enkel voor bepaalde kolommen te tonen ' 07/09/2014 '''''
    Dim c As Range Application.ScreenUpdating = False For Each c In Intersect(ActiveSheet.UsedRange, Rows(3)) 'Optie 1 c.AutoFilter Field:=c.Column, _ Visibledropdown:=InStr("-1-6-9-10-", "-" & c.Column & "-") 'Optie 2 'c.AutoFilter Field:=c.Column, _ Visibledropdown:=(c.Column = 1) + (c.Column = 6) + (c.Column = 9) + (c.Column = 10) 'Optie 3 - Excel 2013 (en hoger) enkel 'c.AutoFilter Field:=c.Column, _ Visibledropdown:=WorksheetFunction.Xor(c.Column = 1, c.Column = 6, c.Column = 9, c.Column = 10) Next
    End Sub

    In de code maak ik een lus over de cellen in rij 3, daar waar de cellen deel uitmaken van de "UsedRange van het werkblad. Voor elke cel vraag ik de .Column eigenschap uit en onderneem ik (mogelijks) een actie. De actie is het verbergen van de Autofilter dropdown pijl voor die kolom. Mijn voorkeur gaat uit naar Optie 1, de andere opties geef ik jullie om om inspiratie op te doen. Niet alle opties zijn even lang qua code, het uitvoeren van de code zal uiteraard quasi niet verschillen in tijd. Experimenteer aub met de gegeven oplossingen, als je in het bezit bent van Excel 2013 kan je ook gebruik maken van de functie Xor, een mooie recente toevoeging aan de set van bestaande Excel formules.

    Laat mij dit artikel besluiten met jou te gidsen naar het tweede deel van dit artikel: deel II over het uitleggen van de Voorwaardelijke opmaak, warm aanbevolen als je je kennis over Voorwaardelijke opmaak wil vergroten!




    Over Wim

    Wim Gielis is Business Intelligence consultant en Excel expert

    Andere links