Как Преобразовать Месяцы в Годы в Excel • Добавляем праздничные дни

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

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

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

Как Преобразовать Месяцы в Годы в Excel • Добавляем праздничные дни

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

Как Преобразовать Месяцы в Годы в Excel • Добавляем праздничные дни

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

Как Преобразовать Месяцы в Годы в Excel • Добавляем праздничные дни

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

Как Преобразовать Месяцы в Годы в Excel • Добавляем праздничные дни

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

Как Преобразовать Месяцы в Годы в Excel • Добавляем праздничные дни

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

Как Преобразовать Месяцы в Годы в Excel • Добавляем праздничные дни

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

Как Преобразовать Месяцы в Годы в Excel • Добавляем праздничные дни

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

Как Преобразовать Месяцы в Годы в Excel • Добавляем праздничные дни

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

Как Преобразовать Месяцы в Годы в Excel • Добавляем праздничные дни

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

Как Преобразовать Месяцы в Годы в Excel • Добавляем праздничные дни

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

Как Преобразовать Месяцы в Годы в Excel • Добавляем праздничные дни

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

Как Преобразовать Месяцы в Годы в Excel • Добавляем праздничные дни

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

Как Преобразовать Месяцы в Годы в Excel • Добавляем праздничные дни

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

Как Преобразовать Месяцы в Годы в Excel • Добавляем праздничные дни

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

Как Преобразовать Месяцы в Годы в Excel • Добавляем праздничные дни

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

Как Преобразовать Месяцы в Годы в Excel • Добавляем праздничные дни

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

Как Преобразовать Месяцы в Годы в Excel • Добавляем праздничные дни

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

Как Преобразовать Месяцы в Годы в Excel • Добавляем праздничные дни

Результат:

Как Преобразовать Месяцы в Годы в Excel • Добавляем праздничные дни

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

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

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

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

Microsoft Excel 2007. Функции дата и время. 8–10-й класс

Теперь мы можем обработать звёздочки и плюсики, присвоив каждой строке (= каждой дате) некоторый признак. Чтобы далее использовать вычисление, определяющее вхождение символов * или + в строку — нам необходимо преобразовать столбец в текст:
эксперт
Мнение эксперта
Михаил Соловьев, консультант по вопросам работы с продуктами Microsoft
Если у вас возникнут сложности, я помогу разобраться!
Задать вопрос эксперту
РАБДЕНЬ нач_дата; число_дней; праздники , функция возвращает число, которое представляет дату, отстоящую на заданное количество рабочих дней вперед или назад от начальной даты;. Если же вы хотите что-то уточнить, обращайтесь ко мне!
В ячейку A1 вбиваем следующую формулу: =»Календарь на » & ГОД(СЕГОДНЯ()) & » год». Функция СЕГОДНЯ() возвращает текущую дату (мы часто будем ей пользоваться). Соответственно связка функций ГОД(СЕГОДНЯ()) возвратит нам текущий год.
Как Преобразовать Месяцы в Годы в Excel • Добавляем праздничные дни

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

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

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

Форматируем
Создание календаря в Excel на целый год с указанием праздничных и выходных дней. Вы можете сразу скачать календарь в Excel или же потратить 15 минут и научиться делать его самостоятельно, параллельно открыв для себя новые возможности Excel.
эксперт
Мнение эксперта
Михаил Соловьев, консультант по вопросам работы с продуктами Microsoft
Если у вас возникнут сложности, я помогу разобраться!
Задать вопрос эксперту
РАБДЕНЬ нач_дата; число_дней; праздники , функция возвращает число, которое представляет дату, отстоящую на заданное количество рабочих дней вперед или назад от начальной даты;. Если же вы хотите что-то уточнить, обращайтесь ко мне!
Для тех, кто просто хочет скачать календарь в Excel на текущий год, на картинке ниже показан как он выглядит. Красным отмечены праздничные дни, розовым — выходные. Текущий день, также автоматически выделяется заливкой.

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

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

Понравилась статья? Поделиться с друзьями:
Добавить комментарий

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

Adblock
detector