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

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

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

Налаштування аркуша для списків

Перед створенням будь-якої таблиці рекомендується зберігати дані списків на окремому аркуші — окремо від основної області введення даних. Це допомагає підтримувати чистоту книги та спрощує керування списками.

Крок 1 — Створення нового аркуша:

1. Натисніть на іконку + поруч із останньою вкладкою;
2. Двічі клацніть нову вкладку та перейменуйте її на Lists.

Крок 2 — Додавання першого списку:

1. Клацніть клітинку A1 і введіть заголовок — наприклад, Status;
2. Введіть кожне значення у рядках нижче, по одному в кожну клітинку:
   • A2: Open
   • A3: Closed
   • A4: Pending
3. Тримайте стовпець охайним — без порожніх рядків, зайвих пробілів чи об'єднаних клітинок.

Крок 3 — Перетворення діапазону на таблицю Excel:

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

1. Клацніть будь-де всередині діапазону списку;
2. Натисніть Ctrl + T (Win) або cmd + T (Mac);
3. Підтвердьте діапазон і встановіть прапорець My table has headers;
4. Натисніть OK.

Excel призначає таблиці стандартну назву, наприклад, Table1. Перейменуйте її на щось змістовне — наприклад, Statuses — через вкладку Table Design.

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

Не можна безпосередньо ввести структуроване посилання на кшталт =Statuses[Status] у поле джерела перевірки даних — Excel не приймає його там. Охайний обхідний шлях — використання функції INDIRECT, яка перетворює текстовий рядок на дійсне посилання на діапазон.

Покроково:

1. Виділити клітинки стовпця Status у головній таблиці;
2. Відкрити Data Validation → Settings → List;
3. У полі Source ввести: =INDIRECT("Statuses");
4. Натиснути OK.

Тепер, коли до Statuses додається нове значення, випадаючий список оновлюється одразу — Name Manager не потрібен.

Завдання

Файл: продовжуйте працювати з тим самим файлом, що й у попередньому розділі.

1. Перейдіть на аркуш Lists і додайте нове значення під Pending у таблиці Statuses:
   • A5: Cancelled
2. Поверніться на головний аркуш і відкрийте випадаючий список у будь-якій клітинці Status.
3. Переконайтеся, що Cancelled тепер з'являється у списку разом із Open, Closed та Pending.

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