✅ Створення випадаючого фільтра для вилучення даних на основі вибору

Зміст

Витяг даних з випадаючого списку в Excel

Перегляд відео - Витяг даних за допомогою випадаючого списку в Excel

У цьому посібнику я покажу вам, як створити розкривний фільтр у Excel, щоб ви могли витягти дані на основі вибраного зі спадного меню.

Як показано на малюнку нижче, я створив розкривний список із назвами країн. Щойно я вибираю будь-яку країну зі спадного меню, дані для цієї країни витягуються праворуч.

Зверніть увагу: щойно я вибираю Індію зі спадного фільтра, всі записи для Індії витягуються.

Витяг даних з випадаючого списку в Excel

Нижче наведено кроки для створення випадаючого фільтра, який видобуватиме дані для вибраного елемента:

Створіть унікальний список предметів.
Додайте випадаючий фільтр, щоб відобразити ці унікальні елементи.
Використовуйте допоміжні стовпці для вилучення записів для вибраного елемента.

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

Створіть унікальний список предметів

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

У наведеному вище прикладі першим кроком є отримання унікального списку всіх країн.

Нижче наведено кроки, щоб отримати унікальний список:

Виберіть усі країни та вставте їх в іншу частину аркуша.
Перейдіть до Дані -> Видалити дублікати.
У діалоговому вікні Видалити дублікати виберіть стовпець, у якому є список країн. Це дасть вам унікальний список, як показано нижче.

Тепер ми будемо використовувати цей унікальний список для створення розкривного списку.

Дивитися також: Найкращий посібник із пошуку та видалення дублікатів у Excel.

Створення випадаючого фільтра

Нижче наведено кроки для створення розкривного списку в осередку:

Перейдіть до Дані -> Перевірка даних.
У діалоговому вікні Перевірка даних виберіть вкладку Налаштування.
На вкладці "Налаштування" виберіть "Список" у спадному меню, а в полі "Джерело" виберіть унікальний список країн, які ми створили.
Натисніть OK.

Тепер мета-вибрати будь-яку країну зі спадного списку, і це повинно дати нам список записів для країни.

Для цього нам потрібно буде використовувати допоміжні стовпці та формули.

Створіть допоміжні стовпці для вилучення записів для вибраного елемента

Щойно ви зробите вибір зі спадного меню, вам потрібно, щоб Excel автоматично визначив записи, що належать до цього вибраного елемента.

Це можна зробити за допомогою трьох допоміжних стовпців.

Ось кроки для створення допоміжних стовпців:

Колонка помічника №1 - Введіть серійний номер для всіх записів (20 у цьому випадку для цього можна скористатися функцією ROWS ()).
Колонка помічника №2 - Використовуйте цю просту функцію IF: = IF (D4 = $ H $ 2, E4, ””)
- Ця формула перевіряє, чи відповідає країна в першому рядку країні у спадному меню. Тому, якщо я вибираю Індію, вона перевіряє, чи в першому рядку Індія є країною чи ні. Якщо це Істинно, він повертає цей номер рядка, інакше повертає порожнім (""). Тепер, коли ми обираємо будь -яку країну, відображаються лише ті номери рядків (у другому допоміжному стовпці), у яких є вибрана країна. (Наприклад, якщо вибрано Індію, це виглядатиме як на малюнку нижче).

Тепер нам потрібно витягти дані лише для цих рядків, які відображають номер (оскільки це рядок, що містить цю країну). Однак ми хочемо, щоб ці записи були без пробілів один за одним. Це можна зробити за допомогою третього стовпця -помічника

Третя колона помічника - Використовуйте таку комбінацію функцій IFERROR та SMALL:
= ПОМИЛКА (МАЛЕНЬКА ($ F $ 4: $ F $ 23, E4), ””)

Це дасть нам щось таке, як показано нижче на фото:

Тепер, коли у нас є число разом, нам просто потрібно витягти дані з цього числа. Це можна легко зробити за допомогою функції INDEX (використовуйте цю формулу в клітинках, де вам потрібно отримати результат):
= ПОМИЛКА (ІНДЕКС ($ B $ 4: $ D $ 23, $ G4, COLUMNS ($ J $ 3: J3)), "")