Общая формула
=SUMPRODUCT(MID(schedule,WEEKDAY(ROW(INDIRECT(start&":"&end))),1)*ISNA(MATCH(ROW(INDIRECT(start&":"&end)),holidays,0)))
Резюме
Чтобы рассчитать количество рабочих часов между двумя датами с помощью настраиваемого расписания, вы можете использовать формулу, основанную на функциях WEEKDAY и SUMPRODUCT, с помощью ROW, INDIRECT и MID. В показанном примере формула в F8:
=SUMPRODUCT(MID(D6,WEEKDAY(ROW(INDIRECT(B6&":"&C6))),1)*ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0)))
Что возвращает 36 часов на основе настраиваемого графика, где 8 часов работают с понедельника по пятницу, 4 часа работают в субботу, а понедельник 3 сентября - выходной. Праздники представлены в названном диапазоне G6: G8. График работы вводится в виде текстовой строки в столбце D и может быть изменен по желанию.
Примечание. Это формула массива, которую необходимо вводить с помощью Control + Shift + Enter. Если у вас стандартный 8-часовой рабочий день, эта формула проще.
Объяснение
По сути, эта формула использует функцию WEEKDAY для определения дня недели (например, понедельник, вторник и т. Д.) Для каждого дня между двумя заданными датами. WEEKDAY возвращает число от 1 до 7. По умолчанию воскресенье = 1, а суббота = 7.
Уловка этой формулы состоит в том, чтобы собрать массив дат, который вы можете передать в функцию WEEKDAY. Это делается с помощью ROW с INDIRECT:
ROW(INDIRECT(B6&":"&C6))
СТРОКА интерпретирует объединенные даты как номера строк и возвращает такой массив:
(43346;43347;43348;43349;43350;43351;43352)
Каждое число в массиве представляет дату. Затем функция WEEKDAY оценивает массив и возвращает массив значений дня недели:
(2;3;4;5;6;7;1)
Эти числа соответствуют дню недели каждой даты. Они предоставляются функции MID в качестве аргумента начального номера вместе со значением в D6, "0888884" для текста:
MID("0888884",(2;3;4;5;6;7;1),1)
Поскольку мы передаем MID массив начальных чисел, он возвращает массив таких результатов:
("8";"8";"8";"8";"8";"4";"0")
Эти значения соответствуют количеству отработанных часов каждый день с даты начала до даты окончания. Обратите внимание, что значения в этом массиве - это текст, а не числа. Чтобы преобразовать в фактические числа, мы умножаем их на второй массив, созданный для управления праздниками, как описано ниже. Математическая операция переводит текст в числовые значения.
каникулы
Для обработки праздников мы используем ISNA, MATCH и именованный диапазон «праздники» следующим образом:
ISNA(MATCH(ROW(INDIRECT(B6&":"&C6)),holidays,0))
Это выражение использует MATCH для поиска дат, которые находятся в праздничных днях именованного диапазона, используя тот же массив дат, созданный выше с помощью INDIRECT и ROW. ПОИСКПОЗ возвращает число, если праздники найдены, и ошибку # Н / Д, если нет. Функция ISNA "переворачивает" результаты так, что ИСТИНА представляет праздники, а ЛОЖЬ - не праздничные дни. ISNA возвращает массив или такие результаты:
(FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Наконец, оба массива умножаются друг на друга внутри SUMPRODUCT. Математическая операция приводит ИСТИНА и ЛОЖЬ к 1 и нулю, а текстовые значения в первом массиве - к числовым значениям (как объяснено выше), поэтому в итоге мы имеем:
=SUMPRODUCT((8;8;8;8;8;4;0)*(0;1;1;1;1;1;1))
После умножения у нас есть единственный массив внутри SUMPRODUCT, содержащий все рабочие часы в диапазоне дат:
=SUMPRODUCT((0;8;8;8;8;4;0))
Затем СУММПРОИЗВ суммирует все элементы в массиве и возвращает результат 36.
