Створення залежного випадаючого списку в Excel (Покроковий посібник)

Перегляд відео - Створення залежного випадаючого списку в Excel

Випадаючий список Excel - це корисна функція під час створення форм для введення даних або інформаційних панелей Excel.

Він показує список елементів у вигляді спадного меню в комірці, і користувач може зробити вибір зі спадного меню. Це може бути корисним, якщо у вас є список назв, продуктів або регіонів, які часто потрібно вводити до набору клітинок.

Нижче наведено приклад розкривного списку Excel:

У наведеному вище прикладі я використовував елементи в A2: A6 для створення розкривного списку в C3.

Прочитайте: Ось докладний посібник про те, як створити розкривний список Excel.

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

Вони називаються залежними випадаючими списками в Excel.

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

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

Це називається умовний або залежний випадаючий список у Excel.

Створення залежного випадаючого списку в Excel

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

• Виберіть клітинку, у якій потрібно розташувати перший (основний) випадаючий список.
• Перейдіть до Дані -> Перевірка даних. Відкриється діалогове вікно перевірки даних.
• У діалоговому вікні перевірки даних на вкладці налаштувань виберіть Список.
• У полі Джерело вкажіть діапазон, який містить елементи, які мають відображатися у першому розкривному списку.
• Натисніть OK. Це створить спадне меню 1.
• Виберіть весь набір даних (A1: B6 у цьому прикладі).
• Перейдіть до Формули -> Визначені імена -> Створити з виділення (або ви можете скористатися комбінацією клавіш Control + Shift + F3).
• У діалоговому вікні "Створити ім'я з виділення" встановіть прапорець "Верхній рядок" і зніміть прапорці з усіх інших. Це створює 2 діапазони назв ("Фрукти" та "Овочі"). Фрукти з назвою асортимент стосуються всіх фруктів у списку, а назва овочів - усіх овочів зі списку.
• Натисніть OK.
• Виберіть клітинку, у якій потрібно розташувати залежний/умовний випадаючий список (E3 у цьому прикладі).
• Перейдіть до Дані -> Перевірка даних.
• У діалоговому вікні Перевірка даних на вкладці налаштувань переконайтеся, що вибрано Список у.
• У полі Джерело введіть формулу = НЕПРЯМА (D3). Тут D3 - це клітинка, яка містить основне випадаюче меню.
• Натисніть OK.

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

Завантажте файл прикладу

Як це працює? - Умовний випадаючий список (у комірці E3) відноситься до = INDIRECT (D3). Це означає, що коли ви вибираєте «Фрукти» у клітинці D3, випадаючий список у E3 посилається на іменований діапазон «Фрукти» (за допомогою функції «ПРОМІЖНА») і, отже, перераховує всі елементи цієї категорії.

Важлива примітка: Якщо в головній категорії є більше одного слова (наприклад, "Сезонні фрукти" замість "Фрукти"), то вам потрібно використати формулу = НЕПРЯМОЮ (ЗАМІНА (D3, "", "_")), а не проста функція непряма, показана вище.

• Причиною цього є те, що Excel не дозволяє пробіли в іменованих діапазонах. Тож коли ви створюєте іменований діапазон із використанням кількох слів, Excel автоматично вставляє підкреслення між словами. Наприклад, коли ви створюєте іменований діапазон із "Сезонні фрукти", він буде називатися Season_Fruits у бекенді. Використання функції SUBSTITUTE у функції INDIRECT забезпечує пробіли є перетворюється на підкреслення.

Автоматично скинути/очистити вміст залежного випадаючого списку

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

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

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

Ось код VBA для очищення вмісту залежного випадаючого списку:

Приватний додатковий робочий аркуш_Зміна (ByVal Target As Range) On Error Поновлюється далі If Target.Column = 4 Тоді If Target.Validation.Type = 3 Тоді Application.EnableEvents = False Target.Offset (0, 1) .ClearContents End If End Якщо exitHandler: Application.EnableEvents = Допоміжний підрозділ справжнього виходу

Заслуга цього коду належить цьому підручнику Дебри щодо очищення залежних випадаючих списків у Excel при зміні виділення.

Ось як змусити цей код працювати:

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

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

Якщо ви не прихильник VBA, ви також можете скористатися простим трюком умовного форматування, який виділятиме клітинку, коли виникає невідповідність. Це може допомогти вам візуально побачити та виправити невідповідність (як показано нижче).

Ось кроки t0, щоб виділити невідповідність у залежних випадаючих списках:

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

Формула використовує функцію VLOOKUP для перевірки того, чи є елемент у залежному випадаючому списку з основної категорії чи ні. Якщо це не так, формула повертає помилку. Це використовується функцією ISERROR для повернення TRUE, яка повідомляє умовне форматування, щоб виділити клітинку.

Вам також можуть сподобатися наступні підручники Excel:

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