Excel: выбор из списка. Выпадающий список в ячейке листа

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

Как же правильно создать выпадающий список в Excel 2007? Рассмотрим ниже.

Для начала, необходимо создать список тех значений, которые будут предоставляться на выбор для ввода в ячейке (в нашем случае, это диапазон ячеек F2:F8). Далее выделите ячейку (или группу ячеек) , в которой, собственно, и будет выпадать наш список (в нашем случае, это диапазон ячеек A2:A22).

После этого у Вас появится окошко «Проверка вводимых значений». В первой закладке «Параметры» выбираете «Тип данных» — «Список», а в графе «Источник» указываете диапазон списка.

По желанию, а также для большей наглядности, можете заполнить оставшиеся две вкладки «Сообщение для ввода» и «Сообщение об ошибке».

В закладке «Сообщение для ввода» Вы можете указать подсказку для пользователя о его дальнейших действиях, например, фразу «Выберите данные из списка». Подсказка будет появляться при выборе ячейки с выпадающим списком.

В закладке «Сообщение об ошибке», Вы можете ввести текст, который будет появляться при попытке ввести неверные данные в ячейку.

После того как все сделано, жмете ОК.

Выпадающий список готов. Теперь, при выборе любой из ячеек диапазона A2:A22, будет появляться подсказка и выпадающий список (стрелочка справа от ячейки). Вот так

В том случае, если Вы попытаетесь ввести в одну из ячеек из выбранного диапазона неверные данные, появится сообщение об ошибке

Для удобства список можно разместить на другом листе документа Excel 2007, но для создания выпадающего списка теперь придется задать ему имя. Делается это просто. Выделяете список данных, кликаете по нему правой кнопкой мыши и выбираете «Имя диапазона…». В открывшемся окне «Создание имени» в графе «Имя» задаете имя списку (без пробелов) и проверяете правильность диапазона (диапазон можете сразу выделить и скопировать, он нам понадобится позже) и жмете ОК.

А теперь возвращаемся на страницу с диапазоном, в котором Вы хотели бы видеть выпадающий список и при создании выпадающего списка в графе источник указываете только что скопированный диапазон списка данных.

Здравствуйте, друзья. Когда Вы заполняете таблицы данными, перечень которых определен заранее, было бы удобно не писать их вручную, а выбирать мышью из выпадающего списка. Такой подход экономит время и минимизирует вероятность ошибки.

Приведу пример. Вы занимаетесь учетом расходов офиса, связанных с его хозяйственной деятельностью. Ваша таблица выглядит так:

Сейчас Вы вносите категории и виды расходов вручную. А значит, можете случайно ошибиться в написании. Вроде бы, ничего страшного, но если потом придется оценивать расходы с помощью сводной таблицы, ошибки исказят результат, придется исправлять.

Подробнее о сводных таблицах читайте .

Создание раскрывающегося списка

Давайте построим перечни категорий и видов расходов, чтобы на их основании сделать выпадающие списки. Вынесем их в отдельную таблицу.

Теперь воспользуемся инструментом Проверка данных , чтобы сделать выбор из списка. Как это работает, я описывал . Действуем по алгоритму:

Теперь Вы сможете выбрать нужные пункты из раскрывающегося списка и не ошибетесь с написанием.

Правда, остались нюансы. Мы не контролируем соответствие выбранной категории и вида расходов. Например, можно выбрать категорию «Канцелярия » и вид – «Кофе ». Этого нельзя допускать. Усовершенствуем проверку данных, сделаем списки, зависимые от значения другой ячейки.

Создание зависимого списка в Экселе

Задача: когда мы выбираем категорию – в списке видов расходов должны быть лишь те, которые относятся к этой категории. Решение может показаться сложным, но это не так. Я постараюсь пояснить его максимально просто.

Cтруктурируем нашу таблицу видов расходов:

Теперь виды расходов разбиты по колонкам, соответствующим каждой из категорий. Далее мы будем использовать функцию ДВССЫЛ(текст) . Что она делает? Она пытается преобразовать введенный текст в ссылку на ячейки. Что будет, если записать такую формулу: =СУММ(ДВССЫЛ(«F1:F5»)) . Функция ДВССЫЛ распознает текст «F1:F5» , как диапазон ячеек и вернет его. А функция СУММ – просуммирует все значения в этом диапазоне.

То же самое произойдет, если мы диапазону «F1:F5» присвоим имя. Например, «структура». Формула =СУММ(ДВССЫЛ(«структура»)) даст аналогичный результат. Именно этой возможностью мы и воспользуемся.

Чтобы больше узнать об именовании ячеек, прочтите . Рекомендую это сделать, имена – удобный и практичный инструмент.

Дадим имена всем столбцам с исходными данными. При этом, диапазон с видами должен именоваться точно так же, как и его категория. Например, диапазону J4:J8 дадим имя «Канцелярия ». Именуем:

Теперь, если кликнуть Формулы – Определенные имена – Диспетчер имен – можно увидеть все заданные имена. Если Вы где-то ошиблись, или список изменился, в этом окне можно внести исправления.

Еще раз настраиваем проверку данных:

Если все еще не понятен принцип, опишу по пунктам, как теперь будет работать наша таблица:

• С помощью обычного списка, в столбце B выбираем категорию товаров. Например, «Питание »
• Слово «Питание » попадает, как источник данных в столбец C , т.е. в виды расходов
• У нас есть диапазон данных L4:L8 , который называется Питание . Функция ДВССЫЛ это определяет и заменяет на слово «Питание » на диапазон L4:L8
• Теперь этот диапазон будет источником для списка вида расходов

Все перечисленное подтверждаю изображением ниже:

Как видите, список видов расходов заполнился не всем перечнем, а только теми пунктами, которые принадлежат выбранной категории «Питание». Именно этого мы и добивались.

Думаю, Вы разобрались, как сделать зависимый список в Excel. Если не разобрались – пишите комментарии. А я статью закончил, продуктивной Вам работы!

Связанные выпадающие списки

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

{"Bottom bar":{"textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24,"textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance":30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500,"textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100%; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive":"","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40}}

Чтобы упростить работу пользователей с листом, добавьте в ячейки раскрывающиеся списки. Раскрывающиеся списки позволяют пользователям выбирать элементы из созданного вами списка.

Видео

таблице Excel . Если это не так, список можно быстро преобразовать в таблицу, выделив любую ячейку диапазона и нажав клавиши CTRL+T .



Примечания:

• Скачивание примеров

  Предлагаем скачать образец книги с несколькими примерами проверки данных, аналогичными примеру в этой статье. Вы можете воспользоваться ими или создать собственные сценарии проверки данных. Скачать примеры проверки данных Excel .

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

  

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

  
  

  См. также

  На новом листе введите данные, которые должны отображаться в раскрывающемся списке. Желательно, чтобы элементы списка содержались в таблице Excel .

  Примечания:

  • Почему данные следует поместить в таблицу? Потому что в этом случае при добавлении и удалении элементов все раскрывающиеся списки, созданные на основе этой таблицы, будут обновляться автоматически. Дополнительные действия не требуются.

При заполнении таблички в редакторе Excel очень часто данные повторяются. К примеру, вы пишите наименование товара или ФИО работника. Сегодня мы поговорим о том, как в Эксель сделать выпадающий список, чтобы постоянно не вводить одно и то же, а просто выбирать нужное значение.

Есть самый простой вариант справиться с поставленной задачей. Вы делаете правый клик мышкой по ячейке под столбиком с данными для вызова контекстного меню. Тут ищем пункт «Выбрать из раскрывающегося списка». Это же действие легко выполнить комбинацией Alt + стрелочка вниз .

Вот только данный метод не подойдет, если необходимо сделать такой объект в Эксель в другой ячейке и в 2-3 и т.д до и после. При такой необходимости используйте следующий вариант.

Традиционный способ

Выделяете область самих ячеек, из которых создаете выпадающий перечень, далее переходите:

Вставка/Имя/Присвоить (Excel 2003)

В свежих версиях (2007, 2010, 2013, 2016) переходите:

Формулы/Определенные имена/Диспетчер имен/Создать

Вписываете любое наименование, тапаете ОК .

Затем выделяете ячейки (или несколько), куда планируете вставить перечень заданных полей. В меню переходите:

Данные/Тип данных/Список

В разделе «Источник» вносите ранее написанное имя, просто отмечаете диапазон. Полученную ячеечку можете копировать в любое место, в ней уже будет меню заданных табличных элементов.

Кроме того, вы можете ее растягивать, чтобы создать диапазон. Кстати, в случае изменения информации в нем, списочная информация тоже будет изменяться, она является динамической.

Как в Экселе сделать выпадающий список: пользуемся управленческими методами

При использовании этого варианта, вы вставляете элемент управления, представляющий собой непосредственно диапазон данных. Для этого:

• во-первых, ищите раздел «Разработчик» (Эксель 2007/2010), в других выпусках активируете самостоятельно через параметры «Настроить ленту»;

• во-вторых, переходите на раздел, кликаете «Вставить»;
• в-третьих, выбираете «Поле…» и жмете по значку;
• рисуете прямоугольник;

• делаете правый клик по нему и жмете Формат объекта;

• ищите «Формировать…», выделяете нужные границы;
• отмечаете ту область, куда хотите установить порядковый номер элемента в перечне;
• жмете ОК.

Как создать в Экселе выпадающее меню: пользуемся элементами ActiveX

Действия аналогичны вышеописанным, но ищем «Поле с ActiveX».

Здесь основные отличия в том, что спецэлемент ActiveX может быть в 2 вариантах - режим отладки, который позволяет менять параметры, и - режим ввода, получится сделать только выборку сведений из него. Поменять режим можно клавишей Режим Конструктора в разделе Разработчик . С помощью этого способа вы можете настроить цвет, шрифт и выполнить оперативный поиск.

Дополнительные возможности

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

В первую очередь, следует сделать табличку, где будут сами списочки, а потом отдельно окошки с названиями продуктов.

Для второй активируем окошко проверки сведений, но в пункте «Источник» пишем «=ДВССЫЛ» и адрес 1-й ячейки. Все получилось.

После этого, чтобы окошки снизу дополнились такими же свойствами, выделяем верхний участок и с нажатой мышкой перетаскиваем все вниз. Готово.

Как в Эксель сделать выпадающий список? Как видите, это легко выполнить любым указанным методом. Просто выбирайте для себя самый оптимальный. А выбор будет зависеть от целей создания, предназначения, области использования, количества информации и прочего.