Перейти к основному содержанию
 

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

Автор: Силувия Последнее изменение: 2024 июля 03 г.

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

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

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

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

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

Чтобы разрешить множественный выбор в раскрывающемся списке, вы можете использовать Visual Basic для приложений (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

Результат

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

Заметки:
Приведенный выше код VBA:
  • Применяется ко всем раскрывающимся спискам проверки данных на текущем листе, как существующим, так и созданным в будущем.
  • Не позволяет вам выбирать один и тот же элемент более одного раза в каждом раскрывающемся списке.
  • В качестве разделителя выбранных элементов используется запятая. Чтобы использовать другие разделители, пожалуйста, просмотрите этот раздел, чтобы изменить разделитель.

Использование Kutools for Excel за несколько кликов

Если вам не нравится VBA, есть более простая альтернатива: Kutools for Excel's Раскрывающийся список с множественным выбором особенность. Этот удобный инструмент упрощает включение нескольких вариантов выбора в раскрывающихся списках, позволяя вам легко настроить разделитель и управлять дубликатами в соответствии с вашими различными потребностями.

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

  1. Укажите диапазон, содержащий раскрывающийся список, из которого необходимо выбрать несколько элементов.
  2. Укажите разделитель для выбранных элементов в ячейке раскрывающегося списка.
  3. Нажмите 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 = "; "
Примечание. Чтобы изменить разделитель на символ новой строки в этих кодах VBA, измените эту строку на:
delimiter = vbNewLine

Установка указанного диапазона

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

Как вы можете видеть, все приведенные выше коды VBA содержат следующую строку:

Set TargetRange = Me.UsedRange

Вам просто нужно изменить строку на:

Set TargetRange = Me.Range("C2:C10")
Внимание: Вот 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
Внимание: В коде обязательно замените «ваш пароль" в линии pswd = "ВашПароль" с фактическим паролем, который вы используете для защиты листа. Например, если ваш пароль «abc123", то строка должна быть pswd = "abc123".

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

Лучшие инструменты для офисной работы

🤖 Kutools AI Помощник: Революционный анализ данных на основе: Интеллектуальное исполнение   |  Генерировать код  |  Создание пользовательских формул  |  Анализ данных и создание диаграмм  |  Вызов функций Kutools...
Популярные опции: Найдите, выделите или определите дубликаты   |  Удалить пустые строки   |  Объедините столбцы или ячейки без потери данных   |   Раунд без формулы ...
Супер поиск: Множественный критерий VLookup    VLookup с несколькими значениями  |   VLookup по нескольким листам   |   Нечеткий поиск ....
Расширенный раскрывающийся список: Быстрое создание раскрывающегося списка   |  Зависимый раскрывающийся список   |  Выпадающий список с множественным выбором ....
Менеджер столбцов: Добавить определенное количество столбцов  |  Переместить столбцы  |  Переключить статус видимости скрытых столбцов  |  Сравнить диапазоны и столбцы ...
Рекомендуемые функции: Сетка Фокус   |  Просмотр дизайна   |   Большой Формулный Бар    Менеджер книг и листов   |  Библиотека ресурсов (Авто текст)   |  Выбор даты   |  Комбинировать листы   |  Шифровать/дешифровать ячейки    Отправлять электронные письма по списку   |  Суперфильтр   |   Специальный фильтр (фильтровать жирным шрифтом/курсивом/зачеркиванием...) ...
15 лучших наборов инструментов12 Текст Инструменты (Добавить текст, Удалить символы, ...)   |   50+ График Тип (Диаграмма Ганта, ...)   |   40+ Практических Формулы (Рассчитать возраст по дню рождения, ...)   |   19 Вносимые Инструменты (Вставить QR-код, Вставить изображение из пути, ...)   |   12 Конверсия Инструменты (Числа в слова, Конверсия валюты, ...)   |   7 Слияние и разделение Инструменты (Расширенные ряды комбинирования, Разделить клетки, ...)   |   ... и более

Улучшите свои навыки работы с Excel с помощью Kutools for Excel и почувствуйте эффективность, как никогда раньше. Kutools for Excel предлагает более 300 расширенных функций для повышения производительности и экономии времени.  Нажмите здесь, чтобы получить функцию, которая вам нужна больше всего...


Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!