Een van de typische taken voor een Microsoft Excel-gebruiker. We hebben twee datumbereiken van het type "begin-eind". De uitdaging is om te bepalen of deze reeksen elkaar overlappen en zo ja, met hoeveel dagen.
Kruisen of niet?
Laten we beginnen met het oplossen van de vraag of er in principe een intervallen van intervallen is? Stel dat we een tabel met ploegendiensten voor werknemers hebben, zoals deze:
Het is duidelijk te zien dat de werkploegen van Yaroslav en Elena elkaar kruisen, maar hoe dit te berekenen zonder toevlucht te nemen tot het maken van een kalenderschema en visuele controle? De functie zal ons helpen SUMPRODUCT (SOMPRODUCT).
Laten we een andere kolom in onze tabel invoegen met een formule die de booleaanse waarde TRUE oplevert als de datums elkaar kruisen:
Hoeveel dagen duurt de overtocht?
Als het fundamenteel niet eenvoudig is om te begrijpen of onze intervallen elkaar kruisen of niet, maar om precies te weten hoeveel dagen er precies in de kruising vallen, dan wordt de taak ingewikkelder. Logischerwijs is het nodig om maar liefst 3 verschillende situaties in één formule te “pompen”:
- intervallen overlappen elkaar niet
- een van de intervallen absorbeert de andere volledig
- intervallen kruisen elkaar gedeeltelijk
Van tijd tot tijd zie ik de implementatie van deze aanpak door andere gebruikers met behulp van een aantal geneste IF-functies, enz.
In feite kan alles prachtig worden gedaan met behulp van de functie MEDIAAN (MEDIAAN) uit categorie Statistisch.
Als we het begin van het eerste interval voorwaardelijk aanwijzen als N1, en het einde voor K1, en het begin van de tweede N2 en eindigen voor K2, dan kan onze formule in algemene termen worden geschreven als:
=MEDIAAN(N1;K1+ 1;K2+1)-MEDIAAN(N1;K1+ 1;N2)
Compact en elegant, nietwaar?
- Hoe werkt Excel eigenlijk met datums? Hoe het aantal kalender- of werkdagen tussen datums berekenen?
- Hoe bouw je een kalenderschema (vakantiedagen, trainingen, diensten...) in Excel met behulp van voorwaardelijke opmaak?
- Een of meer voorwaarden controleren met IF (IF)-functies