Създаване на зависим падащ списък в Excel (Урок стъпка по стъпка)

Гледайте видео - Създаване на зависим падащ списък в Excel

Падащият списък на Excel е полезна функция, когато създавате формуляри за въвеждане на данни или табла за управление на Excel.

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

По -долу е даден пример за падащ списък на Excel:

В горния пример използвах елементите в A2: A6, за да създам падащо меню в C3.

Прочети: Ето подробно ръководство за това как да създадете падащ списък в Excel.

Понякога обаче може да искате да използвате повече от един падащ списък в Excel, така че наличните елементи във втори падащ списък да зависят от избора, направен в първия падащ списък.

Те се наричат ​​зависими падащи списъци в Excel.

По-долу е даден пример за това какво имам предвид под зависим падащ списък в Excel:

Можете да видите, че опциите в падащото меню 2 зависят от избора, направен в падащото меню 1. Ако избера „Плодове“ в падащото меню 1, ще ми се покажат имената на плодовете, но ако избера зеленчуци в падащото меню 1, тогава I Показват се имената на зеленчуците в падащото меню 2.

Това се нарича условен или зависим падащ списък в Excel.

Създаване на зависим падащ списък в Excel

Ето стъпките за създаване на зависим падащ списък в Excel:

• Изберете клетката, където искате първия (основния) падащ списък.
• Отидете на Данни -> Проверка на данни. Това ще отвори диалоговия прозорец за потвърждаване на данните.
• В диалоговия прозорец за валидиране на данни в раздела с настройки изберете Списък.
• В поле Източник посочете диапазона, който съдържа елементите, които трябва да бъдат показани в първия падащ списък.
• Щракнете върху OK. Това ще създаде падащото меню 1.
• Изберете целия набор от данни (A1: B6 в този пример).
• Отидете на Формули -> Определени имена -> Създаване от селекция (или можете да използвате клавишната комбинация Control + Shift + F3).
• В диалоговия прозорец „Създаване на име от избор“ поставете отметка в горния ред и премахнете отметката от всички останали. По този начин се създават 2 диапазона с имена („Плодове“ и „Зеленчуци“). Плодовете с имена се отнасят до всички плодове в списъка, а имената от зеленчуци се отнасят до всички зеленчуци в списъка.
• Щракнете върху OK.
• Изберете клетката, в която искате зависим/условен падащ списък (E3 в този пример).
• Отидете на Данни -> Проверка на данни.
• В диалоговия прозорец Проверка на данни, в раздела за настройки, уверете се, че е избрано Списък в.
• В полето Източник въведете формулата = ИНДИРЕКТНО (D3). Тук D3 е клетката, която съдържа основното падащо меню.
• Щракнете върху OK.

Сега, когато направите избора в падащото меню 1, опциите, изброени в падащия списък 2, ще се актуализират автоматично.

Изтеглете примерния файл

Как работи това? - Условният падащ списък (в клетка E3) се отнася до = INDIRECT (D3). Това означава, че когато изберете „Плодове“ в клетка D3, падащият списък в E3 се отнася до назования диапазон „Плодове“ (чрез функцията INDIRECT) и следователно изброява всички елементи в тази категория.

Важна забележка: Ако основната категория е повече от една дума (например „Сезонни плодове“ вместо „Плодове“), тогава трябва да използвате формулата = INDIRECT (SUBSTITUTE (D3, ”“, ”_“)), вместо проста функция INDIRECT, показана по -горе.

• Причината за това е, че Excel не позволява интервали в именувани диапазони. Така че, когато създавате именен диапазон, използвайки повече от една дума, Excel автоматично вмъква подчертаване между думите. Например, когато създадете именен диапазон със „Сезонни плодове“, той ще бъде наречен Season_Fruits в задната част. Използването на функцията SUBSTITUTE в рамките на функцията INDIRECT гарантира, че пространствата са превърнати в подчертаване.

Автоматично нулиране/изчистване на съдържанието на зависим падащ списък

Когато сте направили подбора и след това промените падащото меню на родителя, зависимият падащ списък няма да се промени и следователно би бил грешен запис.

Например, ако изберете „Плодове“ като категория и след това изберете Apple като елемент, а след това се върнете и промените категорията на „Зеленчуци“, зависимото падащо меню ще продължи да показва Apple като елемент.

Можете да използвате VBA, за да се уверите, че съдържанието на зависимия падащ списък се нулира, когато основният падащ списък се промени.

Ето кода на VBA за изчистване на съдържанието на зависим падащ списък:

Private Sub Worksheet_Change (ByVal Target As Range) On Error Resume Next If Target.Column = 4 then If Target.Validation.Type = 3 then Application.EnableEvents = False Target.Offset (0, 1) .ClearContents End If End If exitHandler: Application.EnableEvents = Sub Ex Sub Sub Sub

Заслугата за този код принадлежи на този урок от Debra за изчистване на зависими падащи списъци в Excel, когато селекцията се промени.

Ето как да накарате този код да работи:

• Копирайте VBA кода.
• В работната книга на Excel, където имате зависимия падащ списък, отидете в раздела Разработчик и в групата „Код“ щракнете върху Visual Basic (можете да използвате и клавишната комбинация - ALT + F11).
• В прозореца на VB Editor, вляво в Explorer, ще видите всички имена на работни листове. Щракнете двукратно върху този, който има падащия списък.
• Поставете кода в кодовия прозорец вдясно.
• Затворете редактора на VB.

Сега, когато променяте основния падащ списък, кодът VBA ще се задейства и ще изчисти съдържанието на зависимия падащ списък (както е показано по -долу).

Ако не сте фен на VBA, можете също да използвате прост трик за условно форматиране, който ще подчертае клетката, когато има несъответствие. Това може да ви помогне визуално да видите и коригирате несъответствието (както е показано по -долу).

Ето стъпките t0 подчертават несъответствията в зависимите падащи списъци:

• Изберете клетката, която има зависим падащ списък (и).
• Отидете на Начало -> Условно форматиране -> Ново правило.
• В диалоговия прозорец Ново правило за форматиране изберете „Използвайте формула, за да определите кои клетки да форматирате“.
• В полето за формула въведете следната формула: = ISERROR (VLOOKUP (E3, INDEX ($ A $ 2: $ B $ 6,, MATCH (D3, $ A $ 1: $ B $ 1)), 1,0))
• Задайте формата.
• Щракнете върху OK.

Формулата използва функцията VLOOKUP, за да провери дали елементът в зависимия падащ списък е този от основната категория или не. Ако не е, формулата връща грешка. Това се използва от функцията ISERROR за връщане на TRUE, което казва на условното форматиране, за да маркира клетката.

Може да харесате и следните уроци по Excel:

• Извличане на данни въз основа на избор на падащ списък.
• Създаване на падащ списък с предложения за търсене.
• Изберете няколко елемента от падащ списък.
• Създайте множество падащи списъци без повторение.
• Спестете време с формуляри за въвеждане на данни в Excel.