Три типи розкривних списків із кількома стовпцями – покрокова інструкція
Коли ви шукаєте "розкривний список Excel із кількома стовпцями» у Google, вам може знадобитися виконати одне з таких завдань:
Створіть залежний розкривний список
Метод А: Використання формул
Метод В: Лише кілька кліків за допомогою Kutools для Excel
Відображення кількох варіантів у розкривному списку
Метод А: Використання сценарію VBA
Метод В: Лише кілька кліків за допомогою Kutools для Excel
У цьому посібнику ми крок за кроком продемонструємо, як виконати ці три завдання.
Схожі відео
Створіть залежний розкривний список на основі кількох стовпців
Як показано на зображенні 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, перейдіть до Кутулс вкладка, виберіть Випадаючий список > Вибірний список, що випадає. Потім налаштуйте наступним чином.
- Укажіть діапазон, що містить розкривний список, з якого потрібно вибрати кілька елементів.
- У комірці розкривного списку вкажіть роздільник для виділених елементів.
- Натисніть OK , щоб завершити налаштування.
Результат
Тепер, коли ви клацаєте клітинку з розкривним списком у вказаному діапазоні, поруч із нею з’являється поле зі списком. Просто натисніть кнопку «+» поруч із елементами, щоб додати їх до комірки, що розкривається, і натисніть кнопку «-», щоб видалити елементи, які вам більше не потрібні. Дивіться демонстрацію нижче:
- Перевірте Перенесення тексту після вставки розділювача якщо ви хочете відобразити вибрані елементи вертикально в клітинці. Якщо ви віддаєте перевагу горизонтальному списку, залиште цей параметр не позначеним.
- Перевірте Увімкніть пошук якщо ви хочете додати рядок пошуку до розкривного списку.
- Щоб застосувати цю функцію, будь ласка завантажте та встановіть Kutools для Excel перший.
Відображення кількох стовпців у розкривному списку
Як показано на знімку екрана нижче, цей розділ покаже вам, як відобразити кілька стовпців у розкривному списку.
За замовчуванням розкривний список перевірки даних відображає лише один стовпець елементів. Щоб відобразити кілька стовпців у розкривному списку, ми рекомендуємо використовувати поле зі списком (елемент керування 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),"")
Статті по темі
Автозаповнення під час набору тексту у випадаючому списку Excel
Якщо у вас є розкривний список перевірки даних із великими значеннями, вам потрібно прокрутити список унизу, лише щоб знайти правильний, або ввести ціле слово безпосередньо у поле списку. Якщо є спосіб дозволити автозаповнення під час введення першої літери у випадаючому списку, все стане простіше. Цей посібник пропонує метод вирішення проблеми.
Створіть випадаючий список з іншої книги в Excel
Створити випадаючий список перевірки даних серед робочих аркушів у книзі досить просто. Але якщо дані списку, необхідні для перевірки даних, знаходяться в іншій книзі, що б ви зробили? У цьому посібнику ви дізнаєтесь, як детально створити випадаючий список з іншої книги в Excel.
Створіть розкривний список для пошуку в Excel
Для випадаючого списку з численними значеннями пошук правильного - непроста робота. Раніше ми запровадили метод автоматичного заповнення випадаючого списку при введенні першої літери у випадаючому вікні. Окрім функції автозавершення, ви також можете зробити пошук у розкривному списку для підвищення ефективності роботи при пошуку належних значень у розкривному списку. Щоб зробити пошук у розкривному списку спробним, скористайтеся методом у цьому посібнику.
Автоматично заповнювати інші комірки під час вибору значень у спадному списку Excel
Скажімо, ви створили випадаючий список на основі значень у діапазоні комірок B8: B14. Вибираючи будь-яке значення зі спадного списку, ви хочете, щоб відповідні значення в діапазоні комірок C8: C14 автоматично заповнювались у вибраній комірці. Для вирішення проблеми методи з цього посібника допоможуть вам.
Найкращі інструменти продуктивності офісу
Покращуйте свої навички Excel за допомогою Kutools для Excel і відчуйте ефективність, як ніколи раніше. Kutools для Excel пропонує понад 300 додаткових функцій для підвищення продуктивності та економії часу. Натисніть тут, щоб отримати функцію, яка вам найбільше потрібна...
Вкладка Office Передає інтерфейс із вкладками в Office і значно полегшує вашу роботу
- Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
- Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
- Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!