Как сделать зависимый список в excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Список

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

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

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

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

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

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

Зависимые выпадающие списки в Excel

Изучим простой способ создания зависимых (также называют связанных) выпадающих списков в Excel.

Для начала поясним, что же такое связанные выпадающие списки.
Это 2 выпадающих списка, при этом список значений одного из выпадающих списков зависит от выбора значения в другом выпадающем списке. Создать по отдельности выпадающие списки не представляет сложностей, но и связать выпадающие списки можно достаточно просто.
Для наглядности рассмотрим следующую ситуацию: у нас есть несколько категорий блюд, например, пицца, суши и паста, а также виды блюд в каждой категории (пицца Пепперони, суши Филадельфия, паста Феттучини и т.д.):

Список значений

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

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

В основе создания связанных выпадающих списков лежит применение функции ДВССЫЛ, которая позволяет преобразовывать текст из ячейки в ссылку.
Другими словами, если в ячейку введено текстовое значение «А1», то функция ДВССЫЛ вернет ссылку на ячейку А1.
Теперь зададим имена диапазонам состоящим из всех видов блюд каждой конкретной категории.
Для этого в панели вкладок выбираем Формулы -> Определенные имена -> Присвоить имя:

Вкладка для создания имени диапазона

Выделяем диапазон ячеек A2:A6 и создаем диапазон с именем Пицца, аналогичные действия повторяем и для списков с суши (имя диапазона — Суши) и пастой (имя диапазона — Паста):

Создание имени диапазона

Обратите внимание, что при создании имен диапазонов имя не должно включать в себя пробелы.
В том случае если в названии категории все же содержится пробел (например, Японская кухня), то одним из вариантов решения является использование нижнего подчеркивания вместо пробела в имени диапазона (Японская_кухня).

Создадим первый выпадающий список в ячейке A10, состоящий из категорий блюд (Пицца, Суши и Паста). В панели вкладок выбираем Данные -> Работа с данными -> Проверка данных, указываем тип данных Список и в качестве источника выделяем диапазон A1:C1:

Создание первого всплывающего списка

Теперь создаем второй выпадающий список, полностью повторяем действия с созданием первого списка, только в поле Источник записываем формулу =ДВССЫЛ(A10):

Создание второго всплывающего списка

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

Подробно ознакомиться с примером зависимых выпадающих списков — скачать пример.

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