Выбор нескольких элементов в раскрывающемся списке Excel Раскрывающийся список – полное руководство

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

• Использование кода VBA
• Использование Kutools для Excel всего за несколько кликов

• Разрешение дублирования элементов
• Удаление любых существующих элементов
• Установка пользовательского разделителя
• Установка Ограниченный диапазон
• Выполнение в защищённом листе

Включение множественного выбора в Раскрывающийся список

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

С использованием кода VBA

Чтобы включить множественный выбор в раскрывающемся списке, воспользуйтесь Visual Basic for Applications (VBA) в Excel. С помощью скрипта можно изменить поведение стандартного раскрывающегося списка, превратив его в список с поддержкой множественного выбора. Выполните следующие действия.

Шаг 1: Откройте редактор листа (кода)

1. Откройте лист, содержащий раскрывающийся список, для которого нужно включить множественный выбор.
2. Щёлкните правой кнопкой мыши по ярлыку листа и выберите в контекстном меню пункт «Просмотреть код».
   

Шаг 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

Результат

Когда вы вернётесь на лист, раскрывающийся список позволит выбирать несколько вариантов. См. демонстрацию ниже:

С помощью Kutools для Excel за несколько кликов

Если вы не знакомы с VBA, гораздо проще воспользоваться функцией «Создать выпадающий список с множественным выбором» из набора «Kutools для Excel». Этот удобный инструмент легко добавляет поддержку множественного выбора в раскрывающиеся списки и позволяет гибко настраивать разделитель элементов и управление дубликатами — всё в соответствии с вашими потребностями.

После установки Kutools для Excel перейдите на вкладку «Kutools», выберите «Раскрывающийся список» > «Создать выпадающий список с множественным выбором», а затем настройте его следующим образом.

1. Укажите диапазон, содержащий раскрывающийся список, из которого нужно выбрать несколько элементов.
2. Укажите разделитель для количества выбранных элементов в ячейке с раскрывающимся списком.
3. Нажмите «OK», чтобы завершить настройку.
   

Результат

Теперь при щелчке по ячейке с раскрывающимся списком в ограниченном диапазоне рядом с ней появится список. Просто нажмите кнопку «+» рядом с нужными элементами, чтобы добавить их в ячейку раскрывающегося списка, или кнопку «–», чтобы удалить ненужные элементы. См. демонстрацию ниже:

Дополнительные операции для многоэлементного выбора в Раскрывающийся список

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

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

При включении множественного выбора в раскрывающемся списке дубликаты могут стать проблемой. Приведённый выше код 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 есть следующая строка:

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 здесь.

Связанные статьи