Перейти к содержимому

Kutools для Office — один пакет. Пять инструментов. Выполняйте больше.

Как автоматически фильтровать строки на основе значения ячейки в Excel?

Author Xiaoyang Last modified

В 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 19+. Для старых версий Excel динамическая фильтрация через формулы менее очевидна, и вам может потребоваться исследовать вспомогательные столбцы в сочетании с традиционными функциями Автофильтра или расширенным фильтром. Проверьте свою версию, чтобы убедиться в совместимости.

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

Потенциальные ошибки: Если вы видите ошибку #ИМЯ? или #РАЗЛИВ!, проверьте, что вы используете совместимую версию Excel, и убедитесь, что в диапазоне результатов нет объединенных ячеек. Также избегайте размещения других данных в области разлива формул, чтобы избежать блокировок.


Автоматическая фильтрация строк на основе введенного значения ячейки с помощью кода VBA

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

a screenshot of filtering rows based on entered value in a cell

Чтобы автоматизировать фильтрацию таким образом, вы можете настроить простое решение на 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

a screenshot showing how to use the VBA code

Примечание: В этом коде VBA A1:C20 относится к вашему диапазону данных, к которому применяется фильтр. E2 — это ячейка, куда вы вводите значение фильтра (ваши целевые критерии), а E1:E2 — это ячейки, используемые как условия фильтра. Вы можете изменить эти диапазоны по необходимости — убедитесь, что они точно отражают расположение ваших данных и критериев фильтрации для конкретной структуры вашего листа. Также лучше избегать объединенных ячеек и убедиться, что ваш диапазон данных содержит заголовки, если это необходимо для точной фильтрации.

3. Теперь введите или измените ваши условия фильтрации в ячейках E1 и/или E2. Как только вы нажмете Enter, код VBA автоматически запустится и отфильтрует указанный диапазон данных, чтобы отобразить только те строки, которые соответствуют вашим входным критериям.

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

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

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


Демонстрация: Автоматическая фильтрация строк на основе введенного значения ячейки с помощью кода VBA

 

Фильтрация данных по нескольким критериям или другим конкретным условиям, таким как по длине текста, с учетом регистра и т.д.

Когда вам нужно выполнить более сложные операции фильтрации — такие как сочетание нескольких критериев фильтрации, фильтрация по длине текста, чувствительность к регистру текста или фильтрация на основе определенных периодов — встроенные фильтры Excel могут оказаться недостаточными. В таких случаях функция Супер фильтр из Kutools для Excel предоставляет практичный способ легко и эффективно управлять сложными требованиями фильтрации. Эта функция помогает вам:

  • Применять несколько критериев по нескольким столбцам; Фильтровать данные по количеству символов в ячейке;
  • Фильтровать по верхнему или нижнему регистру текста; Фильтровать данные на основе года, месяца, дня, недели или квартала.

a screenshot of using Kutools for Excel to filter date by multiple criteria

Kutools для Excel: Укомплектован более чем 300 практичными надстройками Excel, полностью функциональная бесплатная пробная версия на 30 дней. Скачать и попробовать бесплатно сейчас!


Лучшие инструменты для повышения продуктивности в Office

🤖 Kutools AI Aide: Совершенно новый подход к анализу данных благодаря: Интеллектуальное выполнение |  Генерация кода  |  Создание пользовательских формул |  Анализ данных и построение диаграмм  |  Вызов Kutools Functions
Популярные функции: Поиск, выделение или отметка дубликатов | Удалить пустые строки | Объединить столбцы или адреса без потери данных | Округлить ...
Супер ПОИСК: VLOOKUP по нескольким критериям | VLOOKUP по нескольким значениям | Многолистовой поиск | Распознавание нечетких соответствий ...
Расширенный раскрывающийся список: Быстро создать раскрывающийся список | Зависимый раскрывающийся список | Множественный выбор в раскрывающемся списке ...
Менеджер столбцов: Добавить определённое количество столбцов | Переместить столбцы | Переключить видимость скрытых столбцов | Сравнить диапазоны и столбцы ...
Рекомендуемые функции: Сетка фокусировки | Дизайн листа | Улучшенная строка формулы | Управление книгой и листами | Библиотека автотекста | Выбор даты | Объединить данные | Зашифровать/расшифровать ячейки | Отправить письмо по списку | Супер фильтр | Специальный фильтр (фильтр жирный/курсив/зачеркнутый...) ...
Топ15 наборов инструментов:12 текстовых инструментов (Добавить текст, Удалить определенные символы, ...) |50+ типов диаграмм (Диаграмма Ганта, ...) |40+ полезных формул (Расчет возраста на основе даты рождения, ...) |19 инструментов для вставки (Вставить QR-код, Вставить изображение по пути, ...) |12 инструментов преобразования (Преобразовать в слова, Конвертация валюты, ...) |7 инструментов объединения и разделения (Расширенное объединение строк, Разделить ячейки, ...) | ... и многое другое
Используйте Kutools на предпочитаемом вами языке — поддерживает Английский, Испанский, Немецкий, Французский, Китайский и более40 других языков!

Повысьте свои навыки работы в 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 Tabs PowerPoint
  • Комплексный набор — надстройки для Excel, Word, Outlook и PowerPoint плюс Office Tab Pro
  • Один установщик, одна лицензия — настройка занимает считанные минуты (MSI-совместимо)
  • Совместная работа — максимальная эффективность между приложениями Office
  • 30-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
  • Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек