Связанные (зависимые) выпадающие списки
Способ 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. Умные таблицы
Этот подход очень похож на предыдущий, но использует вместо именованных диапазонов - "умные" динамические таблицы, которые будут выступать источником данных для вторичного списка:
Таким образом, мы:
- Сначала преобразуем наши справочники в "умные" таблицы, используя сочетание клавиш Ctrl+T или команду Главная - Форматировать как таблицу (Home - Format as Table) и
- Даём им имена (Фрукты, Овощи, Зелень) на вкладке Конструктор (Design) в поле Имя таблицы (Table Name).
- Создаём первый и второй (связанный) выпадающие списки точно так же, как в предыдущем способе с функцией ДВССЫЛ (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, т.к. нам нужен один столбец с моделями
В итоге должно получиться что-то вроде этого:
Осталось добавить выпадающий список на основе созданной формулы к ячейке 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)
Она ищет нужную категорию в зелёной шапке таблицы, спускается на одну ячейку вниз и ссылается затем на диапазон, по высоте равный количеству товаров в выбранной категории.
Ссылки по теме
- 4 способа создать выпадающий список в ячейках листа
- Автоматическое создание выпадающих списков при помощи инструментов надстройки PLEX
- Выбор фото из выпадающего списка
- Выпадающий список с автоматическим удалением уже использованных элементов
- Динамическая выборка данных для выпадающего списка функциями ИНДЕКС и ПОИСКПОЗ
Спасибо за объяснение, помогло.
Остался вопрос, если я хочу сделать в колонке товар исключение, возможно ли сделать так.
К примеру выбирается список товара из drop down листа, но в соседнюю колонку надо чтобы добавляли не больше 6 занчений.
Пример: Выбрали Апельсин в соседней колонке не больше 6 значений, выбрали Яблоко в соседней колонки не больше 4 значений.То есть к каждому товару прикрепленно определенное количесвто значений которых может добавить пользователь.
Спасибо
Есть 5 прайсов с одинаковыми наименованиями но с разными ценами.
Необходимо: в 1-й ячейке из выпадающего списка выбрать один из 5 прайсов, во 2-й ячейке выбираем из списка необходимый пункт прайса и !в 3-й ячейке необходима автоматическая подгрузка стоимости услуги выбранной во 2-й ячейке.
Связанные (зависимые) выпадающие списки делал, не могу понять как привязать цену услуги.
Я подобное делал через ВПР следующим образом:
Создал две таблицы.
1. Таблица ("Данные") со всеми вариациями данных: в Вашем случае это будет выглядеть так КОЛОНКА 1(Прайс Позиция), КОЛОНКА 2(Цена)
2. Таблица ("Сложение") - в данной таблице производится сложение данных, введенных в шаблоне (скажем это у нас в ячейке G3 происходит)
2 с выпадающими списками: "Прайс" и "Позиция"
и 3я колонка "Цена"
Так вот, после того как вы выбрали "прайс" и "позицию", эти два значения соединяются в ячейке G3. После этого, в ячейке где должна быть цена, через ВПР производиться сравнении того, что у нас "сложилось" в G3 и таблицы "Данные". Находя соответствие, ВПР подтягивает цену.
Подскажите, реально ли совместить функцию ФИЛЬТР и СМЕЩ? После выбора (по примеру) марки машины, в моей таблице "модели" имеют на одну марку свыше 60 позиции, что соответственно не очень удобно для выпадающего списка.
Хотелось бы реализовать в поле где выбирается "модель" функцию ФИЛЬТР, чтобы после выбора марки, в модели вбить несколько букв для осуществления быстрого поиска -
Или данные две возможности несовместимы?
P.S.: в принципе можно рассмотреть вариант от обратного. Выбирается сперва "Модель" с помощью ФИЛЬТР, а "Марки" сделать зависимыми от "Модели"...
Использовала в работе 3-й способ. Есть проблема: работает только для 1 пункта из выпадающего списка, т.е. выбираю овощи, выпадающий список соответствует овощам, выбираю фрукты или зелень, выпадающие списки пусты. В чем может быть причина?
Попробовала воспользоваться советом из комментариев и преобразовала список в таблицу, но результат - в ячейке "ЛОЖЬ".
Помогите пожалуйста!
У меня в 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 способым стараяю или это невозможно
Жду хороший ответов с нетерпением
Вопрос к Автору и всем остальным.
Есть некий файл (Адреса проживания/прописки сотрудников).
В нем сам лист с сотрудниками, и лист справочник в котором указано какому населенному пункту какая административно-территориальная единица соответствует (лист содержит колонки: Страна, Регион, Район, Тип громады (выпадающий список), Наименование громады, Тип населенного пункта (выпадающий список), Наименование населенного пункта). Длина листа более 30 тысяч строк.
Как на листе с списком сотрудников реализовать зависимые каскадные списки.
P.S. Должно работать в Excel 2007 и новее
С уважением,
Простите никак не могу сообразить как сделать свою задачу:
Есть листы Exel по имени Продавца
в каждом есть Товар (название) и соответственно цена
Сводный лист. Там Поле Продавец, Товар, цена
Как сделать так, чтобы можно было выбрать в Поле Продавец из листов Exel и потом поле Товар выбиралось из диапазона Товар конкретного листа, ну и соответственно поле Цена ставилось из соответствующего листа
У меня есть таблица:
А:Менеджер, Б:ФИО клиента
Есть список этих менеджеров
Подскажите как сделать список, если в таблице появился новый клиент, то вывести список всех менеджеров, если уже существующий то вывести только менеджера который уже назначен?
Пока смог вывести менеджера кто уже был через ИНДЕКС(А:А; ПОИСКПОЗ(Б2;Б:Б;0))
А если поиск не нашел, как вывести всех?
Возможно ли в выпадающем списке сделать разделитель(не выбираемый)
Пример. Выпадающий список:
Маша
Саша
------- (<--- разделить список, и чтобы выбрать этот разделитель было не возможно
Иван
А можно сделать "протягиваемый" зависимый ниспадающий список?
У меня есть список ошибок, и для каждой нужен выбор "Причина" и зависимая от выбранной причины "Детализация".
Как реализовать зависимый ниспадающий список в нескольких строках, без создания списка в каждой строке вручную?
П.С. при записи формулы "двссыл(f2)" список выдаёт ошибку. Работает только в виде "двссыл($f$2)". А в таком случае, ссылка на ячейку не протянется.
А как реализовать предложенный способ3 через макрос в UserForm combobox ? Спасибо
А есть макрос, которые позволяет создать многоуровневые зависимые списки? Если есть- поделитесь пожалуйста
СМЕЩ(D3;ПОИСКПОЗ(B3&C3;Kessebohmer!O:О&Kessebohmer!P:P;0);0;СЧЁТЕСЛИМН(Kessebohmer!O:О;B3;Kessebohmer!P:P;C3))
Список разворачивается, но пустой.
Прошу оказать помощи в решении, на первый взгляд простой задачки.
При создании связанных (зависимых) выпадающих списков при выборе показателя эффективности всё работает.
В какую сторону курить/копать, чтобы при выборе того или значения активности выбиралось необходимое, связанное с ним значение эффективности? Буду очень признателен.
Пробую способ 3. Все ломается на этапе ввода OFFCET. Получаю ошибку there's a problem with this formula. Причем пробовала вбить формулу в ячейку и проверить - то же самое. Выделяет ссылку на А1 и дальше никак. В чем может быть проблема?
Полезный урок, спасибо.
Подскажите, использование 3го метода в случае:
1я колонка и 5 строк указано наименование Армавир, во втором столбце указаны населенные пункты: Армавир, Белецк, Белореченск, Бесскорбная, Великовченое. Формула ищет не с 1й строки второго столбца, со слова Армавир, а с Белецкий.
Как быть?
Фактически пользуюсь способом 2.
То есть у меня много УТ с одним полем. И все они используются для проверки данных.
Проблема в том, что в названиях моих данных часто встречаются символы, такие как " ", "-", "+" и др.
Из-за этого я пользуюсь длинными формулами в окне "проверка данных" с несколькими функциями "подставить".
В общем-то в этом нет ничего сложного, но в моих данных могут появится новые недопустимые символы, которые я не могу предсказать сейчас. При их появлении придется вносить изменения в формулы в окне "проверка данных".
Я бы смог избежать такой учести, если бы объединил все УТ со списками в одну УТ с множеством полей, ведь в наименовании столбцов допустимы все эти символы и можно было бы не пользоваться функцией "подставить" вовсе.
Негативный момент такого решения - так как в каждом поле такой УТ разное число значений, в этой УТ неизбежно будут пустые ячейки, которые будут отображаться в выпадающем списке.
Подскажите пожалуйста, как избежать отображения в выпадающем списке пустых значений, когда ссылаешься на столбец другой умной таблицы, в котором неизбежно есть пустые ячейки.
И дополнительно, возможно, вы подскажите - можно ли избавиться от повторений в выпадающем списке.
Только начинаю осваивать программу и столкнулся с проблемой, нужна ваша помощь!
Создаю таблицу в которой есть несколько выпадающих текстовых списков. Каждому элементу этих списков хочу присвоить числовое значение, чтобы впоследствии произвести между этими значениями математические вычисления. В сторону какой функции/функций смотреть? спасибо