Подсчет рабочих дней при нестандарной рабочей неделе в MS EXCEL

Подсчитаем количество рабочих дней между двумя датами в случае нестандартной рабочей недели: в случае четырехдневной недели или когда выходные дни - воскресенье и среда. При подсчете учтем праздники.

Подсчет рабочих дней в случае стандартной рабочей недели (ПН-ПТ рабочие, СБ-ВС выходные) можно осуществить с помощью функции ЧИСТРАБДНИ(). Если необходимо подсчитать рабочие дни в случае нестандартной рабочей недели, то необходимо использовать более сложные формулы (см. файл примера).

1. Рабочие дни ПН-ЧТ (4 рабочих дня), праздники не учитываются

=СУММПРОИЗВ(--ЕОШИБКА(ПОИСКПОЗ(ДЕНЬНЕД(СТРОКА(ДВССЫЛ(A6&":"&B6));2);{5;6;7};0)))

Разберем подробнее:

  • СТРОКА(ДВССЫЛ(A6&":"&B6)) возвращает массив последовательных чисел {40544:40545:40546:...40573:40574} Число 40544 представляет собой начальную дату в числовом формате (01.01.2011) из ячейки A6, а 40574 - конечную дату из ячейки B6. Этот массив можно получить, выделив в Строке формул СТРОКА(ДВССЫЛ(A6&":"&B6)) и нажав клавишу F9.
  • функция ДЕНЬНЕД() возвращает массив {6:7:1:2:3:4:5:6:7:1:2:3:4:5:6:7:1:2:3:4:5:6:7:1:2:3:4:5:6:7:1}, где 1 соответствует понедельнику (01.01.2011), 2 - вторнику и т.д.
  • функция ПОИСКПОЗ() поочередно ищет 6, 7, 1, 2, ... (т.е. числа из предыдущего массива) среди чисел константы массива {5;6;7} и возвращает номер найденной позиции или ошибку {2:3:#Н/Д:#Н/Д:...} Константа массива {5;6;7} представляет собой перечень дней недели, считаемых выходными, т.е. в данном случае Пт, Сб и Вс. Ошибка #Н/Д соответствует рабочим дням.
  • функция ЕОШИБКА() возвращает массив {ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА:...}, где ИСТИНА соответствует ошибке.
  • Двойное отрицание (--) преобразует массив {ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА:...} в числовую форму {0:0:1:1: ...}
  • функция СУММПРОИЗВ() производит сложение без необходимости использования формулы массива.

Если требуется подсчитать количество рабочих дней на неделе, где выходные Среда, Суббота и Воскресенье, то константу массива {5;6;7} нужно изменить на {3;6;7}

2. Рабочие дни ПН-ЧТ (4 рабочих дня), праздники учитываются

В случае учета праздничных дней, формула усложняется и вводить ее нужно как формулу массива, т.е. вместо ENTER нажимать CTRL+SHIFT+ENTER

=СУММ(ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(ДЕНЬНЕД(СТРОКА(ДВССЫЛ(A6&":"&B6));2);{5;6;7};0));
--ЕОШИБКА(ПОИСКПОЗ(СТРОКА(ДВССЫЛ(A6&":"&B6));Праздники;0))))

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (2 оценок)
Яндекс.Метрика