Як фільтрувати дані з вибору зі спадного списку в 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. Відтепер, коли ви вибираєте один елемент зі спадного списку, всі рядки на основі цього виділення відфільтровуються одночасно, див. Знімок екрана:
Фільтруйте дані із вибору зі спадного списку на двох робочих аркушах із кодом 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, див. Знімок екрана:
Найкращі інструменти продуктивності офісу
Покращуйте свої навички Excel за допомогою Kutools для Excel і відчуйте ефективність, як ніколи раніше. Kutools для Excel пропонує понад 300 додаткових функцій для підвищення продуктивності та економії часу. Натисніть тут, щоб отримати функцію, яка вам найбільше потрібна...
Вкладка Office Передає інтерфейс із вкладками в Office і значно полегшує вашу роботу
- Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
- Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
- Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
