lightanddesign.ru
You might also like
Иерархические данные необходимо создать для выполнения следующих задач
- Отдел — сотрудники отдела. Если вы выбираете отдел из списка всех отделов компании, необходимо динамически создать список, содержащий всех сотрудников этого отдела (двухуровневая иерархия).
- Город — Улица — Номер дома. При вводе жилого адреса необходимо выбрать город из списка городов, затем улицу из списка всех улиц в этом городе, затем номер дома из списка всех домов на этой улице (трехуровневая иерархия).
В этой статье рассматриваются многоуровневые связанные списки. О двухуровневом связанном списке, или просто связанном списке, см. статью о связанных списках и расширяемых связанных списках. Поскольку это одна из более сложных статей на Excel2.com, она является хорошей отправной точкой для ознакомления с этими темами. Используя условия проверки списка, реализуйте многоуровневые связанные списки с помощью инструмента Data Validation (Data/Processing Data/Data Validation). Создайте многоуровневый связный список на конкретном примере.
Примечание: Многоуровневый связный список, описанный в этой статье, на самом деле следует называть трехуровневым связным списком, поскольку сложно создать четырехуровневый связный список, используя описанный здесь подход. Если вам необходимо создать структуру с более чем четырьмя уровнями, см. раздел Многоуровневые списки ссылок для типов предшественников/последователей.
Постановка задачи
Имеется список регионов. Для каждого региона имеется список стран. Для каждой страны есть список городов.
Пользователь должен иметь возможность выбрать регион и в ячейке рядом с регионом выбрать из выпадающего списка страну, относящуюся к этому региону. В соседнем поле пользователь должен иметь возможность выбрать нужный город из этой страны (см. образец файла).
В окончательном виде трехуровневый связный список должен работать следующим образом.
Сначала с помощью выпадающего списка выберите, например, регион "США".
Затем выберите страну "США" в области "США".
Список стран в выпадающем списке также будет включать только страны из региона ‘США’, выбранного на предыдущем шаге.
Наконец, выберите город "Атланта" из страны "США".
Кроме того, список городов в выпадающем списке будет включать только города из страны, выбранной на предыдущем шаге, то есть из региона "США".
Решение
Теперь приступайте к созданию трехуровневого списка ссылок. В рабочем листе Таблица создайте таблицу, содержащую данные, введенные для трехуровневого связанного списка.
На листе "Страна" настройте список регионов и список стран.
Обратите внимание, что названия регионов (диапазон A2:A12 в листе "Страны") должны точно совпадать с заголовками столбцов, содержащих соответствующие названия стран (B1:L 1).
Это требование накладывается формулой (см. статью о транспонировании). = АДРЕС (АДРЕС (СТРОКА ($ A $ 1) — СТОЛБЕЦ ($ A $ 1) + СТОЛБЕЦ (); 1)))
Используется для форматирования заголовков столбцов. Введите это в ячейку диапазона B1:L1.
Поместите список стран и городов на лист с городами.
Откуда взят список стран на городском листе? Конечно, после того, как названия стран будут заполнены на листе стран, вам нужно чудесным образом перенести их на лист городов. Используйте формулу для организации этого чудесного движения. Используя решение, описанное в статье Объединение списков, настройте список стран в столбце A листа Cities. Значения для этого списка берутся из поля Diap_Countries (которое должно быть создано заранее с помощью Менеджера имен). Пространство имен Diap_trans формируется по следующей формуле
Для создания списка стран вам также понадобится тип Rows_Columns_Countries.
Окончательное выражение для столбца A городского листа имеет следующий вид.
Они составляют список стран, которые нам нужны.
Далее создадим динамический диапазон и выпадающий список имен регионов. Для этого:.
- Нажмите кнопку меню Назначить имя (Формула / Определенное имя / Назначить имя).
- Введите ‘Region’ в поле Name, и
- Введите формулу в поле Диапазон.
Формула подсчитывает количество элементов в столбце A листа страны (функция SCAN ()), устанавливает ссылку на последний элемент в столбце (функция INDEX ()) и формирует регион, содержащий все значения в регионе. Колонка A не может содержать пробелов.
Аналогично, создайте динамический диапазон List_Countries, чтобы создать выпадающий список, содержащий названия стран.
Давайте создадим тип Position_region для указания выбранной пользователем позиции региона внутри созданного выше региона.
Поскольку в этой формуле используется относительная адресация, важно активировать ячейку B5 в рабочем листе перед созданием формулы.
Аналогично, создайте именованный тип для указания местоположения страны, выбранной пользователем в диапазоне List_Country = POST (table!B5; List_Country; 0). Перед созданием формулы ячейка C5 должна быть активной на листе таблицы.
Создадим номинальную константу MaxTrans равную 20 и MaxCities равную 30. Эти константы представляют собой максимальное количество стран в регионе и максимальное количество городов в стране. Эти значения являются произвольными и могут быть изменены.
Создайте регион с именем Selected_Region и определите регион с листом стран, содержащим страны выбранного региона.
Например, если выбран регион Америка, функция CHANGE() возвращает ссылку на диапазон стран. $ B $ 2: $ B $ 20
Создайте аналогичную Selected_Country для определения региона на листе Cities, который содержит города выбранного региона. = MESH (cities!$ A $ 2 ;; Country_position; MaxCities)
Создайте два последних именованных типа Страна и Город: = MEMBERSHIP (country!$ A $ 2 ;; Region_position; SCRESHIP (Selected_Region)) Country_position; ACCOUNT (Selected_Country))
Используйте эти типы для того, чтобы в выпадающем списке не появлялись пустые строки. Наконец, создайте выпадающий список, связанный с ячейкой столбца Страна на рабочем листе.
Также создайте связанный выпадающий список для ячейки столбца Город (диапазон C5:C22, введите в поле Источник: = Город).
На листе сетки после выбора региона и страны теперь можно выбрать город.
Чтобы добавить новый регион и его страну, просто введите новый регион в колонку А (страна), и соответствующий заголовок автоматически появится в строке 1. Чтобы добавить новый город, найдите в первой строке необходимое для него название. Страна (появляется там автоматически после добавления страны в лист страны). В этом заголовке введите название города.
Совет: В этой статье города (и страны) размещены в разных колонках. Обычно значения одного типа располагаются в колонках (списках). В табличном многоуровневом связанном списке MS Excel все исходные данные размещаются на одном листе, а данные одного типа (название города) размещаются в столбце. Это облегчает формулировку типов и позволяет создавать списки с большим количеством иерархических уровней (4-6). Чтобы добавить новый регион и его страну, просто введите новый регион в колонку A (страна), и соответствующий заголовок автоматически появится в строке 1. Чтобы добавить новый город, найдите название нужной страны в первой строке (оно автоматически появится после слова "добавить страну"). В этом заголовке введите название города.
