Расширяемый Связанный список в MS EXCEL

В статье рассмотрен улучшенный вариант Связанного списка.

В случае, если содержание Связанного списка зависит от большого количества значений ячеек (>5), то нам потребуется создать такое же количество Именованных диапазонов. Занятие это мало увлекательное, поэтому лучше настроим Связанный список так, чтобы для него было удобно добавлять новые перечни элементов (См. файл примера).

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

Для обеспечения этого требования введем в ячейку B1 листа Списки формулу
=ДВССЫЛ(АДРЕС(СТРОКА($A$1)-СТОЛБЕЦ($A$1)+СТОЛБЕЦ();1))

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

Модифицируем файл примера из статьи Связанный список. Сначала удалим через Диспетчер имен (Формулы/ Определенные имена/ Диспетчер имен) все созданные ранее Имена.

Создадим Динамический диапазон для формирования Выпадающего (раскрывающегося) списка содержащего названия Регионов. Для этого необходимо:

  • нажать кнопку меню «Присвоить имя» (Формулы/ Определенные имена/ Присвоить имя);
  • в поле Имя ввести Регионы;
  • в поле Диапазон ввести формулу

=списки!$A$2:ИНДЕКС(списки!$A:$A;СЧЁТЗ(списки!$A:$A))

  • Нажать ОК.

Формула =списки!$A$2:ИНДЕКС(списки!$A:$A;СЧЁТЗ(списки!$A:$A)) подсчитывает количество введенных Регионов в столбце А на листе Списки (функция СЧЁТЗ()) и определяет ссылку на последний элемент в столбце (функция ИНДЕКС()), тем самым формируется диапазон, содержащий все значения Регионов. Пропуски в столбце А не допускаются. Также для формирования динамического диапазона можно использовать функцию СМЕЩ().

Теперь создадим Именованную формулу Позиция для определения позиции, выбранного пользователем региона, в созданном выше диапазоне Регионы =ПОИСКПОЗ(A5;Регионы;0). Т.к. в формуле использована относительная адресация, то важно перед созданием формулы сделать активной ячейку B5 на листе Таблица. Формула вернет порядковый номер выбранного Региона. Он же является порядковым номером столбца, содержащим названия стран, выбранного Региона в строке 1.

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

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

В принципе, можно в качестве источника Связанного списка для ячеек из столбца B на листе Таблица указать =Выбранный_Регион, но тогдав Выпадающем списке будут появляться пустые строки. Для исключения этих строк, наконец, создадим последнюю Именованную формулу Страны для более точного определения диапазона на листе Списки, содержащего страны выбранного Региона:
 =СМЕЩ(списки!$A$2;;Позиция;СЧЁТЗ(Выбранный_Регион))

Теперь через Диспетчер имен (Формулы/ Определенные имена/ Диспетчер имен) можно посмотреть все созданные выше Имена.

Наконец сформируем выпадающий список (связанный список) для ячеек из столбца Страна налисте Таблица.

  • выделяем диапазон B5:B22;
  • вызываем инструмент Проверка данных,
  • устанавливаем тип данных Список,
  • в поле Источник вводим: =Страны.

Тестируем. Выбираем с помощью выпадающего списка в ячейке A5 РегионАмерика, вызываем связанный список в ячейке B5 и балдеем – появился список стран для Региона Америка: США, Мексика… В отличие от Связанного списка – без пустых строк.

Теперь заполняем следующую строку. Выбираем в ячейке A6 Регион Африка, вызываем связанный список в ячейке B6 и опять балдеем: Чад, Танзания… Опять без пустых строк.

А теперь – основное отличие от Связанного списка: для добавления новых Регионов и их Стран теперь достаточно ввести новый Регион в столбец A (лист Списки), в строке 1 автоматически отобразится соответствующий заголовок. Под появившимся заголовком в строке 1 введите страны нового Региона. И все!

Частными случаями Связанного списка являются:

  • Динамический выпадающий список: в этой структуре перечень элементов для выпадающего списка только один, но его содержание зависит от значений нескольких ячеек. Эта структура позволяет исключать из выпадающего списка уже введенные значения, что обеспечивает гарантированный ввод неповторяющихся значений.
  • Вложенный связанный список: в этой структуре все перечни элементов для выпадающего списка содержатся в одном столбце, а не в разных, как в обычном связанном списке. Значения в этом столбце должны быть отсортированы. Пример: список сотрудников компании, отсортированный в алфавитном порядке. Перечень элементов для выпадающего списка формируется в зависимости от выбранной буквы алфавита.
  • Запрос на выборку: в этой структуре все перечни элементов для связанного списка содержатся в одной таблице (в базе данных). Сам связанный список реализован не в виде выпадающего списка, а формулами на листе EXCEL. Пользователь задает в определенной ячейке критерий, на основе которого из базы данных выбираются строки, удовлетворяющие этому критерию. Выбранные строки помещаются в отдельную таблицу.

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (4 оценок)