Выберите несколько элементов в раскрывающемся списке 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 for Excel за несколько кликов
Если вам не нравится VBA, есть более простая альтернатива: Kutools for 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 for Excel и почувствуйте эффективность, как никогда раньше. Kutools for Excel предлагает более 300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы получить функцию, которая вам нужна больше всего...
Вкладка Office: интерфейс с вкладками в Office и упрощение работы
- Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
- Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
- Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
Содержание
- Включение множественного выбора
- Использование кода VBA
- Использование Kutools for Excel за несколько кликов
- Больше операций
- Разрешение дублирования элементов
- Удаление любых существующих элементов
- Установка пользовательского разделителя
- Установка указанного диапазона
- Выполнение на защищенном листе
- Статьи по теме
- Лучшие инструменты для работы в офисе
- Комментарии