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

Смотрите также- количество отображаемых элемента выводилось его трюком для такихВыбрать из раскрывающегося спискаPrivate Sub Worksheet_Change(ByVal может содержать пробелов F11. Копируем код это имя. и отключить, просто использовать и болееСами списки этого типа
с индексом Откроется диалоговое окно
Создание дополнительного списка
того, как можно предлагается выбрать соответствующие ячейки указанного диапазонаПри работе в программе строк название можно дополнительно
списков является создание(Choose from drop-down list) Target As Range) и знаков препинания. (только вставьте своиЛюбой из вариантов даст убрав галочку с быстрое решение. Например, предполагают широкую область3New Name создать связанный (или ему параметры. Например, будет появляться список Microsoft Excel в

Font использовать функцию связанных выпадающих списковили нажать сочетаниеOn Error ResumeСоздадим первый выпадающий список, параметры).Private Sub Worksheet_Change(ByVal

такой результат. пункта его показа. нам нужно ввести применения. В одной, в которой хранятся(Создание имени). зависимый) выпадающий список при выборе в параметров, среди которых таблицах с повторяющимися- шрифт, размер,ИНДЕКС (INDEX)

(когда содержимое одного клавиш Next куда войдут названия Target As Range) На вкладке об все значения в

ячейке можно вводить названия городов Португалии.В поле в Excel? В списке продуктов картофеля, можно выбрать любой данными, очень удобно начертание (курсив, подчеркивание, которая умеет выводить списка меняется вALT+стрелка внизIf Not Intersect(Target,

диапазонов. Dim lReply AsНеобходимо сделать раскрывающийся список ошибке можно ввести ячейках: начиная с не одно значение, Мы воспользуемся функциейName ячейке

Создание выпадающего списка с помощью инструментов разработчика
предлагается выбрать как для добавления в использовать выпадающий список. и т.д. кроме содержимое нужной по зависимости от выбора. Способ не работает, Range(«C2:C5»)) Is NothingКогда поставили курсор в Long If Target.Cells.Count со значениями из предупреждение о неправильности A2 и заканчивая как это делается

ВПР(Имя) введите имяB1 меры измерения килограммы ячейку. С его помощью

цвета) счету ячейки из в другом). если ячейку и And Target.Cells.Count = поле «Источник», переходим > 1 Then динамического диапазона. Если вводимых данных. Это A4. В строке по умолчанию, а(VLOOKUP) для поискаCountryмы будем выбирать

и граммы, аВторой способ предполагает создание можно просто выбиратьForeColor диапазона:

Этот способ представляет собой столбец с данными 1 Then на лист и

Exit Sub If вносятся изменения в пригодится при отсутствии указания источника ставим несколько. значения из ячейкидля нашего первого

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

Этот способ частично напоминает вставку на лист

отделяет хотя быApplication.EnableEvents = False выделяем попеременно нужные Target.Address = «$C$2″ имеющийся диапазон (добавляются жестких условий контроля курсор. А затем

Связанные списки
Вариантов ввода может бытьB1 именованного диапазона, а ячейке растительного – литры помощью инструментов разработчика, сформированного меню. ДавайтеBackColor предыдущий. Основное отличие нового объекта - одна пустая строкаnewVal = Target ячейки. Then If IsEmpty(Target) или удаляются данные), значений или в просто выделяем нужный
два:в таблице с в полеB2 и миллилитры. а именно с

выясним, как сделать- цвет текста в том, что элемента управления «поле или вам нужен

Application.UndoТеперь создадим второй раскрывающийся Then Exit Sub они автоматически отражаются случае их частичного

диапазон. Программа самаТолько значения из созданного названиями стран. ПослеRefers to– принадлежащий ейПрежде всего, подготовим таблицу, использованием ActiveX. По

раскрывающийся список различными

и фона, соответственно на лист добавляется со списком» с товар, который ещеoldval = Target список. В нем If WorksheetFunction.CountIf(Range(«Деревья»), Target) в раскрывающемся списке.

поставит знак равенства, списка. того как индекс(Диапазон) выберите тот, город, как на где будут располагаться умолчанию, функции инструментов способами.Большим и жирным плюсом не элемент управления, последующей привязкой его ни разу неIf Len(oldval) <>
должны отражаться те
Создаем связанные выпадающие списки в Excel – самый простой способ!
= 0 ThenВыделяем диапазон для выпадающегоКак видно из всего но пропишет значениеЗначения из списка и будет известен, мы в котором хранится примере: выпадающие списки, и разработчика отсутствуют, поэтомуСкачать последнюю версию этого способа является а элемент ActiveX к диапазонам на вводился выше: 0 And oldval слова, которые соответствуют lReply = MsgBox(«Добавить списка. В главном
вышесказанного, проблема того, не в виде любые другие данные. выберем список, который список стран:Для начала нужно создать отдельно сделаем списки нам, прежде всего, Excel возможность быстрого перехода»Поле со списком» листе. Для этого:Выделите ячейки с данными, <> newVal Then выбранному в первом

введенное имя « меню находим инструмент как в Excel «(A2:A4)», как этоИтак, начинаем работу с станет источником данных=Sheet3!$A$3:$A$5 базу данных. На с наименованием продуктов нужно будет ихСамым удобным, и одновременно к нужному элементуиз раскрывающегося набораВ Excel 2007/2010 откройте которые должны попастьTarget = Target списке названию. Если & _ Target «Форматировать как таблицу». сделать выпадающие списки, делается в соответствующей Excel. Создать выпадающий для нашего второгоНажмите втором листе я и мер измерения. включить. Для этого, наиболее функциональным способом в списке при под кнопкой

вкладку в выпадающий список & «,» & «Деревья», то «граб», & » вОткроются стили. Выбираем любой. разрешается не так строке формул, а список здесь достаточно выпадающего списка. ДляОК занес список стран,Присваиваем каждому из списков
переходим во вкладку создания выпадающего списка, вводе первых буквВставить (Insert)Разработчик (Developer) (например, наименованиями товаров). newVal «дуб» и т.д. выпадающий список?», vbYesNo Для решения нашей уж и сложно. в варианте «=$A$2:$A$4». просто. Но сначала этого напишем такую, чтобы сохранить и которые хочу дать именованный диапазон, как «Файл» программы Excel,

является метод, основанный с клавиатуры(!), чегос вкладки. В более раннихЕсли у вас ExcelElse Вводим в поле

+ vbQuestion) If задачи дизайн не Вопрос в другом:Метод действительно хорош своей необходимо выполнить несколько формулу: закрыть диалоговое окно. пользователям на выбор это мы уже а затем кликаем на построении отдельного
Разработчик (Developer) версиях — панель 2003 или старшеTarget = newVal

«Источник» функцию вида lReply = vbYes имеет значения. Наличие
какой метод использовать? быстротой вставки значений. предварительных действий, чтобы=CHOOSE(VLOOKUP(B1,Sheet3!$A$3:$B$5,2,FALSE),England,France,Portugal)Имена диапазонам, содержащим города, в первом раскрывающемся делали ранее с по надписи «Параметры». списка данных. остальных способов. Приятным: инструментов — выберите вEnd If =ДВССЫЛ(E3). E3 – Then Range(«Деревья»).Cells(Range(«Деревья»).Rows.Count + заголовка (шапки) важно.

Если данных не Но в качестве в конечном итоге

=ВЫБОР(ВПР(B1;Sheet3!$A$3:$B$5;2;ЛОЖЬ);England;France;Portugal) можно присвоить точно списке, а в обычными выпадающими списками.В открывшемся окне переходимПрежде всего, делаем таблицу-заготовку, моментом, также, являетсяМеханизм добавления тот жеФормы (Forms) менюIf Len(newVal) = ячейка с именем 1, 1) = В нашем примере много, подойдет простейший самого главного недостатка вся процедура свеласьЧто же делает эта таким же образом. соседнем столбце указалВ первой ячейке создаём в подраздел «Настройка где собираемся использовать возможность настройки визуального — выбираем объектчерез менюВставка — Имя - 0 Then Target.ClearContents первого диапазона. Target End If это ячейка А1 вариант. Второй способ можно отметить невозможность только к выбору формула? Она ищетТеперь мы можем создать числовой индекс, который список точно таким ленты», и ставим выпадающее меню, а представления (цветов, шрифтов из списка иВид — Панели инструментов ПрисвоитьApplication.EnableEvents = TrueБывает, когда из раскрывающегося End If End со словом «Деревья». является более распространенным. использования диапазонов ячеек, способа ввода нужных значение из ячейки выпадающие списки в соответствует одному из же образом, как
флажок напротив значения
также делаем отдельным
и т.д.) рисуем его на — Формы (View(Insert — Name -End If списка необходимо выбрать If End Sub То есть нужно Рядовому пользователю он находящихся на других данных.B1
тех ячейках, где списков городов. Списки делали это ранее,

«Разработчик». Жмем на списком данные, которыеПри использовании этого способа, листе. А вот — Toolbars - Define),End Sub сразу несколько элементов.Сохраняем, установив тип файла выбрать стиль таблицы

подойдет лучше всего. листах, кроме активногоНачальный этап предусматривает доступв списке стран планировали выбирать данные. городов располагаются правее через проверку данных. кнопку «OK». в будущем включим
также возможно указывать дальше начинаются серьезные
Forms)
если Excel 2007
Не забываем менять диапазоны
Как в Excel сделать выпадающие списки: простейшие методы
Рассмотрим пути реализации «с поддержкой макросов». со строкой заголовка. Но если знания в данный момент. к функции, позволяющей и возвращает соответствующий Выделите ячейку в столбцахВо второй ячейке тожеПосле этого, на ленте в это меню. в качестве отличия от предыдущего. Если этой вкладки или новее - на «свои». Списки задачи.
Для чего нужны выпадающие списки?
Переходим на лист со Получаем следующий вид программы у юзераНаконец, еще один метод, выполнить данную процедуру. индекс, который затемB1D

запускаем окно проверки появляется вкладка с
- Эти данные можноListFillRange
- способа. не видно, то
откройте вкладку создаем классическим способом.Создаем стандартный список с списком. Вкладка «Разработчик» диапазона: не ограничиваются только позволяющий решить проблему Как сделать в использует функция(в ней мы,
Как в Excel сделать выпадающие списки: общая методика
данных, но в названием «Разработчик», куда размещать как нане только одномерныеВо-первых, созданный выпадающий ActiveX нажмите кнопкуФормулы (Formulas) А всю остальную помощью инструмента «Проверка — «Код» -Ставим курсор в ячейку, использованием простейших функций, того, как в Excel выпадающие спискиCHOOSE будем выбирать страну),F графе «Источник» вводим мы и перемещаемся.

этом же листе диапазоны. Можно, например список может находитсяОфис — Параметры Excelи воспользуйтесь кнопкой работу будут делать данных». Добавляем в «Макросы». Сочетание клавиш где будет находиться здесь оптимальным станет Excel сделать выпадающие на основе стандартных(ВЫБОР), чтобы выбрать
Выпадающий список в Excel 2007: ручной ввод данных
откройте вкладкуи функцию «=ДВССЫЛ» и Чертим в Microsoft документа, так и задать диапазон из в двух принципиально-Диспетчер имен (Name Manager) макросы. исходный код листа для быстрого вызова выпадающий список. Открываем создание именованных списков списки. Сначала создается возможностей программы? Очень 1-й, 2-й илиDataH адрес первой ячейки.
Источник из диапазона
Excel список, который на другом, если двух столбцов и разных состояниях -флажок, затемНа вкладке «Разработчик» находим готовый макрос. Как – Alt + параметры инструмента «Проверка с последующим вводом сам диапазон с просто. Сначала выбираем 3-й именованный диапазон.(Данные), нажмите. Так, например, рядом Например, =ДВССЫЛ($B3). должен стать выпадающим вы не хотите,

нескольких строк, указав режиме отладки, когдаОтображать вкладку Разработчик наСоздать инструмент «Вставить» – это делать, описано F8. Выбираем нужное данных» (выше описан диапазонов данных именно
Ввод именованных диапазонов (списков)
присвоением соответствующего имени. одну ячейку илиВот так будет выглядетьData Validation сКак видим, список создан. меню. Затем, кликаем чтобы обе таблице дополнительно, что выводить можно настраивать его ленте (Office Button. Введите имя (можно «ActiveX». Здесь нам выше. С его имя. Нажимаем «Выполнить». путь). В поле из них. Поскольку А после этого несколько, в которых наш второй раскрывающийся(Проверка данных), аFranceТеперь, чтобы и нижние на Ленте на располагались визуально вместе. нужно два столбца параметры и свойства, — Excel Options

Управление параметрами списка
любое, но обязательно нужна кнопка «Поле помощью справа отКогда мы введем в «Источник» прописываем такую в самом именованном в строке источника предполагается ввести какой-то список: затем в выпадающемстоит индекс ячейки приобрели те значок «Вставить», иВыделяем данные, которые планируем (свойство двигать его по — Show Developer без пробелов и со списком» (ориентируемся выпадающего списка будут пустую ячейку выпадающего функцию: списке изменение производится нужно будет просто диапазон значений, иВ результате мы получим меню выберите2 же свойства, как среди появившихся элементов занести в раскрывающийсяColumnCount листу и менять Tab in the начать с буквы!)
Заключение
на всплывающие подсказки). добавляться выбранные значения.Private списка новое наименование,Протестируем. Вот наша таблица быстро и просто. ввести его название, правым кликом вызываем два связанных (илиData Validation, который соответствует списку и в предыдущий в группе «Элемент список. Кликаем правой=2). Тогда можно получить размеры и - Ribbon) для выделенного диапазонаЩелкаем по значку – Sub Worksheet_Change(ByVal Target появится сообщение: «Добавить со списком на В общем, выбрать поставив перед ним контекстное меню. В зависимых) выпадающих списка.(Проверка данных). городов раз, выделяем верхние ActiveX» выбираем «Поле кнопкой мыши, и
весьма привлекательные результаты,
Выпадающий список в Excel с помощью инструментов или макросов
режиме ввода, когдаНайдите значок выпадающего списка (например становится активным «Режим As Range) On введенное имя баобаб одном листе:
для себя самый знак равенства. Такая нем используем сначала Если мы выбираемОткроется диалоговое окно2 ячейки, и при со списком». в контекстном меню окупающие все потраченные
Создание раскрывающегося списка
единственное, что можно среди элементов управленияТовары конструктора». Рисуем курсором Error Resume Next

в выпадающий список?».Добавим в таблицу новое удобный способ можно методика позволяет создавать
- раздел «Данные», затем страну

- Data Validation. Позже Вы увидите, нажатой клавише мышкиКликаем по месту, где

- выбираем пункт «Присвоить на дополнительные настройки — выбирать из форм (не ActiveX!).
). Нажмите (он становится «крестиком»)
If Not Intersect(Target,
Выпадающий список в Excel с подстановкой данных
Нажмем «Да» и добавиться значение «елка». без проблем. списки с использованием – пункт «ПроверкаFrance(Проверка вводимых значений). как этот индекс
- «протаскиваем» вниз. должна быть ячейка имя…». усилия:

- него данные. Переключение Ориентируйтесь по всплывающимОК небольшой прямоугольник – Range(«Е2:Е9»)) Is Nothing еще одна строкаТеперь удалим значение «береза».Автор: Панькова Оксана Владимировна данных и значений, данных». И переходим, в связанном спискеМы хотим дать пользователю будет использован.

- Всё, таблица создана. со списком. КакОткрывается форма создания имени.Способ 1. между этими режимами подсказкам -. место будущего списка.
And Target.Cells.Count = со значением «баобаб».Осуществить задуманное нам помогла

Под выпадающим списком понимается которые располагаются на

на вкладку параметров.

у нас будут на выбор списокЕсли Вы работаете в
Мы разобрались, как сделать видите, форма списка В поле «Имя»Примитивный происходит с помощьюПоле со спискомВыделите ячейки (можно сразу
- Жмем «Свойства» – открывается 1 Then Application.EnableEventsКогда значения для выпадающего «умная таблица», которая содержание в одной

- разных листах книги.В ней имеется условие города только из вариантов, поэтому в Excel 2010, то
- выпадающий список в появилась. вписываем любое удобноеСпособ 2. кнопки: несколько), в которых

- перечень настроек. = False If списка расположены на легка «расширяется», меняется. ячейке нескольких значений. И именно такой (критерий) проверки со Франции. поле можете создать лист-источник Экселе. В программеЗатем мы перемещаемся в наименование, по которомуСтандартныйРежим Конструктора (Design Mode)Щелкните по значку хотите получить выпадающийВписываем диапазон в строку Len(Target.Offset(0, 1)) = другом листе илиТеперь сделаем так, чтобы Когда пользователь щелкает вариант в основном строкой типа данных,Из этой статьи ВыAllow в отдельной рабочей можно создавать, как «Режим конструктора». Жмем будем узнавать данныйСпособ 3.на вкладке и нарисуйте небольшой
- список и выберите ListFillRange (руками). Ячейку,

- 0 Then Target.Offset(0, в другой книге, можно было вводить по стрелочке справа, применяется в версиях где и выбираем узнали, как можно(Тип данных) выберите
книге. Если же простые выпадающие списки, на кнопку «Свойства список. Но, этоЭлемент управленияРазработчик (Developer)
горизонтальный прямоугольник - в меню (на куда будет выводиться
Выпадающий список в Excel с данными с другого листа/файла
1) = Target стандартный способ не новые значения прямо появляется определенный перечень. редактора Excel 2007 значение «Список». В сделать простейшие связанныеList у Вас версия так и зависимые. элемента управления».
- наименование должно начинатьсяСпособ 4.:
- будущий список. вкладке) выбранное значение –
Else Target.End(xlToRight).Offset(0, 1) работает. Решить задачу в ячейку с Можно выбрать конкретное. и ниже, поскольку принципе, остальные поля выпадающие списки в(Список). Это активирует Excel 2003 года, При этом, можно
Как сделать зависимые выпадающие списки
Открывается окно свойств элемента

обязательно с буквы.Элемент ActiveXЕсли эта кнопка нажата,Щелкните по нарисованному спискуДанные — Проверка (Data в строку LinkedCell. = Target End можно с помощью
- этим списком. ИОчень удобный инструмент Excel эти модификации не

- пока можно оставить Microsoft Excel. Вы поле и Вы планируете использовать различные методы

- управления. В графе Можно также вписатьСложность то мы можем правой кнопкой мыши — Validation) Для изменения шрифта If Target.ClearContents Application.EnableEvents функции ДВССЫЛ: она данные автоматически добавлялись для проверки введенных умеют работать с без изменений. Чуть
Выбор нескольких значений из выпадающего списка Excel
можете взять этотSource использовать именованный диапазон, создания. Выбор зависит «ListFillRange» вручную через
- примечание, но этонизкая настраивать параметры выпадающего и выберите команду. Из выпадающего списка и размера – = True End сформирует правильную ссылку в диапазон. данных. Повысить комфорт диапазонами, в которых ниже расположена строка, простой пример и(Источник), где необходимо то значения должны от конкретного предназначения двоеточие прописываем диапазон не обязательно. Жмемсредняя списка, нажав соседнююФормат объекта (Format control)Тип данных (Allow) Font. If End Sub на внешний источникСформируем именованный диапазон. Путь:
- работы с данными указаны данные из в которой будет использовать его для указать имя диапазона находиться в той списка, целей его ячеек таблицы, данные на кнопку «OK».высокая кнопку. В появившемся диалоговомвыберите вариантСкачать пример выпадающего спискаЧтобы выбранные значения показывались информации. «Формулы» — «Диспетчер позволяют возможности выпадающих разных листов.
- вводиться источник значений. решения реальных задач. со странами. Введите же книге, можно создания, области применения,
которой будут формироватьПереходим во вкладку «Данные»
Возможность настройки шрифта, цветаСвойства (Properties)
окне задайтеСписок (List)При вводе первых букв снизу, вставляем другой
Делаем активной ячейку, куда
имен» — «Создать».
списков: подстановка данных,
Что касается управления списками,
Вот он-то насУрок подготовлен для Вас в этом поле
на другом листе. и т.д. пункты выпадающего списка.
программы Microsoft Excel.
и т.д.
, которая откроет окно
Формировать список по диапазонуи введите в
с клавиатуры высвечиваются
код обработчика.Private Sub
хотим поместить раскрывающийся
Вводим уникальное название отображение данных другого в редакторе Excel и интересует. командой сайта office-guru.ru «=Country» и жмите
Выпадающий список с поиском
- Мы будем использовать именованныеАвтор: Максим ТютюшевДалее, кликаем по ячейке, Выделяем область таблицы,нет со списком всех

- - выделите ячейки строчку подходящие элементы. И Worksheet_Change(ByVal Target As список. диапазона – ОК.

- листа или файла, есть множество нужных

- Начнем с ручного вводаИсточник: http://www.excel-user.com/2011/02/cascading-validation-lists.htmlОК диапазоны и сделаемПРЕДСТАВЬТЕ СИТУАЦИЮ: и в контекстном где собираемся применятьнет
возможных настроек для
с наименованиями товаров,Источник (Source) это далеко не Range) On ErrorОткрываем параметры проверки данных.Создаем раскрывающийся список в наличие функции поиска функций. Но самая, значений. Если говоритьПеревел: Антон Андронов
Выпадающий список в ячейке листа
Видео
. Теперь нам нужно так, чтобы этиМы хотим создать меню последовательно переходим
выпадающий список. Жмемда выделенного объекта: которые должны попасть
Способ 1. Примитивный
знак равенства и все приятные моменты Resume Next If В поле «Источник» любой ячейке. Как и зависимости. пожалуй, интересная – о том, какАвтор: Антон Андронов сделать второй раскрывающийся связанные выпадающие списки в Excel небольшую по пунктам «Объект на кнопку «ПроверкаКоличество отображаемых строкСамые нужные и полезные в список имя диапазона (т.е. данного инструмента. Здесь
Способ 2. Стандартный
- Not Intersect(Target, Range(«Н2:К2»)) вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”). это сделать, ужеПуть: меню «Данные» -
- распространение параметров для в Excel сделатьМногие пользователи стандартного офисного список, чтобы пользователи работали во всех табличку, где можно ComboBox» и «Edit». данных», расположенную навсегда 8 свойства, которые можноСвязь с ячейкой=Товары можно настраивать визуальное Is Nothing AndИмя файла, из которого известно. Источник – инструмент «Проверка данных» ввода с одинаковыми выпадающие списки таким пакета от Microsoft могли выбрать город. версиях Excel. Следующий выбрать страну иВыпадающий список в Microsoft Ленте.любое
- и нужно настроить:- укажите ячейку). представление информации, указывать Target.Cells.Count = 1 берется информация для имя диапазона: =деревья. — вкладка «Параметры». условиями. Применяется она методом, решение состоит явно недооценивают табличный Мы поместим этот шаг – создать соответствующий ей город. Excel готов.Открывается окно проверки вводимыхБыстрый поиск элемента поListFillRange куда нужно выводить
Нажмите в качестве источника Then Application.EnableEvents =
списка, заключено в
Снимаем галочки на вкладках Тип данных – в тех случаях, в том, чтобы редактор Excel. Выпадающий раскрывающийся список в именованные диапазоны для При этом сЧтобы сделать и другие значений. Во вкладке первым буквам- диапазон ячеек, порядковый номер выбранногоОК сразу два столбца. False If Len(Target.Offset(1, квадратные скобки. Этот «Сообщение для ввода»,
Способ 3. Элемент управления
«Список». когда на листе в вышеуказанном поле список в ячейке, ячейку наших списков. На помощью выпадающих списков, ячейки с выпадающим
- «Параметры» в поленет откуда берутся данные пользователем элемента..У кого мало времени 0)) = 0 файл должен быть «Сообщение об ошибке».Ввести значения, из которых имеется слишком много прописывать значения самостоятельно. экономящий время, затрачиваемоеB2 вкладке необходимо ограничить доступные списком, просто становимся «Тип данных» выбираемнет для списка. ВыделитьКоличество строк спискаВсе! Наслаждайтесь! и нужно быстро Then Target.Offset(1, 0)
- открыт. Если книга Если этого не будет складываться выпадающий проверок и их При этом стоит на ввод или. А теперь внимание
- с нужными значениями сделать, Excel не список, можно разными условия нужно поменять. обратить внимание, что
- поиск данных, в – фокус! Нам(Формулы) есть команда и городов, из край готовой ячейки,
- поле «Источник» ставимНеобходимость использования дополнительной функции не даст, надо показывать в выпадающем источника данных для смотрим обучающее видео: Target.End(xlDown).Offset(1, 0) = находится в другой
позволит нам вводить способами: Для этого в
для русскоязычной локализации нем создается за нужно проверить содержимоеName Manager которых они могут нажимаем кнопку мыши, знак равно, иИНДЕКС просто вписать его
Способ 4. Элемент ActiveX
списке. По умолчанию списка может выступатьКому интересны подробности и Target End If папке, нужно указывать новые значения.Вручную через «точку-с-запятой» в разделе проверки данных, вводить значения нужно пару секунд, в ячейки с названием(Диспетчер имён). Нажав выбирать. В первой
и протягиваем вниз. сразу без пробеловнет руками с клавиатуры — 8, но и динамический именованный нюансы всех описанных Target.ClearContents Application.EnableEvents =
путь полностью.Вызываем редактор Visual Basic. поле «Источник». который использовался во через точку с отличие от Access, страны (ячейка B1), на нее, откроется ячейке мы сделаемТакже, в программе Excel пишем имя списка,да (например, Лист2!A1:A5) можно больше, чего диапазон, например прайс-лист. способов — дальше True End IfВозьмем три именованных диапазона: Для этого щелкаемВвести значения заранее. А всех предыдущих методах,
запятой. Для английского где эта процедура чтобы получить индекс диалоговое окно выбор страны, а можно создавать связанные которое присвоили емунетLinkedCell не позволяет предыдущий
Тогда при дописывании по тексту. End Sub
- Это обязательное условие. Выше правой кнопкой мыши в качестве источника обращаемся к вкладке задания применяется только сложнее. Ниже будут соответствующий базе данныхName Manager во второй будут
- выпадающие списки. Это выше. Жмем наВозможность создания связанных выпадающих- связанная ячейка, способ.
- новых товаров кОдин щелчок правой кнопкойЧтобы выбираемые значения отображались
- описано, как сделать по названию листа указать диапазон ячеек «Сообщения для ввода». запятая. Неудобство такого
- приведены три основных с городами. Если(Диспетчер имён). доступны только принадлежащие такие списки, когда
кнопку «OK». списков куда будет выводитьсяПосле нажатия на прайсу, они будут мыши по пустой в одной ячейке, обычный список именованным и переходим по со списком. Тут можно ввести метода очевидно: это метода по созданию
пользователь выберетНажмите кнопку выбранной стране города. при выборе одногоВыпадающий список готов. Теперь,нет выбранный из спискаОК автоматически добавляться к ячейке под столбцом разделенные любым знаком диапазоном (с помощью вкладке «Исходный текст».Назначить имя для диапазона подсказку, которая отобразится занимает слишком много таких списков иPortugal
Для чего нужен выпадающий список в excel
Чтобы упростить работу пользователей с листом, добавьте в ячейки раскрывающиеся списки. Раскрывающиеся списки позволяют пользователям выбирать элементы из созданного вами списка.

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

Почему данные следует поместить в таблицу? Потому что в этом случае при добавлении и удалении элементов все раскрывающиеся списки, созданные на основе этой таблицы, будут обновляться автоматически. Дополнительные действия не требуются.
Выделите на листе ячейку, в которую требуется поместить раскрывающийся список.
Перейдите на вкладку Данные на ленте, а затем — Проверка данных.
Примечание: Если не удается выбрать пункт Проверка данных, лист может быть защищен или предоставлен к общему доступу. Разблокируйте определенные области защищенной книги или отмените общий доступ к листу, а затем повторите шаг 3.
На вкладке Параметры в поле Разрешить выберите Список.
Выберите в поле Источник , а затем выберите диапазон списка. В примере данные находятся на листе «Города» в диапазоне A2:A9. Обратите внимание на то, что строка заголовков отсутствует в диапазоне, так как она не является одним из вариантов, доступных для выбора.

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

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

Не знаете, какой вариант выбрать в поле Стиль ?
Чтобы отобразить сообщение, которое не мешает пользователям вводить данные, отсутствуют в раскрывающемся списке, выберите Сведения или Предупреждение. Сведения покажут сообщение с этим значком
, а предупреждение — сообщение с этим значком
.
Чтобы запретить пользователям вводить данные, которые отсутствуют в раскрывающемся списке, выберите Остановить.
Примечание: Если вы не добавили заголовок и текст, по умолчанию выводится заголовок «Microsoft Excel» и сообщение «Введенное значение неверно. Набор значений, которые могут быть введены в ячейку, ограничен».
После создания раскрывающегося списка убедитесь, что он работает правильно. Например, рекомендуется проверить, изменяется ли ширина столбцов и высота строк при отображении всех ваших записей.
Если список элементов для раскрывающегося списка находится на другом листе и вы хотите запретить пользователям его просмотр и изменение, скройте и защитите этот лист. Подробнее о защите листов см. в статье Блокировка ячеек.
Если вы решили изменить элементы раскрывающегося списка, см. статью Добавление и удаление элементов раскрывающегося списка.
Чтобы удалить раскрывающийся список, см. статью Удаление раскрывающегося списка.
Предлагаем скачать образец книги с несколькими примерами проверки данных, аналогичными примеру в этой статье. Вы можете воспользоваться ими или создать собственные сценарии проверки данных. Скачайте примеры проверки данных Excel.
Ввод данных станет быстрее и точнее, если ограничить значения в ячейке вариантами из раскрывающегося списка.
Сначала создайте на листе список допустимых элементов, а затем выполните сортировку или расположите их в нужном порядке. В дальнейшем эти элементы могут служить источником для раскрывающегося списка данных. Если список небольшой, на него можно легко ссылаться и вводить элементы прямо в средстве проверки данных.
Создайте список допустимых элементов для раскрывающегося списка. Для этого введите элементы на листе в одном столбце или строке без пустых ячеек.
Выделите ячейки, для которых нужно ограничить ввод данных.
На вкладке Данные в разделе Сервис выберите Проверка данных или Проверка.

Примечание: Если команда проверки недоступна, лист может быть защищен или книга может быть предоставлена для общего доступа. Если книга является общей или лист защищен, изменить параметры проверки данных невозможно. Дополнительные сведения о защите книги см. в статье Защита книги.
Перейдите на вкладку Параметры , а затем во всплывающем меню Разрешить выберите Список.
Выберите в поле Источник , а затем на листе выберите список допустимых записей.
Диалоговое окно свернется, чтобы было видно весь лист.
Нажмите клавишу RETURN или выберите Развернуть
, чтобы восстановить диалоговое окно, а затем нажмите кнопку ОК.
Можно также ввести значения, разделенные запятой, непосредственно в поле Источник .
Чтобы изменить список допустимых записей, просто измените значения в исходном списке или измените диапазон в поле Источник .
Можно указать собственное сообщение об ошибке, которое будет отображаться при вводе недопустимых данных. На вкладке Данные выберите Проверка данных или Проверить, а затем перейдите на вкладку Оповещение об ошибке .
См. также
На новом листе введите данные, которые должны отображаться в раскрывающемся списке. Желательно, чтобы элементы списка содержались в таблице Excel.
Почему данные следует поместить в таблицу? Если данные содержатся в таблице, то при добавлении или удалении элементов из списка все раскрывающиеся списки, основанные на этой таблице, автоматически обновляются. Дополнительные действия не требуются.
Выделите на листе ячейку, в которую требуется поместить раскрывающийся список.
Перейдите на вкладку Данные на ленте, а затем выберите Проверка данных.
На вкладке Параметры в поле Разрешить выберите Список.
Если вы уже создали таблицу с раскрывающимися записями, выберите в поле Источник , а затем выберите и перетащите ячейки, содержащие эти записи. Однако не включайте в него ячейку заголовка. Добавьте только ячейки, которые должны отображаться в раскрывающемся списке. Вы также можете просто ввести список записей в поле Источник , разделенный запятой следующим образом:
Фрукты;Овощи;Зерновые культуры;Молочные продукты;Перекусы
Если пользователи не могут оставить ячейку пустой, проверка пустое поле Игнорировать.
Установите флажок в раскрывающемся списке В ячейке .
Перейдите на вкладку Входное сообщение .
Если вы хотите, чтобы при выборе ячейки отображалось сообщение, проверка флажок Показать сообщение и введите заголовок и сообщение в полях (не более 225 символов). Если вы не хотите, чтобы сообщение отображалось, снимите этот флажок.
Перейдите на вкладку Оповещение об ошибке .
Если вы хотите, чтобы сообщение отображалось, когда кто-то вводит что-то, чего нет в вашем списке, проверка флажок Показывать оповещение, выберите параметр в поле Тип и введите название и сообщение. Если вы не хотите, чтобы сообщение отображалось, снимите этот флажок.
Нажмите кнопку ОК.
После создания раскрывающегося списка убедитесь, что он работает правильно. Например, рекомендуется проверить, изменяется ли ширина столбцов и высота строк при отображении всех ваших записей. Если вы решили изменить элементы раскрывающегося списка, см. статью Добавление и удаление элементов раскрывающегося списка. Чтобы удалить раскрывающийся список, см. статью Удаление раскрывающегося списка.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
5 способов создать выпадающий список в ячейке Excel
Одной из наиболее полезных функций при вводе данных является возможность использовать выпадающий список в Excel. Он позволяет выбирать значение из предварительно определенного перечня и разрешает вводить только те данные, которые соответствуют вашим требованиям. Мы предложим вам несколько простых способов, как создавать выпадающие списки в Excel. Более сложные способы, основанные на динамических диапазонах и использовании данных из других таблиц, мы также рассмотрим.
Зачем нужен выпадающий список?
Часто случается так, что в какой-то из колонок вашей таблицы нужно вводить одинаковые повторяющиеся значения. К примеру, фамилии сотрудников, названия товаров или делать выбор в ячейке Excel вида «да – нет». Что может случиться? Конечно, в первую очередь будут ошибки при вводе. Человеческий фактор ведь никто не отменял. Чем нам сие грозит? К примеру, когда мы решим подсчитать, сколько заказов выполнил каждый из менеджеров, то окажется, что фамилий больше, чем сотрудников. Далее придётся искать ошибки, исправлять их и вновь повторять расчет.
Ну и конечно же, все время руками вводить одни и те же слова – просто бессмысленная работа и потеря времени. Вот здесь-то выпадающие списки нам и пригодятся. При нажатии выпадает перечень заранее определённых значений, из которых просто необходимо указать какое-то одно.
Важно то, что вы теперь будете не писать, а выбирать их с помощью мыши или клавиатуры. Это значительно ускоряет работу, а также гарантирует защиту от случайных ошибок. Проверка того, что мы записали в таблицу, теперь уже не нужна.
1 — Самый быстрый способ создать выпадающий список.
Как проще всего добавить выпадающий список в таблицу Excel? Всего один щелчок правой кнопкой мыши по пустой клетке под столбцом с данными, затем команда контекстного меню «Выберите из раскрывающегося списка» (Choose from drop-down list). А можно просто стать в нужное место и нажать сочетание клавиш Alt+стрелка вниз . Появится отсортированный перечень уникальных ранее введенных значений.
Способ не работает, если нашу ячейку и столбец с записями отделяет хотя бы одна пустая строка или вы хотите ввести то, что еще не вводилось выше. На нашем примере это хорошо видно.

2 — Используем меню.
Давайте рассмотрим небольшой пример, в котором нам нужно постоянно вводить в таблицу одни и те же наименования товаров. Выпишите в столбик данные, которые мы будем использовать (например, названия товаров). В нашем примере — в диапазон G2:G7.
Выделите ячейку таблицы (можно сразу несколько), в которых хотите использовать ввод из заранее определенного перечня. Там мы разместим наш выпадающий список.

Бонусом здесь идет возможность задать подсказку и сообщение об ошибке, если автоматически вставленное значение вы захотите изменить вручную. Для этого существуют вкладки Подсказка по вводу (Input Message) и Сообщение об ошибке (Error Alert).
В качестве источника значений для выпадающего списка в Excel можно использовать также и именованный диапазон.

К примеру, диапазону I2:I13, содержащему названия месяцев, можно присвоить наименование «месяцы». Затем имя можно ввести в поле «Источник».

Кроме того, и источник и в виде обычного диапазона ячеек, и именованный диапазон могут находиться на других листах вашей рабочей книги.
Но вы можете и не использовать диапазоны или ссылки, а просто определить возможные варианты прямо в поле «Источник». К примеру, чтобы реализовать в таблице Excel простейший выбор «да – нет», вы можете вписать туда –
Используйте для разделения значений точку с запятой, запятую, либо другой символ, установленный у вас в качестве разделителя элементов. (Смотрите Панель управления — Часы и регион — Форматы — Дополнительно — Числа.)
3 — Создаем элемент управления.
Вставим на лист новый объект – элемент управления «Поле со списком» с последующей привязкой его к данным на листе Excel. Делаем:
- Откройте вкладку Разработчик (Developer). Если её не видно, то в Excel 2007 нужно нажать кнопку Офис – Параметры – флажок Отображать вкладку Разработчик на ленте (Office Button – Options – Show Developer Tab in the Ribbon) или в версии 2010–2013 и выше щелкните правой кнопкой мыши по ленте, выберите команду Настройка ленты (Customize Ribbon) и включите отображение вкладки Разработчик (Developer) с помощью флажка.
- Найдите нужный значок среди элементов управления (см.рисунок ниже).

Но нам ведь нужен не этот номер, а соответствующее ему слово. Используем функцию ИНДЕКС (INDEX в английском варианте). Она позволяет найти в списке значений одно из них соответственно его порядковому номеру. В качестве аргументов ИНДЕКС укажите диапазон ячеек (F5:F11) и адрес с полученным порядковым номером (F2).
Формулу в F3 запишем, как показано на рисунке:
Как и в предыдущем способе, здесь возможны ссылки на другие листы, на именованные диапазоны.
Обратите также внимание, что здесь мы не привязаны ни к какой конкретному месту таблицы. Таким списком Excel удобно пользоваться, поскольку его можно свободно «перетаскивать» мышкой в любое удобное место. Для этого на вкладке «Разработчик» нужно активизировать режим конструктора.
4 — Элемент ActiveX
Действуем аналогично предыдущему способу, но выбираем иконку чуть ниже — из раздела «Элементы ActiveX».

Определяем перечень допустимых значений (1). Обратите внимание, что здесь для показа можно выбирать сразу несколько колонок. Затем выбираем адрес, по которому будет вставлена нужная позиция из перечня (2).Указываем количество столбцов, которые будут использованы как исходные данные (3), и номер столбца, из которого будет происходить выбор для вставки на лист (4). Если укажете номер столбца 2, то в А5 будет вставлена не фамилия, а должность. Можно также указать количество строк, которое будет выведено в перечне. По умолчанию — 8. Остальные можно прокручивать мышкой (5).
Этот способ сложнее предыдущего, но зато возвращает сразу значение, а не его номер. Поэтому необходимость в промежуточной ячейке и обработке ее при помощи ИНДЕКС — отпадает. Думаю, таким списком пользоваться гораздо удобнее.
5 — Выпадающий список в Excel с автозаполнением
Задача: Создать перечень, в который будут автоматически добавляться значения из заданного динамического диапазона. Если в любую ячейку этого диапазона будут внесены изменения, то сразу же изменится и набор предлагаемых к выбору значений. Никакие формулы и настройки здесь корректировать не нужно.
Вот как автозаполнение может выглядеть на простом примере:
Способ 1. Укажите заведомо большой источник значений для списка.
Самая простая и несложная хитрость. В начале действуем по обычному алгоритму действий: в меню выбираем на вкладке Данные – Проверка . (Data – Validation). Из перечня Тип данных (Allow) выберите вариант Список (List). Поставьте курсор в поле Источник (Source). Зарезервируем в списке набор с большим запасом: например, до 55-й строки, хотя занято у нас только 7. Обязательно не забудьте поставить галочку в чекбоксе «Игнорировать пустые . «. Тогда ваш «резерв» из пустых значений не будет вам мешать.

Действительно самый простой способ, но не слишком удобный. Ведь зарезервированное место может и закончиться.
Конечно, в качестве источника можно указать и весь столбец:
Но обработка такого большого количества ячеек может несколько замедлить вычисления. Особенно в больших таблицах Excel.
Способ 2. Применяем именованный диапазон.
Именованный диапазон отличается от обычного тем, что ему присвоено определенное наименование. С ним гораздо проще работать, так как не нужно вводить ссылку, а достаточно просто указать его имя. Давайте рассмотрим небольшой пример.
В столбце А находятся имена сотрудников, которые мы будем вводить. Перечень может быть сколь угодно длинным. Нам необходимо, чтобы каждая новая запись включалась в раскрывающийся список без всяких дополнительных действий с нашей стороны.
Выделим имеющийся в нашем распоряжении перечень имен A2:A10. Затем присвоим ему название, заполнив поле «Имя», находящееся левее строки формул.
Так вы присвоите какое-то имя этому диапазону Excel.
Создадим в С2 перечень значений. В качестве источника для него укажем выражение
Недостатком работы с таким выпадающим списком в Excel является то, что новые значения нельзя просто дописывать в конец используемого перечня. Они останутся за пределами именованного диапазона. Если что-то нужно добавить, то их придется вставлять внутрь диапазона, использовав вставку пустой строки.
Перечень ещё можно отсортировать, чтобы удобно было пользоваться.
Главное неудобство пользования таким списком заключается в том, что используемый нами диапазон — статический. Автоматически его размеры измениться не могут. Согласитесь, не слишком удобный и технологичный способ. Слишком много ручных операций.
А теперь давайте пойдем дальше и посмотрим, как можно работать с динамическим диапазоном, который автоматически подстраивается под вводимые значения.
Способ 3. Выпадающий список на основе «умной» таблицы Excel.
Начиная с 2007 года таблица для Excel — уже не просто набор строк и столбцов. Если вы просто расположите показатели с привычном для нас табличном виде, то он не будет считать их таблицей. Существует специальное форматирование, после чего диапазон начинает вести себя как единое целое, приобретая целый ряд интересных свойств. В частности, он начинает сам отслеживать свои размеры, динамически изменяясь при корректировке данных.
Любой набор значений в таблице может быть таким образом преобразован. Например, A1:A8. Выделите их мышкой. Затем преобразуйте в таблицу, используя меню Главная — Форматировать как таблицу (Home — Format as Table). Укажите, что в первой строке у вас находится название столбца. Это будет «шапка» вашей таблицы. Внешний вид может быть любым: это не более чем внешнее оформление и ни на что больше оно не влияет.
Как уже было сказано выше, «умная» таблица хороша для нас тем, что динамически меняет свои размеры при добавлении в нее информации. Если в строку ниже нее вписать что-либо, то она тут же присоединит к себе её. Таким образом, новые значения можно просто дописывать. К примеру, впишите в A9 слово «кокос», и таблица тут же расширится до 9 строк.
Следовательно, автоматическое обновление набора используемой информации в списке можно организовать, если использовать содержимое какого-либо столбца «умной» таблицы.
Осталось только обозначить ее как источник. Проблема заключается в том, что программа в качестве источника в списке не понимает выражение вида
и не считает его формулой. Хотя в обычных выражениях на листе вашей рабочей книги это вполне будет работать. Эта конструкция обозначает ссылку на первый столбец. Но в поле «Источник» она почему-то игнорируется.
Чтобы использовать «умную таблицу» как источник, нам придется пойти на небольшую хитрость и воспользоваться функцией ДВССЫЛ (INDIRECT в английском варианте). Эта функция преобразует текстовую переменную в обычную ссылку.

Формула теперь будет выглядеть следующим образом:
Таблица5 — имя, автоматически присвоенное «умной таблице». У вас оно может быть другим. На вкладке меню Конструктор (Design) можно изменить стандартное имя на свое (но без пробелов!). По нему мы сможем потом адресоваться к нашей таблице на любом листе книги.
«Продукт» — название нашего первого и единственного столбца, присвоено по его заголовку.
Не забудьте также заключить все выражение в кавычки, чтобы обозначить его как текстовую переменную.
Теперь если в A9 вы допишете еще один фрукт (например, кокос), то он тут же автоматически появится и в нашем перечне. Аналогично будет, если мы что-то удалим. Задача автоматического увеличения выпадающего списка значений решена.
Надеемся, вы сможете теперь с помощью списков без ошибок вводить часто повторяющиеся данные в таблицу Excel при помощи выпадающего списка.
А вот еще полезная для вас информация:
Как сделать выпадающий список с выбором нескольких значений — В статье показано, как создать выпадающий список в Excel, который позволяет пользователям выбирать несколько значений с дубликатами или без них. Также рассмотрим, как разрешить множественный выбор только в отдельных строках, столбцах или ячейках, как это…
Как сделать зависимый выпадающий список в Excel? — Одной из наиболее полезных функций проверки данных является возможность создания выпадающего списка, который позволяет выбирать значение из предварительно определенного перечня. Но как только вы начнете применять это в своих таблицах,…
alt=»использование формул для выпадающего списка» width=»141″ height=»150″ /> Создаем выпадающий список в Excel при помощи формул — Задача: Создать выпадающий список в Excel таким образом, чтобы в него автоматически попадали все новые значения. Сделаем это при помощи формул, чтобы этот способ можно было использовать не только в…
Выпадающий список в Excel
Элемент списка знаком нам по формам на сайтах. Удобно выбирать уже готовые значения. Например, никто не вводит месяц вручную, его берут из такого перечня. Заполнить выпадающий список в Excel можно с использованием различных инструментов. В статье рассмотрим каждый из них.
Как сделать выпадающий список в Excel
Как сделать выпадающий список в Excel 2010 или 2016 с помощью одной командой на панели инструментов? На вкладке «Данные» в разделе «Работа с данными» найдите кнопку «Проверка данных». Нажмите на нее и выберите первый пункт.
Откроется окно. Во вкладке «Параметры» в выпадающем разделе «Тип данных» выберите «Список».
Снизу появится строка для указания источников.
Указывать информацию можно по-разному.
- Ручной ввод
Введите перечень через точку с запятой.
- Выбор диапазона значений с листа Excel
Для этого начните выделять ячейки мышью.
Как отпустите – окно снова станет нормальным, а в строке появятся адреса.
- Создание выпадающего списка в Excel с подстановкой данных
Сначала назначим имя. Для этого создайте на любом листе такую таблицу.
Выделите ее и нажмите правую кнопку мыши. Щелкните по команде «Присвоить имя».
Введите имя в строку сверху.
Вызовите окно «Проверка данных» и в поле «Источник» укажите имя, поставив перед ним знак «=».
В любом из трех случаев Вы увидите нужный элемент. Выбор значения из выпадающего списка Excel происходит с помощью мыши. Нажмите на него и появится перечень указанных данных.
Вы узнали, как создать выпадающий список в ячейке Excel. Но можно сделать и больше.
Подстановка динамических данных Excel
Если Вы добавите какое-то значение в диапазон данных, которые подставляются в перечень, то в нем изменения не произойдет, пока вручную не будут указаны новые адреса. Чтобы связать диапазон и активный элемент, необходимо оформить первый как таблицу. Создайте вот такой массив.
Выделите его и на вкладке «Главная» выберите любой стиль таблицы.
Обязательно поставьте галочку внизу.
Вы получите такое оформление.
Создайте активный элемент, как было описано выше. В качестве источника введите формулу

Чтобы узнать имя таблицы, перейдите на вкладку «Конструктор» и посмотрите его. Можете поменять имя на любое другое.
Функция ДВССЫЛ создает ссылку на ячейку или диапазон. Теперь ваш элемент в ячейке привязан к массиву данных.
Попробуем увеличить количество городов.
Обратная процедура — подстановка данных из выпадающего списка в таблицу Excel, работает очень просто. В ячейку, куда надо вставить выбранное значение из таблицы, введите формулу:
Например, если перечень данных находится в ячейке D1, то в ячейке, куда будут выведены выбранные результаты введите формулу

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

Ненужный элемент исчезнет.
Зависимые элементы
Иногда в Excel встречается необходимость создания нескольких перечней, когда один зависит от другого. Например, каждый город имеет несколько адресов. При выборе в первом мы должны получить только адреса выбранного населенного пункта.
В этом случае дайте имя каждому столбцу. Выделите без первой ячейки (названия) и нажмите правую кнопку мыши. Выберите «Присвоить имя».
Это будет название города.
При именовании Санкт-Петербурга и Нижнего Новгорода Вы получите ошибку, так как имя не может содержать пробелов, символов подчеркивания, специальных символов и т.д.
Поэтому переименуем эти города, поставив нижнее подчеркивание.
Первый элемент в ячейке A9 создаем обычным образом.
А во втором пропишем формулу:
Сначала Вы увидите сообщение об ошибке. Соглашайтесь.
Проблема в отсутствии выбранного значения. Как только в первом перечне будет выбран город, второй заработает. 
Как настроить зависимые выпадающие списки в Excel с поиском
Можно использовать динамический диапазон данных для второго элемента. Это удобнее, если количество адресов будет расти.
Создадим выпадающий перечень городов. Оранжевым выделен именованный диапазон.
Для второго перечня нужно ввести формулу:
Функция СМЕЩ возвращает ссылку на диапазон, который смещен относительно первой ячейки на определенное число строк и столбцов:=СМЕЩ(начало; вниз; вправо; размер_в_строках; размер_в_столбцах)
ПОИСКПОЗ возвращает номер ячейки с выбранным в первом списке (E6) городом в указанной области SA:$A.
СЧЕТЕСЛИ считает количество совпадений в диапазоне со значением в указанной ячейке (E6).
Мы получили связанные выпадающие списки в Excel с условием на совпадение и поиском диапазона для него.
Мультивыбор
Часто нам необходимо получить несколько значений из набора данных. Можно вывести их в разные ячейки, а можно объединить в одну. В любом случае необходим макрос.
Нажмите на ярлыке листа внизу правую кнопку мыши и выберите команду «Просмотреть код».
Откроется окно разработчика. В него надо вставить следующий алгоритм.
Обратите внимание, что в строке
Следует проставить адрес ячейки со списком. У нас это будет E7.
Вернитесь на лист Excel и создайте в ячейке E7 список.
При выборе значения будут появляться под ним.
Следующий код позволит накапливать значения в ячейке.
Как только Вы переведете указатель на другую ячейку, Вы увидите перечень выбранных городов. Для создания объединенных ячеек в Excel прочитайте эту статью.
Мы рассказали, как добавить и изменить выпадающий список в ячейку Excel. Надеемся, эта информация поможет вам.