Сортировка по месяцам в Excel

Обновлено: 21.11.2024

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

Самый простой способ сделать это — добавить в таблицу новый столбец. Этот столбец будет называться описательно, например, «Месяц рождения» или просто «Месяц». Например, предположим, что у вас есть дни рождения людей в столбце B, вы можете добавить новый столбец в столбец C. Затем в этом столбце вы можете использовать функцию МЕСЯЦ следующим образом:

Эта конкретная формула будет находиться в ячейке C3, но аналогичные формулы будут использоваться в каждой ячейке столбца C. В результате столбец C будет содержать числа в диапазоне от 1 до 12, представляющие месяцы рождения людей. Теперь вы можете сортировать список на основе содержимого столбца C, в результате чего список будет отсортирован по месяцам.

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

Выберите ячейки в столбце B (при условии, что столбец B содержит даты рождения).
Нажмите Ctrl+Shift+F. Excel отображает диалоговое окно «Формат ячеек».
Убедитесь, что отображается вкладка "Число".
В списке "Категория" выберите "Пользовательский". (См. рис. 1.)

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

Рисунок 2. Диалоговое окно "Сортировка".

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

Есть еще один способ решить проблему. Это включает фактическое преобразование дат в текст (вместо внутренних серийных номеров) следующим образом:

Выберите ячейки, содержащие даты.
Нажмите Ctrl+Shift+F. Excel отображает диалоговое окно «Формат ячеек».
Убедитесь, что выбрана вкладка "Число".
В списке категорий выберите Дата.
В списке типов выберите 14 марта 2012 г.
Нажмите «ОК». Теперь ваши ячейки должны быть отформатированы так, чтобы отображалась длинная дата с первым месяцем.
Нажмите Ctrl+C. Это скопирует ячейки в буфер обмена.
Откройте Блокнот.
Нажмите Ctrl+V. Информация теперь вставляется в Блокнот, и даты должны оставаться длинными.
Выберите информацию, только что вставленную в Блокнот.
Нажмите Ctrl+C. Это скопирует информацию в буфер обмена.
Вернуться в Excel.
Нажмите Ctrl+Shift+F. Excel отображает диалоговое окно «Формат ячеек».
Убедитесь, что выбрана вкладка "Число".
В списке категорий выберите «Текст».
Нажмите "ОК".
Нажмите Ctrl + V.

Ваши даты теперь вставляются в Excel как настоящие текстовые записи, а не как даты. Это позволяет легко сортировать информацию по месяцу в дате.

Биография автора

На его счету более 50 научно-популярных книг и множество журнальных статей. Аллен Уайатт является всемирно признанным автором. Он является президентом Sharon Parq Associates, компании, предоставляющей компьютерные и издательские услуги. Узнайте больше об Аллене.

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

Встроенные в Excel параметры сортировки — это мощные и эффективные инструменты, но они не всегда работают правильно, когда речь идет о сортировке дат. Это руководство научит вас нескольким полезным приемам, позволяющим упорядочить Excel по дате осмысленным образом, не испортив ваши данные.

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

Упорядочить даты в хронологическом порядке в Excel очень просто. Вы просто используете стандартную опцию Сортировка по возрастанию:

Как сортировать по дате в Excel

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

Вот подробные шаги по сортировке данных в Excel по дате:

Вот оно!Записи отсортированы по дате, и все строки собраны вместе:

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

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

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

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

Совет. Если результат отображается в виде даты, а не числа, установите формат Общий для ячеек с формулами.

А теперь отсортируйте таблицу по столбцу Месяц. Для этого выберите номера месяцев (C2:C8), нажмите Сортировка и фильтр > Сортировать от меньшего к большему, а затем разверните выделение, когда Excel попросит вас сделать это. Если все сделано правильно, вы получите следующий результат:

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

Если названия месяцев вводятся в виде текста, отсортируйте их по пользовательскому списку, как описано в этом примере.

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

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

В этом случае пригодится функция Excel ТЕКСТ, которая может преобразовать дату в текстовую строку в указанном формате. Для наших целей подойдет код формата "mmdd" или "mm.dd".

С исходной датой в B2 формула принимает следующий вид:

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

Того же результата можно добиться, используя формулу DATE следующим образом:

Формула создает список дат, извлекая месяц и день из фактической даты в ячейке B2 и заменяя реальный год поддельным, в этом примере 2000, хотя вы можете указать любой. Идея состоит в том, чтобы иметь один и тот же год для всех дат, а затем отсортировать список дат в хронологическом порядке. Поскольку год один и тот же, даты будут отсортированы по месяцам и дням, а это именно то, что вы ищете.

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

Что касается сортировки по годам, проще всего расположить даты в хронологическом порядке с помощью сортировки Excel по возрастанию (Самые старые к новым).

Даты будут отсортированы по годам, затем по месяцам, а затем по дням, как показано на снимке экрана ниже.

Если вас по какой-то причине такое расположение не устраивает, вы можете добавить вспомогательный столбец с формулой ГОД, которая извлекает год из даты:

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

Совет. Если вы хотите отсортировать даты по дням без учета месяцев и лет, извлеките день с помощью функции ДЕНЬ, а затем выполните сортировку по столбцу День:

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

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

Для недели, которая начинается с воскресенья (1) по субботу (7), используется следующая формула:

Если ваша неделя начинается с понедельника (1) по воскресенье (7), вот правильный вариант:

Где A2 – ячейка, содержащая дату.

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

Если названия дней недели вводятся в виде текста, а не дат, используйте функцию пользовательской сортировки, как описано в следующем примере.

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

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

Выберите записи, которые вы хотите отсортировать по названию месяца.
На вкладке Данные в группе Сортировка и фильтр нажмите Сортировка.
В диалоговом окне Сортировка выполните следующие действия:
В разделе Столбец выберите имя столбца, содержащего названия месяцев.
В разделе Сортировать выберите "Значения ячеек".
В разделе Порядок выберите Пользовательский список.

Готово! Ваши данные отсортированы по названию месяца в хронологическом, а не в алфавитном порядке:

Совет. Для сортировки по названиям дней недели выберите либо полные названия (Воскресенье, Понедельник, Вторник, …), либо краткие названия ( Вс, Пн, Вт…) в диалоговом окне Пользовательские списки.

Как автоматически сортировать по дате в Excel

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

Лучший способ сделать это — использовать макрос. Ниже вы найдете несколько примеров кода для автоматической сортировки следующих данных по дате в хронологическом порядке.

Макрос 1: автоматическая сортировка при каждом изменении рабочего листа

Этот макрос выполняется всякий раз, когда в любом месте рабочего листа происходит изменение.

Предполагается, что ваши данные находятся в столбцах от A до C, а даты, по которым вы хотите отсортировать, находятся в столбце C, начиная с C2. Также предполагается, что строка 1 содержит заголовки (Header:=xlYes). Если ваши записи находятся в разных столбцах, внесите следующие изменения:

Измените ссылку A1 на верхнюю левую ячейку целевого диапазона (включая заголовки).
Замените ссылку C2 на самую верхнюю ячейку, содержащую дату.

Макрос 2: автоматическая сортировка при изменении определенного диапазона

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

Совет. Эти макросы можно использовать для автоматической сортировки по любому типу данных, а не только по датам. Наши примеры кодов сортируются в порядке возрастания. Если вы хотите отсортировать по убыванию, измените Order1:=xlAscending на Order1:=xlDescending.

Как добавить макрос на лист

Поскольку оба макроса запускаются автоматически при изменении рабочего листа, код следует вставить на лист, где вы хотите отсортировать данные (в данном примере — Лист 1). Вот как:

Нажмите Alt + F11, чтобы открыть редактор VBA.
В Проводнике проектов слева дважды щелкните лист, для которого нужно выполнить автоматическую сортировку.
Вставьте код в окно кода.

Автоматическая сортировка дат по формуле

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

=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$20, ПОИСКПОЗ(СТРОКИ($A$2:A2), СЧЁТЕСЛИ($A$2:$A$20, "

Где A2:A20 — исходные (несортированные) даты, включая несколько пустых ячеек для возможных новых записей.

Введите формулу в пустую ячейку рядом со столбцом с исходными датами (в данном примере C2) и одновременно нажмите клавиши Ctrl + Shift + Enter, чтобы завершить ее. Затем перетащите формулу в оставшиеся ячейки (в нашем случае C2:C20).

Совет. Чтобы новые добавленные даты сортировались автоматически, обязательно включите достаточное количество пустых ячеек в указанный диапазон. Например, наш список дат находится в диапазоне A2:A7, но мы подставляем $A$2:$A$20 в формулу и заполняем его в ячейках от C2 до C20. Функция ЕСЛИОШИБКА предотвращает ошибки в дополнительных ячейках, вместо этого возвращая пустую строку ("").

Сортировка Excel по дате не работает

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

Вот как сортировать по дате в Excel. Я благодарю вас за чтение и надеюсь увидеть вас в нашем блоге на следующей неделе!

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

<р>1. Выберите диапазон с его заголовком для сортировки (B1:B10) и на ленте выберите Главная > Сортировка и фильтр > Пользовательская сортировка.

<р>2. В окне «Сортировка» в разделе «Порядок» выберите «Пользовательский список…»

<р>3. На экране «Пользовательские списки» выберите «Январь», «Февраль», «Март» и т. д. из списка слева и нажмите «ОК».

<р>4. Теперь в окне «Сортировка» в разделе «Порядок» у вас есть пользовательский список по месяцам. Нажмите "ОК".

Наконец, все месяцы из столбца B отсортированы в хронологическом порядке.

Сортировка дат по месяцам

Допустим, у вас есть список дат в столбце B, и вы хотите отсортировать их по месяцам от самой старой до самой новой.

Для этого вам потребуется добавить один вспомогательный столбец.

<р>1. В ячейку C2 введите формулу:

МЕСЯЦ возвращает номер месяца на основе введенной даты. В этом случае значение C2 равно 2 (февраль).

<р>2. Поместите курсор в правый нижний угол ячейки C2, пока не появится крестик.

<р>3. Перетащите формулу в конец диапазона (C10).

<р>4. Теперь у вас есть номер месяца для каждой даты в столбце B, и вы можете сортировать оба столбца на основе столбца C.
Выберите диапазон с данными в столбце C (C1:C10). Затем на ленте выберите Главная > Сортировка и фильтр > Сортировать от меньшего к большему.

<р>5. Во всплывающем окне выберите «Расширить выбор» и нажмите «ОК».

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

Примечание. Даты сортируются только по месяцам, а не по годам, поскольку месяц является единственным условием сортировки в этом примере.

Сортировка по месяцам как текст в Google Sheets

Поскольку в Google Sheets нет функции, аналогичной пользовательским спискам Excel, вам придется использовать формулу массива для сортировки месяцев в виде текста.

Для этого введите следующую формулу в ячейку C2:

В этом случае функция МЕСЯЦ имеет в качестве входных данных диапазон, а не одно значение. Этот вариант вернет массив чисел от 1 до 12 (для каждого месяца), отсортированный по возрастанию. Поскольку функция SORT является самим массивом, она будет принимать значения из B2: B10 в виде массива и сортировать их соответствующим образом, начиная с ячейки D2 вниз. Таким образом, вы получите все месяцы из столбца B, отсортированные в столбце C от самого старого к самому новому.

Сортировка дат по месяцам в Google Таблицах

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

Логика здесь та же. Функция МЕСЯЦ будет сортировать даты в диапазоне от самой старой до самой новой, а функция СОРТИРОВКИ создаст новый массив в столбце C на основе этого списка.

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

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

Оглавление

Сортировать даты в хронологическом порядке

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

Для этого выберите список дат, которые вы хотите отсортировать. На вкладке Главная в Excel найдите группу Редактирование и нажмите Сортировка и фильтрация. В раскрывающемся списке выберите Сортировать от старых к новым.

Откроется диалоговое окно с двумя вариантами: Расширить выбор или Продолжить с текущим выбором.

Если у вас есть данные в соседних столбцах и вы хотите изменить порядок этих данных, выберите Расширить выбор, в противном случае выберите Продолжить с текущим выбором.. р>

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

Сортировка дат по месяцам

Допустим, вы готовите список дней рождения, чтобы устроить вечеринку с пиццей в любой день рождения сотрудника. Вечеринки с пиццей — это здорово, но отслеживать дни рождения каждый месяц может быть утомительно. Однако Excel может помочь.

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

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

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

Когда появится диалоговое окно, выберите Расширить выбор, и это должно отсортировать дни рождения ваших сотрудников по месяцам.

Обратите внимание, что этот метод полностью игнорирует компонент года при сортировке дат.

Сортировка дат по годам

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

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

Однако, если по какой-то причине вы хотите использовать формулу, вы можете использовать функцию ГОД следующим образом:

После того как ваш столбец YEAR будет готов, дальнейший процесс будет таким же. Используйте параметр Сортировать от меньшего к большему и выберите Расширить этот выбор при запросе Excel.

Это должно отсортировать ваши данные по годам.

Сортировка дат по месяцам и дням

Когда вы сортировали дни рождения своих сотрудников по месяцам, не заметили ли вы чего-то странного? У Джоша и Моники в декабре день рождения. Однако в отсортированном списке дата рождения Джоша была указана перед датой рождения Моники, хотя день рождения Моники на пять дней раньше дня рождения Джоша. Было бы ужасно, если бы мы забыли день рождения Моники и отпраздновали день рождения Джоша всего через пять дней, не так ли?

Это происходит из-за того, что при сортировке выходных данных функции МЕСЯЦ не учитывается составляющая дня в дате. Если в двух строках указан один и тот же месяц, Excel отсортирует их в порядке появления. Поскольку в столбце «МЕСЯЦ» Джоша и Моники указано «12», они были отсортированы по дате появления без учета дня.

Это можно исправить, отсортировав наши данные как по месяцу, так и по дню дат. Мы добьемся этого с помощью некоторой логики и функции ТЕКСТ, например:

Получив текстовые строки в формате mm.dd, используйте встроенную функцию сортировки. На этот раз вы заметите, что дата рождения Моники правильно указана перед датой рождения Джоша.

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

Итак, на основе столбца даты мы создали еще один вспомогательный столбец "Дата конвертации". Как только столбец будет готов, мы можем использовать встроенную в Excel функцию Сортировать от старых к новым и должны получить тот же результат, что и при использовании функции ТЕКСТ.

Сортировка дат по названиям месяцев или дней недели

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

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

Выберите месяцы, указанные на листе Excel, и перейдите к пункту Сортировка и фильтрация в группе Редактирование на вкладке Главная. Выберите Пользовательская сортировка и при появлении запроса выберите Расширить выбор.

Откроется диалоговое окно. В меню Сортировать по выберите столбец, содержащий месяцы. В правом конце вы увидите раскрывающееся меню в разделе Заказ. Разверните меню и выберите Пользовательские списки.

На левой панели, содержащей настраиваемые списки, выберите формат отображения месяцев (например, январь, февраль, март… или январь, февраль, март…). Дважды нажмите ОК. Вы увидите, что месяцы теперь отсортированы в хронологическом порядке.

Тот же метод работает, если вы хотите отсортировать данные по дням недели. Если у вас указаны дни недели вместо месяцев, единственное, что вам нужно сделать по-другому, — это выбрать соответствующий пользовательский список. В приведенном выше примере мы выбрали формат «Январь, февраль, март…», но для будних дней вам нужно выбрать формат «Воскресенье, понедельник, вторник…».

Сортировка Excel по дате не работает

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

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

Подпишитесь и станьте частью нашей семьи, насчитывающей более 15 000 человек!

Теперь подпишитесь на Excel Trick и получите бесплатную копию нашей электронной книги "200+ ярлыков Excel" (формат для печати), которая резко повысит вашу продуктивность.

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