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

Три типи розкривних списків із кількома стовпцями – покрокова інструкція


Схожі відео


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

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

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

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

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

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

1) Клацніть на Налаштування вкладка;
2) Виберіть список в дозволяти випадаючий список;
3) Натисніть у Source виберіть комірки, що містять континенти, які потрібно відобразити у розкривному списку;
4) Клацніть на OK кнопку. Дивіться знімок екрана:

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

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

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

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

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

1) Залишайтеся в Налаштування вкладка;
2) Виберіть список в дозволяти випадаючий список;
3) Введіть наступну формулу в Source коробка
=INDIRECT(SUBSTITUTE(G9," ","_"))
де G9 є першою коміркою головного розкривного списку.
4.4) Клацніть на OK кнопки.

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

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

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

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

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

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

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

1) Залишайтеся в Налаштування вкладка;
2) Виберіть список в дозволяти випадаючий список;
3) Введіть наступну формулу в Source коробка
=INDIRECT(SUBSTITUTE(H9," ","_"))
де H9 є першою клітинкою другорядного розкривного списку.
4.4) Клацніть на OK кнопки.

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

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

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

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

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

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

1. Увімкніть функцію;
2. Виберіть потрібний режим: Рівень 2 or Розкривний список 3-5 рівня;
3. Виберіть стовпці, на основі яких потрібно створити залежний розкривний список;
4. Виберіть вихідний діапазон.

Зображення 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. Перевірте код

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

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

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

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

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

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

1. Увімкніть це Вибірний список, що випадає особливість;
2. Вкажіть область (можна вказати діапазон, поточна трудова книжка, поточний аркуш or інший конкретний аркуш поточного робочого зошита на основі ваших потреб);
3. Укажіть роздільник, щоб відокремити кілька виділень і напрямок відображення тексту (по горизонталі or вертикально);

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

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


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

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

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

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

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

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

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

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

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

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

1) У КолонкаКонт у полі введіть число, яке відповідає кількості стовпців, які потрібно відобразити у розкривному списку;
2) У ColumnWidths визначте ширину кожного стовпця. Тут я визначаю ширину кожного стовпця як 80 пт;100 пт;80 пт;80 пт;80 пт;
3) У LinkedCell укажіть клітинку для виведення того самого значення, що й вибране в розкривному списку. Ця комірка буде використана в наступних кроках;
4) У ListFillRange у полі введіть діапазон даних, який потрібно відобразити у розкривному списку.
5) У ListWidth укажіть ширину для всього розкривного списку.
6) Закрийте властивості діалогове вікно.

Крок 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;ЛОЖЬ),””) як приклад,

1) B1 це клітинка, яку ви вказали як LinkedCell у діалоговому вікні «Властивості».
2) Число 2 представляє другий стовпець діапазону таблиці «B3:F6».
3) ВЛООКУП тут функція шукає значення в B1 і повертає значення у другому стовпці діапазону B3:F6.
4) ПОМИЛКА обробляє помилки у функції VLOOKUP. Якщо функція VLOOKUP обчислює помилку #N/A, функція IFERROR поверне помилку як нуль.

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

Автозаповнення під час набору тексту у випадаючому списку Excel
Якщо у вас є розкривний список перевірки даних із великими значеннями, вам потрібно прокрутити список унизу, лише щоб знайти правильний, або ввести ціле слово безпосередньо у поле списку. Якщо є спосіб дозволити автозаповнення під час введення першої літери у випадаючому списку, все стане простіше. Цей посібник пропонує метод вирішення проблеми.

Створіть випадаючий список з іншої книги в Excel
Створити випадаючий список перевірки даних серед робочих аркушів у книзі досить просто. Але якщо дані списку, необхідні для перевірки даних, знаходяться в іншій книзі, що б ви зробили? У цьому посібнику ви дізнаєтесь, як детально створити випадаючий список з іншої книги в Excel.

Створіть розкривний список для пошуку в Excel
Для випадаючого списку з численними значеннями пошук правильного - непроста робота. Раніше ми запровадили метод автоматичного заповнення випадаючого списку при введенні першої літери у випадаючому вікні. Окрім функції автозавершення, ви також можете зробити пошук у розкривному списку для підвищення ефективності роботи при пошуку належних значень у розкривному списку. Щоб зробити пошук у розкривному списку спробним, скористайтеся методом у цьому посібнику.

Автоматично заповнювати інші комірки під час вибору значень у спадному списку Excel
Скажімо, ви створили випадаючий список на основі значень у діапазоні комірок B8: B14. Вибираючи будь-яке значення зі спадного списку, ви хочете, щоб відповідні значення в діапазоні комірок C8: C14 автоматично заповнювались у вибраній комірці. Для вирішення проблеми методи з цього посібника допоможуть вам.

Більше підручника для випадаючого списку ...

Найкращі інструменти продуктивності офісу

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

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

вкладка kte 201905


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

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