Рассчитать количество рабочих дней

Это глава из книги Билла Джелена Гуру Excel расширяют горизонты: делайте невозможное с Microsoft Excel.

Задача: подсчитать, сколько рабочих дней попадает между двумя датами. Функция Excel ЧИСТРАБДНИ прекрасно справляется с задачей, если у вас 5-дневная рабочая неделя с субботой и воскресеньем в качестве выходных. Эта заметка покажет вам, как выполнить расчет рабочей недели любой продолжительности и любым набором выходных.

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

ЧИСТРАБДНИ вычисляет количество рабочих дней между двумя датами, включая начальную и конечную даты. Следует указать более раннюю дату в качестве первого аргумента, более позднюю дату в качестве второго аргумента, и дополнительно определить массив праздничных дней в качестве третьего аргумента (рис. 1). Формула в ячейки C3: =ЧИСТРАБДНИ(A3;B3;$E$2:$E$11). 17.02.09 вычесть 10.02.09 дает 8 календарных дней, минус два выходных и один праздничный день. Итого, 5 рабочих дней. Однако, если вам «посчастливилось» работать с понедельника по субботу, стандартная формула не справится с задачей.

Рис. 1. ЧИСТРАБДНИ предполагает, что рабочая неделя длится с понедельника по пятницу

Скачать заметку в формате Word или pdf, примеры в формате Excel

Для начала определите диапазон, и назовите его Праздники. Для этого выделите диапазон вместе с заголовком и пройдите по меню ФОРМУЛЫ –> Создать из выделенного (рис. 2). Убедитесь, что выбрана опция в строке выше. Кликните Ok в окне Создание имени из выделенного диапазона.

Рис. 2. Присвоение имени диапазону

Определив диапазон, снова введите формулу в С3. Теперь она примет вид: =ЧИСТРАБДНИ(A3;B3;Праздники)

Теперь перейдем к расчету числа рабочих дней при 6-дневной рабочей недели (рис. 3). Формула в С3: =СУММПРОИЗВ(--(СЧЁТЕСЛИ(Праздники;СТРОКА(ДВССЫЛ(A3&":"&B3)))=0);--(ДЕНЬНЕД(СТРОКА(ДВССЫЛ(A3&":"&B3));2)<7)). Хотя эта формула имеет дело с массивами, она в конечном счете возвращает одно значение, так что вам не нужно использовать Ctrl+Shift+Enter для ввода этой формулы.

Рис. 3. Число рабочих дней при 6-дневной рабочей неделе

Рассмотрим работу формулы подробнее. В отношении всех дат, входящих в диапазон от даты старта до даты окончания (включительно), формула проверяет две вещи. Во-первых, не является ли какая-либо дата праздником. Во-вторых, какие даты в диапазоне дат воскресенья.

Вам нужен быстрый способ, чтобы сравнить каждую дату, начиная с А3, и заканчивая В3 со списком праздников. В данном примере (в ячейке С3), у вас только 8 дней, но в строке 5, у вас более 300 дней. Формула использует тот факт, что в Excel даты хранятся в виде порядковых номеров. Хотя в ячейке A3 отображается 20 января 2009, Excel на самом деле хранит дату как числовое значение 39854.

Первая функция ДВССЫЛ формирует массив всех дат, входящих в диапазон. Аргументы внутри ДВССЫЛ сцепляют дату (хранящуюся как число) из ячейки А3 с датой из B3, разделяя их двоеточием. Промежуточный результат вычисления можно увидеть в окне Вычисление формулы (рис. 4). Фрагмент ДВССЫЛ(A3&":"&B3) превратился в $39854:$39861.

Рис. 4. Работа фрагмента формулы ДВССЫЛ(A3&":"&B3)

Обычно аргумент ДВССЫЛ это диапазон типа "А2:IU2". Однако, если вы когда-либо использовали ссылку на диапазон от первого столбца (A) до последнего столбца, вы видели, что =СУММ(2:2) эквивалентна =СУММ(А2:IV2) в Excel 2003 и =СУММ(А2:XFD2) в Excel 2007. Таким образом, можно ссылаться на целые строки. Фрагмент СТРОКА($39854:$39861) возвращает массив из восьми чисел {39854; 39855; …; 39861} (рис. 5).

Рис. 5. Фрагмент формулы СТРОКА

Теперь вы можете сравнить этот массив из восьми чисел с диапазоном праздничных дат. Фрагмент СЧЁТЕСЛИ(Праздники;СТРОКА(ДВССЫЛ(A3&":"&B3))) превратился в СЧЁТЕСЛИ({39814:39832:39860: 39958:39997:40063:40098:40128:40143:40172};{39854:39855:39856:39857:39858:39859:39860: 39861}). Формула подсчитывает, сколько раз каждый праздник (первый аргумент функции СЧЁТЕСЛИ) совпадет с одним из значений из диапазона дат (второй аргумент; см. рис. 6).

Рис. 6. Аргументы функции СЧЁТЕСЛИ

Функция СЧЁТЕСЛИ возвращает 1, если праздник находится в диапазоне дат и 0, если – нет (рис. 7). Так как вас интересуют не праздничные даты, а рабочие, вы сравниваете результат функции СЧЁТЕСЛИ с нулем. {0;0;0;0;0;0;1;0;0}=0 возвращает {ИСТИНА;ИСТИНА;ИСТИНА;ИСТИНА;ИСТИНА; ИСТИНА;ЛОЖЬ;ИСТИНА;ИСТИНА;}. Операция минус минус преобразует массив логических значений в числовые: {1;1;1;1;1;1;0;1;1}.

Рис. 7. Фрагмент формулы СЧЁТЕСЛИ=0

Рис. 8 иллюстрирует только что описанные этапы расчета. Столбец Е – это значения, возвращаемые фрагментом СТРОКА(ДВССЫЛ(A3&":"&B3)). Столбец F – СЧЁТЕСЛИ(Праздники;{39854;39855; …;39861}) – возвращает 0, если дата из столбца Е не совпадает ни с одним из праздников, и 1 – если совпадает. Например, дате в Е3, не соответствует ни один из праздников, СЧЁТЕСЛИ в F3 возвращает 0. В тоже время, в ячейке F8, находится дата – 39860 – обнаруженная в списке праздничных дат, так что СЧЁТЕСЛИ в F8 возвращает 1.

Рис. 8. Дополнительные столбцы, иллюстрирующие работу формулы

В столбце G, проверяется, равен ли результат СЧЁТЕСЛИ единице. Если да, возвращается ИСТИНА, иначе – ЛОЖЬ. В столбце H введена формула —G, которая преобразует каждое значение ИСТИНА в 1, а ЛОЖЬ в 0. На рис. 3, ячейки Н2:Н9 представляют собой виртуальные результаты первой половины формулы, которая находит даты – не праздники.

Вторая половина формулы использует функцию ДЕНЬНЕД, чтобы найти даты, не являющиеся воскресеньями. Функция ДЕНЬНЕД возвращает порядковый номер дня недели и имеет два аргумента, второй из которых определяет, какой день недели считать первым (рис. 9).

Рис. 9. Значения второго аргумента функции ДЕНЬНЕД

На рис. 8 в столбце К представлен виртуальный результат, который возвращает функция ДЕНЬНЕД с вторым аргументом рамным 2 (понедельник = 1). В столбце L значение ДЕНЬНЕД сравнивается с числом 7. Будний день возвращает значение ИСТИНА, воскресенье – ЛОЖЬ. В столбце М логические значения превращаются в числовые. И, наконец, в столбце N перемножаются значения из столбцов Н и М. В столбце N значение 1 соответствует рабочему дню, а значение 0 означает, что день праздничный и/или воскресный. Действие в столбце N соответствует перемножению массивов, являющихся первым и вторым аргументами функции СУММПРОИЗВ (рис. 10). Когда соответствующие элементы обоих массивов содержат 1, у нас рабочий день. Когда, либо значение первого массива, либо второго (либо обоих) равно 0, результат 0. Конечный результат есть сумма нулей и единиц. В нашем случае значение в ячейке С3 равно 6.

Рис. 10. Аргументы функции СУММПРОИЗВ

Если у вас 7-дневная рабочая неделя, вам остается только исключить праздничные дни. Формула упрощается: =СУММПРОИЗВ(--(СЧЁТЕСЛИ(Праздники;СТРОКА(ДВССЫЛ(A3&":"&B3)))=0)).

Проблема становится сложнее, если выходные дни расположены произвольным образом. Например, сотрудник работает по совместительству в понедельник, среду и пятницу. Теперь та часть формулы, что проверяла воскресенья, должна проверить 3 определенных дня недели. Обратите внимание, что Тип_возврата 2 функции ДЕНЬНЕД никогда не возвращает 0. Вы можете использовать его в качестве первого аргумента в функцию ВЫБОР, чтобы указать, какие дни являются рабочими. С помощью фрагмента формулы =ВЫБОР(ДЕНЬНЕД(некоторые даты,2),1,0,1,0,1,0,0) мы получим 1 для понедельника, среды и пятницы, и 0 – для остальных дней недели (рис. 11).

Рис. 11. Функция ВЫБОР для произвольного числа рабочих дней

Поскольку функция ВЫБОР обычно не возвращает массив, вы должны ввести следующую формулу, используя сочетание клавиш Ctrl+Shift+ввод: {=СУММПРОИЗВ(--(СЧЁТЕСЛИ(Праздники;СТРОКА (ДВССЫЛ(A3&":"&B3)))=0);--(ВЫБОР(ДЕНЬНЕД(СТРОКА(ДВССЫЛ(A3&":"&B3));2);1;0;1;0;1;0;0)))}

Резюме: вы познакомились с концепцией создания огромного массива всего из двух значений. Например, =СТРОКА(ДВССЫЛ(«1:10000»)) создает массив 10 000 значений от 1 до 10 000. Вы можете использовать эту концепцию, чтобы проверить большой массив дат, задав лишь начальную и конечную точку.

Источник

* * *

Начиная с версии 2010 в Excel появилась более гибкая функция ЧИСТРАБДНИ.МЕЖД, которая дает возможность расчета для любой продолжительности рабочей недели и любого (но постоянного) набора выходных дней (рис. 12). В ячейке С2 формула имеет вид: =ЧИСТРАБДНИ.МЕЖД(A2;B2;"0101011";Праздники).

Рис. 12. Функция ЧИСТРАБДНИ.МЕЖД

Синтаксис функции: ЧИСТРАБДНИ.МЕЖД(нач_дата;кон_дата;[выходной];[праздники])

Аргумент Выходной является необязательным. Указывает, какие дни недели являются выходными и не включаются в число рабочих дней между начальной и конечной датой. Значение может задаваться номером выходного дня (всего 17 значений, подробнее см. справку Excel) или строкой, определяющей, какие дни являются выходными.

Строковые значения дней недели включают семь знаков, каждый из которых обозначает день недели (начиная с понедельника). Значение 1 представляет нерабочие дни, а 0 — рабочие дни. В строке допустимо использовать только знаки 1 и 0. При значении 1111111 всегда возвращается 0. Строку следует взять в кавычки. В нашем случае срока "0101011" означает, что рабочими являются понедельник, среда и пятница.