Выпадающий список по значению, зависимый выпадающий список, связанный выпадающий список. Как только не называют это хитрую возможность Excel. «А в чем хитрость?» — спросите вы. Это выпадающий список, который выдает только те значения, которые привязаны к конкретному значению другой ячейки. Т.е. условно, вы выбираете город, а к нему выпадают списком только те улицы, которые есть в этом городе, а не все улицы подряд из столбца. Делается это непросто, но мы разберемся.
Содержание
Выпадающий список по значению. Введение.
Честно говоря, очень удивился, что на некоторых сайтах (не будем показывать пальцем :)) предлагали неработающие варианты. Зная как работают функции, было понятно, что формула будет работать неверно. Поэтому я сваял работающий вариант. Который:
- можно использовать для всего столбца значений;
- не съезжает при копировании и перетаскивании.
Зависимый выпадающий список. Задача
Есть список городов, если вы выбираете Город, то к нему выпадают только привязанные к нему Улицы
По логике: нужно найти город, его позицию в столбце и перенести в выпадающий список только привязанные к нему адреса из соседнего столбца. Как на примере в картинке выше. Это реализуется формулами ПОИСКПОЗ и СМЕЩ, ну и еще в моем варианте СЧЕТЕСЛИ.
Создаем список для примера
В ячейку E3 добавим выпадающий список зависящий от города в ячейке D3.
Сперва добавляем новый диапазон Формулы — Диспетчер имен — Создать
В окне создание имени Заносим имя (напр. Зависимая_улица) и вписываем формулу
Выглядит сложновато, но ничего нереального, разберемся.
Смещ, как работает формула по реквизитам (ячейка от которой начинаем отчет позиции; на сколько смещаемся по строкам (находим при помощи ПОИСКПОЗ, вычитаем 2 и пр), на сколько смещаем по столбцам =1; Смещаемся вверх на столько ячеек сколько улиц привязано к городу (считаем их СЧЕТЕСЛИ); и на один столбец вправо) — это даст нам диапазон, который отображается в окне выбора выпадающего списка.
Теперь создадим выпадающий список Данные — Проверка данных — выбираем список и источник (диапазон Зависимая_улица). Получилось:
Работает!
Пример как всегда можно скачать здесь
Внимание! Такой выпадающий список работает, только если есть сортировка по главному столбцу. Если у вам нужно сортировать не только по этому столбцу, то предлагаю сделать отдельным столбцом сортировку формулой.
Столбец с основными данными — городами должен быть заполнен правильно в каждой строке — как это «правильно» можно прочитать здесь
