Випадаючий список Excel: створення, редагування, видалення та додаткові операції

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

---

Створіть простий розкривний список

Для використання випадаючого списку вам потрібно навчитися створювати його спочатку. Цей розділ містить 6 способів допомогти вам створити розкривний список у програмі Excel.

Створити випадаючий список з діапазону комірок

Тут продемонстровано кроки для створення випадаючого списку з діапазону комірок в Excel. Будь ласка, виконайте наступне

1. Виберіть діапазон комірок для пошуку випадаючого списку.

Tips : Ви можете створити випадаючий список для кількох несуміжних комірок одночасно, утримуючи клавішу Ctrl під час вибору комірок по черзі.

2. клацання дані > Перевірка достовірності даних > Перевірка достовірності даних.

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

примітки:

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

Створити динамічний випадаючий список з таблиці

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

1. Виберіть діапазон вихідних даних, а потім натисніть Ctrl + T ключі.

2. клацання OK в спливаючому Створити таблицю діалогове вікно. Потім діапазон даних перетворюється в таблицю.

3. Виберіть діапазон комірок для розміщення випадаючого списку, а потім натисніть дані > Перевірка достовірності даних > Перевірка достовірності даних.

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

Потім створюються динамічні випадаючі списки. Під час додавання чи видалення даних із діапазону таблиць значення в розкривному списку оновлюватимуться автоматично.

Створіть динамічний випадаючий список із формулами

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

1. Виберіть комірки, куди виводити випадаючі списки.

2. клацання дані > Перевірка достовірності даних > Перевірка достовірності даних.

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

Потім створюються динамічні випадаючі списки. Під час додавання або видалення даних із певного діапазону значення в розкривних списках оновлюються автоматично.

Створити випадаючий список з іменованого діапазону

Ви також можете створити випадаючий список із названого діапазону в Excel.

1. По-перше, створіть іменований діапазон. Виберіть діапазон комірок, на основі якого ви створите іменований діапазон, а потім введіть ім'я діапазону в ІМ'Я і натисніть Що натомість? Створіть віртуальну версію себе у ключ

2. клацання дані > Перевірка достовірності даних > Перевірка достовірності даних.

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

Тепер створюється випадаючий список із використанням даних із іменованого діапазону.

Створіть випадаючий список з іншої книги

Припустимо, що існує робоча книга з назвою “ДжерелоДані", І ви хочете створити випадаючий список в іншій книзі на основі даних у цьому"ДжерелоДані”, Будь-ласка, виконайте наступні дії.

1. Відкрийте книгу “SourceData”. У цій книзі виберіть випадаючий список, на основі якого ви створите дані, введіть назву діапазону в ІМ'Я , а потім натисніть Що натомість? Створіть віртуальну версію себе у ключ

Тут я називаю діапазон як Місто.

2. Відкрийте робочий аркуш, який ви вставите, розкривний список. Клацніть Формули > Визначте ім’я.

3 В Нове ім'я діалоговому вікні, вам потрібно створити іменований діапазон на основі назви діапазону, який ви створили у книзі “SourceData”, будь ласка, налаштуйте наступним чином.

примітки:

4. Відкрийте книгу, до якої ви вставите випадаючий список, виділіть комірки для випадаючого списку та натисніть дані > Перевірка достовірності даних > Перевірка достовірності даних.

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

Тепер випадаючі списки були вставлені у вибраний діапазон. А випадаючі значення - з іншої книги.

Легко створюйте випадаючий список за допомогою дивовижного інструменту

Тут настійно рекомендую Створіть простий розкривний список корисність Kutools для Excel. За допомогою цієї функції ви можете легко створити випадаючий список із певними значеннями комірок або створити випадаючий список із користувацькими списками, попередньо встановленими в Excel.

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

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

примітки: Якщо ви хочете створити випадаючий список на основі користувацького списку, встановленого в Excel, будь ласка, виберіть Спеціальні списки опція в Source розділ, виберіть спеціальний список у Спеціальні списки , а потім клацніть на OK кнопки.

Тепер випадаючі списки були вставлені у вибраний діапазон.

---

Редагувати випадаючий список

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

Відредагуйте випадаючий список на основі діапазону комірок

Для редагування випадаючого списку на основі діапазону комірок виконайте наступні дії.

1. Виберіть клітинки, що містять випадаючий список, який потрібно відредагувати, а потім клацніть дані > Перевірка достовірності даних > Перевірка достовірності даних.

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

Відредагуйте випадаючий список на основі названого діапазону

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

1. клацання Формули > Менеджер імен.

Tips : Ви можете відкрити Менеджер імен вікно, натиснувши Ctrl + F3 ключі.

2 В Менеджер імен вікно, вам потрібно налаштувати наступним чином:

3. Тоді a Microsoft Excel спливаюче діалогове вікно, клацніть на Так кнопка, щоб зберегти зміни.

Потім випадаючі списки на основі цього іменованого діапазону оновлюються.

---

Видалити випадаючий список

У цьому розділі йдеться про видалення випадаючого списку в Excel.

Видалити розкривний список за допомогою вбудованої програми Excel

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

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

2. клацання дані > Перевірка достовірності даних > Перевірка достовірності даних.

3 В Перевірка достовірності даних у діалоговому вікні натисніть Очистити всі кнопку, а потім натисніть кнопку OK зберегти зміни.

Тепер випадаючі списки видаляються з вибраного діапазону.

Легко видаляйте випадаючі списки за допомогою дивовижного інструменту

Kutools для Excel надає зручний інструмент - Очистити обмеження перевірки данихs для легкого видалення випадаючого списку з одного або декількох вибраних діапазонів одночасно. Будь ласка, виконайте наступне.

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

2. клацання Кутулс > Запобігання друку > Очистити обмеження перевірки даних. Дивіться знімок екрана:

3. Тоді a Kutools для Excel спливає діалогове вікно, щоб запитати вас, чи очистити випадаючий список, натисніть кнопку OK кнопки.

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

---

Додайте колір до випадаючого списку

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

Додайте колір до випадаючого списку за допомогою умовного форматування

Ви можете створити умовні правила для комірки, що містить випадаючий список, щоб зробити її кольоровою. Будь ласка, виконайте наступне.

1. Виділіть комірки, що містять випадаючий список, який ви хочете зробити кольоровим.

2. клацання Головна > Умовне форматування > Управління правилами.

3 В Умовне форматування Rues Manager у діалоговому вікні натисніть Нове правило кнопки.

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

5. Коли він повернеться до Менеджер правил умовного форматування діалогове вікно, повторіть кроки 3 та 4, щоб вказати кольори для інших випадаючих елементів. Після закінчення вказівки кольорів клацніть на OK зберегти зміни.

Відтепер, при виборі елемента зі спадного списку, комірка буде виділена вказаним кольором тла на основі вибраного тексту.

Легко додайте колір до випадаючого списку за допомогою дивовижного інструменту

Тут представити Кольоровий випадаючий список особливість Kutools для Excel , щоб допомогти вам легко додати колір до випадаючого списку в Excel.

1. Виділіть комірки, що містять випадаючий список, до якого потрібно додати колір.

2. клацання Кутулс > Випадаючий список > Кольоровий випадаючий список.

3 В Кольоровий розкривний список діалогове вікно, будь ласка, виконайте наступне

Tips : Якщо ви хочете виділити рядки на основі вибору в розкривному списку, будь ласка, виберіть Рядок діапазону даних опція в Застосувати до , а потім виберіть рядки, які ви виділите в Виділити рядки коробка

Тепер випадаючі списки мають кольорове кодування, як показано на скріншотах нижче.

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

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

---

Створіть залежний випадаючий список в Excel або на аркуші Google

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

Створіть залежний випадаючий список на аркуші Excel

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

Будь ласка, натисніть Як створити залежний каскадний випадаючий список в Excel? для покрокового підручника.

Створіть залежний випадаючий список на аркуші Google

Якщо ви хочете створити залежний випадаючий список на аркуші Google, див Як створити залежний випадаючий список у таблиці Google?

---

Створіть випадаючі списки для пошуку

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

Припустимо, вихідні дані, які ви хочете створити, випадаючий список на основі розташувань у стовпці A аркуша1, як показано нижче. Будь ласка, виконайте наступні дії, щоб створити розкривний список, що можна знайти в Excel, із цими даними.

1. По-перше, створіть допоміжний стовпець біля списку вихідних даних із формулою масиву.

У цьому випадку я вибираю клітинку B2, ввожу в неї формулу нижче, а потім натискаю Ctrl + Shift + Що натомість? Створіть віртуальну версію себе у клавіші, щоб отримати перший результат.

=IFERROR(INDEX($A$2:$A$50,SMALL(IFERROR(MATCH(IF(FIND(CELL("contents"),$A$2:$A$50)>0,$A$2:$A$50,""),$A$2:$A$50,0),""),ROW(A1))),"")

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

примітки: У цій формулі масиву $ A $ 2: $ A $ 50 - це діапазон вихідних даних, за яким ви створите випадаючий список. Будь ласка, змініть його на основі вашого діапазону даних.

2. клацання Формули > Визначте ім’я.

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

Тепер вам потрібно створити випадаючий список на основі названого діапазону. У цьому випадку я буду створювати випадаючий список, який можна шукати на Аркуші2.

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

5 В Перевірка достовірності даних діалогове вікно, будь ласка, виконайте наступне

6. Клацніть правою кнопкою миші вкладку аркуша (Аркуш2) і виберіть Переглянути код з меню, що клацне правою кнопкою миші.

7. На відкритті Microsoft Visual Basic для додатків вікно, скопіюйте наведений нижче код VBA в редактор коду.

Код VBA: створити розкривний список для пошуку в Excel

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculate
End Sub

8 Натисніть кнопку інший + Q клавіші, щоб закрити Microsoft Visual Basic для програм вікна.

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

примітки: Цей метод чутливий до регістру.

---

Створити випадаючий список, але показувати різні значення

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

1. Праворуч від вихідних даних (стовпець з назвою країни) створіть новий стовпець, що містить скорочення назв країн, які потрібно відобразити у спадному вікні.

2. Виберіть як список назв країн, так і список абревіатур, введіть ім'я в полі ІМ'Я а потім натисніть Що натомість? Створіть віртуальну версію себе у ключ

3. Виділіть комірки для випадаючого списку (тут я вибираю D2: D8), а потім натисніть дані > Перевірка достовірності даних > Перевірка достовірності даних.

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

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

6. На відкритті Microsoft Visual Basic для додатків вікно, скопіюйте наведений нижче код VBA в редактор коду.

Код VBA: Показати різні значення у випадаючому списку

Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice 20201027
    selectedNa = Target.Value
    If Target.Column = 4 Then
        selectedNum = Application.VLookup(selectedNa, ActiveSheet.Range("dropdown"), 2, False)
        If Not IsError(selectedNum) Then
            Target.Value = selectedNum
        End If
    End If
End Sub

примітки:

7 Натисніть кнопку інший + Q клавіші, щоб закрити Microsoft Visual Basic для додатків вікна.

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

---

Створіть випадаючий список із прапорцями

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

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

Якщо ви хочете створити розкривний список із прапорцями в Excel, див Як створити випадаючий список із кількома прапорцями в Excel?.

---

Додайте автозаповнення до випадаючого списку

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

Щоб зробити автоматичне заповнення розкривного списку на аркуші в Excel, див Як автозавершити при введенні в розкривному списку Excel?.

---

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

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

1. По-перше, вам потрібно створити випадаючий список із конкретними значеннями, на основі яких ви будете отримувати дані.

Tips : Будь ласка, виконайте наведені вище кроки для створити розкривний список в Excel.

Створіть випадаючий список з унікальним списком елементів

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

1) Скопіюйте випадаючий список на основі клітинок, які ви створите Ctrl + C клавіші, а потім вставте їх у новий діапазон.

2) Виділіть клітинки в новому діапазоні, натисніть дані > Видалити дублікати.

3) У Видалити дублікати у діалоговому вікні натисніть OK кнопки.

4) Тоді a Microsoft Excel спливає, щоб повідомити, скільки дублікатів видалено, натисніть OK.

Тепер ви отримуєте унікальний список елементів, і ви можете створити випадаючий список на основі цього унікального списку зараз.

2. Потім вам потрібно створити три допоміжні стовпці наступним чином.

3. Створіть діапазон на основі вихідного діапазону даних, щоб вивести витягнуті дані за поданими нижче формулами.

примітки:

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

---

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

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

Для вибору декількох елементів зі спадного списку в Excel, будь ласка, див Як створити випадаючий список із кількома виділеннями або значеннями в Excel?. Цей посібник містить два методи, які допоможуть вам вирішити проблему.

---

Встановити значення за замовчуванням (попередньо вибране) для випадаючого списку

За замовчуванням комірка спадного списку відображається порожньою, стрілка спадного меню відображається лише при натисканні на клітинку. Як швидко зрозуміти, які клітинки містять випадаючі списки на аркуші?

Цей розділ продемонструє, як встановити значення за замовчуванням (попередньо вибране) для випадаючого списку в Excel. Будь ласка, виконайте наступне.

Перш ніж застосовувати наступні два методи, вам потрібно створити випадаючий список і виконати деякі конфігурації наступним чином.

1. Виділіть комірки для випадаючого списку, натисніть дані > Перевірка достовірності даних > Перевірка достовірності даних.

Tips : Якщо ви вже створили розкривний список, виберіть клітинки, що містять випадаючий список, а потім натисніть дані > Перевірка достовірності даних > Перевірка достовірності даних.

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

Створивши випадаючий список, застосуйте один із наведених нижче методів, щоб встановити для них значення за замовчуванням.

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

Ви можете застосувати формулу нижче, щоб встановити значення за замовчуванням для випадаючого списку, який ви створили, як показано вище.

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

= IF (C2 = "", "--Виберіть елемент зі списку--")

примітки:

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

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

1. Відкрийте аркуш, що містить випадаючі списки, для яких потрібно встановити значення за замовчуванням, натисніть інший + F11 ключі, щоб відкрити Microsoft Visual Basic для додатків вікна.

2 В Microsoft Visual Basic для додатків вікна, натисніть Insert > Модулі, а потім вставте наведений нижче код VBA у вікно коду.

Код VBA: Встановіть значення за замовчуванням для всіх випадаючих списків на аркуші одночасно

Sub SetDropDownListToDefaultValue()
'Updated by Extendoffice 20201026
Dim xWs As Worksheet
Dim xRg, xFRg As Range
Dim xET: xET = Null
Dim xStr As String
xStr = "- Choose from the list -"
Set xWs = Application.ActiveSheet
Set xRg = xWs.UsedRange.Cells
    On Error Resume Next
    For Each xFRg In xRg
    xET = Null
    xET = xFRg.Validation.Type
    If Not IsNull(xET) Then
        If xFRg.Validation.Type = 3 Then
            xFRg.Value = "'" & xStr
        End If
    End If
    Next
End Sub

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

3 Натисніть кнопку F5 клавіша, потім з'явиться діалогове вікно Макроси, переконайтесь, що DropDownListToDefault вибрано в Назва макросу , а потім клацніть на прогін для запуску коду.

Потім вказане значення за замовчуванням негайно заповнюється у випадаючі комірки списку.

---

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

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

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

2 В Microsoft Visual Basic для додатків вікно, скопіюйте наведений нижче код VBA в редактор коду.

Код VBA: Збільште розмір шрифту випадаючих списків на аркуші

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'updateby Extendoffice 20201027
    On Error GoTo LZoom
    Dim xZoom As Long
    xZoom = 100
    If Target.Validation.Type = xlValidateList Then xZoom = 130
LZoom:
    ActiveWindow.Zoom = xZoom
End Sub

примітки: тут xZoom = 130 в коді означає, що ви збільшите розмір шрифту всіх випадаючих списків на поточному аркуші до 130. Ви можете змінити його, як вам потрібно.

3 Натисніть кнопку інший + Q клавіші, щоб закрити Microsoft Visual Basic для додатків вікна.

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

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