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

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

Как сделать выпадающий список в Excel

Как сделать выпадающий список в Excel 2010 или 2016 с помощью одной командой на панели инструментов? На вкладке «Данные» в разделе «Работа с данными» найдите кнопку «Проверка данных». Нажмите на нее и выберите первый пункт.

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


Снизу появится строка для указания источников.


Указывать информацию можно по-разному.

Сначала назначим имя. Для этого создайте на любом листе такую таблицу.

Выделите ее и нажмите правую кнопку мыши. Щелкните по команде «Присвоить имя».

Введите имя в строку сверху.

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


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

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

Подстановка динамических данных Excel

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

Выделите его и на вкладке «Главная» выберите любой стиль таблицы.


Обязательно поставьте галочку внизу.

Вы получите такое оформление.

Создайте активный элемент, как было описано выше. В качестве источника введите формулу

=ДВССЫЛ("Таблица1[Города]")

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


Функция ДВССЫЛ создает ссылку на ячейку или диапазон. Теперь ваш элемент в ячейке привязан к массиву данных.

Попробуем увеличить количество городов.


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

Адрес_ячейки

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

Как убрать (удалить) выпадающий список в Excel

Откройте окно настройки выпадающего списка и выберите «Любое значение» в разделе «Тип данных».

Ненужный элемент исчезнет.

Зависимые элементы

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


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

Это будет название города.


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


Поэтому переименуем эти города, поставив нижнее подчеркивание.


Первый элемент в ячейке A9 создаем обычным образом.


А во втором пропишем формулу:

ДВССЫЛ(A9)

Сначала Вы увидите сообщение об ошибке. Соглашайтесь.

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

Как настроить зависимые выпадающие списки в Excel с поиском

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


Для второго перечня нужно ввести формулу:

СМЕЩ($A$1;ПОИСКПОЗ($E$6;$A:$A;0)-1;1;СЧЁТЕСЛИ($A:$A;$E$6);1)

ПОИСКПОЗ возвращает номер ячейки с выбранным в первом списке (E6) городом в указанной области SA:$A.
СЧЕТЕСЛИ считает количество совпадений в диапазоне со значением в указанной ячейке (E6).

Мы получили связанные выпадающие списки в Excel с условием на совпадение и поиском диапазона для него.

Мультивыбор

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


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

Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("C2:F2")) 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

Обратите внимание, что в строке

If Not Intersect(Target, Range("E7")) Is Nothing And Target.Cells.Count = 1 Then

Следует проставить адрес ячейки со списком. У нас это будет E7.

Вернитесь на лист Excel и создайте в ячейке E7 список.

При выборе значения будут появляться под ним.

Следующий код позволит накапливать значения в ячейке.

Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("E7")) 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. Надеемся, эта информация поможет вам.

Отличного Вам дня!

Если вам нужно, чтобы при нажатии на одну из ячеек в табличном документе 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, находим значок со списком «Поле со списком». Выносим элемент на страницу документа.

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

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

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

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

Создаем раскрывающийся список в Экселе: способ первый

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

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

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

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

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

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

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

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

Как сделать списки в Excel 2007

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

Задаем имя «Город_М_О» и жмем «ОК».

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


В появившемся окне выбираем тип данных «Список» и в поле «Источник» вводим «=Город_М_О», то есть заданное нами имя диапазона, который содержит список.


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


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

Как это сделать в Excel 2003

Здесь, чтобы присвоить имя диапазону нам потребуется зайти в меню «Вставка»


И окно присвоения имени выглядит немного по-другому.


Так же переходим в нужную нам ячейку и в меню «Данные» выбираем «Проверка». А открывшееся окно будет таким же, как и в Excel 2007.
Покоряйте 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