Note: The other languages of the website are Google-translated. Back to English
Увійти  \/ 
x
or
x
Реєстрація  \/ 
x

or

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

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

Цей підручник насамперед охоплює: (Клацніть на будь-який заголовок у змісті нижче або праворуч, щоб перейти до відповідного вмісту.)


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

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

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

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

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

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

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

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

3.1) У дозволяти випадаючий список, виберіть список;
3.2) У джерело виберіть діапазон комірок, значення яких ви відображатимете у розкривному списку;
3.3) Клацніть на OK кнопки.

примітки:

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

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

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

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

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

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

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

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

4.1) Виберіть список в дозволяти випадаючий список;
4.2) Виберіть діапазон таблиці (за винятком заголовка) у вікні джерело коробка;
4.3) Клацніть на OK кнопки.

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

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

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

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

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

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

3.1) У дозволяти поле, виберіть список;
3.2) У джерело поле, введіть в нього формулу нижче;
= OFFSET ($ A $ 13,0,0, COUNTA ($ A $ 13: $ A $ 24), 1)
примітки: У цій формулі $ A $ 13 - це перша комірка діапазону даних, а $ A $ 13: $ A $ 24 - це діапазон даних, на основі якого ви будете створювати випадаючі списки.
3.3) Клацніть на OK кнопку. Дивіться знімок екрана:

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

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

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

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

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

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

3.1) У дозволяти поле, виберіть список;
3.2) Клацніть на джерело , а потім натисніть F3 ключ
3.3) У Вставити ім'я діалоговому вікні, виберіть ім'я діапазону, яке ви створили зараз, і натисніть кнопку OK кнопка;
Поради: Ви також можете ввести вручну = назва діапазону в джерело коробці. У цьому випадку я вступлю = Місто.
3.4) Клацніть OK коли він повернеться до Перевірка достовірності даних діалогове вікно. Дивіться знімок екрана:

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

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

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

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

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

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

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

3.1) Введіть ім'я в Назва коробка;
3.2) У Відноситься до введіть у нього формулу нижче.
= SourceData.xlsx! Місто
3.3) Клацніть OK щоб його зберегти

примітки:

1). У формулі SourceData це назва книги, що містить дані, на основі яких ви будете створювати випадаючий список; Місто - це назва діапазону, яку ви вказали у книзі SourceData.
2). Якщо пробіл або інші символи, такі як -, # ..., включно з назвою книги вихідних даних, потрібно вкласти назву книги в одинарні лапки, наприклад = 'Вихідні дані.xlsx'! Місто.

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

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

5.1) У дозволяти поле, виберіть список;
5.2) Клацніть на джерело , а потім натисніть F3 ключ
5.3) У Вставити ім'я діалоговому вікні, виберіть ім'я діапазону, яке ви створили зараз, і натисніть кнопку OK кнопка;
чайові: Ви також можете ввести вручну = назва діапазону в джерело коробці. У цьому випадку я вступлю = Тест.
5.4) Клацніть OK коли він повернеться до Перевірка достовірності даних діалогове вікно.

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

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

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

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

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

3.1) У Застосувати до Ви можете побачити, що вибраний діапазон відображається тут. Ви можете змінити застосований діапазон комірок, як вам потрібно;
3.2) У джерело розділу, якщо ви хочете створити випадаючі списки на основі даних діапазону комірок або вам просто потрібно ввести значення вручну, виберіть Введіть значення або посилайтеся на значення комірки варіант. У текстовому полі виберіть діапазон комірок або введіть значення (розділені комами), на основі яких ви створите випадаючий список;
3.3) Клацніть OK.

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

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


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

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

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

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

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

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

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

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

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

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

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

2.1) У Назва Виберіть названий діапазон, який потрібно оновити;
2.2) У Відноситься до натисніть кнопку щоб вибрати оновлений діапазон для випадаючого списку;
2.3) Клацніть на близько кнопки.

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 В Нове правило форматування діалогове вікно, будь ласка, налаштуйте наступним чином.

4.1) У Виберіть тип правила , виберіть Форматувати лише комірки, що містять варіант;
4.2) У Форматувати лише клітинки з розділ, виберіть Конкретний текст з першого випадаючого списку виберіть що містить з другого випадаючого списку, а потім виберіть перший елемент списку джерел у третьому полі;
чайові: Тут я вибираю клітинку A16 у третьому текстовому полі. A16 - це перший елемент вихідного списку, на основі якого я створив розкривний список.
4.3) Клацніть на сформований кнопки.
4.4) У Формат ячеек діалогове вікно, перейдіть до Заповнювати на вкладці виберіть колір фону для вказаного тексту, а потім клацніть на OK кнопку. Або ви можете вибрати певний колір шрифту для тексту, як вам потрібно.
4.5) Клацніть на OK , коли вона повертається до Нове правило форматування діалогове вікно.

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

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

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

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

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

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

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

3.1) У Застосувати до розділ, виберіть Осередок спадного списку варіант;
3.2) У Діапазон перевірки даних (випадаючий список) Ви можете побачити, що вибрані посилання на комірки відображаються всередині. Ви можете змінювати діапазон комірок, як вам потрібно;
3.3) У Елементи списку вікно (тут відображаються всі випадаючі елементи у вибраному діапазоні), виберіть елемент, для якого ви вкажете колір;
3.4) У Виберіть колір розділ, виберіть колір тла;
примітки: Вам потрібно повторити кроки 3.3 та 3.4, щоб вказати інший колір для інших елементів;
3.5) Клацніть на OK кнопку. Дивіться знімок екрана:

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

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

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

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


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

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

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

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

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

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

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


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

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

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

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

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

=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 В Редагувати ім'я діалогове вікно, будь ласка, налаштуйте наступним чином.

3.1) У Назва поле, введіть назву для названого діапазону;
3.2) У Відноситься до поле, введіть в нього формулу нижче;
=OFFSET(Sheet1!$B$2,0,0,COUNTA(Sheet1!$B$2:$B$50)-COUNTIF(Sheet1!$B$2:$B$50,""),1)
3.3) Клацніть на OK кнопку. Дивіться знімок екрана:

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

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

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

5.1) У дозволяти поле, виберіть список;
5.2) Клацніть на джерело , а потім натисніть F3 ключ;
5.3) У спливаючому вікні Вставити ім'я діалоговому вікні, виберіть іменований діапазон, який ви створили в кроці 3, а потім натисніть OK;
чайові: Ви можете безпосередньо ввести названий діапазон як = іменований діапазон в джерело коробка
5.4) Клацніть на Повідомлення про помилку вкладка зніміть прапорець Показувати попередження про помилку після введення недійсних даних і нарешті клацніть на OK кнопки.

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 В Перевірка достовірності даних діалогове вікно, будь ласка, налаштуйте наступним чином.

4.1) У дозволяти поле, виберіть список;
4.2) У джерело поле, виберіть діапазон вихідних даних (у цьому випадку перелік назв країн);
4.3) Клацніть OK.

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

примітки:

1) У коді цифра 4 у рядку Якщо Target.Column = 4 Потім представляє номер стовпця випадаючого списку, який ви створили на кроках 3 і 4. Якщо ваш випадаючий список знаходиться у стовпці F, будь ласка, замініть номер 4 на 6;
2) “випадає”У п’ятому рядку - ім’я діапазону, яке ви створили на кроці 2. Ви можете змінити його, як вам потрібно.

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

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


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

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

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

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


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

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

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


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

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

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

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

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

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

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

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

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

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

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

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

2.1) Для першого допоміжного стовпця (тут я вибираю стовпець D як першого допоміжного стовпця) введіть формулу нижче в першу комірку (крім заголовка стовпця), а потім натисніть вводити ключ. Виділіть комірку результату, а потім перетягніть Ручка заповнення аж донизу, поки не досягне дна діапазону.
= РЯДКИ ($ A $ 2: A2)
2.2) Для другого допоміжного стовпця (стовпець E) введіть формулу нижче у клітинку E2, а потім натисніть вводити ключ. Виберіть E2, а потім перетягніть Ручка заповнення до нижньої частини діапазону.
Примітка: Якщо в розкривному списку не вибрано жодного значення, тут результати формул відображатимуться порожніми.
= IF (A2 = $ H $ 2, D2, "")
2.3) Для третього допоміжного стовпця (стовпець F) введіть формулу нижче в F2, а потім натисніть вводити ключ. Виберіть F2, а потім перетягніть Ручка заповнення до нижньої частини діапазону.
примітки: Якщо в розкривному списку не вибрано жодного значення, результати формул відображатимуться порожніми.
= IFERROR (МАЛЕНЬКИЙ ($ E $ 2: $ E $ 17, D2), "")

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

3.1) Виберіть першу вихідну комірку (тут я вибираю J2), введіть в неї формулу нижче, а потім натисніть вводити ключ
=IFERROR(INDEX($A$2:$C$17,$F2,COLUMNS($J$2:J2)),"")
3.2) Виберіть комірку результату, а потім перетягніть Ручка заповнення поперек праворуч дві клітини.
3.3) Тримайте діапазон J2: l2 вибраним, перетягуйте ручку заповнення донизу, поки вона не досягне нижньої частини діапазону.

примітки:

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

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


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

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

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


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

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

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

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

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

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

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

2.1) У дозволяти поле, виберіть список;
2.2) У джерело Виберіть вихідні дані, які відображатимуться у випадаючому списку.
чайові: Для випадаючого списку, який ви вже створили, пропустіть ці два кроки.
2.3) Потім перейдіть до Повідомлення про помилку вкладка зніміть прапорець Показувати попередження про помилку після введення недійсних даних коробка;
2.4) Клацніть на OK кнопки.

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

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

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

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

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

примітки:

1) У формулі, C2 - порожня комірка поруч із коміркою випадаючого списку, ви можете вказати будь-яку порожню комірку, як вам потрібно.
2) --Виберіть пункт зі списку-- - значення за замовчуванням, яке відображається в комірці спадного списку. Ви також можете змінити значення за замовчуванням залежно від ваших потреб.
3) Формула працює лише до вибору елементів зі спадного меню, після вибору елемента зі спадного меню значення за замовчуванням буде замінено, а формула зникне.
Встановіть значення за замовчуванням для всіх випадаючих списків на аркуші одночасно з кодом 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 для додатків вікна.

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

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


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

Kutools для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

  • Повторне використання: Швидко вставте складні формули, діаграми і все, що ви використовували раніше; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
  • Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
  • Об’єднати клітинки / рядки / стовпці без втрати даних; Вміст розділених комірок; Об'єднати повторювані рядки / стовпці... Запобігання дублюючим клітинам; Порівняйте діапазони...
  • Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
  • Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
  • Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
  • Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
  • Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
  • Понад 300 потужних функцій. Підтримує Office / Excel 2007-2019 та 365. Підтримує всі мови. Простота розгортання на вашому підприємстві чи в організації. Повна функція 30-денної безкоштовної пробної версії. 60-денна гарантія повернення грошей.
вкладка kte 201905

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

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    通りすがりのわんこ · 1 months ago
    こちらはOffice365ですが、どうやらそのコーディングでは動作しないようです。
    代わりに初歩的ですが、以下にて動作を確認出来ました。

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim xZoom As Variant
    If (Target.Row >= 11 And Target.Row <= 35 And Target.Column >= 3 And Target.Column <= 6) Then
    ActiveWindow.zoom = 150
    Else
    ActiveWindow.zoom = 60
    End If
    End Sub