Перейти до основного матеріалу

Виберіть кілька елементів у розкривному списку Excel – повний посібник

Розкривні списки 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 для Excel у кілька кліків

Якщо ви не влаштовуєте VBA, є простіша альтернатива Kutools для 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 Aide: Революціонізуйте аналіз даних на основі: Інтелектуальне виконання   |  Згенерувати код  |  Створення спеціальних формул  |  Аналізуйте дані та створюйте діаграми  |  Викликати функції Kutools...
Популярні функції: Знайдіть, виділіть або визначте дублікати   |  Видалити порожні рядки   |  Об’єднайте стовпці або клітинки без втрати даних   |   Раунд без Формули ...
Супер пошук: VLookup за кількома критеріями    Багатозначний VLookup  |   VLookup на кількох аркушах   |   Нечіткий пошук ....
Розширений розкривний список: Швидке створення випадаючого списку   |  Залежний спадний список   |  Виберіть розкривний список, що вибирається ....
Менеджер колонок: Додайте конкретну кількість стовпців  |  Перемістити стовпці  |  Перемкнути статус видимості прихованих стовпців  |  Порівняйте діапазони та стовпці ...
Особливості: Фокус сітки   |  Перегляд дизайну   |   Велика панель формул    Диспетчер робочих книг і аркушів   |  Бібліотека ресурсів (автотекст)   |  Вибір дати   |  Об’єднайте робочі аркуші   |  Шифрування/розшифрування клітинок    Надсилайте листи за списком   |  Супер фільтр   |   Спеціальний фільтр (фільтр жирний/курсив/закреслений...) ...
Топ-15 наборів інструментів12 текст Tools (додати текст, Видалити символи, ...)   |   50 + Графік типи (діаграма Ганта, ...)   |   40+ Практичний Формули (Розрахуйте вік на основі дня народження, ...)   |   19 вставка Tools (Вставте QR-код, Вставити зображення зі шляху, ...)   |   12 Перетворення Tools (Числа до слів, Валютна конверсія, ...)   |   7 Злиття та розділення Tools (Розширені комбіновані ряди, Розділені клітини, ...)   |   ... і більше

Покращуйте свої навички Excel за допомогою Kutools для Excel і відчуйте ефективність, як ніколи раніше. Kutools для Excel пропонує понад 300 додаткових функцій для підвищення продуктивності та економії часу.  Натисніть тут, щоб отримати функцію, яка вам найбільше потрібна...

Опис


Вкладка Office Передає інтерфейс із вкладками в Office і значно полегшує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!