Как в excel сделать зависимые списки в: Связанные (зависимые) выпадающие списки

Зависимые выпадающие списки в Excel — TutorExcel.Ru

Зависимые выпадающие списки в Excel

Изучим простой способ создания зависимых (также называют связанных) выпадающих списков в Excel.

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

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

: на первом уровне (списке) — категории блюд, на втором — блюда из выбранной категории.

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

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

Выделяем диапазон ячеек A2:A6 и создаем диапазон с именем Пицца, аналогичные действия повторяем и для списков с суши (имя диапазона — Суши) и пастой (имя диапазона — Паста):

Обратите внимание, что при создании имен диапазонов имя не должно включать в себя пробелы.
В том случае если в названии категории все же содержится пробел (например, Японская кухня), то одним из вариантов решения является использование нижнего подчеркивания вместо пробела в имени диапазона (Японская_кухня).

Создадим первый выпадающий список в ячейке A10, состоящий из категорий блюд (Пицца, Суши и Паста). В панели вкладок выбираем Данные -> Работа с данными -> Проверка данных, указываем тип данных Список и в качестве источника выделяем диапазон A1:C1:

Теперь создаем второй выпадающий список, полностью повторяем действия с созданием первого списка, только в поле Источник записываем формулу =ДВССЫЛ(A10):

Имена созданных диапазонов обязательно должны совпадать с элементами первого списка, поэтому если в первом списке есть категории содержащие пробелы, то при обращении к имени диапазона необходимо заменить пробелы на нижние подчеркивания.
Это можно осуществить с помощью функции ПОДСТАВИТЬ, которая позволяет заменить старый текст (пробел) на новый текст (нижнее подчеркивание) в текстовой строке, т.е. в нашем случае формула примет вид =ДВССЫЛ(ПОДСТАВИТЬ(A10;» «;»_»)).
Также минусом данного способа создания списков является невозможность использования динамических именованных диапазонов.

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

Удачи вам и до скорой встречи на страницах блога Tutorexcel.ru!

Поделиться с друзьями:

Поиск по сайту:

Выпадающие списки,Функции

• ← Точечная диаграмма в Excel
• Всплывающие подсказки в Excel →

Зависимые выпадающие списки в Excel

Перейти к содержанию

Search for:

Главная » Уроки MS Excel

Автор Антон Андронов На чтение 2 мин Опубликовано 15. 06.2015

Этот пример описывает, как создать зависимые выпадающие списки в Excel. Вот то, чего мы попытаемся добиться:

Когда пользователь выбирает «Pizza» из первого раскрывающегося списка…

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

Примечание переводчика: Лист, на котором будут создаваться связанные выпадающие списки, назовем Sheet1, а вспомогательный лист – Sheet2.

Чтобы создать такие зависимые выпадающие списки, действуйте в соответствии с нашей инструкцией:

1. Создайте следующие именованные диапазоны на листе Sheet2:
   

   Имя диапазона	Адрес диапазона
   Food	A1:A3
   Pizza	В1:В4
   Pancakes	С1:С2
   Chinese	D1:D3

2. Выделите ячейку B1 на листе Sheet1 .
3. На вкладке Data (Данные) нажмите кнопку Data Validation (Проверка данных).Откроется диалоговое окно Data Validation (Проверка вводимых значений).
4. Выберите List (Список) из раскрывающегося списка Allow (Тип данных).
5. Поместите курсор в поле Source (Источник) и введите «=Food».
6. Нажмите ОК.

Результат:

7. Теперь выделите ячейку Е1.
8. Выберите List (Список) из выпадающего списка Allow (Тип данных).
9. Поместите курсор в поле Source (Источник) и введите:

   =INDIRECT($B$1)
=ДВССЫЛ($B$1)

10. Нажмите ОК.

Результат:

Пояснение: Функция INDIRECT (ДВССЫЛ) возвращает ссылку, заданную текстовым значением. Например, пользователь выбирает «Chinese» из первого выпадающего списка, а функция INDIRECT (ДВССЫЛ) возвращает ссылку на именованный диапазон Chinese, который находится на листе Sheet2. В результате второй раскрывающийся список состоит из блюд китайской кухни.

Урок подготовлен для Вас командой сайта office-guru.ru
Источник: http://www.excel-easy.com/examples/dependent-drop-down-lists.html
Перевел: Антон Андронов
Правила перепечатки
Еще больше уроков по Microsoft Excel

Оцените качество статьи. Нам важно ваше мнение:

Adblock
detector

Простой способ создания динамического зависимого раскрывающегося списка в Excel

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

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

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

• Сделать динамический выпадающий список в Excel
• Создать множественный зависимый раскрывающийся список
• Создать раскрывающийся раскрывающийся список, исключая пустые ячейки
• Отсортировать выпадающий список по алфавиту

Примечания:

• Этот новый способ создания раскрывающихся списков с динамическим массивом работает только в Excel 365 и Excel 2021. В преддинамическом Excel вам придется делать это старомодным способом, как описано в разделе Создание зависимого списка. вниз в Excel 2019 — 2007.
• Это решение для одной строки. Если вы хотите скопировать свои списки выбора на несколько строк , затем следуйте инструкциям в раскрывающемся списке Зависимые для нескольких строк.

Как создать динамический раскрывающийся список в Excel

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

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

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

1. Получите элементы для основного раскрывающегося списка

Для начала мы извлечем все различные названия фруктов из столбца A. Это может быть это можно сделать с помощью функции UNIQUE в ее простейшей форме — укажите список фруктов для первого аргумента ( array ) и опустите остальные необязательные аргументы, так как их значения по умолчанию отлично подходят для нас:

= UNIQUE(A3:A15)

Формула переходит в G3, и после нажатия клавиши Enter результаты автоматически переносятся в следующие ячейки.

2. Создайте основной раскрывающийся список

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

Ваш основной раскрывающийся список готов!

3. Получение элементов для зависимого раскрывающегося списка

Чтобы получить элементы для вторичного раскрывающегося меню, мы отфильтруем значения в столбце B на основе значения, выбранного в первом раскрывающемся списке. Это можно сделать с помощью другой функции динамического массива под названием FILTER:

=ФИЛЬТР(B3:B15, A3:A15=D3)

Где B3:B15 — исходные данные для зависимого раскрывающегося списка, A3:A15 — исходные данные для основного раскрывающегося списка, а D3 — основной выпадающая ячейка.

Чтобы убедиться, что формула работает правильно, вы можете выбрать какое-либо значение в первом раскрывающемся списке и просмотреть результаты, возвращаемые ФИЛЬТРОМ. Идеальный! 🙂

4. Создайте зависимый раскрывающийся список

Чтобы создать второй раскрывающийся список, настройте критерии проверки данных точно так же, как вы сделали для первого раскрывающегося списка на шаге 2. Но на этот раз укажите диапазон разлива, возвращаемый Функция ФИЛЬТР: =$H$3#

Вот так! Ваш зависимый раскрывающийся список Excel готов к использованию.

Советы и примечания:

• Чтобы новых записей включались в раскрывающийся список автоматически , отформатируйте исходные данные как таблицу Excel. Или вы можете включить в свои формулы несколько пустых ячеек, как показано в этом примере.
• Если ваши исходные данные содержат пропуски, вы можете отфильтровать пробелы с помощью этого решения.
• до отсортируйте по алфавиту элементы раскрывающегося списка, оберните свои формулы в функцию СОРТИРОВКИ, как описано в этом примере.

Как создать множественный зависимый выпадающий список в Excel

В предыдущем примере мы сделали выпадающий список зависимым от другой ячейки. Но что, если вам нужна многоуровневая иерархия, то есть раскрывающийся список 3 ^rd в зависимости от списка 2 ^nd или даже раскрывающийся список 4 ^th в зависимости от списка 3 ^rd . Это возможно? Да, вы можете настроить любое количество зависимых списков (конечно, разумное количество :).

Для этого примера мы поместили штаты/провинции в столбец C и теперь пытаемся добавить соответствующее раскрывающееся меню в G3:

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

1. Настройте первый раскрывающийся список

Основной раскрывающийся список создается точно так же, как и в предыдущем примере (см. шаги 1 и 2 выше). Единственным отличием является эталон диапазона разлива, который вы вводите в Source 9. Коробка 0004.

На этот раз формула UNIQUE находится в E8, а основной выпадающий список будет в E3. Итак, вы выбираете E3, нажимаете Data Validation и указываете эту ссылку: =$E$8#

2. Настройте второй раскрывающийся список

Как вы могли заметить, теперь столбец B содержит несколько вхождений одних и тех же экспортеров. . Но вы же хотите, чтобы в выпадающем списке были только уникальные имена, верно? Чтобы исключить все повторяющиеся вхождения, оберните функцию UNIQUE вокруг формулы FILTER и введите эту обновленную формулу в F8: 9.0005

=UNIQUE(FILTER(B3:B15, A3:A15=E3))

Где B3:B15 — исходные данные для второго раскрывающегося списка, A3:A15 — исходные данные для первого раскрывающегося списка, а E3 первая выпадающая ячейка.

После этого используйте следующую ссылку диапазона разливов для критериев проверки данных: =$F$8#

3. Настройте третий раскрывающийся список

Чтобы собрать элементы для раскрывающегося списка 3 ^rd , сделайте использование формулы ФИЛЬТР с несколькими критериями. Первый критерий сравнивает весь список фруктов со значением, выбранным в 1 ^st (A3:A15=E3), а второй критерий проверяет список экспортеров на соответствие выбору в раскрывающемся списке 2 ^nd (B3:B15=F3). Полная формула переходит в G8:

=ФИЛЬТР(C3:C15, (A3:A15=E3) * (B3:B15=F3))

Если вы собираетесь добавить больше зависимых раскрывающихся списков (4 ^th , 5 ^th и т. д.), то, скорее всего, столбец C будет содержать несколько вхождений одного и того же элемента. Чтобы предотвратить попадание дубликатов в подготовительную таблицу и, следовательно, в 3-й выпадающий список, вложите формулу ФИЛЬТР в функцию УНИКАЛЬНАЯ, как мы это делали на предыдущем шаге:

=УНИКАЛЬНЫЙ(ФИЛЬТР(C3:C15, (A3:A15=E3) * (B3:B15=F3)))

Последнее, что вам нужно сделать, это создать еще одно правило проверки данных с этим Исходная ссылка : =$G$8#

Ваш раскрывающийся список с множественными зависимостями готов к работе!

Совет. Аналогичным образом можно получить элементы для последующих выпадающих списков . Предполагая, что столбец D содержит исходные данные для раскрывающегося списка 4 ^th , вы можете ввести следующую формулу в H8, чтобы получить соответствующие элементы:

=УНИКАЛЬНЫЙ(ФИЛЬТР(D3:D15, (A3:A15=E3) * (B3:B15=F3) * (C3:C15=G3)))

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

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

Если по какой-то причине использование таблицы Excel невозможно, вы можете расширить свой раскрывающийся список следующим образом:

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

Помня об этих двух моментах, давайте уточним формулы в нашей таблице подготовки данных. Правила проверки данных вообще не требуют каких-либо корректировок.

Формула для основного раскрывающегося списка

С именами фруктов в A3:A15 мы добавляем 5 дополнительных ячеек в массив, чтобы учесть возможные новые записи. Кроме того, мы встраиваем функцию FILTER в UNIQUE, чтобы извлекать уникальные значения без пробелов.

Учитывая вышеизложенное, формула в G3 принимает следующий вид:

=УНИКАЛЬНЫЙ(ФИЛЬТР(A3:A20, A3:A20<>""))

Формула для зависимого раскрывающегося списка

Формула в G3 делает не нужно много настраивать — просто добавьте в массивы еще несколько ячеек:

=ФИЛЬТР(B3:B20, A3:A20=D3)

Результатом является полностью динамический расширяемый зависимый раскрывающийся список:

Как отсортировать раскрывающийся список по алфавиту

Хотите упорядочить раскрывающийся список по алфавиту без обращения к исходным данным? В новом динамическом Excel для этого тоже есть специальная функция! В таблице подготовки данных просто оберните функцию SORT вокруг существующих формул.

Правила проверки данных настроены точно так же, как описано в предыдущих примерах.

Для сортировки от А до Я

Поскольку сортировка по возрастанию является опцией по умолчанию, вы можете просто вложить существующие формулы в аргумент массива СОРТИРОВКИ, опуская все остальные аргументы, которые являются необязательными.

Для главного выпадающего списка (формула в G3):

=СОРТИРОВКА(УНИКАЛЬНЫЙ(ФИЛЬТР(A3:A20, A3:A20<>"")))

Для зависимого выпадающего списка (формула формула в h4):

=СОРТИРОВКА(ФИЛЬТР(B3:B20, A3:A20=D3))

Готово! Оба выпадающих списка сортируются в алфавитном порядке от А до Я.

Для сортировки от Я до А

Для сортировки в порядке убывания необходимо установить аргумент 3 ^rd ( sort_order ) функции SORT на -1 .

Для основного раскрывающегося списка (формула в G3):

=СОРТИРОВКА(УНИКАЛЬНЫЙ(ФИЛЬТР(A3:A20, A3:A20<>"")), 1, -1)

Для зависимый раскрывающийся список (формула в h4):

=СОРТИРОВАТЬ(ФИЛЬТР(B3:B20, A3:A20=D3), 1, -1)

Это позволит отсортировать как данные в таблице подготовки, так и элементы в раскрывающихся списках от Z до A:

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

Практическая рабочая тетрадь для скачивания

Раскрывающийся список, зависящий от Excel (файл .xlsx)

Вас также может заинтересовать

Война в Украине!

Чтобы поддержать Украину и спасти жизни , пожалуйста, посетите эту страницу.

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

Главная / Advanced Excel / Как создать зависимый раскрывающийся список в Excel

Действия по созданию зависимого раскрывающегося списка в Excel

Трехуровневый зависимый Выпадающий список

Дополнительные руководства

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

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

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

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

Действия по созданию зависимого раскрывающегося списка в Excel

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

• Прежде всего, вы должны создать именованные диапазоны для раскрывающихся списков. Для этого выберите список продуктов. Перейдите в -> Формулы -> Определенные имена -> Создать из выделения.

• Вы получите всплывающее окно. Отметьте «Верхний ряд» и нажмите «ОК».

• Используя те же шаги, создайте еще два именованных диапазона размеров. Один для белой бумаги, а второй для серой бумаги.

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

• Теперь выберите ячейку, в которую вы хотите добавить раскрывающийся список продуктов, и выберите «Данные» -> «Инструменты данных» -> «Проверка данных».

• В окне проверки данных выберите «Список» и в поле «Источник» введите приведенную ниже формулу, а затем нажмите «ОК».

= Косвенный («Продукт»)

• Выберите ячейку, в которую вы хотите добавить раскрывающийся список размеров. Перейдите в -> Данные -> Инструменты для работы с данными -> Проверка данных.
• В окне проверки данных выберите «Список» и в поле «Источник» введите приведенную ниже формулу и нажмите «ОК».

=Indirect(«A5»)

Наконец, ваш зависимый выпадающий список готов.

Как это работает

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

Если вы заметили, наши диапазоны с именами размеров имеют имя, равное значениям, которые мы имеем в раскрывающемся списке продуктов.

Когда мы выбираем «WhitePaperSheet» в ячейке продукта, тогда косвенная функция в ячейке размера ссылается на именованный диапазон «WhitePaperSheet», а когда вы выбираете «GreyPaperSheet», она будет ссылаться на именованный диапазон «GreyPaperSheet».

Трехуровневый зависимый раскрывающийся список

В приведенном выше примере вы создали двухуровневый зависимый раскрывающийся список.