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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Комментарии

Артур (не проверено)

А если сделать наоборот? То есть при выборе страны в нижней ячейке без списков выводить к какому региону принадлежит страна?

Creator

Предлагаемая Вами задача решена в статье про функцию ВПР(). Обратите внимание, что статья называется Связанный список, т.е. имеется 2 списка, значения второго списка формируются в зависимости от выбранного значения первого списка. А в Вашем примере вместоро второго списка получаем только 1 значение, а это структура Справочник, см. статью http://excel2.ru/articles/spravochnik

Артур (не проверено)

Меня интересует такой вариант. При выборе региона, вместо ещё одного списка, заполнят в определенный диапазон ячейки странами. Как это сделать?

Creator

Попробуйте посмотреть в этом разделе http://excel2.ru/gruppy-statey/vyvod-otobrannyh-znacheniy-v-otdelnyy-diapazon

Дарья (не проверено)

а в Excel 2013 такое провернуть ?

Creator

В EXCEL 2013 файл примера не тестировался, хотя я не вижу причин, чтобы в этой версии EXCEL он был неработоспособен. Скачайте файл примера и попробуйте выбрать значения из связанного списка. В случае проблем пишите на creator@excel2.ru

erikbond (не проверено)

Возможно ли сделать 2, 3, 4, 10 выпадающих списков (именно форм), но чтобы они были взаимосвязаны между собой. Что я имею ввиду: допустим у меня есть оглавление книги,
1 - Земля; 2 - Луна; 3 - Марс и т.д.
1.1 - Страны; 1.2 - Население; 2.1 - Температура на поверхности земли и т.д.
1.1.1 - Евразия
1.1.1.1 - Россия
и т.д.
То есть при выборе любого из списков меняются другие списки, я пытался через ЕСЛИ, но там очень много надо функций ЕСЛИ, а их всего вроде 7 можно юзать, ну то есть определенное количество, да и формула огромная иначе будет. Пытался с Адресом, Смещением, но правда очень голову ломает мне данный метод, быть может есть попроще что-то?
P.s. я могу эти подкатегории все разместить в одном столбце, в одной строке, или разбить на таблицы.
p.s.s. Проще говоря, реально что надо: у меня есть 40 таблиц с ценниками на проектные работы, в каждой таблице есть свои категории, а у этих категорий есть еще параметры, а у вторых параметров уже имеются цены. Дайте пожалуйста знать когда будет ответ на почту erikbond@bk.ru, подскажите как решить данную ситуацию пожалуйста.

massa

У вас по тексту написано так:

"выделяем ячейки B5:B22
...
в поле Источник вводим: =ДВССЫЛ(B5)"

указана в формуле ячейка B5. Список не создается, указывая на отсутствие источника, т.е. ошибочно, как я понял, указана вами эта ячейка. Должно быть =ДВССЫЛ(A5), т.к. 2 список должен выбираться на основе значений из 1 списка.
Тем более, что дальше в контексте примечания к действиям выше вы используете по тексту верную формулу, цитирую:

<...> В этом случае формула =ДВССЫЛ(A5) работать не будет <...>

Прошу исправить.

Creator

Исправил. Досадная опечатка, спасибо. В файле примера, конечно, все было правильно.

Яндекс.Метрика