Как сделать связанный выпадающий список в «Эксель», зависящий от значения в соседней ячейке.
При создании какой-либо формы для заполнения самый лучший способ введения данных — это выпадающие списки.
Они позволяют стандартизировать варианты ответов и не дают возможности человеку заполняющему фурму вносить свои фразы и слова, предлагают готовые ответы, которые в дальнейшем легко анализировать и обрабатывать.
Существует один незначительный недостаток выпадающих списков – для большого количества вариантов ответа списки получаются очень длинными.
Как сократить длину выпадающих списков?
Для сокращения длины списка его следует разбить на несколько списков сформированных по разным критериям. И сделать так, чтобы в ячейке появлялся список соответствующий нужному критерию.
Например, существует список различных продуктов, этот список очень большой, чтобы упростить список следует разделить продукты по категориям: фрукты, овощи, молочные, мясные и т.д.
| Список продуктов. | |
| Бананы | Перловая каша |
| Ванильный сахар | Петрушка |
| Горох сушеный | Просо |
| Горчица молотая | Рис |
| Греча | Рыба Сом |
| Грибы | Сало копченое |
| Дрожжи сухие | Сахар обычный |
| Желатин | Сахарная пудра |
| Какао порошок | Свинина |
| Какао порошок | Сгущённое молоко |
| Карри (специя) | Сливки молочные |
| Картофель | Сливочное масло |
| Кефир | Сметана |
| Килька в томате | Смородина |
| Консервированная кукуруза | Сода |
| Консервированный горошек | Соевый соус |
| Корица | Соль |
| Котлеты свиные | Спагетти |
| Кофе | Суповой набор куриный |
| Крабовые палочки | Суповой набор мясной |
| Крахмал | Сухие грибы |
| Крупа кукурузная | Сыр |
| Крыжовник | Творог зернистый |
| Курица (филе, бедра, окорочка) | Тесто |
| Лавровый лист | Томатная паста |
| Лимон | Укроп сушеный |
| Лук | Уксус обычный |
| Макароны | Фасоль |
| Малина | Чай зеленый |
| Манка | Чай Каркаде |
| Маргарин | Чай черный |
| Масло растительное | Черный перец |
| Масло сливочное | Чеснок |
| Мед | Шпинат |
| Перец | Яблоки |
| Яйца | |
В зависимости от того, какую категорию будет выбирать человек, выпадающий список будет выдавать ему только наименования из соответствующей категории.
Как реализовать зависимый выпадающий список?
- Для начала необходимо присвоить ячейкам имя:
- Выделяется диапазон ячеек;
- Во вкладке «формулы» выбирается кнопка «присвоить имя»;
- Ячейкам присваивается имя соответствующее их категории (например, для овощей имя «Овощи»);
- На новом листе создается форма для заполнения:
- В первой ячейке создается выпадающий список, состоящий из имен ячеек (категорий: овощи, фрукты и т.д.)
Выбирается вкладка «Данные» -> «Проверка данных»
Выбирается «Список» и указывается диапазон, из которого будут выбираться значения.
Нажимаем «ОК»
- Во второй ячейке создается список, зависящий от первого.
Создается список аналогично с первым, но в качестве источника данных выбирается не диапазон ячеек, а функция ДВССЫЛ(). В качестве аргумента функции указывается адрес первой ячейки со списком категории.
Зависимые выпадающие списки в Excel
Изучим простой способ создания зависимых (также называют связанных) выпадающих списков в Excel.
Для начала поясним, что же такое связанные выпадающие списки.
Это 2 выпадающих списка, при этом список значений одного из выпадающих списков зависит от выбора значения в другом выпадающем списке. Создать по отдельности выпадающие списки не представляет сложностей, но и связать выпадающие списки можно достаточно просто.
Для наглядности рассмотрим следующую ситуацию: у нас есть несколько категорий блюд, например, пицца, суши и паста, а также виды блюд в каждой категории (пицца Пепперони, суши Филадельфия, паста Феттучини и т.д.):
В результате мы хотим получить своеобразный двухуровневый зависимый выпадающий список: на первом уровне (списке) — категории блюд, на втором — блюда из выбранной категории.
Как сделать зависимые выпадающие списки?
В основе создания связанных выпадающих списков лежит применение функции ДВССЫЛ, которая позволяет преобразовывать текст из ячейки в ссылку.
Другими словами, если в ячейку введено текстовое значение «А1», то функция ДВССЫЛ вернет ссылку на ячейку А1.
Теперь зададим имена диапазонам состоящим из всех видов блюд каждой конкретной категории.
Для этого в панели вкладок выбираем Формулы -> Определенные имена -> Присвоить имя:
Выделяем диапазон ячеек A2:A6 и создаем диапазон с именем Пицца, аналогичные действия повторяем и для списков с суши (имя диапазона — Суши) и пастой (имя диапазона — Паста):
Обратите внимание, что при создании имен диапазонов имя не должно включать в себя пробелы.
В том случае если в названии категории все же содержится пробел (например, Японская кухня), то одним из вариантов решения является использование нижнего подчеркивания вместо пробела в имени диапазона (Японская_кухня).
Создадим первый выпадающий список в ячейке A10, состоящий из категорий блюд (Пицца, Суши и Паста). В панели вкладок выбираем Данные -> Работа с данными -> Проверка данных, указываем тип данных Список и в качестве источника выделяем диапазон A1:C1:
Теперь создаем второй выпадающий список, полностью повторяем действия с созданием первого списка, только в поле Источник записываем формулу =ДВССЫЛ(A10):
Имена созданных диапазонов обязательно должны совпадать с элементами первого списка, поэтому если в первом списке есть категории содержащие пробелы, то при обращении к имени диапазона необходимо заменить пробелы на нижние подчеркивания.
Это можно осуществить с помощью функции ПОДСТАВИТЬ, которая позволяет заменить старый текст (пробел) на новый текст (нижнее подчеркивание) в текстовой строке, т.е. в нашем случае формула примет вид =ДВССЫЛ(ПОДСТАВИТЬ(A10;" ";"_")).
Также минусом данного способа создания списков является невозможность использования динамических именованных диапазонов.
Подробно ознакомиться с примером зависимых выпадающих списков — скачать пример.