Связанные выпадающие списки
Категория: Выпадающие списки, просмотров: 87024, опубликовано: 04.04.2010 Скачать пример |
Способ 1. Функция ДВССЫЛ (INDIRECT)
Этот фокус основан на применении функции ДВССЫЛ (INDIRECT), которая умеет делать одну простую вещь - преобразовывать содержимое любой указанной ячейки в адрес диапазона, который понимает Excel. То есть, если в ячейке лежит текст "А1", то функция выдаст в результате ссылку на ячейку А1. Если в ячейке лежит слово "Маша", то функция выдаст ссылку на именованный диапазон с именем Маша и т.д. Такой, своего рода, "перевод стрелок" ;)
Возьмем, например, вот такой список моделей автомобилей Toyota, Ford и Nissan:
Выделим весь список моделей Тойоты (с ячейки А2 и вниз до конца списка) и дадим этому диапазону имя Toyota в меню Вставка - Имя - Присвоить (Insert - Name - Define). Затем повторим то же самое со списками Форд и Ниссан, задав соответственно имена диапазонам Ford и Nissan.
При задании имен помните о том, что имена диапазонов в Excel не должны содержать пробелов, знаков препинания и начинаться обязательно с буквы. Поэтому если бы в одной из марок автомобилей присутствовал бы пробел (например Ssang Yong), то его пришлось бы заменить в ячейке и в имени диапазона на нижнее подчеркивание (т.е. Ssang_Yong).
Теперь создадим первый выпадающий список для выбора марки автомобиля. Выделите пустую ячейку и откройте меню Данные - Проверка (Data - Validation), затем из выпадающего списка Тип данных выберите вариант Список и в поле Источник - выделите ячейки с названиями марок (желтые ячейки в нашем примере). После нажатия на ОК первый выпадающий список готов:
Теперь создадим второй выпадающий список, в котором будут отображаться модели выбранной в первом списке марки. Точно так же, как в предыдущем случае, выделите пустую ячейку и откройте меню Данные - Проверка - далее Список. В поле Источник нужно будет ввести вот такую формулу:
=ДВССЫЛ(F3)
где F3 - адрес ячейки с первым выпадающим списком - замените на свой.
Все. После нажатия на ОК содержимое второго списка будет выбираться по имени диапазона, выбранного в первом списке.
Минусы такого способа:
- В качестве вторичных (зависимых) диапазонов не могут выступать динамические диапазоны задаваемые формулами типа СМЕЩ (OFFSET). Для первичного (независимого) списка их использовать можно, а вот вторичный список должен быть определен жестко, без формул.
- Имена вторичных диапазонов должны совпадать с элементами первичного выпадающего списка. Т.е. если в нем есть текст с пробелами, то придется их заменять на подчеркивания и т.д.
- Надо руками создавать много именованных диапазонов.
Способ 2. Список соответствий и функции СМЕЩ (OFFSET) и ПОИСКПОЗ (MATCH)
Этот способ требует наличия отсортированного списка соответствий марка-модель вот такого вида:
Для создания первичного выпадающего списка можно марок можно воспользоваться обычным способом, описанным выше, т.е.
- дать имя диапазону D1:D3 (например Марки)
- выбрать на вкладке Данные (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, т.к. нам нужен один столбец с моделями
В итоге должно получиться что-то вроде этого:
Осталось добавить выпадающий список на основе созданной формулы к ячейке G8. Для этого:
Вуаля!
Ссылки по теме
Комментарии:
| Дмитрий 17.12.2006 |
Спасибо большое, осилил. Но, для меня это часть пути. Мне нужно сделать следующее. У меня есть список по столбцам: 1. Марка 2. Модель 3. Цена в автосалоне №1 4. Цена в автосалоне №2 ... n. Цена в автосалоне №n Итак, мы можем выбрать марку и модель автомобиля. Необходимо, чтобы после выбора модели (из выпадающего списка)в соседних строках автоматически появлялись цены автосалона1, автосалона2,.., автосалона "n". Как это можно реализовать? Спасибо. |
| Александр 19.01.2007 |
Функцией ВПР. |
| Станислав 16.03.2007 |
Я так понял, что это работает, когда всё находится в одном листе книги Excel. Если таблица с данными - на другом листе - ничего не получится?. |
| Николай 14.04.2007 |
Почему не получится? Все будет работать. Ссылка на именованный диапазон доступна с любого листа книги.. |
| Вадим 21.04.2007 |
Пожалуйста, подскажите, а как создать один список, но при этом, чтобы данные подтягивались из полей с разных листов? Спасибо. |
| Николай Павлов 20.05.2007 |
Собирайте данные на один лист с помощью обычных ссылок, а дальше - как описано :). |
| Людмила 15.08.2007 |
Я делаю все как описано. Почему при вводе формулы =ДВССЫЛ(F3) у меня появляется сообщение: "При вычислении "Источник" возникает ошибка". Что мне делать?. |
| Даниил 07.09.2007 |
Спасибо, воспользовался! Теперь пытаюсь узнать, возможно ли без привлечения VBA сделать так, чтобы от одного списка зависело содержимое сразу нескольких списков. Суть задачи, думаю, вам не особо интересна, но её решение помогло бы и предыдущему человеку, Дмитрию.. |
| Константин 11.09.2007 |
Людмила у меня таже проблема. Но судя по данным которые я использую проблема в непонимании комбинации слов (пробелов между словами) Попробуйте заменить на нижние прочерки пробелы -- так вроде получается. Хотя иногда это не решение проблемы. |
| Exl 02.10.2007 |
Все бы хорошо, только у вас ссылка идет на конкретную ячейку (F3) а ведь в списке может быть много строк и у каждой своё значение. Пример работает.Спасибо.. |
| udal 03.10.2007 |
Пишу =ДВССЫЛ(H722) - выдает ошибку, 2003 эксель!. |
| Николай Павлов 03.10.2007 |
Господа (и дамы)! Ответить на вопрос из серии "я делаю все как описано, но у меня не работает - что делать?" невозможно не видя ваших файлов. Это вопрос типа "У меня болит живот - что это может быть?" Может колбаса несвежая, может огурчики с молоком, а может и 4 неделя беременности. :)Лучше всего такие вопросы задавать в форуме, где прикреплять к сообщению свои файлы. Тогда вы гораздо быстрее и точнее получите правильный ответ. . |
| Андрей 30.10.2007 |
Попробывал сделать, как написано. Во втором списке выпадают только значения, присвоенные впервому столбцу. В чем подвох? С Excelем знаком мало, но срочно надо сделать таблицу. Заранее благодарю откликнувшихся!. |
| Андрей 30.10.2007 |
Всё сделал,огромное спасибо!. |
| Елена 21.11.2007 |
Помогите, пожалуйста! При создании второго списка после введения функции ДВССЫЛ и выбора ячейки, ОК - появляется сообщение, что указанный именной диапазон не найден. . |
| Валентина 12.03.2008 |
При вводе формулы =ДВССЫЛ(F3). у меня появляется сообщение: "При вычислении "Источник" возникает ошибка". . |
| Валентина 12.03.2008 |
Этот вопрос уже задавался, но ответ на него я не увидела, просьба помочь. |
| Светлана 14.03.2008 |
я ввожу для второго списка =ДВССЫЛ(H4), вижу сообщение "При вычислении "Источник" возникает ошибка". нажимаю продолжить, все работает, но на певую значение из первого выпадающего списка второй список не выпадает. выбираю второе и далее значение из первого списка - все работает. Не понимаю, что не так.. |
29.03.2008 |
пишите так =INDIRECT($A$8) и будет счастье.. |
| Сергей 29.03.2008 |
Скажите, а как сделать, чтобы каждому значению выпадающего списка назначалось свое значение в соседней ячейке.. Спасибо!. |
| Ольга 27.05.2008 |
Как сделать, чтобы от выбранного значения изсписка зависело значение соседних столбцов(автоматически показывалось). Т.е. та же проблема, что у Дмитрия. Что такое функция ВПР и как ей пользоваться?. |
| Lexxi 23.06.2008 |
При вводе формулы =ДВССЫЛ(F3). у меня появляется сообщение: "При вычислении "Источник" возникает ошибка".Офис 2007. Вопрос задавался, но ответа не увидела. |
| Василий 08.07.2008 |
Добрый день)) Подскажите пожалуйста, существует ли возможость создания 4х уровневых списков в эксель и более? Если да, то большая просьба выложить пример для ознакомления) Очень надо)). |
| Федор 16.09.2008 |
Так существует ли возможность 4х уровневых списков???!!! Весьма актуально при создании справочников. Заранее - спасибо!. |
| kippuS 15.10.2008 |
Единственное, в чем минус этой штуки - не получается использовать в качестве 2го списка динамического. Если имя диапазона данных 2го списка описано не просто диапазоном типа "A1:B2", а через СМЕЩ, то ф-ция ДВССЫЛ на него не работает... Есть ли какие-нибудь соображения по этому поводу? Пока что использую вместо динамического списка постоянный, но макрос добавления новых элементов чуток поменял - так что он добавляет ячейку со сдвигом вниз, расширяя область, и заполняя ее. Но не очень красиво получается.... |
| Роман 13.11.2008 |
To Федор. Напиши свой e-mail, сброшу пример.. |
| leemonka 18.11.2008 |
"При вычислении "Источник" возникает ошибка" Это означает, что значение, которое выбрано в первом списке и название именованного диапазона в зависимом списке не совпадают. Например: в первом диапазоне Вы выбрали Отдел Продаж и хотите, чтобы в зависимом выпадал список должностей этого отдела, но список должностей у вас именован как ОтделПродаж -> возникнет указанная ошибка, т.е. Проверка не находит такой именованный диапазон. Необходимо привести в соответствие значения первого списка и названия диапазонов для зависимых списков, либо в функции ДВССЫЛ, которую вы ставите в поле Источник меню Проверка, поставить =ДВССЫЛ(ПОДСТАВИТЬ($B12;" ";"")). Тогда функция ДВССЫЛ в качестве аргумента получит Отдел Продаж, где пробел заменен на пустое место, т.е. ОтделПродаж, и не будет ругаться. Описанный способ хорош в том случае, если в первом списке у вас значения из нескольких слов и вам хочется сохранить их именно в таком виде, а имя диапазона не может состоять из нескольких слов.. |
| Sim 24.11.2008 |
Помогите плиз... У меня таблица из двух колонок в одной из которых модель техники, во второй двигатель соответствующий модели техники. Как сделать, что-бы при выборе модели техники из выпадающего списка, в одной ячейки, во вторую падал двигатель соответствующий выбраной модели техники. (Прописал бы функцией если, но модельный ряд велик, за 150 моделей). Заранее благодарю.. |
| Sim 24.11.2008 |
Всё спасибо, разобрался ВПР помог.... |
| Андрей 24.12.2008 |
Все работает, читайте внимательно инструкцию. |
| Антон 26.12.2008 |
Пытаюсь присвоить имя ООО "Никто не знает", говорит "недопустимое имя"! Как быть?!. |
| Николай Павлов 28.12.2008 |
Антон, в имени диапазона не должно быть пробелов - замените на подчеркивание и все будет ОК.. |
| Антон 29.12.2008 |
По вышему совету заменил все пробелы не подчеркивание, получилось ООО_"Никто_не_знает", но это не помогло. Может будут ещё какие-нибудь соображения1. |
| leemonka 29.12.2008 |
Антон, у вас как называется сам именованный диапазон, в котором содержатся данные, относящиеся к этому ООО ? я писала, что необходимо привести в соответствие значения первого списка и названия диапазонов для зависимых списков. В названии именованных диапазонов не может быть кавычек, можно просто все кавычки заменить пустыми значениями, аналогично пробелам, формулой. Можно другими способами извратиться Можно просто в первом списке все значения написать в одно слово как ООО_Никто_Не_Знает также проименовать диапазон и не париться (но для пользователя таблички некрасиво). . |
| Катерина 14.01.2009 |
Спасибо большое, работает отлично на одном листе, но вот загвоздка... собрала ссылками данные списка из соседнего листа, получилось 4 колонки, задаю параметры для первого списка - все работает, а когда завожу в соседнюю ячейку второй список, зависимый от первого, то пишет "При вычислении "Источник" возникает ошибка." и после этой ошибки первый список работает норм, а второй позволяет выбрать только по 2 критериям из четырех...... Кто-нить сталкивался с ентим? помогите плиззз. |
| Катерина 15.01.2009 |
Сама разобралась с предыдущей проблемой (была ошибка в наименовании списка) :) Теперь другая проблема: автофильтр с такой информацией отказывается работать.. не подскажите как быть?. |
| Сергей 18.01.2009 |
И у меня еще есть вопрос? Я пытаюсь построить все это в макросе - не работает - пишет Run-time error '1004': Application-defined or object-defined error Использую следующий оператор With Range(Cells(10, 2), Cells(10, 2)).Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="двссыл($А$10)" End With В ячейке А10 - первый список . |
| Сергей 21.01.2009 |
Как размножить данную функцию по строкам что бы в каждую не вбивать ручным способом. . |
| Серж 25.01.2009 |
Прочитав комменты, посчитал что в инструкции есть маленький но весомый недочет, в ней указанно следующие: =ДВССЫЛ(F3), а необходимо пистать так =ДВССЫЛ("F3") и тогда все будет типи-топи. |
| Тема 31.01.2009 |
Хотите загадку =) Вот есть у меня два списка, назовем их условно brand_list и model_list. Первый был создан (Вставка - Имя - Присвоить) при помощи простого указания диапазона в поле Формула. Второй создается (Вставка - Имя - Присвоить) при помощи несложных вычислений в поле Формула. Оба списка легальны, т.к. я указываю их в качесвте "наполнителя" впадающего списка и в обоих случаях список отображается правильно. Так вот загадка в следующем. Если я указываю ссылки на эти списки через формулу ДВССЫЛ(...) то первый список работает, а второй нет!!! Пытался прописать ДВССЫЛ(...) в обычной ячейке. Первый список дает значение #ЗНАЧ, второй пишет #ССЫЛКА Уже битых 4 часа колдую. Есть сображения у кого?. |
| Тема 31.01.2009 |
Похоже что не только у меня эта проблема. Вот здесь описано то же самое: http://techrepublic.com.com/5208-6230-0.html?forumID=102&threadID=226636&start=0 Судя по всему, формула ДВССЫЛ(...) напрочь не умеет работать с динамическими массивами.. |
| Тема 31.01.2009 |
И послений вопрос: есть два рабочих выпадающих списка. Все прекрасно работает, как и описано в статье. Одна проблема - при "размножении" ячеек вниз (например, нужно сделать 200 строк с выпадающими списками), все списки продолжают ссылаться на самый первый, т.е. референсы не обновляются! Перебивать их вручную - можно с ума сойти =) Есть ли лечение?. |
| Suomi 05.02.2009 |
Удобно! Спасибо Вам большое! Есть вопрос. Выбираю Марку авто список Моделей ограничивается, все ок, но при возврате к ячейке "Марка" и её корректировке в ячейке "Модели" значение остаётся прежним!!! Как сделать так, чтобы Excel в такой момент начинал громко ругаться и долго (до тех пор пока юзер не скорректирует Модель авто)?? Помогите плиз. О-ооочень нужно. . |
| Николай 12.09.2009 |
Серж прав =ДВССЫЛ(F3), а необходимо пистать так =ДВССЫЛ("F3"). Спасибо!. |
| Олег 19.09.2009 |
Хотелось бы добавить к словам Suomi, что при выборе любого значения следующий связанный выпадающий список меняет свои значения, но только при условии непосредственного выбора и при этом не обнуляет свои значения при измененеии значений в первом связанном списке. Вся мной описаная ситуация непосредственно связана с присловутым человеческим фактором и гиганским количесвом времени необходимом на обучение ЮЗЕРА для работы с выпадающими списками. То есть я хочу сказать что нет защиты от дурака. А если при этом ЮЗЕР является офличностью в прямом и переносном смысле слова, то пожалуй косяк в отчете будет не его при выводе на печать а ваш. Да не плохо был бы открыть тему "ЛАЖА" или "Человеческий фатор" ну или что-то в этом роде.. |
| Юля 10.10.2009 |
есть таблица, заполенная числами. при выборе из раскрывающегося списка какого-либо значения, необходимо заменить значения в таблице на другие. помогите сделать. |
| Максим 17.10.2009 |
To Юля: Создай таблицу необходимых значений для каждого числа и используй функцию ВПР.. |
| Виталий 10.11.2009 |
У меня таблица, в ней два поля. Есть два выпадающих списка. Первый список должен содержать список всех значений таблицы из первого поля. Второй должен отображать значения из второго поля таблицы, сгруппированных по первому полю значением, выбранным в первом списке. -- Спасибо.. |
| татьяна 11.11.2009 |
у меня дана таблица "стороны треугольника" в ней два столбца в первом в ячейке А2 стоит а в А3 стоит b в А4 с в А6 p d F8 S во втором столбце в ячейке B6 задана формула =(В2+В3+в4)/2 нужно дать имена ячейкам В2,В3,В4,В6. Всем дать имя получилось,кроме одной (В4)-пишет что имя недопустимо скажите в чем тут дело? Спасибо.. |
| Tair 26.11.2009 |
Подскажите, где искать полный список всех автомобильных моделей и марок в табличке? . |
| Павел 11.12.2009 |
Спасибо.. |
| Юрий 21.12.2009 |
Если имена диапазона вводить на украинском то выпадающий список дает збой :(. |
| Степан 04.01.2010 |
е рботает ни как и ни в какую. |
| Владимир 17.01.2010 |
А как это можно реализовать, если данные находятся в другой книге?. |
| Степан 17.01.2010 |
аааблдеть. заработала, но надо при первом списке выбора марки в меню источник просто имя диапазона с разными общими названиями без кавычек, скобок. а вот уже второй зависимый список - напихать скобок, тогда не даёт ошибки.... |
| Shalman 23.01.2010 |
Толи лыжи не едут, то ли... Не работает, хоть убей. Все делаю как по инструкции.... |
| Shalman 23.01.2010 |
Алилую! Работает! Только я сам не понял, что я такое поменял))). |
| Юля 28.01.2010 |
Здравствуйте! Подскажите пожалуйста а как такое сделать в Excel на Visual Basic. Ничего не получается(( . |
| Евгений 10.02.2010 |
Хочу вставить свои "5 копеек". Судя по представленным комментариям, бОльшая часть проблем связана с тем, что пользователи не совсем владеют требованиями к именнованным диапазонам. В названиях могут быть ТОЛЬКО латинские и кириллические буквы, цифры и знак подчеркивания. При чем имя не может начинаться с цифры. Пробелы недопустимы! Но такие ограничения ломаю красоту и вместо ООО "Пупки и Ко" надо писать ООО_Пупкин_и_Ко или ОООПупкинИКо. Для программистов это не проблема, а вот если это печатная форма, то некрасиво получается. Решением может быть использование ВПР(). Например на листе есть Диапазон "ГруппыТехнические" ------------------------- Литер.имя Техн.имя Группа один Группа_1т Группа два Группа_2т Группа три! Группа_3т Диапазон "ГруппыЛитературные" (как часть первого диапазона) Группа один Группа два Группа три! И три диапазона данных Группа_1т, Группа_2т и Группа_3т. В ячейке $G$18 стоит проверка данных с источником "=ГруппыЛитературные" В ячейке с выбором содержимого групп стоит проверка =ДВССЫЛ(ВПР($G$18;ГруппыТехнические;2;ЛОЖЬ)) Так мы можем "зрительно" обойти технические ограничения. . |
| max 15.02.2010 |
ребята помогите плиз. |
| max 15.02.2010 |
у меня уже есть 2 списка мне теперь нужно привязать еще 2. чтобы 3 зависел от 2 а 3 от 4 уже трое суток мучаюсь все перепробовал. объясните пожалуйста как это сделать и если можно пример я не откажусь, заранее спасибо. |
| max 15.02.2010 |
а можно сделать так чтобы 3 столбик зависел от первого??. |
| Vladeks 16.02.2010 |
Вот где червячок зарыт: Если у Вас возникает ошибка: "При вычислении "Источник" возникает ошибка", проверьте правильность имён диапазонов и заглавий столбцов. Т.Е. если в А1 Вы написали "Тойота", то диапазон А2:А10 должен называться не "Тайота", не "Toyota", а именно "Тойота".. |
| max 16.02.2010 |
как раз вот тут у меня проблем нету . |
| Евгений 16.02.2010 |
Для max Так алгоритм не зависит от уровня вложенности. Если всего три уровня, то второй зависит от первого (о третьем не думаем), а третий - от второго (о первом тоже можно не думать). То есть мы рассматриваем пары уровней. По идее, эти уровни можно вкладывать до посинения или пока есть желание вручную давать имена диапазонам.... |
| max 16.02.2010 |
это только мысли вы покажите на примеры ото у меня от этого всего уже крыша едет мне это увидеть надо вот это было бы здорово. |
| dyhak 19.02.2010 |
А как... подскажите плиз) 2 дня сижу, ничего в голоу не лезет( есть раскрывающийся список в эксель. Там к примеру фамилии иванов (ячейка а3) петров (ячейка а4) сидоров(ячейка а5) Если выбрать в раскрывающемся списке например "петров", то ячейка а3 и а4 скроются, останется только а4. Задача: присвоить другой ячейке, например е3, значение той ячейки, которая в данный момент выбрана из раскрывающего списка. Нужно чтобы в бланк фамилия и данные автоматом подставлялись при выборе сотрудника из списка. Вот.. |
| СергейКА 27.02.2010 |
Разобрался в той ошибке, что возникает у многих ("При вычислении "Источник" возникает ошибка.") Прав оказался Тема (письмо от 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 - адрес ячейки с вычисленным адресом диапазона. Вот так вот. Кривовато, но что сделаешь, если имеешь дело с кривоватыми мелкомягкими функциями .... Зато работает :). |
| СергейКА 27.02.2010 |
Кстати, такой способ позволяет и размножать строки с выпадающими списками и увеличивать количество и размер списков (производителей и их марок), ничего нигде дополнительно не описывая и не меняя. Кроме всего прочего, можно списки производителей и их марок поместить на один лист, а выпадающие списки - на другой лист. . |
| Алексей 04.03.2010 |
имеется таблица из 2-х колонок. В первой название фирмы (ООО "Рога и Копыта") во второй адрес (Москва, ул. Ленина, д. 999, кв. 999.)и т.д. Делаю выпадающий список на название фирмы. Как сделать, что бы при выборе фирмы из списка в соседнюю (нижнюю) ячейку автоматом вводился адрес фирмы? Возможен ли вариант добавлять в список отсутствующие фирмы и их адреса. Заранее спасибо.. |
| Алексей 04.03.2010 |
В случае необходимости написания макроса, просьба помочь листингом.. |
| Паша 05.03.2010 |
Ребят, а можно сделать что-то на подобие группирования? например, когда выбираешь марку авто, чтобы сразу под ней выкидывало все модели...возможно я что-то делаю неправильно, но второй подсписок получается такой же...надо прокручивать и выбирать...и посчитать общую сумму трудно.... |
| Светлана 16.03.2010 |
Спасибо! Всё отлично работает! Алексей, по поводу автоматического ввода адреса: ВПР не помогает разве? По поводу добавления доп фирм и адресов - я, как правило, присваиваю имя исходному диапазону данных и при внесении в него доп. значений, просто расширяю диапазон для имени. Топорно, конечно, но меня сие не особо затрудняет.. |
| Дмитрий 31.03.2010 |
А можно ли добавлять значения в список "на лету". Т.е. посмотрел что выпадает, увидел что нужного нет, и сразу, не переходя на ячейки со списком, добавить новое значение, которое пропишется в т.ч. и в список.. |
| Иван 05.04.2010 |
А у меня проблема нарисовалась: где-то в середине "марка" подхватывает первую "модель" из следующего диапазона. То есть, исходя из примера: "Ford", помимо своего диапазона, прихватывает еще и "Almera" из "Nissan". Почему так??? . |
| Светлана 13.04.2010 |
Пожалуйста, помогите - как лучше отслеживать срок хранения по датам? Заранее спасибо.. |
| Silvan 05.05.2010 |
СергейКа спасибо! Ваш вариант очень понравился.. |
| Александр 07.05.2010 |
Привет! Нужна помощь! Как быть, если необходимые диапазоны ячеек находятся в другом файле? Спасибо!. |
| Александр 07.05.2010 |
У меня выпадающие списки работают только в том случае если файл с необходимым диапазоном ячеек открыт.. |
| sts 07.05.2010 |
используя пример "СергейКА от 27.02.2010" создал все работает но как только разношу на разные страницы: "исходники" -Лист1 и "выпадающие списки" -Лист2 привязать никак не получается т.к. INDIRECT (используемый в списках) отказывается работать с оператором COUNTA если тот ссылается на данные с другой страницы или если эту строку держу на Лист1: (в ячейке Е2) =АДРЕС(1;1;1;1)&":"&АДРЕС(1;СЧЁТЗ($1:$1);1;1) а выпадающему списку в поле Источник дописываю: =ДВССЫЛ("Лист1!$E$2") максимум чего добился это появления в выпадающем списке строки вида: $A$1:$D$4 СергейКА - незатруднит показать на примере как правильно прописывается?. |
| СергейКА 12.05.2010 |
если вы хотите сослаться на другой лист, то ячейка адреса должна вычислять адрес в полной форме (c указанием листа). данные на Лист1, выпадающий список на Лист2, выбранное значение в F3, ячейка вычисляемого адреса E2 на Лист2, тогда E2 содержит: =ADDRESS(2;MATCH('Лист2!$F3';'Лист1!$1:$1';0);1;1;"Лист1")&":"&ADDRESS(COUNTA(INDIRECT(ADDRESS(2;MATCH('Лист2!$F3';'Лист1!$1:$1';0);1;1)&":"&ADDRESS(999;MATCH('Лист2!$F3';'Лист1!$1:$1';0);1;1)));MATCH('Лист2!$F3';'Лист1!$1:$1';0);1) Вычислится строка типа "Лист1!$B$2:$B$15" выпадающий список в поле Источник: =ДВССЫЛ($E$2) Может где ошибся, извините. В общем смысл такой, что вычисленный адрес (в виде строки) может указывать КУДА УГОДНО, но он должен быть УЖЕ ВЫЧИСЛЕН до подстановки его в =ДВССЫЛ(...) / =Indirect(...). |
| Юлия 13.05.2010 |
Спасибо за статью.. |
| Юлия 13.05.2010 |
Здравствуйте. Мне нужна помощь. Нужно создать связанный список с 3 списками. Т.е. есть 4 столбца "Район", "Тип","Вид","Учреждение". Нужно чтобы при выборе значения в поле "Район" в поле "Учреждение" отображались учреждения данного района, а еще если выбраны значения в полях "Тип" или "Вид" ("Тип" и "Вид") отображались учреждения данного типа и вида.. |
| Юлия 13.05.2010 |
И еще к предыдущему сообщению. Можно ли в выпадающем списке "Учреждение" разрешить менять (и добавлять) значения(не в том месте, где весь список учреждений, а прямо из ячейки, где происходит выбор).. |
| Юлия 13.05.2010 |
К примеру, который рассмотрен в статье. Если нужно сделать таблицу (ведомость) в которой n-е количество строк, в первом столбце указывается марка, во-втором - модель, в третьем кол-во (заносят с клавиатуры).. |
| Марат 19.05.2010 |
У меня такая проблема! Есть два листа: Лист1 и Лист2. В первом листе (Лист1) содержится таблица из двух столбцов, в первом код вида экономической деятельности, во втором соответствующее ему название. Возможно ли сделать такое: Чтобы на втором листе (Лист2) при вводе в один столбец кода экономической деятельности во втором столбце автоматически с первого листа со второго столбца выпадало название, соответствующее этому коду! Заранее благодарю! . |
| sts 20.05.2010 |
СергейКА 12.05.2010 спасибо с этим разобрался единственный нюанс забыли в второй группе дописать имя листа (как в первой) =ADDRESS(2;MATCH('Лист2!$F3';'Лист1!$1:$1';0);1;1;"Лист1")&":"&ADDRESS(COUNTA(INDIRECT(ADDRESS(2;MATCH('Лист2!$F3';'Лист1!$1:$1';0);1;1;"ЛИСТ1")&":"&ADDRESS(999;MATCH('Лист2!$F3';'Лист1! $1:$1';0);1;1)));MATCH('Лист2!$F3';'Лист1!$1:$1';0);1) еще раз спасибо я "с разбегу" неподумав начал было адресовать все 4 функции "ADDRESS" на Лист1. пример: =ADDRESS(1;1;1;1;"Лист1") (поетому немношко застрял)*) так же пришлось доработать формулу данных E3 для первого списка из F3 исходник =ADDRESS(1;1;1;1)&":"&ADDRESS(1;COUNTA($1:$1);1;1) в доработаном виде: =ADDRESS(1;1;1;1;"Лист1")&":"&ADDRESS(1;COUNTA('Лист1!$1:$1');1;1). |
| sts 20.05.2010 |
Марат 19.05.2010 можно См пример СергеяКА (с доработкой, по моему вопросу) единственное что надо : при вводе кода НЕОшибиться и соответственно можно оставить на ячейке F3 код =ДВССЫЛ(E3) (можно будет как выбрать код так и ввести его (без ошибок!) но!!: при создании списка откройте меню Данные - Проверка (Data - Validation), затем из выпадающего списка Тип данных выберите вариант Список и в поле Источник введите формулу ...., а на 2 и 3 вкладках снимите "галки" 2- отображать подсказку если ячейка является текущей 3- выводить сообщение об ошибке а во второй ячейке тогда после первой ячейки в которой вводите "код" на второй ячейке (G3) прописываете код: =INDIRECT(ADDRESS(2;MATCH('Лист2!$F3';'Лист1!$1:$1';0);1;1;"Лист1")&":"&ADDRESS(COUNTA(INDIRECT(ADDRESS(2;MATCH('Лист2!$F3';'Лист1!$1:$1';0);1;1;"ЛИСТ1")&":"&ADDRESS(999;MATCH('Лист2!$F3';'Лист1! $1:$1';0);1;1)));MATCH('Лист2!$F3';'Лист1!$1:$1';0);1)) тобиш я просто взял и добавил всю формулу внутрь команды =INDIRECT(). |
| sts 20.05.2010 |
Извиняюсь выше в коменте неточность небольшая там надо применить укороченную формулу (не целиком) (я вам на почту вышлю рабочий пример фаила). |
| sts 20.05.2010 |
Марат (вот вкраце подправленный пример с использованием варианта от СергеяКА, фаил я вам выслал) лист1 1столбец- коды 2столбец -значения (напротив каждого кода 1 значение) лист2: Ячейка А2(необязательно A2, место можно любое)введите формулу: =АДРЕС(1;1;1;1;"Лист1")&":"&АДРЕС(СЧЁТЗ(Лист1!$A:$A);1;1;1) ячейка B2: Откройте меню Данные - Проверка (Data - Validation), затем из выпадающего списка Тип данных выберите вариант Список и в поле Источник введите формулу: =ДВССЫЛ($A$2) на 2 и 3 вкладках снимите "галки" 2- отображать подсказку если ячейка является текущей 3- выводить сообщение об ошибке ячейка С2 введите формулу: =ДВССЫЛ(АДРЕС(ПОИСКПОЗ(Лист2!B2;Лист1!$A:$A;0);2;1;1;"Лист1")) после размножте просто перетянув B2 и C2 на нужное кол-во строк. |
| Леонид 20.05.2010 |
Подскажите пожалуйста, есть таблица заполненная с помощью списков из способа 1, если вдруг поменяется имя, например, вместо Toyota станет Toiota, как сделать чтобы в таблице тоже изменились имена?. |
| Andrey2012 14.06.2010 |
Можно сделать динамически изменяющимся и способ №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-го уровня. . |
| Andrey2012 14.06.2010 |
немного подправил 2-ю формулу '=СМЕЩ('Способ1'!$A$2;1;ПОИСКПОЗ('Способ1'!$F$3;Фирмы;0)-1;СЧЁТЗ(ДВССЫЛ(АДРЕС(3;ПОИСКПОЗ('Способ1'!$F$3;Фирмы;0);;1;"Способ1")&":"&АДРЕС(20000;ПОИСКПОЗ('Способ1'!$F$3;Фирмы;0);;1)));1). |
| Alex 13.07.2010 |
Вопрос на засыпку: А как сделать тоже самое, что проделано в способе №2, но чтобы исходные данные для формирования списков были в одном файле, а сам выпадающий список в другом!? Т.е. например в соответствии со способом №2: столбцы A и B, и ячейки D1, D2, D3 будут размещаться в файле "Файл1.xls", на листе "Лист1"; а ячейки с выпадаюшим списком G7 и G8, в файле "Файл2.xls", на листе "Лист2". Возможно ли такое сделать???. |
| Mark 10.08.2010 |
Проверка?! Где мой комент?!. |
| Mark 10.08.2010 |
Прошу прощения, но долгое время описывал просьбу, и тут на тебе, пустой текст в комментарии. Мучаюсь над зависимыми динамическими списками на разных листах. Списки на листе "Списки" (Фирма, Код, Название) На другом листе ("BL"), 3 колонки A В С (Фирма, Код, Название), заполняя таблицу, нужна зависимость первых двух, название всегда одно для каждого кода. Прочитав статью, могу создать 2 статические ячейки как в примерах, но вот как их размножить?! Чтобы построчно заполнять таблицу. Фирмы, коды и названия постоянно будут добавляться! Помогите пожалуйста.. |
| Олег Сергеевиx 12.08.2010 |
Трехуровневый список кто-нибудь пробовал сделать? Какие есть способы?. |
| Валерий 29.08.2010 |
Работаю с многоуровневыми списками: если умеете создавать двухуровневый список со значениями А (список1)и В (список2), то создав промежуточную ячейку с формулой ***=СЦЕПИТЬ(значение (А);значение(В);...), получим набор имен (из всех возможных комбинаций значений первых двух списков)для создания имен именованных диапазонов списков третьтего уровня, для отображения которых используется все та же формула: =ДВССЫЛ(***) и так далее, на сколько хватит терпения и аккуратности.. |