Jedno z typowych zadań dla użytkownika programu Microsoft Excel. Mamy dwa zakresy dat typu „początek-koniec”. Wyzwanie polega na ustaleniu, czy te zakresy się pokrywają, a jeśli tak, o ile dni.
Przecinają się czy nie?
Zacznijmy od rozwiązania pytania, czy w zasadzie istnieje przecięcie przedziałów? Załóżmy, że mamy tabelę zmian roboczych dla pracowników takich jak ta:
Widać wyraźnie, że zmiany pracy Jarosława i Eleny przecinają się, ale jak to obliczyć bez uciekania się do budowania harmonogramu kalendarza i kontroli wizualnej? Funkcja nam pomoże SUMPRODUCT (SUMA ILOCZYN).
Wstawmy do naszej tabeli kolejną kolumnę z formułą, która daje wartość logiczną TRUE, jeśli daty się przecinają:
Ile dni trwa przeprawa?
Jeśli zasadniczo nie jest łatwo zrozumieć, czy nasze przedziały przecinają się, czy nie, ale dokładnie wiedzieć, ile dni dokładnie przypada na przecięcie, zadanie staje się bardziej skomplikowane. Logicznie rzecz biorąc, w jednej formule trzeba „pompować” aż 3 różne sytuacje:
- interwały nie nakładają się
- jeden z interwałów całkowicie pochłania drugi
- odstępy częściowo się przecinają
Od czasu do czasu widzę implementację tego podejścia przez innych użytkowników korzystających z wielu zagnieżdżonych funkcji IF itp.
Tak naprawdę wszystko można zrobić pięknie za pomocą tej funkcji MEDIAN (MEDIANA) z kategorii Statystyczny.
Jeśli warunkowo wyznaczymy początek pierwszego przedziału jako N1i koniec dla K1i początek drugiego N2 i koniec na K2, to w ogólnym zarysie nasz wzór można zapisać jako:
=MEDIANA(N1;K1+ 1;K2+1)-MEDIANA(N1;K1+ 1;N2)
Kompaktowy i elegancki, prawda?
- Jak Excel faktycznie działa z datami? Jak obliczyć liczbę dni kalendarzowych lub roboczych między datami?
- Jak zbudować harmonogram kalendarza (święta, szkolenia, zmiany…) w Excelu za pomocą formatowania warunkowego?
- Sprawdzanie jednego lub więcej warunków za pomocą funkcji JEŻELI (JEŻELI)