Залежні випадаючі списки

Залежний випадаючий список — це список, який змінюється залежно від вибору в іншій клітинці. Класичний приклад у нашій таблиці: коли користувач обирає Tech у стовпці Category, у випадаючому списку Product мають з’явитися лише Laptop і Phone — а не Chair чи Desk. Якщо змінити категорію на Office, список продуктів відповідно зміниться.

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

Логіка роботи

Суть полягає у поєднанні двох знайомих інструментів:

• Іменовані діапазони — по одному для кожної категорії, кожен вказує на відповідний список продуктів;
• INDIRECT — для динамічного вибору іменованого діапазону залежно від клітинки з категорією.

Якщо ваші іменовані діапазони називаються Tech і Office, а категорія вибирається у клітинці D2, тоді ця формула у полі валідації Product: =INDIRECT(D2).

Покрокове налаштування

Крок 1 — Підготуйте списки на аркуші Lists:

• E1: Laptop
• E2: Phone
• F1: Chair
• F2: Desk

Крок 2 — Створення іменованого діапазону для кожної категорії:

• Виділити E1:E2 → у Рядку імені ввести Tech;
• Виділити F1:F2 → у Рядку імені ввести Office.

Крок 3 — Застосування перевірки даних до стовпця Product:

1. Виділіть клітинки стовпця Product (E2:E51);
2. Відкрийте Data Validation → Settings → List;
3. У полі Source введіть: =INDIRECT(D2) — де D2 це перша клітинка Category;
4. Натисніть OK

Відоме обмеження

Якщо клітинка Category порожня, INDIRECT не має що обробити, і Excel видасть помилку перевірки при натисканні на випадаючий список Product. Це можна уникнути, якщо встановити прапорець Ignore blank у правилі перевірки Product — детальніше у Section 1, Chapter 5.

Завдання

1. Перевірте, вибравши Tech у Category — переконайтеся, що у стовпці Product з’являються лише Laptop та Phone;
2. Змініть Category на Office — переконайтеся, що список Product змінюється на Chair та Desk, або перевірте будь-яку клітинку у стовпці Product поруч із значенням Office у стовпці Category (наприклад, клітинка E4);
3. Перейдіть на аркуш Lists і додайте Tablet під Phone у стовпці E;
4. Відкрийте Formulas → Name Manager, знайдіть іменований діапазон Tech та розширте його, щоб включити новий рядок (E1:E3);
5. Перевірте випадаючий список Product ще раз — переконайтеся, що Tablet тепер з’являється.