Производственный календарь в excel с возможностью редактирования
Для автоматизации процесса нам понадобится
* для Excel 2010 и 2013 вам понадобится бесплатная надстройка Power Query, скачать можно отсюда.
На странице так же доступны предыдущие версии и версия производственного календаря в xlsx:
Для Excel 2010 и Excel 2013 такой же функционал доступен в виде бесплатной надстройки Power Query, можно скачать отсюда. Функционал надстройки развивается каждый месяц, я рекомендую регулярно обновляться, чтобы всегда иметь под рукой новейшие функции и возможности трансформации данных.
Для скачивания чего-либо из Интернета нам нужна ссылка на файл или страницу.
Портал открытых данных публикует наборы данных и обязательно сопровождает каждый набор Паспортом.
Вставляем ссылку на csv файл
Теперь необходимо преобразовать полученные данные.
- Используем первую строку в качестве заголовков столбцов
Зададим фильтр с нужными нам условиями
В результате получается таблица
- Получив столбец с датой, можно убрать предыдущие столбцы
Подобным запросом мы получаем не полный список календарных дат, так как публикуемый на портале Открытых Данных набор содержит только праздничные и предпраздничные дни.
Полученный список может быть использован в функциях РАБДЕНЬ (WORKDAY), РАБДЕНЬ.МЕЖД (WORKDAY.INTL), а также для разметки полного календаря рабочими / не рабочими днями (что актуально для моделей в Power Pivot и Power BI).
Разобраться, как это работает, вы можете, изучив xlsx-файл доступный по ссылке.
В нём вы найдёте несколько запросов
При создании таблиц с определенным типом данных иногда нужно применять календарь. Кроме того, некоторые пользователи просто хотят его создать, распечатать и использовать в бытовых целях. Программа Microsoft Office позволяет несколькими способами вставить календарь в таблицу или на лист. Давайте выясним, как это можно сделать.
Создание различных календарей
Все календари, созданные в Excel, можно разделить на две большие группы: охватывающие определенный отрезок времени (например, год) и вечные, которые будут сами обновляться на актуальную дату. Соответственно и подходы к их созданию несколько отличаются. Кроме того, можно использовать уже готовый шаблон.
Способ 1: создание календаря на год
Прежде всего, рассмотрим, как создать календарь за определенный год.
- Разрабатываем план, как он будет выглядеть, где будет размещаться, какую ориентацию иметь (альбомную или книжную), определяем, где будут написаны дни недели (сбоку или сверху) и решаем другие организационные вопросы.
- Для того, чтобы сделать календарь на один месяц выделяем область, состоящую из 6 ячеек в высоту и 7 ячеек в ширину, если вы решили писать дни недели сверху. Если вы будете их писать слева, то, соответственно, наоборот. Находясь во вкладке «Главная», кликаем на ленте по кнопке «Границы», расположенной в блоке инструментов «Шрифт». В появившемся списке выбираем пункт «Все границы».
Открывается окно, в котором нужно установить требуемую высоту строки. Ели вы впервые делаете подобную операцию и не знаете, какой размер установить, то ставьте 18. Потом жмите на кнопку «OK».
Теперь нужно установить ширину. Кликаем по панели, на которой указаны наименования столбцов буквами латинского алфавита. В появившемся меню выбираем пункт «Ширина столбцов».
В открывшемся окне установите нужный размер. Если не знаете, какой размер установить, можете поставить цифру 3. Жмите на кнопку «OK».
После этого, можно считать, что календарь готов, хотя вы можете дополнительно отформатировать его на своё усмотрение.
Способ 2: создание календаря с использованием формулы
Но, все-таки у предыдущего способа создания есть один весомый недостаток: его каждый год придется делать заново. В то же время, существует способ вставить календарь в Excel с помощью формулы. Он будет каждый год сам обновляться. Посмотрим, как это можно сделать.
-
В левую верхнюю ячейку листа вставляем функцию:
="Календарь на " & ГОД(СЕГОДНЯ()) & " год"
Таким образом, мы создаем заголовок календаря с текущим годом.
Но, как видим, в том месте, где должно отобразиться просто название месяца установилась дата. Для того, чтобы привести формат ячейки к нужному виду, кликаем по ней правой кнопкой мыши. В контекстном меню выбираем пункт «Формат ячеек…».
Производим в блоке календаря за январь выделение ячеек, в которых содержатся числа. Кликаем по значку «Условное форматирование», размещенному на ленте во вкладке «Главная» в блоке инструментов «Стили». В появившемся перечне выбираем значение «Создать правило».
Открывается окно создания правила условного форматирования. Выбираем тип «Использовать формулу для определения форматируемых ячеек». В соответствующее поле вставляем формулу:
=И(МЕСЯЦ(D6)1+3*(ЧАСТНОЕ(СТРОКА(D6)-5;9))+ЧАСТНОЕ(СТОЛБЕЦ(D6);9))
D6 – это первая ячейка выделяемого массива, который содержит даты. В каждом конкретном случае её адрес может отличаться. Затем кликаем по кнопке «Формат».
В открывшемся окне переходим во вкладку «Шрифт». В блоке «Цвет» выбираем белый или цвет фона, если у вас установлен цветной фон календаря. Жмем на кнопку «OK».
В целом работа над созданием «вечного» календаря завершена, хотя вы можете ещё долго проводить над ним различные косметические работы, редактируя внешний вид на свой вкус. Кроме того, отдельно можно будет выделить, например, праздничные дни.
Способ 3: использование шаблона
Те пользователи, которые ещё в недостаточной мере владеют Экселем или просто не хотят тратить время на создание уникального календаря, могут воспользоваться готовым шаблоном, закачанным из интернета. Таких шаблонов в сети довольно много, причем велико не только количество, но и разнообразие. Найти их можно, просто вбив соответствующий запрос в любую поисковую систему. Например, можно задать следующий запрос: «календарь шаблон Excel».
Вы можете в нем с помощью кнопки заливки во вкладке «Главная» закрасить различными цветами ячейки, в которых содержатся даты, в зависимости от их важности. Собственно, на этом вся работа с подобным календарем может считаться оконченной и им можно начинать пользоваться.
Мы разобрались, что календарь в Экселе можно сделать двумя основными способами. Первый из них предполагает выполнение практически всех действий вручную. Кроме того, календарь, сделанный этим способом, придется каждый год обновлять. Второй способ основан на применении формул. Он позволяет создать календарь, который будет обновляться сам. Но, для применения данного способа на практике нужно иметь больший багаж знаний, чем при использовании первого варианта. Особенно важны будут знания в сфере применения такого инструмента, как условное форматирование. Если же ваши знания в Excel минимальны, то можно воспользоваться готовым шаблоном, скачанным из интернета.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Июнь 2020
См. также: Производственный календарь на: 2021, 2020, 2019, 2018, 2017.
Производственный календарь 2020
Желтый цвет: Нерабочие дни с сохранением зарплаты в соответствии с указами Президента РФ от 25.03.2020 № 206, от 02.04.2020 № 239, кроме организаций, на которые действие указов не распространяется.
Норма рабочего времени в 2020 году
Порядок исчисления нормы рабочего времени на определенные календарные периоды времени (месяц, квартал, год) в зависимости от установленной продолжительности рабочего времени в неделю утвержден приказом Минздравсоцразвития России от 13 августа 2009 г. N 588н.
| Период | Д кален дарных | Д рабочих | Д выход, празд. | Д пред- празд. | Часы 40- часовая | Часы 36- часовая | Часы 24- часовая |
| Январь | 31 | 17 | 14 | 0 | 136,0 | 122,4 | 81,6 |
| Февраль | 29 | 19 | 10 | 0 | 152,0 | 136,8 | 91,2 |
| Март | 31 | 19 | 12 | 0 | 152,0 | 136,8 | 91,2 |
| 1 квартал | 91 | 55 | 36 | 0 | 440,0 | 396,0 | 264,0 |
| Апрель | 30 | 0 | 30 | 0 | 0,0 | 0,0 | 0,0 |
| Май | 31 | 14 | 17 | 0 | 112,0 | 100,8 | 67,2 |
| Июнь | 30 | 20 | 10 | 1 | 159,0 | 143,0 | 95,0 |
| 2 квартал | 91 | 34 | 57 | 1 | 271,0 | 243,8 | 162,2 |
| 1 полугодие | 182 | 89 | 93 | 1 | 711,0 | 639,8 | 426,2 |
| Июль | 31 | 22 | 9 | 0 | 176,0 | 158,4 | 105,6 |
| Август | 31 | 21 | 10 | 0 | 168,0 | 151,2 | 100,8 |
| Сентябрь | 30 | 22 | 8 | 0 | 176,0 | 158,4 | 105,6 |
| 3 квартал | 92 | 65 | 27 | 0 | 520,0 | 468,0 | 312,0 |
| Октябрь | 31 | 22 | 9 | 0 | 176,0 | 158,4 | 105,6 |
| Ноябрь | 30 | 20 | 10 | 1 | 159,0 | 143,0 | 95,0 |
| Декабрь | 31 | 23 | 8 | 1 | 183,0 | 164,6 | 109,4 |
| 4 квартал | 92 | 65 | 27 | 2 | 518,0 | 466,0 | 310,0 |
| 2 полугодие | 184 | 130 | 54 | 2 | 1 038,0 | 934,0 | 622,0 |
| Год | 366 | 219 | 147 | 3 | 1 749,0 | 1 573,8 | 1 048,2 |
Для рабочего проекта я выбрал способ получения дат через VBA(для поддержки всех версий независимо от надстроек), но в связи с популярностью Power Qwery решил сделать решение и при помощи этой надстройки.
Я постарался в коде в некоторых местах прописать комментарии, т.к. прописывать их напрямую в статье не очень удобно - код не маленький и описывать каждый кусок проблематично и больше запутает, чем прояснит процесс, как мне кажется.
Product Calendar_VBA.xlsm (76,8 KiB, 370 скачиваний)
Прежде чем читать далее и пробовать применить, необходимо знать азы работы в Power Query(Power Query - что такое и почему её необходимо использовать в работе?), уметь создавать и редактировать запросы и вызвать расширенный редактор: переходим в нужный запрос -Главная -Расширенный редактор:
В случае с Power Qwery все с одной стороны проще, а с другой есть свои нюансы. Взять хотя бы попытку получить напрямую текст страницы https://data.gov.ru/opendata/7708660670-proizvcalendar : если попытаться подключиться через стандартный метод(Другие (Other) -Из интернета (from Web) , то придется очень долго разворачивать элемент Document на составные части разметки HTML в поисках тегов DIV и A для определения строки с гиперссылкой. Что на мой взгляд не оптимально и уж совсем не гибко - любое изменение структуры страницы, даже малейшее может привести к ошибке.
Поэтому я использовал менее очевидный, но куда более удобный в данном случае вариант - Lines.FromBinary(Web.Contents("https://data.gov.ru/opendata/7708660670-proizvcalendar")) . Это самая важная строка в текущей задаче - она получает исходный текст страницы сайта в виде разбитого на строки текста, в котором потом можно будет просматривать и искать нужное нам
а дальше по сути идет тоже самое, что делалось кодом VBA: ищем в этом тексте ссылку, выдергиваем только ссылку для скачивания файла календаря, подключаемся к этой ссылке для получения конечного CSV и делаем преобразования. Только это выглядит куда проще и заметно короче, чем тоже самое на VBA :) Сам код из расширенного редактора:
Так же не стал расписывать со скринами по шагам все преобразования, т.к. каждый желающий может скачать файл(приложен ниже) с запросом PQ и просмотреть по шагам все действия:
Но если вдруг это надо будет - пишите в комментариях, постараюсь описать процесс наглядно(в будущем подготовлю видеоурок на данную тему).
Читайте также: