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

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

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

Первый способ создания двухуровнего списка

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

Теперь приступим к созданию первого выпадающего списка группы (в моем случае - список стран):

1. Выберите ячейку, в которую будете вставлять выпадающий список;
2. Переходим на вкладку ленты Данные ;
3. Выбираем команду Проверка данных ;
4. В выпадающем списке выбираем значение Список ;
5. В поле Источник указываем следующую формулу =ДВССЫЛ("Таблица1[#Заголовки]") .

Формула ДВССЫЛ возвращает ссылку на диапазон заголовков «умной» таблицы. Преимущество использования такой таблицы в том, что при добавлении столбцов, выпадающий список будет автоматически расширен.

Осталось создать второй зависимый выпадающий список - список подгрупп.

Смело повторяем 4 первых пункта описанных выше. Источником в окне Проверка данных для второго выпадающего списка будет служить формула =ДВССЫЛ("Таблица1["&F2&"]") . Ячейка F2 в данном случае - значение первого выпадающего списка.

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

Второй способ создания двухуровнего списка

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

ВАЖНО! Перед созданием зависимого списка по подгруппам необходимо отсортировать исходную таблицу по первому столбцу (столбец с группой) далее будет понятно зачем это делается.

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

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

Рекомендация: удобно в качестве источника указывать именованный диапазон. Для его создания откройте Диспетчер имен со вкладки Формулы и присвойте имя диапазону с уникальными значениями.

Теперь самая сложная часть - указать в Источнике динамическую ссылку на диапазон со значениями второго выпадающего списка (списка подгрупп). Решать ее будем с помощью функции СМЕЩ(ссылка, смещ_по_строкам, смещ_по_столбцам, [высота], [ширина]) , которая возвращает ссылку на диапазон, отстоящий от ячейки или диапазона ячеек на заданное число строк и столбцов.

• Ссылка в нашем случае - $A$1 - верхний левый угол исходной таблицы;
• Смещ_по_строкам - ПОИСКПОЗ(F3;$A$1:$A$67;0)-1 - номер строки со значением искомой группы (в моем случае страны ячейка F3 ) минус единица;
• Cмещ_по_столбцам - 1 - так как нам необходим столбец с подгруппами (городами);
• [Высота] - СЧЁТЕСЛИ($A$1:$A$67;F3) - количество подгрупп в искомой группе (количество городов в стране F3 );
• [Ширина] - 1 - так как это ширина нашего столбца с подгруппами.

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

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

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

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

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

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

Создание выпадающего списка с источником на этом же листе

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

В этом окне выбираем тип данных — список, в строке источник указываем диапазон ранее созданного списка-источника. Можно ввести этот диапазон с клавиатуры, но легче это сделать, просто выделив его мышью. После нажатия кнопки ОК увидим, возле нашей ячейки показывается стрелочка, при нажатии на которую выпадает наш перечень.

Создание выпадающего списка с источником на другом листе

Использование списка-источника, расположенного на том же активном листе, не совсем удобно, поскольку можно «нечаянно» изменить его содержимое. Поэтому предпочтительнее этот список «спрятать» на другом листе, а доступ к нему заблокировать.

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

Выполняем это так: создаем список данных на одном листе, затем выделяем его и присваиваем ему имя, кликаем последовательно вкладка формулы/присвоить имя (в разделе определенные имена), в открывшемся окне задаем имя списка. Если, предварительно мы не выделили список, то задаем диапазон его ячеек.

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

Как скрыть лист с источником списка

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

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

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

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

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

Способ 1: создаем дополнительный список

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

Шаг 1: подготавливаем данные

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

Если вы не хотите, чтобы таблица с данными находилась на том листе, что и основная, вы можете создать ее на отдельном листе. Роли это не сыграет.

Шаг 2: вводим имя диапазона

Чтобы использовать опцию выбора из списка в Excel, предварительно надо ввести имя диапазона с данными для будущего списка. Делается это довольно просто:

1. Выделите ячейки, в которых находятся в данном случае наименования товаров.
2. Нажмите правой кнопкой мыши (ПКМ) по выделению.
3. Выберите из меню опцию «Присвоить имя».
4. В появившемся окне в поле «Имя» введите название диапазона. Оно может быть абсолютно любым.
5. Нажмите «ОК».

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

Шаг 3: делаем выпадающий список

Теперь можно переходить непосредственно к использованию опции выбора из списка в Excel. Делается это следующим образом:

1. Выделите нужный диапазон ячеек, в котором будут располагаться выпадающие списки.
2. Перейдите на вкладку «Данные».
3. В группе инструментов «Работа с данными» нажмите по кнопке «Проверка данных».
4. В появившемся окне на вкладке «Параметры» выберите из выпадающего списка «Тип данных» значение «Список».
5. Введите в поле «Источник» название ранее созданного диапазона ячеек, предварительно поставив знак равенства. В нашем случае - «=Продукты».
6. Нажмите «ОК».

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

Способ 2: создание выпадающего списка через меню «Разработчика»

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

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

Шаг 1: включаем меню «Разработчика»

Итак, в первую очередь необходимо активировать меню «Разработчика», так как по умолчанию его нет среди прочих вкладок.

1. Нажмите по кнопке «Файл».
2. Нажмите по кнопке «Параметры».
3. В появившемся одноименном окне перейдите в раздел «Настройка ленты».
4. В области «Основные вкладки» установите отметку напротив пункта «Разработчик».
5. Нажмите «ОК».

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

Шаг 2: вставляем выпадающий список

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

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

Уже на этом этапе нужный элемент появится, но, если нажать по нему, откроется пустой список. Соответственно, надо добавить в него продукты.

Шаг 3: задаем необходимые параметры

Чтобы добавить в выпадающий список пункты, необходимо:

1. На панели инструментов нажать по кнопке «Режим конструктора».
2. Затем нажать кнопку «Свойства элемента управления», располагающуюся рядом.
3. В появившемся окне со свойствами в графе ListFillRange введите диапазон ячеек, в котором находятся пункты будущего выпадающего списка.
4. Теперь нажмите ПКМ по выпадающему списку и в меню выберите «Объект ComboBox», а в подменю Edit.

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

Способ 3: создание связанного списка

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

Шаг 1: создаем дополнительный список

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

Шаг 2: Связываем первый список со вторым

Ну а теперь перейдем непосредственно к основному - к созданию второго элемента «Выбора из списка» в Excel, который будет связан с первым.

1. Установите курсор в той ячейке, где будет располагаться второй список.
2. Откройте окно «Проверки вводимых значений», нажав на вкладке «Данные» по кнопке «Проверка данных».
3. В появившемся окне на вкладке «Параметры» выберите из выпадающего списка «Тип данных» пункт «Список».
4. В поле для ввода «Источник» введите формулу «ДВССЫЛ», ссылающуюся на первый список. В данном случае она будет выглядеть следующим образом: «=ДВССЫЛ($B3)».
5. Нажмите «ОК».

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

Заключение

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