Креирање зависне падајуће листе у Екцелу (Корак по корак водич)

Гледајте видео - Креирање зависне падајуће листе у програму Екцел

Екцел падајућа листа је корисна функција при креирању образаца за унос података или Екцел контролних табли.

Приказује листу ставки као падајући мени у ћелији, а корисник може да изабере из падајућег менија. Ово би могло бити корисно када имате листу имена, производа или регија које често морате унијети у скуп ћелија.

Испод је пример падајуће листе програма Екцел:

У горњем примеру, користио сам ставке у А2: А6 за креирање падајућег менија у Ц3.

Читати: Ево детаљног водича о томе како да направите Екцел падајућу листу.

Понекад ћете, међутим, можда желети да користите више од једне падајуће листе у Екцелу, тако да ставке доступне на другој падајућој листи зависе од избора направљене на првој падајућој листи.

То се у Екцел-у назива зависним падајућим листама.

Испод је пример шта мислим под зависном падајућом листом у Екцелу:

Можете видети да опције у падајућем менију 2 зависе од избора направљеног у падајућем менију 1. Ако изаберем 'Воће' у падајућем менију 1, приказаће ми се називи воћа, али ако изаберем Поврће у падајућем менију 1, онда у падајућем менију 2 приказана су имена поврћа.

Ово се у Екцел -у назива условном или зависном падајућом листом.

Креирање зависне падајуће листе у програму Екцел

Ево корака за креирање зависне падајуће листе у Екцелу:

• Изаберите ћелију у којој желите прву (главну) падајућу листу.
• Идите на Подаци -> Валидација података. Ово ће отворити дијалошки оквир за потврду података.
• У дијалогу за проверу ваљаности података, на картици поставки, изаберите Листа.
• У пољу Извор наведите опсег који садржи ставке које треба приказати на првој падајућој листи.
• Притисните ОК. Ово ће створити падајући мени 1.
• Изаберите цео скуп података (А1: Б6 у овом примеру).
• Идите на Формуле -> Дефинисана имена -> Креирај из избора (или можете користити пречицу на тастатури Цонтрол + Схифт + Ф3).
• У дијалогу „Креирај име из избора“ означите опцију Горњи ред и поништите све остале. Тиме се стварају 2 назива („Воће“ и „Поврће“). Асортиман воћа се односи на сво воће са листе, а назив Поврће се односи на сво поврће са листе.
• Притисните ОК.
• Изаберите ћелију у којој желите зависну/условну падајућу листу (Е3 у овом примеру).
• Идите на Подаци -> Валидација података.
• У дијалогу Валидација података, на картици подешавања, уверите се да је Листа у изабраној.
• У поље Извор унесите формулу = ИНДИРЕКТНО (Д3). Овде је Д3 ћелија која садржи главни падајући мени.
• Притисните ОК.

Сада, када направите избор у падајућој листи 1, опције наведене у падајућој листи 2 би се аутоматски ажурирале.

Преузмите датотеку примера

Како ово ради? - Условна падајућа листа (у ћелији Е3) односи се на = ИНДИРЕКТНО (Д3). То значи да када одаберете „Воће“ у ћелији Д3, падајућа листа у Е3 односи се на именовани распон „Воће“ (путем функције ИНДИРЕКТНО) и стога наводи све ставке у тој категорији.

Важна напомена: Ако је главна категорија више од једне речи (на пример, „Сезонско воће“ уместо „Воће“), онда морате да користите формулу = ИНДИРЕКТНО (ЗАМЕНИЦА (Д3, ”„, ”_”)), уместо једноставна ИНДИРЕКТНА функција приказана горе.

• Разлог за то је што Екцел не дозвољава размаке у именованим опсезима. Дакле, када креирате именовани опсег користећи више речи, Екцел аутоматски убацује подвлаку између речи. На пример, када креирате именовани опсег са „Сезонско воће“, он ће се у позадини назвати Сезона_Фруитс. Коришћење функције СУБСТИТУТЕ у оквиру функције ИНДИРЕЦТ осигурава размаке су претворене у доње црте.

Аутоматски поништи/обриши садржај зависне падајуће листе

Када сте извршили избор, а затим променили падајући падајући изборник, зависна падајућа листа се не би променила и, према томе, био би погрешан унос.

На пример, ако изаберете „Воће“ као категорију, а затим изаберете Аппле као ставку, а затим се вратите назад и промените категорију у „Поврће“, зависни падајући мени ће наставити да приказује Аппле као ставку.

Помоћу ВБА можете да се уверите да се садржај зависне падајуће листе ресетује кад год се главна падајућа листа промени.

Ево ВБА кода за брисање садржаја зависне падајуће листе:

Привате Суб Ворксхеет_Цханге (БиВал Таргет Ас Ранге) Он Еррор Настави даље Иф Таргет.Цолумн = 4 Тхен Иф Таргет.Валидатион.Типе = 3 Тхен Апплицатион.ЕнаблеЕвентс = Фалсе Таргет.Оффсет (0, 1) .ЦлеарЦонтентс Енд Иф Енд Иф екитХандлер: Апплицатион.ЕнаблеЕвентс = Потпуни подзавршни излаз

Заслуга за овај код припада овом упутству компаније Дебра за брисање зависних падајућих листа у Екцелу када се промени избор.

Ево како да овај код функционише:

• Копирајте ВБА код.
• У Екцел радној свесци где имате зависну падајућу листу, идите на картицу Девелопер, а у групи „Цоде“ кликните на Висуал Басиц (можете користити и тастерску пречицу - АЛТ + Ф11).
• У прозору ВБ Едитор, са леве стране у истраживачу пројеката, видели бисте сва имена радних листова. Двапут кликните на ону са падајућом листом.
• Залепите код у прозор са кодом са десне стране.
• Затворите ВБ Едитор.

Сада, кад год промените главну падајућу листу, ВБА код би се покренуо и обрисао би садржај зависне падајуће листе (као што је приказано испод).

Ако нисте љубитељ ВБА -а, можете користити и једноставан трик условног обликовања који ће истакнути ћелију кад год постоји неслагање. Ово вам може помоћи да визуелно видите и исправите неусклађеност (као што је приказано испод).

Ево корака који т0 истичу неусклађености на зависним падајућим листама:

• Изаберите ћелију са зависним падајућим листама.
• Идите на Почетна -> Условно обликовање -> Ново правило.
• У дијалогу Ново правило обликовања изаберите „Користи формулу за одређивање ћелија које треба форматирати“.
• У поље формуле унесите следећу формулу: = ГРЕШКА (ВЛООКУП (Е3, ИНДЕКС ($ А $ 2: $ Б $ 6,, МАТЦХ (Д3, $ А $ 1: $ Б $ 1)), 1,0))
• Подесите формат.
• Притисните ОК.

Формула користи функцију ВЛООКУП да провери да ли је ставка на зависној падајућој листи она из главне категорије или не. Ако није, формула враћа грешку. Ово користи функција ИСЕРРОР да врати ТРУЕ која говори условном обликовању да означи ћелију.

Можда ће вам се допасти и следећи Екцел водичи:

• Издвајање података на основу одабира падајуће листе.
• Прављење падајуће листе са предлозима за претрагу.
• Изаберите више ставки са падајуће листе.
• Направите више падајућих листа без понављања.
• Уштедите време помоћу образаца за унос података у Екцелу.