Як будувати розкривні списки в Excel | Бізнес-школа Laba (Лаба)
Для відстеження статусу замовлення - авторизуйтесь
Введіть код, який був надісланий на пошту Введіть код із SMS, який був надісланий на номер
anastasiiasytar@gmail.com
Код дійсний протягом 2 хвилин Код з SMS дійсний протягом 2 хвилин
Ви впевнені, що хочете вийти?
Сеанс завершено
На головну

Пошук

Зміст

Як будувати розкривні списки в Excel

Гайд від аналітика Laba.

cover-6597f0c8b2d61748004878.jpg

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

Є багато способів, щоб створювати розкривні списки в 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.

Бажаєте отримувати дайджест статей?

Один лист з найкращими матеріалами за місяць. Підписуйтесь, аби нічого не проґавити.
Дякуємо за вашу підписку!
Курс з теми:
«Advanced Excel»
Програми
Веде Микита Свідло
23 вересня 21 жовтня
Микита Свідло