Как создать многоуровневый зависимый выпадающий список в Excel?

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

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

Чтобы создать многоуровневый зависимый выпадающий список, выполните следующие шаги:

Шаг 1: создайте данные для многоуровневого зависимого выпадающего списка.

1. Сначала создайте первый, второй и третий выпадающий список данных, как показано на скриншоте ниже:

Шаг 2: создайте имена диапазонов для значений каждого выпадающего списка.

2. Затем выберите значения первого выпадающего списка (исключая ячейку заголовка) и присвойте им имя диапазона в поле «Имя», расположенном рядом со строкой формул, см. скриншот:

3. После этого выберите данные второго выпадающего списка и нажмите «Формулы» > «Создать из выделения», см. скриншот:

4. В открывшемся диалоговом окне «Создать имена из выделения» отметьте только опцию «Верхняя строка», см. скриншот:

5. Нажмите «ОК», и имена диапазонов будут созданы сразу для всех данных второго выпадающего списка. Затем вам нужно создать имена диапазонов для значений третьего выпадающего списка. Продолжайте нажимать «Формулы» > «Создать из выделения». В диалоговом окне «Создать имена из выделения» отметьте только опцию «Верхняя строка», см. скриншот:

6. Затем нажмите кнопку «ОК», и значения третьего уровня выпадающего списка получат имена диапазонов.

Шаг 3: создайте выпадающий список проверки данных.

7. Затем щелкните ячейку, куда вы хотите поместить первый зависимый выпадающий список. Например, я выберу ячейку I2, затем нажмите «Данные» > «Проверка данных» > «Проверка данных», см. скриншот:

8. В диалоговом окне «Проверка данных» на вкладке «Параметры» выберите «Список» из раскрывающегося списка «Разрешить», а затем введите следующую формулу в текстовое поле «Источник».

=Continents

Примечание: В этой формуле «Континенты» — это имя диапазона первых значений выпадающего списка, которое вы создали на шаге 2, пожалуйста, измените его по своему усмотрению.

9. Затем нажмите кнопку «ОК», и первый выпадающий список будет создан, как показано на скриншоте ниже:

10. После этого вам нужно создать второй зависимый выпадающий список. Выберите ячейку, куда вы хотите поместить второй выпадающий список. Здесь я нажму J2, и продолжу нажимать «Данные» > «Проверка данных» > «Проверка данных». В диалоговом окне «Проверка данных» выполните следующие действия:

• (1.) Выберите «Список» из раскрывающегося списка «Разрешить»;
• (2.) Затем введите эту формулу в текстовое поле «Источник».

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

Примечание: В приведенной выше формуле I2 — это ячейка, содержащая значение первого выпадающего списка, пожалуйста, измените её на свою собственную.

11. Нажмите «ОК», и второй зависимый выпадающий список будет создан сразу же, см. скриншот:

12. На этом этапе вам нужно создать третий зависимый выпадающий список. Щелкните ячейку для вывода значения третьего выпадающего списка. Здесь я выберу ячейку K2, и затем нажму «Данные» > «Проверка данных» > «Проверка данных». В диалоговом окне «Проверка данных» выполните следующие действия:

• (1.) Выберите «Список» из раскрывающегося списка «Разрешить»;
• (2.) Затем введите эту формулу в текстовое поле «Источник».

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

Примечание: В приведенной выше формуле J2 — это ячейка, содержащая значение второго выпадающего списка, пожалуйста, измените её на свою собственную.

13. Затем нажмите «ОК», и три зависимых выпадающих списка будут успешно созданы, см. демонстрацию ниже:

Создание многоуровневого зависимого выпадающего списка в Excel с удивительной функцией

Создание многоуровневых зависимых выпадающих списков в Excel может быть сложным, особенно при работе с большими наборами данных или сложными иерархиями. «Kutools for Excel» устраняет сложности, предлагая удобный интерфейс и продвинутые функции, которые упрощают процесс. С помощью функции Kutools «Динамический выпадающий список» вы можете создавать динамические, многоуровневые зависимые выпадающие списки всего за несколько кликов — без необходимости обладания продвинутыми навыками работы с Excel.

1. Сначала вы должны создать свои данные в формате, показанном на скриншоте ниже:

2. Затем нажмите «Kutools» > «Выпадающий список» > «Динамический выпадающий список», см. скриншот:

3. В диалоговом окне «Зависимый выпадающий список» выполните следующие действия:

• Отметьте опцию «Зависимый выпадающий список с 3-5 уровнями» в разделе «Тип»;
• Укажите диапазон данных и диапазон вывода по вашему усмотрению.
• Затем нажмите кнопку «ОК».
  

4. Теперь трехуровневый выпадающий список создан, как показано в следующей демонстрации:

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

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

Больше статей о связанных выпадающих списках: