Эксель выбор из раскрывающегося списка по условию. Создание выпадающего списка в Excel
При заполнении таблички в редакторе Excel очень часто данные повторяются. К примеру, вы пишите наименование товара или ФИО работника. Сегодня мы поговорим о том, как в Эксель сделать выпадающий список, чтобы постоянно не вводить одно и то же, а просто выбирать нужное значение.
Есть самый простой вариант справиться с поставленной задачей. Вы делаете правый клик мышкой по ячейке под столбиком с данными для вызова контекстного меню. Тут ищем пункт «Выбрать из раскрывающегося списка». Это же действие легко выполнить комбинацией Alt + стрелочка вниз .
Вот только данный метод не подойдет, если необходимо сделать такой объект в Эксель в другой ячейке и в 2-3 и т.д до и после. При такой необходимости используйте следующий вариант.
Традиционный способ
Выделяете область самих ячеек, из которых создаете выпадающий перечень, далее переходите:
Вставка/Имя/Присвоить (Excel 2003)
В свежих версиях (2007, 2010, 2013, 2016) переходите:
Формулы/Определенные имена/Диспетчер имен/Создать
Вписываете любое наименование, тапаете ОК .
Затем выделяете ячейки (или несколько), куда планируете вставить перечень заданных полей. В меню переходите:
Данные/Тип данных/Список
В разделе «Источник» вносите ранее написанное имя, просто отмечаете диапазон. Полученную ячеечку можете копировать в любое место, в ней уже будет меню заданных табличных элементов.
Кроме того, вы можете ее растягивать, чтобы создать диапазон. Кстати, в случае изменения информации в нем, списочная информация тоже будет изменяться, она является динамической.
Как в Экселе сделать выпадающий список: пользуемся управленческими методами
При использовании этого варианта, вы вставляете элемент управления, представляющий собой непосредственно диапазон данных. Для этого:
- во-первых, ищите раздел «Разработчик» (Эксель 2007/2010), в других выпусках активируете самостоятельно через параметры «Настроить ленту»;
- во-вторых, переходите на раздел, кликаете «Вставить»;
- в-третьих, выбираете «Поле…» и жмете по значку;
- рисуете прямоугольник;
- делаете правый клик по нему и жмете Формат объекта;
- ищите «Формировать…», выделяете нужные границы;
- отмечаете ту область, куда хотите установить порядковый номер элемента в перечне;
- жмете ОК.
Как создать в Экселе выпадающее меню: пользуемся элементами ActiveX
Действия аналогичны вышеописанным, но ищем «Поле с ActiveX».
Здесь основные отличия в том, что спецэлемент ActiveX может быть в 2 вариантах - режим отладки, который позволяет менять параметры, и - режим ввода, получится сделать только выборку сведений из него. Поменять режим можно клавишей Режим Конструктора в разделе Разработчик . С помощью этого способа вы можете настроить цвет, шрифт и выполнить оперативный поиск.
Дополнительные возможности
В программе Excel возможно создание связанного выпадающего перечня. То есть при выборе какого-то значения, в другой графе можно подобрать нужные ему параметры. К примеру, вы выбираете какой-то продукт, и понадобилось отметить меру измерения, к примеру, грамм, килограмм.
В первую очередь, следует сделать табличку, где будут сами списочки, а потом отдельно окошки с названиями продуктов.
Для второй активируем окошко проверки сведений, но в пункте «Источник» пишем «=ДВССЫЛ» и адрес 1-й ячейки. Все получилось.
После этого, чтобы окошки снизу дополнились такими же свойствами, выделяем верхний участок и с нажатой мышкой перетаскиваем все вниз. Готово.
Как в Эксель сделать выпадающий список? Как видите, это легко выполнить любым указанным методом. Просто выбирайте для себя самый оптимальный. А выбор будет зависеть от целей создания, предназначения, области использования, количества информации и прочего.
Если вам нужно, чтобы при нажатии на одну из ячеек в табличном документе Excel, раскрывался список с возможными вариантами значений, то вы попали по адресу. В данной статье расскажем вам о самых распространенных и популярных способах как это можно сделать. Это не занимает много времени. Специальные знания и навыки вам не нужны. Только желание, внимательность и четко следовать прописной инструкции. Итак, поехали!
Способ 1. Стандартный.
Для начала необходимо задать диапазон значений, который вы хотите видеть в своем выпадающем списке. К примеру, поговорим о ячейке «программа». Создадим список, который у нас должен выпадать из ячейки.
Вводим значения для выпадающего списка
Если у вас Excel 2003, тогда необходимо выполнить следующие действия. Станьте на ячейку, которую хотите сделать как выпадающий список, выбираете меню Данные – Проверка.
Выделяем будущую ячейку со списком
В Excel 2007 и выше данное окно вызывается через вкладку «Данные » -> «Проверка данных ».
Список в ячейке в MS Excell 2010
У вас появляется диалоговое окно с проверкой, куда необходимо ввести диапазон значений.
Указываем диапазон ячеек со значениями списка
Задаем конкретный тип вводимых значений, в нашем случае рассматриваем элемент «Список».
Для указания значений выпадающего списка, есть конкретное поле – «источник» . Здесь указывается диапазон ячеек, откуда будут браться значения для выпадающего списка. Делается это нажатием на значок в конце строки. Далее выделяете диапазон ячеек и нажимаете «Enter».
Вот какой результат получается в итоге.
Готовая ячейка с выпадающим списком
Для того чтобы в поле «Источник» не задавать постоянно диапазон значений. Можно эти значения объединить в одну категорию, присвоить ей имя и писать это имя в данной графе.
Указываем конкретный перечень значений, который должен выпадать. Заходим, выполняем следующие шаги.
- Шаг 1 – выбираем меню – «Вставка»;
- Шаг 2 – переходим в меню «Имя»;
- Шаг 3 — открываем диалоговое окно «Присвоить».
Создаем константу со значениями списка
Если у вас английская версия тогда так
- Insert;
- Name;
- Define.
В случае, если работаете с седьмой офисной версией или более новее Excel 2007. Тогда вам в помощь вкладка «Формулы» – «Диспетчер имен» (Name Manager) , и выбираем создать. Выбор имени ничем не ограничен. Можете написать, например «Обзор».
Создание имени диапазона значений в Excel 2010
Указываем имя созданного диапазона
Обратите внимание на такой момент. Источником данных может быть и любой именованный диапазон данных, к примеру, прайс-лист.
В этом случае, когда каталог прайса будет добавляться новыми позициями, они автоматически высвечиваются в выпадающем меню. Также, одним важным моментом для таких списков, считаются создание связанных выпадающих элементов. В таких случаях. Когда в одном элементе меняется содержание, в другом автоматически происходит изменение и обновление структуры.
Способ 2 – Элемент управления.
В этом способе рассмотрен вариант добавления нового объекта и привязка его к конкретному диапазону в екселе. Какие необходимо выполнить шаги:
Если у вас версия ексель 2007 года и позже, тогда выбираем меню Разработчик. Если версия ранняя тогда Вид — Панели инструментов – Формы.
Элемент списка знаком нам по формам на сайтах. Удобно выбирать уже готовые значения. Например, никто не вводит месяц вручную, его берут из такого перечня. Заполнить выпадающий список в Excel можно с использованием различных инструментов. В статье рассмотрим каждый из них.
Как сделать выпадающий список в Excel
Как сделать выпадающий список в Excel 2010 или 2016 с помощью одной командой на панели инструментов? На вкладке «Данные» в разделе «Работа с данными» найдите кнопку «Проверка данных». Нажмите на нее и выберите первый пункт.
Откроется окно. Во вкладке «Параметры» в выпадающем разделе «Тип данных» выберите «Список».
Снизу появится строка для указания источников.
Указывать информацию можно по-разному.
Сначала назначим имя. Для этого создайте на любом листе такую таблицу.
Выделите ее и нажмите правую кнопку мыши. Щелкните по команде «Присвоить имя».
Введите имя в строку сверху.
Вызовите окно «Проверка данных» и в поле «Источник» укажите имя, поставив перед ним знак «=».
В любом из трех случаев Вы увидите нужный элемент. Выбор значения из выпадающего списка Excel происходит с помощью мыши. Нажмите на него и появится перечень указанных данных.
Вы узнали, как создать выпадающий список в ячейке Excel. Но можно сделать и больше.
Подстановка динамических данных Excel
Если Вы добавите какое-то значение в диапазон данных, которые подставляются в перечень, то в нем изменения не произойдет, пока вручную не будут указаны новые адреса. Чтобы связать диапазон и активный элемент, необходимо оформить первый как таблицу. Создайте вот такой массив.
Выделите его и на вкладке «Главная» выберите любой стиль таблицы.
Обязательно поставьте галочку внизу.
Вы получите такое оформление.
Создайте активный элемент, как было описано выше. В качестве источника введите формулу
ДВССЫЛ("Таблица1[Города]")
Чтобы узнать имя таблицы, перейдите на вкладку «Конструктор» и посмотрите его. Можете поменять имя на любое другое.
Функция ДВССЫЛ создает ссылку на ячейку или диапазон. Теперь ваш элемент в ячейке привязан к массиву данных.
Попробуем увеличить количество городов.
Обратная процедура - подстановка данных из выпадающего списка в таблицу Excel, работает очень просто. В ячейку, куда надо вставить выбранное значение из таблицы, введите формулу:
Адрес_ячейки
Например, если перечень данных находится в ячейке D1, то в ячейке, куда будут выведены выбранные результаты введите формулу
Как убрать (удалить) выпадающий список в Excel
Откройте окно настройки выпадающего списка и выберите «Любое значение» в разделе «Тип данных».
Ненужный элемент исчезнет.
Зависимые элементы
Иногда в Excel встречается необходимость создания нескольких перечней, когда один зависит от другого. Например, каждый город имеет несколько адресов. При выборе в первом мы должны получить только адреса выбранного населенного пункта.
В этом случае дайте имя каждому столбцу. Выделите без первой ячейки (названия) и нажмите правую кнопку мыши. Выберите «Присвоить имя».
Это будет название города.
При именовании Санкт-Петербурга и Нижнего Новгорода Вы получите ошибку, так как имя не может содержать пробелов, символов подчеркивания, специальных символов и т.д.
Поэтому переименуем эти города, поставив нижнее подчеркивание.
Первый элемент в ячейке A9 создаем обычным образом.
А во втором пропишем формулу:
ДВССЫЛ(A9)
Сначала Вы увидите сообщение об ошибке. Соглашайтесь.
Проблема в отсутствии выбранного значения. Как только в первом перечне будет выбран город, второй заработает.
Как настроить зависимые выпадающие списки в Excel с поиском
Можно использовать динамический диапазон данных для второго элемента. Это удобнее, если количество адресов будет расти.
Создадим выпадающий перечень городов. Оранжевым выделен именованный диапазон.
Для второго перечня нужно ввести формулу:
СМЕЩ($A$1;ПОИСКПОЗ($E$6;$A:$A;0)-1;1;СЧЁТЕСЛИ($A:$A;$E$6);1)
ПОИСКПОЗ возвращает номер ячейки с выбранным в первом списке (E6) городом в указанной области SA:$A.
СЧЕТЕСЛИ считает количество совпадений в диапазоне со значением в указанной ячейке (E6).
Мы получили связанные выпадающие списки в Excel с условием на совпадение и поиском диапазона для него.
Мультивыбор
Часто нам необходимо получить несколько значений из набора данных. Можно вывести их в разные ячейки, а можно объединить в одну. В любом случае необходим макрос.
Нажмите на ярлыке листа внизу правую кнопку мыши и выберите команду «Просмотреть код».
Откроется окно разработчика. В него надо вставить следующий алгоритм.
Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("C2:F2")) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False If Len(Target.Offset(1, 0)) = 0 Then Target.Offset(1, 0) = Target Else Target.End(xlDown).Offset(1, 0) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub
Обратите внимание, что в строке
If Not Intersect(Target, Range("E7")) Is Nothing And Target.Cells.Count = 1 Then
Следует проставить адрес ячейки со списком. У нас это будет E7.
Вернитесь на лист Excel и создайте в ячейке E7 список.
При выборе значения будут появляться под ним.
Следующий код позволит накапливать значения в ячейке.
Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("E7")) 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
Как только Вы переведете указатель на другую ячейку, Вы увидите перечень выбранных городов. Для прочитайте эту статью.
Мы рассказали, как добавить и изменить выпадающий список в ячейку Excel. Надеемся, эта информация поможет вам.
Отличного Вам дня!
Под выпадающим списком понимается содержание в одной ячейке нескольких значений. Когда пользователь щелкает по стрелочке справа, появляется определенный перечень. Можно выбрать конкретное.
Очень удобный инструмент Excel для проверки введенных данных. Повысить комфорт работы с данными позволяют возможности выпадающих списков: подстановка данных, отображение данных другого листа или файла, наличие функции поиска и зависимости.
Создание раскрывающегося списка
Путь: меню «Данные» - инструмент «Проверка данных» - вкладка «Параметры». Тип данных – «Список».
Ввести значения, из которых будет складываться выпадающий список, можно разными способами:
Любой из вариантов даст такой результат.
Выпадающий список в Excel с подстановкой данных
Необходимо сделать раскрывающийся список со значениями из динамического диапазона. Если вносятся изменения в имеющийся диапазон (добавляются или удаляются данные), они автоматически отражаются в раскрывающемся списке.
Протестируем. Вот наша таблица со списком на одном листе:
Добавим в таблицу новое значение «елка».
Теперь удалим значение «береза».
Осуществить задуманное нам помогла «умная таблица», которая легка «расширяется», меняется.
Теперь сделаем так, чтобы можно было вводить новые значения прямо в ячейку с этим списком. И данные автоматически добавлялись в диапазон.
Когда мы введем в пустую ячейку выпадающего списка новое наименование, появится сообщение: «Добавить введенное имя баобаб в выпадающий список?».
Нажмем «Да» и добавиться еще одна строка со значением «баобаб».
Выпадающий список в Excel с данными с другого листа/файла
Когда значения для выпадающего списка расположены на другом листе или в другой книге, стандартный способ не работает. Решить задачу можно с помощью функции ДВССЫЛ: она сформирует правильную ссылку на внешний источник информации.
- Делаем активной ячейку, куда хотим поместить раскрывающийся список.
- Открываем параметры проверки данных. В поле «Источник» вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).
Имя файла, из которого берется информация для списка, заключено в квадратные скобки. Этот файл должен быть открыт. Если книга с нужными значениями находится в другой папке, нужно указывать путь полностью.
Как сделать зависимые выпадающие списки
Возьмем три именованных диапазона:
Это обязательное условие. Выше описано, как сделать обычный список именованным диапазоном (с помощью «Диспетчера имен»). Помним, что имя не может содержать пробелов и знаков препинания.
- Создадим первый выпадающий список, куда войдут названия диапазонов.
- Когда поставили курсор в поле «Источник», переходим на лист и выделяем попеременно нужные ячейки.
- Теперь создадим второй раскрывающийся список. В нем должны отражаться те слова, которые соответствуют выбранному в первом списке названию. Если «Деревья», то «граб», «дуб» и т.д. Вводим в поле «Источник» функцию вида =ДВССЫЛ(E3). E3 – ячейка с именем первого диапазона.
- Создаем стандартный список с помощью инструмента «Проверка данных». Добавляем в исходный код листа готовый макрос. Как это делать, описано выше. С его помощью справа от выпадающего списка будут добавляться выбранные значения. Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("Е2:Е9" )) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False If Len(Target.Offset(0, 1)) = 0 Then Target.Offset(0, 1) = Target Else Target.End (xlToRight).Offset(0, 1) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub
- Чтобы выбранные значения показывались снизу, вставляем другой код обработчика. Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("Н2:К2" )) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False If Len(Target.Offset(1, 0)) = 0 Then Target.Offset(1, 0) = Target Else Target.End (xlDown).Offset(1, 0) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub
- Чтобы выбираемые значения отображались в одной ячейке, разделенные любым знаком препинания, применим такой модуль.
Выбор нескольких значений из выпадающего списка Excel
Бывает, когда из раскрывающегося списка необходимо выбрать сразу несколько элементов. Рассмотрим пути реализации задачи.
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
Не забываем менять диапазоны на «свои». Списки создаем классическим способом. А всю остальную работу будут делать макросы.
Выпадающий список с поиском
При вводе первых букв с клавиатуры высвечиваются подходящие элементы. И это далеко не все приятные моменты данного инструмента. Здесь можно настраивать визуальное представление информации, указывать в качестве источника сразу два столбца.
Проще всего эту задачу выполнить следующим образом. Нажатием правой кнопки по ячейке под столбцом с данными вызываем контекстное меню. Здесь интересует поле Выбрать из раскрывающегося списка . То же самое можно сделать, нажав комбинацию клавиш Alt+Стрелка вниз .
Однако, такой метод не сработает, если желаете создать список в другой ячейке, не входящей в диапазон и в более, чем одной до или после. Это позволит сделать следующий метод.
Стандартный способ
Потребуется выделить диапазон ячеек
, из которых будет создан выпадающий список
, после чего Вставка
– Имя
– Присвоить
(Excel 2003). В более новой версии (2007, 2010, 2013, 2016) перейдите на вкладку Формулы
, где в разделе Определенные имена
обнаружите кнопку Диспетчер имен
.
Жмем кнопку Создать , вносите имя, можно любое, после чего ОК .
Выделите ячейки (или несколько), где хотите вставить выпадающий перечень требуемых полей. В меню выберите Данные – Тип данных – Список . В поле Источник внесите ранее созданное имя или же можно просто указать диапазон, что будет равносильно.
Теперь полученную ячейку можно скопировать в любое место на листе, она будет содержать перечень необходимых элементов таблицы. Также её можно растянуть, чтобы получить диапазон с выпадающими списками.
Интересный момент – при изменении данных в диапазоне, основанный на нем список также будет меняться, то есть он будет динамическим .
Используем элементы управления
Метод основан на вставке элемента управления, называемом «поле со списком «, которое будет представлять собой диапазон данных.
Выберите вкладку Разработчик
(для Excel 2007/2010), в других версиях потребуется активировать эту вкладку на ленте в параметрах
– Настроить ленту
.
Переходим на эту вкладку – жмем кнопку Вставить . В элементах управления выбираем Поле со списком (не ActiveX) и нажмите по значку. Нарисуйте прямоугольник .
Правой кнопкой по нему – Формат объекта
.
Связью с ячейкой выберите то поле, куда следует поместить порядковый номер элемента в списке. Затем жмем ОК .
Применение элементов ActiveX
Все, как и в предыдущем только выбираем Поле со списком
(ActiveX).