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

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


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

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

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

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

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

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

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

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

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

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

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

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

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

Решение



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

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

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

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

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

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

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

=СМЕЩ(страны!$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;;Позиция_страны;СЧЁТЗ(Выбранная_Страна))

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

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

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

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

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

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


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

Аноним, 23 октября 2016 г.
Здравствуйте. Я начинаю осваивать возможности Exsel. Двухуровневый список - уже освоила (материал, цвета). Но мне нужно создать многоуровневый список (материал, цвета, цена). Попыталась разобраться в создании многоуровневого списка в Вашей версии. Но подача материала очень сложная, непонятная и со множеством ссылок. Человек хорошо владеющий возможностями Exsel возможно и разберётся, но человек начинающий осваивать таблицу - запутается. Можете-ли пошагово без ссылок на см. туда или см. туда подсказать создание 3-х уровневой таблицы для новичков?
Михаил, 23 октября 2016 г.
Вы правы, этот материал не для новичков. Если нужно использовать 3-х уровневый список, то можно взять готовое решение и использовать его без глубокого понимания. Расписывать эту статью для новичков мне, честно говоря, скучновато. По большому счету - это бесполезно. Чтобы освоить этот материал, нужно неплохо разбираться в EXCEL.
Аноним, 24 октября 2016 г.
Михаил, здравствуйте. Все "профи" - когда-то были новичками. И, по-моему, очень хорошо - когда человек узнает новое, совершенствует свои навыки и развивается. И, по-моему, очень плохо - когда человек молчком "топчется" на месте, не развивается, а деградирует. Но - это только мои мысли. Я рассматривала вариант с использованием уже существующей таблицы, но у меня не самостоятельная таблица, а таблица в таблице. В любом случае - спасибо за ответ.
Аноним, 18 декабря 2016 г.
А как же тогда быть новичкам? Очень сложно найти толковый сайт, чтобы просто и доступно, да еще и с файлами примеров, где можно потренироваться. Я с практически с нуля изучаю Excel, ваш сайт единственный, где я мало по малу продвигаюсь. По-этому не бросайте "новичков", подумайте о том что благодаря Вам, они тоже могут стать "профи".
Аноним, 25 января 2017 г.
Поправьте: Создадим аналогичный диапазон Выбранная_Страна..... В конце поправьте(содержащего города выбранной страны) И ещё Пропишите что название Список_Регионов, там где создаём его(аналогично то аналогично, но большинство людей читают статью бегло и могу пропустить как назвать вновь созданный диапазон) У меня вопрос такого плана, есть возможность сделать Список который будет содержать несколько уровней глубины, заранее неизвестно какой глубины будет список, с возможностью добавления/удаления пунктов в список Допустим категории: -Авто -бензин -Ремонт - работа - запчасти -ТО
Михаил, 25 января 2017 г.
Я думаю, что 3 уровня - это предел для EXCEL, т.к. их сложно создавать и в случае ошибки очень трудно искать ее причину. Если есть потребность в более высоком уровне вложения, а тем более с настраиваемой глубиной, то скорее всего Вам нужно мигрировать в Access, там нет предела для создания таких списков. Список, что приведен у Вас, похож на всего 1 категорию "тип затрат".
Аноним, 11 января 2018 г.
Добрый день! Моя задача выбрать одно значение из всплывающего списка и чтобы данные, связанные с этим значением заняли свои места. Конкретный пример - выбор человека, а паспортные данные (1.серия и номер 2. кем выдан 3.дата выдачи) заполнили соответствующие ячейки. Хотелось бы так упростить свою работу... Спасибо.
Аноним, 11 марта 2020 г.
Здравствуйте! Подскажите, пожалуйста, как правильно называется и где в вашем блоге (или вообще) можно найти обучалку по следующей задаче: нужно чтобы при нажатии строки первого уровня (тренировочные мероприятия в апреле) ниже раскрывались и были доступны для добавления строчки второго уровня (строчки с данными по тренеру, количеству спортсменов, суммами смет, и т.д.). Смысл в том, чтобы можно было считать суммы смет по направлениям и при необходимости посмотреть кто именно куда ездил по этим сметам. Зрительно помню, что эти строчки слева обозначены плюс/минус (раскрыть, свернуть). Но не помню как их делать, 10 лет прошло...Плиз, хелп )))
Михаил, 11 марта 2020 г.
Добрый день, если я Вас правильно понял, то речь идет о Сводных таблицах https://excel2.ru/articles/svodnye-tablicy-v-ms-excel
(только для авторизованных пользователей)

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