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

Создадим выпадающий список, содержимое которого зависит от значений другой ячейки.


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

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

В этой статье рассмотрен только двухуровневый связанный список . Многоуровневый связанный список рассмотрен в одноименной статье Многоуровневый связанный список . Создание иерархических структур данных позволяет избежать неудобств выпадающих списков связанных со слишком большим количеством элементов. Связанный список можно реализовать в EXCEL, с помощью инструмента Проверка данных ( Данные/ Работа с данными/ Проверка данных ) с условием проверки Список (пример создания приведен в данной статье) или с помощью элемента управления формы Список (см. статью Связанный список на основе элемента управления формы ).

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

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

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

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

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

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

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

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

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

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

  • выделяем ячейки A 5: A 22 ;
  • вызываем инструмент Проверка данных ;
  • устанавливаем тип данных – Список ;
  • в поле Источник вводим: =Регионы

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

  • выделяем ячейки B 5: B 22 ;
  • вызываем инструмент Проверка данных;
  • устанавливаем тип данных – Список ;
  • в поле Источник вводим: =ДВССЫЛ(A5)

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

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

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

Необходимо помнить, что в именах нельзя использовать символ пробела. Поэтому, при создании имен, вышеуказанным способом, он будет автоматически заменен на нижнее подчеркивание «_». Например, если вместо Америка (ячейка В1 ) ввести « Северная Америка » (соответственно подкорректировав ячейку А2 ), то после нажатия кнопки Создать из выделенного фрагмента будет создано имя «Северная_Америка». В этом случае формула =ДВССЫЛ(A5) работать не будет, т.к. при выборе региона « Северная Америка » функция ДВССЫЛ() не найдет соответствующего имени. Поэтому формулу можно подкорректировать, чтобы она работала при наличии пробелов в названиях Регионов : =ДВССЫЛ(ПОДСТАВИТЬ(A5;" ";"_")) .

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

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

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


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

Аноним, 29 ноября 2015 г.
есть следующая задача - есть условно общий список игр для списка сайтов. Необходимо создать двухуровневый список, где в зависимости от выбранного сайта будет доступен свой набор игр. Как сделать двухуровневый список - понятно, а вот как красиво прописать зависимость для каждого сайта и игр - нет (вариант просто для каждого бренда прописать свой список игр - не подходит, так как одна игра может быть доступна для нескольких сайтов, не хочется плодить списки). Заранее спасибо за помощь!
Аноним, 29 ноября 2015 г.
Аноним, 3 октября 2017 г.
Очень понятно написано. Лучшее объяснение. Все получилось. Спасибо.
Аноним, 9 октября 2017 г.
Не получается создать зависимый выпадающий список с числовыми значениями - почему-то везде приводятся примеры с текстовыми значениями в ячейках. В прилагаемом файле выпадающий список каким-то чудом работает, но как - не пойму (ячейки с рабочим и нерабочим выпадающим списком помечены комментариями). Прошу разъяснить мне, почему в одном и том же файле в одном случае зависимый выпадающий список с числовыми значениями работает, а в другом случае - нет. Что я делаю не так? Выслать файл через приложение "В контакте" не получается. Выслал на admin@excel2.ru. Помогите, пожалуйста!
Михаил, 10 октября 2017 г.
шлите на creator@excel2.ru админ видно в отпуске )))))))))
Аноним, 20 февраля 2018 г.
полная муть ничего не понятно и не работает. С какого перепугу мы создаём диапазоны с А5 и В5 если в листе Списки создавали данные в ячейках с 1 по 5?
Аноним, 11 февраля 2020 г.
всё круто в этом примере, но он не будет работать с наименованиями диапазонов из более-чем-одного-слова (пробелы в имени диапазона недопустимы)☹
Михаил, 12 февраля 2020 г.
Вы невнимательно прочитали статью. В абзаце перед разделом "Теперь о недостатках" сказано как настроить список для ввода значений с пробелами
(только для авторизованных пользователей)

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