Как автоматически фильтровать строки на основе значения ячейки в 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 19+. Для старых версий 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