Зависимые выпадающие списки в Excel

Изучим простой способ создания зависимых (также называют связанных) выпадающих списков в Excel.

Для начала поясним, что же такое связанные выпадающие списки.
Это 2 выпадающих списка, при этом список значений одного из выпадающих списков зависит от выбора значения в другом выпадающем списке. Создать по отдельности выпадающие списки не представляет сложностей, но и связать выпадающие списки можно достаточно просто.
Для наглядности рассмотрим следующую ситуацию: у нас есть несколько категорий блюд, например, пицца, суши и паста, а также виды блюд в каждой категории (пицца Пепперони, суши Филадельфия, паста Феттучини и т.д.):

Список значений
В результате мы хотим получить своеобразный двухуровневый зависимый выпадающий список: на первом уровне (списке) — категории блюд, на втором — блюда из выбранной категории.

Как сделать зависимые выпадающие списки?

В основе создания связанных выпадающих списков лежит применение функции ДВССЫЛ, которая позволяет преобразовывать текст из ячейки в ссылку.
Другими словами, если в ячейку введено текстовое значение «А1», то функция ДВССЫЛ вернет ссылку на ячейку А1.
Теперь зададим имена диапазонам состоящим из всех видов блюд каждой конкретной категории.
Для этого в панели вкладок выбираем Формулы -> Определенные имена -> Присвоить имя:

Вкладка для создания имени диапазона
Выделяем диапазон ячеек A2:A6 и создаем диапазон с именем Пицца, аналогичные действия повторяем и для списков с суши (имя диапазона — Суши) и пастой (имя диапазона — Паста):

Создание имени диапазона
Обратите внимание, что при создании имен диапазонов имя не должно включать в себя пробелы.
В том случае если в названии категории все же содержится пробел (например, Японская кухня), то одним из вариантов решения является использование нижнего подчеркивания вместо пробела в имени диапазона (Японская_кухня).

Создадим первый выпадающий список в ячейке A10, состоящий из категорий блюд (Пицца, Суши и Паста). В панели вкладок выбираем Данные -> Работа с данными -> Проверка данных, указываем тип данных Список и в качестве источника выделяем диапазон A1:C1:

Создание первого всплывающего списка
Теперь создаем второй выпадающий список, полностью повторяем действия с созданием первого списка, только в поле Источник записываем формулу =ДВССЫЛ(A10):

Создание второго всплывающего списка
Имена созданных диапазонов обязательно должны совпадать с элементами первого списка, поэтому если в первом списке есть категории содержащие пробелы, то при обращении к имени диапазона необходимо заменить пробелы на нижние подчеркивания.
Это можно осуществить с помощью функции ПОДСТАВИТЬ, которая позволяет заменить старый текст (пробел) на новый текст (нижнее подчеркивание) в текстовой строке, т.е. в нашем случае формула примет вид =ДВССЫЛ(ПОДСТАВИТЬ(A10;" ";"_")).
Также минусом данного способа создания списков является невозможность использования динамических именованных диапазонов.

Подробно ознакомиться с примером зависимых выпадающих списков — скачать пример.

Удачи вам и до скорой встречи на страницах блога Tutorexcel.ru!

Поделиться с друзьями:
Поиск по сайту:

Комментарии (13)

  1. Evgen

    В каждой колонке разное количество наименований, и в выпадающем списке отображается «КУЧА» пустых строк. Как сделать, чтобы отображались только заполненые? Если можно, то просьба подкорректировать мою формулу.

    1. tutorexcel Автор поста

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

  2. Dima

    Андрей, такое возможно только путем программирования и применения элемента ListBox. Через выпадающие списки такое сделать невозможно. К тому же — как Вы собрались помещать в одну ячейку несколько элементов? Если напишите — может придумаем что-нибудь.

  3. Татьяна

    Добрый день, помогите пожалуйста. Есть задачка: есть Лист 1, в нем находится таблица состоящая из двух столбцов. первый столбец это наименование, второй обозначение (например: Наименование «Банан», обозначение «желтый фрукт»). Необходимо на Листе 2 создать выпадающий список на основании столбца Наименование, который работал бы следующим образом.
    Из выпадающего списка я выбираю «яблоко», а в соседней ячейке (справа) появляются данные из столбца обозначение — «фрукт круглой формы, зеленого или красного цвета».

    1. tutorexcel Автор поста

      А данные в соседней ячейке из столбца с обозначениями в каком виде хотите отобразить? Тоже в в виде списка?

  4. Юлия

    Здравствуйте, я по Вашему примеру все сделала, когда я хочу сделать второй список, программа отвечает «Указанный именованный диапазон не найден» Как быть

    1. tutorexcel Автор поста

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

  5. Александр

    Добрый день!
    Подскажите, как создать зависимый список, если данные для зависимого списка расположены в умной таблице, где в первой строке название категорий, а в столбцах сами данные для зависимого списка. Формула ДВССЫЛ работает только на первый столбец.
    Пробовал при помощи СМЕЩ и ПОИСПОЗ, но почему то не работает эта функция.

    1. tutorexcel Автор поста

      Александр, добрый день.
      Если я правильно Вас понял, то здесь можно обойтись и без дополнительного использования функций.
      Первый список задается стандартным способом по именам категорий, а для второго используйте формулу =ДВССЫЛ(«Таблица1[«&A1&»]»), где Таблица1 — имя умной таблицы, а A1 — ячейка с первым списком.
      Если неправильно Вас понял, то опишите чуть подробнее проблему 🙂

      1. Александр

        Благодарю за быстрый ответ!
        Уточню :
        На одном листе есть умная таблица, состоящая из 3 столбцов : Категория, Описание, Сумма.
        В первом столбце организован выпадающий список по категориям ( списки категорий взяты со страницы «СпискиДанных» из заголовка умной «Таблицы13» )
        В следующем столбце «Описание» должен быть зависимый список по категориям. ( данные также в таблице «Таблица13») Вот с этим то списком и проблемы.
        Таблица «СпискиДанных» организована на отдельной странице при помощи умной таблицы. Заголовки с категориями и описание в столбцах соответственно.

        =ДВССЫЛ(«’Списки данных’!Таблица13[«&’Списки данных’!B3&»]» -правильно ли написана формула? при вводе — пишет ошибка в формуле

Добавить комментарий

Ваш e-mail не будет опубликован.

Читайте ранее:
Точечная диаграмма в Excel

Точечная диаграмма в Excel (или точечный график) часто используется для сравнения пар значений на координатной плоскости или нахождения зависимости между...

Закрыть