Связанный список в EXCEL

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

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

Создание Связанного списка на основе Проверки данных рассмотрим на конкретном примере.

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

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

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

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

Присвоим имена диапазонам, содержащим Регионы и Страны (т.е. создадим Именованные диапазоны ). Быстрее всего это сделать так:

• выделитьячейки А1:Е6 на листе Списки (т.е. диапазон, охватывающий все ячейки с названиями Регионов и Стран );
• нажать кнопку «Создать из выделенного фрагмента» (пункт меню Формулы/ Определенные имена/ Создать из выделенного фрагмента );
• Убедиться, что стоит только галочка «В строке выше»;
• Нажать ОК.

Проверить правильность имени можно через Диспетчер Имен ( Формулы/ Определенные имена/ Диспетчер имен ). Должно быть создано 5 имен.

Можно подкорректировать диапазон у имени Регионы (вместо =списки!$A$2:$A$6 установить =списки!$A$2:$A$5 , чтобы не отображалась последняя пустая строка)

На листе Таблица , для ячеек A 5: A 22 сформируем выпадающий список для выбора Региона .

Теперь сформируем выпадающий список для столбца Страна (это как раз и будет желанный Связанный список ).

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

Тестируем. Выбираем с помощью выпадающего списка в ячейке A 5 Регион – Америка , вызываем связанный список в ячейке B 5 и балдеем – появился список стран для Региона Америка : США, Мексика …

Теперь заполняем следующую строку. Выбираем в ячейке A 6 Регион – Азия , вызываем связанный список в ячейке B 6 и опять балдеем: Китай, Индия …

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

Конечно, можно вручную откорректировать диапазоны или даже вместо Именованных диапазонов создать Динамические диапазоны . Но, при большом количестве имен делать это будет достаточно трудоемко. Кроме того, при добавлении новых Регионов придется вручную создавать именованные диапазоны для их Стран .

Чтобы не создавать десятки имен, нужно изменить сам подход при построении Связанного списка . Рассмотрим этот подход в другой статье: Расширяемый Связанный список .

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

Специальная вставка в Excel — Самые полезные функции Транспонирование в Excel. Раздел Операция.

• на вкладке Формулы в группе Определенные имена выберите Присвоить имя ;
• В поле Имя введите: Месяц ;
• В поле Область выберите Книжный лист ;
• В поле Range введите формулу =MEMBERSHIP(sheet1!$B$5;;;1;ACCOUNT(sheet1!$B$5:$I$5)).
• Нажмите OK.

Задание 4. Как и раньше, выберите B2: C3. Затем наведите курсор мыши на рамку выбранного диапазона. Нажмите клавишу CTRL на клавиатуре. Рядом с курсором мыши появится значок «+». Удерживая нажатой левую кнопку мыши и клавишу CTRL, перетащите диапазон на нужный адрес (F3: G4). Отпустите сначала мышь, а затем CTRL.

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

Связи ячеек: консолидация и обеспечение согласованности данных | Справочные статьи Smartsheet

Если после выбора диапазона A1: D4 вы дважды щелкните инструмент «Формат по образцу», это действие исправит нажатие кнопки. Теперь вы можете щелкать разные ячейки несколько раз, чтобы одним щелчком вставить формат в несмежные диапазоны.

1. Щёлкните ячейку, содержащую входящую связь (или удерживайте нажатой кнопку мыши и потяните рамку, чтобы выделить группу ячеек).
2. Щёлкните ячейку правой кнопкой мыши и выберите пункт Удалить ссылку.

Типы ячеек, не допускающие связывания

• ячейки с формулами в столбцах;
• даты окончания;
• предшественники;
• сводные ячейки в родительских строках («Дата начала», «Дата окончания», «% выполнено»);
• даты начала с зависимостью.

На листе Таблица , для ячеек A 5: A 22 сформируем выпадающий список для выбора Региона .