Рабочие дни списком в excel

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

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

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

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

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

=РАБДЕНЬ.МЕЖД(B1;1;1;$B$3:$B$16)

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

=РАБДЕНЬ.МЕЖД(A3;1;1;$B$3:$B$16)

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

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

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

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

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



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



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

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

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

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

• в бухгалтерских расчетах (зарплата, стаж, отпуска…)
• в логистике — для корректного определения сроков доставки с учётом выходных и праздников (помните классическое «давай уже после праздников?»)
• в управлении проектами — для правильной оценки сроков с учётом, опять же, рабочих-нерабочих дней
• при любом использовании функций типа РАБДЕНЬ (WORKDAY) или ЧИСТРАБДНИ (NETWORKDAYS), т.к. они требуют в качестве аргумента список праздников
• при использовании функций Time Intelligence (типа TOTALYTD, TOTALMTD, SAMEPERIODLASTYEAR и т.д.) в Power Pivot и Power BI
• … и т.д. и т.п. — примеров масса.

Тем, кто работает в корпоративных ERP-системах типа 1С или SAP — проще, в них производственный календарь встроен. Но что делать пользователям Excel?

Можно, конечно, вести такой календарь вручную. Но тогда придется как минимум раз в год (а то и чаще, как в «весёлом» 2020 году) его актуализировать, аккуратно внося туда все выходные, переносы и нерабочие дни, придуманные нашим правительством. А потом повторять эту процедуру каждый следующий год. Скукота.

А как насчёт чуть напрячься и сделать «вечный» производственный календарь в Excel? Такой, чтобы сам обновлялся, брал данные из интернета и формировал всегда актуальный список нерабочих дней для последующего использования в любых расчетах? Заманчиво?

Сделать такое, на самом деле, совсем не сложно.

Источник данных

Главный вопрос — а где брать данные? В поисках подходящего источника я перебрал несколько вариантов:

• Оригинальные указы публикуются на сайте правительства в формате PDF (вот, один из них, например) и отпадают сразу — полезную информацию из них не вытащить.
• Заманчивым вариантом, на первый взгляд, казался «Портал открытых данных РФ», где есть соответствующий набор данных, но, при ближайшем изучении всё оказалось печально. Для импорта в Excel сайт жутко неудобен, техподдержка не отвечает (самоизолировались?), да и сами данные там давно устарели — производственный календарь на 2020 год обновлялся последний раз в ноябре 2019 (позорище!) и, само-собой, не содержит наших «коронавирусных» и «голосовательных» выходных 2020 года, например.

Разочаровавшись в официальных источниках, я стал рыть неофициальные. Их в интернете множество, но большинство из них, опять же, совершенно неприспособлены для импорта в Excel и выдают производственный календарь в виде красивых картинок. Но нам-то с вами не на стенку его вешать, правильно?

И вот в процессе поисков случайно обнаружилось прекрасное — сайт http://xmlcalendar.ru/

Без лишних «рюшечек», простой, легкий и быстрый сайт, заточенный под одну задачу — отдавать всем желающим производственный календарь за нужный год в XML-формате. Отлично!

Если, вдруг, вы не в курсе, то XML — это текстовый формат с разметкой содержимого специальными <тегами>. Легкий, удобный и читаемый большинством современных программ, включая Excel.

Я, на всякий случай, связался с авторами сайта и они подтвердили, что сайт существует уже 7 лет, данные на нем постоянно актуализируются (у них для этого даже ветка на github есть) и закрывать его они не собираются. И совершенно не против, чтобы мы с вами с него грузили данные для любых наших проектов и расчётов в Excel. Бесплатно. Как же приятно, что есть еще такие люди! Респектище!

Осталось загрузить эти данные в Excel с помощью надстройки Power Query (для версий Excel 2010-2013 её можно бесплатно скачать с сайта Microsoft, а в версиях Excel 2016 и новее — она уже встроена по умолчанию).

Логика действий будет такая:

1. Делаем запрос для загрузки данных с сайта за один любой год
2. Превращаем наш запрос в функцию
3. Применяем эту функцию к списку всех имеющихся лет, начиная с 2013 и до текущего года — и получаем «вечный» производственный календарь с автоматическим обновлением. Вуаля!

Шаг 1. Импортируем календарь за один год

Сначала загрузим производственный календарь за один любой год, например, за 2020. Для этого в Excel идём на вкладку Данные (или Power Query, если вы установили её как отдельную надстройку) и выбираем Из интернета (From Web). В открывшееся окно вставляем ссылку на соответствующий год, скопированную с сайта:

После нажатия на ОК появляется окно предварительного просмотра, в котором нужно нажать кнопку Преобразовать данные (Transform data) или Изменить данные (Edit data) и мы попадем в окно редактора запросов Power Query, где и продолжим работу с данными:

Сразу же можно смело удалить в правой панели Параметры запроса (Query settings) шаг Измененный тип (Changed Type) — он нам не нужен.

Таблица в столбце holidays содержит коды и описания нерабочих дней — увидеть её содержимое можно, дважды «провалившись» в неё щелчком мыши по зелёному слову Table:

Для возврата назад придется удалить в правой панели все появившиеся шаги обратно до Источника (Source).

Вторая таблица, куда можно зайти аналогичным образом, содержит как раз то, что нам нужно — даты всех нерабочих дней:

Останется эту табличку обработать, а именно:

1. Отфильтровать только даты праздников (т.е. единички) по второму столбцу Attribute:t

2. Удалить все столбцы, кроме первого — щелчком правой кнопкой мыши по заголовку первого столбца и выбором команды Удалить другие столбцы (Remove Other Columns):

3. Разделить первый столбец по точке отдельно на месяц и день с помощью команды Разделить столбец — По разделителю на вкладке Преобразование (Transform — Split column — By delimiter):

4. И, наконец, создать вычисляемый столбец с нормальными датами. Для этого на вкладке Добавление столбца жмём на кнопку Настраиваемый столбец (Add Column — Custom Column) и вводим в появившееся окно следующую формулу:

=#date(2020, [#»Attribute:d.1″], [#»Attribute:d.2″])

Здесь у оператора #date три аргумента: год, месяц и день, соответственно. После нажатия на ОК получаем требуемый столбец с нормальными датами выходных, а остальные столбцы удаляем как в п.2

Шаг 2. Превращаем запрос в функцию

Следующая наша задача — преобразовать созданный на 2020 год запрос в универсальную функцию для любого года (номер года будет её аргументом). Для этого делаем следующее:

1. Разворачиваем (если ещё не развернута) панель Запросы (Queries) слева в окне Power Query:

2. После преобразования запроса в функцию возможность видеть шаги, из которых состоит запрос и легко их редактировать, к сожалению, пропадает. Поэтому имеет смысл сделать копию нашего запроса и резвиться уже с ней, а оригинал оставить про запас. Для этого щелкаем правой кнопкой мыши в левой панели по нашему запросу calendar и выбираем команду Дублировать (Duplicate).

Щелкнув еще раз правой по получившейся копии calendar(2) выберем команду Переименовать (Rename) и введём новое имя — пусть будет, например, fxYear:

3. Открываем исходный код запроса на внутреннем языке Power Query (он лаконично назвается «М») с помощью команды Расширенный редактор на вкладке Просмотр (View — Advanced Editor) и вносим туда небольшие правки для превращения нашего запроса в функцию на любой год.

Было:

Стало:

Если интересны подробности, то здесь:

• (year as number)=>  — объявляем, что в нашей функции будет один числовой аргумент — переменная year
• Подклеиваем переменную year в веб-ссылку на шаге Источник. Поскольку Power Query не позволяет склеивать числа и текст, то на лету преобразовываем номер года в текст с помощью функции Number.ToText
• Подставляем переменную year вместо 2020 года на предпоследнем шаге #»Добавлен пользовательский объект«, где мы формировали дату из фрагментов.

После нажатия на Готово наш запрос превращается в функцию:

Шаг 3. Импортируем календари за все года

Осталась самая малость — сделать последний главный запрос, который будет закачивать данные за все имеющиеся года и складывать все полученные даты праздников в одну таблицу. Для этого:

1. Щёлкаем в левой панели запросов в серое пустое место правой кнопкой мыши и выбираем последовательно Новый запрос — Другие источники — Пустой запрос (New Query — From other sources — Blank query):

2. Нам нужно сформировать список всех лет, для которых мы будем запрашивать календари, т.е. 2013, 2014 … 2020. Для этого в строке формул появившегося пустого запроса вводим команду:

Конструкция:

={ЧислоА..ЧислоБ}

… в Power Query формирует список целых чисел от А до Б. Например, выражение

={1..5}

…выдало бы список 1,2,3,4,5.

Ну, а чтобы не привязываться жёстко к 2020 году, мы используем функцию DateTime.LocalNow() — аналог экселевской функции СЕГОДНЯ (TODAY) в Power Query — и извлекаем из неё, в свою очередь, текущий год функцией Date.Year.

3. Полученный набор лет хоть и выглядит вполне адекватно, но для Power Query представляет собой не таблицу, а особый объект — список (List). Но преобразовать его в таблицу — не проблема: достаточно нажать кнопку В таблицу (To Table) в левом верхнем углу:

4. Финишная прямая! Применяем созданную нами ранее функцию fxYear к полученному списку лет. Для этого на вкладке Добавление столбца жмём кнопку Вызвать настраиваемую функцию (Add Column — Invoke Custom Function) и задаём её единственный аргумент — колонку Column1 с годами:

После нажатия на ОК наша функция fxYear по очереди отработает импорт для каждого года и мы получим столбец, где в каждой ячейке будет содержаться таблица с датами нерабочих дней (содержимое таблицы хорошо видно, если щёлкнуть мышью в фон ячейки рядом со словом Table):

Останется развернуть содержимое вложенных таблиц, щёлкнув по значку с двойными стрелками в шапке столбца Даты (галочку Использовать исходное имя столбца как префикс при этом можно снять):

… и после нажатия на ОК мы получим то, что хотели — список всех праздников с 2013 и до текущего года:

Первый, ненужный уже столбец, можно удалить, а для второго задать тип данных Дата (Date) в выпадающем списке в шапке столбца:

Сам запрос можно переименовать как-нибудь более осмысленно, чем Запрос1 и выгрузить затем результаты на лист в виде динамической «умной» таблицы с помощью команды Закрыть и загрузить на вкладке Главная (Home — Close & Load):

Обновлять созданный календарь в будущем можно щелчком правой кнопки мыши по таблице или запросу в правой панели через команду Обновить. Или же использовать кнопку Обновить все на вкладке Данные (Date — Refresh All) или сочетание клавиш Ctrl+Alt+F5.

Вот и всё.

Теперь вам больше никогда не нужно тратить время и мыслетопливо на поиски и актуализацию списка праздничных дней — теперь у вас есть «вечный» производственный календарь. Во всяком случае, до тех пор, пока авторы сайта http://xmlcalendar.ru/ поддерживают своё детище, что, я надеюсь, будет ещё очень и очень долго (спасибо им еще раз!).

Ссылки по теме

• Импорт курса биткойна в Excel из интернета через Power Query
• Поиск ближайшего рабочего дня функцией РАБДЕНЬ (WORKDAY)
• Поиск пересечения интервалов дат

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

Одним из самых простых способов генерации списка рабочих дней является использование WORKDAY. INTL.

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

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

= WORKDAY. INTL (B3, 1, 1, $ D $ 4: $ D $ 15)

На этом этапе вы можете скопировать формулу вниз, чтобы создать столько рабочих дней, сколько вам нужно.

РАБОТА. Функция INTL возвращает дату рабочего дня в зависимости от количества дней, которые вы указываете для увеличения. Эта функция имеет два обязательных аргумента и два необязательных аргумента:

• Дата начала (обязательно): Этот аргумент — это дата начала.

• Дней (обязательно): Этот аргумент — это количество дней с даты начала, которую вы хотите увеличить.

• Weekends (необязательно): По умолчанию WORKDAY. Функция INTL исключает субботу и воскресенье, но этот третий аргумент позволяет указать, какие дни следует исключать в качестве выходного дня. Когда вы входите в WORKDAYS. Функция INTL, Excel активирует интерактивную подсказку, из которой вы можете выбрать соответствующий код выходного дня.

• Праздники (необязательно): Этот аргумент позволяет вам предоставить Excel список дат для исключения в дополнение к выходным дням.

В этой формуле формулы вы указываете, что Excel начнется с 12/31/2012 и увеличится до 1, чтобы дать вам следующий рабочий день после даты начала. Для необязательных аргументов вы указываете, что вам нужно исключить субботу и воскресенье, а также праздники, перечисленные в ячейках $ D $ 4: $ D $ 15.

= WORKDAY. INTL (B3, 1, 1, $ D $ 4: $ D $ 15)

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