Перейти к основному содержанию

Как отфильтровать данные из выпадающего списка в Excel?

В Excel большинство из нас может фильтровать данные с помощью функции «Фильтр». Но пробовали ли вы когда-нибудь фильтровать данные из выпадающего списка? Например, когда я выбираю один элемент из раскрывающегося списка, я хочу, чтобы соответствующие ему строки были отфильтрованы, как показано на следующем снимке экрана. В этой статье я расскажу о том, как фильтровать данные с помощью раскрывающегося списка на одном или двух листах.

Фильтрация данных из выпадающего списка на одном листе с помощью вспомогательных формул

Отфильтровать данные из выпадающего списка на двух листах с кодом VBA


Фильтрация данных из выпадающего списка на одном листе с помощью вспомогательных формул

Чтобы отфильтровать данные из раскрывающегося списка, вы можете создать несколько столбцов вспомогательных формул, выполните следующие шаги один за другим:

1. Сначала вставьте раскрывающийся список. Щелкните ячейку, в которую вы хотите вставить раскрывающийся список, затем щелкните Данные > проверка достоверности данных > проверка достоверности данных, см. снимок экрана:

2. В выскочившем проверка достоверности данных диалоговое окно под Настройки вкладка, выберите Список из Разрешить раскрывающийся список, а затем щелкните кнопку, чтобы выбрать список данных, на основе которого вы хотите создать раскрывающийся список, см. снимок экрана:

3, Затем нажмите OK Кнопка, раскрывающийся список вставляется сразу, выберите один элемент из раскрывающегося списка, затем введите эту формулу: = СТРОКИ ($ A $ 2: A2) (A2 - первая ячейка в столбце, содержащая значение раскрывающегося списка) в ячейку D2, а затем перетащите дескриптор заполнения вниз к ячейкам, чтобы применить эту формулу, см. снимок экрана:

4. Продолжайте вводить эту формулу: = ЕСЛИ (A2 = $ H $ 2; D2; "") в ячейку E2, а затем перетащите дескриптор заполнения вниз, чтобы заполнить эту формулу, см. снимок экрана:

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

5. А затем введите эту формулу: = ЕСЛИОШИБКА (МАЛЕНЬКИЙ ($ 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. С этого момента, когда вы выбираете один элемент из раскрывающегося списка, все строки, основанные на этом выборе, фильтруются одновременно, см. Снимок экрана:


Отфильтровать данные из выпадающего списка на двух листах с кодом VBA

Если ячейка раскрывающегося списка находится на Листе 1, а отфильтрованные данные на Листе 2, при выборе одного элемента из раскрывающегося списка другой лист будет отфильтрован. Как вы могли закончить эту работу в Excel?

Следующий код VBA может оказать вам услугу, пожалуйста, сделайте следующее:

1. Щелкните правой кнопкой мыши вкладку листа, содержащую ячейку раскрывающегося списка, и выберите Просмотреть код из контекстного меню в открывшемся Microsoft Visual Basic для приложений окна, скопируйте и вставьте следующий код в пустой модуль:

Код 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 - это ячейка, содержащая раскрывающийся список, и Sheet2 содержит ли рабочий лист данные, которые вы хотите отфильтровать. Номер 1 в скрипте: Автофильтр 1 - это номер столбца, по которому вы хотите выполнить фильтрацию. Вы можете изменить их по своему усмотрению.

2. С этого момента, когда вы выбираете один элемент из раскрывающегося списка на Sheet1, и соответствующие данные будут отфильтрованы на Sheet2, см. Снимок экрана:

Лучшие инструменты для офисной работы

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

Улучшите свои навыки работы с Excel с помощью Kutools for Excel и почувствуйте эффективность, как никогда раньше. Kutools for Excel предлагает более 300 расширенных функций для повышения производительности и экономии времени.  Нажмите здесь, чтобы получить функцию, которая вам нужна больше всего...

Описание


Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Comments (3)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
For me, the Formula =ROWS($A$2:A2) didn't workend! It always gave me "2" back. I had to put =ROWS($A2:A2), so without the second "$", in order to reproduce your result.
This comment was minimized by the moderator on the site
How do I add multiple drown down menus? For example,
If i wanted a drop down menu for Product and name?.
This comment was minimized by the moderator on the site
Hey Kev, wondering if you found an answer to your question here? I have been looking for a bit to no avail.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations