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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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