Выпадающий список в ячейке листа
Видео
У кого мало времени и нужно быстро ухватить суть - смотрим обучающее видео:
Кому интересны подробности и нюансы всех описанных способов - дальше по тексту.
Способ 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
- Выбор фото из выпадающего списка
- Автоматическое удаление уже использованных элементов из выпадающего списка
- Выпадающий список с автоматическим добавлением новых элементов
1. Выпадающий список с отображение на экране сразу нескольких элементов со списка (как после нажатия треугольника (который бы менял данные при вводе в его поле названия).
2. И в конце можно было выбрать элемент с помощью Таб или enter (а лучше и тем и другим).
На счет пункта 2 вообще есть возможность прописывать в теле макроса клавишу для завершения определенного действия?
Подскажите, пожалуйста, есть ли возможность в эксель создать выпадающий список так, чтобы после выбора из него значения этому значению присваивалась гиперссылка. Например, есть выпадающий список из населенных пунктов, можно ли сделать так, чтобы при выборе населенного пункта к нему привязывалась гиперссылка, ведущая к расположению этого н.п. на карте Гугль?
Заранее благодарю за консультацию.
Есть задание сделать подбор и поиск из разных таблиц .Приблизительно как сделан подбор на этом сайте
на кнопке " Подобрать услугу"
Возможно ли такое сделать в exele.
Выпадающие списки не подходят около 400 строк неудобно подбирать .
Посоветуйте в каком направлении копать????
С элементом "поле со списком" проблем не возникает. А вот элемент "список" из ActiveX, который выполняет вроде те же функции, но для меня более удобен, почему то при каждом пересчете книги меняется в размерах самопроизвольно (уменьшается его ширина и высота). Каждый раз приходиться включать режим конструктора и подправлять его вручную. Все настройки перепробовала. Может это связано с тем, что диапазон исходных данных находиться на другом листе и задан формулой массива? Или в чем причина? Подскажите, кто знает.
По 4-му способу, нужно чтобы в выпадающем списке отображалась информация по нескольким столбцам.
Как корректно прописать свойство ColumnCount=2? И может ли быть не 2 колонки, а 6 к примеру?
Спасибо.
Спасибо большое за сайт, очень просто и доходчиво объясняется и в видео материалах, книге и комментариях. Большое значение имеет дикция Тренера. Николай, Ваши видеоуроки приятно слушать и материал легко усваивается. Спасибо большое!
А теперь вопрос связанным выпадающим спискам (более 2 уровней):
как сделать так, чтобы в связанных выпадающих списках на любом уровне позиции списка не повторялись ( использую метод с формулой смещ()?
То есть для списка первого уровня все легко и понятно, мы просто создали список уникальных позиций и сослались на него через проверку данных.
А для следующих уровней мы же не используем простую ссылку как для первого уровня, вернее используем ссылку на диапазон, создаваемый через формулу смещ(), отталкиваясь от выбранного значения в первом уровне.
например: 1 уровень : доходы, расходы. 2 уровень расходов: производственные и административные, 3 уровень административных расходов : 10 позиций и так далее. Соответственно, во втором списке позиция "админ расходы" отразится 10 раз.
Чем глубже требуемый анализ, тем больше дублирующихся значений в списке предшествующих уровнях.
Помогите пож-ста советом.
Спасибо!
И может кто-то еще подскажет, столкнулся с тем, что выпадающий список Activ x просматривает диапазон только вниз, то есть если я хочу сделать выпадающий список который расположен горизонтально по столбцам, то список видит только первую ячейку.
Подскажите пожалуйста, при использовании 3 и 4 способа можно ли диапазон значений сделать динамическим?
В данный момент у меня на отдельном листе записаны данные и их количество меняется в разных документах, чтобы не переписывать постоянно свойства у меня в качестве источника данных задан весь столбец (A:A), но при этом сами понимаете сколько ячеек в выпадающем списке. Можно ли сделать так, чтобы показывались только непустые ячейки?
И еще одна заморочка. В первом столбце листа с исходными у меня только номер, остальные значения в документ подставляются функцией ВПР, но при этом в случае использования способа 3 все работет нормально, т.к. номер совпадает с номером строки. А при использовании 4 способа ВПР отказывался работать пока я не задал текстовый формат столбцу с номерами и ячейке в которую подставляется значение
Весь лень пытался найти на форуме способ решения своей задачи, не удалось.
Очень много похожих решений но все же есть отличия.
Мне необходимо чтобы при выборе из выпадающего списка наименования товара в четырех соседних ячейках автоматические выдавались параметры этого товара, которые расположены в том же списке. Т.е. вся эта информация храниеся в одной таблице, мне нужно что бы на другом листе при выборе товара выдавалась инфа об этом товаре в одну строчку (и 5 столбцов).
Подскажите как удалить выпадающий список с помощью макроса?
Список сделан по 2му методу (стандартный), данные берутся из отдельного листа.
Дело в том что у меня в столбце я выбераю варианты с выпадаещего списка. но проблема В том что ячейки которые я создал от куда будут брать данные для списка им дал цвет формат и так далее.
Но когда выбераю на столбце там только текст попадает.
Так как у меня список длиный и каждый раз я заполняю его с нова не хотелось постоянно менять формат звета и так далее.
Есть какоето решение на мою проблему?
P.S. У меня от вашей орфографии и грамматики мозг взорвался - сижу и думаю, как дальше жить после такого с утра.
Есть вопрос, делаю с ActiveX, в настройках свойств выпадающего списка прописываю в ListFillRange не одномерный диапазон (A1:C16), в ColumnCount ставлю "3", в списке все отображается нормально, но в LinkedCell (G2) выводится только левое значение из массива.
Пробовал в LinkedCell ставить несколько ячеек (G2:I2), результат тот же.
В чем здесь дело, не пойму, где я что пропустил)...
Excel 2016.