Как связать списки в excel

Как сделать связанный выпадающий список в «Эксель», зависящий от значения в соседней ячейке.

При создании какой-либо формы для заполнения самый лучший способ введения данных — это выпадающие списки.

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

Существует один незначительный недостаток выпадающих списков – для большого количества вариантов ответа списки получаются очень длинными.

Как сократить длину выпадающих списков?

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

Например, существует список различных продуктов, этот список очень большой, чтобы упростить список следует разделить продукты по категориям: фрукты, овощи, молочные, мясные и т.д.

Список продуктов.
Бананы Перловая каша
Ванильный сахар Петрушка
Горох сушеный Просо
Горчица молотая Рис
Греча Рыба Сом
Грибы Сало копченое
Дрожжи сухие Сахар обычный
Желатин Сахарная пудра
Какао порошок Свинина
Какао порошок Сгущённое молоко
Карри (специя) Сливки молочные
Картофель Сливочное масло
Кефир Сметана
Килька в томате Смородина
Консервированная кукуруза Сода
Консервированный горошек Соевый соус
Корица Соль
Котлеты свиные Спагетти
Кофе Суповой набор куриный
Крабовые палочки Суповой набор мясной
Крахмал Сухие грибы
Крупа кукурузная Сыр
Крыжовник Творог зернистый
Курица (филе, бедра, окорочка) Тесто
Лавровый лист Томатная паста
Лимон Укроп сушеный
Лук Уксус обычный
Макароны Фасоль
Малина Чай зеленый
Манка Чай Каркаде
Маргарин Чай черный
Масло растительное Черный перец
Масло сливочное Чеснок
Мед Шпинат
Перец Яблоки
Яйца

Продукты по категориям

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

Как реализовать зависимый выпадающий список?

  1. Для начала необходимо присвоить ячейкам имя:Присвоение имени ячейкам
  • Выделяется диапазон ячеек;
  • Во вкладке «формулы» выбирается кнопка «присвоить имя»;
  • Ячейкам присваивается имя соответствующее их категории (например, для овощей имя «Овощи»);Присвоение имени ячейкам
  1. На новом листе создается форма для заполнения:
  • В первой ячейке создается выпадающий список, состоящий из имен ячеек (категорий: овощи, фрукты и т.д.)

Проверка данных

Выбирается вкладка «Данные» -> «Проверка данных»

Список

Выбирается «Список» и указывается диапазон, из которого будут выбираться значения.

Нажимаем «ОК»%d1%81%d0%bf%d0%b8%d1%81%d0%be%d0%ba-1

Список выпадающий

  • Во второй ячейке создается список, зависящий от первого.

Зависимый список значений в Эксель

Создается список аналогично с первым, но в качестве источника данных выбирается не диапазон ячеек, а функция ДВССЫЛ(). В качестве аргумента функции указывается адрес первой ячейки со списком категории.

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

history 21 января 2013 г.
    Группы статей

  • Выпадающий список
  • Имена
  • Проверка данных

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ссылка на основную публикацию