Как фильтровать рабочие дни и выходные в 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. Воспользуйтесь постоянно бесплатными функциями ИИ! Скачайте прямо сейчас!
Шаг 1: Выберите диапазон дат, который вы хотите преобразовать.
Шаг 2: Перейдите на вкладку Kutools, нажмите Формат > Применить формат даты. Появится диалоговое окно.
Шаг 3: В диалоговом окне выберите формат Wed или Wednesday из списка форматов даты, чтобы отобразить названия дней недели.
Шаг 4: Нажмите OK. Выбранные даты теперь отображаются как названия дней недели. Если вы хотите фильтровать их как текст, а не как даты, нажмите Kutools > К фактическим, чтобы преобразовать отформатированные результаты в статичный текст.
Шаг 5: Перейдите к Данные > Фильтр. В выпадающем списке фильтра отметьте Сб и Вс чтобы отобразить только выходные. Этот подход более нагляден, чем использование числовых значений дней недели.
Шаг 6: Нажмите OK. Только выходные (суббота и воскресенье) будут показаны в отфильтрованных результатах.
Примечания:
- Чтобы отфильтровать только рабочие дни, отметьте Пн, Вт, Ср, Чт и Пт на Шаге 5.
- Чтобы сохранить исходные значения дат, рассмотрите возможность копирования их во вспомогательный столбец перед применением форматирования и фильтрации.
- Инструменты Применить формат даты и К фактическим полезны при подготовке чистых, легко читаемых данных о днях недели для отчетов или экспорта.
Узнайте больше об этих инструментах:
- Применить формат даты
- К фактическим
Kutools для Excel - Усильте Excel более чем 300 необходимыми инструментами. Наслаждайтесь постоянно бесплатными функциями ИИ! Получите прямо сейчас
Фильтрация рабочих дней и выходных с помощью утилиты Супер фильтр из Kutools для Excel
Если вы хотите фильтровать даты по рабочим дням или выходным, не изменяя исходный формат данных, утилита Супер фильтр из Kutools для Excel предоставляет эффективное решение.
Kutools для Excel - Наполнен более чем 300 необходимыми инструментами для Excel. Воспользуйтесь постоянно бесплатными функциями ИИ! Скачайте прямо сейчас!
Этот метод идеально подходит для пользователей, которым требуется многоуровневая фильтрация, предпочитают не модифицировать сырые данные или нуждаются в графическом интерфейсе для повторяющихся задач фильтрации.
Шаг 1: Нажмите Kutools Plus > Супер фильтр, чтобы активировать панель Супер фильтр в книге.
Шаг 2: На панели Супер фильтр выполните следующие действия:
1) Нажмите кнопку Обзор и выберите диапазон данных, который вы хотите отфильтровать.
2) Наведите курсор на метку ИЛИ, чтобы развернуть редактор условий, затем настройте критерии фильтрации:
- Выберите имя столбца (например, Дата) в первом поле.
- Выберите Неделя во втором поле.
- Выберите Равно в третьем поле.
- Выберите число от 1 до 7 в последнем поле. Например, 1 = Воскресенье, 7 = Суббота.
Примечание: Числа от 1 до 7 представляют Воскресенье-Субботу соответственно. Измените критерии, если ваша неделя начинается с другого дня или используются другие региональные настройки.
3) Нажмите кнопку Фильтр, чтобы применить.
Теперь данные будут отображать только строки, соответствующие указанному дню — например, Воскресенье, если вы выбрали 1.
Если вы хотите отфильтровать все выходные (суббота и воскресенье), добавьте два условия:
- Дата > Неделя > Равно > 1
- Дата > Неделя > Равно > 7
Убедитесь, что логика между ними установлена как ИЛИ.
Чтобы отфильтровать только рабочие дни (понедельник-пятница), примените следующее:
- Дата > Неделя > Не равно > 1
- Дата > Неделя > Не равно > 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 (заголовок: Дата).
- Шаг 2: Настройте диапазон критериев в другом месте вашего листа. В ячейке заголовка диапазона критериев (например, D1), введите тот же заголовок, что и в столбце дат ("Дата"). В ячейке ниже него (например, D2), введите эту формулу для фильтрации выходных:
=OR(WEEKDAY(B2)=1, WEEKDAY(B2)=7) - Шаг 3: Выберите ваши исходные данные. Перейдите на вкладку Данные > Расширенный в группе Сортировка и фильтр.
- Шаг 4: В диалоговом окне Расширенный фильтр установите:
- Диапазон списка: ваши исходные данные, включая заголовки
- Диапазон критериев: ваш блок критериев (заголовки и ячейка формулы)
- Выберите, фильтровать список на месте или копировать в другое место
- Шаг 5: Нажмите OK. Будут отображаться только выходные даты (или скопированы, если была выбрана эта опция).
Чтобы отфильтровать рабочие дни:
Используйте эту формулу в ячейке критериев ниже заголовка "Дата":
=AND(WEEKDAY(B2)>1, WEEKDAY(B2)<7)Практические заметки:
- Формула в ячейке критериев должна ссылаться на первую ячейку с данными в столбце дат (например, 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-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек