Определение выходных дней в табеле

Это глава из книги Билла Джелена .

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

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

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

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

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


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

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


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

Вам нужен быстрый способ, чтобы сравнить каждую дату, начиная с А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).


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


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


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


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

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

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


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

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

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

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

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

В этой статье описаны синтаксис формулы и использование функции РАБДЕНЬ в Microsoft Excel.

Описание

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

Совет: Чтобы вычислить число, которое представляет дату, отстоящую на заданное количество рабочих дней вперед или назад, с использованием параметров, определяющих количество выходных в неделе и то, какие это дни, используйте функцию РАБДЕНЬ.МЕЖД .

Синтаксис

РАБДЕНЬ(нач_дата;количество_дней;[праздники])

Аргументы функции РАБДЕНЬ описаны ниже.

    Нач_дата Обязательный. Начальная дата.

    Количество_дней Обязательный. Количество дней до или после начальной даты, не являющихся выходными или праздниками. Положительное значение аргумента "количество_дней" обозначает дату в будущем, отрицательное - дату в прошлом.

    Праздники Необязательный. Список из одной или нескольких дат, например государственных праздников, которые требуется исключить из рабочего календаря. Список может представлять собой диапазон ячеек, содержащих даты, или константу массива, содержащую числа, которые представляют даты.

Важно: Даты должны быть введены с использованием функции ДАТА или как результат вычисления других формул и функций. Например, для указания даты 23 мая 2008 г. воспользуйтесь выражением ДАТА(2008,5,23). Если ввести даты как текст, это может привести к возникновению проблем.

Замечания

    В приложении Microsoft Excel даты хранятся в виде последовательных чисел, что позволяет использовать их в вычислениях. По умолчанию дате 1 января 1900 г. соответствует число 1, а 1 января 2008 г. - число 39 448, поскольку интервал между ними составляет 39 448 дней.

    Если значение любого из аргументов не является допустимой датой, то функция РАБДЕНЬ возвращает значение ошибки #ЗНАЧ!.

    Если сумма аргументов "нач_дата" и "количество_дней" не является допустимой датой, функция РАБДЕНЬ возвращает значение ошибки #ЧИСЛО!.

    Если значение аргумента "количество_дней" не является целым числом, оно усекается.

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем - клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Для определения выходных дней (суббот и воскресений) воспользуйтесь функцией ДЕНЬНЕД, которая устанавливает номер дня недели. Эту функцию введите в ячейку С28:

ДЕНЬНЕД(С9)

В следующую ячейку - С29 - введите функцию ЕСЛИ:

ЕСЛИ(С28=7;ИСТИНА;ЕСЛИ(С28=1;ИСТИНА;ЛОЖЬ))

Функция ЕСЛИ действует по описанной ниже схеме:

  • День недели, определенный в ячейке С28, - суббота?
  • Если да, возвращается значение ИСТИНА.
  • Если нет, переходим к следующему ЕСЛИ.
  • Если днем недели является воскресенье, возвращается значение ИСТИНА.
  • В противном случае возвращается значение ЛОЖЬ.

Учет выходных и праздничных дней

На следующем этапе нам предстоит создать формулу, которая при соблюдении одного из условий (праздничный день, выходной день или праздничный день, выпадающий на выходной) возвращает значение ИСТИНА. Мы предполагаем разместить эту формулу в ячейке СЗО.

Для решения задачи воспользуемся логической функцией ИЛИ. Эта функция возвращает значение ИСТИНА, если хотя бы один из ее аргументов имеет значение ИСТИНА, и значение ЛОЖЬ, если все аргументы имеют значение ЛОЖЬ.

Функция ИЛИ имеет следующий синтаксис:

ИЛИ(логическое_значение1;логическое_значение2; ...)

Здесь логииеское_значение1, логическое_значение2, ... - это от 1 до 30 проверяемых условий, которые могут иметь либо значение ИСТИНА, либо значение ЛОЖЬ.

Рис. 7.12.

В первом аргументе нам необходимо указать адрес ячейки С29, в которой определяется день недели, а во втором аргументе - адрес ячейки С27, где устанавливаются даты праздников.

Формула в ячейке СЗО должна быть такой:

ИЛИ(С29;С27)

Произведем вложение функций:

  • Формулу из ячейки С25 вложим в формулу из ячейки С26: =ЕНД(ВПР(С9;ДатаПраздн;1;ЛОЖЬ))
  • - Полученную формулу вложим в формулу из ячейки С27: =НЕ(ЕНД(ВПР(С9;ДатаПраздн;1;ЛОЖЬ)))
  • Формулу из ячейки С28 вложим в формулу из ячейки С29: =ЕСЛИ(ДЕНЬНЕД(С9)=7/ИСТИНА;ЕСЛИ(ДЕНЬНЕД(С9)=1;ИСТИНА;ЛОЖЬ))
  • Полученную формулу вложим в формулу из ячейки СЗО: =ИЛИ(ЕСЛИ(ДЕНЬНЕД(С9)=7;:ИСТИНА;ЕСЛИ(ДЕНЬНЕД(С9)=1;ИСТИНА;ЛОЖЬ)) ;С27)

Перенос выходных дней

На практике нередки случаи переноса выходных дней. В нашем табеле реакция на такие переносы не обеспечена. Сейчас мы исправим этот недостаток. Даты дней, которые вследствие переноса стали рабочими, будем вводить в область G22:G24 листа Праздники. Этой области присвоим имя ДатаПеренос (рис. 7.13).

Даты дней, которые вследствие переноса стали выходными, добавим в список праздников, находящийся в диапазоне A15:G16 листа Праздники (рис. 7.5).

Теперь в табеле нам нужна формула, которая будет определять, не перенесен ли данный выходной. Для ее получения доработаем формулу из ячейки С27 табеля.


Рис. 7.13.

Скопируйте ее из строки формул, вставьте в ячейку С31, замените имя Да-таПраздн именем ДатаПеренос и удалите функцию НЕ.

Результирующая формула в ячейке С31 должна иметь следующий вид:

ЕНД(ВПР(С9;ДатаПеренос;1;ЛОЖЬ))

Формула возвращает значение ЛОЖЬ, если находит дату среди перенесенных, или значение ИСТИНА, если не находит. Таким образом, эта ячейка будет содержать значение ЛОЖЬ, если день вследствие переноса стал рабочим. Если же ячейка содержит значение ИСТИНА, значит, статус дня не изменился.

Теперь мы должны проанализировать значения ячеек С3О и С31. Первая содержит логическое значение, которое указывает на то, является ли день выходным либо праздничным. Логическое значение во второй ячейке отражает факт изменения статуса дня (то есть не стал ли он вследствие переноса выходных рабочим днем).

Формула для анализа должна работать так. День является выходным, если его статус не изменился (С31 содержит значение ИСТИНА), и он является праздничным, субботним или воскресным (СЗО содержит значение ИСТИНА). Во всех остальных случаях день будет рабочим.

Составить такую формулу можно с помощью функции И. Она возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА, и значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ. Данная функция принадлежит к категории Логические и имеет следующий синтаксис:

И(логическое_значение1;логическое_значение2; ...)

Здесь логическое_значение1, логическое_значение2, ... - это от I до 30 проверяемых условий, которые могут иметь либо значение ИСТИНА, либо значение ЛОЖЬ.

Введите в ячейку С32 такую функцию:

И(С30;С31)

В данную функцию надлежит вложить формулы из ячеек СЗО и С31. Ниже показано, как выглядит результирующая формула в ячейке С32:

И(ИЛИ(ЕСЛИ(ДЕНЬНЕД(С9)=7; ИСТИНА; ЕСЛИ(ДБНЬНЕД(С9)=1;ИСТИНА;ЛОЖЬ));С27); ЕНД(ВПР(А9;ДатаПеренос;1;ЛОЖЬ)))

Скопируйте формулы из ячеек С27 и С32 в ячейки С10 и С11.

Добрый день уважаемый читатель!

Сегодня я хочу немного облегчить жизнь и работу тех специалистов, в обязанностях которых существует работа с датами, хотя статья будет полезная многим. Существует много задач с датами, но в статье я рассмотрю и покажу, как вычислить рабочие дни в Excel. Эта возможность очень полезна и жизненно необходима по начислению зарплаты, и нормировщиков рабочего времени, специалистов отдела кадров. Я думаю, что о преимуществах автоматического вычисления дат вам очень пригодятся.

Для начала, вы должны знать, что Excel видит любую дату как обыкновенное числовое значение, которое начинается с цифры 1 и соответствует дате 1 января 1900 г. К примеру, 1 января 2018 г. будет равняться числу 43101 и разница между датами составит 43100 дней. А ввиду этого мы очень просто можем манипулировать датами, так же, как и числами, а это значительно делает проще нашу работу.


Это очень простой и понятный пример, в котором необходимо от последней даты отнять начальную дату, а полученное значение преобразовать в числовой формат. Как видите это можно произвести без помощи функций, но для получения рабочих дней нам это не подойдет.

Вычисление рабочих дней в Excel между двумя датами

Итак, поскольку количество календарных дней нам не подходит, а нужны дни работы, пользуемся для решения этой задачи функцией ЧИСТРАБДНИ.

Синтаксис этой функции достаточно прост и понятен:

= ЧИСТРАБДНИ(_нач_дата_; _кон_дата_; _[праздники]_), где:



Простой пример:

У нас есть даты начала периода в А2:А5 и даты конца периода – В2:В5. Используя нашу функцию, мы получаем нужный нам интервал в рабочих днях. Сложный пример:

Усложним нашу задачу добавив несколько праздничных дней в диапазоне Е2:Е5. Тогда наша формула пересчитает рабочие дни и отминусует праздники, попавшие в вычитаемый период.

Диапазон с праздниками советую закрепить , так как чтобы при копировании формулы она не выдавала ошибку и предоставляла правильный результат.

А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями, прочитанным и ставьте лайк!