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


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

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

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

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

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

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

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

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

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

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

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

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

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

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

  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&»]» -правильно ли написана формула? при вводе — пишет ошибка в формуле

  6. Катя

    Как можно сделать связанные выпадающие ячейки, если в списке нельзя обойтись без знаков «» и пробелов?

  7. Полина

    Добрый день! Очень нужно помощь, мучаюсь третий день ничего не понимаю! Мне необходимо сделать зависимые ячейки от позиций из раскрывающегося списка. Есть список позиций меню (раскрывающийся список я уже сделала), необходимо что бы в зависимости от выбранного блюда в соседней ячейке автоматически отображалась цена. Если возможно, объясните как правильно это сделать?

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

      Полина, добрый день.
      Попробуйте, если это возможно, подтягивать цену в соседнюю ячейку с помощью функции ВПР (ГПР и пр.).
      Также можете скинуть файл мне на почту (info@tutorexcel.ru), я постараюсь подсказать, что можно сделать.

  8. Руслан

    Добрый день!
    Подскажите пожалуйста. Который день пытаюсь сделать…
    1. Как сделать кликабельными пункты (элементы) выпадающего списка? Чтобы при выборе пункта из списка можно было перейти на другой лист или открыть (файл).
    2. Как сделать чтобы при заполнении ячейки в одном листе — заполнялась ячейка в таблице с автоматическим переходом на следующую строчку таблицы?

    заранее благодарен, с уважением

  9. Павел

    Добрый день!
    Спасибо очень хороший пример, но есть вопрос, как его доработать так, чтобы в качестве исходных данных была умная таблица (т.е. динамическая), а в зависимый список содержал только не пустые значения (т.е. при выборе Суши в зависимом списке было всего 3 значения).
    Заранее благодарен.

  10. Сергей

    Сделал по Вашему примеру. Всё получилось. Спасибо! Но…
    Пример работает только для первой строки. В остальных строках, во втором столбце, вне зависимости от значения в первом, выпадающий список только для варианта из строки 1. Т.е.. 1 строка 1 столбец Пицца 2 столбец Гавайская… 2 строка 1 столбец Суши 2 столбец опять Гавайская(семейство Пицц) Что делаю не так?

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

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

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

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

Закрыть