Как в Excel можно получить из названия месяца его номер (если март то 3)?
Какую функцию EXCEL надо использовать чтобы по названию месяца получить его порядковый номер в году?
Например январь — 1, февраль — 2 и.т.д.
Как преобразовать месяц в число, соответствующее номеру месяца?
Для получения из текста номера месяца можно использовать функцию ПОИСКПОЗ .
Для этого надо составить массив названий месяцев в порядке их следования, начиная с января.
Если название месяца содержится в ячейке A1 то формула для получения номера месяца будет такой:
Но в ячейке A1 месяц может быть записан в определённом падеже и тогда ничего не найдётся.
Лучше сократить формулу и искать месяц по первым трём буквам, учитывая что слово май может быть записано как мая надо включить замену функцией подставить:
С учётом разных падежей слова "май" получится следующая формула преобразующая в число любой месяц в любом падеже, можно скопировать и пользоваться:
Как получить название месяца по дате в Excel (4 простых способа)
Excel позволяет форматировать даты разными способами. Вы можете выбрать отображение даты в коротком или длинном формате даты.
Вы также можете отображать только номер дня, название месяца или год с заданной даты.
В этом коротком руководстве по Excel я покажу вам несколько простых способов получить название месяца по дате в Excel.
Получение названия месяца по дате
Есть несколько разных способов получить ежемесячный отчет с даты в Excel.
Выбранный вами метод будет зависеть от того, как вы хотите получить результат (т.е. хотите ли вы его в виде текстовой строки или иметь полную дату, но отображать только название месяца)
Давайте посмотрим на несколько способов сделать это, которые вы можете использовать в разных сценариях.
Пользовательское форматирование
Использование произвольного форматирования чисел для получения названия месяца — лучший метод из всех, рассмотренных в этом руководстве.
Это потому, что это не меняет основную дату. Он только изменяет способ отображения даты в ячейке, то есть показывает только название месяца с даты.
Преимущество использования этого метода заключается в том, что вы по-прежнему можете использовать дату в расчетах.
Предположим, у вас есть даты, показанные ниже, и вы хотите отображать только название месяца, а не всю дату.
Ниже приведены шаги для этого:
- Выделите все ячейки с датами, для которых вы хотите отображать название месяца.
- Перейдите на вкладку «Главная»
- В группе «Номер» щелкните значок средства запуска диалогового окна (или вы можете использовать сочетание клавиш Ctrl +1). Откроется диалоговое окно Формат ячеек.
- В параметрах категории нажмите Пользовательский
- В поле типа введите — ‘мммм‘. вы должны увидеть одно из названий месяцев в окне предварительного просмотра образца.
- Нажмите ОК.
Вышеупомянутые шаги преобразуют все даты в их соответствующие полные названия месяцев (как показано ниже).
Как я уже упоминал, этот метод хорош тем, что даже если вы видите названия месяцев в ячейке, базовое значение по-прежнему является датой (что означает, что базовое значение по-прежнему является числом, представляющим дату).
Итак, если вы хотите использовать эти даты в расчетах, вы можете легко это сделать.
Вы также можете использовать настраиваемое форматирование чисел, чтобы по-разному отображать название месяца или значение месяца. Для этого вам нужно будет указать правильный код для пользовательского форматирования чисел, чтобы отображалось название месяца.
Ниже приведены различные коды месяцев, которые вы можете использовать:
- м — это покажет номер месяца. Например, дата в январе будет отображаться как 1, дата в феврале — как 2 и т. Д.
- мм — это также покажет номер месяца, но также обеспечит отображение двух цифр. Например, дата в январе будет отображаться как 01, дата в феврале — как 02, а дата в ноябре — как 11.
- М-м-м — это отобразит название месяца в виде трехбуквенного кода. Например, дата в январе будет отображаться как январь, дата в августе — как август и т. Д.
- мммм — это вариант, который мы использовали в приведенном выше примере, и он будет показывать полное название месяца
- ммммм — эта опция показывает только первый алфавит названия месяца. Например, январь отображается как J, а февраль — как F и так далее. Я никогда не видел, чтобы это использовалось, потому что это сбивает с толку, поскольку январь также показывает J, а июль также показывает J
ТЕКСТ Функция
Функция ТЕКСТ позволяет нам преобразовать дату в любой допустимый формат, который мы хотим, и выдает результат в виде текстовой строки.
Например, мы можем использовать функцию ТЕКСТ, чтобы показать название месяца с даты.
Теперь, если вам интересно, чем оно отличается от пользовательского форматирования чисел, который мы использовали ранее, большая разница здесь в том, что с помощью функции ТЕКСТ вы можете комбинировать результат с другими функциями или текстовыми строками.
Не волнуйтесь, если вы немного заблудились, следующие несколько примеров прояснят это.
Предположим, у вас есть набор данных, как показано ниже, и вы хотите показать название месяца вместо полной даты.
Ниже приведена формула ТЕКСТ, которая даст вам название месяца:
= ТЕКСТ (A2; «мммм»)
Приведенная выше текстовая формула принимает дату в качестве входных данных и применяет к ней указанный формат (который в этой формуле равен «мммм»).
В отличие от метода пользовательского форматирования чисел, когда вы используете функцию ТЕКСТ, результатом является текстовое значение. Это означает, что вы не можете использовать результат как дату или число в вычислениях.
Но польза от использования функции ТЕКСТ заключается в том, что вы можете комбинировать результат функции с другими текстовыми строками.
Позвольте мне объяснить на примере.
Предположим, у вас есть тот же набор данных, и в этом случае вместо названия месяца вы хотите получить название месяца, за которым следует номер квартала (например, январь — квартал 1).
Приведенная ниже формула сделает это за вас:
= ТЕКСТ (A2; «мммм») & «- Квартал» & ОКРУГЛЕНИЕ ВВЕРХ (МЕСЯЦ (A2) / 3,0)
В приведенной выше формуле используются ROUNDUP и функция MONTH для получения номера квартала календарного года, а затем он комбинируется с названием месяца, которое задается функцией TEXT.
Поскольку результатом функции ТЕКСТ является текстовая строка, я могу комбинировать ее с другими текстовыми строками или результатами формулы.
ВЫБРАТЬ функцию
Еще одна формула, которую вы можете использовать для быстрого получения названия месяца из номера месяца, — это использование формулы ВЫБРАТЬ.
Хотя в конечном итоге это длинная формула, метод выбора формулы полезен, когда вы хотите получить результат, который нельзя получить с помощью пользовательского форматирования чисел или текстовой функции.
Например, если вы хотите вернуть пользовательские названия месяцев (например, названия месяцев на любом другом языке или только пять алфавитов для каждого названия месяца), вы можете сделать это с помощью формулы ВЫБРАТЬ.
Предположим, у вас есть набор данных, показанный ниже, и вы хотите получить название месяца для каждой из этих дат.
Ниже приведена формула, которая сделает это:
= ВЫБРАТЬ (МЕСЯЦ (A2), «Янв», «Фев», «Мар», «Апр», «Май», «Июнь», «Июль», «Авг», «Сен», «Октябрь», «Ноябрь» «,» Декабрь «)
Формула CHOOSE принимает номер индекса (который задается формулой месяца в нашем примере), а затем использует этот номер индекса, чтобы решить, какое значение вернуть.
В этом примере я сохранил названия месяцев в виде стандартных трехбуквенных названий, но вы можете изменить это и использовать любое имя, какое захотите.
Использование Power Query
Я начал гораздо больше использовать Power Query в своей работе, так как считаю, что с его помощью намного проще очищать данные.
Он имеет встроенную функцию, которая позволяет быстро преобразовать дату в название месяца.
Реальная ценность использования Power Query в таком сценарии будет заключаться в том, что вы импортируете данные из других файлов Excel (или объединяете данные из нескольких файлов Excel в один файл), и при этом вы хотите преобразовать свои даты в названия месяцев. .
Вы можете посетить мой курс Power Query на YouTube, если хотите узнать о нем больше.
Чтобы этот метод работал, ваши данные должны быть в таблице Excel. Хотя вы все еще можете использовать Power Query с именованными диапазонами, если ваши данные не находятся в таблице Excel, я рекомендую вам использовать любой из вышеперечисленных методов.
Предположим, у вас есть данные, указанные ниже, и вы хотите преобразовать эти даты в названия месяцев.
Ниже приведены шаги для этого:
- Выберите любую ячейку в наборе данных
- Перейдите на вкладку «Данные».
- На вкладке «Получить и преобразовать данные» нажмите «Из таблицы / диапазона».
- В открывшемся редакторе Power Query щелкните правой кнопкой мыши заголовок столбца Date.
- Перейдите в меню «Преобразование» >> «Месяц» >> «Название месяца».
- Нажмите «Закрыть и загрузить».
Вышеупомянутые шаги вставят новый лист и предоставят вам результирующую таблицу на этом новом листе.
Теперь, если вам интересно, зачем все это делать, если вы можете просто использовать произвольное форматирование чисел или текстовую функцию, вам необходимо понять реальную ценность Power Query, пытающегося автоматизировать работу.
Если вам нужно название месяца с даты только один раз, не стесняйтесь использовать методы, показанные выше.
Но если вы уже используете Power Query для управления данными из нескольких разных источников или объединенных файлов или листов, то знание того, что вы можете легко получить название месяца по дате, может сэкономить вам много времени.
Итак, это некоторые из методов, которые вы можете использовать для быстрого получения названия месяца по дате в Excel.
Надеюсь, вы нашли этот урок полезным.
Вам также могут понравиться другие руководства по Excel:
Функции и формулы для преобразования номера в название месяца
Время от времени возникает необходимость изменить порядковый номер месяца на название и наоборот (например, 3 на «Март» или «Октябрь» на 10).
Сначала рассмотрим, каким способом изменить номер месяца его название. А после речь пойдет о том, как выполнить обратный процесс, то есть изменить название на соответствующий ему порядковый номер.
Изменение порядкового номера месяца на его название в Excel
Первый способ, который пришел мне на ум, это просто составить список ячеек с последовательными названиями месяцев, а затем использовать функцию:
где $A$2:$A$13 – это, конечно, диапазон с названиями месяцев, а B2 — ячейка с порядковым номером в году, для которого мы ищем его название.
По схожему принципу с функцией ИНДЕКС работает другая функция, которую вы можете использовать — ВЫБОР. Разница состоит в том, что она не использует ячейки листа, в которых хранятся названия месяцев. Названия месяцев просто даем в качестве аргумента функции.
Функция ВЫБОР принимает в качестве аргументов номер индекса (который определяет какой из элементов в списке значений вернуть в качестве результата) и список значений. Интересующиеся подробностями, конечно же, могут скачать файл с примерами.
Если в качестве списка значений указать названия месяцев (по очередности), а в качестве индекса — ячейку с номером месяца, то функция вернет соответствующее ему название для каждого заданного числа.
Вариант2 автономный и более удобный для использования формулы на разных листах или рабочих книга без применения внешних ссылок.
Примеры как получить название месяца из даты в Excel
Подобно тому как показано выше, в случае, когда необходимо «вытянуть» название месяца из даты, необходимо воспользоваться помощью функции МЕСЯЦ.
Если у вас есть список с датами и нужно «вытянуть» из них название месяцев, то функция ИНДЕКС в приведенном выше виде выдаст ошибку. Если вы хотите, чтобы формула работала правильно, необходимо при помощи функции МЕСЯЦ «вытянуть» номер месяца из ячейки с датой. Формула будет выглядеть следующим образом:
Еще один способ (возможно, самый удобный) — использовать функцию ТЕКСТ (все, что делает функция ТЕКСТ, это преобразует числовое значение в текст с соответствующим форматированием).
Если у вас есть список с датами и нужно «вытянуть» из них название месяцев, просто введите:
где E2 – это, конечно, ячейка с датой.
Если вы работаете только с числами (1-12), функция в приведенной выше форме будет работать неправильно. Вы должны помнить, что число 1 для Excelя это 1 января 1900 года, число 2 — 2 января 1900 года и так далее. Это все тот же месяц — январь. Поэтому ТЕКСТ(E2;»ММММ») возвращает «Январь» для каждого номера месяца.
Однако вы можете легко изменить последовательные числа на номера месяцев. Например, вы можете умножить каждое число на среднее количество дней в месяце (30).
будет работать правильно, и в качестве результата вернет названия месяцев.
Как преобразовать название месяца в порядковый номер
Теперь будем преобразовывать обратно – имя месяца в соответствующий ему порядковый номер в году.
Пример1. Формула первая:
Значение Декабрь1 Excel автоматически преобразует в дату дек.01. Далее функция ДАТАЗНАЧ преобразует дату в число после с помощью функции МЕСЯЦ мы получаем порядковый номер в году для текущего названия месяца. По сути формулу можно упростить:
Пример2. Формула вторая:
Пример3. Формула третья:
Наиболее оптимальным вариантом является лаконичная формула =МЕСЯЦ(A2&»1″). В ней не нужно резервировать названия месяцев и присваивать им соответственные порядковые номера. К тому же в ней используется всего лишь одна функция.
Преобразование названия месяца в число — Excel и Google Таблицы
Если название месяца хранится в виде текста, вы можете использовать функции MONTH и DATEVALUE для преобразования названия месяца, но если месяц не сохраняется в виде текста, вам нужно только настроить форматирование.
От названия месяца к числу — форматирование
Если ячейка, содержащая название месяца, выровнена по правому краю, тогда ячейка может содержать дату (хранящуюся как серийный номер) с форматированием номера ячейки, установленным на месяц:
Просто измените формат даты с MMM (сокращенное имя) или MMMM (полное имя) на M (номер месяца, без нуля в начале) или MM (номер месяца с нулем в начале).
Вы можете изменить формат даты в меню форматирования ячеек:
Введите «M» или «MM» в поле «Тип».
Для доступа к меню форматирования ячеек используйте сочетание клавиш CTRL + 1 или нажмите эту кнопку:
От названия месяца к номеру — Функции
Если ваше название месяца хранится в виде текста, вы можете использовать эту формулу, содержащую МЕСЯЦ и ДАТУ, чтобы вычислить номер месяца:
Преобразование названия месяца в число в Google Таблицах
Все приведенные выше примеры работают в Google Таблицах точно так же, как и в Excel.