Как сделать сложный выпадающий список в excel?

Как быстро создать многоуровневые (каскадные) выпадающие списки в Excel

В данной статье рассматривается методика быстрого создания многоуровневых (каскадных) выпадающих списков в Excel на основе умных таблиц для моделирования иерархических данных. В качестве примера в видеоуроке создаются 6-уровневые выпадающие списки.

Видеоурок к статье:

Первая таблица Группы_товаров связывает группы товаров и категории: в магазине 2 группы товаров – поля Продукты_питания и Одежда , каждая из которых включает по 2 категории товаров: продукты питания состоят из элементов Молочные_продукты и Мясо , одежда – Верхняя_одежда и Спортивные_товары .

Обратите внимание! Элементы таблицы будут являться одновременно названием для нижестоящих таблиц. К именам таблиц предъявляются специальные требования: имя должно начинаться с буквы, не должно содержать пробелов и специальных символов.

На следующем уровне создаются таблицы, связывающие категории и виды продукции: в приведенном примере создана таблица с именем Молочные_продукты , содержащая поля Молоко (подразделяется на козье и коровье) и Сыр (подразделяется на твердый и полутвердый), а также таблица с именем Мясо , содержащая поля Красное_мясо (подразделяется на говядина и свинина) и Птица (подразделяется на курицу и индейку).

Остальные данные организуются по такому же принципу.

Список 1 уровня

Создать список конкретных умных таблиц книги: Таблица1;Таблица2;Таблица3

Создать список полей конкретной таблицы:

=ДВССЫЛ(» Таблица1 «&»[#Заголовки]»)

Создать список элементов конкретного поля конкретной таблицы: =ДВССЫЛ(» Таблица1 «&»[ Поле1 ]»)

Список 2 уровня

Создать список полей таблицы, выбираемой в вышестоящем списке:

=ДВССЫЛ( A1 &»[#Заголовки]»)

Создать список элементов выбираемого поля конкретной таблицы: =ДВССЫЛ(» Таблица1 «&»[«& А1 &»]»)

Список 3 уровня

Создать список элементов выбираемого поля выбираемой таблицы: =ДВССЫЛ( A1 &»[«& B1 &»]»)

После того, как данные организованы, необходимо непосредственно создать выпадающие списки, которые задаются специальными формулами, вводимыми в поле Источник в меню Проверка данных – Список:

Формулы можно задать вручную, что, однако, достаточно трудоемко. Лучше всего воспользоваться специальной группой команд Выпадающие списки в надстройке SubEx для Excel , которая моментально сформирует за вас нужные формулы!

Всего возможны три вида выпадающих списков:

1 уровня (первичный, не связанный ни с чем список)

2 уровня (имеющий одну связь на один вышестоящий выпадающий список: имя таблицы или имя поля)

3 уровня (имеющий две связи на вышестоящие списки: на имя таблицы и имя поля)

Обратите внимание! Все уровни иерархии моделируются с использованием этих трёх видов выпадающих списков, сгруппированных в различных комбинациях.

Итак, вот формулы, которые подставляются в Проверку данных в зависимости от конкретной ситуации. Обратите внимание, вместо красного текста подставляются непосредственно названия таблиц и полей, а вместо оранжевого — ставится ссылка на выпадающий список вышестоящего уровня:

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

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

Как было сказано выше полученная система таблиц и списков является полностью динамической: можно добавлять новые поля и элементы в любую таблицу и они автоматически подвяжутся в выпадающие списки.

Подробный пример создания 6-уровневых выпадающих списков на примере номенклатуры товаров магазина приведен в видеоуроке!

Выпадающий список в Excel — Инструкция по созданию

Выпадающий список в Excel – это довольно удобная функция, которая поможет создать более сложные документы и сделать работу удобной для визуального восприятия пользователя.

Несколько наиболее распространенных типов выпадающих списков, которые можно создать в программе Excel:

  • С функцией мультивыбора;
  • С наполнением;
  • С добавлением новых элементов;
  • С выпадающими фото;
  • Другие типы.

    Сделать список в Эксель с мультивыбором

    Создать перечень в ячейке программы можно с помощью встроенных инструментов панели управления.

    Рассмотрим подробнее все основные и самые распространенные типы, и процесс их создание на практике.

    Совет! Классический всплывающий список программы имеет функцию мультивыбора, то есть пользователь, нажав на соответствующую группу, может выбрать для нее соответствующий вариант.

    Мультивыбор необходим, когда с помощью одного перечня нужно задать значение нескольким ячейкам документа.

    Внешний вид с мультивыбором для заполнения сразу нескольких ячеек

    Чтобы создать такой, следуйте инструкции:

    • Выделите ячейки. Если посмотреть на рисунок, то выделять нужно начиная с C2 и заканчивая C5;
    • Найдите вкладку «Данные», которая расположена на главной панели инструментов в окне программы. Затем нажмите на клавишу проверки данных, как показано на рисунке ниже;

    • В открывшемся окне откройте самую первую вкладку и выберите элемент, как показано на рисунке. Таким образом в данных местах создастся перечень. В текстовом поле укажите диапазон тех ячеек, которые будут заполняться при каждом выборе элементов.

    Заполнение информации для создания выпадающего списка с мультивыбором значений ячеек

    Пример заполнения:

    Таким образом вы создадите классический горизонтальный список с функцией мультивыбора.

    Однако, чтобы он заполнялся автоматически, необходимо создать макрос со следующим исходным кодом, который показан на рисунке.

    Программный код для создания макроса

    Еще рекомендуем прочитать эти статьи:

    • Горячие клавиши Excel — Самые необходимые варианты
    • Формулы EXCEL с примерами — Инструкция по применению
    • Как построить график в Excel — Инструкция
    Читать еще:  Как сделать сводную диаграмму в excel 2010?

    Создать список в Экселе с наполнением

    Стандартный перечень с наполнением позволит вам автоматизировать процесс ввода информации. При нажатии на ее, появится выбор ее возможных значений.

    Пользователю остается только выбрать необходимое значение для заполнения.

    Пользовательский список с наполнением

    Самый простой способ создать такой перечень – использовать «умные таблицы программы».

    С их помощью можно легко и быстро форматировать необходимые вам виды списков с наполнением:

    • Выделите необходимые ячейки и нажмите в главной вкладке на клавишу «Форматировать как таблицу»;

    Пример форматирования и расположение клавиш:

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

    Форматирование перечня с наполнением с помощью «умных таблиц»

    Создать раскрывающийся список в ячейке (версия программы 2010)

    Также можно создавать перечень в ячейке листа.

    Пример указан на рисунке ниже:

    Пример в ячейке листа

    Чтобы создать такой, следуйте инструкции:

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

    Заполнение и проверка вводимых значений в процессе создания списка с заполнением ячейки листа

    Также вам может быть интересно:

    • Округление в Excel — Пошаговая инструкция
    • Таблица Эксель — Cоздание и настройка

    В статье были рассмотрены основные типы выпадающих списков и способы их создания. Помните, что процесс их создания идентичен в таких версиях программы: 2007, 2010, 2013.

    Выпадающие списки помогут вам не только настроить более быструю работу всего документа, но и сделать его адаптированным для работы с большими проектами.

    Тематические видеоролики к статье:

    Создание выпадающих списков в Excel

    4 способа создать выпадающий список на листе Excel.

    Как сделать раскрывающийся список в Excel

    Как в ексель сделать выпадающий список Как добавить всплывающий список в ексель Как создать в ексел раскрывающийся список

    Многоуровневый связанный список в MS EXCEL

    Для моделирования сложных иерархических данных создадим Многоуровневый связанный список.

    Потребность в создании иерархических данных появляется при решении следующих задач:

    • ОтделСотрудники отдела. При выборе отдела из списка всех отделов компании, динамически должен формироваться список, содержащий всех сотрудников этого отдела (двухуровневая иерархия);
    • Город – Улица – Номер дома. При заполнении адреса проживания из списка городов нужно выбирать город, затем из списка всех улиц этого города – улицу, затем, из списка всех домов на этой улице – номер дома (трехуровневая иерархия).

    В этой статье рассмотрен Многоуровневый связанный список. Двухуровневый связанный список или просто Связанный список рассмотрен в статьях Связанный список и Расширяемый Связанный список. Материал статьи один из самых сложных на сайте Excel2.ru, поэтому необходимо для начала ознакомиться с вышеуказанными статьями.
    Многоуровневый связанный список будем реализовывать с помощью инструмента Проверка данных ( Данные/ Работа с данными/ Проверка данных ) с условием проверки Список.
    Создание Многоуровневого связанного списка рассмотрим на конкретном примере.

    Примечание : Рассмотренный в этой статье Многоуровневый связанный список на самом деле правильнее назвать Трехуровневым, т.к. создать четырехуровневый связанный список, используя рассмотренный здесь подход, очень проблематично. Для тех, кому требуется создать структуру с 4-мя и более уровнями, см. статью Многоуровневый связанный список типа Предок-Родитель.

    Постановка задачи

    Имеется перечень Регионов. Для каждого Региона имеется свой перечень Стран. Для каждой Страны имеется свой перечень Городов.

    Пользователь должен иметь возможность, выбрав определенный Регион, в соседней ячейке выбрать из Выпадающего (раскрывающегося) списка нужную ему Страну из этого Региона. В другой соседней ячейке пользователь должен иметь возможность выбрать нужный ему Город из этой Страны (см. файл примера ).

    В окончательном виде трехуровневый связанный список должен работать так:

    Сначала выберем, например, Регион «Америка» с помощью Выпадающего списка.

    Затем выберем Страну «США» из Региона «Америка».

    Причем перечень стран в выпадающем списке будет содержать только страны из выбранного на предыдущем шаге Региона «Америка».

    И, наконец, выберем Город «Атланта» из Страны «США».

    Причем перечень городов в выпадающем списке будет содержать только города из выбранной на предыдущем шаге Страны, т.е. из «США».

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

    Список Регионов и перечни Стран разместим на листе Страны.

    Обратите внимание, что названия Регионов (диапазон А2:А12 на листе Страны) в точности должны совпадать с заголовками столбцов, содержащих названия соответствующих Стран (В1:L1).

    Это требование обеспечивается формулой (см. статьи о Транспонировании).
    =ДВССЫЛ(АДРЕС(СТРОКА($A$1)-СТОЛБЕЦ($A$1)+СТОЛБЕЦ();1))

    с помощью которой формируются заголовки столбцов. Введем ее в диапазон ячеек В1:L1.

    Список Стран и перечни Городов разместим на листе Города.

    Откуда же возьмется перечень стран на листе Города? Очевидно, что после заполнения листа Страны названиями стран, необходимо, что они каким-то чудесным образом переместились на лист Города. Это чудесное перемещение организуем формулами. Список Стран сформируем на листе Города в столбце А с помощью решения приведенного в статье Объединение списков. Значения для этого списка будем брать из Именованного диапазона Диап_Стран (его нужно предварительно создать через Диспетчер имен). Именованный диапазон Диап_Стран образуем формулой:

    Для формирования списка Стран нам также понадобится Именованная формула Строки_Столбцы_Стран

    Окончательная формула в столбце А на листе Города выглядит так:

    сформирует необходимый нам список Стран.

    Теперь создадим Динамический диапазон для формирования Выпадающего списка содержащего названия Регионов. Для этого необходимо:

    • нажать кнопку меню «Присвоить имя» ( Формулы/ Определенные имена/ Присвоить имя );
    • в поле Имя ввести Регионы;
    • в поле Диапазон ввести формулу

    Формула подсчитывает количество элементов в столбце А на листе Страны (функция СЧЁТЗ() ) и определяет ссылку на последний элемент в столбце (функция ИНДЕКС() ), тем самым формируется диапазон, содержащий все значения Регионов. Пропуски в столбце А не допускаются.

    Аналогичным образом создадим Динамический диапазон Список_Стран для формирования выпадающего списка содержащего названия стран:

    Создадим Именованную формулу Позиция_региона для определения позиции, выбранного пользователем региона, в созданном выше диапазоне Регионы:

    Т.к. в формуле использована относительная адресация, то важно перед созданием формулы сделать активной ячейку B5 на листе Таблица.

    Аналогичным образом создадим именованную формулу для определения позиции, выбранной пользователем страны, в диапазоне Список_Стран =ПОИСКПОЗ(таблица!B5;Список_Стран;0) . Перед созданием формулы нужно сделать активной ячейку С5 на листе Таблица.

    Создадим Именованные константы МаксСтран равную 20 и МаксГородов равную 30. Константы соответствует максимальному количеству стран в регионе и, соответственно, максимальному количеству городов в стране. Эти значения произвольны и их можно изменить.

    Читать еще:  Таблица в excel 2010 как сделать

    Создадим именованный диапазон Выбранный_Регион для определения диапазона на листе Страны, содержащего страны выбранного региона:

    Теперь, например, при выборе региона Америка функция СМЕЩ() вернет ссылку на диапазон страны!$B$2:$B$20

    Создадим аналогичный диапазон Выбранная_Страна для определения диапазона на листе Города, содержащего города выбранного региона: =СМЕЩ(города!$A$2;;Позиция_страны;МаксГородов)

    Создадим две последние именованные формулы Страны и Города:
    =СМЕЩ(страны!$A$2;;Позиция_региона;СЧЁТЗ(Выбранный_Регион))
    =СМЕЩ(города!$A$2;;Позиция_страны;СЧЁТЗ(Выбранная_Страна))

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

    • выделяем диапазон B5:B22 налисте Таблица;
    • вызываем инструмент Проверка данных,
    • устанавливаем тип данных Список,
    • в поле Источник вводим: =Страны .

    Также создадим связанный выпадающий список для ячеек из столбца Город (диапазон С5:С22, в поле Источник вводим: =Города )

    На листе Таблица после выбора Региона и Страны теперь есть возможность выбора Города.

    Для добавления новых Регионов и их Стран достаточно ввести новый Регион в столбец A (лист Страны), в строке 1 автоматически отобразится соответствующий заголовок. Под появившимся заголовком в строке 1 введите страны нового Региона.
    Для добавления новых Городов, на листе Города в строке 1 найдите нужное название страны (оно автоматически появится там после добавления страны на листе Страны). Под этим заголовком введите название города.

    СОВЕТ: В этой статье города (и страны) размещены в нескольких столбцах. Обычно однотипные значения размещают в одном столбце (списке). В статье Многоуровневый связанный список в MS EXCEL на основе таблицы все исходные данные размещены на одном листе, а однотипные данные (названия городов) — в одном столбце. Это облегчает написание формул и позволяет создать списки с большим количеством уровней иерархии (4-6).

    Связанные статьи

    Комментарии

    Здравствуйте! Возможна ли реализация раскрывания 2 и 3 уровня списка при наведении на первый? То-есть чтобы пользователь не раскрывал 3 списка, а нажал 1 раз и мог выбрать значение сразу из 3го уровня?
    Спасибо!

    Я не могу скачать файл с примером, помогите пожалуйства

    Все замечательно. Только вот после корректировки списка стран, в выпадающем списке стран выпадает только одна страна. Как так?

    Перепроверил, должно все работать, скачайте файл примера.

    Здравствуйте. Спасибо за статью, всё получилось. Есть один существенный недостаток — при добавлении новой страны на листе города съезжают все страны. Можно ли это как-то автоматически поправить?

    Из многостолбцового списка стран, на листе Страны, автоматически формируется одностолбцовый список стран на листе Города и заголовки-названия стран в первой строке. Действительно, при добавлении новых стран, если для каждой страны заполнены города, происходит смещение заголовков на листе Города в зависимости от того в какой регион была добавлена страна. Это своеобразная «плата» за автоматическое формирование одностолбцового списка стран на листе Города, в столбце А. Чтобы избавиться от смещения нужно вставить столбец А как значения, а новые страны на листе города добавлять вручную вниз списка. Или передвигать названия городов вслед за смещением заголовков стран. Хуже с удалением стран с листа Страны, на листе города исчезновение страны заметить очень сложно. Вывод: Excel не предназначен для таких сложных структур, используйте ACCESS.

    Добрый день! Возможно ли вот эту функцию на закладке Города =IFERROR(INDEX(Диап_Стран;—RIGHT(SMALL(Строки_Столбцы_Стран;ROW(Z1));2);—LEFT(SMALL(Строки_Столбцы_Стран;ROW(Z1));LEN(SMALL(Строки_Столбцы_Стран;ROW(Z1)))-2));»») адаптировать для 2003 Excela? При пересохранении вашего файла в низшую версию (2003 Excel) именно она не работает, выдает ошибку.

    Все дело в функции ЕСЛИОШИБКА() О том как ее заменить см. статью Функция ЕСЛИОШИБКА() в MS EXCEL

    День добрый! Подскажите, возможно ли какими то способами (формулами) сделать выпадающее меню из — к примеру всего столбца $A:$A, то есть чтобы эксель сам нашел текст, и вставил его в выпадающее меню без пробелов?
    Вот что я имею ввиду:
    у меня есть столбец A,и я запишу цифры (то есть имена строк), и текст который необходимо воткнуть в выпадающее меню:
    _____А_____|____B____|
    1
    2
    3___Яблоко
    4___Груша
    5___Вишня
    6
    7
    8

    И все, возможно ли воткнуть этот текст в выпадающее меню (указывая ТОЛЬКО весь столбец $А:$A)? И при этом чтобы пустых строчек не было в меню?! Спасибо заранее!

    Если списки большие, то формулы массива могут притормаживать.

    В общем попробовал я ваш метод, не получается. Вот какая ситуация у меня происходит. Во всех ячейках в одном столбце, почти одна и та же формула: =ЕСЛИ($J$1=$A$3;B3;»»), меняются только ссылки на ячейки. У меня есть 2 таблицы, они подписаны: таблица 5 и таблица 5а, напротив каждой из них, по несколько строчек в одном столбце.
    ——-А——-|——-B——|———С———|———D———|
    1_|____________|______________|__________________|__________________|
    2_|____________|_5.1.Дерево___|_»ТАБЛИЦА 5″[V]___|_5.1.Дерево_______|
    3_|_Таблица 5__|_5.2.Куст_____|__________________|_5.2.Куст_________|
    4_|____________|_5.3.Трава____|__________________|_5.3.Трава________|
    —————|______________|__________________|__________________|
    5_|____________|_5а.1.Утюг____|__________________|__________________|
    6_|_Таблица 5а_|_5a.2.Мел_____|__________________|__________________|
    7_|____________|_5a.3.Кот_____|__________________|__________________|

    Ну так вот, в столбце «С» во 2 ячейке, у меня ссылка на выпадающее меню, в котором выбирается Таблица 5, или таблица 5а. Если таблица 5 выбирается, тогда 5.1, 5.2, и 5.3 появляется по формуле написанной ниже в столбце D.
    =ЕСЛИ(ЕОШИБКА(ИНДЕКС(ТАБЛИЧКИ;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$1:D1;ТАБЛИЧКИ);0)));»»;ИНДЕКС(ТАБЛИЧКИ;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$1:D1;ТАБЛИЧКИ);0)))
    А именнованная формула ТАБЛИЧКИ, выглядит вот так:
    =СМЕЩ($B$2;;;СЧЁТЗ($B$2:$B$100))

    Все вроде бы нормально, когда выбираю таблицу 5, А ВОТ когда выбираю таблицу 5а, все идет сикось накось, ТО ЕСТЬ по сути у меня должно 5а.1., 5а.2. и т.д., появится в ячейках D2 и ниже, но НЕТ, не появляется, заместо этого у меня пустота (от нулевого значения я избавился с вашей помощью)

    ———А——-|——-B——|———С———|———D———| 1_|____________|______________|__________________|__________________| 2_|____________|_5.1.Дерево___|_»ТАБЛИЦА 5а»[V]__|__________________|
    3_|_Таблица 5___|_5.2.Куст_____|__________________|__________________| 4_|____________|_5.3.Трава____|__________________|__________________| —————|______________|__________________|__________________|
    дальше не стал тут вырисовывать, тоже самое что и в верхней таблице.

    Помогите пожалуйста, заранее огромное спасибо Вам. Сайт просто замечательный, но что-то пока не догоняю никак 🙁

    Читать еще:  Как в excel сделать округление до 10?

    Выпадающий список в Excel с помощью инструментов или макросов

    Под выпадающим списком понимается содержание в одной ячейке нескольких значений. Когда пользователь щелкает по стрелочке справа, появляется определенный перечень. Можно выбрать конкретное.

    Очень удобный инструмент Excel для проверки введенных данных. Повысить комфорт работы с данными позволяют возможности выпадающих списков: подстановка данных, отображение данных другого листа или файла, наличие функции поиска и зависимости.

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

    Путь: меню «Данные» — инструмент «Проверка данных» — вкладка «Параметры». Тип данных – «Список».

    Ввести значения, из которых будет складываться выпадающий список, можно разными способами:

    1. Вручную через «точку-с-запятой» в поле «Источник».
    2. Ввести значения заранее. А в качестве источника указать диапазон ячеек со списком.
    3. Назначить имя для диапазона значений и в поле источник вписать это имя.

    Любой из вариантов даст такой результат.

    Выпадающий список в Excel с подстановкой данных

    Необходимо сделать раскрывающийся список со значениями из динамического диапазона. Если вносятся изменения в имеющийся диапазон (добавляются или удаляются данные), они автоматически отражаются в раскрывающемся списке.

    1. Выделяем диапазон для выпадающего списка. В главном меню находим инструмент «Форматировать как таблицу».
    2. Откроются стили. Выбираем любой. Для решения нашей задачи дизайн не имеет значения. Наличие заголовка (шапки) важно. В нашем примере это ячейка А1 со словом «Деревья». То есть нужно выбрать стиль таблицы со строкой заголовка. Получаем следующий вид диапазона:
    3. Ставим курсор в ячейку, где будет находиться выпадающий список. Открываем параметры инструмента «Проверка данных» (выше описан путь). В поле «Источник» прописываем такую функцию:

    Протестируем. Вот наша таблица со списком на одном листе:

    Добавим в таблицу новое значение «елка».

    Теперь удалим значение «береза».

    Осуществить задуманное нам помогла «умная таблица», которая легка «расширяется», меняется.

    Теперь сделаем так, чтобы можно было вводить новые значения прямо в ячейку с этим списком. И данные автоматически добавлялись в диапазон.

    1. Сформируем именованный диапазон. Путь: «Формулы» — «Диспетчер имен» — «Создать». Вводим уникальное название диапазона – ОК.
    2. Создаем раскрывающийся список в любой ячейке. Как это сделать, уже известно. Источник – имя диапазона: =деревья.
    3. Снимаем галочки на вкладках «Сообщение для ввода», «Сообщение об ошибке». Если этого не сделать, Excel не позволит нам вводить новые значения.
    4. Вызываем редактор Visual Basic. Для этого щелкаем правой кнопкой мыши по названию листа и переходим по вкладке «Исходный текст». Либо одновременно нажимаем клавиши Alt + F11. Копируем код (только вставьте свои параметры).
    5. Сохраняем, установив тип файла «с поддержкой макросов».
    6. Переходим на лист со списком. Вкладка «Разработчик» — «Код» — «Макросы». Сочетание клавиш для быстрого вызова – Alt + F8. Выбираем нужное имя. Нажимаем «Выполнить».

    Когда мы введем в пустую ячейку выпадающего списка новое наименование, появится сообщение: «Добавить введенное имя баобаб в выпадающий список?».

    Нажмем «Да» и добавиться еще одна строка со значением «баобаб».

    Выпадающий список в Excel с данными с другого листа/файла

    Когда значения для выпадающего списка расположены на другом листе или в другой книге, стандартный способ не работает. Решить задачу можно с помощью функции ДВССЫЛ: она сформирует правильную ссылку на внешний источник информации.

    1. Делаем активной ячейку, куда хотим поместить раскрывающийся список.
    2. Открываем параметры проверки данных. В поле «Источник» вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).

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

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

    Возьмем три именованных диапазона:

    Это обязательное условие. Выше описано, как сделать обычный список именованным диапазоном (с помощью «Диспетчера имен»). Помним, что имя не может содержать пробелов и знаков препинания.

    1. Создадим первый выпадающий список, куда войдут названия диапазонов.
    2. Когда поставили курсор в поле «Источник», переходим на лист и выделяем попеременно нужные ячейки.
    3. Теперь создадим второй раскрывающийся список. В нем должны отражаться те слова, которые соответствуют выбранному в первом списке названию. Если «Деревья», то «граб», «дуб» и т.д. Вводим в поле «Источник» функцию вида =ДВССЫЛ(E3). E3 – ячейка с именем первого диапазона.

    Выбор нескольких значений из выпадающего списка Excel

    Бывает, когда из раскрывающегося списка необходимо выбрать сразу несколько элементов. Рассмотрим пути реализации задачи.

    1. Создаем стандартный список с помощью инструмента «Проверка данных». Добавляем в исходный код листа готовый макрос. Как это делать, описано выше. С его помощью справа от выпадающего списка будут добавляться выбранные значения.
    2. Чтобы выбранные значения показывались снизу, вставляем другой код обработчика.
    3. Чтобы выбираемые значения отображались в одной ячейке, разделенные любым знаком препинания, применим такой модуль.
      Private Sub Worksheet_Change( ByVal Target As Range)
      On Error Resume Next
      If Not Intersect(Target, Range( «C2:C5» )) Is Nothing And Target.Cells.Count = 1 Then
      Application.EnableEvents = False
      newVal = Target
      Application.Undo
      oldval = Target
      If Len(oldval) <> 0 And oldval <> newVal Then
      Target = Target & «,» & newVal
      Else
      Target = newVal
      End If
      If Len(newVal) = 0 Then Target.ClearContents
      Application.EnableEvents = True
      End If
      End Sub

    Не забываем менять диапазоны на «свои». Списки создаем классическим способом. А всю остальную работу будут делать макросы.

    Выпадающий список с поиском

    1. На вкладке «Разработчик» находим инструмент «Вставить» – «ActiveX». Здесь нам нужна кнопка «Поле со списком» (ориентируемся на всплывающие подсказки).
    2. Щелкаем по значку – становится активным «Режим конструктора». Рисуем курсором (он становится «крестиком») небольшой прямоугольник – место будущего списка.
    3. Жмем «Свойства» – открывается перечень настроек.
    4. Вписываем диапазон в строку ListFillRange (руками). Ячейку, куда будет выводиться выбранное значение – в строку LinkedCell. Для изменения шрифта и размера – Font.

    При вводе первых букв с клавиатуры высвечиваются подходящие элементы. И это далеко не все приятные моменты данного инструмента. Здесь можно настраивать визуальное представление информации, указывать в качестве источника сразу два столбца.

  • Ссылка на основную публикацию
    Adblock
    detector