Выпадающий список с выбором нескольких значений

В статье показано, как создать выпадающий список в Excel, который позволяет пользователям выбирать несколько значений с дубликатами или без них. Также рассмотрим, как разрешить множественный выбор только в отдельных строках, столбцах или ячейках, как это может работать на защищенном листе, как добавить возможность поиска в выпадающий список.

Excel прошел долгий путь с момента своего создания и вводит все больше и больше полезных функций с каждым новым выпуском. В Excel 365 они добавили возможность поиска в списках проверки данных, что значительно экономит время при работе с большими наборами данных. Тем не менее даже с этой новой опцией, Excel по-прежнему позволяет по умолчанию выбрать только один элемент из предопределенного списка значений. Но все же у нас есть решение. Используя VBA, вы можете создавать выпадающий список с множественным выбором. Благодаря возможности предотвращения дублирования и удаления нежелательных или ошибочных элементов эта функция может оптимизировать ввод данных и повысить точность в ваших электронных таблицах Excel.

Как сделать выпадающий список с выбором нескольких значений.

Создание выпадающего списка с множественным выбором в Excel состоит из двух шагов:

1. Во-первых, вы создаете на рабочем листе стандартный список проверки данных в одной или в нескольких ячейках. Подробно эти действия описаны в этой статье: 5 способов создать выпадающий список в ячейке Excel.
2. А затем вставьте код VBA на этот лист.

Шаги эти можно выполнить и в обратном порядке :)

Как создать обычный выпадающий список.

Чтобы вставить раскрывающийся список в Excel, используйте инструмент проверки данных. Шаги немного варьируются в зависимости от того, находятся ли исходные элементы в обычном диапазоне, именованном диапазоне или же в таблице Excel.

По моему опыту, лучшим вариантом является создание списка проверки данных из таблицы. Поскольку таблицы Excel по своей природе являются динамическими, связанный с ними раскрывающийся список будет автоматически расширяться или сокращаться при добавлении или удалении элементов в таблицу.

В этом примере мы будем использовать таблицу с простым именем Table1, которая находится в A2:A25 на скриншоте ниже. Чтобы составить выпадающий список с данными из этой таблицы, выполните следующие действия:

1. Выберите одну или несколько ячеек для создания в них выпадающего списка (D3:D7 в нашем случае).
2. На вкладке "Данные" в группе "Работа с данными" нажмите кнопку "Проверка данных".
3. В раскрывающемся списке "Проверка вводимых значений" выберите "Список".
4. В поле "Источник" введите формулу, которая косвенно ссылается на столбец Table1 с именем Продукты.

=ДВССЫЛ("Table1[Продукты]")

5. Когда закончите, нажмите OK.

Результатом станет автоматически расширяемый и автоматически обновляемый раскрывающийся список, который по умолчанию позволяет выбрать только один элемент.

Для получения дополнительной информации о сделанных нами шагах см. Как создать список проверки данных Excel.

Теперь вставьте на лист код VBA, чтобы разрешить несколько вариантов выбора в выпадающем списке.

Это основная часть процесса. Чтобы превратить обычный список выбора одного значения в раскрывающийся список с возможностью нескольких выборов, вам необходимо вставить один из этих кодов на ваш рабочий лист:

• VBA-код для множественного выбора с дубликатами
• Код VBA для выбора нескольких значений без дубликатов
• Код VBA для выпадающего списка с множественным выбором с удалением дублирующихсяэлементов

Чтобы добавить код VBA в свой рабочий лист, выполните следующие действия:

1. Откройте редактор Visual Basic, нажав ALT + F11.
2. На панели VBA Project слева дважды щелкните имя рабочего листа, содержащего ваш раскрывающийся список. Это откроет окно "Код" для этого листа.

Или вы можете щелкнуть правой кнопкой мыши на вкладке с именем листа и выбрать "Просмотреть код" в контекстном меню. Это сразу откроет окно кода для данного листа.

3. В окне "Код" вставьте код VBA.
4. Закройте редактор 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 с выбором нескольких значений без дубликатов

При выборе из большого списка пользователи иногда могут случайно выбрать один и тот же элемент более одного раза, не заметив этого. Приведенный ниже код решает проблему дубликатов в выпадающем списке проверки данных с выбором нескольких значений. Он позволяет пользователям добавить каждый элемент списка только один раз. Если вы попытаетесь выбрать тот же элемент еще раз, то просто ничего не произойдет. Довольно удобно, верно?

Код VBA для создания выпадающего спуска с несколькими выборами без дубликатов:

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

Выпадающий список с множественным выбором и возможностью удаления элемента.

Когда пользователям нужно выбрать несколько значений из перечня, они могут ошибиться или передумать в процессе ввода. Здесь как раз будет полезен выпадающий список с выбором нескольких значений, который при этом позволяет удалять ненужные или ошибочные элементы.

Рассмотрите сценарий, в котором вам нужно создать набор из нескольких товарных позиций и записать его в одну ячейку. Как в нашем примере – ингридиенты для рецепта. Стандартно единственный способ удалить неправильно выбранный продукт – это очистить все содержимое ячейки и начать все сначала. Благодаря возможности удалять отдельные элементы из уже созданного списка, вы можете легко изменять его без путаницы или ошибок.

Приведенный ниже код реализует функциональность удаления элемента простым и интуитивно понятным способом: первый выбор элемента добавляет его в список, а повторный выбор этого же элемента удаляет его из списка.

Код VBA для выпадающего списка с выбором нескольких значений, который позволяет удалять отдельные элементы.

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

Чуть ниже вы можете видеть, как в Excel работает раскрывающийся список с возможностью удаления. Пользователи могут выбрать несколько вариантов из списка проверки данных и внести коррективы на лету. Оптимизированный и эффективный подход к управлению выбором!

Как изменить вид разделителя в списке.

Символ, который разделяет элементы в выделении, установлен в параметре DelimiterType. Во всех вариантах макроса значением по умолчанию этого параметра является ", " (запятая и пробел). Онрасположен в строке 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 повышает риск безопасности. Ведь теперь его можно просто посмотреть в коде. Чтобы обеспечить безопасность вашей книги, храните ее в месте, защищенном от несанкционированного доступа или использования.

Есть ли возможность поиска в выпадающем списке?

Если ваш выпадающий список содержит десятки значений, то найти среди них нужное займет у вас приличное количество времени. Ведь нужно иногда пролистать почти весь список! 

Есть очень приятная опция – в раскрывающемся списке с возможностью выбора нескольких значений вы можете еще получить поиск по списку. Вы открываете список, вводите первые буквы нужного вам слова, и получаете отфильтрованный по этим буквам список значений. 

Как это сделать? Часто рекомендуют использовать код VBA, но можно прекрасно обойтись и без этого.

Если вы строите раскрывающийся список на основе столбца таблицы Excel, то вы автоматически получаете возможность такого поиска.

А что, если по каким-то причинам вам неудобно использовать таблицу Excel как источник данных для списка? Тогда вы можете использовать именованный диапазон.

Вот краткая пошаговая инструкция, как создать выпадающий список с поиском.

1. Введите данные в столбец Excel. Например, если вы хотите создать раскрывающийся список продуктов с возможностью поиска, введите названия продуктов в один столбец и соответствующие им коды в другой столбец.

2. Выберите столбец данных, а затем нажмите «Формулы» на ленте в верхней части экрана. Тамнажмите кнопку «Задать имя» и дайте именованному диапазону имя.

3. Выберите ячейку, в которой вы хотите, чтобы отображался выпадающий список, а затем нажмите «Данные» на ленте в верхней части экрана. Там нажмите «Проверка данных».

4. В диалоговом окне «Проверка данных» выберите «Список» в качестве критериев проверки. В поле «Источник» введите именованный диапазон, созданный на шаге 2.

5. Установите флажки рядом с «Раскрывающийся список в ячейке» и «Игнорировать пустые». Нажмите «ОК», чтобы закрыть диалоговое окно.

6. Теперь, когда вы нажмете на ячейку со стрелочкой, вы увидите список всех элементов в вашем именованном диапазоне. Вы можете начать вводить несколько букв для поиска определенного элемента, и Excel соответствующим образом отфильтрует список. 

Более подробно создание выпадающего списка мы ранее рассмотрели в специальной статье о способах его создания, ссылку на которую вы можете найти в конце этого руководства.

Кстати, если вы сделаете в своей рабочей книге не обычный, а несколько многоуровневых зависимых выпадающих списков, то множественный выбор в них также будет работать.

Надеюсь, это поможет! Дайте мне знать, если у вас возникнут дополнительные вопросы.

Итак, мы научились создавать выпадающий список Excel с возможностью выбора нескольких значений. Добавление этой потрясающей функции в ваши электронные таблицы повысит точность ввода данных и поможет вам быстрее выполнять свою работу!

Как сделать зависимый выпадающий список в Excel?

Создаем динамический выпадающий список в Excel при помощи формул

5 способов сделать выпадающий список в ячейке Excel

Как проверить правильность ввода данных в Excel?

Как создать пользовательскую функцию?

Почему пользовательская функция не работает: проблемы и решения

Пользовательская функция и макрос VBA: преимущества и недостатки

Как выбрать определенные строки и столбцы