Выпадающий список с подстановкой в Excel — это мощный инструмент, который позволяет автоматизировать и упростить процесс ввода данных.
Ранее мы уже подробно останавливались на том, как создать выпадающий список.
В этой заметке я подробно расскажу о том, как как настроить подстановку значений в соседние ячейки при использовании выпадающего списка.
Что такое выпадающий список с подстановкой в Excel?
Выпадающий список с подстановкой данных в Excel — это элемент управления, который позволяет пользователю выбрать значение из заранее определенного списка, а затем автоматически подставляет соответствующее значение в соседнюю ячейку.
Это особенно полезно для крупных таблиц с повторяющимися данными, например, для каталогов продуктов, клиентских баз данных и других аналогичных приложений.
Пошаговое описание создания выпадающего списка с подстановкой
Шаг 1: Подготовка данных
Для начала необходимо подготовить данные, которые будут использоваться в выпадающем списке. Допустим, у нас есть таблица с названиями продуктов и их продажами по месяцам.
Шаг 2: Создание выпадающего списка
Для создания выпадающего списка используйте функцию проверки данных. Выполните следующие шаги:
- Выберите ячейку, в которой будет находиться выпадающий список.
- Перейдите во вкладку «Данные» на ленте инструментов.
- Нажмите кнопку «Проверка данных».
- В открывшемся окне выберите «Список» в разделе «Тип данных».
- В поле «Источник» введите диапазон ячеек, содержащих значения для выпадающего списка. В нашем примере это будет диапазон =A2:A6.
- Нажмите кнопку «OK».
Теперь в выбранной ячейке появился выпадающий список с названиями продуктов.
Шаг 3: Использование функции ВПР для подстановки значений
В целях эффективного использования выпадающего списка с подстановкой в Excel, важно научиться связывать выбранное значение с соответствующими данными в соседних ячейках. Это можно сделать, используя такие функции, как ВПР, а также комбинацию ИНДЕКС и ПОИСКПОЗ. Давайте рассмотрим поэтапный процесс настройки такого функционала.
Для подстановки значений в соседнюю ячейку используйте функцию ВПР (в английской версии VLOOKUP). Эта функция ищет значение в первом столбце таблицы и возвращает значение из другой колонки этой же строки.
Выполните следующие шаги:
- Выберите ячейку, в которую будет подставляться значение (например, Е2).
- Введите следующую формулу:
=ВПР(B1; A2:B6; 2; ЛОЖЬ)Здесь:
- D2 — это ячейка с выпадающим списком (предположим, что вы создали выпадающий список в ячейке D2).
- A2:B6 — это диапазон ячеек с данными (включая названия продуктов и их цены).
- 2 — номер столбца в диапазоне, из которого нужно вернуть значение (в нашем случае это столбец с ценами).
- ЛОЖЬ — аргумент, указывающий на точное совпадение.
Шаг 4: Проверка работы
Теперь, когда вы выберете продукт из выпадающего списка, в соседней ячейке автоматически подставится его цена.
Выпадающий список с подстановкой сразу нескольких значений
Используя формулу ИНДЕКС+ПОИСКПОЗ или СМЕЩ+ПОИСКПОЗ, вы можете подставлять целый массив значений, используя выпадающий список с подстановкой.
Вот пример исходных данных:
Выбрав из выпадающего списка название месяца, необходимо получить данные о продажах всех видов шоколада за этот месяц.
Выпадающий список месяцев создадим в ячейке R1 на основе именованного диапазона О1:О12 с именем «месяцы».
Подробную инструкцию вы можете посмотреть здесь:
При помощи формулы
=ИНДЕКС(B2:M7;;ПОИСКПОЗ(R1;B1:M1;0))получаем сразу все значения продаж за выбранный месяц – май.
Функция ИНДЕКС выбирает из диапазона B2:M7 (вы его видите на предыдущем скриншоте) столбец с данными. Номер этого столбца определяет функция ПОИСКПОЗ, которая ищет имя месяца в списке B1:M1.
Аналогичные результаты вы можете получить при помощи выпадающего списка с подстановкой и формулы СМЕЩ:
=СМЕЩ(A2:A7;0;ПОИСКПОЗ(R1;B1:M1;0))Здесь вы смещаетесь относительно первоначального диапазона A2:A7 вправо. Смещение происходит на столько столбцов, каков номер выбранного месяца в списке.
Кроме того, вы можете обойтись и без функций поиска. Для этого создайте именованные диапазоны с данными за каждый месяц. К примеру, диапазон F2:F7 будет носить имя «май». Тогда получить его данные вы легко сможете при помощи функции ДВССЫЛ. Это будет так:
=ДВССЫЛ(R1)Этот метод позволяет динамически подставлять разные значения в зависимости от выбора, делая вашу работу в Excel более эффективной и организованной.
Заключение
Создание выпадающего списка с подстановкой данных в соседние ячейки Excel — это полезный инструмент для автоматизации и упрощения работы с данными. Этот процесс включает в себя подготовку данных, создание выпадающего списка с использованием функции проверки данных и подстановку значений с помощью функции ВПР.
Надеюсь, эта пошаговая инструкция и примеры помогут вам легко создать выпадающий список с подстановкой в Excel и применить его для различных задач.
Вот еще полезная информация про выпадающий список: