Цей фокус заснований на застосуванні функції ДВССИЛ (INDIRECT) , яка вміє робити одну просту річ - перетворювати вміст будь зазначеної комірки на адресу діапазону, який розуміє Excel. Тобто, якщо в комірці лежить текст "А1", то функція видасть в результаті посилання на комірку А1. Якщо в комірці лежить слово "АВТО", то функція видасть посилання на іменований діапазон з ім'ям АВТО і т.д.
Візьмемо, наприклад, ось такий список моделей автомобілів Toyota, Ford и Nissan:
Виділимо весь список моделей Тойоти (з комірки А2 і вниз до кінця списку) і дамо цьому діапазону ім'я Toyota в меню Вставка - Ім'я - Присвоїти (Insert - Name - Define) . Потім повторимо те ж саме зі списками Форд і Ніссан, задавши відповідно імена діапазонів Ford і Nissan.
При наданні імен пам'ятайте про те, що імена діапазонів в Excel не повинні містити прогалин, розділових знаків і починатися обов'язково з літери. Тому якщо б в одній з марок автомобілів була би присутня прогалина (наприклад Ssang Yong), то його довелося б замінити в комірці і в імені діапазону на нижнє підкреслення (тобто Ssang_Yong).
Тепер створимо перший випадаючий список для вибору марки автомобіля. Виділіть порожню комірку і відкрийте меню Дані - Перевірка (Data - Validation) , потім з випадного списку Тип даних виберіть варіант Список і в полі Джерело - виділіть комірки з назвами марок (жовті комірки в нашому прикладі). Після натискання на ОК перший список, що випадає готовий:
Тепер створимо другий список, що випадає, в якому будуть відображатися моделі обраної в першому списку марки. Точно так само, як у попередньому випадку, виділіть порожню комірку і відкрийте меню Дані - Перевірка - далі Список . У полі Джерело потрібно буде ввести ось таку формулу:
= ДВССИЛ (F3)
Де F3 - адреса комірки з першим списком, що випадає - замініть на свій.
Все. Після натискання на ОК вміст другого списку буде вибиратися по імені діапазону, обраного в першому списку.
Мінуси такого способу:
Цей спосіб вимагає наявності відсортованого списку відповідностей марка-модель ось такого виду:
Для створення первинного випадаючого списку марок, можна скористатися звичайним способом, описаним вище, тобто
А ось для залежного списку моделей доведеться створити іменований діапазон з функцією СМЕЩ (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. Для цього: