Падащ списък на Excel: създаване, редактиране, премахване и по-разширени операции

Падащият списък е подобен на списъчното поле, което позволява на потребителите да изберат една стойност от списък за избор. Този урок ще демонстрира основните операции за падащ списък: създаване, редактиране и премахване на падащ списък в Excel. Освен това, този урок предоставя разширени операции за падащия списък, за да подобри неговата функционалност за решаване на повече проблеми с Excel.

---

Създайте прост падащ списък

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

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

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

1. Изберете диапазон от клетки за намиране на падащия списък.

Съвети: Можете да създадете падащ списък за множество несъседни клетки едновременно, като задържите Ctrl докато избирате клетките една по една.

2. кликване Дата > Потвърждаване на данните > Потвърждаване на данните.

3. В Потвърждаване на данните диалогов прозорец, под Настройки раздел, моля, конфигурирайте както следва.

бележки:

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

Създайте динамичен падащ списък от таблица

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

1. Изберете оригиналния диапазон от данни и след това натиснете Ctrl + T ключове.

2. кликване OK в изскачането Създаване на таблица диалогов прозорец. След това диапазонът от данни се преобразува в таблица.

3. Изберете диапазон от клетки за поставяне на падащия списък и след това щракнете Дата > Потвърждаване на данните > Потвърждаване на данните.

4. В Потвърждаване на данните диалогов прозорец, трябва да:

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

Създайте динамичен падащ списък с формули

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

1. Изберете клетките, където да изведете падащите списъци.

2. кликване Дата > Потвърждаване на данните > Потвърждаване на данните.

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

След това се създават динамични падащи списъци. При добавяне или премахване на данни от конкретния диапазон, стойностите в падащите списъци ще се актуализират автоматично.

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

Можете също да създадете падащ списък от именуван диапазон в Excel.

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

2. кликване Дата > Потвърждаване на данните > Потвърждаване на данните.

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

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

Създайте падащ списък от друга работна книга

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

1. Отворете работната книга „SourceData“. В тази работна книга изберете данните, въз основа на които ще създадете падащ списък, въведете име на диапазон в Име и след това натиснете Въведете ключ.

Тук наричам диапазона като Град.

2. Отворете работния лист, в който ще вмъкнете падащия списък. Кликнете формули > Определете име.

3. В Ново име диалогов прозорец, трябва да създадете именуван диапазон въз основа на името на диапазона, който сте създали в работната книга „SourceData“, моля, конфигурирайте както следва.

бележки:

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

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

Сега падащите списъци са вмъкнати в избрания диапазон. И падащите стойности са от друга работна книга.

Лесно създайте падащ списък с невероятен инструмент

Тук силно препоръчвам Създайте прост падащ списък полезност на Kutools за Excel. С тази функция можете лесно да създадете падащ списък със специфични стойности на клетки или да създадете падащ списък с потребителски списъци, предварително зададени в Excel.

1. Изберете клетките, които искате да вмъкнете в падащия списък, и след това щракнете Kutools > Падащ списък > Създайте прост падащ списък.

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

Забележка: Ако искате да създадете падащ списък въз основа на персонализиран списък, предварително зададен в Excel, моля, изберете Персонализирани списъци опция в източник изберете персонализиран списък в Персонализирани списъци и след това щракнете върху OK бутон.

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

---

Редактиране на падащия списък

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

Редактирайте падащ списък въз основа на диапазон от клетки

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

1. Изберете клетките, съдържащи падащия списък, който искате да редактирате, и след това щракнете Дата > Потвърждаване на данните > Потвърждаване на данните.

2. В Потвърждаване на данните диалогов прозорец, променете препратките към клетки в източник и след това кликнете върху OK бутон.

Редактирайте падащ списък въз основа на именуван диапазон

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

1. кликване формули > Мениджър на имена.

Съвети: Можете да отворите Мениджър на имена прозорец чрез натискане на Ctrl + F3 ключове.

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

3. След това a Microsoft Excel изскача диалогов прозорец, щракнете върху Да , за да запазите промените.

След това падащите списъци въз основа на този именуван диапазон се актуализират.

---

Премахване на падащия списък

Този раздел говори за премахване на падащ списък в Excel.

Премахване на падащ списък с вграден Excel

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

1. Изберете диапазона от клетки, съдържащ падащия списък, който искате да премахнете.

2. кликване Дата > Потвърждаване на данните > Потвърждаване на данните.

3. В Потвърждаване на данните кликнете върху Изчисти всички , след което щракнете върху OK за да запазите промените.

Сега падащите списъци се премахват от избрания диапазон.

Лесно премахване на падащи списъци с невероятен инструмент

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

1. Изберете диапазона от клетки, съдържащ падащия списък, който искате да премахнете.

2. кликване Kutools > Предотвратяване на писане > Изчистване на ограниченията за валидиране на данни. Вижте екранна снимка:

3. След това a Kutools за Excel се появява диалогов прозорец, който ви пита дали да изчистите падащия списък, моля, щракнете върху OK бутон.

След това падащите списъци в този избран диапазон се премахват незабавно.

---

Добавете цвят към падащия списък

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

Добавете цвят към падащия списък с условно форматиране

Можете да създадете условни правила за клетката, съдържаща падащия списък, за да я кодирате с цвят. Моля, направете следното.

1. Изберете клетките, съдържащи падащия списък, който искате да кодирате с цвят.

2. кликване Начало > Условно форматиране > Управление на правилата.

3. В Мениджър на Rues за условно форматиране кликнете върху Ново правило бутон.

4. В Ново правило за форматиране диалогов прозорец, моля, конфигурирайте както следва.

5. Когато се върне към Мениджър на правилата за условно форматиране диалогов прозорец, повторете горните стъпки 3 и 4, за да посочите цветове за други падащи елементи. След като приключите с определянето на цветовете, щракнете върху OK за да запазите промените.

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

Добавете лесно цвят към падащия списък с невероятен инструмент

Представете тук Цветен падащ списък функция на Kutools за Excel за да ви помогне лесно да добавите цвят към падащия списък в Excel.

1. Изберете клетките, съдържащи падащия списък, който искате да добавите цвят.

2. кликване Kutools > Падащ списък > Цветен падащ списък.

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

Съвети: Ако искате да маркирате редове въз основа на избор от падащ списък, моля, изберете Ред от диапазон от данни опция в Нанесете и след това изберете редовете, които ще маркирате в Маркирайте редове кутия.

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

Маркирайте клетки въз основа на избор от падащ списък

Маркирайте редове въз основа на избор от падащ списък

---

Създайте зависим падащ списък в Excel или google лист

Зависимият падащ списък помага да се покажат изборите в зависимост от стойността, избрана в първия падащ списък. Ако трябва да създадете зависим (каскадиращ) падащ списък в работен лист на Excel или в google лист, методите в този раздел могат да ви направят услуга.

Създайте зависим падащ списък в работен лист на Excel

Демонстрацията по-долу показва зависимия падащ списък в работния лист на Excel.

Моля, кликнете Как да създадете зависим каскаден падащ списък в Excel? за ръководство стъпка по стъпка урок.

Създайте зависим падащ списък в Google лист

Ако искате да създадете зависим падащ списък в google sheet, моля, вижте Как да създадете зависим падащ списък в Google Sheet?

---

Създайте падащи списъци с възможност за търсене

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

Да предположим, че изходните данни, които искате да създадете падащ списък въз основа на това, се намират в колона A на Sheet1, както е показано на екранната снимка по-долу. Моля, направете следното, за да създадете падащ списък с възможност за търсене в Excel с тези данни.

1. Първо създайте помощна колона до списъка с изходни данни с формула за масив.

В този случай избирам клетка B2, въвеждам формулата по-долу в нея и след това натискам Ctrl + Превключване + Въведете ключове, за да получите първия резултат.

=IFERROR(INDEX($A$2:$A$50,SMALL(IFERROR(MATCH(IF(FIND(CELL("contents"),$A$2:$A$50)>0,$A$2:$A$50,""),$A$2:$A$50,0),""),ROW(A1))),"")

Изберете първата клетка с резултати и след това я плъзнете Дръжка за пълнене надолу, докато стигне до края на списъка.

Забележка: В тази формула за масив $A$2:$A$50 е диапазонът от изходни данни, въз основа на който ще създадете падащ списък. Моля, променете го въз основа на вашия диапазон от данни.

2. кликване формули > Определете име.

3. В Редактиране на име диалогов прозорец, моля, конфигурирайте както следва.

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

4. Отворете Sheet2, изберете диапазона от клетки за падащия списък и след това щракнете Дата > Потвърждаване на данните > Потвърждаване на данните.

5. В Потвърждаване на данните диалогов прозорец, моля, направете следното.

6. Щракнете с десния бутон върху раздела лист (Sheet2) и изберете Преглед на кода от менюто с десен бутон.

7. В откриването Microsoft Visual Basic за приложения прозорец, копирайте кода VBA по-долу в редактора на кода.

VBA код: създайте падащ списък с възможност за търсене в Excel

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculate
End Sub

8. Натисни Друг + Q ключове за затваряне на Microsoft Visual Basic за приложения прозорец.

Сега падащите списъци с възможност за търсене са създадени. Ако искате да вземете артикул, просто въведете един или няколко последователни знака от този артикул в падащата клетка, щракнете върху падащата стрелка и след това артикулът, базиран на въведеното съдържание, се показва в падащия списък. Вижте екранна снимка:

Забележка: Този метод е чувствителен към малки и малки букви.

---

Създайте падащ списък, но покажете различни стойности

Да предположим, че сте създали падащ списък, когато избирате елемент от него, искате нещо друго да се покаже в клетката. Както е показано в демонстрацията по-долу, вие сте създали падащ списък въз основа на списъка с имена на държави, когато избирате име на държава от падащото меню, искате да покажете съкращението на избраното име на държава в падащата клетка. Този раздел предоставя VBA метод, който да ви помогне да решите проблема.

1. От дясната страна на изходните данни (колоната с името на държавата) създайте нова колона, съдържаща съкращението на имената на държавите, които искате да показвате в падащата клетка.

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

3. Изберете клетките за падащия списък (тук избирам D2:D8) и след това щракнете Дата > Потвърждаване на данните > Потвърждаване на данните.

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

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

6. В откриването Microsoft Visual Basic за приложения прозорец, копирайте кода VBA по-долу в редактора на кода.

VBA код: Показване на различни стойности в падащия списък

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20201027
    selectedNa = Target.Value
    If Target.Column = 4 Then
        selectedNum = Application.VLookup(selectedNa, ActiveSheet.Range("dropdown"), 2, False)
        If Not IsError(selectedNum) Then
            Target.Value = selectedNum
        End If
    End If
End Sub

бележки:

7. Натисни Друг + Q ключове за затваряне на Microsoft Visual Basic за приложения прозорец.

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

---

Създайте падащ списък с квадратчета за отметка

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

Както е показано в демонстрацията по-долу, когато щракнете върху клетката, съдържаща падащ списък, се появява списъчно поле. В списъчното поле има квадратче за отметка преди всеки елемент. Можете да поставите отметка в квадратчетата, за да покажете съответните елементи в клетката.

Ако искате да създадете падащ списък с квадратчета за отметка в Excel, вижте Как да създадете падащ списък с множество квадратчета за отметка в Excel?.

---

Добавете автоматично довършване към падащия списък

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

За да направите автоматично попълване на падащ списък в работен лист в Excel, моля, вижте Как да извършвам автоматично довършване при въвеждане в падащия списък на Excel?.

---

Филтриране на данни въз основа на избор от падащ списък

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

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

Съвети: Моля, следвайте горните стъпки, за да създайте падащ списък в Excel.

Създайте падащ списък с уникален списък с елементи

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

1) Копирайте клетките, въз основа на които ще създадете падащ списък Ctrl + C ключове и след това ги поставете в нов диапазон.

2) Изберете клетките в новия диапазон, щракнете Дата > Премахване на дубликати.

3) В Премахване на дубликати кликнете върху OK бутон.

4) Тогава a Microsoft Excel изскача, за да ви каже колко дубликати са премахнати, щракнете OK.

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

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

3. Създайте диапазон въз основа на оригиналния диапазон от данни, за да изведете извлечените данни с формулите по-долу.

бележки:

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

---

Изберете няколко елемента от падащия списък

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

За избор на множество елементи от падащия списък в Excel, моля, вижте Как да създадете падащ списък с множество селекции или стойности в Excel?. Този урок предоставя подробно два метода, които да ви помогнат да решите проблема.

---

Задайте стойност по подразбиране (предварително избрана) за падащия списък

По подразбиране клетка от падащ списък се показва като празна, падащата стрелка се появява само когато щракнете върху клетката. Как с един поглед да разбера кои клетки съдържат падащи списъци в работен лист?

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

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

1. Изберете клетките за падащия списък, щракнете Дата > Потвърждаване на данните > Потвърждаване на данните.

Съвети: Ако вече сте създали падащ списък, моля, изберете клетките, съдържащи падащия списък, и след това щракнете Дата > Потвърждаване на данните > Потвърждаване на данните.

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

След като създадете падащия списък, моля, приложете един от методите по-долу, за да зададете стойност по подразбиране за тях.

Задайте стойност по подразбиране за падащия списък с формула

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

1. Изберете клетката от падащия списък, въведете формулата по-долу в нея и след това натиснете Въведете клавиш за показване на стойността по подразбиране. Ако клетките от падащия списък са последователни, можете да плъзнете Дръжка за пълнене от клетката с резултати, за да приложите формулата към други клетки.

=IF(C2="", "--Изберете елемент от списъка--")

бележки:

Задайте стойност по подразбиране за всички падащи списъци в работен лист наведнъж с VBA код

Да предположим, че има много падащи списъци, намиращи се в различни диапазони във вашия работен лист, за да зададете стойност по подразбиране за всички тях, трябва да прилагате формулата многократно. Това отнема много време. Този раздел предоставя полезен VBA код, за да зададете стойност по подразбиране за всички падащи списъци в работен лист наведнъж.

1. Отворете работния лист, съдържащ падащите списъци, които искате да зададете стойност по подразбиране, натиснете Друг + F11 за да отворите Microsoft Visual Basic за приложения прозорец.

2. В Microsoft Visual Basic за приложения прозорец, кликнете Поставете > Модулии след това поставете долния VBA код в прозореца на кода.

VBA код: Задайте стойност по подразбиране за всички падащи списъци в работен лист наведнъж

Sub SetDropDownListToDefaultValue()
'Updated by Extendoffice 20201026
Dim xWs As Worksheet
Dim xRg, xFRg As Range
Dim xET: xET = Null
Dim xStr As String
xStr = "- Choose from the list -"
Set xWs = Application.ActiveSheet
Set xRg = xWs.UsedRange.Cells
    On Error Resume Next
    For Each xFRg In xRg
    xET = Null
    xET = xFRg.Validation.Type
    If Not IsNull(xET) Then
        If xFRg.Validation.Type = 3 Then
            xFRg.Value = "'" & xStr
        End If
    End If
    Next
End Sub

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

3. Натисни F5 клавиш, след което се появява диалогов прозорец Макроси, уверете се, че DropDownListToDefault е избран в Име на макрос и след това щракнете върху бягане бутон за изпълнение на кода.

След това зададената стойност по подразбиране се попълва незабавно в клетки от падащ списък.

---

Увеличете размера на шрифта на падащия списък

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

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

2. В Microsoft Visual Basic за приложения прозорец, копирайте кода VBA по-долу в редактора на кода.

VBA код: Увеличете размера на шрифта на падащите списъци в работен лист

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'updateby Extendoffice 20201027
    On Error GoTo LZoom
    Dim xZoom As Long
    xZoom = 100
    If Target.Validation.Type = xlValidateList Then xZoom = 130
LZoom:
    ActiveWindow.Zoom = xZoom
End Sub

Забележка: тук xZoom = 130 в кода означава, че ще увеличите размера на шрифта на всички падащи списъци в текущия работен лист до 130. Можете да го промените според нуждите си.

3. Натисни Друг + Q ключове за затваряне на Microsoft Visual Basic за приложения прозорец.

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

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