En av de typiska uppgifterna för en Microsoft Excel-användare. Vi har två datumintervall av typen "början-slut". Utmaningen är att avgöra om dessa intervall överlappar och i så fall med hur många dagar.
Skärning eller inte?
Låt oss börja med att lösa frågan om det finns en skärning av intervaller i princip? Anta att vi har en tabell över arbetsskift för anställda så här:
Det är tydligt att Yaroslavs och Elenas arbetsskift skär varandra, men hur beräknar man detta utan att behöva bygga ett kalenderschema och visuell kontroll? Funktionen kommer att hjälpa oss SUMPRODUCT (SUMMAPRODUKT).
Låt oss infoga en annan kolumn i vår tabell med en formel som ger det booleska värdet TRUE om datumen skär varandra:
Hur många dagar tar överfarten?
Om det i grunden inte är lätt att förstå om våra intervaller skär varandra eller inte, utan att veta exakt hur många dagar som faller in i skärningspunkten, så blir uppgiften mer komplicerad. Logiskt sett är det nödvändigt att "pumpa" så många som 3 olika situationer i en formel:
- intervaller överlappar inte varandra
- ett av intervallen absorberar helt det andra
- intervaller delvis skär varandra
Från tid till annan ser jag implementeringen av detta tillvägagångssätt av andra användare som använder ett gäng kapslade IF-funktioner, etc.
Faktum är att allt kan göras vackert med funktionen MEDIAN (MEDIAN) från kategori Statistisk.
Om vi villkorligt betecknar början av det första intervallet som N1, och slutet för K1, och början av den andra N2 och slut för K2, då i allmänna termer kan vår formel skrivas som:
=MEDIAN(N1;K1+ 1;K2+1)-MEDIAN(N1;K1+ 1;N2)
Kompakt och elegant, eller hur? 😉
- Hur fungerar Excel egentligen med datum? Hur beräknar man antalet kalender- eller arbetsdagar mellan datum?
- Hur bygger man ett kalenderschema (helger, utbildningar, skift...) i Excel med villkorlig formatering?
- Kontrollera ett eller flera tillstånd med IF (IF) funktioner