Как связать выпадающий

Хитрости »

1 Май 2011       Дмитрий       128270 просмотров

Чтобы понять о чем пойдет речь в статье сначала необходимо понимать что такое выпадающий список и как его создать. Теперь попробуем разобраться что значит выражение "связанный выпадающий список". Я бы еще назвал такой список зависимым. Т.е. когда список значений одного выпадающего списка зависит от значения, выбранного в другом выпадающем списке или просто забитого в ячейку. Представим ситуацию: есть ячейка А2. В ней создан выпадающий список со значениями: Овощи, Фрукты, Мясо, Напитки. А в ячейке В2 нам нужен такой список, чтобы значения этого самого списка изменялись в зависимости от того, какое значение мы укажем в ячейке А2 - т.е. список выбранной категории продуктов. Например выбрали в А2 значение Овощи - в В2 появился выпадающий список, содержащий значения: Морковь, Капуста, Картошка, Редиска, Помидоры. Выбрали в А2 Мясо - в В2 появился выпадающий список, содержащий значения: Говядина, Телятина, Свинина, Курица, Индейка. И т.д.

Подготовка
Для начала нам потребуется создать все эти списки. Что-то вроде этого:
Списки

Далее для каждого из этих списков необходимо назначить именованный диапазон. Создать можно любым способом из описанных в этой статье. Главное помнить - если сами списки расположено на листе, отличном от того, на котором списки выпадающие - то обязательно создавать именованный диапазон с назначением области действия - Книга.
В приложенном к статье примере диапазоны имеют имена категорий - их можно видеть в заголовках.
Если ваши категории содержат пробел - необходимо заменить его на нижнее подчеркивание (_) или удалить, т.к. в качестве именованного диапазона такое значение не подойдет и ничего в результате не получится.

Создание зависимых списков
В ячейке А2 создаем "список списков" - основной список, на основании значений которого будет создаваться второй список. Этот список может быть создан любым способом (как создать выпадающий список). Назовем его Список категорий.
В ячейке В2 потребуется создать список на основании формулы, хоть по сути и так же, как и остальные: вкладка Данные (Data)-Проверка данных (Data validation)-Список (List). Но теперь вместо прямого указания имени списка необходимо указать ссылку на именованный диапазон, который мы выберем в Списке категорий(ячейка А2), на основании его имени. В этом нам поможет функция ДВССЫЛ (INDIRECT). Просто записываем эту формулу в поле Источник (Source): =ДВССЫЛ($A2)
Список через ДВССЫЛ
На что обратить внимание: если вы планируете распространять такой список на столбец, то ссылка должна выглядеть именно так: $A2. Перед цифрой не должно быть знака доллара ($A - неправильно). Иначе зависимый список будет всегда формироваться исключительно на основании значении ячейки А2.

Источник из другой книги
Сами списки товара могут находится и в другой книге. Если книга называется Книга со списком.xls и на Лист1 в ячейке А1 в этой книге находится имя нужного нам списка, то формула будет выглядеть так:
=ДВССЫЛ("'[Книга со списком.xls]Лист1'!"&$A)
На что обратить внимание: лучше всегда перед именем книги и после имени листа ставить апостроф - '. Так вы избежите проблем и недопонимания, если имя листа или книги содержит пробелы и иные специфические символы. В отличии от списков внутри одной книги в данном случае знак доллара должен быть и перед буквой и перед цифрой. В ином случае возможны ошибки (если, конечно, это не было как связать выпадающий сделано специально с пониманием того, что делалось).

Ограничения: данный способ создания списков хорош, но не обошлось и без ложки дегтя. Даже двух:

  1. обе книги должны быть открыты. Если вы закроете книгу со списками, то получите ошибку - выпадающие списки просто перестанут работать
  2. созданные подобным образом связанные списки не будут работать с динамическими именованными диапазонами

И ничего с этими ограничениями не поделать при подобном подходе.

Скачать пример

  Tips_Lists_Connect_Validation.xls (26,5 KiB, 12 927 скачиваний)

Так же см.:
Выпадающие списки
Именованные диапазоны
Динамические именованные диапазоны

Статья помогла? Поделись ссылкой с друзьями!   Плейлист   Видеоуроки

Поиск по меткам

Access Multex Outlook Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Настройка Печать Поиск данных Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx анализ данных ссылки

Источник: http://www.excel-vba.ru/chto-umeet-excel/svyazannye-vypadayushhie-spiski/




Рекомендуем посмотреть ещё:


Закрыть ... [X]


Поле со списком - элемент управления формы в MS Мастер класс бийск

Как связать выпадающий Как сделать выпадающий список с помощью HTML
Как связать выпадающий Счетчик - элемент управления формы в MS EXCEL
Как связать выпадающий Связанные выпадающие списки Excel для всех
Как связать выпадающий Создание динамической диаграммы в Excel с
Как связать выпадающий Javascript фильтр таблиц - PHP-заметки
Выпадающие списки - Планета Excel Автомобилист. org - Клуб любителей автомобилей Вас приветствует Учебный центр Логопед Мастер Вязание крючком: вязаные игрушки, схемы и Как избавиться от перхоти в домашних условиях? Куклы и пупсы Paola Reina - Paola Reina МИТРО Московский Институт Телевидения и

Похожие новости