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

Спосіб 1. Функція ДВССИЛ (INDIRECT)

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

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

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

При наданні імен пам'ятайте про те, що імена діапазонів в Excel не повинні містити прогалин, розділових знаків і починатися обов'язково з літери. Тому якщо б в одній з марок автомобілів була би присутня прогалина (наприклад Ssang Yong), то його довелося б замінити в комірці і в імені діапазону на нижнє підкреслення (тобто Ssang_Yong).

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

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

= ДВССИЛ (F3)

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

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

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

Спосіб 2. Список відповідностей і функції СМЕЩ (OFFSET) і ПОИСКПОЗ (MATCH)

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

 

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

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

=СМЕЩ($A$1;ПОИСКПОЗ($G$7;$A:$A;0)-1;1;СЧЁТЕСЛИ($A:$A;$G$7);1)

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

Посилання повинні бути абсолютними (зі знаками $). Після натискання Enter до формули будуть автоматично додані імена аркушівю.

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

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

Таким чином:

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

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

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