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

history

В статье Многоуровневый связанный список рассмотрен вариант 3-х уровневого списка. Элементы каждого уровня в нем располагаются на отдельных листах. Это не всегда удобно: при создании 4-х и 5-и уровневых списков - резко увеличивается число задействованных столбцов. В этой статье сформируем связанный список из единой таблицы.


Эта статья является обзорной, а не подробным изложением, т.к. пользователи, которые решаться на создание подобных "монстров" должны хорошо разбираться в Выпадающих списках (Dropdown List using Data Validation), создании имен (Names), динамических диапазонах (D ynamic Named Ranges ), формулах массива (Array Formula), понимать, что такое Связанный список (Dependent Drop-Down List) и др.

Для начала создадим таблицу, в которую будем вводить элементы всех списков (6 уровней - 6 столбцов). См. файл примера .

В этой таблице мы не будем оставлять пустых ячеек, будем повторять значения в столбцах более высокого уровня (например, слово Европа (уровень1) повторяется столько раз, сколько всего домов (уровень6)).

Выбирать значения будем как обычно с помощью Выпадающих списков .

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

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

Поясним картинку. Т.к. на материке Америке (Уровень 1) нет страны Германии (Уровень 2), то это несоответствие подсвечивается Условным форматированием . Это несоответствие появилось вследствие того, что пользователь перевыбрал значение в Уровне1 с Европа на Америка , а значение на следующем уровне, естественно, автоматически не поменялось. Это ограничение обходится в статье Связанный список в MS EXCEL на основе элемента управления формы .

Для функционирования всего этого используется несколько однотипных имен.

Однотипность имен и формул в ячейках позволяет быстро изменять количество уровней (правда, куда уж больше?).

Существенным недостатком этого подхода является, то, что в отличие от Многоуровневого списка , где заполнялась целая таблица (по строкам),

здесь заполняется только одна (!) группа связанного списка.

Примечание . Пример будет работать начиная с версии MS EXCEL 2007, т.к. функция ЕСЛИОШИБКА() будет работать начиная с этой версии, чтобы обойти это ограничение читайте статью про функцию ЕСЛИОШИБКА() .


Комментарии

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

Аноним, 25 апреля 2019 г.
А если в таблице данных изменить название в Уровне 3 для Франции не Provence1, а Кантон1- - то вся уникальность пропадает, т.к ексель отбирает Кантон1 без учета страны и соответсвенно в Уровне 4 появляются Ville1 Ville2 Деревня1_1 Деревня1_2 Деревня1_3 Как это исправить?
Михаил, 26 апреля 2019 г.
Естественно, пропадает. Для Франции и Испании у Вас один и тот же район получается. Поставьте пробел или еще какой знак, чтобы уникальность была.
(только для авторизованных пользователей)

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