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

Элемент списка знаком нам по формам на сайтах. Удобно выбирать уже готовые значения. Например, никто не вводит месяц вручную, его берут из такого перечня. Заполнить выпадающий список в 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, в которой постоянно фигурирует один и тот же список данных, или может быть Вы просто не хотите множество раз использовать действие Копировать > Вставить , то было бы очень удобно иметь заранее настроенный и сохранённый список, чтобы Excel мог помочь Вам в работе. Использование настраиваемых списков – это хорошее решение для данной ситуации. Далее я покажу, как создать настраиваемый список в Excel.

Создаем пользовательский список в Excel

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

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

Затем я выделил эту область, нажал на кнопку Office и в нижней части открывшегося меню щелкнул по Excel Options (Параметры Excel).

В разделе Popular (Общие) Вы найдёте пункт Create lists for use in sorts and fills sequences (Создавать списки для сортировки и заполнения) – кликните по Edit Custom Lists (Изменить списки).

Если Вы работаете в Excel 2010, то Вам нужен другой маршрут. Откройте вкладку File (Файл) и нажмите Options (Параметры). Затем пролистайте вниз, найдите кнопку Edit Custom Lists (Изменить списки).

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

Создаем список с нуля

У меня есть два способа, как создать настраиваемый список. Я могу создать его вручную, введя каждое значение в поле List entries (Элементы списка) и нажав Add (Добавить). Если Вы выберете этот путь, то столкнетесь с некоторыми ограничениями. Поле List entries (Элементы списка) позволяет ввести не более 255 символов. Будьте внимательны к тому, из скольких символов состоит каждая Ваша запись!

Подсказка: Если вы планируете вводить список в поле List entries (Элементы списка) вручную, не ставьте лишние пробелы между элементами. Если пробелы будут стоять до или после элемента, то Microsoft их просто не станет учитывать, а если между словами одного элемента, то все они будут сохранены.

Создание списка из существующего диапазона данных

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

Помните, у нас было ограничение на количество символов, которое можно ввести в поле List entries (Элементы списка)? Только не при импорте! Теперь максимальный размер списка где-то 2000 символов! Нажмите ОК , чтобы закрыть окно с параметрами списка, и ещё раз ОК , чтобы закрыть окно параметров Excel.

Для пользовательского списка можно импортировать только текстовые значения. Если Вам нужно создать настраиваемый список с календарными датами или числами, то придётся использовать поле List entries (Элементы списка).

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

Использование пользовательских списков в Excel

Итак, наш настраиваемый список готов к использованию. Выделите ячейку и введите с клавиатуры любой элемент этого списка. Нажмите на маркер автозаполнения (небольшой квадратик в правом нижнем углу ячейки), и потяните за него, чтобы продолжить последовательность. Чуть правее я вписал “Monday”, затем нажал на маркер и потянул вправо, Excel заполнил ячейки автоматически.

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

Сортировка по пользовательскому списку

Одна из приятных возможностей, которую дает работа с настраиваемыми списками, – это возможность упорядочить данные по любому из списков, сохранённых на Вашем компьютере. Кликните по одному или нескольким столбцам, затем нажмите Sort & Filter (Сортировка и фильтр), раскройте выпадающее меню Order (Порядок), кликните Custom List (Настраиваемый список) и выберите список, по которому желаете выполнить сортировку.

Сортировка может включать более одного столбца. Если Вы хотите добавить ещё один уровень и упорядочить сначала по месяцам, а затем по номеру счёта, то можете кликнуть Add Level (Добавить уровень) и определить, каким образом должны отображаться данные. Когда закончите, нажмите ОК . Теперь информация упорядочена по выбранному списку!

Все достаточно просто! Применение пользовательских списков очень удобно особенно для тех, кто хочет избежать многократного копирования и вставки данных. Если Вы знаете, что есть набор данных, который в любой момент может понадобиться, почему бы не сделать из него настраиваемый список? Кто знает, возможно это сохранит Вам гораздо больше времени, чем Вы можете предположить… и оставит шефа с мыслью, что Вам пришлось изрядно потрудиться. Пусть он так думает.

Добрый день, уважаемый читатель!

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

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

Ну что же, рассмотрим создание выпадающих списков и для чего же это нужно:

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

Ну вот 2 вопроса, что и для чего, я рассказал, а вот о том, как это сделать ниже и поговорим.

А делать выпадающий список в ячейке будем в несколько этапов:

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

2. Выделяем нужный нам диапазон и в меню выбираем пункт “Данные” — “Проверка данных” , в появившемся контекстном окне выбираем из указанного выбора пункт “Список” .

3. В разблокированной ниже строке указываем диапазон данных, которые должны быть у нас в выпадающем списке. Нажимаем “Ок” и работа сделана.

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

А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

Прогресс человечества основывается на желании каждого человека жить не по средствам
Сэмюэль Батлер, философ

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

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

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

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

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

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

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

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

Итак, для создания выпадающего списка необходимо:

1. Создать список значений, которые будут предоставляться на выбор пользователю (в нашем примере это диапазон M1:M3 ), далее выбрать ячейку в которой будет выпадающий список (в нашем примере это ячейка К1 ), потом зайти во вкладку "Данные ", группа "Работа с данными ", кнопка "Проверка данных "

2. Выбираем "Тип данных " -"Список " и указываем диапазон списка

3. Если есть желание подсказать пользователю о его действиях, то переходим во вкладку "Сообщение для ввода " и заполняем заголовок и текст сообщения

которое будет появляться при выборе ячейки с выпадающим списком

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

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

5. Если список значений находится на другом листе, то вышеописанным образом создать выпадающий список не получится (до версии Excel 2010). Для этого необходимо будет присвоить имя списку. Это можно сделать несколько способами. Первый : выделите список и кликните правой кнопкой мыши, в контекстном меню выберите "Присвоить имя "

Для Excel версий ниже 2007 те же действия выглядят так:

Второй : воспользуйтесь Диспетчером имён (Excel версий выше 2003 - вкладка "Формулы " - группа "Определённые имена "), который в любой версии Excel вызывается сочетанием клавиш Ctrl+F3 .
Какой бы способ Вы не выбрали в итоге Вы должны будете ввести имя (я назвал диапазон со списком list ) и адрес самого диапазона (в нашем примере это"2"!$A$1:$A$3 )

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

7. Готово!

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

То есть вручную, через ; (точка с запятой) вводим список в поле "Источник ", в том порядке в котором мы хотим его видеть (значения введённые слева-направо будут отображаться в ячейке сверху вниз).

При всех своих плюсах выпадающий список, созданный вышеописанным образом, имеет один, но очень "жирный" минус: проверка данных работает только при непосредственном вводе значений с клавиатуры. Если Вы попытаетесь вставить в ячейку с проверкой данных значения из буфера обмена, т.е скопированные предварительно любым способом, то Вам это удастся. Более того, вставленное значение из буфера УДАЛИТ ПРОВЕРКУ ДАННЫХ И ВЫПАДАЮЩИЙ СПИСОК ИЗ ЯЧЕЙКИ, в которую вставили предварительно скопированное значение. Избежать этого штатными средствами Excel нельзя.