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

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

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

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

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

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

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

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

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

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

=УНИКАЛЬНЫЙ(A3:A15)

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

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

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

  • Выберите ячейку, в которой вы хотите отобразить раскрывающийся список (в нашем случае D3).
  • На Данные вкладка, в Инструменты данных щелкните Проверка данных.
  • в Проверка данных диалоговом окне выполните следующие действия:
    • Под Разрешатьвыберите Список.
    • в Источник введите ссылку на вывод диапазона разлива по формуле UNIQUE. Для этого введите хештег сразу после ссылки на ячейку, например: =$G$3#

      Это называется ссылкой на диапазон разлива, и этот синтаксис относится ко всему диапазону, независимо от того, насколько он расширяется или сжимается.

    • Нажмите ХОРОШО чтобы закрыть диалог.

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

Ваш основной раскрывающийся список готов!
Первое выпадающее меню выполнено.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Если вы собираетесь добавить больше зависимых раскрывающихся списков (4-й, 5-й и т. д.), то, скорее всего, столбец C будет содержать несколько вхождений одного и того же элемента. Чтобы предотвратить попадание дубликатов в подготовительную таблицу и, следовательно, в 3-й выпадающий список, вложите формулу FILTER в функцию UNIQUE, как мы это делали на предыдущем шаге:

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

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

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

Кончик. Аналогичным образом вы можете получить элементы для последующих раскрывающихся списков. Предполагая, что столбец D содержит исходные данные для вашего 4-го раскрывающегося списка, вы можете ввести следующую формулу в 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

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

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

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

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

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

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

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

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

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

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

Сортировать от Я до А

Для сортировки по убыванию нужно задать 3-й аргумент (Порядок сортировки) функции SORT на -1.

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

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

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

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

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

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

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

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

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

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *