Одне з типових завдань для користувача Microsoft Excel. Маємо два діапазони дат типу «початок-кінець». Завдання полягає в тому, щоб визначити, чи збігаються ці діапазони, і якщо так, то на скільки днів.
Перетинаються чи ні?
Почнемо з вирішення питання про те, чи існує в принципі перетин інтервалів? Припустимо, у нас є таблиця робочих змін для співробітників так:
Добре видно, що робочі зміни Ярослава та Олени перетинаються, але як це розрахувати, не вдаючись до побудови календарного графіка та візуального контролю? Нам допоможе функція SUMPRODUCT (SUMPRODUCT).
Давайте вставимо ще один стовпець у нашу таблицю з формулою, яка дає логічне значення TRUE, якщо дати перетинаються:
Скільки днів триває переправа?
Якщо зрозуміти, перетинаються наші інтервали чи ні, принципово не просто, а точно знати, скільки саме днів потрапляє в перетин, то завдання ускладнюється. Логічно, що в одну формулу необхідно «прокачати» цілих 3 різні ситуації:
- інтервали не накладаються
- один з інтервалів повністю поглинає інший
- інтервали частково перетинаються
Час від часу я бачу реалізацію цього підходу іншими користувачами за допомогою купи вкладених функцій IF тощо.
Насправді за допомогою функції все можна зробити красиво MEDIAN (МЕДІАН) з категорії Статистичний.
Якщо умовно позначити початок першого інтервалу як N1, і закінчення для K1, і початок другої N2 і закінчення для K2, то в загальному вигляді нашу формулу можна записати так:
=МЕДІАН(N1;K1+ 1;K2+1)-МЕДІАН(N1;K1+ 1;N2)
Компактно та елегантно, чи не так? 😉
- Як Excel насправді працює з датами? Як розрахувати кількість календарних або робочих днів між датами?
- Як побудувати календарний розклад (канікули, навчання, зміни…) в Excel за допомогою умовного форматування?
- Перевірка однієї або кількох умов за допомогою функцій IF (ЯКЩО).