В статье рассказано, как сделать выпадающий список в Excel, который позволяет делать выбор нескольких значений, включая дубликаты или без них.
Excel значительно продвинулся с момента своего появления, и постоянно вводит все больше полезных функций. Тем не менее, Excel все еще позволяет выбрать только один элемент из выпадающего списка.
Но у нас есть решение: используя VBA, вы можете создать выпадающий список с возможностью выбора нескольких значений.
Возможность предотвращения дублирования выбранного и удаления ненужных элементов поможет оптимизировать ввод данных и повысить точность в ваших электронных таблицах Excel.
- Как создать выпадающий список с несколькими значениями
- Шаг 1. Создание обычного выпадающего списка
- Шаг 2. Вставка кода VBA для выбора нескольких значений
- Код VBA для выбора нескольких элементов
- Выпадающий список Excel с выбором нескольких значений без дубликатов
- Несколько значений из выпадающего списка с возможностью удаления элемента
- Как изменить вид разделителя в списке
- Как создать выпадающий список с несколькими значениями в отдельных строках
- Как установить возможность множественного выбора только для определенных столбцов, строк, ячеек
- Как включить возможность выбора нескольких значений на защищенном листе.
Как создать выпадающий список с несколькими значениями
Создание выпадающего списка с множественным выбором в Excel включает два шага:
- Создайте стандартный список проверки данных в одной или нескольких ячейках. Подробные инструкции вы найдете в статье: 5 способов создать выпадающий список в ячейке Excel.
- Вставьте код VBA на лист, где находится ваш выпадающий список.
Вы можете выполнить эти шаги в любом порядке.
Шаг 1. Создание обычного выпадающего списка
Чтобы добавить выпадающий список в Excel, используйте инструмент проверки данных. Шаги немного отличаются в зависимости от того, находятся ли исходные элементы в обычном диапазоне, именованном диапазоне или в таблице Excel.
Подробные инструкции и примеры вы можете найти здесь: Выпадающий список Excel при помощи ДВССЫЛ. Здесь же мы рассмотрим альтернативный метод создать выпадающий список из таблицы Excel, который в рекомендованных вам статьях не рассматривался.
Мы будем использовать таблицу Excel с именем Table1, расположенную в диапазоне A2:A25, как показано на скриншоте ниже. Для создания выпадающего списка с данными из этой таблицы выполните следующие действия:
- Выберите одну или несколько ячеек для создания выпадающего списка (например, D3:D7).
- На вкладке «Данные» в группе «Работа с данными» нажмите кнопку «Проверка данных» и далее выберите «Список».
- В поле «Источник» введите функцию ДВССЫЛ, которая косвенно ссылается на столбец в Table1 с именем «Продукты»:
=ДВССЫЛ("Table1[Продукты]")- Когда закончите, нажмите OK.
В результате получится автоматически расширяемый и обновляемый выпадающий список, который пока по умолчанию позволяет выбрать только один элемент.
Шаг 2. Вставка кода VBA для выбора нескольких значений
Чтобы превратить обычный выпадающий список в список с несколькими значениями, вставьте один из следующих кодов VBA на свой рабочий лист:
- Код VBA для множественного выбора с дубликатами
- Код VBA для выбора нескольких значений без дубликатов
- Код VBA для выпадающего списка с множественным выбором с удалением дублирующихся элементов
Для добавления кода VBA выполните следующие шаги:
- Откройте редактор Visual Basic, нажав Alt + F11.
- На панели VBA Project слева дважды щелкните имя рабочего листа, содержащего ваш выпадающий список. Это откроет окно «Код» для этого листа. Или щелкните правой кнопкой мыши на вкладке с именем листа и выберите «Просмотреть код» в контекстном меню.
- В окне «Код» вставьте код VBA.
- Закройте редактор VBA и сохраните файл как рабочую книгу с поддержкой макросов (.xlsm).
Теперь, когда вы вернетесь к рабочему листу, вы сможете выбрать несколько значений из выпадающего списка с повторами.
Код VBA для выбора нескольких элементов
Пример кода VBA, который позволяет записать в ячейку несколько значений из выпадающего списка, включая возможность несколько раз выбрать одно и то же значение:
Option Explicit
Private Sub Worksheet_Change(ByVal Destination As Range)
Dim DelimiterType As String
Dim rngDropdown As Range
Dim oldValue As String
Dim newValue As String
DelimiterType = ", "
If Destination.Count > 1 Then Exit Sub
On Error Resume Next
Set rngDropdown = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitError
If rngDropdown Is Nothing Then GoTo exitError
If Intersect(Destination, rngDropdown) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newValue = Destination.Value
Application.Undo
oldValue = Destination.Value
Destination.Value = newValue
If oldValue = "" Then
'do nothing
Else
If newValue = "" Then
'do nothing
Else
Destination.Value = oldValue & DelimiterType & newValue
' add new value with delimiter
End If
End If
End If
exitError:
Application.EnableEvents = True
End SubКак работает этот код:
- Код позволяет выбор нескольких значений во всех выпадающих списках на данном листе.
- Код работает только на текущем листе, поэтому добавьте его на каждый лист, где требуется множественный выбор.
- Код позволяет дублировать элементы, то есть выбирать один и тот же элемент несколько раз.
- Выбранные элементы разделены запятой и пробелом. Чтобы изменить разделитель, замените «, » на нужный символ в строке DelimiterType = «, » (строка 7 в коде).
Важное замечание. Один и тот же символ не может использоваться как в качестве разделителя, так и как часть элемента списка. В нашем коде разделитель по умолчанию — это запятая, за которой следует пробел («, «). Поэтому эта комбинация символов не должна использоваться нигде в элементах списка.
Предположим, вы измените разделитель на одиночный пробел (» «). Код будет правильно работать только для элементов, состоящих из одного слова. Если вы будете использовать элементы из нескольких слов с пробелами между ними, то неизбежны ошибки.
Выпадающий список Excel с выбором нескольких значений без дубликатов
При выборе из большого списка пользователи могут случайно выбрать один и тот же элемент несколько раз.
Приведенный ниже код позволяет выбрать несколько значений из выпадающего списка, но добавлять каждый элемент только один раз. Если элемент выбран повторно, он не будет добавлен дважды.
Option Explicit
Private Sub Worksheet_Change(ByVal Destination As Range)
Dim rngDropdown As Range
Dim oldValue As String
Dim newValue As String
Dim DelimiterType As String
DelimiterType = ", "
If Destination.Count > 1 Then Exit Sub
On Error Resume Next
Set rngDropdown = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitError
If rngDropdown Is Nothing Then GoTo exitError
If Intersect(Destination, rngDropdown) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newValue = Destination.Value
Application.Undo
oldValue = Destination.Value
Destination.Value = newValue
If oldValue <> "" Then
If newValue <> "" Then
If oldValue = newValue Or _
InStr(1, oldValue, DelimiterType & newValue) Or _
InStr(1, oldValue, newValue & Replace(DelimiterType, " ", "")) Then
Destination.Value = oldValue
Else
Destination.Value = oldValue & DelimiterType & newValue
End If
End If
End If
End If
exitError:
Application.EnableEvents = True
End SubНесколько значений из выпадающего списка с возможностью удаления элемента
При необходимости пользователи могут удалить ненужные или ошибочно выбранные элементы.
Рассмотрим сценарий, когда нужно создать перечень из нескольких товарных позиций в одной ячейке, как в нашем примере с ингредиентами для рецепта. Стандартный способ удаления неправильного элемента — очистить всю ячейку и начать заново. Однако при помощи этого кода если вы выберете какой-либо элемент повторно, то он будет удалён из ячейки.
Option Explicit
Private Sub Worksheet_Change(ByVal Destination As Range)
Dim rngDropdown As Range
Dim oldValue As String
Dim newValue As String
Dim DelimiterType As String
DelimiterType = ", "
Dim DelimiterCount As Integer
Dim TargetType As Integer
Dim i As Integer
Dim arr() As String
If Destination.Count > 1 Then Exit Sub
On Error Resume Next
Set rngDropdown = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitError
If rngDropdown Is Nothing Then GoTo exitError
TargetType = 0
TargetType = Destination.Validation.Type
If TargetType = 3 Then ' is validation type is "list"
Application.ScreenUpdating = False
Application.EnableEvents = False
newValue = Destination.Value
Application.Undo
oldValue = Destination.Value
Destination.Value = newValue
If oldValue <> "" Then
If newValue <> "" Then
If oldValue = newValue Or oldValue = newValue & Replace(DelimiterType, " ", "") Or oldValue = newValue & DelimiterType Then ' leave the value if there is only one in the list
oldValue = Replace(oldValue, DelimiterType, "")
oldValue = Replace(oldValue, Replace(DelimiterType, " ", ""), "")
Destination.Value = oldValue
ElseIf InStr(1, oldValue, DelimiterType & newValue) Or InStr(1, oldValue, " " & newValue & DelimiterType) Then
arr = Split(oldValue, DelimiterType)
If Not IsError(Application.Match(newValue, arr, 0)) = 0 Then
Destination.Value = oldValue & DelimiterType & newValue
Else:
Destination.Value = ""
For i = 0 To UBound(arr)
If arr(i) <> newValue Then
Destination.Value = Destination.Value & arr(i) & DelimiterType
End If
Next i
Destination.Value = Left(Destination.Value, Len(Destination.Value) - Len(DelimiterType))
End If
ElseIf InStr(1, oldValue, newValue & Replace(DelimiterType, " ", "")) Then
oldValue = Replace(oldValue, newValue, "")
Destination.Value = oldValue
Else
Destination.Value = oldValue & DelimiterType & newValue
End If
Destination.Value = Replace(Destination.Value, Replace(DelimiterType, " ", "") & Replace(DelimiterType, " ", ""), Replace(DelimiterType, " ", "")) ' remove extra commas and spaces
Destination.Value = Replace(Destination.Value, DelimiterType & Replace(DelimiterType, " ", ""), Replace(DelimiterType, " ", ""))
If Destination.Value <> "" Then
If Right(Destination.Value, 2) = DelimiterType Then ' remove delimiter at the end
Destination.Value = Left(Destination.Value, Len(Destination.Value) - 2)
End If
End If
If InStr(1, Destination.Value, DelimiterType) = 1 Then ' remove delimiter as first characters
Destination.Value = Replace(Destination.Value, DelimiterType, "", 1, 1)
End If
If InStr(1, Destination.Value, Replace(DelimiterType, " ", "")) = 1 Then
Destination.Value = Replace(Destination.Value, Replace(DelimiterType, " ", ""), "", 1, 1)
End If
DelimiterCount = 0
For i = 1 To Len(Destination.Value)
If InStr(i, Destination.Value, Replace(DelimiterType, " ", "")) Then
DelimiterCount = DelimiterCount + 1
End If
Next i
If DelimiterCount = 1 Then ' remove delimiter if last character
Destination.Value = Replace(Destination.Value, DelimiterType, "")
Destination.Value = Replace(Destination.Value, Replace(DelimiterType, " ", ""), "")
End If
End If
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End If
exitError:
Application.EnableEvents = True
End Sub
Как изменить вид разделителя в списке
Символ, разделяющий элементы, установлен в параметре DelimiterType. По умолчанию используется запятая c пробелом («, «). Этот параметр находится в строке 7 кода. Чтобы изменить разделитель, замените «, » на нужный символ или группу символов.
Например:
- Чтобы разделить выбранные элементы пробелом, DelimiterType = » «.
- Чтобы отделить при помощи точки с запятой — DelimiterType = «; » или DelimiterType = «;» (с пробелом или без него).
- Чтобы разделить несколько значений из выпадающего списка вертикальной чертой, примените DelimiterType = » | «.
Например:
Как создать выпадающий список с несколькими значениями в отдельных строках
Чтобы записать выбранные значения в несколько строк внутри ячейки, установитезначение DelimiterType – vbCrlf. В VBA это означает возврат каретки и перевод строки.
Итак, вы заменяете эту строку кода:
DelimiterType = «,»
на это:
DelimiterType = vbCrLf
В результате каждой выбранный элемент будет размещён в новой строке:
Как установить возможность множественного выбора только для определенных столбцов, строк, ячеек
Все коды VBA, описанные в этом руководстве, работают на всем листе, в котором они размещены. Тем не менее, вы можете легко модифицировать любой из кодов, чтобы он применялся только к определенным ячейкам, строкам или столбцам по мере необходимости.
Для этого найдите эту строку кода:
If rngDropdown Is Nothing Then GoTo exitError
Добавьте сразу после него ещё кусочек кода, в котором укажете, в каком месте рабочего листа можно разрешить несколько вариантов выбора.
Выпадающий список множественного выбора для конкретных столбцов.
Чтобы разрешить выбор нескольких элементов в определенном столбце, добавьте этот код:
If Not Destination.Column = 4 Then GoTo exitError
Где «4» — это номер целевого столбца. В данном случае раскрывающийся список с возможностьювыбора нескольких значений будет активен только в столбце D (четвертый по счёту столбец). Во всех других столбцах выпадающий список будет работать как обычно.
Чтобы разрешить выбор нескольких значений в нескольких столбцах, укажите все их номера, используя такой код:
If Destination.Column <> 4 And Destination.Column <> 6 Then GoTo exitError
В этом случае выпадающий список с несколькими значениями будет доступен в столбцах D (4) и F (6).
Выпадающий список с выбором нескольких значений только для определенных строк
Чтобы разрешить выбор нескольких значений только в определенных строках, используйте этот код:
If Not Destination.Row = 3 Then GoTo exitError
В этом примере замените число «3» номером строки, в которой вы хотите разрешить выпадающему списку выбирать несколько значений.
Чтобы указать такую возможность сразу для нескольких строк, измените код следующим образом:
If Destination.Row <> 3 And Destination.Row <> 6 Then GoTo exitError
Где «3» и «6» — это строки, в которых разрешено выбирать несколько значений из выпадающего списка.
Выбор нескольких значений только в конкретных ячейках.
Чтобы включить мультивыбор исключительно в конкретных ячейках, добавьте одну из приведенных ниже строк кода.
Для одной ячейки:
If Not Destination.Address = «$D$3» Then GoTo exitError
Для нескольких ячеек:
If Destination.Address <> «$D$3» And Destination.Address <> «$F$6» Then GoTo exitError
Конечно, не забудьте заменить «$D$3» и «$F$6» реальными адресами ваших ячеек.
Как включить возможность выбора нескольких значений на защищенном листе.
Чтобы получить выпадающий список с множественным выбором при работе на защищенном паролем листе, вам нужно добавить еще небольшой код после основного кода.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ActiveSheet.Unprotect password:="password"
On Error GoTo exitError2
If Target.Validation.Type = 3 Then
Else
ActiveSheet.Protect password:="password"
End If
Done:
Exit Sub
exitError2:
ActiveSheet.Protect password:="password"
End SubЗамените слово “password” на ваш реальный пароль.
При выборе ячейки программа сама определит, что в ячейке находится выпадающий список, и тогда разрешит редактирование в этой ячейке. Во всех других ячейках любое изменение будет запрещено.
Примечание. Пожалуйста, имейте в виду, что включение вашего реального пароля в код макроса VBA повышает риск безопасности. Ведь теперь его можно просто посмотреть в коде. Чтобы обеспечить безопасность вашей книги, храните ее в месте, защищенном от несанкционированного доступа.
Итак, мы научились делать выпадающий список Excel и выбор нескольких значений из него. Добавление этой потрясающей функции в ваши электронные таблицы повысит точность ввода данных и поможет вам быстрее выполнять свою работу!
Надеюсь эти статьи также вам будут полезны: