Розкривний список у комірці

Відео

 У кого мало часу і потрібно швидко вловити суть – дивіться навчальне відео:

Кому цікаві подробиці та нюанси всіх описаних способів – далі по тексту.

Спосіб 1. Примітивний

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

Спосіб 2. Стандартний

1. Виберіть клітинки з даними, які повинні бути включені в список, що випадає (наприклад, назви товарів).
2. Якщо у вас є Excel 2003 або старіша версія, виберіть у меню Вставити – Ім’я – Призначити (Вставити — Назва — Визначити), якщо Excel 2007 або новішої версії, відкрийте вкладку Формули і використовуйте кнопку Менеджер іменПотім Створювати. Введіть ім’я (можна будь-яке ім’я, але без пробілів і починаючи з літери!) для вибраного діапазону (наприклад, Product). Натисніть на OK.
3. Виділіть комірки (можна відразу кілька), в яких ви хочете отримати список, що випадає, і виберіть у меню (на вкладці) Дані – перевірка (дані – перевірка). З розкривного списку Тип даних (Дозволити) виберіть опцію список і ввести в рядок Source знак рівності та назва діапазону (тобто = Продукти).

прес OK.

все! Насолоджуйтесь!

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

Спосіб 3: Контроль

Цей метод полягає в тому, щоб вставити на аркуш новий об’єкт – елемент керування зі списком, а потім прив’язати його до діапазонів на аркуші. Для цього:

1. В Excel 2007/2010 відкрийте вкладку Розробник. У попередніх версіях панель інструментів Форми через меню Перегляд – Панелі інструментів – Форми (Вид – Панелі інструментів – Форми). Якщо ця вкладка не відображається, натисніть кнопку Office – параметри Excel - прапорець Показати вкладку «Розробник» на стрічці (кнопка «Office» – Параметри Excel – Показати вкладку «Розробник» на стрічці)
2. Знайдіть піктограму спадного меню серед елементів керування форми (не ActiveX!). Дотримуйтесь підказок у спливаючих вікнах комбінований список:

   Натисніть на іконку і намалюйте маленький горизонтальний прямокутник – майбутній список.

3. Клацніть правою кнопкою миші на намальованому списку та виберіть команду Формат об'єкта (керування форматом). У діалоговому вікні, що з’явиться, установіть

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

Натиснувши на OK можна використовувати список.

Для відображення його імені замість порядкового номера елемента можна додатково скористатися функцією ІНДЕКС (ІНДЕКС), яка може виводити вміст потрібної клітинки з діапазону:

Спосіб 4: елемент керування ActiveX

Цей спосіб частково нагадує попередній. Основна відмінність полягає в тому, що на аркуш додається не елемент керування, а елемент керування ActiveX. «Combo Box» зі спадного списку під кнопкою Insert із вкладки Розробник:

Механізм додавання той самий – виберіть об’єкт зі списку та намалюйте його на аркуші. Але потім починаються серйозні відмінності від попереднього способу.

По-перше, створений розкривний список ActiveX може перебувати в двох принципово різних станах – режимі налагодження, коли ви можете налаштовувати його параметри та властивості, переміщувати його по аркушу та змінювати розмір, і – режимі введення, коли єдине, що ви можете зробити це вибрати дані з нього. Перемикання між цими режимами здійснюється за допомогою кнопки. Режим дизайну таб Розробник:

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

Найбільш необхідні і корисні властивості, які можна і потрібно налаштувати:

• ListFillRange – діапазон клітинок, звідки беруться дані для списку. Це не дозволить вам вибрати діапазон мишкою, його потрібно просто ввести руками з клавіатури (наприклад, Sheet2! A1: A5)
• LinkedCell – пов’язана комірка, де буде відображатися елемент, вибраний зі списку
• ListRows – кількість виведених рядків
• шрифт – шрифт, розмір, стиль (курсив, підкреслення тощо, крім кольору)
• ForeColor и забарвлення – колір тексту та фону відповідно

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

При використанні цього методу також можна вказати як ListFillRange не тільки одновимірні діапазони. Ви можете, наприклад, встановити діапазон з двох стовпців і кількох рядків, додатково вказавши, що потрібно відобразити два стовпці (властивість КолонкаКонт=2). Тоді можна отримати дуже привабливі результати, які окуплять всі зусилля, витрачені на додаткові налаштування:

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

:

• Розкривний список із даними з іншого файлу
• Створення залежних спадних меню
• Автоматичне створення випадаючих списків додатком PLEX
• Вибір фотографії зі спадного списку
• Автоматичне видалення вже використаних елементів зі списку, що випадає
• Випадаючий список з автоматичним додаванням нових елементів