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

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

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

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

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

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

Решение

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

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

Эту формулу можно посмотреть через Диспетчер имен (имя формулы - Выбор), но так как формула использует относительную адресацию в ссылках на ячейки Таблица!B$7 и Таблица!A8, то для ее просмотра нужно предварительно выделить ячейку В8 на листе Таблица. О том как работает эта формула см. в разделе статьи ниже. 

ПРИМЕЧАНИЕ: Функция ЕСЛИ() нужна для формирования списка самого верхнего уровня иерархии - у него нет Родителя.

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

Нажмите ОК. 

Опять выделите ячейку B8, сформируйте выпадающий список на основе Проверки данных. В поле Тип данных укажите Список, в поле Источник введите формулу =Выбор.

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

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

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

Заполнение таблицы на листе Списки нужно производить следующим образом:

  • Столбец Номер уровня содержит числовые коды уровней. Самый верхний уровень (0) содержит название Регионов. Можно создать любое количество уровней;
  • Столбец Родитель содержит названия из столбца Потомок более верхнего уровня;
  • Столбец Потомок связывает значений из 2-х соседних уровней. Нужно иметь ввиду, что если мы начали заполнять, например, все страны Америки, то нужно полностью закончить этот список, затем переходить к следующему региону. Выполнение этого требования необходимо для правильной работы вышеуказанной формулы. Выпадающие списки формируются именно из этого столбца.

Как работает эта формула?

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

  • Выражение ЕСЛИ(Таблица!B$7=Списки!$F$5; проверяет заполняем ли мы столбец Регионы (самый верхний уровень с номером 0) или нет. В данном случае выражение вернет значение ИСТИНА;
  • Так как предыдущее выражение =ИСТИНА, то работает первое условие функции ЕСЛИ(), т.е. выражение СМЕЩ(Списки!$D$4;1;0;СЧЁТЕСЛИ(Списки!$B$5:$B$30;0))
  • функция СМЕЩ() выбирает из столбца D (Потомки) все значения, у которых уровень равен 0. Из этих значений формируется выпадающий список.

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

В этом случае формула будет несколько другая, т.к. в ней сработают относительные ссылки:

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

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

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

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

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

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

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