Создание динамического зависимого выпадающего списка в Excel (пошаговое руководство)

В этом руководстве мы пошагово расскажем, как создать зависимый выпадающий список, который отображает варианты выбора в зависимости от значений, выбранных в первом выпадающем списке. Другими словами, мы создадим список проверки данных Excel на основе значения другого списка.

Создание динамического зависимого выпадающего списка
Создайте зависимый выпадающий список за 10 секунд с помощью удобного инструмента
Создание динамического зависимого выпадающего списка в Excel 2021, Excel 365 и более новых версиях
Некоторые вопросы, которые вы можете задать об этом руководстве

Бесплатно скачайте пример файла

Видео: Создание зависимого выпадающего списка в Excel

Создание динамического зависимого выпадающего списка

Шаг 1: Введите элементы для выпадающих списков

1. Сначала введите элементы, которые должны появиться в выпадающих списках, каждый список в отдельном столбце.

Обратите внимание, что элементы в первом столбце (Продукт) будут служить именами Excel для зависимых списков позже. Например, здесь Фрукты и Овощи станут Именами для столбцов B2:B5 и C2:C6 соответственно.

Смотрите скриншот:

2. Затем создайте таблицы для каждого списка данных.

Выберите диапазон столбца A1:A3, нажмите «Вставка» → «Таблица», затем в диалоговом окне Создание таблицы отметьте флажок «Моя таблица имеет заголовки». Нажмите «ОК».

Затем повторите этот шаг, чтобы создать таблицы для остальных двух списков.

Вы можете просмотреть все таблицы и ссылки на диапазоны в Диспетчере имен (нажмите «Ctrl» + «F3», чтобы открыть его).

Шаг 2: Создание имен диапазонов

На этом этапе вам нужно создать «Имена» для основного списка и каждого зависимого списка.

1. Выберите элементы, которые появляются в основном списке («A2:A3»).

2. Затем перейдите к полю «Имя», которое находится рядом со строкой формул.

3. Введите имя в это поле, здесь назовем его «Продукт».

4. Нажмите клавишу «Enter», чтобы завершить.

Затем повторите вышеуказанные шаги, чтобы отдельно создать Имена для каждого зависимого списка.

Здесь второй столбец (B2:B5) называется Фрукты, а третий столбец (C2:C6) — Овощи.

Вы можете просмотреть все имена диапазонов в Диспетчере имен (нажмите «Ctrl» + «F3», чтобы открыть его).

Шаг 3: Добавление основного выпадающего списка

Далее добавьте основной выпадающий список (Продукт), который является обычным выпадающим списком проверки данных, а не зависимым выпадающим списком.

1. Сначала создайте таблицу.

Выберите ячейку («E1»), введите заголовок первого столбца («Продукт»), затем перейдите к следующей ячейке столбца («F1»), введите заголовок второго столбца («Элемент»). Эта таблица будет содержать выпадающие списки.

Затем выберите эти два заголовка («E1» и «F1»), нажмите вкладку «Вставка» и выберите «Таблица» в группе Таблицы.

В диалоговом окне Создание таблицы отметьте флажок «Моя таблица имеет заголовки» и нажмите «ОК».

2. Выберите ячейку «E2», куда вы хотите вставить основной выпадающий список, нажмите вкладку «Данные» и перейдите в группу Инструменты данных, чтобы нажать «Проверка данных» → «Проверка данных».

3. В диалоговом окне Проверка данных

• Выберите «Список» в разделе «Разрешить»,
• Введите формулу ниже в поле «Источник», Продукт — это Имя основного списка,
• Нажмите «ОК».

=Product

Теперь вы можете видеть, что основной выпадающий список создан.

Шаг 4: Добавление зависимого выпадающего списка

1. Выберите ячейку «F2», куда вы хотите добавить зависимый выпадающий список, нажмите вкладку «Данные», и перейдите в группу Инструменты данных, чтобы нажать «Проверка данных» → «Проверка данных».

2. В диалоговом окне Проверка данных

• Выберите «Список» в разделе «Разрешить»,
• Введите формулу ниже в поле «Источник», E2 — это ячейка, содержащая основной выпадающий список.
• Нажмите «ОК».

=INDIRECT(SUBSTITUTE(E2," ","_"))

Если ячейка E2 пуста (вы не выбрали ни один элемент в основном выпадающем списке), вы увидите сообщение, как показано ниже, нажмите «Да», чтобы продолжить.

Теперь зависимый выпадающий список создан.

Шаг 5: Проверка зависимого выпадающего списка.

1. Выберите «Фрукты» в основном выпадающем списке («E2»), затем перейдите к зависимому выпадающему списку («F2»), чтобы нажать на значок стрелки, проверьте, есть ли фрукты в списке, затем выберите один элемент из зависимого выпадающего списка.

2. Нажмите клавишу «Tab», чтобы начать новую строку в таблице ввода данных, выберите «Овощи», и перейдите к следующей ячейке справа, проверьте, есть ли овощи в списке, затем выберите один элемент из зависимого выпадающего списка.

Создайте зависимый выпадающий список за 10 секунд с помощью удобного инструмента

«Kutools for Excel» предоставляет мощный инструмент для создания зависимого выпадающего списка проще и быстрее:

Шаг 1: Введите элементы для выпадающего списка

Сначала организуйте свои данные, как показано на скриншоте ниже:

Шаг 2: Применение инструмента Kutools

1. Выберите созданные вами данные, нажмите вкладку «Kutools», и нажмите «Выпадающий список», чтобы отобразить подменю, нажмите «Динамический выпадающий список».

2. В «Зависимом выпадающем списке»:

• Отметьте «Режим B», который соответствует вашему режиму данных,
• Выберите «область размещения списка», количество столбцов области вывода должно быть равно количеству столбцов диапазона данных,
• Нажмите «Ок».

Теперь зависимый выпадающий список создан.

Создание динамического зависимого выпадающего списка в Excel 2021, Excel 365 и более новых версиях

Если вы используете Excel 365, Excel 2021 или более новые версии, существует другой способ быстро создать динамический зависимый выпадающий список, используя новые функции «UNIQUE» и «FILTER».

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

Шаг 1: Использование формулы для получения элементов для основного выпадающего списка

Выберите ячейку, например, ячейку G3, и используйте функции UNIQUE и FILTER для извлечения уникальных значений из списка «Продукт», который будет источником основного выпадающего списка, и нажмите клавишу «Enter».

=UNIQUE(FILTER(A3:A20, A3:A20<>""))

Шаг 2: Создание основного выпадающего списка

1. Выберите ячейку, куда вы хотите поместить основной выпадающий список, например, ячейку «D3», нажмите вкладку «Данные», и перейдите в группу Инструменты данных, чтобы нажать «Проверка данных» → «Проверка данных».

2. В диалоговом окне «Проверка данных»

• Выберите «Список» в разделе «Разрешить»,
• Введите формулу ниже в поле «Источник»,
• Нажмите «ОК».

=$G$3#

Теперь основной выпадающий список создан.

Шаг 3: Использование формулы для получения элементов для зависимого выпадающего списка

Выберите ячейку, например, ячейку H3, используйте функцию FILTER для фильтрации элементов на основе значения в ячейке «D3» (выбранный элемент в основном выпадающем списке), нажмите клавишу «Enter».

=FILTER(B3:B20, A3:A20=D3)

Шаг 4: Создание зависимого выпадающего списка

1. Выберите ячейку, которая будет содержать зависимый выпадающий список, например, ячейку «E3», нажмите вкладку «Данные», и перейдите в группу Инструменты данных, чтобы нажать «Проверка данных» → «Проверка данных».

2. В диалоговом окне «Проверка данных»

• Выберите «Список» в разделе «Разрешить»,
• Введите формулу ниже в поле «Источник»,
• Нажмите «ОК».

=$H$3#

Теперь зависимый выпадающий список успешно создан.

Когда вы добавляете новые элементы или вносите изменения в A3:A20, выпадающие списки будут автоматически обновляться.

=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")))

=SORT(FILTER(B3:B20, A3:A20=D3))

=SORT(UNIQUE(FILTER(A3:A20, A3:A20<>"")), 1, -1)

=SORT(FILTER(B3:B20, A3:A20=D3), 1, -1)