Как отфильтровать рабочие и выходные дни в Excel?
При анализе данных или учёте посещаемости часто возникает необходимость фильтровать выходные или рабочие дни из длинного списка в Excel — например, выделять только субботы и воскресенья для расчёта сверхурочных или сосредотачиваться на буднях при анализе трудовой активности. Когда даты отображаются в стандартном формате (например, 06/07/2012), не всегда сразу понятно, приходится ли конкретная дата на выходной или рабочий день, из-за чего ручная фильтрация становится медленной и чреватой ошибками. В этой статье представлены несколько практичных способов быстро определять и фильтровать рабочие и выходные дни: от встроенных инструментов Excel и полезных формул до автоматизации с помощью VBA и удобных надстроек, таких как Kutools для Excel. Освоив и применив эти приёмы, вы сможете оптимизировать рабочие процессы, сократить объём ручной сортировки и минимизировать риск ошибочной классификации дат при анализе.
➤ Фильтрация будних дней и выходных с помощью функций
➤ Фильтрация будних дней и выходных с помощью Kutools для Excel
➤ Фильтрация будних дней и выходной день с помощью утилиты Супер фильтр из Kutools для Excel
➤ Использование макроса VBA для автоматической фильтрации или выделения будних дней и выходных
➤ Фильтрация будних дней и выходных с помощью расширенного фильтра и пользовательских критериев
Фильтрация рабочих дней и выходных с помощью функций
Функция Microsoft Excel WEEKDAY возвращает порядковый номер от 1 до 7, соответствующий дню недели для заданной даты. По умолчанию (без второго аргумента) 1 обозначает воскресенье, а 7 — субботу. Например, если дата приходится на воскресенье, функция вернёт 1; если же это пятница, результат будет 6. Это позволяет легко определять выходные и рабочие дни и фильтровать их нужным образом.
Этот метод подходит для большинства стандартных таблиц, обеспечивает полный контроль, но требует наличия вспомогательного столбца.
Шаг 1:В столбце рядом с колонкой дат (например, в ячейке)C2) введите следующую формулу, чтобы получить индекс дня недели:
=WEEKDAY(B2)
Шаг 2: Нажмите Enter, затем перетащите маркер заполнения вниз, чтобы применить формулу ко всем строкам с датами. Теперь в каждой строке будет отображаться номер дня недели.
Шаг 3: Перейдите в меню Данные > Фильтр на ленте, чтобы включить фильтрацию. Excel автоматически выберет блок данных, если перед этим выбрать ячейку внутри набора данных.
Шаг 4: Щёлкните стрелку раскрывающегося списка 
, которая появится в заголовке каждого столбца. Нажмите стрелку фильтра в вспомогательном столбце (например, «День недели») и установите флажки для значений 1 и 7, чтобы отфильтровать выходные дни (воскресенье и субботу).
Шаг 5: Нажмите OK в меню фильтра. Excel отобразит только даты выходных, соответствующие вашему выбору (1 и 7).
Примечание: Если вы хотите отфильтровать только рабочие дни (с понедельника по пятницу), установите флажки для значений 2, 3, 4, 5 и 6.
Советы и напоминания:
- Если в вашем регионе неделя начинается с другого дня, используйте
=WEEKDAY(B2, 2), чтобы понедельник соответствовал значению 1, а воскресенье — 7. Настройте фильтры соответствующим образом. - Пустые ячейки в столбце с датами могут привести к неожиданным результатам — при необходимости очистите данные.
- Обязательно протяните формулу на все строки, чтобы фильтрация работала точно.
Фильтрация рабочих дней и выходных с помощью Kutools для Excel
Встроенный метод Excel обычно требует вспомогательного столбца для разделения рабочих дней и выходных. Альтернативно, Kutools для Excel предлагает более простое решение: вы можете напрямую отображать названия дней недели с помощью форматирования дат или преобразовывать их в обычный текст для упрощения фильтрации. Инструмент Применить формат даты в Kutools делает этот процесс быстрым и лёгким.
Kutools для Excel — включает более 300 незаменимых инструментов для Excel. Работайте в Excel быстрее, проще и эффективнее.Скачать сейчас!
Шаг 1: Выделите диапазон дат, который необходимо преобразовать.
Шаг 2: Перейдите на вкладку Kutools, нажмите Формат > Применить формат даты. Появится диалоговое окно.
Шаг 3: В диалоговом окне выберите формат Wed или Wednesday из списка Формат даты, чтобы отображать названия дней недели.
Шаг 4: Нажмите OK. Выбранные даты теперь отображаются как названия дней недели. Если вы хотите фильтровать их как текст, а не как даты, нажмите Kutools > Преобразовать в фактический текст, чтобы преобразовать отформатированные результаты в статический текст.
Шаг 5: Перейдите в меню Данные > Фильтр. В раскрывающемся списке фильтра установите флажки для значений Sat и Sun, чтобы отобразить только выходные. Такой подход визуально понятнее, чем использование числовых значений дней недели.
Шаг 6: Нажмите OK. В результатах фильтрации будут показаны только выходные (суббота и воскресенье).
Примечания:
- Чтобы отфильтровать только рабочие дни, отметьте Mon, Tue, Wed, Thu и Fri на шаге 5.
- Чтобы сохранить исходные значения дат, рекомендуем сначала скопировать их во вспомогательный столбец — и только затем применять форматирование и фильтрацию.
- Инструменты Применить формат даты и To Actual незаменимы при подготовке чистых и удобочитаемых данных о днях недели для отчётов или экспорта.
Подробнее об этих инструментах:
–Применить формат даты
–К фактическим
Kutools для Excel— раскройте весь потенциал Excel с помощью более чем 300 незаменимых инструментов, которые ускоряют и упрощают вашу работу, а также воспользуйтесь возможностями ИИ для более разумной обработки данных и повышения продуктивности.Получить сейчас
Фильтрация будних дней и выходных с помощью утилиты Супер фильтр из Kutools для Excel
Если вы хотите фильтровать даты по рабочим дням или выходным, не изменяя исходный формат данных, утилита Супер фильтр из состава Kutools для Excel предлагает эффективное решение.
Kutools для Excel — включает более 300 незаменимых инструментов для Excel. Работайте в Excel быстрее, проще и эффективнее.Скачать сейчас!
Этот метод идеально подходит пользователям, которым необходима многоусловная фильтрация, которые предпочитают не изменять исходные данные или которым для повторяющихся задач фильтрации требуется графический интерфейс.
Шаг 1: Нажмите KUTOOLS PLUS > Супер фильтр, чтобы открыть панель «Супер фильтр» в книге.
Шаг 2:В панели Супер фильтр выполните следующие действия:
1) Нажмите кнопку Browse и выберите файл диапазона данных, который необходимо отфильтровать.
2)Наведите курсор на метку OR, чтобы развернуть редактор условий, затем настройте параметр Условия фильтрации:
- Выберите имя столбца (например,)Date) в первом поле.
- Выберите Week во втором поле.
- Выберите Equals в третьем поле.
- Выберите число от 1 до 7 в последнем поле: 1 — воскресенье, 7 — суббота.
Примечание: Числа от 1 до 7 соответствуют дням недели — от воскресенья до субботы соответственно. Измените критерии, если ваша неделя начинается с другого дня или используются иные региональные настройки.
3) Нажмите кнопку Filter, чтобы применить фильтр.
Теперь данные будут отображать только строки, соответствующие указанному дню — например, воскресенье, если вы выбрали 1.
Если вы хотите отфильтровать все выходные дни(субботу и воскресенье), добавьте два условия:
- Date>Week>Equals>1
- Date>Week>Equals>7
Убедитесь, что логика между ними установлена как ИЛИ.
Чтобы отфильтровать только рабочие дни(с понедельника по пятницу), примените следующее:
- Date>Week>Does not equal>1
- Date>Week>Does not equal>7
Убедитесь, что логический оператор между этими условиями установлен как И.
Ключевые моменты и рекомендации:
- Суперфильтр поддерживает сложные многоуровневые фильтры, включая диапазон дат, числовые значения и текстовые поля.
- Вы можете сохранять и повторно использовать конфигурации фильтров — идеальное решение для регулярных отчётов и пакетного анализа данных.
Подробнее об утилите Супер фильтр.
Kutools для Excel— раскройте весь потенциал Excel с помощью более чем 300 незаменимых инструментов, которые ускоряют и упрощают вашу работу, а также воспользуйтесь возможностями ИИ для более разумной обработки данных и повышения продуктивности.Получить сейчас
Используйте макрос VBA для автоматической фильтрации или выделения рабочих дней и выходных
Если вы работаете с большими наборами данных, нуждаетесь в автоматизации или часто фильтруете и выделяете рабочие дни и выходные, макрос VBA поможет сэкономить время и избавиться от рутинной ручной работы. Такой подход идеален для опытных пользователей или тех, кто уже знаком с макросами Excel, — он устраняет необходимость во вспомогательных столбцах и сторонних надстройках. Перед началом убедитесь, что макросы включены, а книга помечена как доверенная.
Шаг 1: Нажмите Alt + F11, чтобы открыть редактор Microsoft Visual Basic for Applications. Затем выберите Вставка > Модуль и вставьте приведённый ниже код VBA в окно модуля:
Sub FilterWeekendDates()
Dim rng As Range
Dim cell As Range
Dim ws As Worksheet
Dim weekDayNum As Integer
Set ws = ActiveSheet
On Error Resume Next
Set rng = Application.InputBox("Select the date range to filter (single column, without header):", "Filter Weekends Only", Type:=8)
On Error GoTo 0
If rng Is Nothing Then Exit Sub
If rng.Columns.Count > 1 Then
MsgBox "Please select only one column.", vbExclamation
Exit Sub
End If
Application.ScreenUpdating = False
ws.Rows.Hidden = False
For Each cell In rng
If IsDate(cell.Value) Then
weekDayNum = Weekday(cell.Value, vbSunday)
If weekDayNum <> 1 And weekDayNum <> 7 Then
cell.EntireRow.Hidden = True
End If
Else
cell.EntireRow.Hidden = True
End If
Next cell
Application.ScreenUpdating = True
End SubШаг 2: Закройте редактор VBA. Чтобы запустить макрос, нажмите Alt + F8, выберите в списке FilterWeekendDates и нажмите Выполнить. Появится диалоговое окно с запросом на выбор одного столбца — диапазона дат (без заголовка). Макрос скроет все строки, которые не приходятся на выходные: суббота и воскресенье останутся видимыми.
Если Вы хотите отфильтровать рабочие дни вместо выходных, обновите строку кода внутри цикла следующим образом:
Исходный код:
If weekDayNum 1 And weekDayNum 7 Then
Измените на:
If weekDayNum = 1 Or weekDayNum = 7 Then
Это изменение позволит скрыть выходные дни (субботу и воскресенье) и отображать только рабочие дни (с понедельника по пятницу).
Советы и примечания:
- В этом скрипте
Weekday(cell.Value, vbSunday)означает: воскресенье = 1, суббота = 7 — это соответствует стандартным региональным настройкам США. Измените логику, если в вашем регионе неделя начинается с понедельника. - Чтобы выделить выходные дни вместо фильтрации, используйте условное форматирование со следующей формулой:
=OR(WEEKDAY(A2,1)=1,WEEKDAY(A2,1)=7)(при условии, что дата находится в столбце A). - Этот макрос лишь визуально скрывает строки. Данные не удаляются и могут быть снова отображены путём отмены фильтров или выполнения команды
ws.Rows.Hidden = False.
Устранение неполадок:
- Если макрос не запускается, убедитесь, что макросы включены в Центре управления безопасностью Excel.
- Убедитесь, что столбец «Выберите диапазон» содержит корректные даты: ячейки, отформатированные как текст, или пустые ячейки могут быть неверно интерпретированы и скрыты.
- Если после запуска ничего не происходит, убедитесь, что диапазон выделен правильно и включает ровно один столбец.
Фильтрация рабочих дней и выходных с помощью расширенного фильтра и пользовательских критериев
Функция расширенного фильтра в Excel позволяет фильтровать данные прямо на месте или копировать результаты в другое место, используя собственные критерии — без вспомогательных столбцов и надстроек. Применив пользовательскую формулу на основе функции WEEKDAY в диапазоне критериев, вы сможете гибко отображать только рабочие дни, только выходные или даже конкретные дни недели. Этот метод идеально подходит для быстрой однократной фильтрации и не загромождает лист лишними столбцами.
Следуйте этим шагам, чтобы использовать расширенный фильтр для фильтрации рабочих дней/выходных:
- Шаг 1: Убедитесь, что ваши данные содержат заголовки. Например, предположим, что даты находятся в столбце B (заголовок: Date).
- Шаг 2:Создайте диапазон критериев в другом месте листа. В ячейке заголовка диапазона критериев (например,)D1) укажите тот же заголовок, что и в столбце с датами («Date»). В ячейке под ней (например, D2) введите следующую формулу для фильтрации выходных дней:
=OR(WEEKDAY(B2)=1, WEEKDAY(B2)=7) - Шаг 3: Выделите исходные данные. Перейдите в меню Data > Advanced в группе «Сортировка и фильтр».
- Шаг 4:В диалоговом окне «Расширенный фильтр» задайте следующие параметры:
- Диапазон списка:исходные данные, включая заголовки
- Диапазон критериев:блок критериев (заголовки и ячейка с формулой)
- Выберите, следует ли фильтровать список на месте или скопировать его в другое место
- Шаг 5: Нажмите кнопку OK. Будут отображены только даты выходных дней (или скопированы, если был выбран соответствующий вариант).
Чтобы отфильтровать рабочие дни:
Используйте эту формулу в ячейке критериев под заголовком «Дата»:
=AND(WEEKDAY(B2)>,1, WEEKDAY(B2)<,7)Практические замечания:
- The formula in the criteria cell should reference the first data cell in your date column (e.g., B2).
- Не вводите формулу как массивовую — просто введите её как обычную.
- Расширенный фильтр идеально подходит для временных представлений или экспорта отфильтрованных данных, если вы не хотите создавать вспомогательные столбцы.
Ограничения:
- Если добавлены новые данные, расширенный фильтр необходимо применить повторно.
- Заголовок в диапазоне критериев должен в точности совпадать с именем столбца в наборе данных.
Типичные ошибки и устранение неполадок:
- Убедитесь, что формула ссылается на нужную ячейку (например, B2, если даты расположены в столбце B).
- Если данные не возвращаются, протестируйте формулу вручную на примере строки, чтобы убедиться в корректности логики.
Демонстрация: фильтрация рабочих дней и выходной день в Excel
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек