Многоуровневый связанный список в MS EXCEL

Для моделирования сложных иерархических данных создадим Многоуровневый связанный список.

Потребность в создании иерархических данных появляется при решении следующих задач:

  • ОтделСотрудники отдела. При выборе отдела из списка всех отделов компании, динамически должен формироваться список, содержащий всех сотрудников этого отдела (двухуровневая иерархия);
  • Город – Улица – Номер дома. При заполнении адреса проживания из списка городов нужно выбирать город, затем из списка всех улиц этого города – улицу, затем, из списка всех домов на этой улице – номер дома (трехуровневая иерархия).

В этой статье рассмотрен Многоуровневый связанный список. Двухуровневый связанный список или просто Связанный список рассмотрен в статьях Связанный список и Расширяемый Связанный список. Материал статьи один из самых сложных на сайте Excel2.ru, поэтому необходимо для начала ознакомиться с вышеуказанными статьями.
Многоуровневый связанный список будем реализовывать с помощью инструмента Проверка данных (Данные/ Работа с данными/ Проверка данных) с условием проверки Список.
Создание Многоуровневого связанного списка рассмотрим на конкретном примере.

Примечание: Рассмотренный в этой статье Многоуровневый связанный список на самом деле правильнее назвать Трехуровневым, т.к. создать четырехуровневый связанный список, используя рассмотренный здесь подход, очень проблематично. Для тех, кому требуется создать структуру с 4-мя и более уровнями, см. статью Многоуровневый связанный список типа Предок-Родитель.

Постановка задачи

Имеется перечень Регионов. Для каждого Региона имеется свой перечень Стран. Для каждой Страны имеется свой перечень Городов.

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

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

Сначала выберем, например, Регион «Америка» с помощью Выпадающего списка.

Затем выберем Страну «США» из Региона «Америка».

Причем перечень стран в выпадающем списке будет содержать только страны из выбранного на предыдущем шаге Региона «Америка».

И, наконец, выберем Город «Атланта» из Страны «США».

Причем перечень городов в выпадающем списке будет содержать только города из выбранной на предыдущем шаге Страны, т.е. из «США».

Решение

Итак, приступим к созданию Трехуровневого связанного списка. Таблицу, в которую будут заноситься данные с помощью Трехуровневого связанного списка, разместим на листе Таблица.

Список Регионов и перечни Стран разместим на листе Страны.

Обратите внимание, что названия Регионов (диапазон А2:А12 на листе Страны) в точности должны совпадать с заголовками столбцов, содержащих названия соответствующих Стран (В1:L1).

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

с помощью которой формируются заголовки столбцов. Введем ее в диапазон ячеек В1:L1.

Список Стран и перечни Городов разместим на листе Города.

Откуда же возьмется перечень стран на листе Города? Очевидно, что после заполнения листа Страны названиями стран, необходимо, что они каким-то чудесным образом переместились на лист Города. Это чудесное перемещение организуем формулами. Список Стран сформируем на листе Города в столбце А с помощью решения приведенного в статье Объединение списков. Значения для этого списка будем брать из Именованного диапазона Диап_Стран (его нужно предварительно создать через Диспетчер имен). Именованный диапазон Диап_Стран образуем формулой:

=СМЕЩ(страны!$B$2;;;90;СЧЁТЕСЛИ(страны!$B$1:$Z$1;"?*"))

Для формирования списка Стран нам также понадобится Именованная формула Строки_Столбцы_Стран

=ЕСЛИ(ЕПУСТО(Диап_Стран);"";
--((СТОЛБЕЦ(Диап_Стран)-1)&
ВЫБОР(ДЛСТР(СТРОКА(Диап_Стран)-1);"0";"")&
СТРОКА(Диап_Стран)-1))

Окончательная формула в столбце А на листе Города выглядит так:

=ЕСЛИОШИБКА(ИНДЕКС(Диап_Стран;
--ПРАВСИМВ(НАИМЕНЬШИЙ(Строки_Столбцы_Стран;СТРОКА(Z1));2);
--ЛЕВСИМВ(НАИМЕНЬШИЙ(Строки_Столбцы_Стран;СТРОКА(Z1));
ДЛСТР(НАИМЕНЬШИЙ(Строки_Столбцы_Стран;СТРОКА(Z1)))-2));"")

сформирует необходимый нам список Стран.

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

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

=страны!$A$2:ИНДЕКС(страны!$A:$A;СЧЁТЗ(страны!$A:$A))

  • Нажать ОК.

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

Аналогичным образом создадим Динамический диапазон Список_Стран для формирования выпадающего списка содержащего названия стран:

=города!$A$2:ИНДЕКС(города!$A:$A;СЧЁТЗ(города!$A:$A))

Создадим Именованную формулу Позиция_региона для определения позиции, выбранного пользователем региона, в созданном выше диапазоне Регионы:

 =ПОИСКПОЗ(A5;Регионы;0)

Т.к. в формуле использована относительная адресация, то важно перед созданием формулы сделать активной ячейку B5 на листе Таблица.

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

Создадим Именованные константы МаксСтран равную 20 и МаксГородов равную 30. Константы соответствует максимальному количеству стран в регионе и, соответственно, максимальному количеству городов в стране. Эти значения произвольны и их можно изменить.

Создадим именованный диапазон Выбранный_Регион для определения диапазона на листе Страны, содержащего страны выбранного региона:

=СМЕЩ(страны!$A$2;;Позиция_региона;МаксСтран)

Теперь, например, при выборе региона Америка функция СМЕЩ() вернет ссылку на диапазон страны!$B$2:$B$20

Создадим аналогичный диапазон Выбранная_Страна для определения диапазона на листе Города, содержащего города выбранного региона: =СМЕЩ(города!$A$2;;Позиция_страны;МаксГородов)

Создадим две последние именованные формулы Страны и Города:
=СМЕЩ(страны!$A$2;;Позиция_региона;СЧЁТЗ(Выбранный_Регион))
=СМЕЩ(города!$A$2;;Позиция_страны;СЧЁТЗ(Выбранная_Страна))

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

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

Также создадим связанный выпадающий список для ячеек из столбца Город (диапазон С5:С22, в поле Источник вводим: =Города)

На листе Таблица после выбора Региона и Страны теперь есть возможность выбора Города.

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

СОВЕТ: В этой статье города (и страны) размещены в нескольких столбцах. Обычно однотипные значения размещают в одном столбце (списке). В статье Многоуровневый связанный список в MS EXCEL на основе таблицы все исходные данные размещены на одном листе, а однотипные данные (названия городов) - в одном столбце. Это облегчает написание формул и позволяет создать списки с большим количеством уровней иерархии (4-6).

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

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

Комментарии

Дмитрий (не проверено)

Здравствуйте! Возможна ли реализация раскрывания 2 и 3 уровня списка при наведении на первый? То-есть чтобы пользователь не раскрывал 3 списка, а нажал 1 раз и мог выбрать значение сразу из 3го уровня?
Спасибо!

KLein2911

Я не могу скачать файл с примером, помогите пожалуйства

Евгений том (не проверено)

Все замечательно. Только вот после корректировки списка стран, в выпадающем списке стран выпадает только одна страна. Как так?

Creator

Перепроверил, должно все работать, скачайте файл примера.

Гость (не проверено)

Здравствуйте. Спасибо за статью, всё получилось. Есть один существенный недостаток - при добавлении новой страны на листе города съезжают все страны. Можно ли это как-то автоматически поправить?

Creator

Из многостолбцового списка стран, на листе Страны, автоматически формируется одностолбцовый список стран на листе Города и заголовки-названия стран в первой строке. Действительно, при добавлении новых стран, если для каждой страны заполнены города, происходит смещение заголовков на листе Города в зависимости от того в какой регион была добавлена страна. Это своеобразная "плата" за автоматическое формирование одностолбцового списка стран на листе Города, в столбце А. Чтобы избавиться от смещения нужно вставить столбец А как значения, а новые страны на листе города добавлять вручную вниз списка. Или передвигать названия городов вслед за смещением заголовков стран. Хуже с удалением стран с листа Страны, на листе города исчезновение страны заметить очень сложно. Вывод: Excel не предназначен для таких сложных структур, используйте ACCESS.

IFERROR (не проверено)

Добрый день! Возможно ли вот эту функцию на закладке Города =IFERROR(INDEX(Диап_Стран;--RIGHT(SMALL(Строки_Столбцы_Стран;ROW(Z1));2);--LEFT(SMALL(Строки_Столбцы_Стран;ROW(Z1));LEN(SMALL(Строки_Столбцы_Стран;ROW(Z1)))-2));"") адаптировать для 2003 Excela? При пересохранении вашего файла в низшую версию (2003 Excel) именно она не работает, выдает ошибку.

Creator

Все дело в функции ЕСЛИОШИБКА() О том как ее заменить см. статью Функция ЕСЛИОШИБКА() в MS EXCEL

erikbond

День добрый! Подскажите, возможно ли какими то способами (формулами) сделать выпадающее меню из - к примеру всего столбца $A:$A, то есть чтобы эксель сам нашел текст, и вставил его в выпадающее меню без пробелов?
Вот что я имею ввиду:
у меня есть столбец A,и я запишу цифры (то есть имена строк), и текст который необходимо воткнуть в выпадающее меню:
_____А_____|____B____|
1
2
3___Яблоко
4___Груша
5___Вишня
6
7
8

И все, возможно ли воткнуть этот текст в выпадающее меню (указывая ТОЛЬКО весь столбец $А:$A)? И при этом чтобы пустых строчек не было в меню?! Спасибо заранее!

Creator

Попробуйте посмотреть статьи http://excel2.ru/articles/otbor-unikalnyh-znacheniy-ubiraem-povtory-iz-s... и http://excel2.ru/articles/vypadayushchiy-spisok-v-ms-excel-na-osnove-pro...

Если списки большие, то формулы массива могут притормаживать.

erikbond

В общем попробовал я ваш метод, не получается. Вот какая ситуация у меня происходит. Во всех ячейках в одном столбце, почти одна и та же формула: =ЕСЛИ($J$1=$A$3;B3;""), меняются только ссылки на ячейки. У меня есть 2 таблицы, они подписаны: таблица 5 и таблица 5а, напротив каждой из них, по несколько строчек в одном столбце.
-------А-------|-------B------|---------С--------|---------D--------|
1_|____________|______________|__________________|__________________|
2_|____________|_5.1.Дерево___|_"ТАБЛИЦА 5"[V]___|_5.1.Дерево_______|
3_|_Таблица 5__|_5.2.Куст_____|__________________|_5.2.Куст_________|
4_|____________|_5.3.Трава____|__________________|_5.3.Трава________|
---------------|______________|__________________|__________________|
5_|____________|_5а.1.Утюг____|__________________|__________________|
6_|_Таблица 5а_|_5a.2.Мел_____|__________________|__________________|
7_|____________|_5a.3.Кот_____|__________________|__________________|

Ну так вот, в столбце "С" во 2 ячейке, у меня ссылка на выпадающее меню, в котором выбирается Таблица 5, или таблица 5а. Если таблица 5 выбирается, тогда 5.1, 5.2, и 5.3 появляется по формуле написанной ниже в столбце D.
=ЕСЛИ(ЕОШИБКА(ИНДЕКС(ТАБЛИЧКИ;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$1:D1;ТАБЛИЧКИ);0)));"";ИНДЕКС(ТАБЛИЧКИ;ПОИСКПОЗ(0;СЧЁТЕСЛИ($D$1:D1;ТАБЛИЧКИ);0)))
А именнованная формула ТАБЛИЧКИ, выглядит вот так:
=СМЕЩ($B$2;;;СЧЁТЗ($B$2:$B$100))

Все вроде бы нормально, когда выбираю таблицу 5, А ВОТ когда выбираю таблицу 5а, все идет сикось накось, ТО ЕСТЬ по сути у меня должно 5а.1., 5а.2. и т.д., появится в ячейках D2 и ниже, но НЕТ, не появляется, заместо этого у меня пустота (от нулевого значения я избавился с вашей помощью)

---------А-------|-------B------|---------С--------|---------D--------| 1_|____________|______________|__________________|__________________| 2_|____________|_5.1.Дерево___|_"ТАБЛИЦА 5а"[V]__|__________________|
3_|_Таблица 5___|_5.2.Куст_____|__________________|__________________| 4_|____________|_5.3.Трава____|__________________|__________________| ---------------|______________|__________________|__________________|
дальше не стал тут вырисовывать, тоже самое что и в верхней таблице.

Помогите пожалуйста, заранее огромное спасибо Вам. Сайт просто замечательный, но что-то пока не догоняю никак :(

owand

Огромное СПАСИБО!!!! Очень помогли!

SheryshevDA

Добрый день!
Создал многоуровневый связанный список, но в столбце С (в Вашем случае города) выпадает лишь одно значение. Помогите найти ошибку.

Creator

Добрый день. Присылайте файл на creator@excel2.ru

SheryshevDA

Файл отправил

SheryshevDA

Спасибо за помощь!

Яндекс.Метрика