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

8867 02.10.2012 Скачать пример

Способ 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  
Роман Морозов
21.09.2016 11:38:05
Добрый день! пользовался связанными выпадающими списками в составе динамической таблицы-очень удобно!..но совсем недавно заметил такую вещь-если удалить строку таблицы, например. с нулевым значением, то потом вновь созданная строчка сбивается и связанный список уже не отражается...((((...к сожалению. файл не могу показать...кто сталкивался и как победить?? спасибо!!!
Ссылка
Анара
06.10.2016 06:27:51
Добрый день! Большое спасибо за ваши статьи и уроки  :) Скажите пожалуйста возможно ли сделать так, чтобы при вводе номенклатурного кода, выходил список с номенклатурным кодом, при выборе которого выходил рядом и само название номенклатуры  Заранее огромнейшее спасибо !!!
Ссылка
Артём
19.10.2016 15:57:58


Добрый день, уважаемые знатоки!
Тема очень помогла и почти на 100% решает поставленную передо мной задачу, кроме одного момента:
проблема в коде создания списка с помощью Data Validation. 


 ' Сформированный словарь выгружаем в строку, разделители - запятые
  For Each key In objDict.Keys
    strList = strList & "," & CStr(key)
  Next
  
  ' отрезаем лишнюю запятую в начале
  
  strList = Mid(strList, 2)
 
  ' присваиваем ячейке список через механизм Data Validation
  With parTargetCell.Validation
    .Delete
    .Add Type:=xlValidateList, _
         AlertStyle:=xlValidAlertWarning, _
         Formula1:=strList
    .IgnoreBlank = True
    .InCellDropdown = True
  End With
  
  ' Уничтожаем объект словаря
  Set objDict = Nothing
  
End Sub


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

Сообщение   E-mail  Предупреждения
Ссылка
Дмитрий
16.12.2016 11:51:37
Добрый день.
Урок впечатлил.
Подскажите как добиться следующего результата на Вашем примере:
Выбираем "овощи" , "огурец" и дальше в определенном месте появится численные значения в двух ячейках к примеру дата поставки, масса партии.
Это возможно сделать?
Ссылка
Александр
17.03.2017 21:13:32
Доброго времени суток!
    У меня вопрос интимный...Возможно...Ексель эта штука хорошая, но сейчас вышел конкурент(конечно его сложно назвать по функционалу) но в удобстве что то есть и это ГУГЛ ТАБЛИЦЫ.
    Не пинайте что мол у нас только эксель, что гугл не принимаем...Но нужна помощь!!!! Работают ли связанные выпадающие списки в гугл таблицах через ДВССЫЛ или нет?
Попробывал по данной статье доступные способы в моем понимание, но они не работают. Хочется понять я что то делаю не так, или просто гугл не принимает данную возможность?
У гугл спаравочник ничего не говорит на данную тему, а спросить больше не у кого.
Спасибо за помощь.
Ссылка
Влад Иголкин
30.03.2017 16:40:59
Николай, в Вашей книге "Готовые решения - бери и пользуйся", в примере со списками,  в формуле СМЕЩ,  использовалась комбинация ...;ПОИСКПОЗ() - 1; ...

=СМЕЩ($A$1;ПОИСКПОЗ($G$7;$A:$A;0)-1;1;СЧЁТЕСЛИ($A:$A; $G$7);1)
я так понимаю, для учета заголовков таблицы (?!), но при этом в примере - заголовков не было. Можете пояснить?


Спасибо. Надеюсь данный вопрос не повторяю.
Ссылка
Andrey Martyn
27.04.2017 10:20:01
Большущее спасибо за статью и видео. Все получилось!

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

Пример: Выбор Фруктов - подтягивает в первый зависимый столбец - список со всеми Фруктами, во второй зависимый - список с Сортом фруктов и в  третий - с Категорией качества фруктов
При выборе Овощей - соответственно выпадают списки из другого набора для Овощей (Вид овоща, Сорт, Категория) и т.д.

То есть ДВССЫЛ нужно не на просто список значений направить, а на список ссылок на списки значений??
Пожалуйста, подскажите. Никак на могу осилить эту матрицу...
Ссылка
Игорь Петричук
11.06.2017 00:30:21
Посмотри ниже. У меня аналогичная задача была. По идее тебе должно помочь
Родитель Ссылка
Павел Чебанов
06.06.2017 16:17:37
Добрый день!
Есть три связанные выпадающие списки, через ДВССЫЛ. 1)Федеральный округ 2)Регион 3)Должности.
Как в 4 столбце "Факт должности", сделать вычисления суммирования, но так чтобы, при смене условий (Округ, регион,должности), менялись и значения суммирования???
Ссылка
Игорь Петричук
11.06.2017 00:28:40
У меня была необходимость создать связанные списки на рабочем листе со справочником на листе справочников. Идея такая: пустую таблицу с разными группами, видами, марками и моделями транспорта, а также гос. или инв. номерами надо заполнить с помощью выпадающих списков. В общем защищался от обезьян, любящих добавить лишние пробелы, тире и т.п.
Сегодня победил не без основной статьи.

задача по транспорту

Пришлось не много помучиться. Прошу оценить мучения.
Ссылка
Alexandr Denisov
14.06.2017 09:03:37
Здравствуйте. Помогите с проблемой.
Необходимо, чтобы когда менеджер выбирает день месяца - в соседнем столбце в выпадающем списке были доступны только те машины, которые отметили как "доступные"  в этот день (и еще надо, чтобы пустых строк не было в выпадающем перечне)

Для этого сделал перечень авто и возможность, чтобы человек вручную по дням отмечал, какие из них будут доступны (т.к. надо чтобы можно было на сегодня и например на 3 дня вперед отметить доступные авто). После этого сделал еще одну таблицу в разрезе дней, куда переносятся доступные авто без пробелом и для каждого дня создал именованный диапазон (с помощью СМЕЩ, чтобы в конце не было пустых ячеек выпадающем списке).
Диапазоны каждого дня назвал д1, д2 ... д31.
И теперь когда  пытаюсь привязать эти диапазоны к Дате с помощью ДВССЫЛ, то выпадает ошибка в источнике (как я понимаю, т.к. ДВССЫЛ и СМЕЩ не совместимы?)
Что можно еще придумать, как можно сразу в источник прописать формулу со СМЕЩ, чтобы при выборе дня месяца - сразу выбирался перечень доступных машин этого дня.
Без СМЕЩ всё получается, но тогда пустые ячейки в перечне.
Буду очень благодарен за помощь. Вот файл эксель.
Ссылка
Sergei Shuvalov
21.06.2017 15:49:32
Ваще рок-н-ролл! ))) Спасибо за классный приём. ))
Ссылка
Иван Л
09.11.2017 12:32:20
Здравствуйте! Благодарю за инструкцию, всё работает!

Использовал Способ № 3. Николай, подскажите пожалуйста, как не привязывать выпадающий список к ячейке, а оставить привязку только к столбцу? Т.е., в ваших примерах везде используется привязка зависимого выпадающего списка к ячейке и нет возможности растянуть список вниз, а хотелось бы.

Более подробно изложил на форуме: ссылка
Спасибо!
Ссылка
Саят Мейр
21.12.2017 07:57:26
Добрый день, спасибо за замечательный урок. Однако у меня такая проблема, есть ситуация когда нужно 6 связанных списков. То есть к примеру: марка машины- модель- двигатель- тип трансмиссии- номер ТО и в итоге должна выпадать информация по этому ТО, объем стоимость и тд.
Так вот, на этапе составления третьего пункта есть ситуация когда одинаковые двигатели стоят на разных марках авто. То же самое с коробкой, там в основном две версии акпп и мкпп, и при выпадении списка выходит так что выпадает все сразу, не только двигатель привязанный к этой модели и тд. Не знаю как решить проблему.  
Ссылка
Иван Чуванов
23.12.2017 10:24:35
У меня в при способе 3 почему то ошибка выскакивает. При задании имени все проходит хорошо диапазон выделяется какой надо
Формула

Смещ(I11;ПОИСКПОЗ(M$12;Таблица4[Группа 1];0);1;СЧЁТЕСЛИ(Таблица4[Группа 1];$M12);1)
или тоже
Смещ(Таблица4[[#Заголовки];[Группа 1]];ПОИСКПОЗ(M$12;Таблица4[Группа 1];0);1;СЧЁТЕСЛИ(Таблица4[Группа 1];$M12);1)

Однако при задании выбора из списка выскакивает ошибка при определении источника данных что это может быть подскажите пожалуйста
Ссылка
Ирина Александрова
08.02.2018 17:40:35
Добрый день! Подскажите дилетанту..
Есть книга - листы приход, расход - выглядят таблицы как в третьем примере. Нужно в расходе настроить выпадающие списки производителя и соответствующих ему артикулов, Но! нет возможности сортировать лист прихода от А до Я, потому как работают с ним несколько людей и им в первую очередь нужна сортировка по датам прихода. Первый список - все понятно, производителей не так много - сделаем на своем листе расхода вручную. А вот как быть с артикулами? Их много и нужно чтобы список выводился из дополняющегося листа прихода товара.
Ссылка
520376Marina Bovt
23.02.2018 15:33:13
Здравствуйте. Подскажите, как быть, если первый список состоит из нескольких слов. Например,
Бахіли медичні одноразові
Бахіли технологічні безворсові на твердій підошві
Беруши 3M у пластиковому чохлі
Білизна натільна
Т.е. для поименованости связанных списков для каждой строки они не подходят.

Пытаюсь сделать с использованием умной таблицы, но не пойму, как делать на нее ссылку  при валидации данных
Ссылка
Симоненков Фёдор
12.03.2018 23:01:16
Одно небольшое уточнение:
В качестве начальной ячейки для функции СМЕЩ необходимо задавать не верхнюю угловую ячейку таблицы, а на одну ячейку выше, иначе при расчёте искомый диапазон смещается на одну ячейку вниз и выводиться неточно, захватывая снизу лишнее, а верхнюю строку обрезая.
Ссылка
Максим Любимов
17.04.2018 15:59:02
Здравствуйте.
Подскажите, пожалуйста, как работает Способ 1.5 из прикрепленного файла.
Ссылка
Виктор Ом
08.05.2018 13:59:59
Здравствуйте,
прошу помочь организовать список в ячейке листа, при условии:
Список состоит из двух ячеек: 1. описание цвета (Желтый, синий, зеленый, желтый) 2. значение цвета (1,2,3,4),
Задача: организовать выпадающий список по цветам с присвоенным значением, с суммированием значений цветов:
Желтый- 1
Зеленый-3
Итого-     4
Заранее благодарю,
Ссылка
Игорь Хоменко
21.05.2018 16:30:47
Добрый день.
У меня вопрос, а как добавить 3 список, который зависит от первого выбора? К примеру Ниссан Альмера автомат или механика?

Спасибо.
Ссылка
Arthur C.
26.06.2018 12:24:30


Ребята нужно вот такую таблицу сделать взаимозависимой .. на выходе что было
Выбор Individ. -> Название курса -> Цена (цена это конечная информация без списка должна отображаться)

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

Всё, разобралась. опечатка в имени была
Ссылка
Страницы: 1  2  3  4  
Наверх