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

До сада смо у овој серији Валидација података научили да правимо нормалну падајућу листу и динамичку падајућу листу користећи различите технике са провером података у Екцелу.

И данас, у овом поглављу, показаћемо вам како да креирате зависну падајућу листу у Мицрософт Екцелу, користећи различите методе.

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

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

Као што знамо да у Екцелу постоји много начина за обављање одређеног задатка, а слично томе, постоји много начина за стварање зависне провере података у Екцелу. Данас ћемо показати 5 различитих техника за креирање зависне листе за проверу ваљаности података.

Сирови подаци могу бити било којим редоследом или форматом и сваки пут не можете да промените податке или формат да бисте добили оно што тражите.

Дакле, узели смо један скуп података, али у 3 различита формата да бисмо добили зависну падајућу листу. И, као што видите, наши подаци су са леве стране, од колоне А до колоне Е, а ми ћемо имати очекивани излаз са десне стране, која је у колони Ј & К. Колона Ј ће имати примарну проверу листу, док ће колона К бити зависна и приказаће вредности у зависности од вредности изабране у колони Ј.

1^ст Пример:-

2^нд Пример:-

3^рд Пример:-

1^ст Пример:-

За сваки код производа имамо листу производа од колоне А8 до Е13. И, желимо да изаберемо шифру производа у Ј10, па у зависности од изабране шифре производа, назив производа у ћелији К10.

Први метод:-

Прва метода је врло једноставна и кратка и захтева само 3 корака да бисте добили зависну падајућу листу. Међутим, успешно функционише само док не промените опсег. Једном када измените податке, мораћете прво да промените именовани опсег да бисте добили ажурирану каскадну проверу података.

Пратите доле наведене кораке:-

• Изаберите целу табелу од А8 до Е13

• Затим идите на картицу „Формуле“, а затим у категорији „Дефинисана имена“ кликните на „Креирај из одабира“
• Такође можете да користите тастерску пречицу ЦТРЛ + СХИФТ + Ф3
• Појавиће се дијалог Цреате Намес фром Селецтионс

• Тражи потврду који редови и колоне ће се користити за креирање назива за друге редове и колоне. Потврђујемо да користимо „горњи ред“ за креирање имена и поништимо ознаку 2^нд опцију, а затим кликнемо на У реду

Белешка: - Размаци и други посебни знакови осим подвлаке и тачке нису дозвољени као имена. Подразумевано ће се претворити у доњу црту. Дакле, користите знак подцртавања и тачку за раздвајање речи. Такође, прво слово не може бити број; то мора бити слово, доња црта или обрнута коса црта.

• Сада, да бисмо потврдили да сваки опсег има име, идемо у „Управитељ имена“ (притисните ЦТРЛ + Ф3)
• Тамо можемо видети свих 5 именованих распона
• Такође можемо видети да сваки назив опсега има доњу црту уместо празног места у средини низа

Сада ћемо направити падајућу листу:-

• Одаберите ћелију Ј10 и притисните АЛТ ++ Д+Л да бисте отворили дијалошки оквир Валидација података
• Одаберите Листа> затим унесите опсег А8: Е8 на картици Извор

• Притисните ОК
• Сада ћемо креирати зависну листу у ћелији К10
• Отворите дијалошки оквир Валидација података притиском на тастере АЛТ+Д+Л
• Изаберите Листа, у извору унесите ову функцију:- = ИНДИРЕКТНА (ЗАМЕНА ($ Ј $ 10, "", "_"))

У валидацији података, за креирање зависне листе, користили смо функцију ИНДИРЕЦТ за враћање вредности на основу листе за проверу примарних података. А, да бисмо доњу црту заменили размаком, користићемо функцију СУБСТИТУТЕ унутар функције ИНДИРЕЦТ.

• Притисните ОК

Када изаберемо било који код производа у ћелији Ј10, тада ће се листа производа изабраног кода производа појавити у ћелији К10. На пример: - Изабрали смо ЕТВ 501, сада можете видети да се листа зависних производа појављује у ћелији К10

Белешка: - Кад год додате назив производа и код производа који се неће појавити на листи.

На пример: - Додали смо Производ 26 под шифром производа ЕТВ 505, али када одаберемо производ ЕТВ 505 тада се додани производ не појављује на падајућој листи.

Дакле, овако можете направити зависну падајућу листу користећи једноставну технику у само 3 једноставна корака.

2^нд Пример:-

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

Користићемо две различите методе за креирање зависне падајуће листе. Обје су готово сличне технике. Међутим, један је без именованог опсега, а други ће имати именовани опсег.

1^ст Метода:-

Да бисмо урадили исто, користићемо функције ОФФСЕТ, МАТЦХ & ЦОУНТИФ заједно.

Пошто знамо да се функција ОФФСЕТ користи за креирање динамичког опсега, стога за креирање листе „Валидација динамичких података“ користимо функцију ОФФСЕТ за враћање динамичког опсега.

МАТЦХ се користи за враћање релативног положаја ставке на листи у Екцелу. И овде ће нам помоћи да упоредимо категорију изабрану на примарној падајућој листи у нашем опсегу на листу, и вратиће број.

ЦОУНТИФ се користи за добијање броја ћелија које одговарају критеријумима. Овде ћемо ово користити за бројање редова који ће се приказати помоћу функције ЦОУНТИФ.

Пратите доле наведене кораке:-

• Одаберите ћелију Ј21, у којој ћемо креирати нашу листу валидације примарних података
• Притисните тастер АЛТ+Д+Л да бисте отворили дијалошки оквир Валидација података
• Изаберите листу из дозвољене категорије
• Кликните на картицу Извор и одаберите распон од Б20: Б24

• И кликните на ОК

• Идите у ћелију К21 и још једном отворите дијалошки оквир за потврду података
• Затим бирамо Листа и у извору уносимо доњу функцију:
• = ОФФСЕТ ($ Е $ 19, МАТЦХ ($ Ј $ 21, $ Д $ 20: $ Д $ 32,0), 0, ЦОУНТИФ ($ Д $ 20: $ Д $ 32, $ Ј $ 21))

• Притисните ОК
• У ћелији К21 можемо видети све одговарајуће вредности изабраног кода производа:-

Дакле, овако можете добити зависну листу тако што ћете узети референце ћелија у функцији.

2^нд Метод:-

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

Да бисте урадили исто, следите доле наведене кораке:-

• Одаберите Б19, затим притисните ЦТРЛ + Ф3 да бисте отворили прозор „Управитељ имена“
• Сада кликнемо на „Ново“ и појављује се дијалошки оквир „Дефинирај име“
• Видимо да се име већ појављује у оквиру за име -то је зато што смо изабрали Б9 пре отварања прозора „Управитељ имена“. А како Б19 има текст у себи, ако желимо, можемо га променити у неки други назив.

• Унесите формулу за помињање испод:-

= ОФФСЕТ ('ДепендентДропДовнЛист'! $ Б $ 20,0,0, ЦОУНТА ('ДепендентДропДовнЛист'! $ Б $ 20: $ Б $ 32))

• Притисните ОК

Пошто смо креирали динамичку листу јединствених производа, сада ћемо креирати динамички опсег за шифру производа која се налази у колони Д.

Слиједите исте кораке које смо слиједили за јединствени производ:-

• Одаберите ћелију Д19, отворите дијалошки оквир Дефинирај име
• Видећете да је име већ тамо
• У референцама унесите доњу формулу:-

= ОФФСЕТ ('Зависна падајућа листа'! $ Д $ 20,0,0, ЦОУНТА ('Зависна падајућа листа'! $ Д $ 20: $ Д $ 35))

• Притисните ОК
• Сада су оба динамичка опсега спремна. Дакле, идемо на Ј22 и притиснемо „АЛТ + Д + Л“ и изаберемо „Листа“
• У извору ћемо имати именовани опсег који смо га дефинисали за „Јединствени код производа“, па притиснемо Ф3 да бисмо видели све доступне именоване опсеге
• Можемо видети именовани опсег „Јединствени код производа“, па кликнемо на њега, а затим на У реду и притиснемо ентер

• У тренутку када притиснемо ентер, добијамо падајућу стрелицу у ћелији Ј22, која садржи листу јединствених кодова производа

• Одаберите ћелију К22 и отворите дијалошки оквир „Валидација података“
• Користићемо исту функцију коју смо користили у последњој методи, али са именованим опсегом
• Изаберите листу, а затим у извору унесите формулу испод:-

= ОФФСЕТ ($ Е $ 19, МАТЦХ ($ Ј $ 22, Продуцт_Цоде, 0), 0, ЦОУНТИФ (Продуцт_Цоде, Ј22))

• Притисните ОК
• Сада имамо примарни падајући мени и подређену листу производа
• Одаберите производ „ЕТВ-101“ из Ј22, а у К22 можемо видети само називе који потпадају под овај производ „ЕТВ-101“. А када променимо било који производ („ЕТВ-103) у Ј22, К22 приказује одговарајуће вредности за тај код

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

Додајмо нови производ на листу; Следите доле наведене кораке:-

• Додајте код производа на листу Уникуе_Прод_Цоде

• Такође додајте Продуцт_Цоде и Продуцт_Наме у податке:-

• Сада проверите да ли се на падајућој листи додаје шифра производа и назив

3^рд Пример:-

Имамо динамичке наслове директно из табеле и додаћемо нове производе у асортиман. Табела је у истом формату који смо користили за 1^ст метода.

4^тх Метод:-

Пратите доле наведене кораке:-

• Изаберите наслов А40: Е40
• Прво креирајте динамички опсег за наслове, отворите дијалошки оквир „Дефинирај име“
• Напишите „Наслов“ на мјесту имена, а затим у „односи се на“ унесите доље наведену формулу:-
• Унесите доњу функцију:-
• = ОФФСЕТ ('Зависна падајућа листа'! $ А $ 40 ,,,, ЦОУНТА ('Зависна падајућа листа'! $ 40: $ 40))
• Притисните ОК

• Динамички опсег „Наслов“ је сада спреман

Сада ћемо креирати именовани опсег за сваки наслов, следите доле наведене кораке:-

• Изаберите табелу од А40 до Е50
• ЦТРЛ + СХИФТ + Ф3 тастерска пречица
• Поништимо избор 2^нд опција
• И пре него што кликнемо на У реду, уверите се да је 1^ст изабрана је опција „Горњи ред“

• Сада смо спремни за оба опсега

Сада ћемо припремити падајућу листу родитеља

• Изаберите ћелију Ј42
• Отворите дијалошки оквир Валидација података

• Затим након избора „Листа“, притиснемо Ф3 у извору да бисмо добили именовани опсег за наслове. Кликнемо на „Наслов“, а затим притиснемо ОК и притиснемо ентер. Сада имамо списак родитеља у Ј42

• Да бисте креирали листу детаља ставке, изаберите ћелијуК42
• Отворите дијалошки оквир Валидација података притиском на тастере АЛТ+Д+Л
• Одаберите Листа, а затим унесите доњу функцију на картици Извор:-
• = ОФФСЕТ (ИНДИРЕКТНО (СУБСТИТУТЕ ($ Ј $ 42, "", "_")) ,,, ЦОУНТА (ИНДИРЕКТНО (СУБСТИТУТЕ ($ Ј $ 42, "", "_"))))

• Притисните ОК

Сада одаберите неку ставку у Ј42, рецимо да одаберемо „Ставка 01“ и погледајте падајућу листу К42. И, као и претходне 3 методе, и овде имамо зависну листу.

Шта има ново? У првом примеру нисте могли додати ниједан производ на листу, али овде можете додати било који нови производ. Дакле, рецимо да овој ставци додамо нови производ. Идемо на А45 и откуцамо „ЕТВ-501 Прод 05“, па се вратимо на К42 и ево вас. Видите, нови производ је додат.

• Сада додајте неколико производа под нову ставку

Када изаберемо „Ставка 06“, идемо на К42 и кликнемо на падајућу листу. Изненађујуће, ништа се не дешава када кликнемо на падајућу стрелицу. То је зато што смо створили све динамично и заборавили да креирамо динамички опсег за сто, због чега се производи не приказују на подређеној листи.

Да бисмо то урадили, морамо користити различите технике. Постоје два метода за то. Можете креирати табелу или једноставно користити само функцију ОФФСЕТ. У следећој методи користићемо функцију ОФФСЕТ и видећемо трик за проширење опсега табеле.

• Дакле, прво идемо на Ј43 и притиснемо „АЛТ + Д + Л“
• Одаберемо „Листа“, а затим у извору притиснемо Ф3 и изаберемо „Заглавље“, притиснемо У реду, а затим притиснемо ентер

• Сада идемо на К43, а након одабира „Листа“, идемо на „Извор“ и улазимо у доље наведену функцију

= ОФФСЕТ ($ А $ 40,1, МАТЦХ ($ Ј $ 43, $ 40: $ 40,0) -1, ЦОУНТА (ОФФСЕТ ($ А $ 40,1, МАТЦХ ($ Ј $ 43, $ 40: $ 40,0) -1,1000 , 1)))

• Притисните ОК

Сада се враћамо и одабиремо „Ставка 06 у ћелији Ј43 и враћамо се на К43 и кликнемо на стрелицу падајућег менија. Али, овај временски списак приказује производе које смо додали за нову ставку. Одабрали смо први производ „ЕТВ-506 Прод 01“.

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

Видео: Како створити зависну (каскадно) падајућу листу у Екцелу помоћу 5 различитих техника у Мицрософт Екцелу

Кликните на видео везу за брзу референцу о употреби. Претплатите се на наш нови канал и наставите да учите са нама!

Ако вам се допадају наши блогови, поделите их са пријатељима на Фацебооку. Такође нас можете пратити на Твиттер -у и Фацебоок -у.
Волели бисмо да чујемо од вас, реците нам како можемо побољшати, допунити или иновирати наш рад и учинити га бољим за вас. Пишите нам на веб локацији е -поште