Элемент списка знаком нам по формам на сайтах. Удобно выбирать уже готовые значения. Например, никто не вводит месяц вручную, его берут из такого перечня. Заполнить выпадающий список в Excel можно с использованием различных инструментов. В статье рассмотрим каждый из них.
Как сделать выпадающий список в Excel
Как сделать выпадающий список в Excel 2010 или 2016 с помощью одной командой на панели инструментов? На вкладке «Данные» в разделе «Работа с данными» найдите кнопку «Проверка данных». Нажмите на нее и выберите первый пункт.
Откроется окно. Во вкладке «Параметры» в выпадающем разделе «Тип данных» выберите «Список».
Снизу появится строка для указания источников.
Указывать информацию можно по-разному.
- Ручной ввод
Введите перечень через точку с запятой.
- Выбор диапазона значений с листа Excel
Для этого начните выделять ячейки мышью.
Как отпустите – окно снова станет нормальным, а в строке появятся адреса.
- Создание выпадающего списка в Excel с подстановкой данных
Сначала назначим имя. Для этого создайте на любом листе такую таблицу.
Выделите ее и нажмите правую кнопку мыши. Щелкните по команде «Присвоить имя».
Введите имя в строку сверху.
Вызовите окно «Проверка данных» и в поле «Источник» укажите имя, поставив перед ним знак «=».
В любом из трех случаев Вы увидите нужный элемент. Выбор значения из выпадающего списка Excel происходит с помощью мыши. Нажмите на него и появится перечень указанных данных.
Вы узнали, как создать выпадающий список в ячейке Excel. Но можно сделать и больше.
Подстановка динамических данных Excel
Если Вы добавите какое-то значение в диапазон данных, которые подставляются в перечень, то в нем изменения не произойдет, пока вручную не будут указаны новые адреса. Чтобы связать диапазон и активный элемент, необходимо оформить первый как таблицу. Создайте вот такой массив.
Выделите его и на вкладке «Главная» выберите любой стиль таблицы.
Обязательно поставьте галочку внизу.
Вы получите такое оформление.
Создайте активный элемент, как было описано выше. В качестве источника введите формулу
=ДВССЫЛ("Таблица1[Города]")
Чтобы узнать имя таблицы, перейдите на вкладку «Конструктор» и посмотрите его. Можете поменять имя на любое другое.
Функция ДВССЫЛ создает ссылку на ячейку или диапазон. Теперь ваш элемент в ячейке привязан к массиву данных.
Попробуем увеличить количество городов.
Обратная процедура - подстановка данных из выпадающего списка в таблицу Excel, работает очень просто. В ячейку, куда надо вставить выбранное значение из таблицы, введите формулу:
=Адрес_ячейки
Например, если перечень данных находится в ячейке D1, то в ячейке, куда будут выведены выбранные результаты введите формулу
=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. Надеемся, эта информация поможет вам.
