Создание динамического Динамический список в Excel (пошагово)

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

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

Скачайте бесплатный образец файла

Видео: Создание Динамический список в Excel

Создайте динамический Динамический список

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

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

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

См. снимок экрана:

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

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

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

Просмотреть все таблицы и соответствующие им диапазоны можно в Менеджер имен (нажмите «Ctrl» + «F3», чтобы открыть).[ [TN_20_END]]

Шаг 2: Создание Имя ячейки

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

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

2. Перейдите в поле «Имя», расположенное слева от строки формул.

3. Введите имя, например «Продукт».

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

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

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

Просмотреть все Имя ячейки можно в Менеджер имен (нажмите «Ctrl» + «F3», чтобы открыть).[ [TN_29_END]]

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

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

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

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

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

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

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

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

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

=Product

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

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

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

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

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

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

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

Теперь динамический список успешно создан.

Шаг 5: Проверка динамического списка.

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

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

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

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

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

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

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

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

2. В окне «Динамический список»:

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

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

Создайте динамический Динамический список в 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. В диалоговом окне «Проверка данных»:

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

=$G$3#

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

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

Выберите ячейку, например H3, и с помощью функции ФИЛЬТР (FILTER) отфильтруйте элементы по значению из ячейки D3 (выбранного элемента в основном раскрывающемся списке), затем нажмите клавишу Enter.

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

Шаг 4: Создание Динамический список

1. Выделите ячейку, в которую вы хотите поместить Динамический список (например, «E3»), перейдите на вкладку «Данные» и в группе «Работа с данными» выберите «Проверка данных» > «Проверка данных».

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

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

=$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)