Как автоматически фильтровать строки по значению ячейки в Excel?
В Excel стандартная функция фильтрации обеспечивает быстрый способ отбора данных на основе критериев, выбираемых вручную. Однако в некоторых рабочих процессах может потребоваться автоматическое обновление данных сразу после ввода значения или условия в определённую ячейку. Например, если вы хотите, чтобы набор данных мгновенно фильтровался на основе ключевого слова или параметра, введённого в другую ячейку, это выходит за рамки возможностей стандартной фильтрации Excel. Такой динамический или «автофильтр» помогает оптимизировать процесс анализа данных, сократить время на ручную фильтрацию и повысить точность за счёт привязки Условия фильтрации непосредственно к значениям ячеек. Если вы стремитесь реализовать подобный механизм автофильтрации на основе ввода в ячейку, существуют несколько практических решений для достижения этого результата.
Автоматическая фильтрация строк по значению ячейки с помощью формулы Excel
Автоматическая фильтрация строк по введённому значению ячейки с помощью кода VBA
Автоматическая фильтрация строк по значению ячейки с помощью формулы Excel
Если вы предпочитаете решение без макросов или не можете использовать VBA из-за настроек безопасности или требований совместной работы, эффект «автофильтрации» можно создать с помощью комбинации формул и встроенных функций фильтрации Excel. Такой подход отлично работает в Microsoft 365, Microsoft 2019 и более поздних версиях, поддерживающих функции динамических массивов, такие как FILTER. Он идеально подходит для совместного использования с коллегами, у которых могут быть отключены макросы, а также для лёгких шаблонов, где требуется автоматизация без установки дополнительных компонентов.
1. Выберите пустую ячейку, с которой должны начинаться отфильтрованные результаты (например, установите курсор в ячейку)G2).
=FILTER(A2:C20, (A2:A20=E1) * (B2:B20=E2), "No match")2. После ввода формулы в ячейку G2 нажмите Enter. Отфильтрованный набор данных появится в диапазоне перелива, начиная с ячейки G2. При изменении значений в E1 или E2 отфильтрованный список будет мгновенно обновляться.
В этом примере предполагается, что исходные данные находятся в диапазоне A2:C20, а условия фильтрации вводятся в ячейку E1 (соответствует столбцу A) и ячейку E2 (соответствует столбцу B). Если вы хотите фильтровать только по одному столбцу, логическое условие можно упростить соответствующим образом. Сообщение «Нет совпадений» появляется, если ни одна строка не соответствует заданным критериям.
Совет: Функция FILTER доступна в Microsoft 365 и некоторых версиях Microsoft Excel 2019 и новее. В более ранних версиях Excel динамическая фильтрация с помощью формул недоступна или работает неочевидным образом, поэтому рекомендуется использовать вспомогательные столбцы вместе со стандартным автофильтром или расширенной фильтрацией. Обязательно проверьте совместимость вашей версии Excel.
При использовании этого решения результаты размещаются в новой области на листе, что позволяет сохранить исходные данные в неизменном виде: оригинальный набор данных остаётся нетронутым — фильтрация применяется только к отображаемым результатам, а не к статусу отображения «Исходные данные».
Возможные ошибки: Если вы видите ошибку #ИМЯ? или #ПЕРЕПОЛН!, убедитесь, что используется совместимая версия Excel и что в диапазоне результатов отсутствуют объединённые ячейки. Также избегайте размещения других данных в области перелива формулы, чтобы предотвратить блокировку.
Автоматическая фильтрация строк по введённому значению ячейки с помощью кода VBA
Представьте, что вы работаете с набором данных и хотите автоматически фильтровать записи по критериям, указанным в определённых ячейках. Например, как только вы вводите нужные условия в ячейки E1 и E2, данные на листе мгновенно фильтруются, чтобы соответствовать этим значениям — как показано ниже:
Чтобы автоматизировать фильтрацию таким образом, вы можете настроить простое решение на VBA. Оно срабатывает каждый раз при изменении значения в назначенных ячейках фильтрации и особенно полезно для панелей мониторинга, интерактивных отчётов или шаблонов, где пользователи ожидают динамической фильтрации через центральные ячейки параметров.
1. Перейдите на лист, где вы хотите автоматизировать фильтрацию строк в зависимости от значений, вводимых в ячейки.
2. Щёлкните правой кнопкой мыши по ярлыку листа внизу окна Excel и выберите в контекстном меню пункт Просмотреть код. В появившемся окне Microsoft Visual Basic for Applications вставьте следующий код VBA в большую пустую область (обычно называемую окном кода или модулем листа), как показано здесь:
Код VBA: автоматическая фильтрация данных в соответствии со введённым значением ячейки
Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20160606
If Target.Address = Range("E2").Address Then
Range("A1:C20").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=Range("E1:E2")
End If
End Sub
Примечание: в этом коде VBA A1:C20 обозначает ваш диапазон данных, к которому применяется фильтр. E2 — это ячейка, в которую вы вводите значение фильтра (ваш критерий), а E1:E2 — ячейки, используемые в качестве условия фильтрации. Вы можете изменить эти диапазоны по необходимости — убедитесь, что они точно соответствуют расположению ваших данных и условий фильтрации в структуре листа. Кроме того, рекомендуется избегать объединённых ячеек и убедиться, что ваш диапазон данных содержит заголовки, если они необходимы для корректной фильтрации.
3. Теперь введите или измените условия фильтрации в ячейке E1 и/или E2. Как только вы нажмёте Enter, код VBA автоматически выполнится и отфильтрует указанный диапазон данных, чтобы отобразить только строки, соответствующие вашим критериям.
Если фильтрация не запускается сразу после ввода значения, убедитесь, что макросы включены в вашей книге, и проверьте, что диапазоны в коде VBA точно соответствуют структуре текущего листа. В многопользовательских сценариях напомните другим пользователям, что для корректной работы этой функции необходимо включить макросы.
Этот метод на основе VBA особенно эффективен для динамических панелей мониторинга с параметрическим управлением, интерактивных шаблонов ввода данных или любых сценариев, где повторное ручное применение условий фильтрации было бы нецелесообразно. Однако имейте в виду: если пользователи отключат макросы, это может вызвать проблемы, а решения на основе VBA работают только в том файле, куда внедрён соответствующий код.
Если ваш сценарий предполагает частую смену критериев несколькими пользователями с разными настройками безопасности Excel или если вы планируете широко распространять файл, рассмотрите альтернативные решения — на основе формул или надстройки Excel.
Демонстрация: автоматическая фильтрация строк по введённому значению ячейки с помощью кода VBA
Фильтруйте данные по нескольким критериям или другим конкретным условиям, например по длине текста, с учётом регистра и т.д. Когда требуется выполнить более сложные операции фильтрации — например, комбинировать несколько условий фильтрации, фильтровать по длине текста, учитывать регистр символов или задавать фильтрацию по определённым периодам — встроенных средств Excel может оказаться недостаточно. В таких случаях Kutools для Excel's Супер фильтр предлагает практичное решение для лёгкого и эффективного выполнения даже самых сложных задач фильтрации. Эта функция позволяет:
Kutools для Excel: оснащён более чем 300 практичными надстройками для Excel, полностью функциональная бесплатная пробная версия на 30 дней.Скачайте и начните бесплатную пробную версию прямо сейчас! |
Лучшие инструменты повышения продуктивности в 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек