Зв'язані випадаючі списки

Опис

В Excel ви можете швидко і легко створити випадаючий список, але чи намагалися ви коли-небудь створювати залежний випадаючий список? Існує кілька способів створити такий список, отож, давайте їх розглянемо.

Спосіб 1. Функція INDIRECT

Цей прийом заснований на застосуванні функції INDIRECT, яка вміє робити одну просту річ - перетворювати вміст будь зазначеної комірки на адресу діапазону, яку розуміє Excel. Тобто, якщо в комірці лежить текст "А1", то функція видасть в результаті посилання на комірку А1. Якщо в комірці лежить слово "Auto", то функція видасть посилання на іменований діапазон з ім'ям Auto і т.д.

Візьмемо, наприклад, ось такий список моделей автомобілів Toyota, Ford і Nissan:

Виділимо весь список моделей Тойоти (з комірки А2 і вниз до кінця списку) і дамо цьому діапазону ім'я Toyota в меню Вставка - Ім'я - Присвоїти (Insert - Name - Define). Потім повторимо те ж саме зі списками Ford і Nissan, задавши відповідно імена діапазонів Ford і Nissan.

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

Тепер створимо другий список, що випадає, в якому будуть відображатися моделі обраної в першому списку марки. Точно так само, як у попередньому випадку, виділіть порожню комірку і відкрийте меню Дані - Перевірка - далі Список . У полі Джерело потрібно буде ввести ось таку формулу:

=INDIRECT(F3)

Де:

• F3 - адреса комірки з першим списком, що випадає - замініть на свій.

Все. Після натискання на ОК вміст другого списку буде вибиратися по імені діапазону, обраного в першому списку.

Мінуси такого способу:

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

Спосіб 2. Список відповідностей (OFFSET та MATCH)

Цей спосіб вимагає наявності відсортованого списку відповідностей марка-модель ось такого виду:

Для створення первинного випадаючого списку марок, можна скористатися звичайним способом, описаним вище, тобто:

• Дати ім'я діапазону D1:D3 (наприклад Марки)
• Вибрати на вкладці Дані (Data) команду Перевірка даних (Data validation)
• Вибрати з випадного списку варіант перевірки Список (List) та вказати в якості Джерела (Source) = Марки або просто виділити комірки D1:D3 (якщо вони на тому ж аркуші, де і список).

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

• Натисніть Ctrl + F3 або скористайтеся кнопкою Диспетчер імен (Name manager) на вкладці Формули (Formulas). У версіях до 2003 це була команда меню Вставка - Ім'я - Присвоїти (Insert - Name - Define)
• Створіть новий іменований діапазон з будь-яким ім'ям (наприклад Моделі) і в полі Лінк (Reference) у нижній частині вікна введіть руками наступну формулу:

=OFFSET($A$1; MATCH($G$7;$A:$A;0)-1; 1; COUNTIF($A:$A;$G$7); 1)

Функція OFFSET вміє видавати посилання на діапазон потрібного розміру, зрушений відносно початкової комірки на задану кількість рядків і стовпців. У більш зрозумілою варіанті синтаксис цієї функції такий:

=OFFSET(початкова_комірка; зсув_вниз; зсув_вправо; розмір_діапазону_в_рядках; размір_діапазону_в_стовпцях)

Таким чином:

• початкова_комірка - беремо першу комірку нашого списку, тобто А1;
• зсув_вниз - нам рахує функція MATCH, яка, просто кажучи, видає порядковий номер комірки з обраної маркою (G7) в заданому діапазоні (стовпці А);
• зсув_вправо=1, тому ми хочемо послатися на моделі в сусідньому стовпці (В);
• розмір_діапазону_в_рядках - обчислюємо за допомогою функції COUNTIF, яка вміє підрахувати кількість входжень в списку (стовпці А) потрібних нам значень - марок авто (G7);
• размір_діапазону_в_стовпцях=1, тому нам потрібен один стовпець з моделями.

У результаті повинно вийти щось на зразок цього:

Залишилося додати випадаючий список на основі створеної формули до комірки G8. Для цього:

• Виділяємо комірку G8
• Вибираємо на вкладці Дані (Data) команду Перевірка даних (Data validation) або в меню Дані - Перевірка (Data - Validation)
• З випадаючого списку вибираємо варіант перевірки Список (List) і вводимо в якості Джерела (Source) знак дорівнює і ім'я нашого діапазону, тобто = Моделі.

Статті по темі:

• Випадаючий список в комірці
• Підсказка при заповнені даних в комірку
• Функція OFFSET