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

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

Може се користити приликом придобијања корисника да попуни образац или приликом креирања интерактивних Екцел контролних табли.

Падајуће листе су прилично честе на веб локацијама/у апликацијама и врло су интуитивне за корисника.

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

У овом водичу ћете научити како да направите падајућу листу у Екцелу (потребно је само неколико секунди да то урадите) заједно са свим сјајним стварима које можете да урадите са њом.

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

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

1. Коришћење података из ћелија.
2. Ручни унос података.
3. Користећи формулу ОФФСЕТ.

#1 Коришћење података из ћелија

Рецимо да имате списак ставки као што је приказано испод:

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

1. Изаберите ћелију у којој желите да креирате падајућу листу.
2. Идите на Подаци -> Алати за податке -> Валидација података.
3. У дијалогу Валидација података, на картици Поставке, изаберите Листа као критеријум провере ваљаности.
   • Чим изаберете Лист, појавиће се изворно поље.
4. У изворно поље унесите = $ А $ 2: $ А $ 6 или једноставно кликните у поље Извор и мишем изаберите ћелије и кликните на ОК. Ово ће уметнути падајућу листу у ћелију Ц2.
   • Уверите се да је означена падајућа опција У ћелији (која је подразумевано проверена). Ако ова опција није означена, ћелија не приказује падајући изборник, међутим, можете ручно унијети вриједности на листу.

Белешка: Ако желите да креирате падајуће листе у више ћелија одједном, изаберите све ћелије у којима желите да их креирате, а затим следите горе наведене кораке. Уверите се да су референце ћелија апсолутне (као што је $ А $ 2) и да нису релативне (попут А2, или А $ 2, или $ А2).

#2 Ручним уносом података

У горњем примеру, референце ћелија се користе у пољу Извор. Такође можете додати ставке директно ручним уносом у изворно поље.

На пример, рецимо да желите да прикажете две опције, Да и Не, у падајућем менију у ћелији. Ево како га можете директно унети у поље извора података за проверу ваљаности података:

• Изаберите ћелију у којој желите да креирате падајућу листу (ћелија Ц2 у овом примеру).
• Идите на Подаци -> Алати за податке -> Валидација података.
• У дијалогу Валидација података, на картици Поставке, изаберите Листа као критеријум провере ваљаности.
  • Чим изаберете Лист, појавиће се изворно поље.
• У поље извора унесите Да, Не
  • Уверите се да је означена падајућа опција У ћелији.
• Притисните ОК.

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

Све ставке унете у изворно поље, одвојене зарезом, приказане су у различитим редовима на падајућој листи.

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

#3 Коришћење Екцел формула

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

Било која формула која враћа листу вредности може се користити за креирање падајуће листе у Екцелу.

На пример, претпоставимо да имате скуп података као што је приказано испод:

Ево корака за креирање Екцел падајуће листе помоћу функције ОФФСЕТ:

• Изаберите ћелију у којој желите да креирате падајућу листу (ћелија Ц2 у овом примеру).
• Идите на Подаци -> Алати за податке -> Валидација података.
• У дијалогу Валидација података, на картици Поставке, изаберите Листа као критеријум провере ваљаности.
  • Чим изаберете Лист, појавиће се изворно поље.
• У поље Извор унесите следећу формулу: = ОФФСЕТ ($ А $ 2,0,0,5)
  • Уверите се да је означена падајућа опција У ћелији.
• Притисните ОК.

Ово ће створити падајућу листу која садржи све називе воћа (као што је приказано испод).

Белешка: Ако желите да направите падајућу листу у више ћелија одједном, изаберите све ћелије у којима желите да је креирате, а затим следите горенаведене кораке. Уверите се да су референце ћелија апсолутне (као што је $ А $ 2) и да нису релативне (као што су А2, или А $ 2, или $ А2).

Како функционише ова формула ??

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

Враћа листу ставки из опсега А2: А6.

Ево синтаксе функције ОФФСЕТ: = ОФФСЕТ (референца, редови, колоне, [висина], [ширина])

Потребно је пет аргумената, где смо референцу навели као А2 (почетна тачка листе). Редови/поља су наведени као 0 јер не желимо да померимо референтну ћелију. Висина је наведена као 5 јер на листи има пет елемената.

Сада, када користите ову формулу, она враћа низ који има листу од пет плодова у А2: А6. Имајте на уму да ако формулу унесете у ћелију, изаберете је и притиснете Ф9, видећете да она враћа низ назива воћа.

Креирање динамичке падајуће листе у Екцелу (помоћу ОФФСЕТ -а)

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

Ево начина да га учините динамичним (и то није ништа друго до мање подешавање формуле):

• Изаберите ћелију у којој желите да креирате падајућу листу (ћелија Ц2 у овом примеру).
• Идите на Подаци -> Алати за податке -> Валидација података.
• У дијалогу Валидација података, на картици Поставке, изаберите Листа као критеријум провере ваљаности. Чим изаберете Лист, појавиће се изворно поље.
• У поље извора унесите следећу формулу: = ОФФСЕТ ($ 2,0,0 $, ЦОУНТИФ ($ А $ 2: $ А $ 100, ””))
• Уверите се да је означена падајућа опција У ћелији.
• Притисните ОК.

У овој формули сам заменио аргумент 5 са ​​ЦОУНТИФ ($ А $ 2: $ А $ 100, ””).

Функција ЦОУНТИФ броји ћелије које нису празне у опсегу А2: А100. Стога се функција ОФФСЕТ прилагођава тако да укључи све ћелије које нису празне.

Белешка:

• Да би ово функционисало, НЕ сме бити празних ћелија између ћелија које су испуњене.
• Ако желите да направите падајућу листу у више ћелија одједном, изаберите све ћелије у којима желите да је креирате, а затим следите горенаведене кораке. Уверите се да су референце ћелија апсолутне (као што је $ А $ 2) и да нису релативне (попут А2, или А $ 2, или $ А2).

Копирајте падајуће листе у Екцел

Ћелије са потврдом података можете копирати у друге ћелије, а такође ће копирати и потврду ваљаности података.

На пример, ако имате падајућу листу у ћелији Ц2 и желите да је примените и на Ц3: Ц6, једноставно копирајте ћелију Ц2 и залепите је у Ц3: Ц6. Ово ће копирати падајућу листу и учинити је доступном у Ц3: Ц6 (заједно са падајућим менијем, такође ће копирати обликовање).

Ако желите само да копирате падајући мени, а не форматирање, ево корака:

• Копирајте ћелију која има падајући мени.
• Изаберите ћелије у које желите да копирате падајући мени.
• Идите на Почетна -> Залепи -> Посебно залепи.
• У дијалошком оквиру Пасте Специал изаберите Валидатион ин Пасте оптионс.
• Притисните ОК.

Ово ће копирати само падајући мени, а не форматирање копиране ћелије.

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

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

Када копирате ћелију (која не садржи падајућу листу) преко ћелије која садржи падајућу листу, падајућа листа се губи.

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

Како одабрати све ћелије са падајућом листом

Понекад је тешко знати које ћелије садрже падајућу листу.

Стога има смисла означити ове ћелије или давањем јасне ивице или боје позадине.

Уместо ручне провере свих ћелија, постоји брз начин да изаберете све ћелије које имају падајуће листе (или било које правило за проверу ваљаности података).

• Идите на Почетна -> Пронађи и изабери -> Иди на Посебно.
• У дијалогу Иди на специјално изаберите Валидација података
  • Валидација података има две опције: Све и Исто. Сви би изабрали све ћелије на које је примењено правило валидације података. Исто би одабрало само оне ћелије које имају исто правило за валидацију података као и оно активне ћелије.
• Притисните ОК.

Ово би тренутно одабрало све ћелије на које је примењено правило провере ваљаности података (ово укључује и падајуће листе).

Сада можете једноставно обликовати ћелије (дати оквир или боју позадине) тако да буду визуелно видљиве и да случајно не копирате другу ћелију на њу.

Ево још једне технике Јона Ацампоре коју можете користити да икона падајуће стрелице увек буде видљива. Такође можете видети неке начине да то учините у овом видеу од господина Екцел.

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

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

Ако више волите читање него гледање видеа, наставите са читањем.

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

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

Испод је пример условне/зависне падајуће листе:

У горњем примеру, када ставке наведене у „падајућем менију 2“ зависе од избора направљеног у „падајућем прозору 1“.

Сада да видимо како то створити.

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

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

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

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

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

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

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