Как настроить выбор из списка в excel. Как сделать сложный выпадающий список в excel
При заполнении таблички в редакторе Excel очень часто данные повторяются. К примеру, вы пишите наименование товара или ФИО работника. Сегодня мы поговорим о том, как в Эксель сделать выпадающий список, чтобы постоянно не вводить одно и то же, а просто выбирать нужное значение.
Есть самый простой вариант справиться с поставленной задачей. Вы делаете правый клик мышкой по ячейке под столбиком с данными для вызова контекстного меню. Тут ищем пункт «Выбрать из раскрывающегося списка». Это же действие легко выполнить комбинацией Alt + стрелочка вниз .
Вот только данный метод не подойдет, если необходимо сделать такой объект в Эксель в другой ячейке и в 2-3 и т.д до и после. При такой необходимости используйте следующий вариант.
Традиционный способ
Выделяете область самих ячеек, из которых создаете выпадающий перечень, далее переходите:
Вставка/Имя/Присвоить (Excel 2003)
В свежих версиях (2007, 2010, 2013, 2016) переходите:
Формулы/Определенные имена/Диспетчер имен/Создать
Вписываете любое наименование, тапаете ОК .
Затем выделяете ячейки (или несколько), куда планируете вставить перечень заданных полей. В меню переходите:
Данные/Тип данных/Список
В разделе «Источник» вносите ранее написанное имя, просто отмечаете диапазон. Полученную ячеечку можете копировать в любое место, в ней уже будет меню заданных табличных элементов.
Кроме того, вы можете ее растягивать, чтобы создать диапазон. Кстати, в случае изменения информации в нем, списочная информация тоже будет изменяться, она является динамической.
Как в Экселе сделать выпадающий список: пользуемся управленческими методами
При использовании этого варианта, вы вставляете элемент управления, представляющий собой непосредственно диапазон данных. Для этого:
- во-первых, ищите раздел «Разработчик» (Эксель 2007/2010), в других выпусках активируете самостоятельно через параметры «Настроить ленту»;
- во-вторых, переходите на раздел, кликаете «Вставить»;
- в-третьих, выбираете «Поле…» и жмете по значку;
- рисуете прямоугольник;
- делаете правый клик по нему и жмете Формат объекта;
- ищите «Формировать…», выделяете нужные границы;
- отмечаете ту область, куда хотите установить порядковый номер элемента в перечне;
- жмете ОК.
Как создать в Экселе выпадающее меню: пользуемся элементами ActiveX
Действия аналогичны вышеописанным, но ищем «Поле с ActiveX».
Здесь основные отличия в том, что спецэлемент ActiveX может быть в 2 вариантах - режим отладки, который позволяет менять параметры, и - режим ввода, получится сделать только выборку сведений из него. Поменять режим можно клавишей Режим Конструктора в разделе Разработчик . С помощью этого способа вы можете настроить цвет, шрифт и выполнить оперативный поиск.
Дополнительные возможности
В программе Excel возможно создание связанного выпадающего перечня. То есть при выборе какого-то значения, в другой графе можно подобрать нужные ему параметры. К примеру, вы выбираете какой-то продукт, и понадобилось отметить меру измерения, к примеру, грамм, килограмм.
В первую очередь, следует сделать табличку, где будут сами списочки, а потом отдельно окошки с названиями продуктов.
Для второй активируем окошко проверки сведений, но в пункте «Источник» пишем «=ДВССЫЛ» и адрес 1-й ячейки. Все получилось.
После этого, чтобы окошки снизу дополнились такими же свойствами, выделяем верхний участок и с нажатой мышкой перетаскиваем все вниз. Готово.
Как в Эксель сделать выпадающий список? Как видите, это легко выполнить любым указанным методом. Просто выбирайте для себя самый оптимальный. А выбор будет зависеть от целей создания, предназначения, области использования, количества информации и прочего.
Создание списков инструментами Excel дает возможность не только сделать этот процесс максимально быстрым, но и унифицировать написание тех или иных слов и терминов. Последнее непосредственно касается создания так называемых выпадающих списков.
Списки для сортировки и заполнения, это те списки, элементы которых располагаются в строгой последовательности, кроме того, они циклически повторяются. Примерами таких списков являются дни недели, месяца года и т.п.
Приложение Excel позволяет не вписывать в ячейки каждый раз следующий день недели. Достаточно один раз вписать в ячейку, например, понедельник и выполнить автоматическое заполнение данными последующих строк или столбцов при помощи мыши. Для этого цепляем мышью уголок ячейки и протягиваем его куда нам нужно.
Изменять параметры такого списка можно в меню списки, которое открывается в основных параметрах Excel. Находим кнопку «Изменить списки» и нажимаем ее. В появившемся окне видны имеющиеся списки, также можно задавать новые. Для этого в окне «Элементы списка» нужно последовательно указать его части и «добавить» в списки. Также можно импортировать списки прямо с листа Excel, предварительно выделив необходимый диапазон.
Создание выпадающих списков
Выпадающие списки дают возможность не только вставлять в ячейку только заранее определенные значения, но и не дают возможности допустить ошибки при написании элементов этого списка. Чтобы в выпадающем окошке появлялся список нужно его предварительно создать. Это можно сделать как на открытом, так и на другом листе книги Excel.
Создание выпадающего списка с источником на этом же листе
Чтобы пользоваться выпадающим списком нужно сначала этот список создать. Создаем список на этом же (открытом) листе и проверяем правильность написания его составляющих. Затем выделяем ячейку или несколько ячеек, куда будем вставлять эти данные, открываем окошко «Проверка вводимых значений», которое открывается по прохождении следующего пути: данные/работа с данными/проверка данных.
В этом окне выбираем тип данных — список, в строке источник указываем диапазон ранее созданного списка-источника. Можно ввести этот диапазон с клавиатуры, но легче это сделать, просто выделив его мышью. После нажатия кнопки ОК увидим, возле нашей ячейки показывается стрелочка, при нажатии на которую выпадает наш перечень.
Создание выпадающего списка с источником на другом листе
Использование списка-источника, расположенного на том же активном листе, не совсем удобно, поскольку можно «нечаянно» изменить его содержимое. Поэтому предпочтительнее этот список «спрятать» на другом листе, а доступ к нему заблокировать.
В этом случае, описанным выше способом не обойтись, так как при открытом окне «Проверки…» мы не сможем попасть на другой лист, чтобы выделить или определить диапазон ячеек исходного списка. В этом случае пользуются назначением имени первоначальному списку.
Выполняем это так: создаем список данных на одном листе, затем выделяем его и присваиваем ему имя, кликаем последовательно вкладка формулы/присвоить имя (в разделе определенные имена), в открывшемся окне задаем имя списка. Если, предварительно мы не выделили список, то задаем диапазон его ячеек.
Переходим к выпадающему перечню на другом листе. Выделяем ячейки рабочего поля редактор, в которые будем вставлять элементы списка, открываем окно проверки… . В типе данных указываем список, в источнике ставим знак равенства и имя требуемого списка.
Как скрыть лист с источником списка
Лист с источниками выпадающих списков желательно открывать как можно реже, дабы случайно не внести в него ненужные изменения. Для этого лист можно защитить паролем и скрыть его отображение. Для того, чтобы спрятать лист нужно кликнуть по его ярлычку правой клавишей мыши и в открывшемся перечне команд нажать скрыть.
Таким образом Excel представляет большие возможности по созданию списков, даже по сравнению с текстовым процессором .
Элемент списка знаком нам по формам на сайтах. Удобно выбирать уже готовые значения. Например, никто не вводит месяц вручную, его берут из такого перечня. Заполнить выпадающий список в Excel можно с использованием различных инструментов. В статье рассмотрим каждый из них.
Как сделать выпадающий список в Excel
Как сделать выпадающий список в Excel 2010 или 2016 с помощью одной командой на панели инструментов? На вкладке «Данные» в разделе «Работа с данными» найдите кнопку «Проверка данных». Нажмите на нее и выберите первый пункт.
Откроется окно. Во вкладке «Параметры» в выпадающем разделе «Тип данных» выберите «Список».
Снизу появится строка для указания источников.
Указывать информацию можно по-разному.
![](https://i2.wp.com/windowstips.ru/wp-content/uploads/2017/11/vpgsch-spsk4.jpg)
Сначала назначим имя. Для этого создайте на любом листе такую таблицу.
Выделите ее и нажмите правую кнопку мыши. Щелкните по команде «Присвоить имя».
Введите имя в строку сверху.
Вызовите окно «Проверка данных» и в поле «Источник» укажите имя, поставив перед ним знак «=».
В любом из трех случаев Вы увидите нужный элемент. Выбор значения из выпадающего списка Excel происходит с помощью мыши. Нажмите на него и появится перечень указанных данных.
Вы узнали, как создать выпадающий список в ячейке Excel. Но можно сделать и больше.
Подстановка динамических данных Excel
Если Вы добавите какое-то значение в диапазон данных, которые подставляются в перечень, то в нем изменения не произойдет, пока вручную не будут указаны новые адреса. Чтобы связать диапазон и активный элемент, необходимо оформить первый как таблицу. Создайте вот такой массив.
Выделите его и на вкладке «Главная» выберите любой стиль таблицы.
Обязательно поставьте галочку внизу.
Вы получите такое оформление.
Создайте активный элемент, как было описано выше. В качестве источника введите формулу
Чтобы узнать имя таблицы, перейдите на вкладку «Конструктор» и посмотрите его. Можете поменять имя на любое другое.
Функция ДВССЫЛ создает ссылку на ячейку или диапазон. Теперь ваш элемент в ячейке привязан к массиву данных.
Попробуем увеличить количество городов.
Обратная процедура - подстановка данных из выпадающего списка в таблицу 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 для проверки введенных данных. Повысить комфорт работы с данными позволяют возможности выпадающих списков: подстановка данных, отображение данных другого листа или файла, наличие функции поиска и зависимости.
Создание раскрывающегося списка
Путь: меню «Данные» - инструмент «Проверка данных» - вкладка «Параметры». Тип данных – «Список».
Ввести значения, из которых будет складываться выпадающий список, можно разными способами:
![](https://i1.wp.com/exceltable.com/vba-macros/images/vba-macros2-2.png)
Любой из вариантов даст такой результат.
Выпадающий список в Excel с подстановкой данных
Необходимо сделать раскрывающийся список со значениями из динамического диапазона. Если вносятся изменения в имеющийся диапазон (добавляются или удаляются данные), они автоматически отражаются в раскрывающемся списке.
![](https://i0.wp.com/exceltable.com/vba-macros/images/vba-macros2-8.png)
Протестируем. Вот наша таблица со списком на одном листе:
![](https://i2.wp.com/exceltable.com/vba-macros/images/vba-macros2-9.png)
Добавим в таблицу новое значение «елка».
![](https://i0.wp.com/exceltable.com/vba-macros/images/vba-macros2-10.png)
Теперь удалим значение «береза».
![](https://i2.wp.com/exceltable.com/vba-macros/images/vba-macros2-11.png)
Осуществить задуманное нам помогла «умная таблица», которая легка «расширяется», меняется.
Теперь сделаем так, чтобы можно было вводить новые значения прямо в ячейку с этим списком. И данные автоматически добавлялись в диапазон.
![](https://i0.wp.com/exceltable.com/vba-macros/images/vba-macros2-12.png)
![](https://i0.wp.com/exceltable.com/vba-macros/images/vba-macros2-13.png)
![](https://i2.wp.com/exceltable.com/vba-macros/images/vba-macros2-16.png)
Когда мы введем в пустую ячейку выпадающего списка новое наименование, появится сообщение: «Добавить введенное имя баобаб в выпадающий список?».
Нажмем «Да» и добавиться еще одна строка со значением «баобаб».
Выпадающий список в Excel с данными с другого листа/файла
Когда значения для выпадающего списка расположены на другом листе или в другой книге, стандартный способ не работает. Решить задачу можно с помощью функции ДВССЫЛ: она сформирует правильную ссылку на внешний источник информации.
- Делаем активной ячейку, куда хотим поместить раскрывающийся список.
- Открываем параметры проверки данных. В поле «Источник» вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).
Имя файла, из которого берется информация для списка, заключено в квадратные скобки. Этот файл должен быть открыт. Если книга с нужными значениями находится в другой папке, нужно указывать путь полностью.
Как сделать зависимые выпадающие списки
Возьмем три именованных диапазона:
![](https://i0.wp.com/exceltable.com/vba-macros/images/vba-macros2-17.png)
Это обязательное условие. Выше описано, как сделать обычный список именованным диапазоном (с помощью «Диспетчера имен»). Помним, что имя не может содержать пробелов и знаков препинания.
- Создадим первый выпадающий список, куда войдут названия диапазонов.
- Когда поставили курсор в поле «Источник», переходим на лист и выделяем попеременно нужные ячейки.
- Теперь создадим второй раскрывающийся список. В нем должны отражаться те слова, которые соответствуют выбранному в первом списке названию. Если «Деревья», то «граб», «дуб» и т.д. Вводим в поле «Источник» функцию вида =ДВССЫЛ(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
- Чтобы выбираемые значения отображались в одной ячейке, разделенные любым знаком препинания, применим такой модуль.
![](https://i0.wp.com/exceltable.com/vba-macros/images/vba-macros2-18.png)
![](https://i0.wp.com/exceltable.com/vba-macros/images/vba-macros2-19.png)
![](https://i2.wp.com/exceltable.com/vba-macros/images/vba-macros2-20.png)
Выбор нескольких значений из выпадающего списка 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
Не забываем менять диапазоны на «свои». Списки создаем классическим способом. А всю остальную работу будут делать макросы.
Выпадающий список с поиском
![](https://i0.wp.com/exceltable.com/vba-macros/images/vba-macros2-21.png)
При вводе первых букв с клавиатуры высвечиваются подходящие элементы. И это далеко не все приятные моменты данного инструмента. Здесь можно настраивать визуальное представление информации, указывать в качестве источника сразу два столбца.
Выпадающие списки в Microsoft Excel незаменимы при создании объемных таблиц, работе с базами данных. В чем же конкретно заключается удобство этого инструмента?
Если при заполнении таблицы некоторые данные периодически повторяются, нет необходимости каждый раз вбивать вручную постоянное значение — например, наименование товара, месяц, ФИО сотрудника. Достаточно один раз закрепить повторяющийся параметр в списке.
Ячейки списка защищены от введения посторонних значений, что снижает вероятность допустить ошибку в работе.
Таблица, оформленная таким образом, выглядит аккуратно.
В статье я расскажу, как в Экселе сделать выпадающий список в ячейк
е и как с ним работать.
Формирование выпадающего списка
Один из распространенных примеров использования выпадающих списков – интернет-магазины, в которых вся продукция распределена по категориям – такая структура облегчает пользователям поиск по сайту.
Рассмотрим наглядно:
Все товары, перечисленные в таблице, нужно отнести к категории «Одежда». Чтобы создать выпадающий список для этого перечня, потребуется выполнить следующие действия:
Выделить любую ячейку, в которой будет создан список.
Зайти на вкладку «Данные», в раздел «Проверка данных».
В открывшемся окне выбрать вкладку «Параметры», а в перечне «Тип данных» вариант – «Список».
В появившейся строке необходимо указать все имеющиеся наименования списка. Сделать это можно двумя способами: выделить мышкой диапазон данных в таблице (в примере – ячейки А1-А7) или вбить названия вручную через точку с запятой.
Выделить все ячейки с нужными значениями, и, щелкнув правой кнопкой мыши, выбрать в контекстном меню пункт «Присвоить имя».
В строке «Имя» указать наименование списка – в данном случае, «Одежда».
Выделить ячейку, в которой создан список, и вписать созданное имя в строку «Источник» со знаком «=» вначале.
Итоговый результат выглядит так. Это самый простой вариант выпадающего списка. В зависимости от версии Excel, действий может быть больше или меньше, но в целом, инструкция универсальна для любой программы.
Как добавлять значения в список
Иногда возникает необходимость дополнить уже имеющийся перечень. В раскрывающемся списке все новые пункты отображаются автоматически при добавлении. Однако чтобы связать диапазон ячеек с добавленным вновь элементом, список требуется оформить в виде таблицы. Для этого нужно выделить диапазон значений, найти на вкладке «Главная» пункт «Форматировать как таблицу» и выбрать любой понравившийся стиль, например.
Возможно, будет полезно почитать:
- Аббатство - это католический монастырь ;
- Самые распространенные расклады ;
- Быт и обычаи Обычаи и нравы 19 века ;
- К чему снятся жабы и лягушки: мужчине, девушке, женщине, беременной – толкования разных сонников ;
- Основные характеристики марса ;
- Должностная инструкция транспортного экспедитора ;
- Татаро-монгольское иго или история о том, как ложь стала правдой ;
- Журавль толкование сонника ;