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

В статье Многоуровневый связанный список рассмотрен вариант 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, т.к. функция ЕСЛИОШИБКА() будет работать начиная с этой версии, чтобы обойти это ограничение читайте статью про функцию ЕСЛИОШИБКА() .