Как да настроите падащ списък в Excel (Excel add do create)

Как да създадете падащ списък в Excel? Всички отдавна знаят колко добре Excel работи с таблици и всякакви формули, но малко хора знаят, че тук могат да се правят падащи списъци. И днес ще говорим за тях.

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

Първият вариант е много прост. Ако въведете подобни данни в една колона отгоре надолу, тогава просто трябва да застанете върху клетката под данните и да натиснете клавишната комбинация "Alt + стрелка надолу". Пред вас ще се появи падащ списък, от който с един клик можете да изберете нужните ви данни.

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

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

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

Следващата стъпка е да изберете клетката, където ще бъде поставен нашият падащ списък и да отидете в раздела на менюто „Данни“, щракнете върху иконата „Проверка на данни“. В прозореца, който се отваря, трябва да изберем типа данни, които ще бъдат въведени в нашата клетка. В нашия случай избираме „Списъци“ и по-долу чрез знака за равенство предписваме името на нашия диапазон и натискаме OK. За да приложите списъка към всички клетки, достатъчно е просто да изберете цялата колона или област, от която се нуждаете, преди да включите проверката на данните.

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

Въведете данни в клетки A1:A10, които ще действат като източник за списъка. В нашия пример въведохме числа, те ще се появят в падащия списък. Изберете клетката (напр. E5), която ще съдържа падащия списък. Изберете менюто Данни -> Валидиране на данни, за да отворите диалоговия прозорец Валидиране на входни стойности.

3. В раздела Опции изберете опцията Списък от падащото меню. Уверете се, че необходимите квадратчета са отметнати.

4. След това щракнете върху бутона. Ще се появи следният диалогов прозорец.

5. Изберете с мишката елементите, които ще се появят в падащия списък на листа, щракнете върху бутона и се върнете в прозореца за проверка на въвеждането, след което щракнете върху бутона OK.

6. Ще бъде създаден падащ списък в Excel.

Ако списъкът ви е кратък, можете да въведете елементи директно в „Източник“ в раздела „Настройки“ на диалоговия прозорец „Проверка на въвеждане“. Разделете всеки елемент от списъка с разделителите, посочени в регионалните настройки.
Ако списъкът трябва да бъде на различен лист, можете да използвате параметъра „= Списък“, преди да посочите диапазона от данни.
Как да създадете падащ списък в excel въз основа на данните от списъка

Представете си, че имаме списък с плодове:
Как да направите падащ списък в Excel

За да създадем падащ списък, трябва да направим следните стъпки:

Отидете в раздела „Данни“ => раздел „Работа с данни“ на лентата с инструменти => изберете елемента „Проверка на данни“.

В полето „Източник“ въведете диапазона от имена на плодове =$A$2:$A$6 или просто поставете курсора на мишката в полето за въвеждане на стойност „Източник“ и след това изберете диапазона от данни с мишката:

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

Как да направите падащ списък в Excel с помощта на ръчно въвеждане на данни

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

За това имаме нужда от:
Изберете клетката, в която искаме да създадем падащ списък;
Отидете в раздела „Данни“ => раздел „Работа с данни“ на лентата с инструменти =>
Валидиране на данни в Excel

В изскачащия прозорец „Проверка на входните стойности“ в раздела „Параметри“ в типа данни изберете „Списък“:
Валидиране на входни стойности в Excel

В полето „Източник“ въведете стойността „Да; Не".
Щракнете върху "OK"
Не точно

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

Ако искате да създадете падащ списък в няколко клетки едновременно, изберете необходимите клетки и следвайте инструкциите по-горе.
Как да създадете падащ списък в Excel с помощта на функцията OFFSET

Заедно с методите по-горе, можете също да използвате формулата OFFSET за създаване на падащи списъци.

Например, имаме списък със списък с плодове:

За да направите падащ списък с помощта на формулата OFFSET, трябва да направите следното:
Изберете клетката, в която искаме да създадем падащ списък;
Отидете в раздела „Данни“ => раздел „Работа с данни“ на лентата с инструменти => изберете елемента „Проверка на данни“:
Валидиране на данни в Excel

В изскачащия прозорец „Проверка на входните стойности“ в раздела „Параметри“ в типа данни изберете „Списък“:
Валидиране на входни стойности в Excel

В полето „Източник“ въведете формулата: =OFFSET(A$2$;0;0;5)
натиснете "ОК"

Системата ще създаде падащ списък със списък с плодове.
Как работи тази формула?

В примера по-горе използвахме формулата =OFFSET(reference,row_offset,column_offset,[height],[width]).
Тази функция съдържа пет аргумента. Аргументът „препратка“ (в примера $A$2) показва от коя клетка да започне отместването. В аргументите “offset_by_rows” и “offset_by_columns” (в примера е посочена стойността “0”) - с колко реда/колони трябва да се преместите, за да се показват данни.

Аргументът „[височина]“ е зададен на „5“, което е височината на диапазона от клетки. Ние не посочваме аргумента „[width]“, тъй като в нашия пример диапазонът се състои от една колона.
Използвайки тази формула, системата ви връща като данни за падащия списък диапазон от клетки, започващ от клетка $A$2, състоящ се от 5 клетки.

Как да направите падащ списък в Excel със заместване на данни (с помощта на функцията OFFSET)

Ако използвате формулата OFFSET в примера по-горе, за да създадете списък, тогава създавате списък с данни, който е фиксиран в определен диапазон от клетки. Ако искате да добавите някаква стойност като елемент от списъка, ще трябва да коригирате формулата ръчно.

По-долу ще научите как да направите динамичен падащ списък, който автоматично ще зарежда нови данни за показване.
За да създадете списък, ще ви трябва:
Изберете клетката, в която искаме да създадем падащ списък;

Отидете в раздела „Данни“ => раздел „Работа с данни“ на лентата с инструменти => изберете елемента „Проверка на данни“;
В изскачащия прозорец „Проверка на входните стойности“ в раздела „Параметри“ в типа данни изберете „Списък“;
В полето „Източник“ въведете формулата: =OFFSET(A$2$;0;0;COUNTIF($A$2:$A$100;)<>”))
натиснете "ОК"

В тази формула в аргумента “[височина]” посочваме като аргумент, обозначаващ височината на списъка с данни - формулата COUNTIF, която изчислява броя на непразните клетки в дадения диапазон A2:A100.

Забележка: за правилната работа на формулата е важно да няма празни редове в списъка с данни, които да се показват в падащото меню.

Как да създадете падащ списък в Excel с автоматично заместване на данни

За да се заредят автоматично нови данни в падащия списък, който сте създали, трябва да направите следното:
Създаваме списък с данни за показване в падащия списък. В нашия случай това е списък с цветове. Изберете списъка с левия бутон на мишката:
падащ списък с автоматично заместване в ексел

В лентата с инструменти щракнете върху елемента „Форматиране като таблица“:

Изберете стила на таблицата от падащото меню

Чрез натискане на бутона "OK" в изскачащия прозорец потвърждаваме избрания диапазон от клетки:

Задайте име на таблицата в горната дясна клетка над колона „A“:

Таблицата с данни е готова, сега можем да създадем падащ списък. За целта са ви необходими:
Изберете клетката, в която искаме да създадем списък;

Отидете в раздела „Данни“ => раздел „Работа с данни“ на лентата с инструменти => изберете елемента „Проверка на данни“:

В изскачащия прозорец „Проверка на входните стойности“ в раздела „Параметри“ в типа данни изберете „Списък“:

В полето източник посочете ="името на вашата таблица". В нашия случай го нарекохме „Списък“:
Изходно поле автоматично заместване на данни в падащия списък на Excel

Готов! Създаден е падащият списък, който показва всички данни от посочената таблица:

За да добавите нова стойност към падащия списък, просто добавете информация към следващата клетка след таблицата с данни:

Таблицата автоматично ще разшири своя диапазон от данни. Падащият списък ще се актуализира съответно с новата стойност от таблицата:
Автоматично заместване на данни в падащия списък в excel

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

В Excel е възможно да се копират създадените падащи списъци. Например в клетка A1 имаме падащ списък, който искаме да копираме в диапазона от клетки A2:A6.

За да копирате падащия списък с текущото форматиране:
щракнете с левия бутон на мишката върху клетката с падащия списък, който искате да копирате;

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

натиснете клавишната комбинация CTRL+V.
Така че ще копирате падащия списък, като запазите оригиналния формат на списъка (цвят, шрифт и т.н.). Ако искате да копирате/поставите падащия списък, без да запазвате формата, тогава:
щракнете с левия бутон на мишката върху клетката с падащия списък, който искате да копирате;

натиснете клавишната комбинация на клавиатурата CTRL + C;
изберете клетката, в която искате да вмъкнете падащия списък;
натиснете десния бутон на мишката => извикайте падащото меню и щракнете върху „Специално поставяне“;
падащ списък в excel

В прозореца, който се показва, в секцията „Вмъкване“ изберете елемента „условия за стойности“:

Натиснете OK
След това Excel ще копира само данните от падащия списък, без да запазва форматирането на оригиналната клетка.
Как да изберете всички клетки, съдържащи падащ списък в excel

Понякога е трудно да се разбере колко клетки във файл на Excel съдържат падащи списъци. Има лесен начин да ги покажете. За това:

Кликнете върху раздела „Начало“ в лентата с инструменти;
Кликнете върху „Намиране и избор“ и изберете „Избор на група клетки“:

В диалоговия прозорец изберете Проверка на данни. В това поле е възможно да изберете елементите „Всички“ и „Същите“. „Всички“ ще избере всички падащи списъци на листа. „Същият“ елемент ще покаже падащи списъци, подобни по съдържание на данните в падащото меню. В нашия случай избираме „всички“:
Падащ списък в Excel. Как да намерите всички списъци

Натиснете OK
Като щракнете върху „OK“, Excel ще избере всички клетки с падащ списък на листа. Така че можете да приведете всички списъци наведнъж в общ формат, да маркирате граници и т.н.

Как да направите зависими падащи списъци в Excel

Понякога трябва да създадем няколко падащи списъка и освен това по такъв начин, че чрез избиране на стойности от първия списък Excel определя какви данни да покаже във втория падащ списък.
Да предположим, че имаме списъци с градове в две държави Русия и САЩ:

За да създадем зависим падащ списък, имаме нужда от:
Създайте два наименувани диапазона за клетки „A2:A5“ с име „Русия“ и за клетки „B2:B5“ с име „САЩ“. За да направим това, трябва да изберем целия диапазон от данни за падащите списъци:
зависим падащ списък в excel

Отидете в раздела „Формули“ => щракнете в секцията „Дефинирани имена“ върху елемента „Създаване от селекция“:
Зависими падащи списъци в Excel

В изскачащия прозорец „Създаване на имена от избрания диапазон“ поставете отметка в квадратчето „на реда по-горе“. Правейки това, Excel ще създаде два диапазона с имена „Русия“ и „САЩ“ със списъци с градове:
зависим-падащ-списък-в-ексел

Натиснете OK
В клетка „D2“ създайте падащ списък, за да изберете държавите „Русия“ или „САЩ“. И така, ще създадем първия падащ списък, в който потребителят ще може да избере една от двете държави.

Сега, за да създадете зависим падащ списък:
Изберете клетка E2 (или всяка друга клетка, където искате да направите зависим падащ списък);
Кликнете върху раздела „Данни“ => „Проверка на данни“;
В изскачащия прозорец „Проверка на въведените стойности“ в раздела „Параметри“ в типа данни изберете „Списък“:
Валидиране на входни стойности в Excel

Натиснете OK

Сега, ако изберете държавата „Русия“ в първия падащ списък, тогава само онези градове, които принадлежат на тази държава, ще се появят във втория падащ списък. Също така в случай, че изберете „САЩ“ от първия падащ списък.