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