Залежні списки в Excel: створюємо логіку без програмування

Доброго дня вам, друзі!

Даруйте що майже тиждень не озивався – не писав нічого нового в цей блог. Справа в тому, що я проводив два дуже відповідальних тренінги з бізнес-аналізу в корпоративному форматі для двох великих українських компаній.

А повернувшись додому, і нарешті отримавши змогу не поспішаючи переглянути пошту, яка накопичилась, знайшов листа з цікавим запитанням стосовно Microsoft Excel. Один з моїх колишніх колег питав, чи не можна зробити залежні випадаючі списки в Excel без програмування. Наприклад, ви вибираєте категорію товару, і потім в іншому місці можете вибрати товар з цієї категорії.

Звичайно, ми всі знаємо як зробити випадаючий список в Excel, скориставшись валыдацыэю даних. Однак, це завдання складніше, оскільки другий список повинен пропонувати різний набір записів для вибору в залежності від того, що ви обрали в першому списку.

А вирішення цієї проблеми виявилось дуже простим – виявляється для створення подібних речей в Excel є функція INDIRECT, яка повертає посилання на іменований діапазон на основі текстового рядка – його імені. Тож все просто.

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

1. Починаємо зі створення списків:

   

   Зверніть увагу на назви підпорядкованих списків – вони співпадають з елементами головного списку.

2. Присвоюємо ім’я головному списку. Для цього виділяємо його елементи окрім назви, після чого клацаємо мишею у полі імені рядка формул і вводимо назву (у даному випадку “Услуга”):

   
   Звичайно ж, ми знаємо, що можна було б скористатись також менеджером імен, натиснувши CTRL+F3.

3. Даємо імена залежним спискам. Робиться це так само, як і у випадку головного списку. Слід пам’ятати лише таку річ: ІМЕНА ЗАЛЕЖНИХ СПИСКІВ ПОВИННІ СПІВПАДАТИ З ЕЛЕМЕНТАМИ ГОЛОВНОГО СПИСКУ!
   
   
   Зверніть увагу на те, що елементи головного списку і назви залежних списків складаються з одного слова! Елементи залежних списків можуть складатись з кількох слів, але назви – ні в якому разі! Далі я скажу як бути, коли необхідно мати довші назви.

   

4. Готуємо місце для розміщення випадаючих списків:

   

5. Застосовуємо валідація для комірок стовпчика “Вид услуги”. Для цього виділяємо їх, переходимо на вкладинку Data і обираємо пункт Data Validation у випадаючому меню кнопки Data Validation:

   
   У вікні, яке з’явиться, робимо такі речі:

   

   Зверніть увагу, що у рядку Source ми ввели назву головного списка попереду якого стоїть знак рівності, тобто записали формулу, яка повертає посилання на іменований діапазон.

6. Тепер застосовуємо валідацію для стовпчика “Услуга”. Робимо це майже так само, як і у попередньому випадку, однак рядок Source у вікні налагодження правил валідації виглядає трохи інакше:

   
   Формула, яку ми записали, повертає посилання на список, назва якого міститься у комірці, що знаходиться зліва від поточної ( B18 у нашому випадку – перша комірка діапазона, де буде обиратись вид послуги). Якщо ви отримаєте повідомлення по помилку, оскільки комірка B18 (в нашорму випадку) пуста, сміливо натискайте Yes.

7. Все! Можна тестувати!

   

   

   Як бачите, все працює! Ось так, просто і швидко можна створити певну багатоступінчасту логіку вибору одного з варіантів в залежності від попереднього вибору.

Деталі ви можете почитати тут. Якщо треба, щоб елементи головного списку складались з двох слів, вам сюди. Також корисно буде почитати ось цю статтю.