Забыли пароль?


Хитрости »
Основные понятия (23)
Сводные таблицы и анализ данных (9)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (17)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (64)
Разное (41)
Баги и глюки Excel (4)

Связанные выпадающие списки


Скачать файл, используемый в видеоуроке:

  Связанные выпадающие списки.xls (216,5 KiB, 1 693 скачиваний)


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

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

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

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

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

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

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

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


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

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

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


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

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

Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки
Обсуждение: 37 комментариев
  1. Сергей:

    Здравствуйте!
    В связанных списках для заголовков одной таблицы использую ДВССЫЛ таким образом =ДВССЫЛ("Таблица[#Заголовки]"), дабы при добавлении нового столбца или изменении заголовков в динамической таблице автоматом отображались данные изменения и добавления.
    Но в некоторых случаях происходит некое зависание, данный список не раскрывается, он будто не видит данных которые необходимо отобразить.
    Подскажите в чем может быть причина?

Поделитесь своим мнением

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


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти
Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2020 Excel для всех   Войти