Связанные выпадающие списки
Связанные выпадающие списки.xls (216,5 KiB, 3 227 скачиваний)
Чтобы понять о чем пойдет речь в статье сначала необходимо понимать что такое выпадающий список и как его создать. Теперь попробуем разобраться что значит выражение "связанный выпадающий список". Я бы еще назвал такой список зависимым. Т.е. когда список значений одного выпадающего списка зависит от значения, выбранного в другом выпадающем списке или просто забитого в ячейку. Представим ситуацию: есть ячейка
Для начала нам потребуется создать все эти списки. Что-то вроде этого:
Далее для каждого из этих списков необходимо назначить именованный диапазон. Создать можно любым способом из описанных в этой статье. Главное помнить - если сами списки расположено на листе, отличном от того, на котором списки выпадающие - то обязательно создавать именованный диапазон с назначением области действия - Книга.
В приложенном к статье примере диапазоны имеют имена категорий - их можно видеть в заголовках.
В ячейке А2 создаем "список списков" - основной список, на основании значений которого будет создаваться второй список. Этот список может быть создан любым способом (как создать выпадающий список). Назовем его Список категорий.
В ячейке В2 потребуется создать список на основании формулы, хоть по сути и так же, как и остальные: вкладка Данные
Сами списки товара могут находится и в другой книге. Если книга называется Книга со списком.xls и на Лист1 в ячейке А1 в этой книге находится имя нужного нам списка, то формула будет выглядеть так:
- обе книги должны быть открыты. Если вы закроете книгу со списками, то получите ошибку - выпадающие списки просто перестанут работать
- созданные подобным образом связанные списки не будут работать с динамическими именованными диапазонами
И ничего с этими ограничениями не поделать при подобном подходе.
Tips_Lists_Connect_Validation.xls (26,5 KiB, 18 294 скачиваний)
Так же см.:
Выпадающие списки
Именованные диапазоны
Динамические именованные диапазоны
Статья помогла? Поделись ссылкой с друзьями!
Видеоуроки
Поиск по меткам
Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистикаКомментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум
У меня пара вопросов по связанным спискам:
1. Допустим, есть 3 магазина одной сети, наименование товаров (которые соответственно могут повторяться) и количество этих товаров. Получается 3 выпадающих списка. Так вот чтобы прописать формулу для третьего списка с выпадающими значениями, нужно и наименование товара этого магазина создавать уникальное? Можно ли этого избежать?
2. Вопрос вытекает из второго. Во втором списке выбираем товар. Можно ли сделать так, чтобы соответствующее товару количество появлялось в соседней графе автоматически, а не приходилось тыкать в список и выбирать одно-единственное значение ему соответствующее?
Анастасия, на мой взгляд Ваши вопросы мало отношения к статье имеют. Но по сайту можно было бы пробежаться, т.к. ответ на второй вопрос на поверхности. По порядку.
1 вопрос: Выводите значения на другой лист и на основании получившихся значений и формируйте список.
2 вопрос: ,
А формула, работающая с динамическим именным диапазоном есть? Заранее спасибо.
Скажите, а как сделать выпадающий список с возможностью выбора сразу нескольких позиций?Т.Е. в ячейке с выпадающим списком мне нужно оставить несколько выбранных в списке позиций.
Извините если не по теме... никак не могу найти решение своей проблемы.
Андрей, такое возможно только путем программирования и применения элемента ListBox. Через выпадающие списки такое сделать невозможно. К тому же - как Вы собрались помещать в одну ячейку несколько элементов? Если напишите - может придумаем что-нибудь.
Уважаемые Гуру!
Как сделать выпадающий список сформированный из данных с другого листа? Т.е лист база. Есть ФИО и класс (из выпадающего списка) нужно на отдельном листе сформировать список:классы и список студентов из базы.
Заранее спасибо!!!
Добрый день.
Подскажите, пожалуйста, можно ли решить "малой кровью" мою проблему.
Есть прайс-лист учебных курсов (ок.40 шт.). Курсы объединены по направлениям. Например, за направлением "Иностранные языки" следуют курсы "Авиационный английский язык для летного состава", "Авиационный английский язык для ИТП", "Язык делового общения (английский)" и т.п.
На основе EXCEL делаю бланк заявки на обучение. В нем размещен этот (скрытый) прайс в диапазоне листа R23C13:R71C16. В нем
RC13 - номер позиции. Для направлений - целое число (1. 2. и т.п.) для курсов из направлений - дробное (1.1, 1.2, 1.3 и т.д.)
RC14 - название направления или курса обучения
RC15 - сроки обучения
RC16 - стоимость
Прайс отсортирован по номеру позиции RC13
В отдельный диапазон R24C2:R31C2 продублированы названия 8-ми направлений подготовки.
Мой замысел состоит в том, чтобы заказчик после указания из списка направлений подготовки мог бы сделать выбор учебной программы из второго ниспадающего списка, в котором отражались бы курсы только заданного направления.
А после выбора тут же извлекались бы соответствующие данные по продолжительности и стоимости обучения (для справки и контроля).
Сделал все. Динамически правильно вычисляю и заношу в R2C9 смещение (например, 30) начала нужного поддиапазона относительно всего массива, заношу в R2C10 начальный его адрес (R52C14), в R2C11 заношу смещение (36) конца поддиапазона и в R2C12 его адрес (R58C14). Отладил механизм извлечения сроков и стоимости курса
И - адью... Как ни бьюсь - не могу для ниспадающего списка курсов динамически задавать границы нужного поддиапазона (напр.R52C14:R58C14), чтобы выбрать название требуемого курса.
Пробовал использовать механизм "Проверка данных". Создал список списков. Работает. На уровне выбора курса, но совершенно отсутствуют средства логического контроля соответствия названия курса и направления. Так, ничто не мешает пользователю после указания учебного курса снова изменить поле направления подготовки. И система не будет иметь ничего против...
Особый гемор со списком списков - как после указания названия курса найти данные о его продолжительности и цене. Ведь для стандартных функций поиска в массиве необходима его сортировка по названию. А в прайсе имеет смысл только сортировка по номеру позиции...
Можно, конечно, "рядышком" втихоря создавать клон прайса и его сортировать по названиям. Но, наверное, это плохая идея, поскольку будет отягощать форму. Ведь предполагается, что заказчики будут скачивать её с сайта, заполнять и отправлять нам по e-mail.
А прайс - штука достаточно подвижная... И каждая версия прайса будет неминуемо порождать новую версию формы заявки на обучение.
Надеюсь на Ваш совет... Извините за размер моего сообщения - иначе не передать суть проблемы
Виктор, а Вы когда свою проблему изливали не обратили внимания на строку:
Комментарии, не имеющие отношения к комментируемой статье, будут удаляться без уведомления и объяснения причин. Если есть вопрос по проблеме в Excel- добро пожаловаться на Форум
Это как раз Ваш случай. Понимать проблему по описанию, без файла - даже пытаться не стану. Я форум для того и делал, чтобы каждый свою проблему в статьи не выкидывал. Зарегистрируйтесь на форуме, ознакомьтесь с правилами, создайте тему, приложите файл и ответ получите гораздо быстрее.
Уважаемый Дмитрий,
Позвольте не согласиться с Вашей оценкой степени соответствия моего комментария к статье. В нем как раз идет речь о вариантах использования связанных списков.
Да, конечно, очень трудно проникнуться особенностями моей проблемы даже по столь пространному комментарию.
Особенно, при наличии парадигмы "даже пытаться не стану"...
Однако, поверьте, потребовался бы много крат бОльшый комментарий, если бы я приложил весь мой файл.
Понял, что моя "излитая" проблема здесь излишня.
Буду изгаляться и решать её через механизм "проверка данных".
Извините за беспокойство!
Видимо, проблема для Вас не такая уж важная, если из всего моего комментария Вы выхватили лишь одну фразу, которая была тупо вырвана из контекста: "Понимать проблему по описанию, без файла – даже пытаться не стану"
Основной упор - БЕЗ ФАЙЛА.
И возвели в парадигму лишь часть этой фразы Вы сами по своим соображениям(возможно лени) - думая, что Ваши ссылки на R23C13:R71C16 и прочее, не видя файла дадут кому-то кроме Вас хоть какое-то понятие Вашей проблемы.
И я нигде не упоминал, что комментарий не имеет отношения к статье - Вы бы увидели это, если бы прочитали мой комментарий так же внимательно как я Ваш. Но, видимо, Вам делать это не хотелось и Вы вычитали лишь то, что хотели вычитать, а не то, что было написано.
Удачи!
Народ подскажите как сделать:
есть 2 взамосвязанных выпадающих списка
Допустим в ячейке А1 наименование, в В1 размер, в С1 цена.
Надо чтобы при выборе наименования и размера менялась цена.
Проблема в том что наименований много и цена меняется из за наименования и так же от размера
Все решил в моем случае надо было для ячейки цена написать вот такую формулу:
=ЕСЛИ($A$16="Модуль_белый";СУММЕСЛИ(Размеры!B3:B8;"="&B16;Размеры!E3:E8);ЕСЛИ($A$16="Модуль_цвета_дерева";СУММЕСЛИ(Размеры!C3:C8;"="&B16;Размеры!F3:F8);ЕСЛИ($A$16="Модуль_текстиль";СУММЕСЛИ(Размеры!D3:D8;"="&B16;Размеры!G3:G8))))
Добрый день!
Сделал выпадающий список, всё работает. спасибо!
Формулу взял такую: =ДВССЫЛ("Регионы["&$T31&"]").( Регионы - это название таблицы).
В каждой колонке разное количество наименований, и в выпадающем списке отображается "КУЧА" пустых строк.
Как сделать, чтобы отображались только заполненые?
Если можно, то просьба подкорректировать мою формулу.
Сергей, обратите внимание на ограничения, которые накладываются на подобные списки. Они приведены в конце статьи.
Из них можно сделать вывод, что не получиться у Вас сделать желаемое.
Спасибо за ответ!
А можно через другую формулу это реализовать?
У меня задача, чтобы 2 поля завили от 3его...
то есть, в 1ом выбрал страну, от этого должны показываться только регионы этой страны(2столбец) и в 3ем показываются магазины в этой стране.
Сергей, в статье про динамические диапазоны две формулы. Но обе они для динамических списков. Сделать же связанные списки без ДВССЫЛ и без применения макросов нельзя. Поэтому...Никак.