Excel подстановка значений из списка

3 способа как в экселе сделать выпадающий список

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

Способ 1 — горячие клавиши и раскрывающийся список в excel

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

Этот же пункт меню можно запустить сочетанием клавиш Alt+»Стрелка вниз» и программа автоматически предложит в выпадающем списке значения ячеек, которые вы ранее заполняли данными. На изображении ниже программа предложила 4 варианта заполнения (дублирующиеся данные Excel не показывает). Единственное условие работы данного инструмента — это между ячейкой, в которую вы вводите данные из списка и самим списком не должно быть пустых ячеек.

Использование горячих клавиш для раскрытия выпадающего списка данных

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

Выпадающий список может работать и в верхней части с данными, которые ниже ячейки

Способ 2 — самый удобный, простой и наиболее гибкий

Данный способ предполагает создание отдельных данных для списка. При чем данные могут находится как на листе с таблицей, так и на другом листе файла Excel.

Сперва необходимо создать список данных, который будет источником данных для подстановки в выпадающий список в excel. Выделите данные и нажмите правой кнопкой мыши. В выпадающем списке выберите пункт «Присвоить имя…».

Создание набора данных для списка

Введите имя для набора данных

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

Для создания проверки вводимых значений введите имя ранее созданного списка

При попытке ввести значение, которого нет в заданном списке, эксель выдаст ошибку.

Кроме списка можно вводить данные вручную. Если введенные данные не совпадут с одним из данных — программа выдаст ошибку

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

Способ 3 — как в excel сделать выпадающий список с использованием ActiveX

Чтобы воспользоваться этим способом, необходимо чтобы у вас была включена вкладка «РАЗРАБОТЧИК». По умолчанию эта вкладка отсутствует. Чтобы ее включить:

1. Нажмите на «Файл» в левом верхнем углу приложения.
2. Выберите пункт «Параметры» и нажмите на него.
3. В окне настройки параметров Excel во вкладке «Настроить ленту» поставьте галочку напротив вкладки «Разработчик».

Включение вкладки «РАЗРАБОТЧИК»

Теперь вы сможете воспользоваться инструментом «Поле со списком (Элемент ActiveX)». Во вкладке «РАЗРАБОТЧИК» нажмите на кнопку «Вставить» и найдите в элементах ActiveX кнопку «Поле со списком (Элемент ActiveX)». Нажмите на нее.

Нарисуйте данный объект в excel выпадающий список в ячейке, где вам необходим выпадающий список.

Теперь необходимо настроить данный элемент. Чтобы это сделать, необходимо включить «Режим конструктора» и нажать на кнопку «Свойства». У вас должно открыться окно свойств (Properties).

С открытым окном свойств нажмите на ранее созданный элемент «Поле со списком». В списке свойств очень много параметров для настройки и вы сможете изучив их, настроить очень много начиная от отображения списка до специальных свойств данного объекта.

Но нас на этапе создания интересуют только три основных:

1. ListFillRange — указывает диапазон ячеек, из которых будут браться значения для выпадающего списка. В моем примере я указал два столбца (A2:B7 — дальше покажу как это использовать). Если необходимо только одни значения указывается A2:A7.
2. ListRows — количество данных в выпадающем списке. Элемент ActiveX отличается от первого способа тем, что можно указать большое количество данных.
3. ColumnCount — указывает сколько столбцов данных указывать в выпадающем списке.

В строке ColumnCount я указал значение 2 и теперь в списке выпадающие данные выглядят вот так:

Как видите получился выпадающий список в excel с подстановкой данных из второго столбца с данными «Поставщик».

Поделиться «3 способа как в экселе сделать выпадающий список»

Excel подстановка значений из списка

Добрый день Уважаемые форумчане.
Пытаюсь два дня найти решение в нэте, но пока никак.
Своих знаний не хватает, по этому обращаюсь к Вам.
Есть таблица с исходными данными (вкладка Invoices), нужно на отдельном листе (Data) при выборе одной из позиций из списка (ячейка B1), что бы в колонку «С» он вертикально выводил список всех строчек с этой позиции из списка исходных данных, рядом подставлял все значения, находящиеся справа от этих позиций.

пример того, как это должно быть, показан на вкладке Example (тока там без форму, естественно. )

заранее Огромное СПАСИБО!

И еще выпадающий список Вам нормальный сделал )см. имена — Контрл F3)
Файл перевложил

Sub SearchByList()
» Author: boa
» Written: 20.10.2017
» Edited:
‘ Description: Берет данные из заданного диапазона искомых значений(Словаря) и сравнивает их со списком значений,
‘ если находит совпадения, то переносит все уникальные значения из заданного столбца
‘ и сопоставленное ему значение из Словаря в новую книгу.

Dim MyList As Range ‘Список искомых значений
Dim MyRange As Range ‘Диапазон для поиска
Dim SearchColumn As Integer ‘колонка в которой ищем совпадения
Dim ZnachColumn As Integer ‘колонка из которой нужно вывести значения
Dim iRow&, V$, Znach As Variant
Dim strCaption$, strLabel$

On Error GoTo Proverka
strCaption = «Поиск уникальных значений по списку»
strLabel = «Введите ссылку на список значений которые надо найти(Словарь).» & vbCrLf & _
«Будут учитываться только видимы значения из выбранного диапазона.»
Set MyList = Application.InputBox(Prompt:=strLabel, Title:=strCaption, Type:=8)
strLabel = «Введите ссылку на диапазон содержащий искомые значения и колонку для сопоставления со Словарем.»
Set MyRange = Application.InputBox(Prompt:=strLabel, Title:=strCaption, Type:=8)
If Not MyRange Is Nothing Then SearchColumn = MyRange.Columns.Count
strLabel = «Введите номер колонки от 1 до » & SearchColumn & » в выбранном диапазоне, по которой должен быть произведен поиск значений из Словаря.»
SearchColumn = Application.InputBox(Prompt:=strLabel, Title:=strCaption, Default:=SearchColumn, Type:=1)

strLabel = «Введите номер колонки в массиве из которой надо вывести найденный результат.» & vbCrLf & _
«Если номер колонки не вводить(нажать «»Отмена»»), то в результат будет выведена вся строка из выделенного диапазона.»
ZnachColumn = Application.InputBox(Prompt:=strLabel, Title:=strCaption, Type:=1)
Proverka:
If MyList Is Nothing Or MyRange Is Nothing Or SearchColumn 0 Then V = CStr(MyRange.Cells(i, ZnachColumn).Value) Else V = i
Dic.Add V, CStr(MyRange.Cells(i, SearchColumn).Value)
End If
Next i

With Workbooks.Add(xlWBATWorksheet).Worksheets(1) ‘вывод результатов
.Cells(1, 1).Value = «Значения из списка»
.Cells(1, 2).Value = «Найденные значение » & Dic.Count
iRow = 2
If ZnachColumn > 0 Then
.Range(.Cells(iRow, 1), .Cells(Dic.Count + 1, 2)).Value = Application.Transpose(Array(Dic.Items, Dic.Keys))
Else
.Range(.Cells(iRow, 1), .Cells(Dic.Count + 1, 1)).Value = Application.Transpose(Array(Dic.Items))
For Each Znach In Dic
.Range(.Cells(iRow, 2), .Cells(iRow, MyRange.Columns.Count + 1)).Value = MyRange.Rows(Znach).Value
iRow = iRow + 1
Next
End If
.UsedRange.EntireColumn.AutoFit
End With

sMsg = «Для заданного списка найдено » & Dic.Count & » значений.» & vbCrLf & _
«Затрачено » & Format(Timer — Start, «0.00») & » сек.»
Debug.Print «Затрачено: » & Timer — Start
MsgBox sMsg, vbInformation
End Sub

при необходимости подкорректируете под свои нужды

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

Для сортировки или заполнения значений в пользовательском порядке можно применять настраиваемые списки. В Excel есть встроенные списки дней недели и месяцев года, но вы можете создавать и свои настраиваемые списки.

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

Сравнение встроенных и настраиваемых списков

В Excel есть указанные ниже встроенные списки дней недели и месяцев года.

Пн, Вт, Ср, Чт, Пт, Сб, Вс

Понедельник, Вторник, Среда, Четверг, Пятница, Суббота, Воскресенье

янв, фев, мар, апр, май, июн, июл, авг, сен, окт, ноя, дек

Январь, Февраль, Март, Апрель, Май, Июнь, Июль, Август, Сентябрь, Октябрь, Ноябрь, Декабрь

Примечание: Изменить или удалить встроенный список невозможно.

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

Высокое, Среднее, Низкое

Большое, Среднее, Малое

Север, Юг, Восток, Запад

Старший менеджер по продажам, Региональный менеджер по продажам, Руководитель отдела продаж, Торговый представитель

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

Примечание: Настраиваемый список может содержать только текст или текст с числами. Чтобы создать настраиваемый список, содержащий только числа, например от 0 до 100, нужно сначала создать список чисел в текстовом формате.

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

Введение значений напрямую

Чтобы создать настраиваемый список этим способом, выполните указанные ниже действия.

В Excel 2010 и более поздних версиях выберите пункты Файл > Параметры > Дополнительно > Общие > Изменить списки.

В Excel 2007 нажмите кнопку Microsoft Office и выберите пункты Параметры Excel > Популярные > Основные параметры работы с Excel > Изменить списки.

Выберите в поле Списки пункт НОВЫЙ СПИСОК и введите данные в поле Элементы списка, начиная с первого элемента.

После ввода каждого элемента нажимайте клавишу ВВОД.

Завершив создание списка, нажмите кнопку Добавить.

На панели Списки появятся введенные вами элементы.

Нажмите два раза кнопку ОК.

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

Выполните указанные ниже действия.

В диапазоне ячеек введите сверху вниз значения, по которым нужно выполнить сортировку или заполнение. Выделите этот диапазон и, следуя инструкциям выше, откройте всплывающее окно «Списки».

Убедитесь, что ссылка на выделенные значения отображается в окне Списки в поле Импорт списка из ячеек, и нажмите кнопку Импорт.

На панели Списки появятся выбранные вами элементы.

«Параметры» > «Дополнительно» > «Общие» > «Изменить списки». Если вы используете Excel 2007, нажмите кнопку Office и выберите «Параметры Excel» > «Популярные» > «Изменить списки».» />

Два раза нажмите кнопку ОК.

Примечание: Настраиваемый список можно создать только на основе значений, таких как текст, числа, даты и время. На основе формата, например значков, цвета ячейки или цвета шрифта, создать настраиваемый список нельзя.

Выполните указанные ниже действия.

По приведенным выше инструкциям откройте диалоговое окно «Списки».

Выделите список, который нужно удалить, в поле Списки и нажмите кнопку Удалить.

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

Однако при открытии книги на другом компьютере или сервере такой список, сохраненный в файле книги, не отображается во всплывающем окне Списки в параметрах Excel: его можно выбрать только в столбце Порядок диалогового окна Сортировка. Настраиваемый список, сохраненный в файле книги, также недоступен непосредственно для команды Заполнить.

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Подстановка данных в excel функция ВПР

• 22 Апрель, 2011 —
• Уроки Excel —
• Tags : таблицы excel, уроки excel, эксель данные
• 75 Comments

Довольно распространенная ситуация, когда данные из одной таблицы в excel надо подставить в другую.

Рассмотрим на примере: допустим есть 2 таблицы — продажи и прайс-лист. Задача-подставить цены из таблицы прайс-лист в таблицу продажи, чтобы можно было в итоге посчитать общую сумму продаж.

Предлагаю 2 варианта выполнения этой задачи.

Вариант 1. Использовать функцию ВПР. скачать пример

Функция ВПР ищет заданное значение в крайнем левом столбце указанной таблицы, двигаясь сверху вниз. В нашем примере функция ВПР будет выполнять поиск «корм для кошек» в таблице «прайс-лист» и найдя его, подставит цену корма для кошек в таблицу «продажи». Ровно так, как показано на рисунке выше. Сразу хочу отметить 2 важных условия для удачной работы формулы: 1-столбец «наименование товара» в обоих таблицах должен иметь одинаковый формат, 2-столбец «наименование товара» в таблице прайс-лист должен быть отсортирован по возрастанию.

Все, вызываем функцию ВПР. Щелкаем в той ячейке, куда будет подставляться цена( С5 нашего примера), далее жмем значок fx на панели инструментов (либо Вставка-функция) и в открывшемся окошке выбираем ссылки и массивы и далее ВПР. Как показано на картинке.

и жмем ОК. Откроется следующее окно, в котором и задаются параметры подстановки:

искомое значение — щелкаем по той ячейке, в которой находится искомое значение — у нас это корм для кошек

таблица — это таблица, из которой берутся данные. Щелкаем на квадратик с красной стрелкой и мышкой обводим нашу таблицу прайс-лист, жмем Enter

номер столбца — здесь нужно указать именно порядковый номер столбца таблицы из которой будут браться цены. В нашем примере столбец номер один-наименование, столбец номер 2-цена. Таким образом, мы ставим цифру 2

интервальный просмотр — здесь можно ввести либо ЛОЖЬ либо ИСТИНА. Других вариантов нет. Можно либо словами написать, либо ввести цифру 0 или 1. 0-ЛОЖЬ, 1-ИСТИНА. Если вводим ЛОЖЬ — выполняется поиск точного соответствия заданному параметру, если вы введете ИСТИНА, то таким образом Вы даете разрешение на поиск приблизительно соответствия, то есть поиск максимально похожего заданному параметру. Чтобы было меньше ошибок, лучше всегда указывать ЛОЖЬ, т.е. поиск точного соответствия.

Все, нажимаем ОК и радуемся:)

Для еще большего удобства работы с такой формулой, можно таблице прайс-лист присвоить имя, и потом в формуле указывать не диапазон поиска, и просто имя таблицы. Для этого выделяем таблицу прайс-лист и жмем правой кнопкой мыши, там находим «имя диапазона»

В новом открывшемся окне пишем имя диапазона, например «прайс»

И тогда в формуле ВПР можно просто впечатать имя диапазона

И второй способ решения данной задачи — подстановка данных в excel через функцию СУММЕСЛИ

Выпадающий список в Excel с помощью инструментов или макросов

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

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

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

Путь: меню «Данные» — инструмент «Проверка данных» — вкладка «Параметры». Тип данных – «Список».

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

1. Вручную через «точку-с-запятой» в поле «Источник».
2. Ввести значения заранее. А в качестве источника указать диапазон ячеек со списком.
3. Назначить имя для диапазона значений и в поле источник вписать это имя.

Любой из вариантов даст такой результат.

Выпадающий список в Excel с подстановкой данных

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

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

Протестируем. Вот наша таблица со списком на одном листе:

Добавим в таблицу новое значение «елка».

Теперь удалим значение «береза».

Осуществить задуманное нам помогла «умная таблица», которая легка «расширяется», меняется.

Теперь сделаем так, чтобы можно было вводить новые значения прямо в ячейку с этим списком. И данные автоматически добавлялись в диапазон.

1. Сформируем именованный диапазон. Путь: «Формулы» — «Диспетчер имен» — «Создать». Вводим уникальное название диапазона – ОК.
2. Создаем раскрывающийся список в любой ячейке. Как это сделать, уже известно. Источник – имя диапазона: =деревья.
3. Снимаем галочки на вкладках «Сообщение для ввода», «Сообщение об ошибке». Если этого не сделать, Excel не позволит нам вводить новые значения.
4. Вызываем редактор Visual Basic. Для этого щелкаем правой кнопкой мыши по названию листа и переходим по вкладке «Исходный текст». Либо одновременно нажимаем клавиши Alt + F11. Копируем код (только вставьте свои параметры).
5. Сохраняем, установив тип файла «с поддержкой макросов».
6. Переходим на лист со списком. Вкладка «Разработчик» — «Код» — «Макросы». Сочетание клавиш для быстрого вызова – Alt + F8. Выбираем нужное имя. Нажимаем «Выполнить».

Когда мы введем в пустую ячейку выпадающего списка новое наименование, появится сообщение: «Добавить введенное имя баобаб в выпадающий список?».

Нажмем «Да» и добавиться еще одна строка со значением «баобаб».

Выпадающий список в Excel с данными с другого листа/файла

Когда значения для выпадающего списка расположены на другом листе или в другой книге, стандартный способ не работает. Решить задачу можно с помощью функции ДВССЫЛ: она сформирует правильную ссылку на внешний источник информации.

1. Делаем активной ячейку, куда хотим поместить раскрывающийся список.
2. Открываем параметры проверки данных. В поле «Источник» вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).

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

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

Возьмем три именованных диапазона:

Это обязательное условие. Выше описано, как сделать обычный список именованным диапазоном (с помощью «Диспетчера имен»). Помним, что имя не может содержать пробелов и знаков препинания.

1. Создадим первый выпадающий список, куда войдут названия диапазонов.
2. Когда поставили курсор в поле «Источник», переходим на лист и выделяем попеременно нужные ячейки.
3. Теперь создадим второй раскрывающийся список. В нем должны отражаться те слова, которые соответствуют выбранному в первом списке названию. Если «Деревья», то «граб», «дуб» и т.д. Вводим в поле «Источник» функцию вида =ДВССЫЛ(E3). E3 – ячейка с именем первого диапазона.

Выбор нескольких значений из выпадающего списка Excel

Бывает, когда из раскрывающегося списка необходимо выбрать сразу несколько элементов. Рассмотрим пути реализации задачи.

1. Создаем стандартный список с помощью инструмента «Проверка данных». Добавляем в исходный код листа готовый макрос. Как это делать, описано выше. С его помощью справа от выпадающего списка будут добавляться выбранные значения.
2. Чтобы выбранные значения показывались снизу, вставляем другой код обработчика.
3. Чтобы выбираемые значения отображались в одной ячейке, разделенные любым знаком препинания, применим такой модуль.
   Private Sub Worksheet_Change( ByVal Target As Range)
   On Error Resume Next
   If Not Intersect(Target, Range( «C2:C5» )) Is Nothing And Target.Cells.Count = 1 Then
   Application.EnableEvents = False
   newVal = Target
   Application.Undo
   oldval = Target
   If Len(oldval) <> 0 And oldval <> newVal Then
   Target = Target & «,» & newVal
   Else
   Target = newVal
   End If
   If Len(newVal) = 0 Then Target.ClearContents
   Application.EnableEvents = True
   End If
   End Sub

Не забываем менять диапазоны на «свои». Списки создаем классическим способом. А всю остальную работу будут делать макросы.

Выпадающий список с поиском

1. На вкладке «Разработчик» находим инструмент «Вставить» – «ActiveX». Здесь нам нужна кнопка «Поле со списком» (ориентируемся на всплывающие подсказки).
2. Щелкаем по значку – становится активным «Режим конструктора». Рисуем курсором (он становится «крестиком») небольшой прямоугольник – место будущего списка.
3. Жмем «Свойства» – открывается перечень настроек.
4. Вписываем диапазон в строку ListFillRange (руками). Ячейку, куда будет выводиться выбранное значение – в строку LinkedCell. Для изменения шрифта и размера – Font.

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