Evolcom.ru

Бытовая техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как преобразовать 1-12 в название месяца в Excel?

Power Query: как перестать бояться функционального программирования и начать работать в расширенном редакторе запросов

Некоторое время назад я наткнулся на статью на сайте Habra о том, как использовать Power BI и как выполнить с его помощью простой план-фактный анализ. Огромное спасибо автору за его труд — материал действительно полезный. Более чем достаточно для начинающих. Но, насколько я понимаю, для многих людей работа с PQ/PBI ограничивается нажатием кнопок в интерфейсе.

Для большинства пользователей этого должно быть достаточно для решения простых задач. Тем более что это самое большинство, которое составляет большинство в повседневной жизни — непрограммисты. И, как показала практика, далеко не все знают, что в PQ есть расширенный режим редактирования запросов. Между тем, страх (нежелание/неумение) копать глубже сковывает функциональность PQ/PBI. Примечательно хотя бы то, что в интерфейсе есть не все кнопки, которые можно использовать. Количество функций, вероятно, в два раза больше, чем количество кнопок.

Если вы чувствуете, что функциональности интерфейса недостаточно для решения ваших текущих задач и/или у вас есть время для удовлетворения академического интереса, приглашаем вас на эту вкладку.

Преамбула

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

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

Что делает автор, переводя код на русский язык

  1. Вручную создает список названий месяцев
  2. Преобразует список в таблицу
  3. Добавляет столбец с индексом, начинающимся с нуля, с шагом в единицу
  4. Преобразует столбец «Индекс», добавляя единицу в каждую ячейку
  5. Меняет имя столбца по умолчанию «Column1» на «Месяц».
  • Минимальное количество строк кода благодаря использованию встроенных функций (например, не переименовывать столбец, а дать ему сразу, не увеличивать столбец, а начать с единицы и т.д.)
  • Отсутствие ручного ввода данных (легко ошибиться, введя их неправильно)
  • Использование названий месяцев в зависимости от местоположения

Критика

Disclamer. Сразу скажу, что у меня нет претензий к автору или его подходу к написанию скриптов (возможно, что скрипты были написаны наспех). В этой статье я хотел показать новичкам, как писать скрипты, используя различные подходы.

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

Вот что меня возмутило:

Читайте так же:
Как подсчитать уникальные значения на основе нескольких критериев в Excel?

Список месяцев набирается вручную
Во-первых, как уже было отмечено выше, набирая текст, можно легко ошибиться, во-вторых, все эти кавычки/запятые… Ну их — только путаница. Поэтому, вместо того, чтобы создавать список, перечисляя месяцы, я бы предложил использовать обычную текстовую строку с естественным разделителем. Т.е. запятую.

Текст кажется ясным по каждому пункту. Функция Split преобразует строку в список, разделяя ее разделителем «, «.

Индекс может начинаться с чего угодно, даже с единицы (если вы читаете документацию)

Более того, в табл. Если последний аргумент не один, то индексный столбец будет добавлен в буфер на другом шаге. Соответственно, можно пронумеровать и ячейки — 15,20,25,30.

С этого все и началось, собственно…

Во что переросла критика

Далее я приведу несколько примеров кода с комментариями постепенно возрастающей сложности. Я начну с того, как сформировать список месяцев.

Самый простой метод — прямое перечисление с использованием синтаксиса языка М:

Немного сложнее — разделение линии разделителем :

Использование функции формирования списка данных:

Во-первых, у нас есть функция List. Функция Dates возвращает список дат, содержащий начальную дату, количество элементов и шаг приращения. В данном случае, добавляя 32 дня к месяцу, мы гарантированно попадаем в следующий месяц с каждым шагом. Очевидно, что этот трюк не сработает, если в календаре 13 или более месяцев. Однако для тренировочных целей он подойдет.

Второй шаг — преобразование списка дат в список названий месяцев. После изучения документации выяснилось, что функция Date.MonthName отвечает за преобразование даты в месяц. Он принимает первый аргумент даты и задает название месяца в виде строки. А если в качестве третьего аргумента указать необязательный аргумент culture («ru-RU», «en-US», «uk-UA», «ar-LY»), то мы получим название месяца, включая местоположение. Итак, нам нужно применить эту функцию к каждому элементу списка.

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

Отлично, говорим мы, и отправляем второй аргумент в Date. MonthName. Далее мы объясним, что происходит — List. Функция transform берет каждый элемент массива и передает его в функцию Date. MonthName, неявно передавая каждую Date в качестве аргумента.

Хорошо, но что если мы хотим, чтобы названия месяцев были локализованы? Скажем, «Ук-УА». Как установить этот параметр? Как мы помним, List.Transform принимает в качестве второго аргумента _функцию_ (и только функцию), и мы не можем явно передать этой функции первый аргумент, не говоря уже о втором. Поэтому нам нужна функция, которая принимает, как в документации, один параметр. Давайте создадим его! Назовите его «fn», сделайте в нем то, что нам нужно, и передайте ему List.Transform. В javascript это называется «цикл»:

Читайте так же:
Как подсчитать максимальное или минимальное количество вхождений в Excel?

После этого наш код будет выглядеть следующим образом:

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

В исходном списке месяц также отображался с маленькой буквы. Мы сделаем это в той же функции:

Теперь попробуем сгенерировать список месяцев немного другим способом: с помощью функции List.Generate. В документации указано следующее:

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

Хорошо, давайте возьмем несколько для ясности:

Что здесь происходит. Вычислите функцию инициализации первого аргумента. Это дата «1 января 2016 года». После этого вычисляется функция, заданная вторым аргументом, значение (x), вычисленное на предыдущем шаге, неявно передается в эту функцию. Эта вторая функция возвращает true/false, что означает, можно ли продолжать вычисления или нет. Следовательно, «1 января 2016 года» * «1 января 2017 года» является точным, то есть мы можем продолжать. К этому времени значение предыдущего шага отправляется в третью функцию, которая просто добавляет месяц к вычисленному значению. Мы получаем «1 февраля 2016 года». Это значение затем отправляется во вторую функцию, где «1 февраля 2016 года» сравнивается с «1 января 2017 года» и функция по-прежнему выдает true, затем добавляется еще один месяц и т. д. По мере добавления месяцев к «1 декабря 2016 года» вычисленное значение становится «1 января 2017 года». В данном случае мы получим false, так как «1 января 2017» равно «1 января 2017», но не «меньше».

После вычисления накопленных таким образом промежуточных значений к каждому из элементов списка применяется конечная функция Date.MonthName. Точно так же, как описано выше для функции List.Transform

Что если, как и в прошлый раз, мы хотим, чтобы месяцы сортировались по локали, а текст начинался со строчной буквы? После этого мы создаем свою собственную пользовательскую функцию и делаем в ней все, что нам нужно:

Этот метод не учитывает количество месяцев в году, поэтому его можно применять к любому году. Даже к 2976, как на Плутоне. Мы выбираем первый день первого месяца одного года и первый день первого месяца следующего года. Кроме того, мы добавляем к дате месяц и строгое неравенство.

Что если мы точно знаем, сколько месяцев в году. Тогда мы можем сформировать массив месяцев по их номерам в случае, если у нас есть массив из чисел 1-12:

Читайте так же:
Как показать или скрыть все объекты (изображения / диаграммы / фигуры / комментарии) в Excel?

Наша команда уже ознакомилась с этим списком. Transform, в котором в качестве первого аргумента передается массив чисел, а в качестве второго — функция для преобразования числа в месяц. Это довольно примитивная идея, но это понятно.

Функция List.TransformMany гораздо интереснее. В принципе, это то же самое, что и List.Transform, но в отличие от List.Transform, List.TransformMany имеет еще один аргумент, функцию, которая неявно передает два аргумента: _основное_ значение и _вычисленное_ значение, что позволяет нам использовать сразу и номер месяца, и его вычисленное имя. Все, что нам нужно сделать, это объединить их в одну строку:

Позже я покажу, как с его помощью создать таблицу.

Переходим к таблицам

Пока все просто — Текст. Разделение, Таблица. Добавление индексного столбца с началом в единице и добавление таблицы. Здесь используется FromList с одновременным именованием столбцов (нет необходимости переименовывать «Custom1»).

Table.FromList + List.Positions
Здесь — нетипичное применение функции Table.FromList. Вторым аргументом ей передаётся функция, возвращающая массив ячеек в строек. Последовательность следующая — создаётся массив строк src, к нему создаётся массив индексов pos, далее массив индексов преобразуется в таблицу, используя кастомную функцию. Эта кастомная функция перебирает массив индексов и обращается к массиву строк по выбранному индексу.

Объясню попроще — например, берётся, скажем, элемент №0 из списка «pos» и передаётся в функцию, где из 0 формируется массив , 0+1>, т.е. <«январь», 1>. Далее функция Table.FromList раскладывает их на два столбца по «Месяц» и «Index»

Когда вы используете только интерфейс, вы увидите поддельную функцию Splitter. SplitByNothing() передается в Table. Эта функция по умолчанию имеет значение FromList, что по сути является заглушкой для записи в Table. FromList.

Мы объединяем два параллельных списка в один с помощью функции Table.FromColumns. Здесь мы создаем два параллельных списка — список месяцев и список индексов. Если попытаться представить себе этот процесс, то он «похож» на застегивание одежды 😉 Прежде чем «застегнуть» список индексов, мы преобразуем его, добавляя по единице к каждому элементу:

Я опишу обещанный пример с List. TransformMany в деталях. Представим, что у нас есть список чисел от 1 до 12:

Кроме того, имеется функция преобразования чисел в месяцы:

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

Также создадим финальную функцию, которая на каждом этапе, используя стартовое и вычисленное значение, создаёт список из стартового и вычисленного. Типа такого — <«march», «3»>:

Теперь передайте функцию List. TransformMany каждому элементу и конечную функцию в качестве аргументов и применим их к нашему массиву от 1 до 12:

Читайте так же:
Как повторить последнее или предыдущее действие в Excel?

После этого создайте таблицу из полученного массива массивов.

В итоге получаем следующую таблицу:

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

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

И, пожалуй, последний способ создания начального массива — это метод List.Accumulate. Вот что говорится об этом в справке:

Я добавлю несколько собственных примеров. Из текстовых значений можно накапливать кумулятивное значение. Например, как объединить все элементы списка в одну строку, разделив их точкой, одновременно преобразуя строки в верхний регистр:

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

В этом примере берется пустая таблица и «вставляются» номера месяцев в текстовую строку. То же самое относится и к столам:

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

Пытаясь объяснить некоторые сложности работы непосредственно с кодом, я надеюсь, что не запутал вас. M — это элегантный и лаконичный язык. И если вы собираетесь изо дня в день выполнять всевозможные выкрутасы в своей работе, имеет смысл потратить некоторое время на его изучение.

Функции и формулы для преобразования номера в название месяца

Время от времени необходимо менять номер месяца на его название и наоборот (например, 3 на «Март» или «Октябрь» на 10).

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

Изменение порядкового номера месяца на его название в Excel

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

Где $A$2:$A$13 — это, конечно же, диапазон с названиями месяцев, а B2 — ячейка с порядковым номером в том году, для которого мы ищем его название.

Пример 1.

Вы также можете использовать функцию SELECT для достижения тех же результатов, что и INDEX. Разница в том, что она не использует ячейки листа, в которых хранятся названия месяцев. В качестве аргументов функции задаются названия месяцев.

Читайте так же:
Как повторить строку заголовка каждую n-ю строку в Excel?

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

Если указать названия месяцев в виде списка значений (по порядку) и ячейку с номером месяца в качестве индекса, то функция вернет соответствующее название для каждого заданного числа.

ВЫБОР.

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

Примеры как получить название месяца из даты в Excel

Как и в предыдущем случае, когда вы хотите «извлечь» название месяца из даты, нужно использовать функцию MONTH.

Если у вас есть список с датами и вам нужно «вытащить» из него названия месяцев с помощью функции ИНДЕКС, возникнет ошибка. Чтобы формула работала правильно, необходимо использовать функцию MONTH, чтобы «вытащить» номер месяца из ячейки с датой. В этом случае формула будет выглядеть следующим образом:

по дате.

Другой способ (возможно, самый практичный) — использовать функцию TEXT (функция TEXT преобразует числовое значение в текст с соответствующим форматированием).

Тип : если у вас есть список дат и вам нужно «вытащить» из них названия месяцев:

Где E2 — это, конечно, ячейка даты.

ИНДЕКС.

Если вы работаете только с числами (1-12), функция в приведенной выше форме не будет работать должным образом. Вам нужно помнить, что число 1 в Excel — это 1 января 1900 года, число 2 — 2 января 1900 года и так далее. Это все тот же месяц — январь. Поэтому TEXT(E2; «MMMM») возвращает «январь» для каждого номера месяца.

Однако последовательные числа можно легко преобразовать в числа месяца. Например, вы можете умножить каждое число на среднее количество дней в месяце (30).

ТЕКСТ.

Будет работать корректно и вернет в качестве результата названия месяцев.

Как преобразовать название месяца в порядковый номер

И наконец, мы преобразуем название месяца обратно в порядковый номер соответствующего года.

Пример1. Формула первая:

получить название месяца из даты.

Excel автоматически преобразует December1 в дату December.01. Затем функция ДАТАЗНАЧ преобразует дату в число, а затем, используя функцию МЕСЯЦ, мы получаем порядковый номер в году для названия текущего месяца. По сути, формулу можно упростить:

Пример2. Вторая формула:

преобразовать название месяца в номер.

Пример3. Формула третья:

ВПР.

Наиболее оптимальным вариантом является лаконичная формула =МЕСЯЦ(A2&»1″). В ней не нужно резервировать названия месяцев и присваивать им соответственные порядковые номера. К тому же в ней используется всего лишь одна функция.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector