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

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


Комментарии
Только для авторизованных пользователей

Аноним, 21 мая 2020 г.
Спасибо автору! Прекрасный способ. Единственный неожиданный момент, с которым могут столкнуться пользователи, при попытке написать ссылку для "Выбор" где-то в блокноте по аналогии с примером автора (для последующей вставки в диалоговое окно), не забывайте: если имена ваших листов содержат пробелы, то вам понадобится заключить эти имена в апострофы при написании ссылки.
Михаил, 24 мая 2020 г.
Спасибо за комментарий. У программистов есть простое правило - никогда не используйте пробелы в наименованиях тех объектов, на которые можно сделать ссылку. Вместо пробелов используют часто подчеркивание _ или просто пишут слитно. В нашем случае ссылка выглядит как =ЕСЛИ(Таблица!D$7=... Если название листа изменить, например написать Таб лица, то формула будет выглядеть так =ЕСЛИ('Таб лица'!C$7=... Добавили апострофы.
(только для авторизованных пользователей)

© Copyright 2013 - 2020 Excel2.ru. All Rights Reserved