Просмотров: 34184

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

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

Хитрости »

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


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

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

Чтобы понять о чем пойдет речь в статье сначала необходимо понимать что такое выпадающий список и как его создать. Теперь попробуем разобраться что значит выражение "связанный выпадающий список". Я бы еще назвал такой список зависимым. Т.е. когда список значений одного выпадающего списка зависит от значения, выбранного в другом выпадающем списке или просто забитого в ячейку. Представим ситуацию: есть ячейка А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, 14 998 скачиваний)

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

Rating: 5.0/5. From 1 vote.

Please wait...

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

{"Bottom bar":{"textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24,"textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance":30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500,"textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100%; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive":"","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40}}

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

Access apple watch Multex Outlook Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Надстройки Настройка Печать Поиск данных Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx анализ данных баги и глюки в Excel ссылки
Источник: http://cc.bingj.com/cache.aspx?q=%d0%ba%d0%b0%d0%ba+%d0%b2+excel+%d1%81%d0%b4%d0%b5%d0%bb%d0%b0%d1%82%d1%8c+%d0%b7%d0%b0%d0%b2%d0%b8%d1%81%d0%b8%d0%bc%d1%8b%d0%b9+%d0%b2%d1%8b%d0%bf%d0%b0%d0%b4%d0%b0%d1%8e%d1%89%d0%b8%d0%b9+%d1%81%d0%bf%d0%b8%d1%81%d0%be%d0%ba&d=4979531009163531&mkt=en-US&setlang=en-US&w=71gDpaYpzuXYbcehJry83DDdpmESI9_O

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


Как сделать гамбургер на дому
Дэдпул карандашом поэтапно
Как сделать чтобы тебя не эвакуировали
Горн своими руками чертеж
Какие открытки можно сделать своими руками на день мамы




ШОКИРУЮЩИЕ НОВОСТИ