Пов’язані (залежні) випадають списки
Спосіб 1. Функція ДВССИЛ (INDIRECT)
Візьмемо, наприклад, ось такий список моделей автомобілів Toyota, Ford і Nissan:
При завданні імен пам'ятайте про те, що імена діапазонів в Excel не повинні містити пробілів, розділових знаків і починатися обов'язково з літери. Тому якби в одній з марок автомобілів мав бути присутнім пробіл (наприклад Ssang Yong), то його довелося б замінити в осередку і в імені діапазону на нижнє підкреслення (тобто Ssang_Yong).
Тепер створимо другий список, що випадає, в якому будуть відображатися моделі обраної в першому списку марки. Також як в попередньому випадку, відкрийте вікно Перевірки даних. але в поле Джерело потрібно буде ввести ось таку формулу:
Усе. Після натискання на ОК вміст другого списку буде вибиратися на ім'я діапазону, обраного в першому списку.
Мінуси такого способу:
- В якості вторинних (залежних) діапазонів не можуть виступати динамічні діапазони задаються формулами типу зміщені (OFFSET). Для первинного (незалежного) списку їх використовувати можна, а ось вторинний список повинен бути визначений жорстко, без формул. Однак, це обмеження можна обійти, створивши відсортований список відповідностей марка-модель (див. Спосіб 2).
- Імена вторинних діапазонів повинні збігатися з елементами первинного списку. Тобто якщо в ньому є текст з прогалинами, то доведеться їх замінювати на підкреслення за допомогою функції ПІДСТАВИТИ (SUBSTITUTE). тобто формула буде виглядати як = ДВССИЛ (ПІДСТАВИТИ (F3; ""; "_"))
- Треба руками створювати багато іменованих діапазонів (якщо у нас багато марок автомобілів).
Спосіб 2. Список відповідностей і функції зміщений (OFFSET) і ПОИСКПОЗ (MATCH)
Цей спосіб вимагає наявності відсортованого списку відповідностей марка-модель ось такого виду:
Для створення первинного списку можна марок можна скористатися звичайним способом, описаним вище, тобто
- дати ім'я діапазону D1: D3 (наприклад Марки) за допомогою Диспетчера імен (Name Manager) з вкладки Формули (Formulas) або в старих версіях Excel - через меню Вставка - Ім'я - Присвоїти (Insert - Name - Define)
- вибрати на вкладці Дані (Data) команду Перевірка даних (Data validation)
- вибрати зі списку варіант перевірки Список (List) і вказати в якості Джерела (Source) = Марки або просто виділити осередки D1: D3 (якщо вони на тому ж аркуші, де список).
А ось для залежного списку моделей доведеться створити іменований діапазон з функцією зміщений (OFFSET). який буде динамічно посилатися тільки на осередки моделей певної марки. Для цього:
Посилання повинні бути абсолютними (зі знаками $). Після натискання Enter до формули будуть автоматично додані імена аркушів - не лякайтеся :)
Функція зміщений (OFFSET) вміє видавати посилання на діапазон потрібного розміру, зрушений відносно початкової комірки на задану кількість рядків і стовпців. У більш зрозумілою варіанті синтаксис цієї функції такий:
= Зміщений (начальная_ячейка; сдвіг_вніз; сдвіг_вправо; размер_діапазона_в_строках; размер_діапазона_в_столбцах)
- початкова осередок - беремо перший осередок нашого списку, тобто А1
- сдвіг_вніз - нам вважає функція ПОИСКПОЗ (MATCH). яка, попросту кажучи, видає порядковий номер комірки з обраної маркою (G7) в заданому діапазоні (стовпці А)
- сдвіг_вправо = 1, тому що ми хочемо послатися на моделі в сусідньому стовпці (В)
- размер_діапазона_в_строках - обчислюємо за допомогою функції СЧЕТЕСЛІ (COUNTIF). яка вміє підрахувати кількість зустрілися в списку (стовпці А) потрібних нам значень - марок авто (G7)
- размер_діапазона_в_столбцах = 1, тому що нам потрібен один стовпець з моделями
У результаті повинно вийти щось на зразок цього:
Залишилося додати, що випадає на основі створеної формули до комірки G8. Для цього:
- виділяємо клітинку G8
- вибираємо на вкладці Дані (Data) команду Перевірка даних (Data validation) або в меню Дані - Перевірка (Data - Validation)
- зі списку вибираємо варіант перевірки Список (List) і вводимо в якості Джерела (Source) знак одно і ім'я нашого діапазону, тобто = Моделі