Создание зависимого раскрывающегося списка в Excel (пошаговое руководство)

Посмотреть видео - Создание зависимого раскрывающегося списка в Excel

Раскрывающийся список Excel - полезная функция при создании форм для ввода данных или панелей мониторинга Excel.

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

Ниже приведен пример раскрывающегося списка Excel:

В приведенном выше примере я использовал элементы в A2: A6 для создания раскрывающегося списка в C3.

Читать: Вот подробное руководство по созданию раскрывающегося списка Excel.

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

В Excel они называются зависимыми раскрывающимися списками.

Ниже приведен пример того, что я имею в виду под зависимым раскрывающимся списком в Excel:

Вы можете видеть, что параметры в раскрывающемся списке 2 зависят от выбора, сделанного в раскрывающемся списке 1. Если я выберу «Фрукты» в раскрывающемся списке 1, мне будут показаны названия фруктов, но если я выберу овощи в раскрывающемся списке 1, то я мне показаны названия овощей в раскрывающемся списке 2.

В Excel это называется условным или зависимым раскрывающимся списком.

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

Вот шаги для создания зависимого раскрывающегося списка в Excel:

• Выберите ячейку, в которой вы хотите отобразить первый (основной) раскрывающийся список.
• Перейдите в Data -> Data Validation. Откроется диалоговое окно проверки данных.
• В диалоговом окне проверки данных на вкладке настроек выберите Список.
• В поле «Источник» укажите диапазон, содержащий элементы, которые должны отображаться в первом раскрывающемся списке.
• Щелкните ОК. Это создаст выпадающий список 1.
• Выберите весь набор данных (A1: B6 в этом примере).
• Перейдите в Формулы -> Определенные имена -> Создать из выделенного (или вы можете использовать сочетание клавиш Control + Shift + F3).
• В диалоговом окне «Создать имя из выделенного» установите флажок «Верхняя строка» и снимите все остальные флажки. В результате будут созданы 2 диапазона имен («Фрукты» и «Овощи»). Именованный диапазон фруктов относится ко всем фруктам в списке, а именованный диапазон овощей относится ко всем овощам в списке.
• Щелкните ОК.
• Выберите ячейку, в которой вы хотите создать раскрывающийся список «Зависимые / условные» (в данном примере - E3).
• Перейдите в Data -> Data Validation.
• Убедитесь, что в диалоговом окне «Проверка данных» на вкладке настроек установлен флажок «Список».
• В поле Источник введите формулу = КОСВЕННО (D3). Здесь D3 - это ячейка, содержащая главное раскрывающееся меню.
• Щелкните ОК.

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

Загрузите файл примера

Как это работает? - Условный раскрывающийся список (в ячейке E3) относится к = КОСВЕННО (D3). Это означает, что когда вы выбираете «Фрукты» в ячейке D3, раскрывающийся список в E3 ссылается на именованный диапазон «Фрукты» (с помощью функции КОСВЕННО) и, следовательно, перечисляет все элементы в этой категории.

Важная заметка: Если основная категория состоит из более чем одного слова (например, «Сезонные фрукты» вместо «Фрукты»), тогда вам необходимо использовать формулу = КОСВЕННО (ПОДСТАВИТЬ (D3, ”“, ”_”)) вместо простая функция КОСВЕННО, показанная выше.

• Причина этого в том, что Excel не допускает пробелов в именованных диапазонах. Поэтому, когда вы создаете именованный диапазон, используя более одного слова, Excel автоматически вставляет подчеркивание между словами. Например, когда вы создаете именованный диапазон с «Сезонными фруктами», он будет называться Season_Fruits в серверной части. Использование функции ПОДСТАВИТЬ в функции ДВССЫЛ позволяет убедиться, что пробелы являются преобразованы в символы подчеркивания.

Сброс / очистка содержимого зависимого раскрывающегося списка автоматически

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

Например, если вы выберете «Фрукты» в качестве категории, а затем выберете Apple в качестве элемента, а затем вернетесь и измените категорию на «Овощи», в зависимом раскрывающемся списке по-прежнему будет отображаться Apple в качестве элемента.

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

Вот код VBA для очистки содержимого зависимого раскрывающегося списка:

Private Sub Worksheet_Change (ByVal Target As Range) При ошибке Возобновить Далее Если Target.Column = 4 Тогда Если Target.Validation.Type = 3 Тогда Application.EnableEvents = False Target.Offset (0, 1) .ClearContents End If End If exitHandler: Application.EnableEvents = True Выход Sub End Sub

Кредит за этот код принадлежит этому руководству Дебры по очистке зависимых раскрывающихся списков в Excel при изменении выбора.

Вот как заставить этот код работать:

• Скопируйте код VBA.
• В книге Excel, где у вас есть зависимый раскрывающийся список, перейдите на вкладку «Разработчик» и в группе «Код» нажмите Visual Basic (вы также можете использовать сочетание клавиш - ALT + F11).
• В окне редактора VB слева в проводнике проекта вы увидите все имена рабочих листов. Дважды щелкните тот, у которого есть раскрывающийся список.
• Вставьте код в окно кода справа.
• Закройте редактор VB.

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

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

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

• Выберите ячейку, в которой есть зависимые раскрывающиеся списки.
• Перейдите на главную -> Условное форматирование -> Новое правило.
• В диалоговом окне «Новое правило форматирования» выберите «Использовать формулу, чтобы определить, какие ячейки нужно форматировать».
• В поле формулы введите следующую формулу: = ISERROR (VLOOKUP (E3, INDEX ($ A $ 2: $ B $ 6,, MATCH (D3, $ A $ 1: $ B $ 1)), 1,0))
• Установите формат.
• Щелкните ОК.

В формуле используется функция ВПР, чтобы проверить, является ли элемент в зависимом раскрывающемся списке элементом из основной категории. Если это не так, формула возвращает ошибку. Это используется функцией ISERROR для возврата TRUE, что указывает условному форматированию выделить ячейку.

Вам также могут понравиться следующие руководства по Excel:

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