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

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

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

Вот о чем мы поговорим:

Как сортировать даты по возрастанию 

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

  1. Выберите даты, которые вы хотите отсортировать в хронологическом порядке, по возрастанию.
  2. На вкладке «Данные» в группе  «Сортировка и фильтр»  выберите «Сортировать от старых к новым». 
как сделать сортировку по датам в excel

Как сортировать таблицу по дате в Excel

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

Вот подробные инструкции по сортировке в таблице Excel по дате:

  1. В столбце таблицы выберите все даты, не включая заголовок столбца.
  2. На вкладке «Данные»  выберите «Сортировать от старых к новым» .
сортировка дат в excel по порядку
  1. Появится диалоговое окно « Предупреждение о сортировке» . Оставьте выбранной по умолчанию опцию «Расширить выделенный диапазон»  и нажмите «Сортировать» :
как настроить сортировку по дате в excel

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

сортировка по дате рождения в excel

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

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

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

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

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

=МЕСЯЦ(B2)

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

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

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

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

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

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

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

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

В этом случае используем сортировку по пользовательскому списку. Такие списки с месяцами и днями недели уже есть в Excel, поэтому ничего создавать не нужно, просто использовать уже имеющееся. Итак, опишем процесс пошагово:

  1. Выберите записи, которые вы хотите отсортировать по названию месяца.
  2. На вкладке "Данные" в группе "Сортировка и фильтр" выберите кнопку "Сортировка".
  3. В появившемся диалоговом окне  выполните следующие действия:
    • В разделе "Столбец" выберите название столбца, в котором содержатся названия месяцев.
    • В разделе "Сортировка" выберите "Значения ячеек".
    • В разделе "Порядок" выберите "Настраиваемый список".
сортировка месяцев по списку
  1. В диалоговом окне "Списки" выберите либо полные названия месяцев (январь, февраль, март, ...), либо короткие имена (янв, февр, март...) в зависимости от того, как записаны они в вашей таблице:
сортировка дат в Excel при помощи списка
  1. Дважды нажмите кнопку "ОК", чтобы закрыть оба диалоговых окна.

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

Совет. Чтобы отсортировать дни рождения по названиям дней недели, которые также записаны словами, выберите либо полные имена (понедельник, вторник, среда ...), либо короткие имена (пн, вт, ср ...) в диалоговом окне «Списки».

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

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

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

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

=ТЕКСТ(B2; "mm.dd")

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

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

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

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

=ДАТА(2000;МЕСЯЦ(B2);ДЕНЬ(B2))

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

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

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

Мы уже рассмотрели этот подход во втором разделе этой статьи: Как сортировать по дате.

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

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

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

=ГОД(B2)

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

сортировка строк по дате в excel

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

=ДЕНЬ(B2)

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

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

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

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

=ДЕНЬНЕД(B2;2) 

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

В этом примере мы  получили такой результат:

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

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

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

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

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

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

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

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

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

  • Измените ссылку A1 на верхнюю левую ячейку целевого диапазона (включая заголовки).
  • Измените ссылку B2 на самую верхнюю ячейку, содержащую дату.
Private Sub Worksheet_Change(ByVal Target As Range)
  On Error Resume Next
  Range("A1").Sort Key1:=Range("B2"), _
    Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom
End Sub

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

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

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

Private Sub Worksheet_Change(ByVal Target As Range)
  On Error Resume Next
  If Not Intersect(Target, Range("B:B")) Is Nothing Then
    Range("A1").Sort Key1:=Range("B2"), _
    Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom
  End If
End Sub

Посмтрите, как работает автоматическая сортировка по дате:

автоматическая сортировка по дате в Excel при помощи VBA

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

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

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

Первый способ:

  1. Нажать Alt + F11 чтобы открыть редактор VBA.
  2. В обозревателе проектов слева дважды щелкните лист, на котором вы хотите выполнить автоматическую сортировку.
  3. Вставьте код в окно «Код».

Второй способ:

  1. Кликните правой кнопкой мыши на ярлыке листа, в который вы хотите вставить код.
  2. В появившемся меню выберите пункт Посмотреть код.
  3. Откроется редактор VBA с окном «Код» для выбранного вами листа.
  4. Вставьте код в это окно.

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

как вставить код VBA на лист Excel

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

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

=ЕСЛИОШИБКА(ИНДЕКС($A$2:$B$20; ПОИСКПОЗ(ЧСТРОК($B$2:B2); СЧЁТЕСЛИ($B$2:$B$20;"<="&$B$2:$B$20); 0);2); "")

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

A2:В20 – диапазон всех данных (имена и даты).

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

При помощи этой формулы мы извлекаем в нужном порядке даты из диапазона A2:B20. Поскольку даты записаны во втором столбце, используем аргумент 2 в формуле ИНДЕКС+ПОИСКПОЗ.

А теперь, если мы хотим еще отсортировать и имена из столбца А, просто изменим значение аргумента [номер столбца] с 2 на 1. Вот как это будет:

=ЕСЛИОШИБКА(ИНДЕКС($A$2:$B$20; ПОИСКПОЗ(ЧСТРОК($B$2:B2); СЧЁТЕСЛИ($B$2:$B$20;"<="&$B$2:$B$20); 0);1); "")

И запишем эту формулу в ячейку Е2, чтобы сохранить привычный порядок следования столбцов. Точно так же завершаем ввод при помощи Ctrl + Shift + Enter и копируем вниз по столбцу.

На скриншоте ниже вы видите результат.

сортировка таблицы по датам при помощи формулы Excel

Теперь при добавлении имени и даты в столбцы А и В новые отсортированные данные будут автоматически появляться в столбцах Е и F.

Функция ЕСЛИОШИБКА предотвращает появление сообщения об ошибках в дополнительных ячейках, возвращая вместо этого пустую строку ("").

Совет. Чтобы вновь добавленные даты сортировались автоматически, обязательно включите достаточное количество пустых ячеек в указанный диапазон. В нашей формуле мы “зарезервировали” 20 строк, вы можете увеличить это число насколько вам необходимо. 

Почему сортировка Excel по дате не работает?

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

Теперь сортировка по дате в Excel не будет для вас проблемой. 

Надеюсь, эта информация будет вам полезна.