Связанные (зависимые) выпадающие списки

Способ 1. Функция ДВССЫЛ (INDIRECT)

Этот фокус основан на применении функции ДВССЫЛ (INDIRECT), которая умеет делать одну простую вещь - преобразовывать содержимое любой указанной ячейки в адрес диапазона, который понимает Excel. То есть, если в ячейке лежит текст "А1", то функция выдаст в результате ссылку на ячейку А1. Если в ячейке лежит слово "Маша", то функция выдаст ссылку на именованный диапазон с именем Маша и т.д. Такой, своего рода, "перевод стрелок" ;)

Возьмем, например, вот такой список моделей автомобилей Toyota, Ford и Nissan:

Связанные списки с ДВССЫЛ

Выделим весь список моделей Тойоты (с ячейки А2 и вниз до конца списка) и дадим этому диапазону имя Toyota на вкладке Формулы (Formulas) с помощью Диспетчера имен (Name Manager). Затем повторим то же самое со списками моделей Ford и Nissan, задав соответственно имена диапазонам Ford и Nissan.

При задании имён помните о том, что имена диапазонов в Excel не должны содержать пробелов, знаков препинания и начинаться обязательно с буквы. Поэтому если бы в одной из марок автомобилей присутствовал бы пробел (например Ssang Yong), то его пришлось бы заменить в ячейке и в имени диапазона на нижнее подчеркивание (т.е. Ssang_Yong).

Теперь создадим первый выпадающий список для выбора марки автомобиля. Выделите пустую ячейку (на картинке выше - зелёную) и нажмите кнопку Проверка данных (Data Validation) на вкладке Данные (Data). Затем из выпадающего списка Тип данных (Allow)  выберите вариант Список (List) и в поле Источник (Source) выделите ячейки с названиями марок (желтые ячейки в нашем примере). После нажатия на ОК первый выпадающий список готов.

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

=ДВССЫЛ(F2)

где F2 - адрес ячейки с первым выпадающим списком (замените на свой).

Все. После нажатия на ОК содержимое второго списка будет выбираться по имени диапазона, выбранного в первом списке.

Минусы такого способа:

  • Надо руками создавать много именованных диапазонов (если у нас много марок автомобилей).
  • В качестве вторичных (зависимых) диапазонов не могут выступать динамические диапазоны задаваемые формулами типа СМЕЩ (OFFSET). Для первичного (независимого) списка их использовать можно, а вот вторичный список должен быть определен жестко, без формул. Однако, это ограничение можно обойти, создав справочник соответствий марка-модель (см. Способы 3 и 4).
  • Имена вторичных диапазонов должны совпадать с элементами первичного выпадающего списка. Т.е. если в нем есть текст с пробелами, то придется их заменять на подчеркивания с помощью функции ПОДСТАВИТЬ (SUBSTITUTE), т.е. формула будет выглядеть как:

=ДВССЫЛ(ПОДСТАВИТЬ(F2;" ";"_"))

Способ 2. Умные таблицы

Этот подход очень похож на предыдущий, но использует вместо именованных диапазонов - "умные" динамические таблицы, которые будут выступать источником данных для вторичного списка:

Связанные списки на умных таблицах и ДВССЫЛ

Таким образом, мы:

  1. Сначала преобразуем наши справочники в "умные" таблицы, используя сочетание клавиш Ctrl+T или команду Главная - Форматировать как таблицу (Home - Format as Table) и
  2. Даём им имена (Фрукты, Овощи, Зелень) на вкладке Конструктор (Design) в поле Имя таблицы (Table Name).
  3. Создаём первый и второй (связанный) выпадающие списки точно так же, как в предыдущем способе с функцией ДВССЫЛ (INDIRECT).
Главным плюсом и отличием такого способа является возможность легко добавлять новые товары в каждую категорию - динамическая "умная" таблица автоматически расширится, и нам не придется вручную исправлять ссылку на диапазон (как это требуется в предыдущем способе).

Способ 3. Отсортированный справочник

Этот способ требует наличия отсортированного списка соответствий марка-модель вот такого вида:

Связанные списки на отсортированном справочнике

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

А вот для зависимого списка моделей придется создать именованный диапазон с функцией СМЕЩ (OFFSET), который будет динамически ссылаться только на ячейки моделей определенной марки. Для этого:

  • Нажмите сочетание клавиш Ctrl+F3 или воспользуйтесь кнопкой Диспетчер имен (Name manager) на вкладке Формулы (Formulas).
  • Создайте новый именованный диапазон с любым именем (например Модели) и в поле Ссылка (Reference) в нижней части окна введите руками следующую формулу:

=СМЕЩ($A$1;ПОИСКПОЗ($G$7;$A:$A;0)-1;1;СЧЁТЕСЛИ($A:$A;$G$7);1)

=OFFSET($A$1;MATCH($G$7;$A:$A;0)-1;1;COUNTIF($A:$A;$G$7);1)

Ссылки должны быть абсолютными (со знаками $). После нажатия Enter к формуле будут автоматически добавлены имена листов - не пугайтесь.

Работает это следующим образом. Функция СМЕЩ (OFFSET) умеет выдавать ссылку на диапазон нужного размера, сдвинутый относительно исходной ячейки на заданное количество строк и столбцов. В более понятном варианте синтаксис этой функции таков:

=СМЕЩ(начальная_ячейка; сдвиг_вниз; сдвиг_вправо; высота_диапазона_в_строках; ширина_диапазона_в_столбцах)

Таким образом:

  • начальная ячейка - берем первую ячейку нашего списка, т.е. А1
  • сдвиг_вниз - нам считает функция ПОИСКПОЗ (MATCH), которая, попросту говоря, выдает порядковый номер ячейки с выбранной маркой (G7) в заданном диапазоне (столбце А)
  • сдвиг_вправо = 1, т.к. мы хотим сослаться на модели в соседнем столбце (В)
  • высота_диапазона_в_строках  - вычисляем с помощью функции СЧЕТЕСЛИ (COUNTIF), которая умеет подсчитать количество встретившихся в списке (столбце А) нужных нам значений - марок авто (G7)
  • ширина_диапазона_в_столбцах = 1, т.к. нам нужен один столбец с моделями

В итоге должно получиться что-то вроде этого:

linked_dropdowns4.gif

Осталось добавить выпадающий список на основе созданной формулы к ячейке G8. Для этого:

  • выделяем ячейку G8 
  • выбираем на вкладке Данные (Data) команду Проверка данных (Data validation)
  • из выпадающего списка выбираем вариант проверки Список (List) и вводим в качестве Источника (Source) знак равно и имя нашего диапазона, т.е.  =Модель

И наш связанный список моделей готов.

Способ 4. Неотсортированный справочник

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

Связанный выпадающий список по неотсортированному справочнику

Первый уровень выпадающего списка (диапазон А2:А14 на рисунке выше) здесь делается классическим образом через команду Данные - Проверка данных - Список (Data - Validation - List) и в качестве источника указать зелёные ячейки с названиями категорий.

А для связанных выпадающих списков во втором столбце (B2:B14) в поле Источник (Source) мы используем хитрую формулу:

=СМЕЩ($E$2;1;ПОИСКПОЗ(A2;$E$2:$G$2;0)-1;СЧЁТЗ(СМЕЩ($E$2;1;ПОИСКПОЗ(A2;$E$2:$G$2;0)-1;10;1));1)

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

 Ссылки по теме




Страницы: 1  2  3  4  
01.03.2019 08:32:03
Добрый день!
Возможно первым методом создать более двух связанных списка? Которые будут ссылаться на одну ячейку .
17.03.2019 05:20:24
почему я применяю формулу без привязки к выпадающим спискам СМЕЩ(I1;ПОИСКПОЗ(I1;Клуб;0);1;СЧЁТЕСЛИ(Клуб;$H$15);1) у меня выдается ошибка #ЗНАЧ!. Все вложенные функции выдают верный результат, а вот функция СМЕЩ ошибку. Попробовала ваш скачать пример и применить к вашему примеру эту формулу, та же ошибка.  
17.06.2019 12:56:12
Добрый день! Подскажите, пожалуйста по проблеме: создала зависимый список по способу 1. Он в целом работает, но для одного из основных значений не выпадает зависимый список. В чём проблема понять не могу.

Всё, разобралась. опечатка в имени была
03.10.2019 19:58:39
Здравствуйте!
В связанных списках для заголовков одной таблицы использую ДВССЫЛ таким образом
=ДВССЫЛ("Таблица[#Заголовки]")
, дабы при добавлении нового столбца или изменении заголовков в динамической таблице автоматом отображались данные изменения и добавления.
Это работает, но в некоторых случаях происходит некое зависание, данный список не раскрывается, он будто не видит данных которые необходимо отобразить.
Кто может подсказать в чем может быть причина?
14.11.2019 23:07:11
Здравствуйте!
Николай, на основании способа 2, можно ли как-то дополнить формулу дабы при смещении на второй столбец отображать только уникальные данные, если в нем присутствуют идентичные наименования.
https://wampi.ru/image/6lsIrq6
29.05.2020 15:00:11
Сергей, попробуй формулу offset запихнуть в =UNIQUE()
=UNIQUE(OFFSET(...))
29.05.2020 12:59:41
Добрый день, а поженить третий пособ с Power Query можно? Вообще поддерживает ли PQ выпадающие списки, может где есть табица соответствия функций Эксель с языком М?
09.06.2020 09:28:12
Здравствуйте!
Спасибо за объяснение, помогло.
Остался вопрос, если я хочу сделать в колонке товар исключение, возможно ли сделать так.
К примеру выбирается список товара из drop down листа, но в соседнюю колонку надо чтобы добавляли не больше 6 занчений.
Пример: Выбрали Апельсин в соседней колонке не больше 6 значений, выбрали Яблоко в соседней колонки не больше 4 значений.То есть к каждому товару прикрепленно определенное количесвто значений которых может добавить пользователь.

Спасибо
01.07.2020 21:40:58
Добрый день! Сегодня создал 3 выпадающих списка, связанные друг с другом последовательно. Дал название порядка 30 диапазонам и примерно 90 ячейкам. После чего обнаружил что выпадающие списки просто пропали и создать новые в данном файле не получается(при чём даже с другими данными), при присвоении примерно 50 имени ячейкам, проверял работоспособность и правильность отображения данных списках, всё работало.   Возник вопрос, возможно есть какой то лимит количества названных диапазонов и ячеек?
24.09.2020 11:50:37
Добрый день коллеги! Помогите пожалуйста с проблемой.
Есть 5 прайсов с одинаковыми наименованиями но с разными ценами.
Необходимо: в 1-й ячейке из выпадающего списка выбрать один из 5 прайсов, во 2-й ячейке выбираем из списка необходимый пункт прайса и !в 3-й ячейке необходима автоматическая подгрузка стоимости услуги выбранной во 2-й ячейке.
Связанные (зависимые) выпадающие списки делал, не могу понять как привязать цену услуги.
19.02.2021 09:07:53
Если еще актуально.

Я подобное делал через ВПР следующим образом:

Создал две таблицы.
1. Таблица ("Данные") со всеми вариациями данных: в Вашем случае это будет выглядеть так КОЛОНКА 1(Прайс Позиция), КОЛОНКА 2(Цена)
2. Таблица ("Сложение") - в данной таблице производится сложение данных, введенных в шаблоне (скажем это у нас в ячейке G3 происходит)
=ячейка где выпадающий список с прайсом&" "&ячейка где выбираем позицию
Т.е. в шаблоне у вас получается 3 колонки:
2 с выпадающими списками: "Прайс" и "Позиция"
и 3я колонка "Цена"

Так вот, после того как вы выбрали "прайс" и "позицию", эти два значения соединяются в ячейке G3. После этого, в ячейке где должна быть цена, через ВПР производиться сравнении того, что у нас "сложилось" в G3 и таблицы "Данные". Находя соответствие, ВПР подтягивает цену.
19.02.2021 08:43:27
Добрый день, Николай.

Подскажите, реально ли совместить функцию ФИЛЬТР и СМЕЩ? После выбора (по примеру) марки машины, в моей таблице "модели" имеют на одну марку свыше 60 позиции, что соответственно не очень удобно для выпадающего списка.

Хотелось бы реализовать в поле где выбирается "модель" функцию ФИЛЬТР, чтобы после выбора марки, в модели вбить несколько букв для осуществления быстрого поиска - https://www.planetaexcel.ru/techniques/1/9645/

Или данные две возможности несовместимы?

P.S.: в принципе можно рассмотреть вариант от обратного. Выбирается сперва "Модель" с помощью ФИЛЬТР, а "Марки" сделать зависимыми от "Модели"...
20.04.2021 22:56:22
Здравствуйте!
Использовала в работе 3-й способ. Есть проблема: работает только для 1 пункта из выпадающего списка, т.е. выбираю овощи, выпадающий список соответствует овощам, выбираю фрукты или зелень, выпадающие списки пусты. В чем может быть причина?
Попробовала воспользоваться советом из комментариев и преобразовала список в таблицу, но результат - в ячейке "ЛОЖЬ".
28.06.2021 22:02:33
Всем доброго дня! А автору огромное спасибо.
Помогите пожалуйста!
У меня в t6:t100 номера заказов (многие из них повтор, неотсортирован, динамик) а на v6:v100 название производимых товаров (38*38*2 или 42*42 ну вот такие интересные имена)
создал вроде отдельный список без повторов и сортированный и динамик на отдельном листе с помощи вашего сайта. Но никак немогу заставить работает вторую выпадающую ячейку. Или не хочет или ...
  t                                  v
№ Заказа     название
2101             38*38
2103              42*32
2102              50*50
2103              50*50
2103              38*38
2105               40*42


2 способым стараяю или это невозможно
Жду хороший ответов с нетерпением
16.07.2021 14:18:24
Все доброго дня.
Вопрос к Автору и всем остальным.
Есть некий файл (Адреса проживания/прописки сотрудников).
В нем сам лист с сотрудниками, и лист справочник в котором указано какому населенному пункту какая административно-территориальная единица соответствует (лист содержит колонки: Страна, Регион, Район, Тип громады (выпадающий список),  Наименование громады, Тип населенного пункта (выпадающий список), Наименование населенного пункта). Длина листа более 30 тысяч строк.
Как на листе с списком сотрудников реализовать зависимые каскадные списки.

P.S. Должно работать в Excel 2007 и новее

С уважением,
SM
27.09.2021 14:42:42
Вот пошагово читаешь вроде все понятно, Но!
Простите никак не могу сообразить как сделать свою задачу:
Есть листы Exel  по имени Продавца
в каждом есть Товар (название) и соответственно цена
Сводный лист. Там Поле Продавец, Товар, цена
Как сделать так, чтобы можно было выбрать в Поле Продавец из листов Exel и потом поле Товар выбиралось из диапазона Товар конкретного листа, ну и соответственно поле Цена ставилось из соответствующего листа
04.10.2021 14:37:51
Доброго дня. Делала зависимый список по Способу №3, все получилось, но возникла задачка усложения формулы. В выпадающем зависимом списке есть повторения одной и той же записи, как сделать чтобы в выпадающем списке были только уникальные значения?
02.11.2021 14:54:29
Доброго времени суток.
У меня есть таблица:
А:Менеджер, Б:ФИО клиента
Есть список этих менеджеров
Подскажите как сделать список, если в таблице появился новый клиент, то вывести список всех менеджеров, если уже существующий то вывести только менеджера который уже назначен?
Пока смог вывести менеджера кто уже был через ИНДЕКС(А:А; ПОИСКПОЗ(Б2;Б:Б;0))
А если поиск не нашел, как вывести всех?
16.11.2021 17:29:20
Добрый день.
Возможно ли в выпадающем списке сделать разделитель(не выбираемый)
Пример. Выпадающий список:
Маша
Саша
-------  (<---  разделить список, и чтобы выбрать этот разделитель было не возможно
Иван
01.06.2022 10:16:04
Добрый день!
А можно сделать "протягиваемый" зависимый ниспадающий список?
У меня есть список ошибок, и для каждой нужен выбор "Причина" и зависимая от выбранной причины "Детализация".
Как реализовать зависимый ниспадающий список в нескольких строках, без создания списка в каждой строке вручную?

П.С. при записи формулы "двссыл(f2)" список выдаёт ошибку. Работает только в виде "двссыл($f$2)". А в таком случае, ссылка на ячейку не протянется.
Добрый день Николай!
А как реализовать предложенный способ3 через макрос в UserForm combobox ? Спасибо
05.12.2022 21:38:36
Здравствуйте! А как вместо последней цифры 10 подставить подсчет занятых ячеек в четвертом примере?
23.12.2022 17:05:06
У меня вопрос по 4 способу (адаптировал формулу под свои значения) и при вводе формулы в отдельную ячейку - она работает https://skr.sh/sHXRfAJwQ0k. Но как только я пытаюсь вставить ее в проверку данных  - выдает ошибку https://skr.sh/sHXmEjLGneL. В чем может быть причина?
10.05.2023 10:44:24
Добрый день!
А есть макрос, которые позволяет создать многоуровневые зависимые списки? Если есть- поделитесь пожалуйста
17.10.2023 11:22:55
Добрые люди помогите решить задачку, никак не получается выделить формулу 2х зависимого выпадающего списка.[img]C:\Users\User\Downloads\Счета Филл[/img]
СМЕЩ(D3;ПОИСКПОЗ(B3&C3;Kessebohmer!O:О&Kessebohmer!P:P;0);0;СЧЁТЕСЛИМН(Kessebohmer!O:О;B3;Kessebohmer!P:P;C3))
Список разворачивается, но пустой.
12.11.2023 20:03:42
Добрый вечер, уважаемые форумчане!
Прошу оказать помощи в решении, на первый взгляд простой задачки.
При создании связанных (зависимых) выпадающих списков при выборе показателя эффективности всё работает.
В какую сторону курить/копать, чтобы при выборе того или значения активности выбиралось необходимое, связанное с ним значение эффективности? Буду очень признателен.
ЭффективностьАктивность
Уровень1Значение 1
Уровень1Значение 2
Уровень1Значение 3
Здравствуйте!

Пробую способ 3. Все ломается на этапе ввода OFFCET. Получаю ошибку there's a problem with this formula. Причем пробовала вбить формулу в ячейку и проверить - то же самое. Выделяет ссылку на А1 и дальше никак. В чем может быть проблема?
Страницы: 1  2  3  4  
Наверх