Як фільтрувати дані з вибору зі спадного списку в Excel?

У Excel більшість із нас може фільтрувати дані за допомогою функції фільтрування. Але чи пробували ви коли-небудь фільтрувати дані зі списку, що випадає? Наприклад, коли я вибираю один елемент зі спадного списку, я хочу, щоб його відповідні рядки були відфільтровані, як показано на наступному знімку екрана. У цій статті я розповім про те, як фільтрувати дані за допомогою випадаючого списку на одному або двох робочих аркушах.

Фільтруйте дані з вибору зі спадного списку на одному аркуші за допомогою допоміжних формул

Фільтруйте дані із вибору зі спадного списку на двох робочих аркушах із кодом VBA

Фільтруйте дані з вибору зі спадного списку на одному аркуші за допомогою допоміжних формул

Щоб відфільтрувати дані зі спадного списку, ви можете створити кілька стовпців допоміжних формул, виконайте наступні кроки один за одним:

1. Спочатку вставте випадаючий список. Клацніть клітинку, куди потрібно вставити випадаючий список, а потім клацніть дані > Перевірка достовірності даних > Перевірка достовірності даних, див. скріншот:

2. В вискочив Перевірка достовірності даних у діалоговому вікні під Налаштування вкладка, виберіть список від дозволяти , а потім клацніть для вибору списку даних, на основі якого потрібно створити випадаючий список, див. знімок екрана:

3. А потім клацніть OK кнопку, випадаючий список вставляється відразу, і виберіть один пункт зі спадного списку, а потім введіть цю формулу: = РЯДКИ ($ A $ 2: A2) (A2 це перша комірка в стовпці, що містить значення випадаючого списку), у клітинку D2, а потім перетягніть маркер заповнення вниз до комірок, щоб застосувати цю формулу, див. знімок екрана:

4. Продовжуйте вводити цю формулу: = IF (A2 = $ H $ 2, D2, "") в клітинку E2, а потім перетягніть маркер заповнення вниз, щоб заповнити цю формулу, див. знімок екрана:

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

5. А потім введіть цю формулу: = IFERROR (МАЛЕНЬКИЙ ($ 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, щоб оптимізувати робочий процес, зокрема:

• Випадаючий список із множинним вибором: Виберіть кілька записів одночасно для ефективної обробки даних.
• Розкривний список із прапорцями: покращте взаємодію з користувачем і ясність ваших електронних таблиць.
• Динамічний спадний список: Автоматично оновлюється на основі змін даних, забезпечуючи точність.
• Розкривний список для пошуку: Швидко знайдіть необхідні записи, заощаджуючи час і зменшуючи клопоти.

Завантажте зараз, щоб отримати безкоштовну 30-денну пробну версію і змініть свій досвід роботи з Excel!

Фільтруйте дані із вибору зі спадного списку на двох робочих аркушах із кодом 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. Відтепер, коли ви виберете один елемент зі спадного списку на Аркуші1, а відповідні дані будуть відфільтровані на Аркуші2, див. Знімок екрана: