Выбор нескольких элементов в выпадающем списке Excel – полное руководство
Выпадающие списки в Excel — это отличный инструмент для обеспечения согласованности данных и удобства ввода. Однако, по умолчанию они ограничивают вас выбором только одного элемента. Но что, если вам нужно выбрать несколько элементов из одного и того же выпадающего списка? Это подробное руководство расскажет о методах, которые позволят включить множественный выбор в выпадающих списках Excel, управлять дубликатами, настраивать разделители и определять область применения этих списков.
- Разрешение дублирующихся элементов
- Удаление существующих элементов
- Настройка пользовательского разделителя
- Установка указанного диапазона
- Выполнение в защищенном листе
Включение множественного выбора в выпадающем списке
Этот раздел предоставляет два метода, которые помогут вам включить множественный выбор в выпадающем списке в Excel.
Использование кода VBA
Чтобы разрешить множественный выбор в выпадающем списке, вы можете использовать "Visual Basic for Applications" (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. Этот удобный инструмент упрощает включение множественного выбора в выпадающих списках, позволяя легко настраивать разделители и управлять дубликатами для удовлетворения различных потребностей.
После установки Kutools для Excel перейдите на вкладку «Kutools», выберите «Выпадающий список» > «Многократный выпадающий список». Затем вам нужно настроить следующим образом.
- Укажите диапазон, содержащий выпадающий список, из которого необходимо выбрать несколько элементов.
- Укажите разделитель для выбранных элементов в ячейке выпадающего списка.
- Нажмите «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
Если у вас есть выпадающий список проверки данных с большим количеством значений, вам нужно прокручивать список, чтобы найти нужное значение, или вводить полное слово в поле списка напрямую. Если бы существовал метод автозаполнения при вводе первой буквы в выпадающем списке, все стало бы намного проще. Это руководство предоставляет метод решения проблемы.
Создание выпадающего списка из другой книги в Excel
Очень легко создать выпадающий список проверки данных между листами в одной книге. Но если данные для проверки находятся в другой книге, что вы будете делать? В этом руководстве вы узнаете, как создать выпадающий список из другой книги в Excel.
Создание поискового выпадающего списка в Excel
Для выпадающего списка с большим количеством значений поиск подходящего значения может быть сложной задачей. Ранее мы представили метод автозаполнения выпадающего списка при вводе первой буквы в поле списка. Кроме функции автозаполнения, вы также можете сделать выпадающий список поисковым для повышения эффективности работы по поиску подходящих значений в выпадающем списке. Для создания поискового выпадающего списка попробуйте метод из этого руководства.
Автоматическое заполнение других ячеек при выборе значений в выпадающем списке Excel
Предположим, вы создали выпадающий список на основе значений в диапазоне ячеек B8:B14. При выборе любого значения в выпадающем списке вы хотите, чтобы соответствующие значения в диапазоне ячеек C8:C14 автоматически заполнялись в выбранной ячейке. Для решения этой проблемы методы из этого руководства помогут вам.
Лучшие инструменты для повышения продуктивности работы с Office
Ускорьте работу в Excel с Kutools для Excel и ощутите новую степень эффективности. Kutools для Excel предлагает более300 расширенных функций для повышения продуктивности и экономии времени. Нажмите здесь, чтобы выбрать нужную вам функцию...
Office Tab добавляет вкладки в Office и делает вашу работу намного проще
- Включите редактирование и чтение с вкладками в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна, а не в отдельных окнах.
- Увеличьте свою продуктивность на50% и сократите сотни лишних кликов мышью каждый день!
Содержание
- Включение множественного выбора
- Использование кода VBA
- Использование Kutools для Excel за несколько кликов
- Дополнительные операции
- Разрешение дублирующихся элементов
- Удаление любых существующих элементов
- Настройка пользовательского разделителя
- Установка указанного диапазона
- Выполнение в защищенном листе
- Связанные статьи
- Лучшие инструменты для повышения производительности Office
- Комментарии