Содержание
- Способ 2. Стандартный
- Способ 3. Элемент управления
- Способ 4. Элемент ActiveX
- Итоговая сравнительная таблица всех способов
- Ссылки по теме:
Этот способ представляет собой вставку на лист нового объекта — элемента управления "поле со списком" с последующей привязкой его к диапазонам на листе. Для этого:
Выпадающий список в ячейке листа
Один щелчок правой кнопкой мыши по пустой ячейке под столбцом с данными, команда контекстного меню Выбрать из раскрывающегося списка (Choose from drop-down list) или нажать сочетание клавиш ALT+стрелка вниз. Способ не работает, если ячейку и столбец с данными отделяет хотя бы одна пустая строка или вам нужен товар, который еще ни разу не вводился выше:
Способ 2. Стандартный
- Выделите ячейки с данными, которые должны попасть в выпадающий список (например, наименованиями товаров).
- Если у вас Excel 2003 или старше — выберите в меню Вставка — Имя — Присвоить(Insert — Name — Define), если Excel 2007 или новее — откройте вкладку Формулы (Formulas) и воспользуйтесь кнопкой Диспетчер имен (Name Manager), затем Создать. Введите имя (можно любое, но обязательно без пробелов и начать с буквы!) для выделенного диапазона (например Товары). Нажмите ОК.
- Выделите ячейки (можно сразу несколько), в которых хотите получить выпадающий список и выберите в меню (на вкладке) Данные — Проверка (Data — Validation). Из выпадающего списка Тип данных (Allow) выберите вариант Список (List) и введите в строчку Источник (Source) знак равенства и имя диапазона (т.е. =Товары).
Нажмите ОК.
Важный нюанс. В качестве источника данных для списка может выступать и динамический именованный диапазон, например прайс-лист. Тогда при дописывании новых товаров к прайсу, они будут автоматически добавляться к выпадающему списку. Еще одним часто используемым трюком для таких списков является создание связанных выпадающих списков (когда содержимое одного списка меняется в зависимости от выбора в другом).
Способ 3. Элемент управления
Этот способ представляет собой вставку на лист нового объекта — элемента управления "поле со списком" с последующей привязкой его к диапазонам на листе. Для этого:
- В Excel 2007/2010 откройте вкладку Разработчик (Developer). В более ранних версиях — панель инструментов Формы (Forms) через меню Вид — Панели инструментов — Формы (View — Toolbars — Forms). Если этой вкладки не видно, то нажмите кнопку Офис — Параметры Excel— флажок Отображать вкладку Разработчик на ленте (Office Button — Excel Options — Show Developer Tab in the Ribbon)
- Найдите значок выпадающего списка среди элементов управления форм (не ActiveX!). Ориентируйтесь по всплывающим подсказкам — Поле со списком:
- Формировать список по диапазону — выделите ячейки с наименованиями товаров, которые должны попасть в список
- Связь с ячейкой — укажите ячейку куда нужно выводить порядковый номер выбранного пользователем элемента.
- Количество строк списка — сколько строк показывать в выпадающем списке. По умолчанию — 8, но можно больше, чего не позволяет предыдущий способ.
После нажатия на ОК списком можно пользоваться.
Чтобы вместо порядкового номера элемента выводилось его название можно дополнительно использовать функцию ИНДЕКС (INDEX), которая умеет выводить содержимое нужной по счету ячейки из диапазона:
Способ 4. Элемент ActiveX
Этот способ частично напоминает предыдущий. Основное отличие в том, что на лист добавляется не элемент управления, а элемент ActiveX "Поле со списком" из раскрывающегося набора под кнопкой Вставить (Insert) с вкладки Разработчик (Developer):
Механизм добавления тот же — выбираем объект из списка и рисуем его на листе. А вот дальше начинаются серьезные отличия от предыдущего способа.
Во-первых, созданный выпадающий ActiveX список может находится в двух принципиально разных состояниях — режиме отладки, когда можно настраивать его параметры и свойства, двигать его по листу и менять размеры и — режиме ввода, когда единственное, что можно — выбирать из него данные. Переключение между этими режимами происходит с помощью кнопки Режим Конструктора (Design Mode) на вкладке Разработчик (Developer):
Если эта кнопка нажата, то мы можем настраивать параметры выпадающего списка, нажав соседнюю кнопку Свойства (Properties), которая откроет окно со списком всех возможных настроек для выделенного объекта:
Самые нужные и полезные свойства, которые можно и нужно настроить:
- ListFillRange — диапазон ячеек, откуда берутся данные для списка. Выделить мышью диапазон он не даст, надо просто вписать его руками с клавиатуры (например, Лист2!A1:A5)
- LinkedCell — связанная ячейка, куда будет выводиться выбранный из списка элемент
- ListRows — количество отображаемых строк
- Font — шрифт, размер, начертание (курсив, подчеркивание и т.д. кроме цвета)
- ForeColor и BackColor — цвет текста и фона, соответственно
Большим и жирным плюсом этого способа является возможность быстрого перехода к нужному элементу в списке при вводе первых букв с клавиатуры(!), чего нет у всех остальных способов. Приятным моментом, также, является возможность настройки визуального представления (цветов, шрифтов и т.д.)
При использовании этого способа, также возможно указывать в качестве ListFillRange не только одномерные диапазоны. Можно, например задать диапазон из двух столбцов и нескольких строк, указав дополнительно, что выводить нужно два столбца (свойство ColumnCount=2). Тогда можно получить весьма привлекательные результаты, окупающие все потраченные на дополнительные настройки усилия:
Итоговая сравнительная таблица всех способов
| Способ 1. Примитивный | Способ 2. Стандартный | Способ 3. Элемент управления | Способ 4. Элемент ActiveX | |
| Сложность | низкая | средняя | высокая | высокая |
| Возможность настройки шрифта, цвета и т.д. | нет | нет | нет | да |
| Количество отображаемых строк | всегда 8 | всегда 8 | любое | любое |
| Быстрый поиск элемента по первым буквам | нет | нет | нет | да |
| Необходимость использования дополнительной функции ИНДЕКС | нет | нет | да | нет |
| Возможность создания связанных выпадающих списков | нет | да | нет | нет |
Ссылки по теме:
- Выпадающий список с данными из другого файла
- Создание зависимых выпадающих списков
- Автоматическое создание выпадающих списков надстройкой PLEX
- Выбор фото из выпадающего списка
- Автоматическое удаление уже использованных элементов из выпадающего списка
- Выпадающий список с автоматическим добавлением новых элементов
Выпадающий список в Excel с помощью инструментов или макросов
Под выпадающим списком понимается содержание в одной ячейке нескольких значений. Когда пользователь щелкает по стрелочке справа, появляется определенный перечень. Можно выбрать конкретное.
Очень удобный инструмент Excel для проверки введенных данных. Повысить комфорт работы с данными позволяют возможности выпадающих списков: подстановка данных, отображение данных другого листа или файла, наличие функции поиска и зависимости.
Создание раскрывающегося списка
Путь: меню «Данные» — инструмент «Проверка данных» — вкладка «Параметры». Тип данных – «Список».
Ввести значения, из которых будет складываться выпадающий список, можно разными способами:
- Вручную через «точку-с-запятой» в поле «Источник».
- Ввести значения заранее. А в качестве источника указать диапазон ячеек со списком.
- Назначить имя для диапазона значений и в поле источник вписать это имя.
Любой из вариантов даст такой результат.
Выпадающий список в Excel с подстановкой данных
Необходимо сделать раскрывающийся список со значениями из динамического диапазона. Если вносятся изменения в имеющийся диапазон (добавляются или удаляются данные), они автоматически отражаются в раскрывающемся списке.
- Выделяем диапазон для выпадающего списка. В главном меню находим инструмент «Форматировать как таблицу».
- Откроются стили. Выбираем любой. Для решения нашей задачи дизайн не имеет значения. Наличие заголовка (шапки) важно. В нашем примере это ячейка А1 со словом «Деревья». То есть нужно выбрать стиль таблицы со строкой заголовка. Получаем следующий вид диапазона:
- Ставим курсор в ячейку, где будет находиться выпадающий список. Открываем параметры инструмента «Проверка данных» (выше описан путь). В поле «Источник» прописываем такую функцию:
Протестируем. Вот наша таблица со списком на одном листе:
Добавим в таблицу новое значение «елка».
Теперь удалим значение «береза».
Осуществить задуманное нам помогла «умная таблица», которая легка «расширяется», меняется.
Теперь сделаем так, чтобы можно было вводить новые значения прямо в ячейку с этим списком. И данные автоматически добавлялись в диапазон.
- Сформируем именованный диапазон. Путь: «Формулы» — «Диспетчер имен» — «Создать». Вводим уникальное название диапазона – ОК.
- Создаем раскрывающийся список в любой ячейке. Как это сделать, уже известно. Источник – имя диапазона: =деревья.
- Снимаем галочки на вкладках «Сообщение для ввода», «Сообщение об ошибке». Если этого не сделать, Excel не позволит нам вводить новые значения.
- Вызываем редактор Visual Basic. Для этого щелкаем правой кнопкой мыши по названию листа и переходим по вкладке «Исходный текст». Либо одновременно нажимаем клавиши Alt + F11. Копируем код (только вставьте свои параметры).
Выпадающий список в эксель | С автозаполнением, с подстановкой и поиском
Private Sub Worksheet_Change(ByVal Target As Range) Dim lReply As Long If Target.Cells.Count > 1 Then Exit Sub If Target.Address = "$C$2" Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.CountIf(Range("Деревья"), Target) = 0 Then lReply = MsgBox("Добавить введенное имя " & _ Target & " в выпадающий список?", vbYesNo + vbQuestion) If lReply = vbYes Then Range("Деревья").Cells(Range("Деревья").Rows.Count + 1, 1) = Target End If End If End If End Sub
Когда мы введем в пустую ячейку выпадающего списка новое наименование, появится сообщение: «Добавить введенное имя баобаб в выпадающий список?».
Нажмем «Да» и добавиться еще одна строка со значением «баобаб».
Выпадающий список в Excel с данными с другого листа/файла
Когда значения для выпадающего списка расположены на другом листе или в другой книге, стандартный способ не работает. Решить задачу можно с помощью функции ДВССЫЛ: она сформирует правильную ссылку на внешний источник информации.
- Делаем активной ячейку, куда хотим поместить раскрывающийся список.
- Открываем параметры проверки данных. В поле «Источник» вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).
Имя файла, из которого берется информация для списка, заключено в квадратные скобки. Этот файл должен быть открыт. Если книга с нужными значениями находится в другой папке, нужно указывать путь полностью.
Как сделать зависимые выпадающие списки
Возьмем три именованных диапазона:
Это обязательное условие. Выше описано, как сделать обычный список именованным диапазоном (с помощью «Диспетчера имен»). Помним, что имя не может содержать пробелов и знаков препинания.
- Создадим первый выпадающий список, куда войдут названия диапазонов.
- Когда поставили курсор в поле «Источник», переходим на лист и выделяем попеременно нужные ячейки.
- Теперь создадим второй раскрывающийся список. В нем должны отражаться те слова, которые соответствуют выбранному в первом списке названию. Если «Деревья», то «граб», «дуб» и т.д. Вводим в поле «Источник» функцию вида =ДВССЫЛ(E3). E3 – ячейка с именем первого диапазона.
Выбор нескольких значений из выпадающего списка Excel
Бывает, когда из раскрывающегося списка необходимо выбрать сразу несколько элементов. Рассмотрим пути реализации задачи.
-
Создаем стандартный список с помощью инструмента «Проверка данных». Добавляем в исходный код листа готовый макрос. Как это делать, описано выше. С его помощью справа от выпадающего списка будут добавляться выбранные значения.
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
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
Не забываем менять диапазоны на «свои». Списки создаем классическим способом. А всю остальную работу будут делать макросы.
Выпадающий список с поиском
- На вкладке «Разработчик» находим инструмент «Вставить» – «ActiveX». Здесь нам нужна кнопка «Поле со списком» (ориентируемся на всплывающие подсказки).
- Щелкаем по значку – становится активным «Режим конструктора». Рисуем курсором (он становится «крестиком») небольшой прямоугольник – место будущего списка.
- Жмем «Свойства» – открывается перечень настроек.
- Вписываем диапазон в строку ListFillRange (руками). Ячейку, куда будет выводиться выбранное значение – в строку LinkedCell. Для изменения шрифта и размера – Font.
При вводе первых букв с клавиатуры высвечиваются подходящие элементы. И это далеко не все приятные моменты данного инструмента. Здесь можно настраивать визуальное представление информации, указывать в качестве источника сразу два столбца.
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Карта сайта
- Скачать примеры
Выпадающий список в Excel
В программе Microsoft Excel есть возможность создать выпадающий список – то есть сделать так, чтобы одна ячейка содержала несколько значений, между которыми может переключаться пользователь. Это очень удобный инструмент для работы с таблицами, в частности, если речь идёт о проверке введённых данных. Поэтому если вы не знаете, как сделать в экселе выпадающий список, то вам помогут приведённые в статье инструкции.
Выпадающий список в Excel
Создание выпадающего списка
Для начала рассмотрим самый простой способ, который поможет вам сделать раскрывающийся список. Чтобы осуществить его, делайте всё как в инструкции ниже:
Шаг 1. Перейдите во вкладку «Данные», которая расположена на верхней панели, затем в блоке «Работа с данными» выберите инструмент проверки данных (на скриншоте показано, какой иконкой он изображен).
Переходим во вкладку «Данные», которая расположена на верхней панели, затем в блоке «Работа с данными» выбираем инструмент проверки данных
Шаг 2. Теперь откройте самую первую вкладку «Параметры», и установите «Список» в перечне типа данных.
В первой вкладке «Параметры», в разделе «Тип данных» выставляем «Список»
Шаг 3. Теперь в поле ввода данных «Источник» вы можете указывать значения, которые будут составлять раскрывающийся список. Сделать это можно следующими способами:
-
вручную. Для этого просто введите значения нужных ячеек, разделив их точкой с запятой;
В поле «Источник» вводим значения нужных ячеек
Вбиваем в поле ввода адрес первой и последней ячейки через двоеточие, нажимаем «ОК»
На заметку! Чтобы не вводить диапазон ячеек вручную, достаточно левой кнопкой мышки выделить первую ячейку со значением, поставить двоеточие и выделить мышью последнюю ячейку со значением.
После того, как вы воспользуетесь любым из способов и укажете имя диапазона значений, вы получите такой результат, как на картинке ниже.
Результат сделанного выпадающего списка
На заметку! Есть ещё один способ указать значение в источнике – написать в поле ввода имя диапазона. Этот способ самый быстрый, но прежде чем прибегать к нему, нужно создать именованный диапазон. О том, как это сделать, мы поговорим позже.
Форма для быстрого создания выпадающего списка
Видео — Создание выпадающих списков в Excel
Раскрывающийся список с подстановкой данных
Если ваша таблица предполагает постоянные изменения, и вы не хотите, чтобы это повлияло на содержимое вашего выпадающего списка, то вам поможет следующий способ. Воспользуйтесь им, и тогда редактирование данных ячеек будет автоматически отображено в выпадающем списке. Для этого проделайте следующее:
-
Выделите левой кнопкой мышки диапазон для списка (в данном примере это будет перечень деревьев), затем откройте вкладку «Главная» и выберите меню «Форматировать как таблицу».
Выделяем левой кнопкой мышки диапазон для списка, открываем вкладку «Главная» и выбираем меню «Форматировать как таблицу»
Выбираем любой понравившийся стиль
Устанавливаем галочку возле «Таблица с заголовками», нажимаем «ОК»
Динамические выпадающие списки, ДВССЫЛ и Умные Таблицы — Функции Excel (7)
Результат отформатированной таблицы
Выделяем левым кликом мыши ту ячейку, в которой будет расположен выпадающий список, и переходим во вкладку «Данные»
В поле «Источник» печатаем функцию «=ДВССЫЛ(“Имя таблицы[Заголовок]”)», подставляя свои данные, как на примере
Итак, список готов. Выглядеть он будет вот так.
Готовый список
На заметку! У этого способа есть принципиальное отличие от первого – в качестве списка используется готовая таблица, а не диапазон значений. Это значит, что любые изменения в таблице будут отображаться и в выпадающем списке.
Давайте протестируем это. Для начала добавим в нашу новую отформатированную таблицу новую ячейку «ёлка». Как видите, это же значение добавилось в список.
Добавляем в таблицу строку с новым значением, она автоматически появится в выпадающем списке
Если удалить ячейку (в данном случае мы удалили «берёза»), это тоже отобразится на содержании раскрывающегося списка.
Удаляем значение из таблицы, оно автоматически удалится из выпадающего списка
На заметку! В этом способе мы имели дело с так называемой «умной таблицей». Она легко расширяется, и это её свойство полезно для многих манипуляций с таблицами Excel, в том числе и для создания выпадающего списка.
Зависимые раскрывающиеся списки
Чтобы создать зависимые выпадающие списки, следуйте дальнейшей пошаговой инструкции:
-
Для начала вам нужно создать именованный диапазон. Перейдите во вкладку «Формулы», затем выберите «Диспетчер имён» и «Создать».
Выделяем диапазон ячеек со значением, открываем вкладку «Формулы», нажимаем «Диспетчер имен»
Нажимаем «Создать»
Пишем имя «Деревья», нажимаем «ОК»
Создаем таким же способом остальные диапазоны
В поле «Источник» указываем ячейки с названием диапазонов, нажимаем «ОК»
Выпадающий список с названием диапазона ячеек
В поле ввода «Источник» вводим функцию «=ДВССЫЛ(E1)», нажимаем «ОК»
Результат выпадающего связанного списка
Теперь вам известны несколько способов, как создать выпадающее меню в Excel-таблице. Это может пригодиться вам в ряде случаев, а особенно – в проверке данных. Выбор правильного способа должен осуществляться в зависимости от того, с каким типом таблицы вы работаете. Если это «одноразовая» таблица, то подойдёт первый способ – он быстрый и лёгкий. Если же таблица требует постоянных изменений, то лучше всего будет воспользоваться тем способом, который включает в себя создание «умных таблиц» и именованных диапазонов.
Видео — Связанные выпадающие списки: легко и быстро
Понравилась статья?
Сохраните, чтобы не потерять!
Похожие статьи
-
В графе «Тип данных» выберите «Список», а в качестве источника укажите название диапазона, поставив перед ним «=». В нашем примере вышло «=бренды»….
-
Хотите выбирать наименование товара из выпадающего списка и получать в соседней ячейке фотографию выбранного продукта? Или выбирать человека из списка и…
-
Переходим во вкладку «Данные» программы Microsoft Excel. Выделяем область таблицы, где собираемся применять выпадающий список. Жмем на кнопку «Проверка…