Сортировка и фильтрация данных Excel по месяцам и дням

Вот как вы сортируете данные Excel по месяцам и дням и не учитываете годы.

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

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

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

= ТЕКСТ (A2; "MM.DT.")

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

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

Щелкните первую ячейку вспомогательного столбца.

В поле функции (f_Икс) формула: = ТЕКСТ (A2; «MM.DT.»).

Убедитесь, что функция обращается к правильной ячейке с правильными датами (в примере дата находится в ячейке B2).

Нажмите Enter или нажмите маленькую зеленую галочку, и все!

Затем скопируйте эту формулу достаточно глубоко во вспомогательный столбец.

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

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

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

= ТЕКСТ (A2; «ДД.ММ.»)

Осторожность! Формула выводит текст вместо чисел в Excel

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

Почему возникают эти проблемы при сортировке данных в Excel?

Проблема с этой задачей заключается в том, что Excel всегда учитывает год при сортировке дат. Итак, 31 декабря 2009 года будет до 1 августа 2010 года. Следовательно, вам понадобится вспомогательный столбец, в котором вы соответствующим образом разложите значения даты.

Другой подход помогает с проблемами сортировки в Excel

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

1. В ячейку C4 запишите следующую формулу:
   = МЕСЯЦ (B4) + ДЕНЬ (B4) / 100
   Эта формула связывает месяц и день с желаемым критерием сортировки.

   

2. Скопируйте формулу в ячейку C13.

   

3. Теперь отметьте область A3: C13.

   

4. Вызовите пункт «Сортировка» через меню «Данные» в разделе «Сортировка и фильтрация» и, следовательно, одноименное диалоговое окно.

   

5. В поле «Сортировать по» выберите запись «Сортировать по столбцу».

6. В поле «Сортировать по» выберите «Значения ячеек».“ фиксированный.

7. В поле «Заказ» выберите запись «По размеру (по возрастанию)» (см. рисунок).

   

8. Если вы теперь подтвердите диалоговое окно, щелкнув кнопку «ОК», список дней рождения будет отсортирован в порядке возрастания согласно вспомогательному столбцу C. Результат можно увидеть на следующем рисунке:

   

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

Функция МЕСЯЦ () используется для чтения месяца из значения даты.

Функция ДЕНЬ () также определяет день по значению даты.

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

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

Как фильтровать по одной дате в Excel

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

Автоматические фильтры уже установлены в списке (маленькие серые стрелки). Столбец C списка содержит значения дат для записей данных. Чтобы отфильтровать список так, чтобы отображались только записи, которые попадают в дату в ячейке D1, выполните следующие действия:

Отфильтровать одну дату из таблицы

1. Щелкните фильтр в столбце C (маленькая стрелка указывает вниз).

2. Excel до версии 2003: выберите запись «Определяется пользователем» из списка фильтров. Используйте настройку «Соответствует» из первого списка.

3. Excel 2007 или более поздняя версия: выберите запись «Равно» из списка фильтров в разделе «Фильтр даты».

   

4. Во всех версиях Excel введите желаемую дату за полем ввода «соответствует» (в примере - 13 мая 2009 г.).

   

5. Подтвердите свой выбор, нажав кнопку ОК.

6. Excel немедленно применит нужный фильтр. На следующем рисунке показан результат:

   

Как фильтровать списки Excel по одному году

Если у вас есть даты в списке, вы не сможете легко отфильтровать их по годам. Пользовательский фильтр «содержит 2011» не найдет дату с 2011 года. Это связано с тем, что Excel интерпретирует значения даты как непрерывные значения с 1 января 1900 года и отображает их только в той форме, с которой мы знакомы по форматированию.

Небольшая хитрость для фильтрации по годам в Excel в любом случае

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

Чтобы выполнить эту фильтрацию на основе 2011 года, выполните следующие действия:

1. Включите новый столбец «Год», введя текст «Год» в ячейку D3.

2. Введите следующую формулу в ячейку D4, чтобы вычислить год первой строки списка:
   = ГОД (A4)

   

3. Скопируйте формулу во все строки списка, чтобы найти все годы.

4. Щелкните любую ячейку в списке.

5. Используйте команду «Фильтр» в области «Данные», чтобы активировать автоматический фильтр для списка (маленькие стрелки).

   

6. Используйте символ автоматического фильтра в новом столбце «Год», чтобы выбрать запись «2011», и нажмите «ОК».

   

При использовании версий Excel до 2007 вы найдете команду для интеграции автоматического фильтра в разделах «Данные» и «Фильтр».

Как отсортировать и отфильтровать дату «с по» в Excel

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

Пример: фильтр в таблице Excel по определенной дате

Например, вы хотите отфильтровать период с 30 ноября 2022-2023 г. по 5 декабря 2022-2023 г. в показанной таблице.

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

1. Щелкните любую ячейку в списке.

2. Выберите команду «Фильтр» на вкладке «Данные» в группе «Сортировка и фильтр» на ленте.
   (Excel версии 2003: активируйте команду меню Данные> Фильтр> Автофильтр)

   

3. Значок автоматического фильтра отображается справа от заголовка столбца (маленькая стрелка). Щелкните значок рядом с «Дата».

   

4. В появившемся меню выберите команду «Фильтр даты», а затем функцию «Между …».

   

5. В первом списке выбора выберите «после или равно» и введите рядом с ним «30.11.2020». Во втором списке выберите «до или равно» и введите рядом с ним 12.05.2020. Используйте вариант «И».

   

6. Нажмите кнопку ОК, чтобы соответствующим образом отфильтровать список.

7. Результатом является список, ограниченный периодом с 30 ноября 2022-2023 года по 5 декабря 2022-2023 года. Все записи данных, которые не попадают в этот период, скрываются. Чтобы изменить критерии фильтрации на другой диапазон дат, снова вызовите команду «Автофильтр». Затем установите новые значения даты для нижнего и верхнего пределов.

   

Скриншоты были сделаны с помощью Excel версии 2016.

часто задаваемые вопросы

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

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

Сортировка по числам в Excel не работает. Что я могу сделать?

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

Excel не распознает дату. Каково решение?

Иногда Excel стоит напротив и просто не хочет распознавать и отображать введенную дату как таковую. Если ничего не работает, создайте новый столбец рядом со старым. Щелкните правой кнопкой мыши новый столбец и выберите «Форматировать ячейки …». В открывшемся меню выберите «Дата» и нажмите «ОК». Теперь выберите и скопируйте старый столбец, который не работал, и вставьте скопированные значения в новый столбец, используя функцию вставки «Значения (W)». Затем удалите старый столбец.