Хитрости »
Основные понятия (22)Здесь собраны статьи, в которых разъясняются базовые понятия работы в Excel и VBA, а так же проблемы, с которыми сталкивается большинство начинающих
Сводные таблицы и анализ данных (5) Раздел поможет изучить сводные таблицы и научиться их использовать "на полную"
Графики и диаграммы (4) Раздел поможет научиться создавать диаграммы и графики в Excel, в том числе нестандартные
Работа с VB проектом (10) С помощью статей раздела вы научитесь создавать процедуры программно и выполнять различные операции с объектами самого VBA
Power BI и Power Query (5) Здесь собраны статьи, раскрывающие различные возможности мощнейшего инструмента для визуализаций бизнесс-процессов Power BI и надстройки для Excel Power Query
Условное форматирование (5) Этот раздел поможет поближе познакомиться с Условным форматированием на примерах различных ситуаций
Списки и диапазоны (5) Статьи, посвященные работе не только с выпадающими списками, но и с диапазонами и хитростями их применения в рабочих файлах
Макросы(VBA процедуры) (59) Статьи раздела направлены на изучение VBA с детальным разбором кодов. Множество статей с примерами кодов под всевозможные ситуации с комментариями и пояснениями
Разное (34) Собраны статьи, которые не подходят ни под одну из представленных выше категорий или входят сразу в несколько. Но эти статье не менее полезные!

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

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

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


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

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

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

    У меня пара вопросов по связанным спискам:
    1. Допустим, есть 3 магазина одной сети, наименование товаров (которые соответственно могут повторяться) и количество этих товаров. Получается 3 выпадающих списка. Так вот чтобы прописать формулу для третьего списка с выпадающими значениями, нужно и наименование товара этого магазина создавать уникальное? Можно ли этого избежать?
    2. Вопрос вытекает из второго. Во втором списке выбираем товар. Можно ли сделать так, чтобы соответствующее товару количество появлялось в соседней графе автоматически, а не приходилось тыкать в список и выбирать одно-единственное значение ему соответствующее?

  2. Анастасия, на мой взгляд Ваши вопросы мало отношения к статье имеют. Но по сайту можно было бы пробежаться, т.к. ответ на второй вопрос на поверхности. По порядку.
    1 вопрос: Как получить список уникальных(не повторяющихся) значений? Выводите значения на другой лист и на основании получившихся значений и формируйте список.
    2 вопрос: Как найти значение в другой таблице или сила ВПР, ВПР с поиском по нескольким листам

  3. Владимир:

    А формула, работающая с динамическим именным диапазоном есть? Заранее спасибо.

  4. Андрей:

    Скажите, а как сделать выпадающий список с возможностью выбора сразу нескольких позиций?Т.Е. в ячейке с выпадающим списком мне нужно оставить несколько выбранных в списке позиций.
    Извините если не по теме... никак не могу найти решение своей проблемы.

    • Андрей, такое возможно только путем программирования и применения элемента ListBox. Через выпадающие списки такое сделать невозможно. К тому же - как Вы собрались помещать в одну ячейку несколько элементов? Если напишите - может придумаем что-нибудь.

  5. Ника:

    Уважаемые Гуру!
    Как сделать выпадающий список сформированный из данных с другого листа? Т.е лист база. Есть ФИО и класс (из выпадающего списка) нужно на отдельном листе сформировать список:классы и список студентов из базы.
    Заранее спасибо!!!

  6. Виктор:

    Добрый день.
    Подскажите, пожалуйста, можно ли решить "малой кровью" мою проблему.
    Есть прайс-лист учебных курсов (ок.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- добро пожаловаться на Форум

      Это как раз Ваш случай. Понимать проблему по описанию, без файла - даже пытаться не стану. Я форум для того и делал, чтобы каждый свою проблему в статьи не выкидывал. Зарегистрируйтесь на форуме, ознакомьтесь с правилами, создайте тему, приложите файл и ответ получите гораздо быстрее.

  7. Виктор:

    Уважаемый Дмитрий,
    Позвольте не согласиться с Вашей оценкой степени соответствия моего комментария к статье. В нем как раз идет речь о вариантах использования связанных списков.
    Да, конечно, очень трудно проникнуться особенностями моей проблемы даже по столь пространному комментарию.
    Особенно, при наличии парадигмы "даже пытаться не стану"...
    Однако, поверьте, потребовался бы много крат бОльшый комментарий, если бы я приложил весь мой файл.
    Понял, что моя "излитая" проблема здесь излишня.
    Буду изгаляться и решать её через механизм "проверка данных".
    Извините за беспокойство!

    • Видимо, проблема для Вас не такая уж важная, если из всего моего комментария Вы выхватили лишь одну фразу, которая была тупо вырвана из контекста: "Понимать проблему по описанию, без файла – даже пытаться не стану"
      Основной упор - БЕЗ ФАЙЛА.
      И возвели в парадигму лишь часть этой фразы Вы сами по своим соображениям(возможно лени) - думая, что Ваши ссылки на R23C13:R71C16 и прочее, не видя файла дадут кому-то кроме Вас хоть какое-то понятие Вашей проблемы.
      И я нигде не упоминал, что комментарий не имеет отношения к статье - Вы бы увидели это, если бы прочитали мой комментарий так же внимательно как я Ваш. Но, видимо, Вам делать это не хотелось и Вы вычитали лишь то, что хотели вычитать, а не то, что было написано.
      Удачи!

  8. Юрий:

    Народ подскажите как сделать:
    есть 2 взамосвязанных выпадающих списка
    Допустим в ячейке А1 наименование, в В1 размер, в С1 цена.
    Надо чтобы при выборе наименования и размера менялась цена.
    Проблема в том что наименований много и цена меняется из за наименования и так же от размера

  9. Юрий:

    Все решил в моем случае надо было для ячейки цена написать вот такую формулу:
    =ЕСЛИ($A$16="Модуль_белый";СУММЕСЛИ(Размеры!B3:B8;"="&B16;Размеры!E3:E8);ЕСЛИ($A$16="Модуль_цвета_дерева";СУММЕСЛИ(Размеры!C3:C8;"="&B16;Размеры!F3:F8);ЕСЛИ($A$16="Модуль_текстиль";СУММЕСЛИ(Размеры!D3:D8;"="&B16;Размеры!G3:G8))))

  10. Сергей:

    Добрый день!

    Сделал выпадающий список, всё работает. спасибо!

    Формулу взял такую: =ДВССЫЛ("Регионы["&$T31&"]").( Регионы - это название таблицы).

    В каждой колонке разное количество наименований, и в выпадающем списке отображается "КУЧА" пустых строк.
    Как сделать, чтобы отображались только заполненые?
    Если можно, то просьба подкорректировать мою формулу.

    • Сергей, обратите внимание на ограничения, которые накладываются на подобные списки. Они приведены в конце статьи.
      Из них можно сделать вывод, что не получиться у Вас сделать желаемое.

      • Сергей:

        Спасибо за ответ!

        А можно через другую формулу это реализовать?
        У меня задача, чтобы 2 поля завили от 3его...
        то есть, в 1ом выбрал страну, от этого должны показываться только регионы этой страны(2столбец) и в 3ем показываются магазины в этой стране.

        • Сергей, в статье про динамические диапазоны две формулы. Но обе они для динамических списков. Сделать же связанные списки без ДВССЫЛ и без применения макросов нельзя. Поэтому...Никак.

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

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


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

Логин
Наши партнеры
Перейти
Перейти
Счетчики
Анализ сайта

Яндекс.Метрика
© 2016 Excel для всех  Войти