Что такое выпадающий список в excel. Создание выпадающего списка в Excel

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Графики и диаграммы (5)
Работа с VB проектом (12)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (64)
Разное (41)
Баги и глюки Excel (4)

Выпадающие списки

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

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

Создание списков
Необходимо выбрать ячейку(или сразу несколько ячеек) , в которую необходимо поместить этот список. В меню выбираем Данные (Data) -Проверка данных (Data Validation) . Переходим на вкладку Параметры (Settings) и в выпадающем списке Тип данных (Allow) выбираем Список (List) .
Поле Источник (Source) : предназначено как раз для ввода значений, из которых будет состоять выпадающий список. Варианты указания значений:

Параметры списков
Распространить изменения на другие ячейки с тем же условием (Apply this chages to all other cells with the same settings) - данный пункт понадобится уже после создания списка в ячейках: если на листе есть много разных проверок данных необходимо изменить тип или условия проверки. Выделяете одну ячейку на листе, меняете необходимые параметры проверки данных и ставите данную галку. Нажимаете ОК . Внесенные изменения будут применены для всех ячеек, в которых были такие же условия.

Подробнее с остальными возможностями проверки данных(помимо выпадающих списков) можно ознакомиться в этой статье: Проверка данных

Скачать пример

(45,5 KiB, 12 556 скачиваний)

Статья помогла? Поделись ссылкой с друзьями! Видеоуроки

{"Bottom bar":{"textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24,"textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance":30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500,"textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100%; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive":"","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40}}

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

Несколько наиболее распространенных типов выпадающих списков, которые можно создать в программе Excel:

С функцией мультивыбора;
С наполнением;
С добавлением новых элементов;
С выпадающими фото;
Другие типы.

Сделать список в Эксель с мультивыбором

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

Рассмотрим подробнее все основные и самые распространенные типы, и процесс их создание на практике.

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

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

Чтобы создать такой, следуйте инструкции:

Выделите ячейки. Если посмотреть на рисунок, то выделять нужно начиная с C2 и заканчивая C5;
Найдите вкладку «Данные», которая расположена на главной панели инструментов в окне программы. Затем нажмите на клавишу проверки данных, как показано на рисунке ниже;

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

Пр имер заполнения:

Таким образом вы создадите классический горизонтальный список с функцией мультивыбора.

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

Создать список в Экселе с наполнением

Стандартный перечень с наполнением позволит вам автоматизировать процесс ввода информации. При нажатии на ее, появится выбор ее возможных значений.

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

Самый простой способ создать такой перечень – использовать «умные таблицы программы».

С их помощью можно легко и быстро форматировать необходимые вам виды списков с наполнением:

Выделите необходимые ячейки и нажмите в главной вкладке на клавишу «Форматировать как таблицу»;

Пример форматирования и расположение клавиш:

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

Создать раскрывающийся список в ячейке (версия программы 2010)

Также можно создавать перечень в .

Пример указан на рисунке ниже:

Пример в ячейке листа

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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 можно с использованием различных инструментов. В статье рассмотрим каждый из них.

Как сделать выпадающий список в 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. Надеемся, эта информация поможет вам.