backtotop
Функции ПОДСТАВИТЬ и ЗАМЕНИТЬ в Excel
Индекс и Поискпоз. Левый ВПР. Так удобнее!

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

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

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

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

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

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

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

  • ПОИСКПОЗ — находит первую позицию выбранного города в столбце городов
  • СМЕЩ — сначала находя ячейку по ПОИСКПОЗ, а потом передавая в выпадающий список нужный диапазон улиц.
  • Формулу мы будем вписывать не в ячейку, а в так называемый диспетчер имен (когда создаем диапазон с именем).

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

vyipadayushhiy-1

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

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

vyipadayushhiy-2

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

=СМЕЩ('1'!$A$1;ПОИСКПОЗ('1'!$D$3;'1'!$A:$A;0)-2+СЧЁТЕСЛИ('1'!$A:$A;'1'!$D$3);1;-СЧЁТЕСЛИ('1'!$A:$A;'1'!$D$3);1)

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

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

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

зависимый выпадающий список

Работает!

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

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

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

 

Функции ПОДСТАВИТЬ и ЗАМЕНИТЬ в Excel
Индекс и Поискпоз. Левый ВПР. Так удобнее!

Комментарии:

( One Comment )

katya12345:

Крутая статья, спасибо!
Сделаю файл для работы, это очень удобное решение для адресов.

Добавить комментарий