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

Обновлено: 23.11.2024

В этом учебном пособии объясняются основы функций МЕСЯЦ и КОНМЕСЯЦ в 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 (ссылки вы найдете ниже). Я благодарю вас за чтение и надеюсь увидеть вас на следующей неделе!

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Еще. Меньше

Когда вы вводите какой-либо текст в ячейку, например "2/2", Excel предполагает, что это дата, и форматирует ее в соответствии с настройкой даты по умолчанию на панели управления. Excel может отформатировать его как «2 февраля». Если вы измените настройку даты в панели управления, формат даты по умолчанию в Excel изменится соответствующим образом. Если вам не нравится формат даты по умолчанию, вы можете выбрать другой формат даты в Excel, например «2 февраля 2012 г.» или «2/2/12». Вы также можете создать свой собственный формат в настольном Excel.

Выполните следующие действия:

Выберите ячейки, которые хотите отформатировать.

В поле "Формат ячеек" перейдите на вкладку "Число".

В списке категорий нажмите Дата.

В разделе "Тип" выберите формат даты. Ваш формат будет предварительно просмотрен в поле «Образец» с первой датой в ваших данных.

Примечание. Форматы даты, начинающиеся со звездочки (*), изменятся, если вы измените региональные настройки даты и времени в панели управления. Форматы без звездочки не изменятся.

Если вы хотите использовать формат даты в соответствии с тем, как даты отображаются на другом языке, выберите язык в Locale (местоположение).

Если вы хотите использовать формат, которого нет в поле "Тип", вы можете создать свой собственный. Самый простой способ сделать это — начать с формата, близкого к тому, что вам нужно.

Выберите ячейки, которые хотите отформатировать.

В поле "Формат ячеек" перейдите на вкладку "Число".

В списке «Категория» нажмите «Дата», а затем выберите нужный формат даты в поле «Тип». Вы можете настроить этот формат на последнем шаге ниже.

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

В поле "Тип" внесите нужные изменения, используя код из таблицы ниже.

Используйте этот код

Месяцы с января по декабрь

Месяцы как первая буква месяца

Дни с воскресенья по субботу

Годы как 1900–9999

Если вы изменяете формат, включающий значения времени, и используете "m" сразу после кода "h" или "hh" или непосредственно перед кодом "ss", Excel отображает минуты вместо месяца.< /p>

Чтобы быстро ввести текущую дату на листе, выберите любую пустую ячейку, нажмите CTRL+; (точка с запятой), а затем при необходимости нажмите клавишу ВВОД.

Чтобы ввести дату, которая будет обновляться до текущей даты каждый раз, когда вы повторно открываете лист или пересчитываете формулу, введите =СЕГОДНЯ() в пустой ячейке и нажмите клавишу ВВОД.

Выполните следующие действия:

Выберите ячейки, которые хотите отформатировать.

Нажмите Control+1 или Command+1.

В поле "Формат ячеек" перейдите на вкладку "Число".

В списке категорий нажмите Дата.

Выберите ячейки, которые хотите отформатировать.

Нажмите Control+1 или Command+1.

В поле "Формат ячеек" перейдите на вкладку "Число".

В поле "Тип" внесите нужные изменения, используя код из таблицы ниже.

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

Функция заполнения

Откройте электронную таблицу в Microsoft Excel и найдите дату, которую вы хотите увеличить.

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

Перейдите на вкладку "Главная". Нажмите «Заполнить» в группе «Редактирование» и «Серии» в раскрывающемся меню.

Нажмите «Дата» в столбце «Тип» и «Месяц» в столбце «Единица даты». Введите «1» в поле «Значение шага».

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

Метод формулы

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

Введите "=ДАТА(ГОД(A1),МЕСЯЦ(A1)+1,ДЕНЬ(A1))" без кавычек в пустой ячейке, заменив "A1" ссылкой на ячейку, содержащую дату. В этом примере дата в ячейке "A1" увеличится на один месяц.

Добавьте знаки доллара к ссылке на ячейку, чтобы ссылка не изменилась при копировании формулы. В примере «=ДАТА(ГОД($A$1),МЕСЯЦ($A$1)+1,ДЕНЬ($A$1))» всегда будет ссылаться на ячейку «A1», даже если вы скопируете формулу в другую ячейку. В противном случае ссылка на ячейку изменится, чтобы отразить вашу новую позицию.

С. Тейлор начал профессиональную писательскую карьеру в 2009 году и часто пишет о технологиях, науке, бизнесе, финансах, боевых искусствах и природе. Он пишет как для онлайн-изданий, так и для офлайн-изданий, включая Journal of Asian Martial Arts, Samsung, Radio Shack, Motley Fool, Chron, Synonym и другие. Он получил степень магистра биологии дикой природы в Университете Клемсона и степень бакалавра искусств в области биологических наук в Колледже Чарльстона. У него также есть несовершеннолетние по статистике, физике и изобразительному искусству.

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

Поменять местами или поменять местами день и месяц даты с помощью настройки форматирования даты

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

<р>1. Выберите ячейки даты, дни и месяцы которых вы поменяете местами, а затем нажмите клавиши Ctrl + 1, чтобы открыть диалоговое окно «Формат ячеек».

<р>2. В диалоговом окне «Формат ячеек» нажмите «Пользовательский» в поле «Категория» на вкладке «Число». Если исходный формат даты — м/д/гггг, введите д/м/гггг в поле «Тип», а если исходный формат даты — д/м/гггг, введите м/д/гггг в поле «Тип» и наконец, нажмите кнопку ОК. Смотрите скриншот:

Теперь день и месяц меняются местами в выбранных ячейках даты.

Быстро поменять местами или поменять местами дату и месяц с помощью Kutools for Excel

В этом разделе вам будет рекомендована утилита «Применить форматирование даты» в Kutools for Excel. С помощью этой утилиты вы можете легко поменять местами день и месяц даты всего за несколько кликов.

Перед применением Kutools for Excel сначала загрузите и установите его.

<р>1. Выберите диапазон дат, который вам нужен, чтобы поменять местами день и месяц, затем нажмите Kutools > Формат > Применить форматирование даты. Смотрите скриншот:

<р>2. В диалоговом окне «Применить форматирование даты» укажите, например, 14/3/2001 или другой соответствующий формат даты в поле «Форматирование даты», а затем нажмите кнопку «ОК». Смотрите скриншот:

Затем вы можете увидеть, что все дни и месяцы выбранных дат сразу же меняются местами, как показано на снимке экрана ниже.

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

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