Выпадающий список в ячейке листа
Видео
У кого мало времени и нужно быстро ухватить суть - смотрим обучающее видео:
Кому интересны подробности и нюансы всех описанных способов - дальше по тексту.
Способ 1. Примитивный
Один щелчок правой кнопкой мыши по пустой ячейке под столбцом с данными, команда контекстного меню Выбрать из раскрывающегося списка (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!). Ориентируйтесь по всплывающим подсказкам - Поле со списком:
Щелкните по значку и нарисуйте небольшой горизонтальный прямоугольник - будущий список. - Щелкните по нарисованному списку правой кнопкой мыши и выберите команду Формат объекта (Format control). В появившемся диалоговом окне задайте
- Формировать список по диапазону - выделите ячейки с наименованиями товаров, которые должны попасть в список
- Связь с ячейкой - укажите ячейку куда нужно выводить порядковый номер выбранного пользователем элемента.
- Количество строк списка - сколько строк показывать в выпадающем списке. По умолчанию - 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
- Выбор фото из выпадающего списка
- Автоматическое удаление уже использованных элементов из выпадающего списка
- Выпадающий список с автоматическим добавлением новых элементов
Николай, подскажите пожалуйста, можно ли привязать к выпадающему списку ( месяца года) таблицу с ее формой и ее же уникальными данными?Объясню почему форма важна, т.к. в году существуют как 5 недельные месяцы так и 6 недельные, и соответственно данные, содержащие уникальные данные по каждому из дней месяца.
Только без конкретного примера ничего определенного сказать не смогу. Лучше создайте тему на форуме, объясните ситуацию, приложите свой файл и вежливо попросите помощи-совета. А мы обязательно поможем
Сделал список, сохранил на сервере. откріл с другого компа. там такой же офис - списка нету =(
как так?
Подскажите, возможно ли соединить работу выпадающего списка с автофильтром. Т.е. при выборе строки из выпадающего списка включается автофильтр с тем же значением что и в выпадающем списке в определенном столбце таблицы.
Спасибо!
Рано я начал радоваться, что все работает.
Я соединил Ваш "Способ 4. Элемент ActiveX" с "Суперфильтр на VBA".
"Элемент ActiveX" работает четко, а "Суперфильтр на VBA" начинает работать только после того как активизирую ячейку куда подставляется переменная элементом ActiveX. Т.е. после того как курсор поставлен в саму ячейку и нажата клавиша Enter. Как быть?
Заранее СПАСИБО.
Все сделал по инструкции, получилось. Для слов выпадающих из списка создал отдельный лист, далее скрыл его и поставил пароль.
Теперь закидываю его в дропбокс. Коллеги копируют файл с дропбокса, все открывается, но выпадающий список не появляется.
Пробовал через флешку передавать, по электронке и все равно нет списка.
Подскажите, пожалуйста, что делать???
заранее благодарю
При использовании способа "Стандартный"
Не работает "проверка вводимых значений" т.е. помимо указанного списка, можно ввести любое слово.
Как можно это исправить???
Спасибо!
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo l
With ActiveWindow
If Target.Validation.Type = 3 Then
.Zoom = 100: Exit Sub
l: .Zoom = 60
End If: End With: End Sub
Вот такой код нужно прописать и все будет работать.
Спасибо за сайт.
В Ехсеl 2010 после некоторых манипуляций пропали кнопки выпадающих (раскрывающихся) списков. Поиском пока ничего не нашел. Как это исправить?
Мои "пять копеек" к статье...
В конце в сравнительной таблице указано, что в 4-м способе НЕльзя сделать связанные списки. Но ведь можно!
Мне подсказали в это теме, начиная с третьего поста:
Огромная благодарность автору и форумчанам!!!
при использовании способа №3 "поле Элемента управления" возникает проблема: при переключении между листами в книге Excel пишет ошибку "недостаточно системных ресурсов для полного вывода на экран".
Это происходит тогда, когда кнопка "поле" обращается к списку, расположенному на другой странице (если расположить список (и связь) на этой странице - все норм).
Но, когда исходные значения располагаются в одной строке (разных столбцах), то 3-й и 4-й способы НЕ РАБОТАЮТ.
Как сделать, чтобы они работали?
Sub List_ini()
Dim Max As Integer
Dim Data() As String
Max = Range([A2], [A2].End(xlToRight)).Count
ReDim Data(Max) As String
For i = 0 To Max
Data(i) = Range("A2";).Cells(1, 1 + i)
Next
ComboBox1.List = Data()
End Sub
Подскажите, в каком направлении копать?
У мене еще один вопрос - можно ли сделать динамическим размер Таблицы, базируясь на каком-нибудь правиле, типа как смещение по кол-ву значени