Как да създадете падащ списък в Excel (единственото ръководство, от което се нуждаете)

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

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

Падащите списъци са доста често срещани на уебсайтове/приложения и са много интуитивни за потребителя.

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

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

Как да създадете падащ списък в Excel

В този раздел ще научите точните стъпки за създаване на падащ списък на Excel:

1. Използване на данни от клетки.
2. Въвеждане на данни ръчно.
3. Използвайки формулата OFFSET.

#1 Използване на данни от клетки

Да предположим, че имате списък с елементи, както е показано по -долу:

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

1. Изберете клетка, в която искате да създадете падащия списък.
2. Отидете на Данни -> Инструменти за данни -> Проверка на данни.
3. В диалоговия прозорец Проверка на данни в раздела Настройки изберете Списък като критерии за валидиране.
   • Веднага щом изберете Списък, се появява полето източник.
4. В полето източник въведете = $ A $ 2: $ A $ 6 или просто щракнете в полето Източник и изберете клетките с мишката и щракнете върху OK. Това ще вмъкне падащ списък в клетка C2.
   • Уверете се, че е поставена отметка на опцията In-cell (която се проверява по подразбиране). Ако тази опция не е маркирана, клетката не показва падащо меню, но можете да въведете ръчно стойностите в списъка.

Забележка: Ако искате да създадете падащи списъци в множество клетки наведнъж, изберете всички клетки, където искате да го създадете, и след това следвайте горните стъпки. Уверете се, че препратките към клетките са абсолютни (например $ A $ 2), а не относителни (като A2, или A $ 2, или $ A2).

#2 Чрез въвеждане на данни ръчно

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

Например, да речем, че искате да покажете две опции, Да и Не, в падащото меню в клетка. Ето как можете директно да го въведете в полето източник на валидиране на данни:

• Изберете клетка, в която искате да създадете падащия списък (клетка C2 в този пример).
• Отидете на Данни -> Инструменти за данни -> Проверка на данни.
• В диалоговия прозорец Проверка на данни в раздела Настройки изберете Списък като критерии за валидиране.
  • Веднага щом изберете Списък, се появява полето източник.
• В полето източник въведете Да, Не
  • Уверете се, че опцията In-cell падащо меню е поставена.
• Щракнете върху OK.

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

Всички елементи, въведени в полето източник, разделени със запетая, се показват в различни редове в падащия списък.

Забележка: Ако искате да създадете падащи списъци в множество клетки наведнъж, изберете всички клетки, където искате да го създадете, и след това следвайте горните стъпки.

#3 Използване на формули на Excel

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

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

Да предположим например, че имате набора от данни, както е показано по -долу:

Ето стъпките за създаване на падащ списък на Excel с помощта на функцията OFFSET:

• Изберете клетка, в която искате да създадете падащия списък (клетка C2 в този пример).
• Отидете на Данни -> Инструменти за данни -> Проверка на данни.
• В диалоговия прозорец Проверка на данни в раздела Настройки изберете Списък като критерии за валидиране.
  • Веднага щом изберете Списък, се появява полето източник.
• В полето Източник въведете следната формула: = OFFSET ($ A $ 2,0,0,5)
  • Уверете се, че опцията In-cell падащо меню е поставена.
• Щракнете върху OK.

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

Забележка: Ако искате да създадете падащ списък в няколко клетки наведнъж, изберете всички клетки, където искате да го създадете, и след това следвайте горните стъпки. Уверете се, че препратките към клетките са абсолютни (например $ A $ 2), а не относителни (като A2, или A $ 2, или $ A2).

Как работи тази формула ??

В горния случай използвахме OFFSET функция, за да създадем падащия списък. Той връща списък с елементи от ra

Той връща списък с елементи от диапазона A2: A6.

Ето синтаксиса на функцията OFFSET: = OFFSET (справка, редове, cols, [височина], [ширина])

Необходими са пет аргумента, където посочихме препратката като A2 (началната точка на списъка). Редовете/Cols са определени като 0, тъй като не искаме да изместваме референтната клетка. Височината е посочена като 5, тъй като в списъка има пет елемента.

Сега, когато използвате тази формула, тя връща масив, който има списъка с петте плода в A2: A6. Имайте предвид, че ако въведете формулата в клетка, изберете я и натиснете F9, ще видите, че тя връща масив от имената на плодовете.

Създаване на динамичен падащ списък в Excel (използване на OFFSET)

Горната техника за използване на формула за създаване на падащ списък може да бъде разширена, за да се създаде и динамичен падащ списък. Ако използвате функцията OFFSET, както е показано по -горе, дори ако добавите още елементи към списъка, падащото меню няма да се актуализира автоматично. Ще трябва да го актуализирате ръчно всеки път, когато променяте списъка.

Ето начин да го направите динамичен (и това не е нищо друго освен незначително ощипване във формулата):

• Изберете клетка, в която искате да създадете падащия списък (клетка C2 в този пример).
• Отидете на Данни -> Инструменти за данни -> Проверка на данни.
• В диалоговия прозорец Проверка на данни в раздела Настройки изберете Списък като критерии за валидиране. Веднага щом изберете Списък, се появява полето източник.
• В полето източник въведете следната формула: = OFFSET ($ A $ 2,0,0, COUNTIF ($ A $ 2: $ A $ 100, ””))
• Уверете се, че падащата опция In-cell е поставена.
• Щракнете върху OK.

В тази формула замених аргумента 5 с COUNTIF ($ A $ 2: $ A $ 100, ””).

Функцията COUNTIF брои непразните клетки в диапазона A2: A100. Следователно функцията OFFSET се настройва така, че да включва всички непразни клетки.

Забележка:

• За да работи това, НЕ трябва да има празни клетки между запълнените клетки.
• Ако искате да създадете падащ списък в няколко клетки наведнъж, изберете всички клетки, където искате да го създадете, и след това следвайте горните стъпки. Уверете се, че препратките към клетките са абсолютни (например $ A $ 2), а не относителни (като A2, или A $ 2, или $ A2).

Копирайте поставяне на падащи списъци в Excel

Можете да копирате поставете клетките с валидиране на данни в други клетки и той също ще копира валидирането на данните.

Например, ако имате падащ списък в клетка C2 и искате да го приложите и към C3: C6, просто копирайте клетката C2 и я поставете в C3: C6. Това ще копира падащия списък и ще го направи наличен в C3: C6 (заедно с падащото меню, той също ще копира форматирането).

Ако искате само да копирате падащото меню, но не и форматирането, ето стъпките:

• Копирайте клетката, която има падащото меню.
• Изберете клетките, където искате да копирате падащото меню.
• Отидете на Начало -> Поставяне -> Специално поставяне.
• В диалоговия прозорец Специално поставяне изберете Проверка в опциите за поставяне.
• Щракнете върху OK.

Това ще копира само падащото меню, но не и форматирането на копираната клетка.

Внимание при работа с падащия списък на Excel

Трябва да бъдете внимателни, когато работите с падащи списъци в Excel.

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

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

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

Понякога е трудно да се знае кои клетки съдържат падащия списък.

Следователно има смисъл да се маркират тези клетки, като им се придаде отделна граница или цвят на фона.

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

• Отидете на Начало -> Намиране и избор -> Отидете на Специално.
• В диалоговия прозорец Отиди на Специално изберете Проверка на данни
  • Валидирането на данни има две възможности: Всички и Същите. Всички биха избрали всички клетки, към които е приложено правило за валидиране на данни. Същото би избрало само онези клетки, които имат същото правило за валидиране на данни като това на активната клетка.
• Щракнете върху OK.

Това незабавно ще избере всички клетки, към които е приложено правило за валидиране на данни (това включва и падащи списъци).

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

Ето още една техника на Jon Acampora, която можете да използвате, за да поддържате винаги видима иконата на стрелката надолу. Можете също да видите някои начини да направите това в това видео от г -н Excel.

Създаване на зависим / условен падащ списък на Excel

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

Ако предпочитате да четете, вместо да гледате видео, продължете да четете.

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

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

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

В горния пример, когато елементите, изброени в „Drop Down 2“, зависят от избора, направен в „Drop Down 1“.

Сега нека видим как да създадем това.

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

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

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

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

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

Важна забележка при работа с условни падащи списъци в Excel:

• Когато сте направили подбора и след това промените падащото меню на родителя, зависимото падащо меню няма да се промени и следователно би било грешен запис. Например, ако изберете САЩ като държава и след това изберете Флорида като щат, а след това се върнете и промените страната на Индия, държавата ще остане като Флорида. Ето един страхотен урок на Debra за изчистване на зависими (условни) падащи списъци в Excel, когато селекцията се промени.
• Ако основната категория е повече от една дума (например „Сезонни плодове“ вместо „Плодове“), тогава трябва да използвате формулата = INDIRECT (SUBSTITUTE (D3, ”“, ”_“)), вместо проста функция INDIRECT, показана по -горе. Причината за това е, че Excel не позволява интервали в именувани диапазони. Така че, когато създавате именен диапазон, използвайки повече от една дума, Excel автоматично вмъква подчертаване между думите. Така че наименованието „Сезонни плодове“ ще бъде „Сезонни_плодове“. Използването на функцията SUBSTITUTE в рамките на функцията INDIRECT гарантира, че пространствата са превърнати в подчертаване.