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

Хотели бы вы узнать, как связать раскрывающийся список в Excel? Раскрывающийся список Excel — полезная функция при создании форм ввода данных или информационных панелей Excel.

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

Пример раскрывающегося списка в Excel

Вот пример раскрывающегося списка Excel:

Здесь вы можете прочитать о: Как скопировать лист Excel в другую книгу — Учебное пособие

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

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

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

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

Если вы выберете 'Фрукты' в раскрывающемся меню 1 отображаются названия фруктов, но если вы выберете Овощи в раскрывающемся меню 1, то названия овощей отображаются в раскрывающемся меню 2. В Excel это называется условным или зависимым раскрывающимся списком.

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

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

• Шаг 1: выберите ячейку, в которой вы хотите разместить первый (основной) раскрывающийся список.
• Шаг 2: перейти к Данные -> Проверка данных. Откроется диалоговое окно проверки данных.

• Шаг 3: в диалоговом окне проверки данных на вкладке конфигурации выберите параметр список.

• Шаг 4: В деревне Источник, указывает диапазон, содержащий элементы, отображаемые в первом раскрывающемся списке.

• Шаг 5: нажмите Принять. Это создаст раскрывающееся меню 1.

• Шаг 6: выберите весь набор данных (в этом примере A1:B6).

• Шаг 7: Идти к Формулы -> Определенные имена -> Создать из выделения. (или вы можете использовать сочетание клавиш Контроль + Шифт + F3).

• Шаг 8: В диалоговом окне 'Создать имя из выделенного', проверьте опцию Верхний ряд и снимите галочки со всех остальных. При этом создаются 2 диапазона имен («Фрукты» и «Овощи»). Диапазон «Именованные фрукты» относится ко всем фруктам в списке, а диапазон «Именованные овощи» — ко всем овощам в списке.

• Шаг 9: нажмите Принять.
• Шаг 10: выберите ячейку, в которой вы хотите разместить раскрывающийся список «Зависимые/условные» (в данном примере E3).
• Шаг 11: Идти к Данные -> Проверка данных.

• Шаг 12: В диалоговом окне проверка достоверности данных, внутри вкладки Конфигурации, Удостоверься что список выбрано.

• Шаг 13: В Исходное поле, введите формулу = НЕПРЯМОЙ (D3). Здесь D3 — это ячейка, содержащая главное раскрывающееся меню.

• Шаг 14: нажмите Принять.

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

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

Как это работает? – Раскрывающийся список в условном формате Excel (в ячейке E3) относится к =ДВССЫЛ(D3). Это означает, что при выборе 'Фрукты' в ячейке D3 раскрывающийся список в E3 относится к диапазону с именем 'Фрукты' (с помощью КОСВНАЯ функция) и, следовательно, перечисляет все элементы этой категории.

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

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

Например: когда вы создаете именованный диапазон с помощью «Сезонные фрукты», Будет называться Время года_Фрукты в бэкэнда. Использование ЗАМЕНИТЬ функцию в КОСВНАЯ функция гарантирует, что пробелы станут подчеркиваниями.

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

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

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

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

Private Sub Worksheet_Change (Цель ByVal как диапазон)

В случае ошибки продолжить дальше

Если Target.Column = 4 Тогда

Если Target.Validation.Type = 3 Тогда

Application.EnableEvents = False

Target.Offset(0, 1).ClearContents

Это закончится, если

Это закончится, если

выходОбработчик:

Application.EnableEvents = True

Выход из подзаголовка

End Sub

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

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

• Шаг 1: скопируйте код VBA.
• Шаг 2: В книге Excel, где есть зависимый раскрывающийся список, перейдите к Вкладка «Разработчик»и внутри группы 'Код', Нажмите на Визуальный Бейсик (вы также можете использовать сочетание клавиш — АЛТ + Ф11).

• Шаг 3: В окне редактора VB, слева в проводнике проекта, вы увидите все имена рабочих листов. Дважды щелкните тот, у которого есть раскрывающийся список.

• Шаг 4: Вставьте код в окно кода справа.

• Шаг 5: Закройте редактор VB.

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

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

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

• Шаг 1: выберите ячейку, содержащую зависимые раскрывающиеся списки.
• Шаг 2: Идти к Главная -> Условное форматирование -> Новое правило.

• Шаг 3: В диалоговом окне Новое правило формат, Выбирать 'Используйте формулу, чтобы определить, какие ячейки формат».

• Шаг 4: В поле формулы введите следующую формулу:=ESERROR(VLOOKUP(E3,INDEX($A$2:$B$6,,MATCH(D3,$A$1:$B$1)), 1,0))

• Шаг 5: Установите формат.
• Шаг 6: нажмите «ОК».

Вам также может быть интересно узнать о: Как сгруппировать сводную таблицу по месяцам в Excel

В формуле используется Функция ВПР чтобы проверить, является ли зависимый элемент раскрывающегося списка элементом родительской категории или нет. В противном случае формула возвращает ошибку. Это используется функция ЕСОШИБКА вернуться истинный который сообщает условному форматированию, чтобы выделить ячейку.

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