Посмотреть видео - Извлечение данных с помощью раскрывающегося списка в Excel
В этом руководстве я покажу вам, как создать раскрывающийся фильтр в Excel, чтобы вы могли извлекать данные на основе выбора из раскрывающегося списка.
Как показано на рисунке ниже, я создал раскрывающийся список с названиями стран. Как только я выберу любую страну из раскрывающегося списка, данные для этой страны будут извлечены справа.
Обратите внимание: как только я выберу Индию из раскрывающегося списка, будут извлечены все записи для Индии.
Извлечь данные из выпадающего списка в Excel
Вот шаги, чтобы создать раскрывающийся фильтр, который будет извлекать данные для выбранного элемента:
- Создайте уникальный список предметов.
- Добавьте раскрывающийся фильтр, чтобы отобразить эти уникальные элементы.
- Используйте вспомогательные столбцы для извлечения записей для выбранного элемента.
Давайте углубимся и посмотрим, что нужно сделать на каждом из этих шагов.
Создайте уникальный список предметов
Хотя в вашем наборе данных могут быть повторы элемента, нам нужны уникальные имена элементов, чтобы мы могли создать раскрывающийся фильтр, используя его.
В приведенном выше примере первым шагом является получение уникального списка всех стран.
Вот шаги, чтобы получить уникальный список:
- Выберите все страны и вставьте их в другую часть листа.
- Перейдите в Данные -> Удалить дубликаты.
- В диалоговом окне «Удалить дубликаты» выберите столбец, в котором находится список стран. Это даст вам уникальный список, как показано ниже.
Теперь мы воспользуемся этим уникальным списком для создания раскрывающегося списка.
Смотрите также: Полное руководство по поиску и удалению дубликатов в Excel.
Создание выпадающего фильтра
Вот шаги, чтобы создать раскрывающийся список в ячейке:
- Перейдите в Data -> Data Validation.
- В диалоговом окне «Проверка данных» выберите вкладку «Настройки».
- На вкладке «Настройки» выберите «Список» в раскрывающемся списке, а в поле «Источник» выберите уникальный список стран, который мы создали.
- Щелкните ОК.
Теперь цель состоит в том, чтобы выбрать любую страну из раскрывающегося списка, и это должно дать нам список записей для страны.
Для этого нам нужно будет использовать вспомогательные столбцы и формулы.
Создать вспомогательные столбцы для извлечения записей для выбранного элемента
Как только вы сделаете выбор из раскрывающегося списка, вам понадобится Excel, чтобы автоматически идентифицировать записи, принадлежащие этому выбранному элементу.
Это можно сделать с помощью трех вспомогательных столбцов.
Вот шаги для создания вспомогательных столбцов:
- Столбец помощника №1 - Введите серийный номер для всех записей (в данном случае 20, для этого можно использовать функцию ROWS ()).
- Столбец помощника №2 - Используйте эту простую функцию IF: = IF (D4 = $ H $ 2, E4, ””)
- Эта формула проверяет, совпадает ли страна в первой строке со страной в раскрывающемся меню. Поэтому, если я выбираю Индию, он проверяет, есть ли в первой строке Индия в качестве страны или нет. Если он имеет значение True, возвращается номер этой строки, в противном случае возвращается пустой («»). Теперь, когда мы выбираем любую страну, отображаются только те номера строк (во втором вспомогательном столбце), в которых есть выбранная страна. (Например, если выбрана Индия, это будет выглядеть как на картинке ниже).
Теперь нам нужно извлечь данные только для этих строк, в которых отображается число (поскольку это строка, содержащая эту страну). Однако нам нужны эти записи без пробелов, одна за другой. Это можно сделать с помощью третьего вспомогательного столбца
- Третий вспомогательный столбец - Используйте следующую комбинацию функций ЕСЛИОШИБКА и МАЛЫЙ:
= ЕСЛИОШИБКА (МАЛЫЙ ($ F $ 4: $ F $ 23; E4), ””)
Это дало бы нам что-то, как показано ниже на картинке:
Теперь, когда у нас есть число, нам просто нужно извлечь данные из этого числа. Это легко сделать с помощью функции ИНДЕКС (используйте эту формулу в тех ячейках, где вам нужно извлечь результат):
= ЕСЛИОШИБКА (ИНДЕКС ($ B $ 4: $ D $ 23, $ G4, COLUMNS ($ J $ 3: J3)), ””)
Эта формула состоит из 2 частей:
ПОКАЗАТЕЛЬ - Это извлекает данные на основе номера строки
ЕСЛИ ОШИБКА - Эта функция возвращает пустое значение, если нет данных
Вот снимок того, что вы наконец получили:
Теперь вы можете скрыть исходные данные, если хотите. Кроме того, вы можете иметь исходные данные и извлеченные данные на двух разных листах.
Вперед, продолжать. воспользуйтесь этой техникой и произведите впечатление на своего начальника и коллег (немного выпендриться - никогда не помешает).
Загрузите файл примера
Понравился учебник? Сообщите мне свои мысли в разделе комментариев.
Вы также можете найти следующие полезные уроки:
- Динамический фильтр Excel - извлекайте данные по мере ввода.
- Динамический поиск в Excel с использованием условного форматирования.
- Создайте динамический раскрывающийся список с предложениями поиска.
- Как извлечь подстроку в Excel с помощью формул.
- Как отфильтровать ячейки с помощью полужирного шрифта в Excel.
