Overlappende kalenderperiodes, deel II
- 11/07/2014
 | Voorbeeldbestanden bij dit artikel: |
Inleiding
Deze pagina is een verderzetting van het eerste deel, waar we overlappende kalenderperiodes bekeken hebben. Ik introduceerde een praktische case, namelijk, grafisch de dagen in juni en juli tonen waar:
- studenten examenperiodes kunnen hebben
- een groot voetbaltornooi georganiseerd wordt
- er een overlap is tussen 2 bovengenoemde periodes
Dit was het resultaat:
-
In het tweede deel van het artikel, zal ik hoofdzakelijk 3 dingen doen:
- de formule om de overlappende kalenderperiodes te berekenen, uitleggen
- mijn regels voor de Voorwaardelijke opmaak tonen en uitleggen
- een interessante truck demonstreren met Gegevensvalidatie en Voorwaardelijke opmaak
Overlap berekenen
De formule in cel P4 is als volgt:
=ALS(MIN(N($I4),N($J4))=0,ALS(EN(date_>,=$C4,date_<,=$D4),1,0),
ALS(MIN(N($C4),N($D4),N($I4),N($J4))=0,0,
ALS(OF(date_<,$M4,date_>,$N4),0,
ALS(EN(date_>,=$C4,date_<,=$D4,date_>,=$I4,date_<,=$J4),2,
ALS(EN(date_>,=$C4,date_<,=$D4,OF(date_<,$I4,date_>,$J4)),1,3)) ) ) )
Dit zal niet echt de gemakkelijkste Excel formule zijn die je vandaag zal zien… Eerst en vooral moet ik je iets vertellen over de component edate_ in de formule. Dit is in feite een formule gedefinieerd via Formules > Name Manager:
=DATUM('Soccer and exams'!$A4,'Soccer and exams'!P$2,'Soccer and exams'!P$3)
'Voetbal en examens' is de naam van het werkblad, zoals je kan zien in het bestand dat je kan downloaden bovenaan deze pagina. Als je de cursor zet op cel P4, kan je die formule gebruiken om een "dynamische" datum op te zetten: het is altijd de datum met jaar in kolom A (zelfde rij), de maand altijd in rij 3 (zelfde kolom) en de dag altijd in rij 3 (zelfde kolom). Het grote voordeel is dat je nu date_ in al je cellen kan zetten, zonder steeds de volledig DATUM functie te typen met zijn 3 invoerargumenten, maar ook dat de formule korter en gemakkelijker te volgen is. Je kan date_ in elke cel van jouw spreadsheet zetten, het zal telkens leiden tot een datum met jaar, maand en dag combinatie via de gespecifieerde absolute en relatieve celreferenties.
Laten we terugkeren naar de formule in cel P4. In feite bevatten alle cellen in juni en juli (kolommen P tot en met BX), voor alle jaren, dezelfde formule want ik gebruik relatieve celverwijzingen. De formule geeft telkens 1 van 4 mogelijke uitkomsten:
- Uitkomst 0: opgemaakt zonder achtergrondkleur - geen examens en geen voetbaltornooi
- Uitkomst 1: opgemaakt in rood - examens en geen voetbaltornooi
- Uitkomst 2: opgemaakt in oranje - zowel examens als een voetbaltornooi
- Uitkomst 3: opgemaakt in groen - geen examens maar wel een voetbaltornooi
De Voorwaardelijke opmaak om de cellen in te kleuren zal gebruik maken van het resultaat van de formule, om te weten hoe het de cel moet opmaken. We zullen dadelijk de Voorwaardelijke opmaak bespreken. Eerst wil ik jullie een leuke truck tonen dat jou kan helpen bij het opzetten van het bestand en de formules. Tijdens het opzetten zal je wellicht snel willen kunnen switchen tussen de getallen 0, 1, 2, 3 in de cellen, en deze getallen weer verbergen. Middels Voorwaardelijke opmaak laat ik jou dat doen. Cel P27 bevat een keuzelijstje waar je de status kan kiezen; de getallen tonen of ze verbergen. De Voorwaardelijke opmaak zal naar die cel kijken en de cellen voor juni en juli zodanig opmaken dat de getallen niet zichtbaar zijn als je kiest voor het verbergen van de getallen. Als je kiest voor het tonen van de getallen zal de Voorwaardelijke opmaak niets doen en zal de "Standaard" getalopmaak actief blijven:
De formule in cel P4 kijkt naar de begindatum en einddatum va beide kalenderperiodes. De formule evalueert de datums in dezelfde rij. Om de formule te versimpelen, gebruik ik 2 hulpkolommen M and N, waar ik de eerste datum van beide kalenderperiodes zet en de laatste datum van beide kalenderperiodes. De "huidige" datum in de cel (1 juni tot en met 30 july voor elk jaar) zal geëvalueerd worden met betrekking tot de kalenderperiodes en hun grenzen.
De N functie converteert de celinhoud tot een getal. Als de cel al een getal bevat, is het oké, het resultaat is opnieuw dat getal. Als de cel tekst bevat, zal de tekst omgezet worden naar het getal 0. Sinds kolommen I en J tekst kunnen bevatten (een vraagteken namelijk) in plaats van datums (wat voor Excel eigenlijk getallen zijn), kan de N functie aangewend worden om telkens de vergelijking te maken op een numerieke manier en het vraagteken gewoon te negeren. Als we de formule lezen, zeggen we:
- De eerste lijn in de formule geeft 1 als de datums van het tornooi onkend zijn maar de datum valt wel in een examenperiode
- De tweede lijn in de formule geeft 0 als datums niet ingevuld zijn
- De derde lijn in de formule geeft 0 als de datum buiten de een tornooi valt
- De vierde lijn in de formule geeft 2 als de datum zowel tijdens een examenperiode valt als tijdens een voetbaltornooi
- De vijfde lijn in de formule geeft 1 als de datum tijdens een examenperiode valt maar niet tijdens een tornooi; indien ook dit niet waar is, is de uitkomst 3
Voorwaardelijke opmaak
Nu is het hoog tijd om naar de regels van de Voorwaardelijke opmaak te kijken. Alle rules voor de Voorwaardelijke opmaak zijn van toepassing op alle 61 dagen voor alle jaren (30 dagen voor juni, 31 dagen voor juli). Vermits ik de grootte van het scherm van de Manager van de Voorwaardelijke opmaak niet kan aanpassen, volgen hier 2 schermafdrukken:
- Regel 1 instrueert om een horizontale rand te hebben tussen de rijen (jaren).
- Het doel van regel 2 is om te switchen tussen het tonen van de getallen en het verbergen van de getallen. Cfr. infra voor nadere uitleg.
- Regels 3 en 4 zullen de rand tussen voor de weken: links van een maandag en rechts van een zondag. De WEEKDAG functie wordt gebruikt om uit te maken welke dag in de week onze date_ is. Dus ja, je kan de formule voor date_ ook in Voorwaardelijke opmaak toepassen!
- Regel 5 stelt zich tot doel om de cellen op te maken wanner het resultaat 0 is in de cel (d.i. geen examens en geen voetbaltornooi). Voor wie pedant wil zijn, deze regel van de Voorwaardelijke opmaak zou weggelaten kunnen worden. In ieder geval, in tegenstelling tot de voorgaande regels van de Voorwaardelijke opmaak, is voor deze regel de kolom "Stoppen Indien Waar" aangevinkt: als de waarde in de cel 0 is, zullen de regels die volgen niet meer geëvalueerd worden voor die cel.
- Regels 6, 7 en 8 zijn gelijkaardig, enkel de celkleur verandert, afhankelijk van de numerieke waarde in de cel(1, 2, 3). Elke keer is "Stoppen Indien Waar aangevinkt, aangezien de waarden 0, 1, 2, 3 mekaar uitsluiten.
Klaar!
Zo, klaar, Voorwaardelijke opmaak voor het afbakenen van (overlappende) kalenderperiodes, inclusief het aanduiden van weken. Ik hoop dat alweer iets nieuws hebt geleerd met deze 2 artikels!
