Розкривний список — це діапазон підготовлених заздалегідь значень, який розкривається в клітинці для вибору. Це зручно: вводити з клавіатури нічого не потрібно, і є можливість заборонити введення значень, які не відповідають наведеним у списку.
Є багато способів, щоб створювати розкривні списки в Excel. Розглянемо деякі разом із аналітиком Laba Олександром Галабурдою.
Як працює розкривний список в Excel
Припустимо, у нас є налаштована таблиця з розкривними списками. Якщо виділити клітинку в стовпці, у правому кутку клітинки з'явиться стрілка вниз. Натиснувши на неї, відкриється список значень для вибору.
Щоби створити такий список, перейдіть в розділ «Дані» на панелі інструментів, у групі «Дані» виберіть пункт «Перевірка даних».
Далі спливає вікно «Перевірка даних».
Вікно ділиться на 3 вкладки:
#1. Параметри
Тут задаються основні параметри розкривного списку в Excel:
- Тип даних. Можна вибрати тип даних, який міститиме список: діапазон цілих чи дійсних чисел, текстові вирази, дати та час. Можна задати обмеження щодо довжини тексту та різні формули.
- Ігнорувати порожні значення — цей пункт означає, що Excel не перевірятиме на правильність клітинки, в яких містяться порожні значення.
- Список припустимих значень. Цей прапорець відображається лише у випадку, якщо вибрано тип даних «Список». Якщо прибрати прапорець, в клітинці відбуватиметься перевірка на відповідність значень списку, але поле з розкривними значеннями буде відсутнє.
- Значення. Працює лише з тими типами даних, у яких можна встановити обмеження за числами чи датами.
- Джерело. Тут перераховуються значення для перевірки даних або задається формула.
- Поширити зміни на інші клітинки з тією ж умовою. Excel тут знаходить всі клітинки в книзі, які посилаються на ідентичну за властивостями умову та змінює їх відповідно до нових параметрів. Якщо прапорець не буде встановлений, умова буде змінена тільки для виділених клітинок у таблиці.
- Очистити все — видаляє встановлену перевірку даних із виділених клітинок.
#2. Повідомлення для вводу
У цій вкладці можна налаштувати розкривну підказку, яка висвічуватиметься при виділенні клітинки зі списком значень.
#3. Повідомлення про помилку
У цій вкладці можна налаштувати сценарій дій для Excel, якщо користувач спробує ввести значення, якого немає у списку.
Приклади розкривних списків в Excel
#1. Стандартний
Виділяємо клітинку (діапазон клітинок), де має розкриватися список (у нашому прикладі це вкладка «Проект», діапазон клітинок A2:A8), переходимо в розділ «Перевірка даних» (описано вище), вибираємо тип даних «Список», у полі «Джерело» вставляємо діапазон із джерела.
Якщо вам потрібно ввести підказки та налаштувати виведення повідомлення про помилку, переходимо у відповідні розділи та прописуємо необхідні властивості. Потім натискаємо «ОК».
Як бачимо, при виділенні клітинки в діапазоні A2:A8 у вкладці «Проект», у нас з'явився список значень.
#2. Список із підстановкою даних
Використовувати списки з чітким набором значень незручно, якщо потрібно поповнювати вихідні списки новими значеннями. У такому разі краще використовувати формули чи іменовані діапазони. Це виключить необхідність щоразу змінювати умову перевірки даних.
Допустимо нам потрібно додати ще 3 товари: ананас, томат та персик. Якщо ми введемо значення в джерело з даними, у розкривному списку нові значення так і не відобразяться.
Налаштуємо перевірку даних по-іншому. Для автодоповнення списків розглянемо 2 варіанти:
#1. Розумна таблиця. Виділяємо діапазон з джерелом, переходимо у вкладку на панелі інструментів «Основне», розділ «Стилі», розкриваємо меню «Формат таблиці» і вибираємо стиль розумної таблиці Excel, який сподобався.
Докладніше про те, що таке «Розумні таблиці» та як із ними працювати — на наших курсах.
Назвемо її «Товари», для цього виділяємо будь-яку клітинку в діапазоні таблиці, у верхньому правому кутку з'являється вкладка «Конструктор таблиць», переходимо, в розділі «Властивості» прописуємо ім'я таблиці. Воно не повинно містити пробіли та розділові знаки.
Щоб розкривний список в Excel став динамічним, виділяємо будь-яку з клітинок, де він знаходиться, переходимо в розділ «Перевірка даних». Нам підтягнеться поточна умова перевірки.
У рядку з джерелом прописуємо посилання на стовпець таблиці з використанням функції INDIRECT: =INDIRECT("Товари[Товар]"). Далі відзначаємо «Застосувати ці зміни до всіх клітинок із тією самою умовою» та натискаємо «ОК».
Тепер при додаванні значень в розумну таблицю, розкривний список в Excel буде автоматично поповнюватися.
#2. Диспетчер імен. Цей спосіб аналогічний попередньому, тільки з тією відмінністю, що ім'я буде надано діапазону без перетворення його в розумну таблицю.
Ім'я діапазону так само, як і в розумній таблиці, не повинно містити пробіли та розділові знаки. Виділяємо діапазон клітинок із запасом порожніх рядків. Наприклад, у нашому випадку ми розуміємо, що у списку більш ніж 25 значень не буде. Переходимо у вкладку «Формули», розділ «Визначені імена», меню «Диспетчер імен», натискаємо «Створити».
Називаємо майбутній список, за потреби коригуємо діапазон значень.
Повертаємося на лист «Проект», виділяємо клітинку, в якій має бути розкривний список, переходимо в меню «Перевірка даних» і в полі Джерело посилаємося на створений діапазон, натискаємо «ОК».
Загальні рекомендації
- Давайте імена джерелам з даними. Це дозволить поповнювати списки новими даними без зміни формули у перевірці даних.
- Якщо у вашому звіті є кілька списків, виносьте джерела на окремий лист. Це практично і не перевантажує головну сторінку звіту зайвими даними.
- Сортуйте список за зручним для вас параметром — у великих масивах це дозволить швидко знайти потрібну позицію.
- Не дублюйте в джерелах значення розкривного списку, інакше у вас буде декілька однакових значень.
- За замовчуванням у розкривному списку відображається всього 8 перших значень. Якщо потрібно відобразити більше або реалізувати функцію швидкого пошуку, використовуйте елементи керування VBA або ActiveX.
Бажаєте отримувати дайджест статей?
