En av de typiske oppgavene for en Microsoft Excel-bruker. Vi har to datointervaller av typen «begynnelse-slutt». Utfordringen er å finne ut om disse områdene overlapper, og i så fall med hvor mange dager.
Kryss eller ikke?
La oss starte med å løse spørsmålet om det er et skjæringspunkt av intervaller i prinsippet? Anta at vi har en tabell over arbeidsskift for ansatte som dette:
Det er tydelig at arbeidsskiftene til Yaroslav og Elena krysser hverandre, men hvordan beregner man dette uten å ty til å bygge en kalenderplan og visuell kontroll? Funksjonen vil hjelpe oss SUMPRODUCT (SUMPRODUKT).
La oss sette inn en annen kolonne i tabellen vår med en formel som gir den boolske verdien TRUE hvis datoene krysser hverandre:
Hvor mange dager er overfarten?
Hvis det grunnleggende ikke er lett å forstå om intervallene våre krysser hverandre eller ikke, men å vite nøyaktig hvor mange dager som faller inn i krysset, så blir oppgaven mer komplisert. Logisk sett er det nødvendig å "pumpe" så mange som 3 forskjellige situasjoner i én formel:
- intervaller overlapper ikke
- ett av intervallene absorberer det andre fullstendig
- intervaller delvis krysser hverandre
Fra tid til annen ser jeg implementeringen av denne tilnærmingen av andre brukere som bruker en haug med nestede IF-funksjoner, etc.
Faktisk kan alt gjøres vakkert ved å bruke funksjonen MEDIAN (MEDIAN) fra kategori Statistiske.
Hvis vi betinget utpeker begynnelsen av det første intervallet som N1, og slutten for K1, og begynnelsen av den andre N2 og slutt for K2, så i generelle termer kan formelen vår skrives som:
=MEDIAN(N1;K1+ 1;K2+1)-MEDIAN(N1;K1+ 1;N2)
Kompakt og elegant, ikke sant? 😉
- Hvordan fungerer Excel egentlig med datoer? Hvordan beregne antall kalendere eller virkedager mellom datoer?
- Hvordan bygge en kalenderplan (ferier, treninger, skift ...) i Excel ved å bruke betinget formatering?
- Kontrollerer en eller flere tilstander med IF (IF) funksjoner