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

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

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

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

linked_dropdowns.gif

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

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

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

linked_dropdowns2.gif

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

=ДВССЫЛ(F3)

или =INDIRECT(F3)

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

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

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

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

Способ 2. Список соответствий и функции СМЕЩ (OFFSET) и ПОИСКПОЗ (MATCH)

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

linked_dropdowns3.gif

 

Для создания первичного выпадающего списка можно марок можно воспользоваться обычным способом, описанным выше, т.е. 

  • дать имя диапазону D1:D3 (например Марки) с помощью Диспетчера имен (Name Manager) с вкладки Формулы (Formulas) или в старых версиях Excel - через меню Вставка - Имя - Присвоить (Insert - Name - Define)
  • выбрать на вкладке Данные (Data) команду Проверка данных (Data validation)
  • выбрать из выпадающего списка вариант проверки Список (List) и указать в качестве Источника (Source) =Марки или просто выделить ячейки D1:D3 (если они на том же листе, где список).

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

  • Нажмите Ctrl+F3 или воспользуйтесь кнопкой Диспетчер имен (Name manager) на вкладке Формулы (Formulas). В версиях до 2003 это была команда меню Вставка - Имя - Присвоить (Insert - Name - Define)
  • Создайте новый именованный диапазон с любым именем (например Модели) и в поле Ссылка (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) или в меню Данные - Проверка (Data - Validation)
  • из выпадающего списка выбираем вариант проверки Список (List) и вводим в качестве Источника (Source) знак равно и имя нашего диапазона, т.е.  =Модели

Вуаля!

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




Страницы: 1  2  
Катерина
03.10.2012 23:40:46
Отличная вещь!
Народ, спасайте, как все-таки размножить данную функцию на множество ячеек.
При простом копировании формулы зависимый список не открывается вообще.
Валерий
03.10.2012 23:41:23
Данные/Проверка/Источник:
В формуле =ДВССЫЛ($G$7)перед копированием ячейки убрать $:=ДВССЫЛ(G7). И будет Вам счастье..
Andrey2012
03.10.2012 23:43:33
Можно сделать динамически изменяющимся и способ №1 (ячейки и имена листов как в примере).
для 1 ячейки с маркой машин (F3) при создании источника данных использовать имя диапазона (в моем примере "Фирмы")со следующим источником:

=СМЕЩ('Способ1'!$A$2;0;0;1;СЧЁТЗ('Способ1'!$2:$2))
Для 2 ячейки с моделью машин (F4) при создании источника данных использовать имя диапазона со следующим источником:
=СМЕЩ('Способ1'!$A$2;1;ПОИСКПОЗ('Способ1'!$F$3;Фирмы;0)-1;СЧЁТЗ(ДВССЫЛ(АДРЕС(3;ПОИСКПОЗ('Способ1'!$F$3;Фирмы;0);;1;"Способ1")&":"&АДРЕС(20000;ПОИСКПОЗ('Способ1'!$F$3;Фирмы;0);;1)));1)

Кстати, не обязательно вручную вбивать длинные формулы в описание источника диапазона. Правая кнопка мыши не работает, зато копирование и вставка на панели и в меню доступны и работают, когда непосредственно редактируешь формулу источника данных.
Если немного усложнить, то можно сделать автоматизированный ввод новых элементов, как в примере "Выпадающий список с добавлением новых элементов". В таком случае будет рабочий двухуровневый список с автоматическим добавлением новых элементов 1-го и 2-го уровня.
СергейКА
03.10.2012 23:46:29
Разобрался в той ошибке, что возникает у многих ("При вычислении "Источник" возникает ошибка.")
Прав оказался Тема (письмо от 31.01.2009) - функция ДВССЫЛ (INDIRECT) напрочь НЕ УМЕЕТ РАБОТАТЬ с динамическими массивами, точнее она не умеет работать ни с чем, что ТРЕБУЕТ ВЫЧИСЛЕНИЯ при её вызове.
Поэтому, если вы пытаетесь совместить "в одном флаконе" советы
по созданию динамических именованных диапазонов (т.е. определяемых через строку типа =СМЕЩ(Лист1!$A$1;1;0;СЧЁТЗ(Лист1!$A:$A)-1;1))
и
по созданию связанных выпадающих списков (как в этой теме), то это
НЕ ПОЛУЧИТСЯ!
Создание связанных выпадающих списков с использованием функции ДВССЫЛ (INDIRECT) и именованных диапазонов возможно только для диапазонов, заданных напрямую, без использования дополнительных вычислений.
Но! :)
Не всё так уж безнадёжно ;)
Помучившись нашёл выход:
1. не надо использовать именованные динамические диапазоны (от них в данном случае нет пользы)
2. если функция INDIRECT не умеет вычислять, то достаточно зараннее вычислить динамический диапазон самому!
Если взять тот пример, что указан в данной теме, то надо
создать ячейку (пусть, например E3), которая вычислит примерно такую формулу:
=ADDRESS(1;1;1;1)&":"&ADDRESS(1;COUNTA($1:$1);1;1)
и при создании первого выпадающего списка (скажем, в ячейке F3) откройте меню Данные - Проверка (Data - Validation), затем из выпадающего списка Тип данных выберите вариант Список и в поле Источник введите формулу
=ДВССЫЛ(E3)
(=INDIRECT(E3))
где E3 - адрес ячейки с вычисленным адресом диапазона, содержащего наименования производителей авто.
Далее перед созданием второго выпадающего списка надо создать ячейку (скажем, G3), в которую поместить вычисление адреса того диапазона, который выбран в первом выпадающем списке.
Это делается примерно такой формулой:
=ADDRESS(2;MATCH($F3;$1:$1;0);1;1)&":"&ADDRESS(COUNTA(INDIRECT(ADDRESS(2;MATCH($F3;$1:$1;0);1;1)&":"&ADDRESS(999;MATCH($F3;$1:$1;0);1;1)));MATCH($F3;$1:$1;0);1)
Если в первом выпадающем списке выбрано какое-либо значение, то вычисленный адрес диапазона марок должен выглядеть примерно так:
$A$2:$A$25
иначе так
#N/A
Не пугайтесь :)
Тогда в при создании второго выпадающего списка в поле Источник (Данные - Проверка (Data - Validation) - Список - Источник) надо ввести формулу
=ДВССЫЛ(G3)
где G3 - адрес ячейки с вычисленным адресом диапазона.
Вот так вот. Кривовато, но что сделаешь, если имеешь дело с кривоватыми мелкомягкими функциями ....
Зато работает :)
Кстати, такой способ позволяет и размножать строки с выпадающими списками и увеличивать количество и размер списков (производителей и их марок), ничего нигде дополнительно не описывая и не меняя. Кроме всего прочего, можно списки производителей и их марок поместить на один лист, а выпадающие списки - на другой лист.
24.08.2015 19:13:38
Вам надо хорошенько изучить функцию INDEX
Dana
03.10.2012 23:48:57
Добрый день! У меня такая задача: есть выпадающий список всех регионов страны, также есть список федеральных округов, нужно сделать так, чтобы при выборе региона из выпадающего списка в соседней ячейке автоматически появлялся соответствующий региону федеральный округ. Подскажите, пожалуйста, как решить. Спасибо!.
25.12.2012 22:13:09
Мне кажется, проще для второго списка форматировать данные (Модель) как таблицу! А в Проверку данных вставить такую формулу:

=ЕСЛИ($G7="Toyota";ДВССЫЛ("Таблица1[Toyota]");ЕСЛИ($G7="Ford";ДВССЫЛ("Таблица1[Ford]");
ДВССЫЛ("Таблица1[Nissan]"))

В квадратных скобках названия столбцов Таблицы1. При добавлении моделей в таблицу они автоматически появляются в выпадающем списке.
У этого способа есть и свои недостатки:
1. Ограничена длина формулы, которая вставляется в Проверку данных;
2. Если один столбец заполнен больше другого, то при выборе марки с числом моделей меньше, будут видны пустые строки.
02.01.2013 23:46:24
Да, тоже вариант - и иногда неплохой.
А чтобы не было пустых лишних строк можно использовать разные таблицы, а не разные столбцы в одной таблице.
08.09.2014 09:40:19
Вариант очень неплох, недостатки перечисленные Вами устранил. Итак:
2) Создал несколько "умных таблиц", по совету Николая Павлова (см. выше)
1) В таблицах выделил диапазоны с моделями и назвал каждый по названиям марок как было описано в способе 1(для удобства чтобы не перемешались с вариантами из способа1 данной темы назвал их Toyota1, Ford1 и Nissan1). Тогда новые имена будут иметь адреса диапазонов =Таблица1[Toyota];=Таблица2[Ford] и =Таблица3[Nissan]. И в итоге источник для выпадающего списка с моделями будет иметь формулу =ДВССЫЛ(F3&1). Тогда не страшна ограничительная длина формулы. Сохраняется лишь тот минус, что приходится создавать много именованных диапазонов.
PS: знак &1 прикрепляет "1" к моим именам диапазонов
При этом список моделей может увеличиваться (выпадающий список будет пополняться автоматически)
02.01.2013 23:36:32
В моём случае метод работает ( у меня соответствие названия услуги и тарифов), но если я сначала выбираю например услугу №1, и из списка подставляю ей соответствующий тариф (пусть будет, например, тариф№1-2), то далее, если я меняю услугу №1 например на услугу №2 (даже если у нее только 1 тариф) то в списке тарифов остается ранее выбранный тариф№1-2). в принципе не очень напрягает, но можно-ли как-то очищать поле для подстановки тарифов после выбора названия услуги? Или подставлять первый тариф из выбранного списка по-умолчанию?
02.01.2013 23:44:40
Да, для этого придется использовать несложный макрос. Щелкаем правой по ярлычку листа со списками, выбираем команду Исходный текст и вставляем туда такой код:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
     If Target.Address(False, False) = "A2" Then Range("A1").ClearContents
End Sub
Предполагается, что первый список (услуги) у вас в А1, а второй (зависимые тарифы) в А2.
14.08.2013 19:57:15
Николай, а если в качестве первого выпадающего списка у меня поле фильтра сводной таблицы, то как нужно изменить этот код, чтобы он работал?
27.01.2014 11:24:26
Николай, добрый день. спасибо за полезную информацию. очень помогает.
не могли бы вы ответить на такой вопрос:
задача та же что и выше (обнулять зависимый список)
но у меня ячейка объединенная. макрос работает если разъединить, а вот с объединенными не хочет. не могли бы вы подсказать решение (если оно есть). спасибо.
06.04.2016 14:32:54
Николай, а как изменить код, если такую проверку надо сделать не в одной связке ячеек, а в большом количестве?
25.04.2016 15:03:19
Подскажите, пожалуйста, как этот макрос приспособить для двух столбцов?
В столбце А - услуга, в столбце В - тариф.
Заранее спасибо.
03.01.2013 00:16:45
Случайно попал на один заграничный сайт, что только не творят с Data Validation..... (не без помощи VBA, конечно)
http://www.contextures.com/excel-data-validation-multiple.html

Интересно!
29.01.2013 01:30:38
Это не просто заграничный сайт, а Дебора Далглейш - мегаспец по сводным таблицам и не только. Весьма известная личность. На этот сайт ссылочка, кстати, давно есть в разделе Ссылки - уж лет 5 как :)
31.01.2013 14:43:13
А к способу 2 можно прикрутить добавление новых элементов? или к первому способу?
11.02.2013 16:26:53
Да, если использовать Таблицы - см. комментарий Анны ниже http://planetaexcel.ru/techniques/1/38/#825
07.02.2013 07:00:19
Help! Долго уже бьюсь над этой проблемой, может кто знает решение...
Итак. Представьте себе, что на рис.3 сего примера перед столбцом "А" есть еще столбец в котором содержатся типы авто (купе, седан, внедорожник и т.п.), и в итоге мы получаем список повторяющихся фирм производителей (столбец "А", а необходим список уникальных значений. В этом и состоит мой вопрос: как реализовать фильтрацию посредством формулы, а ни в коим случаи макросов, которые безусловно имею место быть.

Я уже собрал кое-какую формулу для фильтрации значений:
{=ЕСЛИОШИБКА(ИНДЕКС(list;ПОИСКПОЗ(0;СЧЁТЕСЛИ($B$1:B1;list);0));"")}


AB
1ЗначенияУникальные
2aa

3
bb
4cc
5a
, где синим цветом выделен диапозон "list", а формула содержится в столбце "В" в виде массива.
Пытался подставить формулу Смещения, описанную в данном примере на место именованного диапозона "list", но чем заменить диапозон "$B$1:B1" не знаю.
05.09.2014 13:51:55
Возможно вот эта статья как раз то, что Вам нужно, сам ей недавно воспользовался, третий способ особенно хорош, правда и особенно сложнее предыдущих, но разобраться можно

Извлечение уникальных
11.02.2013 15:07:37
Если каждый список с моделями машин отформатировать "как таблицу" вместо обычного присвоения имени диапазону, тогда и зависимые списки будут подстравиваться в соответствии с изменениями в табличках (в случае с использованием формулы ДВССЫЛ)
11.02.2013 16:25:54
Да, Анна, это хороший вариант. Динамические именованные диапазоны с функцией СМЕЩ в подчиненных списках не работают, но "умные таблицы" (Главная - Форматировать как таблицу) применять можно. Жаль, что в старых версиях команды "Форматировать как таблицу" не было.
21.02.2013 17:34:33
Добрый день. Кто нибудь подскажите как распространить взаимосвязанный список (реализуемый через способ 2) на несколько строчек ниже ?

например в ячейке А1 - располагается марка, в ячейке В1 - располагается модель.
Как распространить взаимосвязанный список на ячейки А2 и В2, А3 и В3, и т.д.

Спасибо.
09.03.2013 09:28:26
Да все то же самое и будет. Только в формуле:
=СМЕЩ($A$1;ПОИСКПОЗ($G$7;$A:$A;0)-1;1;СЧЁТЕСЛИ($A:$A;$G$7);1)
нужно будет убрать абсолютную адресацию с ячейки G7:
=СМЕЩ($A$1;ПОИСКПОЗ(G7;$A:$A;0)-1;1;СЧЁТЕСЛИ($A:$A;G7);1)

чтобы ссылка на марку бралась из соседней ячейки.
А потом копируете обе ячейки с маркой и моделью вниз - настолько, насколько вам надо.
16.03.2013 12:16:29
К сожалению, не работает. пробовал внести указанные изменения в приложенный к уроку пример - перестает работать второй (зависимый список)

Так что вопрос остается открытым
16.03.2013 13:57:54
Перенесите ячейку G8 на H7.
Уберите доллары в именованном диапазоне Модель в адресе ($G$7 -> G7)
Скопируйте обе ячейки G7 и H7 вниз.

Все работает - вопрос закрыт ;)
16.03.2013 14:53:28
Извините, действительно все работает и работало изначально. Ошибка - моя, я не понимал, что в момент редактирования именованного диапазона (ctr+f3) активной должна быть именно ячейка в которой выпадает зависимый список.
11.03.2013 10:40:32
СергейКА, огромное спасибо! Собрал такой костыль и добился желаемого результата.

Что касается способа с использованием "умной таблицы", то его недостаток в том, что данные в таблицу необходимо заносить только вручную (т.е. формулой пополнять таблицу не получится - таблица не увидит новые значения).
27.03.2013 18:52:05
Здравствуйте! У меня такая проблема: есть таблица в которой будет два связанных выпадающих списка. Как сделать первый все понятно, как сделать второй - тоже - присваиваю имена разным диапазонам соответствующие первому выпадающему списку, все работает. Но проблема в том, что файл будут заполнять разные отделы у которых первый и второй список отличаются. Например, у одного это овощи (в зависимом списке огурец, помидор и проч.), фрукты ( апельсин, яблоко), орехи (миндаль, арахис). А другой отдел будет заносить конфеты (в зависимом перечне "ласточка", "маска" и проч), шоколад (Марс, Сникерс и проч.). Соответственно имена диапазонов будут уже другие. Люди, которые будут работать с файлом, присваивать имена не умеют. Может есть возможность присваивать имена диапазонам, которые сами могут меняться? )))
11.04.2013 08:11:50
Такое только макросом вида Range("A2:A10").Name="Конфетки"
А почему бы вам не сделать заранее несколько разных выпадающих списков со всеми категориями (Овощи, Конфеты, Шоколад и т.д.) сразу. Пусть каждый отдел выбирает свое. Или будут косячить? :D
11.04.2013 08:24:18
Сделать сразу списки со всеми категориями не удобно, так как у каждого отдела свои списки, которые я заранее не знаю. Т.е. нужно было придумать чтоб сотрудники отделов сами изначально заносили в справочник всю инфо, а потом работали с выпадающим списком. Мне на форуме подсказали как сделать без макросов. Вот ссылка, может кому-то пригодится http://planetaexcel.ru/forum/?PAGE_NAME=read&FID=1&TID=47056
11.04.2013 09:08:26
А, вон чего вы хотели :) Да, без макросов такое можно - некая вариация 2-го способа. Слэн - крут ;)
05.04.2013 17:39:50
Здравствуйте.Я раньше не имел потребности заниматься Экселем,но сейчас мне это очень необходимо.
Понадобилось сделать таблицу с выпадающими списками,у меня это получилось по способу 2(но без возможности добавлять новые строчки списка прямо в ячейку).Но это не беда,мне подходит и такой вариант.Большая проблема для меня в другом.Я не вижу практического применения голого выпадающего списка(как в примере с именами),также не вижу смысла применения голого связанного списка(как в примере с марками и моделями машин).Мне,и думаю всем другим кто пользуется такими списками, нужно нечто другое. Может то что я сейчас напишу для вас покажется дилетантством,но я не постесняюсь спросить хотя бы потому что больше негде искать ответа и помощи.Не судите строго,я не продвинутый пользователь Exel пока что.Итак мне от такой таблицы с выпадающими списками нужно следующее:например в примере с машинами после выбора марки в первом выпадающем списке и после выбора модели во втором выпадающем списке в этой же строке в последующих пяти,десяти или даже двадцати ячейках должна появиться информация типа-цвет машины,цена,скорость,объем двигателя,расход топлива и другая подобная ей информация. Каждому параметру естественно будет соответствовать название столбца.Тогда при смене марки-модели в таблице будет меняться всё связанное с конкретной моделью.Я понимаю что для всей этой информации мне потребуется создать на отдельном листе что-то с названием библиотека или база данных,меня это не пугает я осилю такую работу.Но вот заставить эту информацию появляться в нужном месте после смены параметра в выпадающем списке я не могу додуматься как сделать такое.Возможно для вас этот ответ очевиден,раз никто не спрашивает об этом,я прочитал почти весь форум на тему выпадающих списков и не нашел ответа.
Если вас не затруднит я вас очень прошу ответьте мне на мой вопрос,желательно с примером,я буду очень благодарен .
11.04.2013 08:14:34
Леонид, для подтягивания к выбранному в выпадающем списке названию остальных подробных данных из библиотеки можно использовать функцию ВПР. Посмотрите видео - все сразу прояснится.
11.04.2013 15:49:41
Ещё один вариант для выпадающих списков без дополнительных диапазонов и с возможностью ввода новых данных и столбцов: тыц
13.04.2013 12:56:25
здравствуйте, во втором способе всё делаем на одном листе, а как заставить работать эти ячейки на другом????
17.05.2013 11:57:13
Проблема.
Мне необходимо указать не один, а много раз марку и модель. Т.е. Марка1 Модель1, Марка2 Модель2 и т.д. список, где в каждой ячейке выбирается значение из выпадающего списка. В примере это сделано только один раз (Марка1 Моедль1). Если рядом сделать вторые выпадающие списки с маркой и моделью (Марка2 Модель2), то выпадающий список с моделью (Модель2) будет реагировать всё равно на выбор в первом списке марки (Марка1).
Единственный способ решения, который вижу я, это создание новое "define name" для каждой новой строки с заменой в формуле следующего:

=OFFSET($A$1;MATCH($G$7;$A:$A;0)-1;1;COUNTIF($A:$A;$G$7);1)
на
=OFFSET($A$1;MATCH($Н$7;$A:$A;0)-1;1;COUNTIF($A:$A;$Н$7);1) (для Марка2 Модель2)
затем
=OFFSET($A$1;MATCH($I$7;$A:$A;0)-1;1;COUNTIF($A:$A;$I$7);1) (для Марка3 Модель3)
и т.д.

Как решить это более автоматически.
17.05.2013 12:06:31
Зачем так сложно? Просто используйте относительную ссылку на G7 вместо абсолютной $G$7:
=OFFSET($A$1;MATCH(G7;$A:$A;0)-1;1;COUNTIF($A:$A;G7);1)
и потом спокойно копируйте обе ячейки G7 и G8 вправо
20.05.2013 10:25:23
Спасибо, Работает.
2-я проблема.
Если и таблицу возможных вариантов и наш конкретный список располагать на одном листе, то работает отлично.
Если располагать на разных для удобства, то тоже работает отлично.

=OFFSET(Sheet0!$A$1;MATCH(Sheet1!G7;Sheet0!$A:$A;0)-1;1;COUNTIF(Sheet0!$A:$A;Sheet1!G7);1)

Если нужен ряд листов с подобными списками (Разными - например план продаж за день - один лист), то при копировании на Sheet2! второй зависимый список (Модель Sheet2!) будет всё ещё ссылаться на первый список (Марка Sheet1!).

Опять-таки единственным выходом, который я вижу, это создавать новое имя "Define Name" для каждого нового листа.

=OFFSET(Sheet0!$A$1;MATCH(Sheet2!G7;Sheet0!$A:$A;0)-1;1;COUNTIF(Sheet0!$A:$A;Sheet2!G7);1)

=OFFSET(Sheet0!$A$1;MATCH(Sheet3!G7;Sheet0!$A:$A;0)-1;1;COUNTIF(Sheet0!$A:$A;Sheet3!G7);1) и т.д.

Подскажите, как решить данный вопрос с более простым созданием каждого следующего листа.
07.01.2014 01:09:34
Николай, доброго времени суток!

Пытаюсь сделать третий выпадающий список на примере способа 2 из колонки "С" и столкнулся с проблемой.
При повторяющихся значениях в колонке "В" оные отображаются во втором выпадающем списке! - Возможно ли как-нибудь убрать эти повторяющиеся значения формулой в списке без удаления повторений из колонки "В"?

Например, у Toyota (колонка - "А";) три Altezza (колонка - "В";) с тремя разными движками в колонке "C"


Спасибо!
08.01.2014 10:09:05
Легкого решения тут не будет. Либо делать отдельные списка для моделей, либо удалять дубликаты в моделях.
30.01.2014 12:18:22
Может стоит создать список в столбце В вида "Альтезза 1.6", "Альтезза 2.0" и т.д.?
25.05.2013 23:16:01
Замечательно удобная функция! Скажите пожалуйста, а если мне нужно "привязать" к первым двум спискам третий? То есть у меня содержание второго раскрывающегося списка зависит от значения первого (и это у меня получилось с Вашей помощью), но моя дальнейшая задача создать третий расрывающийся список, содержание которого будет зависеть от значения второго... Буду благодарна за совет.
13.06.2013 09:29:35
Анна, я бы продолжил "копать" на вашем месте в направлении Способа 2. Т.е. добавил третий столбец с модификациями автомобилей и прописал еще один именованный диапазон по тому же принципу, что и Модель.
09.10.2014 00:14:42
У меня похожая проблема. Пытался решить через способ 2.
Моя таблица вида:

А| B | C
а| 1 | а
а| 2 | а
а| 2 | б
а| 2 | в

Для столбца "А" мы создаем отдельный столбец с уникальными значениями. Т.е. в выпадающем списке уникальные значения.
А вот со столбцом "B" сложней, в вываливающимся списке мы видим "1" и далее повторяющиеся три раза значения "2". Т.е. формула не группирует их. В результате список получается очень длинным и работать с ним не удобно.
Со столбцом "C" проблем нет.
Знаете как справиться с этой задачей?
11.06.2013 14:21:47
Добрый день.Скачал Ваш пример, все работает. Пытаюсь добавить в листе "Способ1", еще две ячейки с выпадающими списками, с источниками : =$A$1:$C$1 и =ДВССЫЛ(+$F$17) соответственно. При сохранении =ДВССЫЛ(+$F$17) выдается ошибка «При вычислении «Источник» возникает ошибка». В листах ничего не менял, все как есть. подскажите, пожалуйста, в чем может быть проблема?
13.06.2013 09:25:41
А зачем плюс перед $F$17?
13.06.2013 09:33:30
При выборе источника если кликнуть по нужной ячейки, система сама плюс подставляет. Но при удалении плиса ничего не изменяется,«При вычислении «Источник» возникает ошибка» все равно вылезает.
13.06.2013 09:50:48
Олег, "сам" плюсик может появляться тут только в одном случае - если вы скобку после ДВССЫЛ забыли открыть. Ошибка же может возникать, если у вас предыдущая ячейка, откуда берется имя диапазона (F17), пока пустая. Как только введете в нее имя - все заработает.
13.06.2013 10:09:58
Да, так и есть, плюс ставится без скобки, но потом удаляю его и ставлю скобки. Очень странно, я первым делам в первую ячейку, в качестве источника вставляю диапазон =$A$1:$C$1 и стразу выбираю значение, например Ford , во второй ставлю =ДВССЫЛ($F$17), тогда ошибка не вылетает. Если же при в первом шаге значение не выбирать, то ошибка возникает.
В Вашем файлике все решилось, но при попытки повторить тоже самое в моем документе, возникает вышеуказанная ошибка. Может быть проблема во включенных макросах?
08.01.2014 10:11:50
Точно не в макросах :)
11.06.2013 16:24:11
Добрый день. Вопрос такой:
Можно ли сделать так, чтобы при выборе значения в первой ячейке (G7 из Способа2), в зависимой ячейке появлялось одно (например первое) значение из соответствующего зависимого списка? Иными словами, значение по умолчанию?
13.06.2013 09:23:50
Только макросом: отслеживать изменение первого списка и подставлять в зависимую ячейку первый элемент из второго.
13.06.2013 19:04:19
Спасибо, похоже, без VBA и не туды и не сюды. Пользуясь случаем, хочу поблагодарить за Ваш отличный ресурс. Еще бы от Вас пару учебничков таких же доходчивых, а то что всё Уокенбах, да Уокенбах. Успехов!
02.07.2013 10:22:53
Спасибо, Александр! Без VBA на трудных задачах тяжеловато, конечно :)
25.06.2013 17:30:03
Здравствуйте. Использовала таблицу для второго списка. Но почему при добавлении в таблицу данных они не появляются в выпадающем списке? Причем при удалении, они пропадают.
02.07.2013 10:21:46
При использовании Таблиц в качестве источников для выпадающих списков нужна другая технология.
28.06.2013 10:50:41
Здравствуйте! У меня такая же проблема как у многих выше. Надо осуществить выбор Город-улица-дом и чтобы списки были связанные. Но при этом адреска на отдельном листе динамичная. Что я сделала в начале (потом почему-то всё частично сломалось). С помощью VBA сформировала уникальный список городов, затем пошла по второму способу. Убрала $ и размножила формулу вниз. Но ссылки постоянно сбиваются((((

Тогда я пошла по другому пути макросом создала табличку для ДВССЫЛ... Но некоторые города содержат пробелы....

И вот Вы написали, что можно макросом отслеживать изменение первого списка. А не подскажите как? Моё предположение - написать событие "Worksheet_SelectionChange" в теле листа... Что-то типа Если выбран такой город, то = такому-то Range. Подскажите, пожалуйста?
15.07.2013 10:30:57
Добрый день.
У меня вопрос следующий. Я сделал по способу №1 все прекрасно работает, но как сделать так, чтобы при изменении выбора в первой ячейки, автоматически обнулялась вторая ячейка?
То есть, изменяется значение в первой ячейке, а данные во второй остаются без изменения, нужно чтобы изменялись или обнулялись.
26.07.2013 10:57:09
Уже задавали такой вопрос - см. http://planetaexcel.ru/techniques/1/38/#532
28.07.2013 12:47:37
Добрый день
стоит задача такого рода, нужно связанные списки, но что бы при выборе из первого сразу подставлялось со второго и если во втором более одного элемента то уже выбирать ручками или что бы второй список самораскрывался для последующего выбора (что предпочтительней). а также нужно что бы можно было выбирать со второго списка в обход первого и первый не обязательно заполнять.
расскажу на примере:
фирма А получает через грузоперевозки груз от от диапазона фирм (у всех есть свое название). но эти фирмы отправлят груз не от своего имени а по фамилии директора или же водителя. вот поэтому надо чтобы в первом списке были фамилии, при выборе необходимой во втором сразу подставлялось значение или же сразу выбрать из второго списка название фирмы не выбирая фамилиии. ну как то так
16.08.2013 16:13:16
Добрый день!
Огромное спасибо за материал!
21.08.2013 15:50:26
Добрый день, большое спасибо за такую полезную информацию.
Я создала два таких зависимых списка (по таблице с численностью населения, порядок списков: область-населенный пункт-район), но во втором столбце встречаются повторяющие значения, т.к. в разных областях есть города с одинаковым названием. Из-за этого в третьем списке выпадает значение района, идущего первым по порядку, а за ним то значение, которое находится в столбце с районами в строке ниже. Как исправить эту ошибку и сделать так, чтобы третий список "видел" все одинаковые значения городов во втором и в нем можно было выбрать один из тех районов, в которых есть такой город.
Заранее огромное спасибо за помощь!
04.01.2014 16:38:01
http://excel2.ru/articles/mnogourovnevyy-svyazannyy-spisok
решение на указанной странице не подходит.
Есть другой способ?
шаг1 ) в ячейке А1 выбираем регион из предложенного списка
шаг2 ) в ячейке А2 выбираем страну из предложенного списка который зависит от выбранного региона
шаг3 ) в ячейке А3 выбираем город из предложенного списка который зависит от выбранной страны
шаг4 ) в ячейке А4 видим результат, получаемый по раннее внесённым формулам, в зависимости выбранной страны. Для каждой страны 5 параметров для формулы занесены в базу заранее.
08.01.2014 10:16:23
Виталий, а почему бы вам автора excel2.ru об этом не спросить - статья-то его, а не моя :)
20.01.2014 18:18:07
А как сделать так, чтобы в ячейке G9 автоматически ставился год начала производства после выбора модели (год прописан в колонке C)?
31.03.2014 07:46:31
Думаю, вам нужна функция ВПР (VLOOKUP)
21.01.2014 12:19:12
Понравилось вот такое решение по связанным спискам неограниченной вложенности! Дейстительно то, что искал.
http://www.sql.ru/forum/845011/svyazannyy-spisok-neogranichennoy-vlozhennosti-v-excel
Но такой вопрос - а как разбить по разным листам? Данные на одном, а перечень заказов на другом.
28.03.2014 10:56:12
Хочу добавить, что Способ 2 в таблицах тоже работает (мне потребовалось время, чтобы это осознать), вместо $G$7 будет ссылка на столбец таблицы вида Таблица[Столбец].
30.03.2014 01:11:36
возможно, невнимательно прочитал комментарии.
но, кажется, и среди них нет варианта без летучих функций ДВССЫЛ и СМЕЩ

для способа 2, Модель:
=ИНДЕКС(Способ2!$B:$B;ПОИСКПОЗ(Способ2!$G$7;Способ2!$A:$A;0)):ИНДЕКС(Способ2!$B:$B;ПОИСКПОЗ(Способ2!$G$7;Способ2!$A:$A;0)+СЧЁТЕСЛИ(Способ2!$A:$A;Способ2!$G$7)-1)
31.03.2014 07:45:09
Спасибо, ikki - ценное дополнение насчет быстродействия.
22.04.2014 11:24:33
Николай Павлов подскажите пожалуйста, не могу никак реализовать данную модель на своём плане :( вот
ФАЙЛ подскажите как сделать так чтобы при выборе наименования, можно было выбрать размер который относится к
этому наименованию и чтобы остальное (длина, вес, м и т.д. по таблице) выходило автоматом??? И как сделать чтобы можно было выбирать несколько позиций подряд?
11.05.2014 15:28:59
Не знаю, коллеги, замечали ли вы. Но при создании связанного списка для "умной" таблицы при задании элемента первичного списка, который так же вносится в эту таблицу (т.е. "является" столбцом, например, Расходы[Группа затрат]) для корректного ввода необходимо в формуле указывать Расходы[@[Группа затрат]] - тогда зависимый список будет корректно отрабатывать каждую строку в таблице. Иначе идет жесткая привязка к первому элементу таблицы в столбце, содержащем элемент первого списка.
19.05.2014 09:54:27
Спасибо за статью, очень понятно объясняет всё, но что делать если я хочу сделать так, что бы список был зависим от результата нескольких списков?....то есть к примеру (речь идёт о мебельной сфере) сейчас я могу выбрать "фабрику", и у меня будут отражаться "коллекции" этой фабрики, а я хочу, что бы я ещё мог выбрать "стиль"...к примеру "классика", и тогда в рамках этой фабрики отражались бы только те коллекции, которые производят классическую мебель. Помогите пожалуйста!))
21.06.2014 17:23:04
Подскажите пожалуйста, а как сделать зависимость цвета, т.е например, есть выпадающий список с тремя значениями: А, В и С. при выборе из списка А - ячейка со значением должна окрашиваться в красный цвет, с В - в синий, а с С - в зеленый? Спасибо!
27.06.2014 18:07:16
Николай, что скажете о таком способе создания связанных динамических списков?
http://www.contextures.com/xlDataVal15.html
Страницы: 1  2