Как сделать всплывающий список в Excel

Выпадающий список по значению, зависимый выпадающий список, связанный выпадающий список. Как только не называют это хитрую возможность Excel. «А в чем хитрость?» — спросите вы. Это выпадающий список, который выдает только те значения, которые привязаны к конкретному значению другой ячейки. Т.е. условно, вы выбираете город, а к нему выпадают списком только те улицы, которые есть в этом городе, а не все улицы подряд из столбца. Делается это непросто, но мы разберемся.

Содержание

Выпадающий список по значению. Введение.

Честно говоря, очень удивился, что на некоторых сайтах (не будем показывать пальцем :)) предлагали неработающие варианты. Зная как работают функции, было понятно, что формула будет работать неверно. Поэтому я сваял работающий вариант. Который:

  • можно использовать для всего столбца значений;
  • не съезжает при копировании и перетаскивании.

Зависимый выпадающий список. Задача

Есть список городов, если вы выбираете Город, то к нему выпадают только привязанные к нему Улицы

По логике: нужно найти город, его позицию в столбце и перенести в выпадающий список только привязанные к нему адреса из соседнего столбца. Как на примере в картинке выше. Это реализуется формулами ПОИСКПОЗ и СМЕЩ, ну и еще в моем варианте СЧЕТЕСЛИ.

Создаем список для примера

В ячейку E3 добавим выпадающий список зависящий от города в ячейке D3.

Сперва добавляем новый диапазон Формулы — Диспетчер имен — Создать

В окне создание имени Заносим имя (напр. Зависимая_улица) и вписываем формулу

Выглядит сложновато, но ничего нереального, разберемся.

Смещ, как работает формула по реквизитам (ячейка от которой начинаем отчет позиции; на сколько смещаемся по строкам (находим при помощи ПОИСКПОЗ, вычитаем 2 и пр), на сколько смещаем по столбцам =1; Смещаемся вверх на столько ячеек сколько улиц привязано к городу (считаем их СЧЕТЕСЛИ); и на один столбец вправо) — это даст нам диапазон, который отображается в окне выбора выпадающего списка.

Теперь создадим выпадающий список Данные — Проверка данных — выбираем список и источник (диапазон Зависимая_улица). Получилось:

Работает!

Пример как всегда можно скачать здесь

Внимание! Такой выпадающий список работает, только если есть сортировка по главному столбцу. Если у вам нужно сортировать не только по этому столбцу, то предлагаю сделать отдельным столбцом сортировку формулой.

Столбец с основными данными — городами должен быть заполнен правильно в каждой строке — как это «правильно» можно прочитать здесь

Похожие статьи

Оцените статью
Как в офисе.ru
Добавить комментарий