Създайте свързани падащи списъци в Excel – най-лесният начин!

ПРЕДСТАВЕТЕ СИТУАЦИЯТА:Искаме да създадем малка таблица в Excel, където можете да изберете държава и съответния град. В същото време, използвайки падащи списъци, е необходимо да се ограничат опциите, достъпни за потребителите на държави и градове, от които те могат да избират. В първата клетка ще изберем държава, а във втората клетка ще бъдат налични само градове, принадлежащи към избраната държава. Мисля, че е разбираемо?

И така, нека започнем нашия прост пример с това как можете да създадете свързан (или зависим) падащ списък в Excel? В клетка B1ще изберем държава, а в клетката B2- градът, който й принадлежи, както в примера:

Първо трябва да създадете база данни. На втория лист въведох списък с държави, които искам да дам на потребителите да избират в първия падащ списък, а в съседната колона посочих цифров индекс, който съответства на един от списъците с градове. Списъците с градове са разположени вдясно в колони д, ЕИ з. Така например до Францияиндекс на разходите 2 , което съответства на списъка с градове 2 . По-късно ще видите как ще се използва този индекс.

Ако работите в Excel 2010, можете да създадете изходен лист в отделна работна книга. Ако имате версия на Excel 2003 и планирате да използвате именуван диапазон, тогава стойностите трябва да са в същата работна книга или на различен лист.

Ще използваме наименувани диапазони и ще накараме тези свързани падащи менюта да работят във всички версии на Excel. Следващата стъпка е да създадем именувани диапазони за нашите списъци. В раздела Формули(Формули) имат команда мениджър на имена(мениджър на имена). Щракването върху него ще отвори диалогов прозорец. мениджър на имена(мениджър на имена).

Щракнете върху бутона Нов(Нов), за да добавите нов именуван диапазон. Ще се отвори диалогов прозорец ново име(Създаване на име).

В полето Име(Име) въведете име Държаваза първия ни наименуван диапазон и в полето Отнася се до(Диапазон) изберете този, който съхранява списъка с държави:

Лист3!$A$3:$A$5

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

Сега можем да създадем падащи списъци в тези клетки, където сме планирали да изберем данни. Маркирайте клетка B1(в него ще изберем държавата), отворете раздела Данни(Данни), щракнете Потвърждаване на данни(Проверка на данни), след което от падащото меню изберете Потвърждаване на данни(Проверка на данни).

Ще се отвори диалогов прозорец Потвърждаване на данни(Проверка на входните стойности).

Искаме да дадем на потребителя списък с опции за избор, така че в полето позволява(тип данни) изберете списък(Списък). Това ще активира полето Източник(Източник), където трябва да посочите името на диапазона с държави. Въведете „=Държава“ в това поле и щракнете Добре. Сега трябва да направим второ падащо меню, така че потребителите да могат да избират град. Ще поставим това падащо меню в клетка B2. А сега внимание - фокус! Трябва да проверим съдържанието на клетката с името на държавата (клетка B1), за да получим индекса, съответстващ на базата данни на града. Ако потребителят избере Португалия, тогава трябва да се обърнем към основата с индекса 3 , който съхранява имената на градовете в Португалия. Ще използваме функцията VLOOKUP(VLOOKUP), за да търсите стойност от клетка B1в таблица с имена на държави. След като индексът стане известен, ще изберем списък, който ще бъде източник на данни за нашия втори падащ списък. За целта пишем следната формула:

ИЗБЕРИ(VLOOKUP(B1,Sheet3!$A$3:$B$5,2,FALSE),Англия,Франция,Португалия)
=SELECT(VLOOKUP(B1;Sheet3!$A$3:$B$5;2;FALSE);Англия;Франция;Португалия)

Какво прави тази формула? Тя търси стойност от клетка B1в списъка с държави и връща съответния индекс, който след това се използва от функцията ИЗБИРАМ(ИЗБОР), за да изберете 1-ви, 2-ри или 3-ти наименуван диапазон.

Ето как ще изглежда нашето второ падащо меню:

В резултат на това ще получим два свързани (или зависими) падащи списъка. Ако изберем държава Франция, в свързания списък ще имаме само градове от Франция.

В тази статия научихте как да правите прости свързани падащи списъци в Microsoft Excel. Можете да вземете този прост пример и да го използвате за решаване на реални проблеми.