Выпадающий список в ячейке листа
Видео
У кого мало времени и нужно быстро ухватить суть - смотрим обучающее видео:
Кому интересны подробности и нюансы всех описанных способов - дальше по тексту.
Способ 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
- Выбор фото из выпадающего списка
- Автоматическое удаление уже использованных элементов из выпадающего списка
- Выпадающий список с автоматическим добавлением новых элементов
С помощью макросов можно сделать, чтобы выпадающий список элемента управления (способ 3) станет появляться в любой ячейке указанного диапазона.
Для этого в модуле листа, на котором должен находится выпадающий список, нужно разместить такой код...
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
' "B:B" - диапазон в котором появляется выпадающий список
If Not Intersect(Target, Range("B:B") Is Nothing Then
Call AddDropDown(Target)
End If
End Sub
А в любом стандартном модуле разместить...
Public Sub AddDropDown(Target As Range)
Dim Control As DropDown
Dim I As Integer
Set Control = ActiveSheet.DropDowns.Add(Target.Left, Target.Top, Target.Width, Target.Height) ' вписываем элемент управления в границы ячейки
Control.OnAction = "SelectDropDown"
Control.ListFillRange = Range("H5:H11").Address ' формировать список по диапазону
Control.DropDownLines = 10 ' количество строк списка
End Sub
Private Sub SelectDropDown()
On Error Resume Next
With ActiveSheet.DropDowns(Application.Caller)
.TopLeftCell.Value = .List(.ListIndex) ' записываем в ячейку, которая находится под элементом управления выбранный элемент выпадающего списка
.Delete ' удаляем элемент управления
End With
End Sub
Идея не моя. Взял из одной книги.
А состоит он в следующем:
Можно ли из выпадающего списка activeх вытащить текстовую информацию, так как она мне потом будет нужна в последующем.
Вытащить я имею ввиду, чтобы в другом файле я мог сослаться на эту ячейку и он извлек мне из нее текст.
Может быть есть возможность просто на какой-то другой промежуточный лист в этом же документе его перетащить...
Так как есть большая база отчетов и ключевая информация из них будет копироваться в другой файл, где все эти отчеты должны кратко отображаться.
Буду искренне благодарен, если кто-то сможет мне подсказать.
Да и в любом случае большое спасибо авторам, благодаря вашему сайту я могу найти ответы на большинство моих вопросов, но, к сожалению, на этот пока не нашел.
Хотел сделать что-то похоже на способ с фильтром, но, к сожалению, не выйдет, как я понимаю...
Или есть еще какой-то другой вариант выпадающего списка с поиском по первым буквам?
так как в списке порядка 2000 наименований
А вообще воспользовался вашим "Суперфильтром"
отлично работает!
Возможно, у меня "чайниковый вопрос" - а каким должен быть список, чтобы содержимое ячейки, выбранное из списка, находилось при поиске?
Допустим, я выбираю из списка значение "А", затем через Ctrl-F делаю поиск "А" - ничего не найдено...
Заранее спасибо!
для чего вообще нужен выпадающий список, чем он полезен?
При создании "Проверки вводимых значений" хочу сослаться на динамический диапазон с другого листа, какая правильная форма записи и возможно ли это в принципе ?
Перепробовал разные варианты, работает корректно только если задаю обычный, не динамический, диапазон вида ($столбе$строка:$столбе$строка)
а хотелось бы видеть [@[имя столбца]], как это происходит в "Умных таблицах"
А строку можно преобразовать в столбец с помощью .
У меня вопрос такого плана. Список по второму способу, если количество элементов списка больше 8 то соответственно в поле списка появляется полоса прокрутки. И вот неудобство - список всегда открывается своей нижней частью (полоса прокрутки внизу) и чтобы выбрать наиболее популярный верхний элемент приходится постоянно прокручивать список. Можно ли это исправить? Или просто поменять порядок списка с ног на голову? Спасибо!
Существует два листа "Материал" и "Расход материала".
На листе Материал содержится весь материал на котором идет печать.
А на листе Расход материала нужно чтобы можно было выбирать из раскрывающегося списка по наименованию, но заполнялись и остальные 2-ве ячейки данными с таблицы Материалы
Раскрывающийся список получается сделать, но вставляется только одно значение. Побывал 4-й способ,
Элемент ActiveX, но с ним неудобно. Можете помочь мне с этим?
И как можно сделать так, чтобы при открытии можно выбрать из списка пользователя который будет автоматом подставлять в поле с Фамилией и Именем. Чтото типа такого
чтобы было понятно, что хочу сделать.
Не зря говорят все гениальное просто!!!
И други, всех с Великим праздником Победы!
Есть вопрос по 4-му методу. Можно ли сделать так, чтобы параметр ListFillRange был переменной величиной, а не постоянной. Т.е. при выборе какого-то конкретного имени из другого выпадающего списка вставлялся именно этот диапазон (имя)? Очень нужно... помогите, пожалуйста.
Реализовал у себя в таблице способ №4 из этой статьи. Раньше пользовался выпадающим списком с мультивыбором (Вариант №1 из этой статьи: .
А теперь собственно вопрос:
Есть ли возможность в данном случае его применить?
Если да, то подскажите как пожалуйста как.
За ранее спасибо!
Сделал по третьему примеру, использовал =СМЕЩ([ИСТ.xlsm]номенклатор!$A$1;ПОИСКПОЗ('ЦЭКС (2)'!$F4;[ИСТ.xlsm]номенклатор!$A:$A;0);1;СЧЁТЕСЛИ([ИСТ.xlsm]номенклатор!$A:$A;'ЦЭКС (2)'!$F4);1)
работает за одним нюансом
добавляет в выпадающий список со смещением в 1 позицию.
т.е. если выбираю в ячейке F4 группу ГСМ, то он начинает со второй строки и захватывает одну строку со следующей группы (з/ч к авто)
У меня тоже есть вопрос (не осилила прочесть все комменты, возможно повторюсь). У меня есть таблица из 2х столбцов "Услуги" и "Цены". Как сделать так, чтобы в одной ячейке я выбирала услугу из выпадающего списка, а в соседней автоматически "вставала" соответствующая цена?
Спасибо!
Вставляю в несколько ячеек имена допустим Маша.
Затем открываю сам список и редактирую слово Маша на Маша_Попова.
По логике событий во всех ячейках где было выбрано слово Маша должно измениться на Маша_Попова. А этого не происходит ( и приходиться во всех ячейках повторно выбирать вместо Маша, новое слово Маша_Попова. Так вот как сделать что бы в ячейке автоматом менялось Маша на Маша_Попова.
Надеюсь Вы поняли мою мысль.
Столкнулся с такой проблемой, при открытии книги в разных версиях EXCEL, а в частности после 2013 в 2010 или 2007 книга с ранее заполненным текстом остается, а вот реализованная функция выпадающего списка исчезает. Т.е. использую описанный выше Способ 2.
В чем может быть проблема?
И как её решить? Не переделывать же заново книгу, каждый раз её открывая в разных версиях EXCEL.