Многоуровневый (3+) связанный список (типа предок-родитель) в MS EXCEL

Для сложных иерархических структур с тремя и более уровнями создадим Многоуровневый связанный список типа Предок-Родитель. Теперь структуры типа: Регион-Страна-Город-Улица можно создавать в MS EXCEL.

В статье Многоуровневый связанный список в MS EXCEL мы уже создавали многоуровневый список. Но, у того списка было 2 недостатка:

  • слишком сложные формулы;
  • максимальное количество уровней - 3.

Предлагаемое ниже решение лишено этих недостатков, но исходный список, содержащий названия стран, городов и улиц получается немного громоздким и его нужно наполнять придерживаясь определенных правил.

Решение

В результате мы должны получить вот такой 4-х уровневый связанный список:

Выпадающие списки будем формировать с помощью одной большой Именованной формулы:

=ЕСЛИ(Таблица!D$3=Списки!$F$5;
СМЕЩ(Списки!$D$4;1;0;СЧЁТЕСЛИ(Списки!$B$5:$B$30;0));
СМЕЩ(Списки!$D$4;ПОИСКПОЗ(Таблица!C10;Списки!$C$5:$C$30;0);0;СЧЁТЕСЛИ(Списки!$C$5:$C$30;Таблица!C10)))

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

Для наглядности уровни выделены Условным форматированием.

При заполнении таблицы на листе Списки требуется группировать значения в столбце Родитель. Т.е. если мы начали заполнять все страны Америки, то нужно полностью закончить этот список, затем переходить к следующему региону. Выполнение этого требования необходимо для правильной работы вышеуказанной формулы.

Рассмотрим как формируется выпадающий список в ячейке D10 на листе Таблица (города США). Значение США находится в ячейке С10.

  • функция СМЕЩ() выбирает из столбца D (Потомки) значения для создания выпадающего списка на основании значения из ячейки С10 (США);
  • выражение ПОИСКПОЗ(Таблица!C10;Списки!$C$5:$C$30;0) возвращает номер позиции первой ячейки в столбце С, содержащей значение США. Это позиция номер 10. Именно с этой позиции будет формироваться наш выпадающий список;
  • теперь для выпадающего списка осталось указать его длину. Для этого используем выражение СЧЁТЕСЛИ(Списки!$C$5:$C$30;Таблица!C10).

Для ввода формулы выделите ячейку D10, вызовите Диспетчер имен, и введите формулу:

Формулу можно протянуть вправо и вниз (или вверх и вправо): выпадающие списки всех уровней будут сформированы автоматически. Функция ЕСЛИ() нужна для формирования списка самого верхнего уровня иерархии - у него нет Родителя.

Чтобы добавить новое значение, например, новую Страну с городами и улицами, нужно добавить в таблицу на листе Списки нужное количество строк. Чтобы не исправлять формулу, строки следует добавлять, вставляя их между уже существующими строками. 

Теперь представим ситуацию, что после заполнения таблицы с помощью выпадающих списков, было изменено значение ячейки С10. Например, вместо США выберем Мексика. Естественно, значение в ячейке D10 не изменится (в ней останется один из городов США). Чтобы подсказать пользователю об ошибке, используем Условное форматирование.

Формула в правиле Условного форматирования ищет в таблице на листе Списки пару Родитель-Потомок. Если такая пара не найдена, то ячейка выделяется красной заливкой.

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

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