Как фильтровать данные из выпадающего списка в Excel?
В Excel большинство из нас фильтрует данные, используя функцию фильтрации. Но пробовали ли вы когда-нибудь фильтровать данные на основе выбора из выпадающего списка? Например, когда я выбираю элемент из выпадающего списка, я хочу, чтобы соответствующие строки были отфильтрованы, как показано на следующем скриншоте. В этой статье я расскажу, как фильтровать данные с помощью выпадающего списка на одном или двух листах.
Фильтрация данных из выпадающего списка на одном листе с использованием вспомогательных формул
Фильтрация данных из выпадающего списка на двух листах с использованием кода VBA
Фильтрация данных из выпадающего списка на одном листе с использованием вспомогательных формул
Чтобы фильтровать данные из выпадающего списка, вы можете создать несколько вспомогательных столбцов с формулами. Пожалуйста, выполните следующие шаги один за другим:
1. Сначала вставьте выпадающий список. Щелкните ячейку, куда вы хотите вставить выпадающий список, затем нажмите Данные > Проверка данных > Проверка данных, см. скриншот:
2. В появившемся окне Проверка данных диалогового окна, на вкладке Параметры , выберите Одномерная таблица из Разрешить выпадающего меню, а затем нажмите кнопку 
, чтобы выбрать список данных, на основе которого вы хотите создать выпадающий список, см. скриншот:
3. Затем нажмите кнопку OK, и выпадающий список будет вставлен сразу. Выберите один элемент из выпадающего списка, затем введите эту формулу: =ROWS($A$2:A2) (A2 — это первая ячейка в столбце, содержащем значение выпадающего списка) в ячейку D2, и затем протяните маркер заполнения вниз для применения этой формулы, см. скриншот:
4. Продолжайте вводить эту формулу: =IF(A2=$H$2,D2,"") в ячейку E2, а затем протяните маркер заполнения вниз для заполнения этой формулой, см. скриншот:
Примечание: В приведенной выше формуле: A2 — это первая ячейка в столбце, содержащем значение выпадающего списка, H2 — это ячейка, где размещен выпадающий список, D2 — это первая формула вспомогательного столбца.
5. Затем введите эту формулу: =IFERROR(SMALL($E$2:$E$17,D2),"") в ячейку F2, затем протяните маркер заполнения вниз до ячеек для заполнения этой формулой, см. скриншот:
Примечание: В приведенной выше формуле: E2:E17 — это ячейки второй вспомогательной формулы, D2 — это первая ячейка в первом вспомогательном столбце.
6. После вставки вспомогательных столбцов с формулами вам нужно вывести отфильтрованный результат в другое место. Пожалуйста, примените эту формулу: =IFERROR(INDEX($A$2:$C$17,$F2,COLUMNS($J$2:J2)),"") в ячейку J2, а затем протяните маркер заполнения от J2 до L2, и первая запись данных на основе выпадающего списка будет извлечена, см. скриншот:
Примечание: В приведенной выше формуле: A2:C17 — это исходные данные, которые вы хотите отфильтровать, F2 — это третий вспомогательный столбец с формулой, J2 — это ячейка, куда вы хотите вывести результат фильтрации.
7. Затем продолжайте протягивать маркер заполнения вниз до ячеек, чтобы отобразить все соответствующие отфильтрованные записи, см. скриншот:
8. Теперь, когда вы выбираете элемент из выпадающего списка, все строки на основе этого выбора фильтруются сразу, см. скриншот:
Усиление возможностей выпадающих списков Excel с помощью расширенных функций Kutools
Повысьте свою продуктивность с помощью расширенных возможностей выпадающих списков Kutools для Excel. Этот набор функций выходит за рамки базовых возможностей Excel, чтобы оптимизировать ваш рабочий процесс, включая:
- Выпадающий список с множественным выбором: Выбирайте несколько записей одновременно для эффективной обработки данных.
- Выпадающий список с флажками: Улучшает взаимодействие с пользователем и ясность в ваших электронных таблицах.
- Динамический выпадающий список: Автоматически обновляется при изменении данных, обеспечивая точность.
- Поисковый выпадающий список: Быстро находите нужные записи, экономя время и уменьшая неудобства.
Фильтрация данных из выпадающего списка на двух листах с использованием кода VBA
Если ячейка выпадающего списка находится на Листе1, а фильтруемые данные — на Листе2, то при выборе одного элемента из выпадающего списка другой лист будет отфильтрован. Как можно выполнить эту задачу в Excel?
Следующий код VBA может помочь вам, пожалуйста, сделайте следующее:
1. Щелкните правой кнопкой мыши вкладку листа, содержащую ячейку выпадающего списка, и выберите Просмотр кода из контекстного меню. В открывшемся окне Microsoft Visual Basic for Applications скопируйте и вставьте следующий код в пустой модуль:
Код VBA: Фильтрация данных из выпадающего списка на двух листах:
Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
On Error Resume Next
If Not Intersect(Range("A2"), Target) Is Nothing Then
Application.EnableEvents = False
If Range("A2").Value = "" Then
Worksheets("Sheet2").ShowAllData
Else
Worksheets("Sheet2").Range("A2").AutoFilter 1, Range("A2").Value
End If
Application.EnableEvents = True
End If
End Sub
Примечание: В приведенном выше коде: A2 — это ячейка, содержащая выпадающий список, а Лист2 — это лист, содержащий данные, которые вы хотите отфильтровать. Цифра 1 в скрипте: AutoFilter 1 — это номер столбца, по которому вы хотите фильтровать. Вы можете изменить их по своему усмотрению.
2. Теперь, когда вы выбираете элемент из выпадающего списка на Листе1, соответствующие данные будут отфильтрованы на Листе2, см. скриншот:
Лучшие инструменты для повышения продуктивности в 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-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек