График рабочих дней без выходных и праздников в Excel

График рабочих дней без выходных и праздников в Excel

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

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

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

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

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

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

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

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

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

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

Рис. 7.12. Панель функции ИЛИ

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

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

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

  • Формулу из ячейки С25 вложим в формулу из ячейки С26:
  • — Полученную формулу вложим в формулу из ячейки С27:

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

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

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

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

Рис. 7.13. Список с перемещенными датами

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

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

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

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

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

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

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

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

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

График рабочих дней без выходных и праздников в Excel

Во всех версиях Excel есть функции, позволяющие делать вычисления с рабочими днями, однако рабочие дни были определены с понедельника по пятницу, а выходные — суббота и воскресенье. В большинстве случаев, это соответствует рабочему графику многих трудящихся, однако не во всех сферах именно такая трудовая неделя.

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

РАБДЕНЬ.МЕЖД(Нач_дата;Число_дней;Выходные;Праздники) – определение даты, отстоящей на заданное число рабочих дней вперед или назад от начальной даты.
WORKDAY.INTL(Start_date;Days;Weekend;Holidays)

  • Нач_дата [Start_date] – дата, относительно которой ведется расчет.
  • Число_дней [Days] – количество не выходных и не праздничных дней до или после начальной даты.
  • Выходные [Weekend] – необязательный аргумент. Если значение не заполняется, то считается, что выходные дни – суббота и воскресенье. В случае, если не совпадают с общепринятыми, то задается, чтобы понять какие дни недели являются выходными, а какие рабочими. Значение 1 – нерабочие дни, а 0 – рабочие дни. Например, 1010100 означает, что выходными днями являются понедельник, среда и пятница.
  • Праздники [Holidays] – необязательный аргумент. Одно или несколько значений дат, которые в рабочие дни являются выходными (государственные праздники).

ПРИМЕР: Определить дату исполнения, при условии, что на подготовку документов требуется 10 рабочих дней после сдачи документов. Учесть, что с 1-го по 8-е января праздники, а выходные дни на неделе понедельник и воскресенье.

dateintl1.png

«1000001» — обозначение выходных и рабочих дней на неделе, а $F$10:$F$15 — праздники.

ЧИСТРАБДНИ.МЕЖД(Нач_дата;Кон_дата;Выходные;Праздники) – определение полных рабочих дней между двумя указанными датами.
NETWORKDAYS.INTL(Start_date;End_date;Weekend;Holidays)

  • Нач_дата [Start_date] – начальная дата периода.
  • Кон_дата [End_date] – конечная дата периода
  • Выходные [Weekend] – необязательный аргумент. Если значение не заполняется, то считается, что выходные дни – суббота и воскресенье. В случае, если не совпадают с общепринятыми, то задается, чтобы понять какие дни недели являются выходными, а какие рабочими. Значение 1 – нерабочие дни, а 0 – рабочие дни. Например, 1100000 означает, что выходными днями являются понедельник и вторник.
  • Праздники [Holidays] – необязательный аргумент. Одно или несколько значений дат, которые в рабочие дни являются выходными, например, государственные праздники.

ПРИМЕР: Вычислить длительность исполнения в рабочих днях, при условии, что выходные дни на неделе понедельник и воскресенье.

dateintl2.png

Оставьте комментарий!

На сообщение “Учет рабочих и праздничных дней в расчетах” комментария 23

  1. Kate :
    11.05.2014 (01:42)

Thanks, now I know the function WORKDAY.INTL and NETWORKDAYS.INTL

Выделил ячейки с праздниками, а программа «ругается»…

Виктор, может вы выделили несмежный диапазон. Можно выделять только смежные ячейки

Как хорошо, что добавили новую функцию, т.к. не у всех рабочая неделя с понедельника по пятницу.

спасибо. это значительно облегчит мои расчеты

Есть ли возможность учесть рабочие дни, которые приходятся в выходные дни как-то просто, без «выкрутасов»?

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

Праздники из года в год надо задавать или можно как-то покороче написать?

Особенно нужна мне была эта функция при работе в 2003-2007 версиях. Рад, что новые функции появляются и облегчают мою работу

Прекрасные функции, мне в финансовых расчетах очень пригодятся!

Пользуюсь старыми… А тут уже и новые есть

Мне кажется это очень хорошая идея поделиться таким функциями с народом.

Что будет в 2007 версии?

Kirill, там не было таких функций, поэтому результата не будет

Всё лучше, чем подсчеты по дням с вычетом выходных — я балда так считал

Очень нужные функции, особенно мне, как HR.

Можно ли в этой функции учесть факт того, что выходной день, например, сб и вс, может быть рабочим?

Жаль, что нельзя выходные в рабочие дни этими функциями описывать

А разве так можно? В 2018м живемс…

А привязки к праздникам страны есть?

Нет, есть только обычная привязка к календарю.

Да уж, в ряде специализированных программах гораздо проще или в базах данных

Как вычислить рабочие дни в Excel

Vichislenie work day 1 Как вычислить рабочие дни в Excel

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

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

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

Количество дней между двумя датами в Excel

Vichislenie work day 2 Как вычислить рабочие дни в Excel

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

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

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

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

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

Vichislenie work day 3 Как вычислить рабочие дни в Excel

  • Начальная дата – является обязательным аргументом и указывается дата начала отсчёта;
  • Конечная дата – также обязательный аргумент и указывает дату окончания периода расчётов;
  • Праздники – не обязательный аргумент, являет собой список дат или ссылку на них, которые следует изъять из расчётов, например, государственные или религиозные праздники.

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

Vichislenie work day 4 Как вычислить рабочие дни в Excel

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

Vichislenie work day 5 Как вычислить рабочие дни в Excel

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

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

График рабочих дней без выходных и праздников в Excel

Очень часто во время создания в Excel отчетов необходимо воспользоваться графиком рабочих дней без выходных и праздничных. Таблица такого типа буде полезна для вычисления товарооборота за 1 рабочий день, денежный оборот по будням и т.д.

Как составить график рабочих дней в Excel?

Один из самых простых способов составить график рабочих дней без выходных и праздников, основан на использовании функции РАБДЕНЬ.МЕЖД.

Построение графика начнем от указания даты последнего рабочего дня прошлого года. Рядом составим список только праздничных (без выходных), которые являются нерабочими днями на вашей фирме для текущего года. Все значения должны иметь формат ячеек «Дата». Пример начала создания графика рабочих дней показан ниже на рисунке:

Шаблон графика.

В ячейке A3 введите первую формулу, которая ссылается на ячейку B1 с датой последнего рабочего дня предыдущего года:

РАБДЕНЬ.МЕЖД.

А в ячейку A4 введите вторую формулу для последующего копирования в остальные ячейки:

Теперь скопируйте эту формулу в остальные ячейки данного столбца ровно столько, сколько требуется получить рабочих дней для графика текущего года:

график рабочих дней.

В результате получился персональный производственный календарь на 2019-й год для конкретной фирмы.

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

=РАБДЕНЬ.МЕЖД( нач_дата;число_дней; выходные;праздники)

  1. Нач_дата : с какой даты следует начать выполнять вычисление.
  2. Число_дней : количество в днях, которое будет вычисляться от начальной даты.
  3. Выходные: по умолчанию если данный опциональный аргумент не заполнен, то функция считает за выходные дни недели субботу и воскресенье. Однако данный аргумент позволяет определить какие именно дни недели считать за выходные. Во время ввода функции вручную в строку формул или в ячейку Excel высверливается интерактивная подсказка с помощью, которой можно выбрать одну из 15-ти доступных вариаций для выходных. Или просто указать в третьем аргументе код вариации, то есть число от 1-го и до 15-ти. Изменение графика рабочего дня в Excel: Изменение графика.
  4. Праздники: опциональный аргумент, в котором следует указать список дат праздников, чтобы они не использовались в вычислениях и были исключены из итогового результата.

Как устроен график рабочих дней в Excel?

В формуле данного примера Excel начинается вычисление от даты 31.12.2018. Во время копирования она увеличивается на 1 день. В такой способ определяется и возвращается следующий рабочий день. В опциональных аргументах указано, что субботы и воскресенья являются выходными днями так же, как и праздничные дни, предварительно записанные в отдельном диапазоне ячеек.

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

Читайте также  Расторжение договоров Как признать сделку кабальной?
Рейтинг
( Пока оценок нет )
Понравилась статья? Поделиться с друзьями:
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:

Adblock
detector