На примерах мы покажем, как получить месяц из даты в таблицах Excel, преобразовать число в его название и наоборот, а также многое другое.
Думаю, вы уже знаете, что дата в Excel хранится в виде порядковых чисел. Поэтому в явном виде в ней нельзя увидеть ни день, ни месяц, ни год. Тем не менее, очень часто работать с ними по отдельности крайне необходимо. Сейчас мы рассмотрим, как выделить месяц из даты в виде числа или текста.
В этом руководстве вы узнаете:
Функция МЕСЯЦ в Excel – синтаксис и использование
Microsoft Excel предоставляет специальную функцию МЕСЯЦ() для извлечения месяца из даты. Она возвращает его порядковый номер в диапазоне от 1 (январь) до 12 (декабрь).
Функцию МЕСЯЦ() можно использовать во всех версиях Excel, и ее синтаксис настолько прост, насколько это возможно:
МЕСЯЦ(дата)
Для корректной работы этой функции аргумент следует вводить с помощью функции ДАТА(год; месяц; день). Либо ссылаться на ячейку, в которой она корректно записана.
Например, формула =МЕСЯЦ(ДАТА(2021;2;5)) возвращает 2, поскольку ДАТА представляет 5 февраля 2021 года.
Формула =МЕСЯЦ("1-Мар-2020") также работает нормально. Дело в том, что если вы введете такой текст в ячейку, то Excel сразу же распознает его как дату. То же самое происходит и в формуле.
На практике гораздо удобнее ссылаться на ячейку с датой или возвращать ее какой-либо другой функцией, чем прописывать прямо в формуле. Например:
=МЕСЯЦ(A1) - возвращает месяц из ячейки A1.
=МЕСЯЦ(СЕГОДНЯ()) - возвращает номер текущего месяца.
На первый взгляд функция МЕСЯЦ() в Excel может показаться простой. Но просмотрите приведенные ниже примеры, и вы удивитесь, узнав, сколько полезных вещей она может делать.
Как определить номер месяца по дате в Excel
Есть несколько способов перевести дату в месяц. Какой из них выбрать, зависит от того, какого именно результата вы пытаетесь достичь.
- Функция МЕСЯЦ() – определить номер месяца из даты.
Это наиболее очевидный и простой способ. Например:
- =МЕСЯЦ(A2) – возвращает месяц из даты в ячейке A2.
- =МЕСЯЦ(ДАТА(2020,7,15)) – возвращает число 7, соответствующее июлю.
- =МЕСЯЦ("15-Апр-2020") – очевидно, возвращает число.
2. Функция ТЕКСТ() – вывести месяц в виде текстовой строки.
Альтернативный способ получить номер месяца из даты Excel - использовать функцию ТЕКСТ:
- =ТЕКСТ(A2; "м") - возвращает номер без нуля в начале, от 1 до12.
- =ТЕКСТ(A2;"мм") - возвращает номер всегда из двух цифр, от 01 до 12.
Будьте очень внимательны при использовании текстовых функций, потому что они всегда возвращают порядковые номера месяцев в виде текстовых значений. И если вы планируете выполнить какие-то дальнейшие вычисления или использовать полученные числа в других формулах, вам лучше придерживаться функции МЕСЯЦ().
Или же при помощи несложного математического действия преобразуйте текст в число:
=--ТЕКСТ(A2; "м")
На следующем скриншоте показаны результаты всех приведенных выше формул. Обратите внимание на выравнивание по правому краю чисел, возвращаемых функцией МЕСЯЦ() (ячейки C2 и C3), в отличие от выравнивания по левому краю текстовых значений, возвращаемых функциями ТЕКСТ (ячейки C5 и C6).
Как получить название месяца из даты
Если вы хотите видеть название месяца, а не число, вы снова используете функцию ТЕКСТ, но с другим шаблоном формата:
- =ТЕКСТ(A2;"МММ") - возвращает сокращенное трёхбуквенное название, с «янв» по «дек».
- =ТЕКСТ(A2;"ММММ") - выводит полное название, с января по декабрь.
Если вы на просто хотите вывести название месяца вместо полной даты, тогда вам не нужны никакие формулы.
Выберите ячейку с датой, нажмите Ctrl + 1, чтобы открыть диалоговое окно «Формат ячеек». На вкладке «Число» выберите «Все форматы» и введите «МММ» или «ММММ» в поле «Тип», чтобы отобразить сокращенное или полное название соответственно. В этом случае ваши данные останутся полностью функциональными датами Excel, которые вы можете использовать в расчетах и других формулах. Дополнительные сведения об изменении формата см. в статье Создание пользовательского формата даты в Excel.
Автоматическое заполнение.
После того как вы отформатировали столбец значений даты в длинном формате, показанном выше, вы сможете вывести месяц в виде текстового значения, используя автоматическое заполнение.
Начните вводить несколько названий месяца в столбце рядом с отформатированными датами.
Excel угадает закономерность и выведет предлагаемое заполнение в светло-сером цвете. Вы можете нажать Enter, чтобы принять эти значения.
Таким образом, названия месяцев будут существовать как текстовые значения, а не только отформатированные даты.
Как преобразовать номер месяца в его название.
Использование форматирования
Предположим, у вас есть список чисел (от 1 до 12) на листе Excel, который вы хотите конвертировать в названия месяцев. Для этого можно использовать любую из следующих формул:
- Чтобы вернуть сокращенное название (янв – дек):
=ТЕКСТ(A2*28;"МММ")
=ТЕКСТ(ДАТА(2020;A4;1);"МММ")
- Чтобы вернуть полное имя (январь – декабрь):
=ТЕКСТ(A2*28;"ММММ")
=ТЕКСТ(ДАТА(2020;A4;1);"ММММ")
Во всех приведенных выше формулах A2 – это ячейка с номером. И единственная реальная разница между формулами – это код формата:
- «МММ» - трехбуквенное сокращение, например янв – дек,
- «ММММ» - название полностью.
3. Чтобы вернуть только первую букву:
=ТЕКСТ(ДАТА(2020;A4;1);"МММММ")
- «МММММ» - только первая буква названия.
Как работают эти формулы?
Excel считает число 1 днем 1 января 1900 года. Умножая 1, 2, 3 и т. д. на 28, вы получаете дни 28, 56, 84, и т. д. 1900 года, то есть какое-то число в январе, феврале, марте и т. д. Код формата «МММ» или «ММММ» выводит название месяца. А год для нас здесь совсем не важен.
Вторая же формула просто создает дату первого числа соответствующего месяца 2020 года.
Более гибкий способ – функции ВЫБОР, ИНДЕКС или ПЕРЕКЛЮЧ.
Для максимальной гибкости вы можете создавать свои собственные названия месяцев с помощью функции ВЫБОР следующим образом:
=ВЫБОР(МЕСЯЦ(A3);"янв";"фев";"мар";"апр";"май";"июн";"июл";"авг";"сен";"окт";"ноя";"дек")
Введите названия месяцев, которые вы хотите вернуть (настроенные по желанию) как значения в функции ВЫБОР, после первого аргумента, который вводится как МЕСЯЦ([дата]). Функция МЕСЯЦ() извлечет номер (число от 1 до 12), и ВЫБОР будет использовать его для возврата n-го значения в списке.
Использование ВЫБОР – это более громоздкая формула. Но это более гибкое решение, поскольку позволяет вывести как результат любые значения, которые вы пожелаете (т.е. вы можете использовать значения, которые являются настраиваемыми, сокращенными, не сокращенными, в другом падеже, на другом языке).
Вот, например:
=ВЫБОР(МЕСЯЦ(A4);"январь";"февраль";"Март";"апрель";"май";"июнь";"июль";"август";"сентябрь";"октябрь";"ноябрь";"декабрь")
Еще один интересный вариант – использование функции ИНДЕКС. Вновь у нас в качестве аргумента используется массив названий, которых мы можем настраивать так, как будет необходимо.
=ИНДЕКС({"январь";"февраль";"Март";"апрель";"май";"июнь";"июль";"август";"сентябрь";"октябрь";"ноябрь";"декабрь"};МЕСЯЦ(A7))
А второй аргумент – это порядковый номер месяца. Его можно указать явным образом либо получить функцией МЕСЯЦ(). Из массива будет извлечено значение, порядковый номер которого мы указали.
И, наконец, третий вариант – функция ПЕРЕКЛЮЧ. Вновь извлекаем порядковый номер как числовое значение от 1 до 12.
А затем пишем вот такую формулу:
=ПЕРЕКЛЮЧ(МЕСЯЦ(A3);1;"янв";2;"фев";3;"мар";4;"апр";5;"май";6;"июн";7;"июл";8;"авг";9;"сен";10;"окт";11;"ноя";12;"дек")
Как видите, здесь тоже каждому числу соответствует определённое значение. Его мы и выводим в столбце B.
Как преобразовать название месяца в число
Теперь рассмотрим противоположную по логике задачу. Существуют две функции Excel, которые могут помочь вам преобразовать названия месяцев в числа – ДАТАЗНАЧ() и МЕСЯЦ(). Функция Excel ДАТАЗНАЧ() преобразует дату, сохраненную в виде текста, в порядковый номер, который Microsoft Excel распознает как дату. Затем функция МЕСЯЦ() извлекает из неё номер месяца.
Полная формула выглядит следующим образом:
=МЕСЯЦ(ДАТАЗНАЧ("1-"&A2))
Где A2 – это ячейка, содержащая название месяца, которое вы хотите превратить в число.
Конструкция
"1-"&
добавляется к значению ячейки, чтобы дать понять Экселю, что это дата. Иначе говоря, если вы в ячейку руками введёте, к примеру,
1-февраль
или
1-фев
то программа определит вашу информацию как 1 февраля текущего года, дополнит недостающее содержимое в ячейке и применит формат даты для её отображения.
То же самое происходит и в формуле ДАТАЗНАЧ, когда мы к единице дописываем имя месяца с дефисом. Единственное важное условие здесь – нужно использовать именительный падеж, как это принято по умолчанию в Windows.
Однако, достаточно даже первых трёх букв месяца, чтобы не заморачиваться с падежами 😊.
Так тоже работает.
Использование Power Query.
Надстройка Excel Power Query может помочь вам перевести дату в месяц и извлекать его номер или название.
Итак, у нас есть список дат и нужно вытащить из них месяц.
- Прежде всего, преобразуйте ваши данные в таблицу.
- После этого выберите любую из ячеек в таблице и перейдите на вкладку «Данные».
- Нажмите кнопку «Из таблицы».
Ваша таблица будет загружена в Power Query.
Здесь у вас есть два варианта действий: либо добавить новый столбец, в который будет записан нужный результат, либо сразу преобразовать ваши даты. Пропустите следующие два шага, если вы просто хотите преобразовать дату в месяц без добавления нового столбца.
- Кликните правой кнопкой мыши заголовок столбца.
- Нажмите «Создать дубликат».
- Кликните по заголовку нужного столбца правой кнопкой мыши.
- Перейдите Преобразование ➜ Месяц ➜ Название месяца.
- Ваши даты тут же будут преобразованы, как на скриншоте ниже.
- Еще одна вещь, которую имеет смысл сделать: щелкните правой кнопкой мыши заголовок столбца и переименуйте его в «Месяц».
- Теперь нажмите «Закрыть и загрузить», и ваши данные будут перенесены в рабочий лист.
Плюсы —
Ваши исходные данные ни при каком раскладе не пострадают.
Минусы —
У вас должен быть установлен Power Query в вашей версии Excel. Название месяца будет полным, в именительном падеже.
Вот основные способы, как можно в Excel вывести месяц из даты. Надеюсь, эти советы были вам полезны.
