Выпадающий список в ячейке листа

Видео

 У кого мало времени и нужно быстро ухватить суть - смотрим обучающее видео:

Кому интересны подробности и нюансы всех описанных способов - дальше по тексту.

Способ 1. Примитивный

Один щелчок правой кнопкой мыши по пустой ячейке под столбцом с данными, команда контекстного меню Выбрать из раскрывающегося списка (Choose from drop-down list) или нажать сочетание клавиш ALT+стрелка вниз. Способ не работает, если ячейку и столбец с данными отделяет хотя бы одна пустая строка или вам нужен товар, который еще ни разу не вводился выше:

dropdown1.gif

Способ 2. Стандартный

  1. Выделите ячейки с данными, которые должны попасть в выпадающий список (например, наименованиями товаров).
  2. Если у вас Excel 2003 или старше - выберите в меню Вставка - Имя - Присвоить (Insert - Name - Define), если Excel 2007 или новее - откройте вкладку Формулы (Formulas) и воспользуйтесь кнопкой Диспетчер имен (Name Manager), затем Создать. Введите имя (можно любое, но обязательно без пробелов и начать с буквы!) для выделенного диапазона (например Товары). Нажмите ОК.
  3. Выделите ячейки (можно сразу несколько), в которых хотите получить выпадающий список и выберите в меню (на вкладке) Данные - Проверка (Data - Validation). Из выпадающего списка Тип данных (Allow) выберите вариант Список (List) и введите в строчку Источник (Source) знак равенства и имя диапазона (т.е. =Товары).

dropdown2.gif

Нажмите ОК.

Все! Наслаждайтесь!

dropdown3.gif

Важный нюанс. В качестве источника данных для списка может выступать и динамический именованный диапазон, например прайс-лист. Тогда при дописывании новых товаров к прайсу, они будут автоматически добавляться к выпадающему списку. Еще одним часто используемым трюком для таких списков является создание связанных выпадающих списков (когда содержимое одного списка меняется в зависимости от выбора в другом).

Способ 3. Элемент управления

Этот способ представляет собой вставку на лист нового объекта - элемента управления "поле со списком" с последующей привязкой его к диапазонам на листе. Для этого:

  1. В Excel 2007/2010 откройте вкладку Разработчик (Developer). В более ранних версиях - панель инструментов Формы (Forms) через меню Вид - Панели инструментов - Формы (View - Toolbars - Forms). Если этой вкладки не видно, то нажмите кнопку Офис - Параметры Excel - флажок Отображать вкладку Разработчик на ленте (Office Button - Excel Options - Show Developer Tab in the Ribbon)
  2. Найдите значок выпадающего списка среди элементов управления форм (не ActiveX!). Ориентируйтесь по всплывающим подсказкам - Поле со списком:
    dropdown4.gif
    Щелкните по значку и нарисуйте небольшой горизонтальный прямоугольник - будущий список.
  3. Щелкните по нарисованному списку правой кнопкой мыши и выберите команду Формат объекта (Format control). В появившемся диалоговом окне задайте
    • Формировать список по диапазону - выделите ячейки с наименованиями товаров, которые должны попасть в список
    • Связь с ячейкой - укажите ячейку куда нужно выводить порядковый номер выбранного пользователем элемента.
    • Количество строк списка - сколько строк показывать в выпадающем списке. По умолчанию - 8, но можно больше, чего не позволяет предыдущий способ.

После нажатия на ОК списком можно пользоваться.

Чтобы вместо порядкового номера элемента выводилось его название можно дополнительно использовать функцию ИНДЕКС (INDEX), которая умеет выводить содержимое нужной по счету ячейки из диапазона:

dropdown6.gif

Способ 4. Элемент ActiveX

Этот способ частично напоминает предыдущий. Основное отличие в том, что на лист добавляется не элемент управления, а элемент ActiveX "Поле со списком" из раскрывающегося набора под кнопкой Вставить (Insert) с вкладки Разработчик (Developer):

dropdown7.gif

Механизм добавления тот же - выбираем объект из списка и рисуем его на листе. А вот дальше начинаются серьезные отличия от предыдущего способа.

Во-первых, созданный выпадающий ActiveX список может находится в двух принципиально разных состояниях - режиме отладки, когда можно настраивать его параметры и свойства, двигать его по листу и менять размеры и - режиме ввода, когда единственное, что можно - выбирать из него данные. Переключение между этими режимами происходит с помощью кнопки Режим Конструктора (Design Mode) на вкладке Разработчик (Developer):

dropdown8.gif

Если эта кнопка нажата, то мы можем настраивать параметры выпадающего списка, нажав соседнюю кнопку Свойства (Properties), которая откроет окно со списком всех возможных настроек для выделенного объекта:

dropdown9.gif

Самые нужные и полезные свойства, которые можно и нужно настроить:

  • ListFillRange - диапазон ячеек, откуда берутся данные для списка. Выделить мышью диапазон он не даст, надо просто вписать его руками с клавиатуры (например, Лист2!A1:A5)
  • LinkedCell - связанная ячейка, куда будет выводиться выбранный из списка элемент
  • ListRows - количество отображаемых строк
  • Font - шрифт, размер, начертание (курсив, подчеркивание и т.д. кроме цвета)
  • ForeColor и BackColor - цвет текста и фона, соответственно

Большим и жирным плюсом этого способа является возможность быстрого перехода к нужному элементу в списке при вводе первых букв с клавиатуры(!), чего нет у всех остальных способов. Приятным моментом, также, является возможность настройки визуального представления (цветов, шрифтов и т.д.)

При использовании этого способа, также возможно указывать в качестве ListFillRange не только одномерные диапазоны. Можно, например задать диапазон из двух столбцов и нескольких строк, указав дополнительно, что выводить нужно два столбца (свойство ColumnCount=2). Тогда можно получить весьма привлекательные результаты, окупающие все потраченные на дополнительные настройки усилия:

dropdown10.gif

 

Итоговая сравнительная таблица всех способов

  Способ 1. Примитивный Способ 2. Стандартный Способ 3. Элемент управления Способ 4. Элемент ActiveX
Сложность низкая средняя высокая высокая
Возможность настройки шрифта, цвета и т.д. нет нет нет да
Количество отображаемых строк всегда 8 всегда 8 любое любое
Быстрый поиск элемента по первым буквам нет нет нет да
Необходимость использования дополнительной функции ИНДЕКС нет нет да нет
Возможность создания связанных выпадающих списков нет да нет нет

Ссылки по теме:


Страницы: 1  2  3  4  
Аркадий
03.10.2012 19:19:06
Все до боли просто оказалось.
Спасибо..
Александр
03.10.2012 19:20:44
Тому, кто выложил эту тему - респект!
Искал, спрашивал - все тупят..
Андрей
03.10.2012 19:44:26
Помогите пожалуйста. Существует на одном листе 2 списка: 1.Товары; 2.Цены на эти товары. На другом листе создана ячейка с выпадающим списком товаров и ячейка, в которой должна отображаться цена выбранного из выпадающего списка товара. Какую формулу мне нужно создать, чтобы в ячейке "цена" отображалась нужная цена для выбранного товара??? Заранее спасибо за ответы...
04.09.2014 11:11:34
Быть может уже не актуально, но все же...
Можно воспользоваться формулой "ВПР" не знаю насчет ранних версий excel начиная с 2007 года должна быть.
Если в столбце "А" - наименования товаров, "B" - цена, "C" - ячейки с выпадающим списком, то в столбце "D" можно вставить формулу =ВПР(C3;$A$3:$B$11;2;ЛОЖЬ) при условии что товары и цена расположены в диапазоне A3:B11
Если вам зрительно мешают ошибки #Н/Д (из-за наличия пустых ячеек в столбце "С") можно дополнить формулу логикой: =ЕСЛИ(E3="";"";ВПР(C3;$A$3:$B$11;2;ЛОЖЬ)), далее скопировать и вставить в ячейки ниже.
Более подробно о функции ВПР можно прочесть в справке по этой функции
09.12.2014 22:47:19
Георгий, не подскажете, что делать если данные, которые нужно найти находятся не в первом столбце, то есть, есть табличка:
А-Преподаватель
В-Группа
С-Студент
D3-выпадающий список Студентов (то есть, выборка из столбца С)
Можно как-нибудь сделать так, чтобы в ячейке в зависимости от выбранного Студента вписывались данные о том, кто его обучает и в какой он группе? То есть, в формате [Преподаватель],[Группа]?
Не могу вывести даже просто Преподавателя.
=ВПР(D3;$A$3:$C$11;1;ЛОЖЬ) выдаёт сущую ерунду. Думаю, тут проблема в том, что он у меня фамилию Студента ищет среди фамилий Преподавателя, то есть, только по первому столбцу. Это так?
10.12.2014 07:10:34
Да, так. ВПР ищет строго по первому столбцу, если вы хотите чтобы по студенту подтягивались данные, перенесите столбец со студентами перед всеми т.е. А-Студент В-преподаватель, С-группа (выделяете весь столбец, ctrl+C (копировать) или ctrl+X (русская Ч) (вырезать), выделяете столбец А, ПКМ и выбираете "вставить скопированные ячейки" в таком случае преподаватели и группы как бы сдвинутся вправо.
и еще, формулу нужно будет поправить =ВПР(D3;$A$3:$C$11;2;ЛОЖЬ) это будут преподаватели, если Вам нужны в одной ячейке перподаватель и через запятую с пробелом группа, тогда =ВПР(D3;$A$3:$C$11;2;ЛОЖЬ)&", "&ВПР(D3;$A$3:$C$11;3;ЛОЖЬ)
11.12.2014 10:45:03
Есть еще статья о том, как сделать левый ВПР с помощью связки функций ИНДЕКС и ПОИСКПОЗ.
01.02.2016 14:24:02
Добрый день. Данный ответ актуален, если работаешь с 1й строчкой ,т.к. при протяжки формулы вниз, меняются все значения: =ВПР(C4;$A$4:$B$12;2;ЛОЖЬ) и т.д., а что делать если строк >1000? П.Э., мне кажется, что надо создать таблицу на соседнем листе и зациклить столбцы ,а не описать строчки: =ВПР(C4;A:B;2;ЛОЖЬ).
Анжей
03.10.2012 19:46:05
Просто и эффективно! Спасибо автору!
ОлегБ
03.10.2012 19:47:25
дополнение к способу1(Примитивному)
выпадающий список появляется не только в первой пустой нижней ячейке, но и:
1)в первой пустой верхней ячейке
2)в средней пустой ячейке! (если два списка прерываются пустой ячейкой, то в ней можно видеть выпадающий список из двух смежных списков!
Также выпадающий список появляется если нажать ПКМ на любом элементе списка и, к сожалению, выпадающий список не работает с числами.
Diana
03.10.2012 19:48:16
Большое спасибо! Качесвенно!
Обязательно дам ссылку своим студентам.
Успехов!
Яна
03.10.2012 19:49:27
Интересно! Как сделать, чтобы при печати значок выпадающего списка "стрелочка" :) не печаталась, а только содержимое?
magrifa
03.10.2012 19:50:26
В свойствах Объекта найдите PrintObject и поставьте False.
При печати объект не будет выводиться на лист, а также его содержимое.
Но для этого у вас есть LinkedCell.К этой ячейке все и привязывайте.
debaucep
03.10.2012 19:51:22
Спасибо! Очень помогло!!
25.12.2012 01:38:45
Прежде всего хочу сказать, что у Вас замечательный во всех отношениях сайт. Живой и толковый форум. И он очень помогает людям.

И есть ВОПРОС. Вы начали разговор в "4 способе..." о создании списка из ДВУХ столбов. Всё ли сказано? Я всё сделал как говорилось, задал ListFillRange и ColumnCount=2, сто раз перепроверил, а второй столбец не появляется... А надо.
Может, есть ещё какой-то нюанс?
Наталья0014
25.12.2012 08:24:50
Информация изложена кратко и доступно! Спасибо автору!
Джамик
25.12.2012 16:12:10
Отлично, спс
26.12.2012 18:37:43
Ответ на мой вопрос о двухстолбовом списке 4-м способом дал SVA:
надо на лист со списком добавить код на КомбоБокс
http://www.planetaexcel.ru/forum/index.php?PAGE_NAME=read&FID=1&TID=157
.
19.01.2013 19:09:26
Сергей, здравствуйте

тоже столкнулась с подобной проблемой, ответ SVA нашла и прочитала. но, к сожалению, до конца не поняла, куда следует вписать и как))) этот код. очень прошу, если возможно, прислать картинку.

с уважением,
28.01.2013 16:06:22
  1. Для отображения двухстолбцового списка достаточно верно прописать ссылки в ListFillRange
  2. Для вставки значений в два-же столбца, надо двойным щелчком по кнопке (при активном режиме конструктора) и внести в появившемся окне вба-редактора следующее: (Ячейки E2, А2 - заменить на нужные или добавить еще при необходимости)
Private Sub ComboBox1_Change()
[E2] = Me.ComboBox1.List(Me.ComboBox1.ListIndex, 0)
[F2] = Me.ComboBox1.List(Me.ComboBox1.ListIndex, 1)
End Sub 
06.01.2013 20:22:39
Здравствуйте, подскажите, как можно создать выпадающий список, перечень элементов которого формируется на основании некоего условия?
06.01.2013 20:43:53
Это не совсем то, что нужно. Нужно при формировании списка отбирать в него значения с проверкой на условие. Могу послать пример. Как лучше это сделать?
07.01.2013 11:35:04
Fona
09.01.2013 11:20:41
Спасибище!!!!Очень помогло!
Добрый вечер! автору - спасибо. Обидно - не возможно сохранять примеры...:cry:
15.01.2013 23:40:47
Четвертый способ очень интересный, но мне не совсем понятно. Получается что значение выбранное в списке, будет всегда вставляться в одну связанную ячейку (LinkedCell), заменяя то что было выбрано ранее. А как сделать что бы следующее выбранное значение не заменяло, а добавлялось в следующую ячейку? Или это не возможно?
26.01.2013 13:24:12
Такое только макросом
22.01.2013 11:20:56
Добрый день!
Мне надо, что бы выпадающий список показывал текст, а в ячейку вставлялся код к этому тексту. Как это можно реализовать?
26.01.2013 13:22:51
Если принципиально вставлять в эту же ячейку, то только макросом - отслеживать момент изменения и подставлять код. Если код можно в соседнюю, то легко реализовать подстановку функцией ВПР.
29.01.2013 22:56:51
Доброго времени суток!!! огромное спасибо за отличные, нужные и понятные примеры!!!
Скромный вопрос - работаем с элементами ActiveX на рабочем листе, код:
Private Sub ComboBox1_Change()
   ComboBox2.ListFillRange = ComboBox1.Value
End Sub

работать не будет и лучше не стараться???? или есть способ??
30.01.2013 15:21:31
Почему не будет? У меня работает - только что попробовал.
30.01.2013 20:05:23
Ой извиняюсь, действительно все в норме. Не работает в том случае если область именованного диапазона в ComboBox1.Value не книга, а сам диапазон находится за пределами активного листа:)
21.02.2013 14:11:16
Добрый день! У меня возник такой вопрос: при использовании 4-го способа возможно ли привести документ к первоначальному виду (состоянию до применения данного способа). Благодарю.
21.02.2013 22:36:27
В смысле? Удалить созданные выпадающие списки на ActiveX? Выделить их - и Delete.
22.02.2013 12:48:45
Простите, может я не совсем ясно выразилась, я имела ввиду возможно ли избавиться от значков, появившихся справа ячеек, с помощью которых возможно было пользоваться выпадающими списками. Спасибо.
23.02.2013 12:55:10
Удалить или скрыть треугольнички черные? В 4-м способе нельзя.
В первом можно - в окне Проверки данных снять флажок Список допустимых значений.
18.03.2013 12:03:34
Можно ли установить пароль на выпадающий список?
29.03.2013 01:03:51
В Excel пароль (защиту) можно установить на ячейки листа (все или выборочно). Что вы имеете ввиду под словами "выпадающий список"? На ячейку со списком? Или на источник данных для списка?
25.03.2013 10:35:45
Задача такая: на листе в 1 столбце нужно выбирать из выпадающего списка (перечень допустимых значений зада динамическим диапазоном). При этом в ячейку можно вводить как одно из значений, так и несколько. Насколько смог разораться, множественный выбор допустим только в 4 способе. Но элемент привязан к одной ячейке, а мне нужно, чтобы в ЛЮБОЙ ячейке заданного столбца выбор был возможен.
Спасибо

О качестве сайта и надстройки сказано много, повторяться не буду - ЭТО КЛАСС!
11.04.2013 07:41:14
Стандартными средствами - точно не реализовать. Можно попробовать макросом, чтобы при выборе из списка, он добавлял значения к уже ранее выбранным. В общем, только VBA :(
09.05.2013 15:51:40
Вот, нашел вам что-то похожее - посмотрите, может что подойдет http://www.contextures.com/excel-data-validation-multiple.html
06.04.2013 20:28:52
Здравствуйте. Ознакомился с обучением и прозрел, спасибо.
Появилась необходимость использовать выпадающий список в такой форме: в выпадающем списке содержатся данные словами типа (D8AIII), но для расчёта мне требуются числа. Т.е. выбираю из списка диаметр арматуры, а в формуле, для расчёта, используется её масса типа (0,395). В формулу я подставляю значение ячейки для расчёта массы.
11.04.2013 07:37:26
Иван, функция ВПРвам поможет.
07.04.2013 18:24:31
Здравствуйте, Николай!

Очень много полезного приобрел и применил на практике из Вашего сайта. За что отдельное спасибо!


Возможно, я просто не нашёл ответ на свой вопрос на Вашем сайте или задам его первым.
У меня есть файл с большим количеством листов. С помощью гиперссылок я один из листов сделал главным. С него можно перейти на любой лист, а с листа вернуться на главный. Но по мере свободного времени на работе я пытаюсь упростить файл для использования (им пользуюсь не только я).

Сейчас я эту главную страницу обрабатываю как интернетовский сайт. Корпоративный стиль и т.д.

Одна из моих задумок состоит в следующем.

Можно ли сделать так, что когда из выпадающего списка я буду выбирать необходимую категорию, то будет появляться список гиперссылок на необходимые листы. Не просто данные с названиями этих листов, а именно сами гиперссылки.

Надеюсь, я понятно выразил суть вопроса. ))

С уважением,
Олег
11.04.2013 07:39:24
Олег, вам нужна будет для этого функция ГИПЕРССЫЛКА - она умеет формировать гиперссылку по текстовому названию листа.
ssg
14.04.2013 15:55:09
Добрый день!
Ребята, помогите пожалуйста.
Такой вопрос.
Необходимо использовать выпадающие списки во многих (во всех) ячейках таблицы.
По функциям подходит список на базе Элемента ActiveX.
Собственно, по нему и вопросы:
1. его что, надо в каждой ячейке, где необходимо исп. выпадающие списки, вставлять и настраивать отдельно?
2. Сам элемент постоянно виден и заграмождает таблицу (в отличии от обычного вып. списка на базе имени диапазона и проверки данных). Тут что то можно сделать - чтобы он появлялся тольк при необходимости ввода значения ?

Спасибо.
18.04.2013 09:42:33
1. Да. Причем к ячейкам он не привязан, кстати - это отдельный автономный графический объект (как картинка). Можно вставить один и скопировать, если у них настройки одинаковые.
2. Нет.

Советую вариант 2 - наиболее универсальный для больших таблиц.
Страницы: 1  2  3  4