Производственный календарь в MS Excel

В предыдущей статье мы уже разобрались, как загрузить производственный календарь в Power BI.

Теперь разберём, как загрузить в Excel производственный календарь для последующего использования. В статье показан один из вариантов, далеко не единственный.
В качестве источника данных воспользуемся набором на Портале открытых данных РФ (https://data.gov.ru/opendata/7708660670-proizvcalendar).
Раскроем паспорт набора:

Набор представляет собой производственный календарь c 1999 до 2025 года. Звездочкой (*) отмечены предпраздничные (сокращенные) дни. Плюсом (+) отмечены перенесенные выходные дни.
Для загрузки в Excel скопируем ссылку на набор (https://data.gov.ru/opendata/7708660670-proizvcalendar/data-20191112T1252-structure-20191112T1247.csv):

Запустим Excel. Воспользуемся коннектором для получения данных с Web-страницы (вкладка “Данные” — “Создать запрос” — “Из других запросов” — “Из Интернета”):

В открывшемся диалоге вставим скопированную ранее ссылку на набор:

Предварительный просмотр данных показывает, что в таком виде данными пользоваться будет крайне сложно. Обработаем данные в Power Query.

Итого, у нас осталось 13 колонок — колонка с годом и с каждый месяцем. Развернём колонки с месяцами в плоский список. Для этого воспользуемся командой “Отменить свёртывание столбцов”:

Команда “Отменить свёртывание столбцов” создаёт пары для каждого сочетания названия столбца и ячейки в этом столбце и превращает их в столбцы:

Далее разделим столбец с днями на отдельные столбцы с помощью команды ”Разделить столбец” — “По разделителю”:

В качестве разделителя укажем запятую, разделим по каждому вхождения разделителя:

И получим таблицу, где каждая строка — характеристики одной даты. Столбец “Атрибут.1” появился как порядковый номер конкретного числа в ячейке, он не пригодится. Удалим его:

Теперь мы можем обработать звёздочки и плюсики, присвоив каждой строке (= каждой дате) некоторый признак. Чтобы далее использовать вычисление, определяющее вхождение символов * или + в строку — нам необходимо преобразовать столбец в текст:

Далее добавляем условный столбец, который будет присваивать признак каждому дню в зависимости от вхождения * и +:

Получаем вот такой столбец:

Теперь можно избавить из дополнительных символов у числа (* и +), чтобы перейти к дальнейшим шагам обработки. Для удаления символов воспользуемся командой “Замена значений”:

Заменим звёздочку на “ничего”. Оставим поле с итоговым значением пустым. Это будет равносильно тому, чтобы удалить выбранные подстроки (в данном случае — звёздочки):

Проделаем аналогичную операцию для удаления плюсиков. Теперь у нас есть три столбца — части даты. Соберём их в дату с помощью вычисляемого столбца:

В результате получим столбец с датой — Date. Теперь можем удалить составные столбцы — они свою роль сыграли:

Осталось скорректировать типы данных. Поставить, что столбец Date — это данные типа Дата, Day type — Текст:

Результат:

Итоговый код обработки данных:

#»Условный столбец добавлен» = Table.AddColumn(#»Измененный тип2″, «Date type», each if Text.Contains([Значение], «*») then «Сокращённый рабочий» else «Выходной»),

#»Замененное значение» = Table.ReplaceValue(#»Условный столбец добавлен»,»*»,»»,Replacer.ReplaceText,),

#»Добавлен пользовательский объект» = Table.AddColumn(#»Замененное значение1″, «Date», each Date.From(

[expert_bq id=»1570″]РАБДЕНЬ нач_дата; число_дней; праздники , функция возвращает число, которое представляет дату, отстоящую на заданное количество рабочих дней вперед или назад от начальной даты;. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] В ячейку A1 вбиваем следующую формулу: =»Календарь на » & ГОД(СЕГОДНЯ()) & » год». Функция СЕГОДНЯ() возвращает текущую дату (мы часто будем ей пользоваться). Соответственно связка функций ГОД(СЕГОДНЯ()) возвратит нам текущий год.

Создание календаря в Excel на год

1. Что такое функция?
2. Сколько функций входит в Excel, и на какие категории распределяются?
3. Какие существуют способы вызова окна “Мастера функций”?
4. Статистические функции и их синтаксис?

Метод 1: преобразовать число в название месяца с помощью формулы. Чаевые: Если вы хотите преобразовать число в сокращение названия месяца, вы можете использовать эту формулу = ТЕКСТ (ДАТА (2000; A1,1; XNUMX); «ммм»).

[expert_bq id=»1570″]РАБДЕНЬ нач_дата; число_дней; праздники , функция возвращает число, которое представляет дату, отстоящую на заданное количество рабочих дней вперед или назад от начальной даты;. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Для тех, кто просто хочет скачать календарь в Excel на текущий год, на картинке ниже показан как он выглядит. Красным отмечены праздничные дни, розовым — выходные. Текущий день, также автоматически выделяется заливкой.

Автоматически выделяем текущую дату в календаре

8. =ДЕНЬНЕД (дата_в_числовом_формате; тип), функция возвращает число от 1 до 7, соответствующему номеру дня недели для заданной даты. В данном примере выбираем “Тип” 2, т.е. понедельнику присваивается номер 1;

Дополнительные данные → Похожие темы → Все про Exel → Как вставить значения → Как объединить ячейки → Как вставить форматы → Дополнительные данные → Вставить формулы→ Аргументы функции