Многоуровневый (3+) связанный список (типа предок-родитель) в 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 не изменится (в ней останется один из городов США). Чтобы подсказать пользователю об ошибке, используем Условное форматирование .

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