Коли ви шукаєте "розкривний список 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 : цей код працює для всіх розкривних списків на поточному аркуші. Просто клацніть клітинку, що містить розкривний список, виберіть елементи один за одним із розкривного списку, щоб перевірити, чи працює це.

примітки: Якщо ви хочете дозволити кілька варіантів вибору в розкривному списку та видалити наявні елементи під час повторного вибору зі спадного списку, ви також можете застосувати код VBA, щоб досягти: Клацніть тут, щоб крок за кроком слідувати посібнику

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

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

Як ви бачите на наведеному вище GIF-зображенні, усю операцію можна виконати лише кількома кліками. Припустімо, що ви вже вставили розкривний список перевірки даних у свій аркуш, тепер вам потрібно лише:

Підказки: після завершення налаштування, коли ви клацаєте комірку, що містить розкривний список, список із «+"І"-Праворуч відображатимуться знаки. Просто натисніть "+", щоб додати відповідний елемент до клітинки, і натисніть кнопку "-", щоб видалити його з комірки.

Натисніть тут щоб дізнатися більше про цю функцію, або завантажити 30-денну безкоштовну пробну версію.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Tips : Щоб повернути дані точно такого ж формату з інших стовпців, потрібно змінити формат клітинок результату до або після наступних операцій. У цьому прикладі я змінюю формат клітинки C11 до Дата форматувати та змінювати формат клітинки C14 до Валюта форматувати заздалегідь.

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),””)

Примітки:

Візьмемо першу формулу =ЯКЩОПОМИЛКА(ВПР(B1;B3:F6,2;XNUMX;ЛОЖЬ),””) як приклад,

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