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