На примерах мы покажем, как получить месяц из даты в таблицах Excel, преобразовать число в его название и наоборот, и многое другое.
Думаю, вы уже знаете, что даты в Excel хранятся в виде порядковых номеров. Следовательно, нельзя явно увидеть ни день, ни месяц, ни год. Однако очень часто бывает крайне необходимо работать с ними по отдельности. Теперь посмотрим, как выбрать месяц из числа в виде числа или текста.
Функция МЕСЯЦ в Excel – синтаксис и использование
Microsoft Excel предоставляет специальную функцию MONTH () для извлечения месяца из даты. Возвращает порядковый номер в диапазоне от 1 (январь) до 12 (декабрь).
Функцию МЕСЯЦ () можно использовать во всех версиях Excel, и ее синтаксис максимально прост:
МЕСЯЦ (дата)
Чтобы эта функция работала правильно, аргумент должен быть введен с помощью функции ДАТА (год; месяц; день). Или обратитесь к ячейке, где написано правильно.
Например, формула = МЕСЯЦ (ДАТА (2021; 2; 5)) возвращает 2, поскольку ДАТА соответствует 5 февраля 2021 года.
Формула = МЕСЯЦ («1 марта 2020 г.») также хорошо работает. Дело в том, что если ввести такой текст в ячейку, Excel сразу распознает его как дату. То же самое происходит в формуле.
На практике гораздо удобнее ссылаться на ячейку с датой или возвращать ее с помощью какой-либо другой функции, чем записывать ее непосредственно в формулу. Например:
= МЕСЯЦ (A1) — возвращает месяц из ячейки A1.
= МЕСЯЦ (СЕГОДНЯ ()) — возвращает номер текущего месяца.
На первый взгляд функция МЕСЯЦ () в Excel может показаться простой. Но взгляните на приведенные ниже примеры, и вы удивитесь, сколько полезных вещей он может делать.
Как определить номер месяца по дате в Excel
Есть несколько способов преобразовать дату в месяц. Какой из них выбрать, зависит от типа результата, которого вы пытаетесь достичь.
- Функция МЕСЯЦ () — определяет номер месяца от даты.
Это наиболее очевидный и простой способ. Например:
- = МЕСЯЦ (A2) — возвращает месяц с даты в ячейке A2.
- = MONTH (DATE (2020,7,15)) — возвращает 7 за июль.
- = МЕСЯЦ («15 апреля 2020 г.») — очевидно, возвращает число.
2. Функция ТЕКСТ () — отображает месяц в виде текстовой строки.
Альтернативный способ получить номер месяца из даты Excel — использовать функцию ТЕКСТ:
- = ТЕКСТ (A2; «m») — возвращает число без нуля в начале, от 1 до 12.
- = ТЕКСТ (A2; «мм») — всегда возвращает двузначное число от 01 до 12.
Будьте очень осторожны при использовании текстовых функций, потому что они всегда возвращают порядковые номера месяцев как текстовые значения. И если вы собираетесь производить дальнейшие вычисления или использовать полученные числа в других формулах, вам лучше придерживаться функции МЕСЯЦ().
Или используйте простую математику, чтобы преобразовать текст в число:
= — ТЕКСТ (A2; «m»)
На следующем снимке экрана показаны результаты всех приведенных выше формул. Обратите внимание на выравнивание по правому краю чисел, возвращаемых функцией MONTH () (ячейки C2 и C3), в отличие от выравнивания по левому краю текстовых значений, возвращаемых функциями TEXT (ячейки 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 года.
Более гибкий способ – функции ВЫБОР, ИНДЕКС или ПЕРЕКЛЮЧ.
Для максимальной гибкости вы можете создавать свои собственные имена с помощью функции SELECT следующим образом:
= ВЫБОР (МЕСЯЦ (LA3); «янв»; «фев»; «мар»; «апр»; «май»; «июн»; «луг»; «назад»; «сет»; «окт»; «ноя» «;» Декабрь»)
Введите названия месяцев, которые вы хотите вернуть (по желанию), как значения в функции ВЫБОР после первого аргумента, который вводится как МЕСЯЦ ([дата]). Функция MONTH () получит число (число от 1 до 12), а SELECT будет использовать его для возврата n-го значения в списке.
Использование SELECT — более громоздкая формула. Но это более гибкое решение, потому что оно позволяет вам выводить любое значение, которое вы хотите, в качестве вывода (т.е вы можете использовать настраиваемые, сокращенные, не укороченные значения, в другом случае, на другом языке).
Например:
= ВЫБОР (МЕСЯЦ (A4); «Январь»; «Февраль»; «Март»; «Апрель»; «Май»; «Июнь»; «Июль»; «Август»; «Сентябрь»; «Октябрь»; «Ноябрь» «;»Декабрь»)
Еще один интересный вариант — использовать функцию ИНДЕКС. Опять же, мы используем массив имен в качестве аргумента, который мы можем настроить по мере необходимости.
= ИНДЕКС ({«Январь»; «Февраль»; «Март»; «Апрель»; «Май»; «Июнь»; «Июль»; «Август»; «Сентябрь»; «Октябрь»; «Ноябрь»; «Декабрь» «}; МЕСЯЦ (LA7))
И второй аргумент — порядковый номер месяца. Его можно указать явно или получить из функции МЕСЯЦ (). Значение будет извлечено из массива, порядковый номер которого был указан.
И, наконец, третий вариант — это функция ПЕРЕКЛЮЧАТЕЛЬ. Получите порядковый номер снова как числовое значение от 1 до 12.
А затем запишем следующую формулу:
= ПЕРЕКЛЮЧАТЕЛЬ (МЕСЯЦ (A3); 1; «ген»; 2; «фев»; 3; «мар»; 4; «апр»; 5; «май»; 6; «июн»; 7; «ушка»; 8; «Август»; 9; «Сентябрь»; 10; «Октябрь»; 11; «Ноябрь»; 12; «Декабрь»)
Как видите, и здесь каждому числу соответствует определенное значение. Мы показываем это в столбце B.
Как преобразовать название месяца в число
Давайте теперь рассмотрим противоположную проблему в логике. Есть две функции Excel, которые могут помочь вам преобразовать названия месяцев в числа: ДАТАЗНАЧ () и МЕСЯЦ (). Функция Excel DATEVALUE () преобразует дату, сохраненную в виде текста, в серийный номер, который Microsoft Excel распознает как дату. Затем функция МЕСЯЦ () извлекает из него номер месяца.
Полная формула выглядит так:
= МЕСЯЦ (ДАТА ЗНАЧЕНИЯ («1 -» & A2))
Где A2 — это ячейка, содержащая название месяца, который вы хотите преобразовать в число.
Дизайн
«1-«&
добавляется к значению ячейки, чтобы сообщить Excel, что это дата. Другими словами, если вы вручную войдете в ячейку, например,
1 февраля
или
1 февраля
тогда программа определит вашу информацию как 1 февраля текущего года, заполнит недостающее содержимое в ячейке и применит формат даты для его отображения.
То же самое происходит в формуле DATEVALUE, когда мы добавляем название месяца с тире к единице. Единственным важным условием здесь является то, что вы должны использовать именительный падеж, как это принято в Windows по умолчанию.
Однако даже первых трех букв месяца достаточно, чтобы не беспокоиться о падежах .
Это тоже работает.
Использование Power Query.
Надстройка Excel Power Query может помочь вам преобразовать дату в месяц и извлечь его номер или заголовок.
Итак, у нас есть список дат, и нам нужно извлечь из них месяц.
- Прежде всего, преобразуйте ваши данные в таблицу.
- Далее выберите одну из ячеек в таблице и перейдите на вкладку «Данные».
- Щелкните кнопку «Из таблицы».
Ваша таблица будет загружена в Power Query.
Здесь у вас есть два варианта: либо добавить новый столбец, который будет содержать желаемый результат, либо немедленно преобразовать ваши даты. Пропустите следующие два шага, если вы просто хотите преобразовать дату в месяц без добавления нового столбца.
- Щелкните правой кнопкой мыши заголовок столбца.
- Щелкните Дублировать».
- Щелкните правой кнопкой мыши заголовок нужного столбца.
- Перейдите в Конвертировать месяц Название месяца.
- Ваши даты будут преобразованы немедленно, как на скриншоте ниже.
- Еще одна вещь, которую имеет смысл сделать: щелкните правой кнопкой мыши заголовок столбца и переименуйте его в «Месяц».
- Теперь нажмите «Закрыть и загрузить», и ваши данные будут перенесены в электронную таблицу.
Профессионалы —
Ваши исходные данные ни в коем случае не будут затронуты.
Недостатки —
В вашей версии Excel должен быть установлен Power Query. Название месяца будет полным в именительном падеже.
Вот основные способы вывести месяц из даты в Excel. Надеюсь, эти советы были вам полезны.