Выпадающие списки в Microsoft Excel незаменимы при создании объемных таблиц, работе с базами данных. В чем же конкретно заключается удобство этого инструмента?
Если при заполнении таблицы некоторые данные периодически повторяются, нет необходимости каждый раз вбивать вручную постоянное значение — например, наименование товара, месяц, ФИО сотрудника. Достаточно один раз закрепить повторяющийся параметр в списке.
Ячейки списка защищены от введения посторонних значений, что снижает вероятность допустить ошибку в работе.
Таблица, оформленная таким образом, выглядит аккуратно.
В статье я расскажу, как в Экселе сделать выпадающий список в ячейк е и как с ним работать.

Формирование выпадающего списка

Один из распространенных примеров использования выпадающих списков – интернет-магазины, в которых вся продукция распределена по категориям – такая структура облегчает пользователям поиск по сайту.
Рассмотрим наглядно:

Все товары, перечисленные в таблице, нужно отнести к категории «Одежда». Чтобы создать выпадающий список для этого перечня, потребуется выполнить следующие действия:
Выделить любую ячейку, в которой будет создан список.
Зайти на вкладку «Данные», в раздел «Проверка данных».
В открывшемся окне выбрать вкладку «Параметры», а в перечне «Тип данных» вариант – «Список».
В появившейся строке необходимо указать все имеющиеся наименования списка. Сделать это можно двумя способами: выделить мышкой диапазон данных в таблице (в примере – ячейки А1-А7) или вбить названия вручную через точку с запятой.
Выделить все ячейки с нужными значениями, и, щелкнув правой кнопкой мыши, выбрать в контекстном меню пункт «Присвоить имя».
В строке «Имя» указать наименование списка – в данном случае, «Одежда».
Выделить ячейку, в которой создан список, и вписать созданное имя в строку «Источник» со знаком «=» вначале.
Итоговый результат выглядит так. Это самый простой вариант выпадающего списка. В зависимости от версии Excel, действий может быть больше или меньше, но в целом, инструкция универсальна для любой программы.

Как добавлять значения в список

Иногда возникает необходимость дополнить уже имеющийся перечень. В раскрывающемся списке все новые пункты отображаются автоматически при добавлении. Однако чтобы связать диапазон ячеек с добавленным вновь элементом, список требуется оформить в виде таблицы. Для этого нужно выделить диапазон значений, найти на вкладке «Главная» пункт «Форматировать как таблицу» и выбрать любой понравившийся стиль, например.

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

Итак, для создания выпадающего списка необходимо:

1. Создать список значений, которые будут предоставляться на выбор пользователю (в нашем примере это диапазон M1:M3 ), далее выбрать ячейку в которой будет выпадающий список (в нашем примере это ячейка К1 ), потом зайти во вкладку "Данные ", группа "Работа с данными ", кнопка "Проверка данных "



2. Выбираем "Тип данных " -"Список " и указываем диапазон списка

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

которое будет появляться при выборе ячейки с выпадающим списком

4. Так же необязательно можно создать и сообщение, которое будет появляться при попытке ввести неправильные данные


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

5. Если список значений находится на другом листе, то вышеописанным образом создать выпадающий список не получится (до версии Excel 2010). Для этого необходимо будет присвоить имя списку. Это можно сделать несколько способами. Первый : выделите список и кликните правой кнопкой мыши, в контекстном меню выберите "Присвоить имя "

Для Excel версий ниже 2007 те же действия выглядят так:

Второй : воспользуйтесь Диспетчером имён (Excel версий выше 2003 - вкладка "Формулы " - группа "Определённые имена "), который в любой версии Excel вызывается сочетанием клавиш Ctrl+F3 .
Какой бы способ Вы не выбрали в итоге Вы должны будете ввести имя (я назвал диапазон со списком list ) и адрес самого диапазона (в нашем примере это"2"!$A$1:$A$3 )

6. Теперь в ячейке с выпадающим списком укажите в поле "Источник" имя диапазона

7. Готово!

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

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

При всех своих плюсах выпадающий список, созданный вышеописанным образом, имеет один, но очень "жирный" минус: проверка данных работает только при непосредственном вводе значений с клавиатуры. Если Вы попытаетесь вставить в ячейку с проверкой данных значения из буфера обмена, т.е скопированные предварительно любым способом, то Вам это удастся. Более того, вставленное значение из буфера УДАЛИТ ПРОВЕРКУ ДАННЫХ И ВЫПАДАЮЩИЙ СПИСОК ИЗ ЯЧЕЙКИ, в которую вставили предварительно скопированное значение. Избежать этого штатными средствами Excel нельзя.

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

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

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

Вводим значения для выпадающего списка

Если у вас Excel 2003, тогда необходимо выполнить следующие действия. Станьте на ячейку, которую хотите сделать как выпадающий список, выбираете меню Данные – Проверка.

Выделяем будущую ячейку со списком

В Excel 2007 и выше данное окно вызывается через вкладку «Данные » -> «Проверка данных ».

Список в ячейке в MS Excell 2010

У вас появляется диалоговое окно с проверкой, куда необходимо ввести диапазон значений.

Указываем диапазон ячеек со значениями списка

Задаем конкретный тип вводимых значений, в нашем случае рассматриваем элемент «Список».

Для указания значений выпадающего списка, есть конкретное поле – «источник» . Здесь указывается диапазон ячеек, откуда будут браться значения для выпадающего списка. Делается это нажатием на значок в конце строки. Далее выделяете диапазон ячеек и нажимаете «Enter».

Вот какой результат получается в итоге.

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

Для того чтобы в поле «Источник» не задавать постоянно диапазон значений. Можно эти значения объединить в одну категорию, присвоить ей имя и писать это имя в данной графе.

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

  1. Шаг 1 – выбираем меню – «Вставка»;
  2. Шаг 2 – переходим в меню «Имя»;
  3. Шаг 3 — открываем диалоговое окно «Присвоить».

Создаем константу со значениями списка

Если у вас английская версия тогда так

  1. Insert;
  2. Name;
  3. Define.

В случае, если работаете с седьмой офисной версией или более новее Excel 2007. Тогда вам в помощь вкладка «Формулы» – «Диспетчер имен» (Name Manager) , и выбираем создать. Выбор имени ничем не ограничен. Можете написать, например «Обзор».

Создание имени диапазона значений в Excel 2010

Указываем имя созданного диапазона

Обратите внимание на такой момент. Источником данных может быть и любой именованный диапазон данных, к примеру, прайс-лист.

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

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

В этом способе рассмотрен вариант добавления нового объекта и привязка его к конкретному диапазону в екселе. Какие необходимо выполнить шаги:

Если у вас версия ексель 2007 года и позже, тогда выбираем меню Разработчик. Если версия ранняя тогда Вид — Панели инструментов – Формы.

Открываем панель элементов

В появившемся диалоговом окне элементов ActiveX, находим значок со списком «Поле со списком». Выносим элемент на страницу документа.

Формат элемента «Список»

В данном меню заносим диапазон значений и привязываем к ячейке данные.

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

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

Создаем простой выпадающий список

Для этого, в ячейки А1:А7 вписываем данные, которые будут отображаться в списке. Теперь выделим ячейку, в которой создадим выпадающий список – В2 .

Переходим на вкладку «Данные» и кликаем по кнопочке «Проверка данных» .

На вкладке «Параметры» в поле «Тип данных» выбираем «Список» . В поле «Источник» можно ввести значения различными способами:

1 – вводим значения для списка вручную, через точку с запятой;

2 – указываем диапазон ячеек, в которые введены данные для выпадающего списка;

3 – выделяем ячейки с именами, кликаем по ним правой кнопкой мыши и выбираем из меню «Присвоить имя» .

Выделяем ячейку В2 и в поле «Источник» ставим «=» , затем пишем созданное имя.

Таким образом, мы создали простой выпадающий список в Excel.

Если у Вас есть заголовок для столбца, и значениями нужно заполнять каждую строку, то выделите не одну ячейку, а диапазон ячеек – В2:В9 . Тогда можно будет выбирать из выпадающего списка нужное значение в каждой ячейке.

Добавляем значения в выпадающий список – динамический список

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

Выделяем диапазон ячеек – D1:D8 , затем на вкладке «Главная» нажимаем «Форматировать как таблицу» и выбираем любой стиль.

Подтверждаем расположение данных и ставим галочку в поле «Таблица с заголовками» .

Вверху пишем заголовок таблицы – «Сотрудники» , и заполняем ее данными.

Выделяем ячейку, в которой будет выпадающий список и кликаем по кнопочке «Проверка данных» . В следующем окне, в поле «Источник» , пишем следующее: =ДВССЫЛ(«Таблица1[Сотрудники]») . У меня одна таблица на листе, поэтому пишу «Таблица1» , если будет вторая – «Таблица2» , и так далее.

Теперь добавим новое имя сотрудника в наш список: Ира. В выпадающем списке оно появилось. Если мы удалим любое имя из таблицы, из списка оно тоже удалится.

Выпадающий список со значениями с другого листа

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

На Листе 2, выделяем одну ячейку или диапазон ячеек, затем кликаем по кнопочке «Проверка данных» .

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

Теперь можно дописывать имена на Листе 1, они будут добавляться в выпадающие списки на Листе 2.

Создаем зависимые выпадающие списки

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

Первый называем «Имя» , второй – «Фамилия» , третий – «Отч» .

Сделаем еще один диапазон, в котором будут прописаны присвоенные имена. Назовем его «Сотрудники» .

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

В поле «Тип данных» выберите «Список» , в поле источник – или введите «=Сотрудники» , или выделите диапазон ячеек, которому присвоено имя.

Первый выпадающий список создан. Теперь в ячейке F2 создадим второй список, который должен зависеть от первого. Если в первом выберем «Имя» , во втором отобразится список имен, если выберем «Фамилия» – список фамилий.

Выделяем ячейку и кликаем по кнопочке «Проверка данных» . В поле «Тип данных» выбираем «Список» , в поле источник прописываем следующее: =ДВССЫЛ($Е$1) . Здесь Е1 – это ячейка с первым выпадающим списком.

По такому принципу можно делать зависимые выпадающие списки.

Если в дальнейшем, нужно будет вписать значения в диапазон, которому задано имя, например, «Фамилия» . Перейдите на вкладку «Формулы» и кликните «Диспетчер имен» . Теперь в имени диапазона выбираем «Фамилия» , и внизу, вместо последней ячейки С3 , напишите С10 . Нажмите галочку. После этого диапазон увеличится, и в него можно будет дописывать данные, которые автоматически будут появляться в выпадающем списке.

Теперь Вы знаете, как сделать раскрывающийся список в Excel.

Оценить статью:

(1 оценок, среднее: 5,00 из 5)

Вебмастер. Высшее образование по специальности "Защита информации".. Автор большинства статей и уроков компьютерной грамотности

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

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

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

Изменять параметры такого списка можно в меню списки, которое открывается в основных параметрах Excel. Находим кнопку «Изменить списки» и нажимаем ее. В появившемся окне видны имеющиеся списки, также можно задавать новые. Для этого в окне «Элементы списка» нужно последовательно указать его части и «добавить» в списки. Также можно импортировать списки прямо с листа Excel, предварительно выделив необходимый диапазон.

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

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

Создание выпадающего списка с источником на этом же листе

Чтобы пользоваться выпадающим списком нужно сначала этот список создать. Создаем список на этом же (открытом) листе и проверяем правильность написания его составляющих. Затем выделяем ячейку или несколько ячеек, куда будем вставлять эти данные, открываем окошко «Проверка вводимых значений», которое открывается по прохождении следующего пути: данные/работа с данными/проверка данных.

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

Создание выпадающего списка с источником на другом листе

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

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

Выполняем это так: создаем список данных на одном листе, затем выделяем его и присваиваем ему имя, кликаем последовательно вкладка формулы/присвоить имя (в разделе определенные имена), в открывшемся окне задаем имя списка. Если, предварительно мы не выделили список, то задаем диапазон его ячеек.

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

Как скрыть лист с источником списка

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

Таким образом Excel представляет большие возможности по созданию списков, даже по сравнению с текстовым процессором .