Коли ви шукаєте "розкривний список Excel із кількома стовпцями» у Google, вам може знадобитися виконати одне з таких завдань:

У цьому посібнику ми крок за кроком продемонструємо, як виконати ці три завдання.

Схожі відео

Отримати файл зразка:

Клацніть, щоб завантажити зразок файлу

Створіть залежний розкривний список на основі кількох стовпців

Як показано на зображенні GIF нижче, ви хочете створити головний спадний список для континентів, додатковий спадний список, який містить країни на основі континенту, вибраного в основному спадному списку, а потім третій спадний список список, що містить міста на основі країни, вибраної у додатковому спадному списку. Метод у цьому розділі може допомогти вам виконати це завдання.

Використання формул для створення залежного розкривного списку на основі кількох стовпців

Крок 1: Створіть головний спадний список

1. Виберіть клітинки (тут я вибираю G9:G13), куди потрібно вставити розкривний список, перейдіть до дані вкладка, клацніть Перевірка достовірності даних > Перевірка достовірності даних.

2 В Перевірка достовірності даних діалогове вікно, будь ласка, налаштуйте наступним чином.

Крок 2: Створіть вторинний спадний список

1. Виберіть весь діапазон, який містить елементи, які потрібно відобразити, у додатковому розкривному списку. Перейти до Формули вкладку, а потім натисніть кнопку Створити з виділення.

2 В Створіть імена з виділення діалогове вікно, лише перевірити Верхній ряд поле, а потім натисніть кнопку OK кнопки.

3. Виберіть комірку, куди потрібно вставити вторинний розкривний список, перейдіть до дані вкладка, клацніть Перевірка достовірності даних > Перевірка достовірності даних.

4 В Перевірка достовірності даних у діалоговому вікні потрібно:

=INDIRECT(SUBSTITUTE(G9," ","_"))

5. Виберіть цю комірку розкривного списку, перетягніть її Ручка автозаповнення вниз, щоб застосувати його до інших клітинок у тому самому стовпці.

Додатковий розкривний список завершено. Коли ви вибираєте континент у головному спадному списку, лише країни цього континенту відображаються у додатковому спадному списку.

Крок 3: Створіть третій спадний список

1. Виберіть весь діапазон, який містить значення, які потрібно відобразити, у третьому спадному списку. Перейти до Формули вкладку, а потім натисніть кнопку Створити з виділення.

2 В Створіть імена з виділення діалогове вікно, лише перевірити Верхній ряд поле, а потім натисніть кнопку OK кнопки.

3. Виберіть комірку, куди потрібно вставити третій розкривний список, перейдіть до дані вкладка, клацніть Перевірка достовірності даних > Перевірка достовірності даних.

4 В Перевірка достовірності даних у діалоговому вікні потрібно:

=INDIRECT(SUBSTITUTE(H9," ","_"))

5. Виберіть цю комірку розкривного списку, перетягніть її Ручка автозаповнення вниз, щоб застосувати його до інших клітинок у тому самому стовпці.

Третій розкривний список міст завершено. Коли ви вибираєте країну у додатковому спадному списку, у третьому спадному списку відображаються лише міста цієї країни.

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

Кілька клацань, щоб створити залежний розкривний список на основі кількох стовпців за допомогою Kutools для Excel

Зображення GIF нижче показує кроки Динамічний випадаючий список особливість Kutools для Excel.

Як бачите, всю операцію можна виконати всього за кілька кліків. Вам просто потрібно:

Зображення GIF вище демонструє лише кроки для створення 2-рівневого спадного списку. Якщо ви хочете створити розкривний список із більш ніж 2 рівнями, натисніть тут, щоб дізнатися більше . Або завантажити 30-денну безкоштовну пробну версію.

Зробіть кілька варіантів у розкривному списку в Excel

У цьому розділі наведено два методи, які допоможуть вам зробити кілька варіантів у розкривному списку в Excel.

Використання кодів VBA для кількох варіантів у розкривному списку Excel

Наведений нижче сценарій VBA може допомогти зробити кілька варіантів у розкривному списку в Excel без дублікатів. Будь ласка, зробіть наступне.

Крок 1. Відкрийте редактор коду VBA та скопіюйте код

1. Перейдіть на вкладку аркуша, клацніть його правою кнопкою миші та виберіть Переглянути код з меню, що клацне правою кнопкою миші.

2. Тоді Microsoft Visual Basic для додатків з’явиться вікно, вам потрібно скопіювати наступний код VBA в Аркуш (Код) редактор.

Код VBA: дозволити кілька варіантів вибору в розкривному списку без дублікатів

Private Sub Worksheet_Change(ByVal Target As Range)
    'Updated by Extendoffice 2019/11/13
    Dim xRng As Range
    Dim xValue1 As String
    Dim xValue2 As String
    If Target.Count > 1 Then Exit Sub
    On Error Resume Next
    Set xRng = Cells.SpecialCells(xlCellTypeAllValidation)
    If xRng Is Nothing Then Exit Sub
    Application.EnableEvents = False
    If Not Application.Intersect(Target, xRng) Is Nothing Then
        xValue2 = Target.Value
        Application.Undo
        xValue1 = Target.Value
        Target.Value = xValue2
        If xValue1 <> "" Then
            If xValue2 <> "" Then
                If xValue1 = xValue2 Or _
                   InStr(1, xValue1, ", " & xValue2) Or _
                   InStr(1, xValue1, xValue2 & ",") Then
                    Target.Value = xValue1
                Else
                    Target.Value = xValue1 & ", " & xValue2
                End If
            End If
        End If
    End If
    Application.EnableEvents = True
End Sub

Крок 2. Перевірте код

Після вставлення коду натисніть інший + Q клавіші, щоб закрити Візуальний редактор і поверніться до аркуша.

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

Кілька клацань, щоб зробити кілька виборів у розкривному списку Excel за допомогою Kutools для Excel

Код VBA має багато обмежень. Якщо ви не знайомі зі сценарієм VBA, важко змінити код відповідно до ваших потреб. Ось рекомендована потужна функція - Розкривний список із кількома виборами що може допомогти вам легко вибрати кілька елементів із розкривного списку.

після встановлення Kutools для Excel, перейдіть до Кутулс вкладка, виберіть Випадаючий список > Вибірний список, що випадає. Потім налаштуйте наступним чином.

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

Результат

Тепер, коли ви клацаєте клітинку з розкривним списком у вказаному діапазоні, поруч із нею з’являється поле зі списком. Просто натисніть кнопку «+» поруч із елементами, щоб додати їх до комірки, що розкривається, і натисніть кнопку «-», щоб видалити елементи, які вам більше не потрібні. Дивіться демонстрацію нижче:

Відображення кількох стовпців у розкривному списку

Як показано на знімку екрана нижче, цей розділ покаже вам, як відобразити кілька стовпців у розкривному списку.

За замовчуванням розкривний список перевірки даних відображає лише один стовпець елементів. Щоб відобразити кілька стовпців у розкривному списку, ми рекомендуємо використовувати поле зі списком (елемент керування ActiveX) замість розкривного списку перевірки даних.

Крок 1. Вставте поле зі списком (елемент керування ActiveX)

1. До Розробник вкладка, клацніть Insert > Combo Box (управління ActiveX).

Порада: Якщо Розробник вкладка не відображається на стрічці, ви можете виконати кроки в цьому підручнику "Показати вкладку розробника”, щоб показати це.

2. Потім намалюйте a Combo Box у клітинці, де потрібно відобразити розкривний список.

Крок 2: Змініть властивості поля зі списком

1. Клацніть правою кнопкою миші поле зі списком і виберіть властивості з контекстного меню.

2 В властивості діалогове вікно, будь ласка, налаштуйте наступним чином.

Крок 3: відобразіть вказані стовпці у розкривному списку

1 Під Розробник вкладку, вимкніть Режим дизайну просто натиснувши на Режим дизайну значок.

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

Крок 4. Показуйте елементи з інших стовпців у певних клітинках

1. Виберіть клітинку під полем зі списком, введіть формулу нижче та натисніть Що натомість? Створіть віртуальну версію себе у щоб отримати значення другого стовпця в тому ж рядку.

=IFERROR(VLOOKUP(B1,B3:F6,2,FALSE),"")

2. Щоб отримати значення третього, четвертого та п’ятого стовпців, почергово застосуйте наступні формули.

=IFERROR(VLOOKUP(B1,B3:F6,3,FALSE),"")

=IFERROR(VLOOKUP(B1,B3:F6,4,FALSE),"")

=IFERROR(VLOOKUP(B1,B3:F6,5,FALSE),"")

Статті по темі