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

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

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

Второй способ создания выпадающего списка в Excel более изящный и универсальный. Выделяете диапазон данных для выпадающего списка, затем нажимаете на пункт меню Формула — Диспетчер имен — Создать . Заполняете поле Имя , и копируете его (оно Вам понадобится позже). Имя должно начинаться с буквы или символа подчеркивания, и не должно содержать пробелов. Нажимаете ОК . Закрываете окно.

Затем выбираете ячейку, в которой будет выпадающий список Excel (можно сразу выделить несколько ячеек, если в них будут одинаковые выпадающие списки). После этого выбираете пункт меню Данные — Проверка данных , затем в окошке Тип данных выберите строку Список , в поле Источник поставьте знак равно, и без пробела вставьте то, что Вы копировали (значение поля Имя ). Не забудьте про знак = , иначе ничего не получится. Выглядит надпись в поле Источник примерно так: =Имя_диапазона . Нажимаете ОК .

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

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

Для надежности можно лист с данными для выпадающих списков скрыть или защитить. Чтобы скрыть лист Excel , нажимаете на его название правой клавишей мыши, и выбираете Скрыть . Чтобы отобразить скрытые листы, нажимаете на название любого открытого листа Excel правой клавишей мыши, и выбираете Отобразить .

Чтобы защитить лист Excel , выбираете пункт меню Редактировать — Защитить лист , и прописываете пароль и действия, которые разрешены для пользователей.

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

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

Из раскрывающегося списка несколькими кликами мыши можно ввести в назначенные ячейки нужную информацию. Широко применяются раскрывающиеся списки при написании расчетных программ в Excel.

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

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

Вариант №0 — «Элементарный».

Делая очередную запись в ячейку А9, при наборе первой буквы наименования профиля, например «Ш», Excel предлагает заполнить ячейку словом «Швеллер». После набора «Ш» достаточно нажать кнопку «Ввод» на клавиатуре – и слово будет введено в ячейку.

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

Переходим непосредственно к вариантам создания раскрывающихся списков.

Вариант №1 — «Простейший».

Если активировать мышью ячейку А9, нажать сочетание клавиш «Alt» «↓», то появится раскрывающийся список, содержащий все ранее введенные в этом столбце значения. Остается лишь выбрать мышью нужную запись. Вместо набора вышеуказанного сочетания клавиш можно щелчком правой кнопки мыши вызвать контекстное меню и выбрать в нем пункт «Выбрать из раскрывающегося списка…». В результате увидим тот же выпадающий список.

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

Вариант №2 — «Простой».

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

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

1. Создаем список возможных значений, записав их в столбец по одному в ячейку. Допустим это перечень в ячейках А2…А8.

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

3. Выбираем в главном меню кнопку «Данные» – «Проверка…».

4. В выпавшем окне «Проверка вводимых значений» выбираем вкладку «Параметры».

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

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

7. Устанавливаем (если он не установлен по умолчанию) флажок «Список допустимых значений» и нажимаем кнопку «ОК».

Раскрывающийся список готов. Его можно скопировать как формулы в любое количество ячеек!

Вариант №3 — «Сложный».

Этот вариант создания раскрывающегося списка, не смотря на свое название «Сложный», по сути таковым не является. Для создания выпадающего списка в нем используется элемент «Поле со списком» панели инструментов «Формы».

Создадим раскрывающийся список этим способом.

1. Создаем список-справочник в ячейках А2…А8.

2. Выбираем в главном меню кнопку «Вид» – «Панели инструментов» – «Формы».

3. В появившейся панели «Формы» выбираем «Поле со списком» и рисуем его, например, в ячейке А9.

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

4. Щелкаем правой кнопкой мыши по нарисованному элементу и в появившемся контекстном меню выбираем «Формат объекта».

5. В выпавшем окне «Форматирование объекта» на вкладке «Элемент управления» заполняем поля в соответствии с рисунком, расположенном ниже и нажимаем «ОК».

6. Раскрывающийся список готов. Он выводит порядковый номер элемента списка в связанную ячейку В9. (Можете назначить любую удобную вам ячейку, не обязательно В9!)

Для вывода в какую-либо ячейку самого значения из списка-справочника применим функцию ИНДЕКС. Допустим, нам необходимо вывести значение в ячейку А9, расположенную под элементом «Поле со списком».

Для этого в ячейку А9 запишем формулу: =ИНДЕКС(A2:A8;В9)

Наглядный пример подробно рассмотрен в статье « ». Можно перейти по ссылке и ознакомиться.

Раскрывающийся список, созданный этим способом плюс применение функций ИНДЕКС и/или ВПР предоставляют безграничные возможности пользователю при извлечении данных из различных базовых справочных таблиц.

Вариант №4 — «Самый сложный».

Для создания выпадающего списка в этом случае используется также элемент «Поле со списком», но панели инструментов «Элементы управления» (в MS Excel 2003). Это так называемые элементы ActiveX. Здесь все очень похоже внешне на вариант №3, но значительно шире возможности настройки и форматирования элемента.

1. Выбираем в главном меню кнопку «Вид» – «Панели инструментов» – «Элементы управления».

2. В появившейся панели «Элементы управления» выбираем «Поле со списком» и рисуем его в ячейке А9. Элемент ActiveX «Поле со списком» размещается не в самой ячейке, а сверху, накрывая ее!!!

3. Нажимаем кнопку «Свойства» на панели «Элементы управления» и в выпавшем окне «Properties» («Свойства») вручную вписываем диапазон исходных данных, адрес связанной ячейки (ячейки, куда будет введено выбранное значение) и количество отображаемых строк.

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

5. Отжимаем кнопку «Выход из режима конструктора» на панели «Элементы управления» и проверяем работу раскрывающегося списка. Все работает! Выбранное значение записано в ячейку А9, в нашем примере — под элементом «Поле со списком». Вообще связанной ячейкой может быть абсолютно любая кроме ячеек, где располагается базовый список.

Итоги.

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

На практике я чаще всего создаю раскрывающиеся списки в Excel, используя варианты №1 и №3, реже — вариант №2 и совсем редко — вариант №4, хотя именно он является, безусловно, самым гибким, предоставляющим самые широкие возможности.

Но часто наш выбор в жизни определяют вкусы, стереотипы и привычки! В зависимости от задачи, которую необходимо решить при работе в 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

ПРЕДСТАВЬТЕ СИТУАЦИЮ: Мы хотим создать в Excel небольшую табличку, где можно выбрать страну и соответствующий ей город. При этом с помощью выпадающих списков, необходимо ограничить доступные пользователям варианты стран и городов, из которых они могут выбирать. В первой ячейке мы сделаем выбор страны, а во второй будут доступны только принадлежащие выбранной стране города. Думаю, это понятно?

Итак, давайте начнём наш простой пример с того, как можно создать связанный (или зависимый) выпадающий список в Excel? В ячейке B1 мы будем выбирать страну, а в ячейке B2 – принадлежащий ей город, как на примере:

Для начала нужно создать базу данных. На втором листе я занес список стран, которые хочу дать пользователям на выбор в первом раскрывающемся списке, а в соседнем столбце указал числовой индекс, который соответствует одному из списков городов. Списки городов располагаются правее в столбцах D , F и H . Так, например, рядом с France стоит индекс 2 , который соответствует списку городов 2 . Позже Вы увидите, как этот индекс будет использован.

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

Мы будем использовать именованные диапазоны и сделаем так, чтобы эти связанные выпадающие списки работали во всех версиях Excel. Следующий шаг – создать именованные диапазоны для наших списков. На вкладке Formulas (Формулы) есть команда Name Manager (Диспетчер имён). Нажав на нее, откроется диалоговое окно Name Manager (Диспетчер имён).

Нажмите кнопку New (Создать), чтобы добавить новый именованный диапазон. Откроется диалоговое окно New Name (Создание имени).

В поле Name (Имя) введите имя Country для нашего первого именованного диапазона, а в поле Refers to (Диапазон) выберите тот, в котором хранится список стран:

Sheet3!$A$3:$A$5

Имена диапазонам, содержащим города, можно присвоить точно таким же образом.

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

Откроется диалоговое окно Data Validation (Проверка вводимых значений).

Мы хотим дать пользователю на выбор список вариантов, поэтому в поле Allow (Тип данных) выберите List (Список). Это активирует поле Source (Источник), где необходимо указать имя диапазона со странами. Введите в этом поле “=Country” и жмите ОК . Теперь нам нужно сделать второй раскрывающийся список, чтобы пользователи могли выбрать город. Мы поместим этот раскрывающийся список в ячейку B2 . А теперь внимание – фокус! Нам нужно проверить содержимое ячейки с названием страны (ячейка B1), чтобы получить индекс соответствующий базе данных с городами. Если пользователь выберет Portugal , то мы должны обратиться к базе с индексом 3 , в которой хранятся названия городов Португалии. Мы воспользуемся функцией ВПР (VLOOKUP) для поиска значения из ячейки B1 в таблице с названиями стран. После того как индекс будет известен, мы выберем список, который станет источником данных для нашего второго выпадающего списка. Для этого напишем такую формулу:

CHOOSE(VLOOKUP(B1,Sheet3!$A$3:$B$5,2,FALSE),England,France,Portugal)
=ВЫБОР(ВПР(B1;Sheet3!$A$3:$B$5;2;ЛОЖЬ);England;France;Portugal)

Что же делает эта формула? Она ищет значение из ячейки B1 в списке стран и возвращает соответствующий индекс, который затем использует функция CHOOSE (ВЫБОР), чтобы выбрать 1-й, 2-й или 3-й именованный диапазон.

Вот так будет выглядеть наш второй раскрывающийся список:

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

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

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

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

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

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

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

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

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

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