Пов’язаний список, що розширюється в MS EXCEL

У статті розглянуто покращений варіант Пов'язаного списку.

Накладемо обмеження на порядок заповнення переліків країн для регіонів на аркуші Списки. Тепер порядок розташування назв Регіонів у стовпціA точно повинен відповідати порядку заголовків переліків Країн у рядку1 (у попередньому випадку, див. статтю Пов'язаний список, цього не потрібно, головне, щоб все назви Регіонів були присутні у заголовках, а порядок був не важливий).

Для забезпечення цієї вимоги введемо в коміркуB1 аркушаСписки формулу =ДВССИЛ(АДРЕСА(РЯДКУ($A$1))-Стовпець($A$1 )+Стовпець();1))

Тепер назви заголовків стовпців автоматично братимуться зі стовпцяА і однозначно відповідатимуть назвам Регіонів.

розширюється

Модифікуємо файл прикладу із статті Пов'язаний список. Спочатку видалимо через Менеджер імен ( Формули/ Визначені імена/ Менеджер імен ) всі створені раніше Імена.

Створимо Динамічний діапазон для формування Випадаючого списку, що містить, назви Регіонів. Для цього необхідно:

  • натиснути кнопку меню "Присвоїти ім'я" (Формули / Певні імена / Присвоїти ім'я);
  • у полі Ім'я ввести Регіони;
  • у полі Діапазон ввести формулу

Формула = списки!> (функція РАХУНОК() ) і визначає посилання на останній елемент у стовпці (функція ІНДЕКС() ), тим самим формується діапазон, що містить усі значення Регіонів. Перепустки в стовпціА не допускаються. Також для формування динамічного діапазону можна використовувати функцію ЗМІЩ().

Створимо іменовану константу МаксСтран рівну 20. Константа відповідаємаксимальну кількість країн у регіоні (константу ми встановлюємо довільно).

Створимо Іменований діапазон Вибраний_Регіон для визначення діапазону на аркушіСписки, що містить країни вибраного Регіону: =ЗМІЩ(списки!$A$2;;Позиція;МаксСтран) . Тепер, наприклад, при виборі регіону Америка функція ЗМІЩ() поверне посилання на діапазон списків!$B$2:$B$20 , що містить усі країни цього Регіону.

В принципі, можна як джерело Пов'язаного списку для осередків зі стовпцяB на аркушіТаблиця вказати =Вибраний_Регіон , але тоді у списку, що випадає, будуть з'являтися порожні рядки. Для виключення цих рядків, нарешті, створимо останню Іменовану формулу Країни для більш точного визначення діапазону на аркушіСписки, що містить країни вибраного Регіону: =СМЕЩ(списки!$A$2;;Позиція;РАХУНОК(Вибраний_Регіон) ))

Тепер через Диспетчер імен можна побачити всі створені вище Імена.

excel

Нарешті сформуємо список, що випадає (пов'язаний список) для осередків зі стовпця Країна на листіТаблиця.

  • виділяємо діапазонB5:B22 ;
  • викликаємо інструмент Перевірка даних,
  • встановлюємо тип даних Список,
  • у полі Джерело вводимо: = Країни.

Тестуємо. Вибираємо за допомогою списку, що випадає в осередкуA5 Регіон – Америка, викликаємо пов'язаний список у осередкуB5 і балдеєм – з'явився список країн для Регіону Америка: США, Мексика… На відміну від Пов'язаного списку – без порожніх рядків.

розширюється

Тепер заповнюємо наступний рядок. Вибираємо в комірціA6 Регіон – Африка, викликаємо пов'язаний список у комірціB6 і знову балдеєм: Чад, Танзанія… Знову без порожніх рядків.

язаний

А тепер – основна відмінність від Пов'язаного списку: для додавання нових Регіонів та їх Країн тепер достатньо ввести новий Регіон у стовпецьA (листСписки ), у рядку1 автоматично з'явиться відповідний заголовок. Під заголовком у рядку1 введіть країни нового регіону. І все!

Окремими випадками Пов'язаного списку є: