Виберіть кілька елементів у розкривному списку Excel – повний посібник
Розкривні списки Excel є чудовим інструментом для забезпечення узгодженості даних і простоти введення. Однак за умовчанням вони обмежують вас вибором лише одного елемента. Але що, якщо вам потрібно вибрати кілька елементів з одного розкривного списку? У цьому вичерпному посібнику розглядаються способи вмикання кількох варіантів у розкривних списках Excel, керування дублікатами, встановлення спеціальних роздільників і визначення сфери цих списків.
- Дозвіл повторюваних елементів
- Видалення наявних елементів
- Налаштування спеціального роздільника
- Встановлення заданого діапазону
- Виконання на захищеному аркуші
Увімкнення кількох варіантів у розкривному списку
У цьому розділі наведено два методи, які допоможуть вам увімкнути кілька варіантів у розкривному списку в Excel.
Використання коду VBA
Щоб дозволити кілька варіантів у розкривному списку, ви можете використовувати Visual Basic для додатків (VBA) в Excel. Сценарій може змінити поведінку розкривного списку, щоб зробити його списком із множинним вибором. Будь ласка, зробіть наступне.
Крок 1. Відкрийте редактор аркуша (коду).
- Відкрийте робочий аркуш, який містить розкривний список, для якого потрібно ввімкнути множинний вибір.
- Клацніть правою кнопкою миші вкладку аркуша та виберіть Переглянути код з контекстного меню.
Крок 2. Використовуйте код VBA
Тепер скопіюйте наступний код VBA та вставте його у вікно початкового аркуша (Код).
Код VBA: увімкніть кілька варіантів у розкривному списку Excel.
Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20240118
Dim xRng As Range
Dim xValue1 As String
Dim xValue2 As String
Dim delimiter As String
Dim TargetRange As Range
Set TargetRange = Me.UsedRange ' Users can change target range here
delimiter = ", " ' Users can change the delimiter here
If Target.Count > 1 Or Intersect(Target, TargetRange) Is Nothing Then Exit Sub
On Error Resume Next
Set xRng = TargetRange.SpecialCells(xlCellTypeAllValidation)
If xRng Is Nothing Then Exit Sub
Application.EnableEvents = False
xValue2 = Target.Value
Application.Undo
xValue1 = Target.Value
Target.Value = xValue2
If xValue1 <> "" And xValue2 <> "" Then
If Not (xValue1 = xValue2 Or _
InStr(1, xValue1, delimiter & xValue2) > 0 Or _
InStr(1, xValue1, xValue2 & delimiter) > 0) Then
Target.Value = xValue1 & delimiter & xValue2
Else
Target.Value = xValue1
End If
End If
Application.EnableEvents = True
On Error GoTo 0
End Sub
Результат
Коли ви повернетеся до робочого аркуша, у розкривному списку ви зможете вибрати кілька варіантів, дивіться демонстрацію нижче:
Наведений вище код VBA:
- Застосовується до всіх розкривних списків перевірки даних на поточному робочому аркуші, як існуючих, так і створених у майбутньому.
- Запобігає вибору того самого елемента більше одного разу в кожному розкривному списку.
- Використовує кому як роздільник для вибраних елементів. Будь ласка, використовуйте інші роздільники перегляньте цей розділ, щоб змінити роздільник.
Використання Kutools для Excel у кілька кліків
Якщо ви не влаштовуєте VBA, є простіша альтернатива Kutools для Excel's Вибірний список, що випадає функція. Цей зручний інструмент спрощує вмикання кількох варіантів у розкривних списках, дозволяючи налаштовувати роздільник і легко керувати дублікатами відповідно до різних потреб.
після встановлення Kutools для Excel, перейдіть до Кутулс вкладка, виберіть Випадаючий список > Вибірний список, що випадає. Потім потрібно налаштувати наступним чином.
- Укажіть діапазон, що містить розкривний список, з якого потрібно вибрати кілька елементів.
- У комірці розкривного списку вкажіть роздільник для виділених елементів.
- Натисніть OK , щоб завершити налаштування.
Результат
Тепер, коли ви клацаєте клітинку з розкривним списком у вказаному діапазоні, поруч із нею з’являється поле зі списком. Просто натисніть кнопку «+» поруч із елементами, щоб додати їх до комірки, що розкривається, і натисніть кнопку «-», щоб видалити елементи, які вам більше не потрібні. Дивіться демонстрацію нижче:
- Перевірте Перенесення тексту після вставки розділювача якщо ви хочете відобразити вибрані елементи вертикально в клітинці. Якщо ви віддаєте перевагу горизонтальному списку, залиште цей параметр не позначеним.
- Перевірте Увімкніть пошук якщо ви хочете додати рядок пошуку до розкривного списку.
- Щоб застосувати цю функцію, будь ласка завантажте та встановіть Kutools для Excel перший.
Більше операцій для розкривного списку з кількома виборами
У цьому розділі зібрано різні сценарії, які можуть знадобитися під час увімкнення кількох варіантів у розкривному списку Перевірка даних.
Дозволяти дублікати елементів у розкривному списку
Дублікати можуть бути проблемою, якщо дозволено кілька варіантів у розкривному списку. Наведений вище код VBA не дозволяє повторювати елементи в розкривному списку. Якщо вам потрібно зберегти повторювані елементи, спробуйте код VBA в цьому розділі.
Код VBA: дозволити дублікати в розкривному списку перевірки даних
Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20240118
Dim xRng As Range
Dim xValue1 As String
Dim xValue2 As String
Dim delimiter As String
Dim TargetRange As Range
Set TargetRange = Me.UsedRange ' Users can change target range here
delimiter = ", " ' Users can change the delimiter here
If Target.Count > 1 Or Intersect(Target, TargetRange) Is Nothing Then Exit Sub
On Error Resume Next
Set xRng = TargetRange.SpecialCells(xlCellTypeAllValidation)
If xRng Is Nothing Then Exit Sub
Application.EnableEvents = False
xValue2 = Target.Value
Application.Undo
xValue1 = Target.Value
Target.Value = xValue2
If xValue1 <> "" And xValue2 <> "" Then
Target.Value = xValue1 & delimiter & xValue2
End If
Application.EnableEvents = True
On Error GoTo 0
End SubРезультат
Тепер ви можете вибрати кілька елементів із розкривних списків на поточному аркуші. Щоб повторити елемент у комірці розкривного списку, продовжуйте вибирати цей елемент зі списку. Перегляньте скріншот:
Видалення будь-яких наявних елементів із розкривного списку
Після вибору кількох елементів із розкривного списку іноді може знадобитися видалити наявний елемент із комірки розкривного списку. Цей розділ містить інший фрагмент коду VBA, який допоможе вам виконати це завдання.
Код VBA: видаліть усі існуючі елементи з комірки розкривного списку
Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20240118
Dim xRngDV As Range
Dim TargetRange As Range
Dim oldValue As String
Dim newValue As String
Dim delimiter As String
Dim allValues As Variant
Dim valueExists As Boolean
Dim i As Long
Dim cleanedValue As String
Set TargetRange = Me.UsedRange ' Set your specific range here
delimiter = ", " ' Set your desired delimiter here
If Target.CountLarge > 1 Then Exit Sub
' Check if the change is within the specific range
If Intersect(Target, TargetRange) Is Nothing Then Exit Sub
On Error Resume Next
Set xRngDV = Target.SpecialCells(xlCellTypeAllValidation)
If xRngDV Is Nothing Or Target.Value = "" Then
' Skip if there's no data validation or if the cell is cleared
Application.EnableEvents = True
Exit Sub
End If
On Error GoTo 0
If Not Intersect(Target, xRngDV) Is Nothing Then
Application.EnableEvents = False
newValue = Target.Value
Application.Undo
oldValue = Target.Value
Target.Value = newValue
' Split the old value by delimiter and check if new value already exists
allValues = Split(oldValue, delimiter)
valueExists = False
For i = LBound(allValues) To UBound(allValues)
If Trim(allValues(i)) = newValue Then
valueExists = True
Exit For
End If
Next i
' Add or remove value based on its existence
If valueExists Then
' Remove the value
cleanedValue = ""
For i = LBound(allValues) To UBound(allValues)
If Trim(allValues(i)) <> newValue Then
If cleanedValue <> "" Then cleanedValue = cleanedValue & delimiter
cleanedValue = cleanedValue & Trim(allValues(i))
End If
Next i
Target.Value = cleanedValue
Else
' Add the value
If oldValue <> "" Then
Target.Value = oldValue & delimiter & newValue
Else
Target.Value = newValue
End If
End If
Application.EnableEvents = True
End If
End SubРезультат
Цей код VBA дозволяє вибрати кілька елементів зі спадного списку та легко видалити будь-який елемент, який ви вже вибрали. Після вибору кількох елементів, якщо ви хочете видалити певний, просто виберіть його знову зі списку.
Налаштування спеціального роздільника
У наведених вище кодах VBA роздільником є кома. Ви можете змінити цю змінну на будь-який потрібний символ для використання як роздільника для вибору в розкривному списку. Ось як ви можете зробити:
Як ви бачите, усі наведені вище коди VBA мають такий рядок:
delimiter = ", "Вам просто потрібно змінити кому на будь-який роздільник, як вам потрібно. Наприклад, якщо ви хочете розділити елементи крапкою з комою, змініть рядок на:
delimiter = "; "delimiter = vbNewLineВстановлення заданого діапазону
Наведені вище коди VBA застосовуються до всіх розкривних списків на поточному аркуші. Якщо ви хочете, щоб коди VBA застосовувалися лише до певного діапазону розкривних списків, ви можете вказати діапазон у наведеному вище коді VBA таким чином.
Як ви бачите, усі наведені вище коди VBA мають такий рядок:
Set TargetRange = Me.UsedRangeВам просто потрібно змінити рядок на:
Set TargetRange = Me.Range("C2:C10")Виконання на захищеному аркуші
Уявіть, що ви захистили аркуш паролем "123" і встановіть клітинки розкривного списку на "розблокована" перед активацією захисту, таким чином гарантуючи, що функція множинного вибору залишається активною після захисту. Однак згадані вище коди VBA не можуть працювати в цьому випадку, і в цьому розділі описано інший сценарій VBA, який спеціально розроблено для обробки функціональності множинного вибору на захищеному аркуші.
Код VBA: увімкніть множинний вибір у розкривному списку без дублікатів
Private Sub Worksheet_Change(ByVal Target As Range)
'Updated by Extendoffice 20240118
Dim xRng As Range
Dim xValue1 As String
Dim xValue2 As String
Dim delimiter As String
Dim TargetRange As Range
Dim isProtected As Boolean
Dim pswd As Variant
Set TargetRange = Me.UsedRange ' Set your specific range here
delimiter = ", " ' Users can change the delimiter here
If Target.Count > 1 Or Intersect(Target, TargetRange) Is Nothing Then Exit Sub
' Check if sheet is protected
isProtected = Me.ProtectContents
If isProtected Then
' If protected, temporarily unprotect. Adjust or remove the password as needed.
pswd = "yourPassword" ' Change or remove this as needed
Me.Unprotect Password:=pswd
End If
On Error Resume Next
Set xRng = TargetRange.SpecialCells(xlCellTypeAllValidation)
If xRng Is Nothing Then
If isProtected Then Me.Protect Password:=pswd
Exit Sub
End If
Application.EnableEvents = False
xValue2 = Target.Value
Application.Undo
xValue1 = Target.Value
Target.Value = xValue2
If xValue1 <> "" And xValue2 <> "" Then
If Not (xValue1 = xValue2 Or _
InStr(1, xValue1, delimiter & xValue2) > 0 Or _
InStr(1, xValue1, xValue2 & delimiter) > 0) Then
Target.Value = xValue1 & delimiter & xValue2
Else
Target.Value = xValue1
End If
End If
Application.EnableEvents = True
On Error GoTo 0
' Re-protect the sheet if it was protected
If isProtected Then
Me.Protect Password:=pswd
End If
End SubУвімкнувши кілька варіантів вибору в розкривних списках Excel, ви можете значно підвищити функціональність і гнучкість своїх робочих аркушів. Незалежно від того, чи добре ви працюєте з кодуванням VBA, чи віддаєте перевагу більш простому рішенню, такому як Kutools, тепер у вас є можливість перетворити ваші стандартні розкривні списки на динамічні інструменти з кількома виборами. З цими навичками тепер ви можете створювати більш динамічні та зручні документи Excel. Для тих, хто прагне глибше заглибитися в можливості Excel, наш веб-сайт може похвалитися великою кількістю посібників. Дізнайтеся більше порад і підказок щодо Excel тут.
Найкращі інструменти продуктивності офісу
Покращуйте свої навички Excel за допомогою Kutools для Excel і відчуйте ефективність, як ніколи раніше. Kutools для Excel пропонує понад 300 додаткових функцій для підвищення продуктивності та економії часу. Натисніть тут, щоб отримати функцію, яка вам найбільше потрібна...
Вкладка Office Передає інтерфейс із вкладками в Office і значно полегшує вашу роботу
- Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
- Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
- Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
Зміст
- Увімкнення множинного вибору
- Використання коду VBA
- Використання Kutools для Excel у кілька кліків
- Більше операцій
- Дозвіл повторюваних елементів
- Видалення наявних елементів
- Налаштування спеціального роздільника
- Встановлення заданого діапазону
- Виконання на захищеному аркуші
- Статті по темі
- Найкращі інструменти для підвищення продуктивності офісу
- Коментарі