Как сделать месяц в Excel

Обновлено: 21.11.2024

Колетта Теске — журналист Lifewire, специализирующийся на бытовой электронике. У нее 30-летний опыт написания статей для Adobe, Boeing, Microsoft и многих других компаний.

Что нужно знать

Синтаксис функции МЕСЯЦ: МЕСЯЦ(серийный_номер). Serial_number — это дата, из которой вы хотите извлечь месяц.
Выберите ячейку, в которой будет отображаться серийный номер, затем перейдите в строку формул, введите =месяц и дважды щелкните МЕСЯЦ.
Выберите дату, из которой вы хотите извлечь серийный номер месяца, введите закрывающую скобку и нажмите Enter.

Функция Excel МЕСЯЦ извлекает месяц из списка дат. Месяц отображается как порядковый номер от 1 до 12. Если вы хотите преобразовать это число в текст, создайте именованный диапазон. Вот как использовать функцию МЕСЯЦ в Excel, чтобы получить номер месяца из даты и превратить его в название месяца.

Инструкции в этой статье относятся к Excel для Microsoft 365, Excel 2019, Excel 2016 и Excel 2013.

Синтаксис функции МЕСЯЦ

Функция МЕСЯЦ в Excel возвращает число от 1 до 12. Это число соответствует месяцу даты в выбранной ячейке или диапазоне.

Дата должна быть правильно введена с помощью функции ДАТА в Excel.

Синтаксис функции МЕСЯЦ: МЕСЯЦ(серийный_номер)

Серийный_номер — это дата, из которой вы хотите извлечь месяц, и она должна быть допустимой датой Excel.

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

Если ваш рабочий лист Excel содержит столбец дат в формате даты, распознаваемом Excel, используйте функцию МЕСЯЦ, чтобы извлечь порядковый номер месяца и поместить порядковый номер в отдельный столбец.

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

Перейдите к строке формул и введите =месяц. По мере ввода Excel предлагает функцию.

Дважды щелкните МЕСЯЦ.

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

Введите закрывающую скобку и нажмите Enter.

Результат появится в выбранной ячейке.

Чтобы применить формулу к другим датам в столбце, выберите ячейку, содержащую функцию МЕСЯЦ, затем перетащите маркер заполнения в нижнюю часть столбца.

Порядковые номера дат отображаются в выделенных ячейках.

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

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

Создать именованный диапазон

Первым шагом для преобразования серийного номера в название месяца является создание диапазона. Этот диапазон содержит номер и соответствующий месяц.

Данные для именованного диапазона могут находиться на том же листе или на другом листе книги.

Выберите ячейку, введите 1, затем нажмите Enter, чтобы перейти к ячейке ниже.

Введите 2.

Выберите обе ячейки.

Перетащите маркер заполнения, пока число 12 не отобразится рядом с маркером заполнения.

Выберите ячейку справа от цифры 1 и введите январь. Или введите нужный формат для месяца. Например, используйте Ян для января.

Перетащите маркер заполнения вниз, пока слово "декабрь" не отобразится рядом с маркером заполнения.

Выберите ячейки серийного номера и названия месяца.

Перейдите в поле "Имя" и введите имя диапазона.

Нажмите Enter, чтобы создать именованный диапазон.

Преобразовать число в текст

Следующий шаг — выбрать столбец, в который вы хотите вставить текстовую версию месяца.

Выберите ячейку рядом с первым порядковым номером в столбце.

Введите =vlookup. По мере ввода Excel предлагает возможные функции. Дважды щелкните ВПР.

Выберите первый серийный номер в столбце, затем введите запятую.

Введите именованный диапазон, затем введите запятую.

Введите номер столбца в именованном диапазоне, который вы хотите отобразить, введите закрывающую скобку и нажмите Enter.

Функция МЕСЯЦ Excel извлекает месяц из заданной даты как число от 1 до 12. Вы можете использовать функцию МЕСЯЦ, чтобы извлечь номер месяца из даты в ячейку или передать номер месяца в другую функцию, например Функция ДАТА.

Функция МЕСЯЦ извлекает месяц из заданной даты в виде числа от 1 до 12. Например, учитывая дату "12 июня 2021 г.", функция МЕСЯЦ вернет 6 для июня. МЕСЯЦ принимает только один аргумент, серийный_номер, который должен быть допустимой датой Excel.

Даты можно передавать в функцию МЕСЯЦ в виде текста (например, "13 августа 2021") или в виде собственных дат Excel, которые представляют собой большие порядковые числа. Чтобы создать значение даты с нуля с отдельными входными данными для года, месяца и дня, используйте функцию ДАТА.

Функция МЕСЯЦ будет "сбрасываться" каждые 12 месяцев (как календарь). Чтобы работать с продолжительностью месяцев больше 12, используйте формулу для расчета месяцев между датами.

Функция МЕСЯЦ возвращает число. Если вам нужно название месяца, см. этот пример.

Примеры

Чтобы использовать функцию МЕСЯЦ, укажите дату:

Для даты "15 сентября 2017 г." в ячейке A1 функция МЕСЯЦ возвращает 9:

Вы можете использовать функцию МЕСЯЦ, чтобы извлечь номер месяца из даты в ячейку или ввести номер месяца в другую функцию, например функцию ДАТА. Приведенная ниже формула извлекает месяц из даты в ячейке A1 и использует функции СЕГОДНЯ и ДАТА для создания даты в первый день того же месяца в текущем году.

См. ниже дополнительные примеры формул, использующих функцию МЕСЯЦ.

Примечание. Даты – это порядковые номера в Excel, начинающиеся с 1 января 1900 года. Даты до 1900 года не поддерживаются. Чтобы значения дат отображались в удобном для человека формате, примените числовой формат по вашему выбору.

В этом руководстве рассказывается, как использовать функцию автозаполнения Microsoft Excel для заполнения столбца или строки электронной таблицы названиями месяцев или лет.

Мы предоставляем подробные примеры листов как для последовательных, так и для непоследовательных шаблонов, а также предлагаем альтернативные методы автозаполнения.

Обучающие разделы

Как автоматически заполнять последовательные имена MONTH

► Пример: январь, февраль, март .

Автозаполнение Excel для месяцев в году очень просто. Наш пример взят из рабочего листа, в котором месяцы года указаны в качестве заголовков столбцов.

Введите название месяца, с которого начинается ряд.
Наведите указатель мыши на правый нижний угол ячейки, пока не появится маркер заполнения.
Удерживая нажатой ЛЕВУЮ кнопку мыши, перетащите курсор, чтобы выбрать ячейки для автозаполнения. Ячейки заполняются при отпускании мыши.

Другие методы: вместо использования ЛЕВОЙ кнопки мыши после ввода первой даты перетащите маркер заполнения с помощью ПРАВОЙ кнопки мыши , а затем выберите «Заполнить месяцы» в появившемся меню.

Как автоматически заполнять имена MONTH любым значением шага

► Пример: январь, март, май .

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

Примечание. Вы также можете перетащить маркер заполнения, удерживая нажатой ПРАВУЮ кнопку мыши, и выбрать «Заполнить месяцы» в контекстном меню маркера заполнения.

Как автоматически заполнять последовательные годы

► Пример: 2018, 2019, 2020, .

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

Необходимо ввести первые два года, чтобы Excel мог определить закономерность. Если введен только один год, Excel заполнит все ячейки только этим годом.

Введите первые два года серии и нажмите Enter.
Выберите эти две ячейки и наведите курсор на нижний правый угол второй ячейки, пока не появится маркер заполнения.
Нажмите ЛЕВУЮ кнопку мыши и перетащите маркер заполнения, чтобы заполнить ячейки, которые вы хотите заполнить автоматически.

Другие методы. Вы также можете перетащить маркер заполнения, удерживая нажатой ПРАВУЮ кнопку мыши, и выбрать линейный ряд в появившемся меню. (См. наше подробное руководство по автозаполнению любого линейного ряда в Microsoft Excel.)

Как автоматически заполнять непоследовательные годы

► Пример: 2018, 2022, 2026 .

Заголовки столбцов в этом образце электронной таблицы содержат каждый четвертый год, поэтому необходимо ввести первые два года, чтобы Excel мог определить значение шага.

В этом учебном пособии объясняются основы функций МЕСЯЦ и КОНМЕСЯЦ в Excel. Вы найдете множество примеров формул, демонстрирующих, как извлечь месяц из даты в Excel, получить первый и последний день месяца, преобразовать название месяца в число и многое другое.

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

Из этого руководства вы узнаете:

Функция МЕСЯЦ Excel — синтаксис и использование

Microsoft Excel предоставляет специальную функцию МЕСЯЦ для извлечения месяца из даты, которая возвращает номер месяца в диапазоне от 1 (январь) до 12 (декабрь).

Функция МЕСЯЦ может использоваться во всех версиях Excel 2016–2000, и ее синтаксис настолько прост, насколько это возможно:

Где серийный_номер — любая действительная дата месяца, который вы пытаетесь найти.

Для корректной работы формул Excel МЕСЯЦ дату следует вводить с помощью функции ДАТА(год, месяц, день). Например, формула =МЕСЯЦ(ДАТА(2015,3,1)) возвращает 3, поскольку ДАТА представляет собой 1-й день марта 2015 г.

Такие формулы, как =МЕСЯЦ("1 марта 2015"), также работают нормально, хотя в более сложных сценариях могут возникнуть проблемы, если даты вводятся в виде текста.

На практике вместо указания даты в функции МЕСЯЦ удобнее ссылаться на ячейку с датой или указывать дату, возвращенную какой-либо другой функцией. Например:

=МЕСЯЦ(A1) — возвращает месяц даты в ячейке A1.

=МЕСЯЦ(СЕГОДНЯ()) – возвращает номер текущего месяца.

На первый взгляд функция МЕСЯЦ Excel может показаться простой. Но просмотрите приведенные ниже примеры, и вы будете поражены, узнав, сколько полезных вещей он может сделать на самом деле.

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

Есть несколько способов получить месяц из даты в Excel. Какой из них выбрать, зависит от того, какого именно результата вы пытаетесь достичь.

Это наиболее очевидный и простой способ преобразования даты в месяц в Excel. Например:

=МЕСЯЦ(A2) — возвращает месяц даты в ячейке A2.
=МЕСЯЦ(ДАТА(2015,4,15)) — возвращает 4, соответствующие апрелю.
=MONTH("15-Apr-2015") - очевидно, также возвращает число 4.

Альтернативный способ получить номер месяца из даты Excel — использовать функцию ТЕКСТ:

=ТЕКСТ(A2, "m") — возвращает номер месяца без начального нуля в формате от 1 до 12.
=TEXT(A2,"mm") – возвращает номер месяца с начальным нулем, например, от 01 до 12.

На следующем снимке экрана показаны результаты, возвращаемые всеми приведенными выше формулами. Обратите внимание на выравнивание по правому краю чисел, возвращаемых функцией МЕСЯЦ (ячейки C2 и C3), в отличие от текстовых значений, выровненных по левому краю, возвращаемых функциями ТЕКСТ (ячейки C4 и C5).

Как извлечь название месяца из даты в Excel

Если вы хотите получить название месяца, а не число, вы снова используете функцию ТЕКСТ, но с другим кодом даты:

=ТЕКСТ(A2, "ммм") – возвращает сокращенное название месяца, например, январь – декабрь.
=TEXT(A2,"mmmm") — возвращает полное название месяца, например, с января по декабрь.

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

Выберите ячейку(и) с датами, нажмите Ctrl+1, чтобы открыть диалоговое окно Формат ячеек. На вкладке Число выберите «Пользовательский» и введите «ммм» или «мммм» в поле «Тип», чтобы отобразить сокращенное или полное название месяца соответственно. В этом случае ваши записи останутся полностью функциональными датами Excel, которые вы можете использовать в расчетах и других формулах. Дополнительные сведения об изменении формата даты см. в разделе Создание пользовательского формата даты в Excel.

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

Предположим, у вас есть список чисел (от 1 до 12) на листе Excel, который вы хотите преобразовать в названия месяцев. Для этого можно использовать любую из следующих формул:

=ТЕКСТ(ДАТА(2015, A2, 1), "мммм")

Во всех приведенных выше формулах A2 — это ячейка с номером месяца. И единственная реальная разница между формулами — это коды месяцев:

"ммм" – трехбуквенное обозначение месяца, например январь – декабрь.
"мммм" – месяц указан полностью.
"ммммм" – первая буква названия месяца.

Как работают эти формулы

При использовании вместе с кодами формата месяца, такими как "ммм" и "мммм", Excel считает число 1 днем 1 января 1900 года. Умножая 1, 2, 3 и т. д. на 28, вы получаете дни 28, 56. , 84 и т. д. 1900 года, которые приходятся на январь, февраль, март и т. д. Код формата "ммм" или "мммм" отображает только название месяца.

Как преобразовать название месяца в число в Excel

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

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

Где A2 в ячейке, содержащей название месяца, которое вы хотите преобразовать в число (&"1" добавляется для функции ДАТАЗНАЧ, чтобы понять, что это дата).

Как получить последний день месяца в Excel (функция КОНМЕСЯЦА)

Функция КОНМЕСЯЦА в Excel используется для возврата последнего дня месяца на основе указанной даты начала. Он имеет следующие аргументы, оба из которых обязательны:

Start_date – начальная дата или ссылка на ячейку с начальной датой.
Месяцы — количество месяцев до или после даты начала. Используйте положительное значение для будущих дат и отрицательное значение для прошлых дат.

Вот несколько примеров формул КОНМЕСЯЦА:

=КОНМЕСЯЦА(A2, 1) – возвращает последний день месяца через месяц после даты в ячейке A2.

=КОНМЕСЯЦА(A2, -1) – возвращает последний день месяца за один месяц до даты в ячейке A2.

Вместо ссылки на ячейку вы можете жестко указать дату в формуле КОНМЕСЯЦА. Например, обе приведенные ниже формулы возвращают последний день апреля.

Чтобы получить последний день текущего месяца, используйте функцию СЕГОДНЯ() в первом аргументе формулы КОНМЕСЯЦА, чтобы в качестве даты начала использовалась сегодняшняя дата. И вы помещаете 0 в аргумент месяца, потому что вы не хотите менять месяц в любом случае.

Примечание. Поскольку функция КОНМЕСЯЦА в Excel возвращает порядковый номер, представляющий дату, вам необходимо применить формат даты к ячейкам с вашими формулами. Подробные инструкции см. в разделе Как изменить формат даты в Excel.

А вот результаты, возвращаемые формулами КОНМЕСЯЦ в Excel, о которых говорилось выше:

Если вы хотите подсчитать, сколько дней осталось до конца текущего месяца, просто вычтите дату, возвращаемую функцией СЕГОДНЯ(), из даты, возвращаемой функцией КОНМЕСЯЦА, и применяйте к ячейке общий формат:

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

Как вы уже знаете, Microsoft Excel предоставляет только одну функцию для возврата последнего дня месяца (КОНМЕСЯЦ). Что касается первого дня месяца, то его можно получить несколькими способами.

Пример 1. Получить 1-й день месяца по номеру месяца

Если у вас есть номер месяца, используйте простую формулу DATE, например:

Например, =ДАТА(2015, 4, 1) вернет значение 1-апр-15.

Если ваши числа расположены в определенном столбце, например в столбце A, вы можете добавить ссылку на ячейку прямо в формулу:

=ДАТА(2015, B2, 1)

Пример 2. Получить 1-й день месяца из даты

Если вы хотите рассчитать первый день месяца на основе даты, вы можете снова использовать функцию Excel ДАТА, но на этот раз вам также понадобится функция МЕСЯЦ для извлечения номера месяца:

Например, следующая формула вернет первый день месяца на основе даты в ячейке A2:

=ДАТА(2015,МЕСЯЦ(A2),1)

Пример 3. Поиск первого дня месяца по текущей дате

Если ваши расчеты основаны на сегодняшней дате, используйте связь функций Excel КОНМЕСЯЦА и СЕГОДНЯ:

=EOMONTH(TODAY(),0) +1 – возвращает 1-й день следующего месяца.

Как вы помните, мы уже использовали аналогичную формулу КОНМЕСЯЦА для получения последнего дня текущего месяца. А теперь вы просто добавляете 1 к этой формуле, чтобы получить первый день следующего месяца.

Аналогичным образом вы можете получить первый день предыдущего и текущего месяца:

=EOMONTH(TODAY(),-2) +1 — возвращает 1-й день предыдущего месяца.

=EOMONTH(TODAY(),-1) +1 — возвращает 1-й день текущего месяца.

Вы также можете использовать функцию Excel ДАТА для решения этой задачи, хотя формулы будут немного длиннее. Например, угадайте, что делает следующая формула?

=ДАТА(ГОД(СЕГОДНЯ()), МЕСЯЦ(СЕГОДНЯ()), 1)

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

И как заставить его возвращать первый день следующего или предыдущего месяца? Руки вниз :) Просто добавьте или вычтите 1 из текущего месяца:

Чтобы вернуть первый день следующего месяца:

=ДАТА(ГОД(СЕГОДНЯ()), МЕСЯЦ(СЕГОДНЯ())+1, 1)

Чтобы вернуть первый день предыдущего месяца:

=ДАТА(ГОД(СЕГОДНЯ()), МЕСЯЦ(СЕГОДНЯ())-1, 1)

Как посчитать количество дней в месяце

В Microsoft Excel существует множество функций для работы с датами и временем. Однако в нем отсутствует функция подсчета количества дней в данном месяце. Итак, нам нужно восполнить это упущение с помощью наших собственных формул.

Пример 1. Чтобы получить количество дней на основе номера месяца

Если вы знаете номер месяца, следующая формула ДЕНЬ/ДАТА вернет количество дней в этом месяце:

В приведенной выше формуле функция ДАТА возвращает первый день следующего месяца, из которого вы вычитаете 1, чтобы получить последний день нужного месяца. Затем функция ДЕНЬ преобразует дату в номер дня.

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

=ДЕНЬ(ДАТА(2015, 4 +1, 1) -1)

Пример 2. Получение количества дней в месяце по дате

Если вы не знаете номер месяца, но знаете какую-либо дату в этом месяце, вы можете использовать функции ГОД и МЕСЯЦ, чтобы извлечь год и номер месяца из даты.Просто вставьте их в формулу ДЕНЬ/ДАТА, описанную в приведенном выше примере, и она расскажет вам, сколько дней содержит данный месяц:

=ДЕНЬ(ДАТА(ГОД(A2), МЕСЯЦ(A2) +1, 1) -1)

Где A2 – ячейка с датой.

В качестве альтернативы можно использовать гораздо более простую формулу ДЕНЬ/КОНМЕСЯЦ. Как вы помните, функция КОНМЕСЯЦА в Excel возвращает последний день месяца, поэтому никаких дополнительных вычислений не требуется:

На следующем снимке экрана показаны результаты, возвращаемые всеми формулами, и, как вы видите, они идентичны:

Как суммировать данные по месяцам в Excel

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

Самое простое решение — добавить вспомогательный столбец с простой формулой Excel МЕСЯЦ, которая будет преобразовывать даты в числа месяцев. Скажем, если ваши даты находятся в столбце A, вы используете =МЕСЯЦ(A2).

А теперь запишите список чисел (от 1 до 12 или только те номера месяцев, которые вас интересуют) в пустой столбец и просуммируйте значения для каждого месяца, используя формулу СУММЕСЛИ, подобную этой:< /p>

=СУММЕСЛИ(C2:C15, E2, B2:B15)

Где E2 – номер месяца.

На следующем снимке экрана показан результат вычислений:

Если вы не хотите добавлять вспомогательный столбец на лист Excel, не беда, вы можете обойтись и без него. Чуть более хитрая функция СУММПРОИЗВ сработает:

Где столбец A содержит даты, столбец B содержит значения для суммирования, а E2 — номер месяца.

Примечание. Имейте в виду, что оба приведенных выше решения суммируют все значения за данный месяц независимо от года. Таким образом, если на вашем листе Excel есть данные за несколько лет, все они будут суммированы.

Как условно форматировать даты по месяцам

Теперь, когда вы знаете, как использовать функции Excel МЕСЯЦ и КОНМЕСЯЦА для выполнения различных вычислений на листах, вы можете сделать еще один шаг и улучшить визуальное представление. Для этого воспользуемся возможностями условного форматирования дат в Excel.

В дополнение к примерам, представленным в вышеупомянутой статье, сейчас я покажу вам, как можно быстро выделить все ячейки или целые строки, относящиеся к определенному месяцу.

Пример 1. Выделение дат в текущем месяце

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

Во-первых, вы извлекаете номера месяцев из дат в столбце A, используя простейшую формулу =МЕСЯЦ($A2). Затем вы сравниваете эти числа с текущим месяцем, возвращаемым функцией =МЕСЯЦ(СЕГОДНЯ()). В результате у вас есть следующая формула, которая возвращает TRUE, если номера месяцев совпадают, и FALSE в противном случае:

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

Пример 2. Выделение дат по месяцам и дням

А вот еще одна задача. Предположим, вы хотите выделить основные праздники на листе независимо от года. Скажем, в дни Рождества и Нового года. Как бы вы подошли к этой задаче?

Просто используйте функцию Excel ДЕНЬ, чтобы извлечь день месяца (1–31), и функцию МЕСЯЦ, чтобы получить номер месяца, а затем проверьте, равен ли ДЕНЬ 25 или 31, и если МЕСЯЦ равен равно 12:

=И(ИЛИ(ДЕНЬ($A2)=25, ДЕНЬ($A2)=31), МЕСЯЦ(A2)=12)

Вот как работает функция МЕСЯЦ в Excel. Он кажется гораздо более универсальным, чем кажется, да?

В паре следующих постов мы собираемся подсчитать недели и годы, и, надеюсь, вы узнаете еще несколько полезных приемов. Если вас интересуют более мелкие единицы времени, ознакомьтесь с предыдущими частями нашей серии статей о датах в Excel (ссылки вы найдете ниже). Я благодарю вас за чтение и надеюсь увидеть вас на следующей неделе!

Читайте также: