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

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

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

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

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

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

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

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

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

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

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

Для начала на отдельном листе (это не обязательно) разместим список допустимых значений в одном столбце или одной строке (рис. 1а); см. также Excel-файл, лист «Список».

Рис. 1. Список фамилия: (а) в произвольном порядке; (б) в алфавитном порядке.

Скачать в формате , примеры в формате

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

Присвоим нашему списку имя диапазона. Для этого выделим диапазон; в нашем случае это область А2:А21 и введем имя диапазона, как показано на рис. 2; в нашем случае – это «фамилии»:

Рис. 2. Присвоение диапазону имени

Выберем область, в которой будем вводить фамилии (см. Excel-файл, лист «Ввод»). В нашем примере – А2:А32 (рис. 3). Перейдем на вкладку Данные, группу Работа с данными, выберем команду Проверка данных:

Рис. 3. Проверка данных

В диалоговом окне «Проверка вводимых значений» перейдем на вкладку Параметры (рис. 4). В поле «Тип данных» выберем «Список». В поле «Источник» укажем: (а) область ячеек, в которых хранится список; этот вариант подходит в том случае, если список расположен на том же листе Excel; (б) имя диапазона; этот вариант может использоваться как в том случае, когда список расположен на том же листе Excel, так и в том случае, если список расположен на другом листе Excel (как в нашем случае). В обоих случаях следует убедиться, что перед ссылкой или именем стоит знак равенства (=).

Рис. 4. Выбор источника данных для списка: (а) на том же листе; (б) на любом листе

И еще о двух опциях на вкладке «Параметры»:

• Игнорировать пустые ячейки. Если галочка установлена, Excel позволит оставить ячейку пустой. Если галочка снята, то из ячейки можно выйти только после выбора одной из фамилий списка. Особенность опции заключается в том, что перемещаться между ячейками (например клавишей Ввод или стрелками вверх / вниз) Excel позволит, а вот начать набор, потом стереть все символы и перейти в другую ячейку нельзя.
• Список допустимых ячеек. Если галочки нет, то, когда вы установите курсор в ячейку для ввода, значок списка не появится рядом с ячейкой, и соответственно выбрать из списка не получится. Хотя все остальные свойства работы со списком будут действовать, и Excel не позволит вам ввести произвольное значение в ячейку.

Перейдем в окне «Проверка вводимых значений» на вкладку «Сообщения для ввода». Поставим галочку в поле «Отображать подсказку, если ячейка является текущей». Введем в соответствующие поля заголовок и текст сообщения (рис. 5). В последующем, когда пользователь встанет на одну из ячеек области ввода (в примере на рис. 5 – в ячейку А6), отобразится созданное нами сообщение.

Рис. 5. Установка Сообщения для ввода

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

Рис. 6. Установка Сообщения об ошибке

Допустимые типы сообщений об ошибке (рис. 7):

• Останов – предотвращает ввод недопустимых данных; кнопка Повторить позволяет вернуться к вводу, кнопка Отмена Повторить.
• Предупреждение – предупреждает о вводе недопустимых данных, но не запрещает такой ввод; кнопка Да позволяет принять недопустимый ввод; кнопка Нет позволяет продолжить набор (ранее набранное в ячейке значение становится доступным для редактирования); кнопка Отмена очищает ячейку и позволяет начать ввод сначала или перейти к вводу в другие ячейки; по умолчанию выбрана кнопка Нет .
• Сообщение – уведомляет о вводе недопустимых данных; хотя и разрешает их ввести. Этот тип сообщения является самым гибким. При появлении информационного сообщения пользователь может нажать кнопку ОК , чтобы принять ввод недопустимых данных, либо нажать кнопку Отмена , чтобы отменить ввод; по умолчанию выбрана кнопка ОК.

Рис. 7. Выбор типа сообщения об ошибке

Некоторые замечания. 1. Если вы ввели в окне Сообщение вкладки Сообщение об ошибке слишком длинный текст, то окно сообщения об ошибке будет слишком широким (как на рис. 7); используйте перенос строки Shift + Enter в том месте сообщения, где вы хотите разделить строки (рис. 8).

Рис. 8. Окно сообщения об ошибке уменьшенной ширины

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

3. Максимальное число записей в раскрывающемся списке ограничено, правда, не слишком сильно:), а именно числом 32 767.

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

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

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

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

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

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

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

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

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

Для начала введите список диапазонов возраста в последовательные ячейки по столбцу или по строке. Мы ввели наши возрастные диапазоны в ячейки от A9 до A13 на том же рабочем листе, как показано ниже. Вы также можете добавить свой список параметров на другой рабочий лист в той же книге.

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

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

В разделе «Инструменты данных» на вкладке Данные нажмите кнопку Проверка данных .

Откроется диалоговое окно «Проверка данных». На вкладке «Параметры» выберите «Список» в раскрывающемся списке «Тип данных».

Теперь мы будем использовать Имя, которое мы назначили для диапазона ячеек, содержащих параметры нашего раскрывающегося списка. Введите =Возраст в поле «Источник» (если вы назвали диапазон ячеек как-то по-другому, замените «Возраст» на это имя). Убедитесь, что флажок Игнорировать пустые ячейки отмечен.

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

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

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

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

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

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

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

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

Option Explicit Option Compare Text Dim bu As Boolean Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.CountLarge > 1 Then Exit Sub If Target.Row = 1 Then Me.TextBox1.Visible = False: Me.ListBox1.Visible = False: Exit Sub If Target.Column = 3 Then " номер столбца, в который вносим значения bu = True With Me.TextBox1 .Top = Target.Top: .Text = Target.Value: .Activate End With With Me.ListBox1 .Top = Target.Top + 5 If (.Top + .Height + ActiveWindow.PointsToScreenPixelsY(0) * Application.InchesToPoints(1) * 15 / 1440) > _ (ActiveWindow.Application.Height + ActiveWindow.Application.Top) Then _ .Top = .Top - .Height + Target.Height "* ActiveWindow.Zoom / 100 .Clear End With bu = False Me.TextBox1.Visible = True: Me.ListBox1.Visible = True Else Me.TextBox1.Visible = False: Me.ListBox1.Visible = False End If End Sub Private Sub TextBox1_Change() If Len(TextBox1.Text) = 0 Or bu Then Exit Sub "при отсутствии символов для поиска - выход Dim x, i As Long, txt As String, lt As Long, s As String txt = TextBox1.Text: lt = Len(TextBox1.Text) "Где ищем значения x = Sheets("номенклатура";).Columns(1).SpecialCells(2).Offset(1).Value " For i = 1 To UBound(x, 1) " поиск по первым буквам "If txt = Mid(x(i, 1), 1, lt) Then s = s & x(i, 1) & "~" For i = 1 To UBound(x, 1) "поиск по любому вхождению If InStr(x(i, 1), txt) Then s = s & "~" & x(i, 1) Next i ListBox1.List = Split(s, "~";) End Sub Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer) If KeyCode = 13 Or KeyCode = 9 Then With Me.TextBox1 ActiveCell.Value = .Value .Visible = False: ListBox1.Visible = False End With ActiveCell(2, 1).Select End If End Sub Private Sub ListBox1_Click() If ListBox1.ListIndex = -1 Then Exit Sub Application.EnableEvents = False bu = True With Me.ListBox1 ActiveCell.Value = .Value Me.TextBox1.Text = .Value Me.TextBox1.Visible = False: .Visible = False End With Application.EnableEvents = True bu = False End Sub Private Sub Worksheet_Change(ByVal Target As Range) Dim lReply As Long If Target.Column = 2 Then Exit Sub If Not Intersect(Target, Range("C2:C100000";)) Is Nothing Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.CountIf(Sheets("номенклатура";).Columns(1), Target) = 0 Then lReply = MsgBox("Добавить введенное имя " & Target & " в выпадающий список", vbYesNo + vbQuestion) If lReply = vbYes Then Worksheets("номенклатура";).Range("номенклатура";).Cells(Worksheets("номенклатура";).Range("номенклатура";).Rows.Count + 1, 1) = Target End If End If End If Sheets("номенклатура";).Range("номенклатура";).Sort Key1:=Sheets("номенклатура";).Range("A1";), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal "этот код и поможет отсортировать в алфавитном порядке" End Sub