Выпадающие списки в Microsoft Excel незаменимы при создании объемных таблиц, работе с базами данных. В чем же конкретно заключается удобство этого инструмента?

Зачем нужны такие списки

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

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

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

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

Все товары, перечисленные в таблице, нужно отнести к категории «Одежда». Чтобы создать выпадающий список для этого перечня, потребуется выполнить следующие действия:

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

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

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

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

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

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

Путь: меню «Данные» - инструмент «Проверка данных» - вкладка «Параметры». Тип данных – «Список».

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

Любой из вариантов даст такой результат.



Выпадающий список в Excel с подстановкой данных

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

Протестируем. Вот наша таблица со списком на одном листе:

Добавим в таблицу новое значение «елка».

Теперь удалим значение «береза».

Осуществить задуманное нам помогла «умная таблица», которая легка «расширяется», меняется.

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

Когда мы введем в пустую ячейку выпадающего списка новое наименование, появится сообщение: «Добавить введенное имя баобаб в выпадающий список?».

Нажмем «Да» и добавиться еще одна строка со значением «баобаб».

Выпадающий список в Excel с данными с другого листа/файла

Когда значения для выпадающего списка расположены на другом листе или в другой книге, стандартный способ не работает. Решить задачу можно с помощью функции ДВССЫЛ: она сформирует правильную ссылку на внешний источник информации.

1. Делаем активной ячейку, куда хотим поместить раскрывающийся список.
2. Открываем параметры проверки данных. В поле «Источник» вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).

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

Как сделать зависимые выпадающие списки

Возьмем три именованных диапазона:

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

1. Создадим первый выпадающий список, куда войдут названия диапазонов.

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


3. Теперь создадим второй раскрывающийся список. В нем должны отражаться те слова, которые соответствуют выбранному в первом списке названию. Если «Деревья», то «граб», «дуб» и т.д. Вводим в поле «Источник» функцию вида =ДВССЫЛ(E3). E3 – ячейка с именем первого диапазона.


Выбор нескольких значений из выпадающего списка Excel

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

1. Создаем стандартный список с помощью инструмента «Проверка данных». Добавляем в исходный код листа готовый макрос. Как это делать, описано выше. С его помощью справа от выпадающего списка будут добавляться выбранные значения.
Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("Е2:Е9" )) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False If Len(Target.Offset(0, 1)) = 0 Then Target.Offset(0, 1) = Target Else Target.End (xlToRight).Offset(0, 1) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub
2. Чтобы выбранные значения показывались снизу, вставляем другой код обработчика.
Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("Н2:К2" )) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False If Len(Target.Offset(1, 0)) = 0 Then Target.Offset(1, 0) = Target Else Target.End (xlDown).Offset(1, 0) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub
3. Чтобы выбираемые значения отображались в одной ячейке, разделенные любым знаком препинания, применим такой модуль.


Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range("C2:C5" )) Is Nothing And Target.Cells.Count = 1 Then
Application.EnableEvents = False
newVal = Target
Application.Undo
oldval = Target
If Len(oldval) <> 0 And oldval <> newVal Then
Target = Target & "," & newVal
Else
Target = newVal
End If
If Len(newVal) = 0 Then Target.ClearContents
Application.EnableEvents = True
End If
End Sub


Не забываем менять диапазоны на «свои». Списки создаем классическим способом. А всю остальную работу будут делать макросы.

Выпадающий список с поиском



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

В данной статье рассмотрим, как создать раскрывающийся список в excel 2007. Возьмем пример, когда нам нужно в ячейке выбрать заданные значения от 1 до 5 из выпадающего списка. Создаем сам список и выделяем его левой кнопкой мыши. Кликаем правой кнопкой мыши в выделенной области и выбираем пункт Имя диапазона .

В открывшемся окошке в поле Имя вводим название нашего списка, назовем Значение . В поле Область из выпадающего списка выберем Книга (либо номер Листа к которому желаете применить список). Жмем Ок.

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

Список создан. Теперь применим этот список к ячейке.

Выделяем ячейку, к которой будет привязан список. В ленте переходим на вкладку Данные и в группе Работа с данными кликаем по кнопке Проверка данных . В следующем окошке во вкладке Параметры в поле Тип данных выбираем из списка пункт Список.

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

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

Можно создать , минуя присвоение названия списка. То есть:

1. создаем сам список;
2. переходим в Ленте на вкладку Данные , жмем кнопку Проверка данных;
3. в открывшемся окошке, во вкладке Параметры в поле Тип данных выбираем Список;
4. в поле Источник кликаем левой кнопкой мыши для активации данного поля. Далее выделяем ячейки образующие список;
5. жмем Ок.

Все, раскрывающийся список в excel 2007 готов.

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

Инструкция для Excel

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

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

Основы того, как в «Экселе» сделать раскрывающийся список

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

Если вы создали имя диапазона в поле «Источник», введите значок равенства, после чего впишите название диапазона.

Необходимые настройки для создания

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

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

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

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

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

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

Чтобы удалить раскрывающейся список, выберите ячейку, содержащую его. Перейдите на вкладку «Данные» в ленте Microsoft Excel. Нажмите кнопку «Проверка данных» из группы «Работа с данными». Перейдите на вкладку «Настройки», нажмите кнопку «Очистить все», а затем - «OK».

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

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

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

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 нельзя.