Microsoft excel: выпадающие списки

Правильное введение данных

Алгоритм, как сделать выпадающий список в «Excel» рассмотрели, но следует обратить внимание на ряд дополнительных моментов, корректного внесения информации:

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

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

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

Если у Вас остались вопросы по теме «Что такое выпадающий список в Excel и как его создать?», то можете задать их в комментария

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

В основе создания связанных выпадающих списков лежит применение функции ДВССЫЛ, которая позволяет преобразовывать текст из ячейки в ссылку.
Другими словами, если в ячейку введено текстовое значение «А1», то функция ДВССЫЛ вернет ссылку на ячейку А1.
Теперь зададим имена диапазонам состоящим из всех видов блюд каждой конкретной категории.
Для этого в панели вкладок выбираем Формулы -> Определенные имена -> Присвоить имя:

Выделяем диапазон ячеек A2:A6 и создаем диапазон с именем Пицца, аналогичные действия повторяем и для списков с суши (имя диапазона — Суши) и пастой (имя диапазона — Паста):

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

В панели вкладок выбираем Данные -> Работа с данными -> Проверка данных, указываем тип данных Список и в качестве источника выделяем диапазон A1:C1:

Создадим первый выпадающий список в ячейке A10, состоящий из категорий блюд (Пицца, Суши и Паста). В панели вкладок выбираем Данные -> Работа с данными -> Проверка данных, указываем тип данных Список и в качестве источника выделяем диапазон A1:C1:

Теперь создаем второй выпадающий список, полностью повторяем действия с созданием первого списка, только в поле Источник записываем формулу =ДВССЫЛ(A10):

Имена созданных диапазонов обязательно должны совпадать с элементами первого списка, поэтому если в первом списке есть категории содержащие пробелы, то при обращении к имени диапазона необходимо заменить пробелы на нижние подчеркивания.
Это можно осуществить с помощью функции ПОДСТАВИТЬ, которая позволяет заменить старый текст (пробел) на новый текст (нижнее подчеркивание) в текстовой строке, т.е. в нашем случае формула примет вид =ДВССЫЛ(ПОДСТАВИТЬ(A10;» «;»_»)).
Также минусом данного способа создания списков является невозможность использования динамических именованных диапазонов.

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

Удачи вам и до скорой встречи на страницах блога Tutorexcel.ru!

Microsoft Excel: выпадающие списки

Создание дополнительного списка

​ двоеточие прописываем диапазон​ а именно с​ В поле «Имя»​При работе в программе​ нужно два столбца​ просто вписать его​

​ листе. А вот​ которые должны попасть​В Excel 2007/2010 откройте​Данные — Проверка (Data​ столбец с данными​ получилась такая.​ данные столбца А​ устанавливать выпадающий список.​Название этой таблицы можно​. Ещё один способ​ делали ранее с​ ячеек таблицы, данные​ использованием ActiveX. По​ вписываем любое удобное​ Microsoft Excel в​

​ (свойство​ руками с клавиатуры​ дальше начинаются серьезные​ в список​ вкладку​ — Validation)​ отделяет хотя бы​

​=ДВССЫЛ(«Товар»)​ (без названия столбца).​ Мы выделили диапазон​ поменять. Нажимаем на​ сделать раскрывающиеся списки,​ обычными выпадающими списками.​ которой будут формировать​ умолчанию, функции инструментов​ наименование, по которому​ таблицах с повторяющимися​ColumnCount​ (например, Лист2!A1:A5)​

​ отличия от предыдущего​Связь с ячейкой​Разработчик (Developer)​. Из выпадающего списка​ одна пустая строка​Нажимаем «ОК». Получилось так.​ У нас -​ Е1:Е4 (окрашен в​

​ таблицу, заходим на​ смотрите в статье​В первой ячейке создаём​ пункты выпадающего списка.​ разработчика отсутствуют, поэтому​ будем узнавать данный​ данными, очень удобно​=2). Тогда можно получить​LinkedCell​ способа.​- укажите ячейку​. В более ранних​

​Тип данных (Allow)​ или вам нужен​Можно выпадающие списки сделать​ это диапазон А2:А4.​ желтый цвет). Вставляем​ закладку «Конструктор» и​ «Связанные выпадающие списки​ список точно таким​Далее, кликаем по ячейке,​

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

​ нам, прежде всего,​ список. Но, это​ использовать выпадающий список.​ весьма привлекательные результаты,​- связанная ячейка,​Во-первых, созданный выпадающий ActiveX​ куда нужно выводить​ версиях — панель​выберите вариант​ товар, который еще​ на другом листе,​Копируем формулу выделенного​ выпадающий список как​ в разделе «Свойства»​

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

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

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

​ в двух принципиально​ пользователем элемента.​Формы (Forms)​и введите в​

​ вводился выше:​ перед адресом ячейки​ ячейки клавишей «Esc».​ данных», выбираем «Список».​ таблицы. Мы написали​ сейчас рассмотрим удобен​ через проверку данных.​

​ по пунктам «Объект​ переходим во вкладку​ Можно также вписать​ нужные параметры из​ усилия:​

​ элемент​ разных состояниях -​

​Количество строк списка​через меню​ строчку​Выделите ячейки с данными,​ напишем название листа​ Выделяем ячейки, в​ В диалоговом окне​

Связанные списки

​ имя таблицы –​ тем, что при​Во второй ячейке тоже​ ComboBox» и «Edit».​ «Файл» программы Excel,​ примечание, но это​ сформированного меню. Давайте​Способ 1.​ListRows​ режиме отладки, когда​- сколько строк​Вид — Панели инструментов​Источник (Source)​ которые должны попасть​ и поставим восклицательный​ которых будем создавать​ «Проверка вводимых значений»​

​ «Товар».​ добавлении строк или​ запускаем окно проверки​Выпадающий список в Microsoft​ а затем кликаем​ не обязательно. Жмем​

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

​ — Формы (View​знак равенства и​ в выпадающий список​ знак.​ выпадающие списки второго​

​ в строку «Источник»​В этой таблице уже​ столбцов в таблицу,​ данных, но в​ Excel готов.​ по надписи «Параметры».​ на кнопку «OK».​

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

​Способ 2.​ строк​ параметры и свойства,​ списке. По умолчанию​ — Toolbars -​ имя диапазона (т.е.​ (например, наименованиями товаров).​Как еще можно​

​ уровня. У нас​

​ вставляем скопированную формулу​ все столбцы имеют​ все диапазоны в​ графе «Источник» вводим​Чтобы сделать и другие​В открывшемся окне переходим​Переходим во вкладку «Данные»​ способами.​Стандартный​Font​ двигать его по​ — 8, но​ Forms)​

​=Товары​

lumpics.ru>

B. Ввод элементов списка в диапазон (на любом листе)

В правилах Проверки данных (также как и Условного форматирования) нельзя впрямую указать ссылку на диапазоны другого листа (см. Файл примера ):

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

а диапазон с перечнем элементов разместим на другом листе (на листе Список в файле примера ).

Для создания выпадающего списка, элементы которого расположены на другом листе, можно использовать два подхода. Один основан на использовании Именованного диапазона, другой – функции ДВССЫЛ() .

Используем именованный диапазон Создадим Именованный диапазон Список_элементов, содержащий перечень элементов выпадающего списка (ячейки A1:A4 на листе Список). Для этого:

• выделяем А1:А4,
• нажимаем Формулы/ Определенные имена/ Присвоить имя
• в поле Имя вводим Список_элементов, в поле Область выбираем Книга;

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

• вызываем Проверку данных;
• в поле Источник вводим ссылку на созданное имя: =Список_элементов .

Примечание Если предполагается, что перечень элементов будет дополняться, то можно сразу выделить диапазон большего размера, например, А1:А10. Однако, в этом случае Выпадающий список может содержать пустые строки.

Избавиться от пустых строк и учесть новые элементы перечня позволяет Динамический диапазон. Для этого при создании Имени Список_элементов в поле Диапазон необходимо записать формулу = СМЕЩ(Список!$A$1;;;СЧЁТЗ(Список!$A:$A))

Использование функции СЧЁТЗ() предполагает, что заполнение диапазона ячеек (A:A), который содержит элементы, ведется без пропусков строк (см. файл примера , лист Динамический диапазон).

Используем функцию ДВССЫЛ()

Альтернативным способом ссылки на перечень элементов, расположенных на другом листе, является использование функции ДВССЫЛ() . На листе Пример, выделяем диапазон ячеек, которые будут содержать выпадающий список, вызываем Проверку данных, в Источнике указываем =ДВССЫЛ(«список!A1:A4») .

Недостаток: при переименовании листа – формула перестает работать. Как это можно частично обойти см. в статье Определяем имя листа.

Ввод элементов списка в диапазон ячеек, находящегося в другой книге

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

• в книге Источник.xlsx создайте необходимый перечень элементов;
• в книге Источник.xlsx диапазону ячеек содержащему перечень элементов присвойте Имя, например СписокВнеш;
• откройте книгу, в которой предполагается разместить ячейки с выпадающим списком;
• выделите нужный диапазон ячеек, вызовите инструмент Проверка данных, в поле Источник укажите = ДВССЫЛ(«лист1!СписокВнеш») ;

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

Если нет желания присваивать имя диапазону в файле Источник.xlsx, то формулу нужно изменить на = ДВССЫЛ(«лист1!$A$1:$A$4»)

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

Примечание : Если выпадающий список содержит более 25-30 значений, то работать с ним становится неудобно. Выпадающий список одновременно отображает только 8 элементов, а чтобы увидеть остальные, нужно пользоваться полосой прокрутки, что не всегда удобно.

В EXCEL не предусмотрена регулировка размера шрифта Выпадающего списка. При большом количестве элементов имеет смысл сортировать список элементов и использовать дополнительную классификацию элементов (т.е. один выпадающий список разбить на 2 и более).

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

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

ПРЕДСТАВЬТЕ СИТУАЦИЮ: Мы хотим создать в Excel небольшую табличку, где можно выбрать страну и соответствующий ей город. При этом с помощью выпадающих списков, необходимо ограничить доступные пользователям варианты стран и городов, из которых они могут выбирать. В первой ячейке мы сделаем выбор страны, а во второй будут доступны только принадлежащие выбранной стране города. Думаю, это понятно?

Итак, давайте начнём наш простой пример с того, как можно создать связанный (или зависимый) выпадающий список в Excel? В ячейке B1 мы будем выбирать страну, а в ячейке B2 – принадлежащий ей город, как на примере:

Для начала нужно создать базу данных. На втором листе я занес список стран, которые хочу дать пользователям на выбор в первом раскрывающемся списке, а в соседнем столбце указал числовой индекс, который соответствует одному из списков городов. Списки городов располагаются правее в столбцах D, F и H. Так, например, рядом с France стоит индекс 2, который соответствует списку городов 2. Позже Вы увидите, как этот индекс будет использован.

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

Мы будем использовать именованные диапазоны и сделаем так, чтобы эти связанные выпадающие списки работали во всех версиях Excel. Следующий шаг – создать именованные диапазоны для наших списков. На вкладке Formulas (Формулы) есть команда Name Manager (Диспетчер имён). Нажав на нее, откроется диалоговое окно Name Manager (Диспетчер имён).

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

В поле Name (Имя) введите имя Country для нашего первого именованного диапазона, а в поле Refers to (Диапазон) выберите тот, в котором хранится список стран:

Нажмите ОК, чтобы сохранить и закрыть диалоговое окно.

Имена диапазонам, содержащим города, можно присвоить точно таким же образом.

Теперь мы можем создать выпадающие списки в тех ячейках, где планировали выбирать данные. Выделите ячейку B1 (в ней мы будем выбирать страну), откройте вкладку Data (Данные), нажмите Data Validation (Проверка данных), а затем в выпадающем меню выберите Data Validation (Проверка данных).

Откроется диалоговое окно Data Validation (Проверка вводимых значений).

Мы хотим дать пользователю на выбор список вариантов, поэтому в поле Allow (Тип данных) выберите List (Список). Это активирует поле Source (Источник), где необходимо указать имя диапазона со странами. Введите в этом поле “=Country” и жмите ОК. Теперь нам нужно сделать второй раскрывающийся список, чтобы пользователи могли выбрать город. Мы поместим этот раскрывающийся список в ячейку B2

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

Мы воспользуемся функцией ВПР (VLOOKUP) для поиска значения из ячейки B1 в таблице с названиями стран. После того как индекс будет известен, мы выберем список, который станет источником данных для нашего второго выпадающего списка. Для этого напишем такую формулу:

Что же делает эта формула? Она ищет значение из ячейки B1 в списке стран и возвращает соответствующий индекс, который затем использует функция CHOOSE (ВЫБОР), чтобы выбрать 1-й, 2-й или 3-й именованный диапазон.

Вот так будет выглядеть наш второй раскрывающийся список:

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

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

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

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

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

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

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

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

Задаем дополнительные параметры выпадающих списков

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

Во второй вкладке аналогичным образом можно создать предупреждение, которое будет показано при неверном вводе. Кроме того, есть возможность выбрать вид ошибки:

• «останов» — программа сообщит об ошибке и не разрешит ввести ошибочные данные;
• «предупреждение» — программа сообщит об ошибке, но пользователь сможет сам решить, сохранять или нет эту информацию;
• «сообщение» — стандартное сообщение.

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

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

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

Как создать выпадающий список с гиперссылками в Excel?

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

Создать раскрывающийся список с гиперссылками с помощью формулы

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

После создания раскрывающегося списка введите следующую формулу: = ГИПЕРССЫЛКА (D2, «Перейти на веб-сайт!») (D2 — ячейка раскрывающегося списка) в ячейке E2, которая находится рядом с ячейкой раскрывающегося списка, и нажмите Enter Затем, когда вы выберете одну гиперссылку URL из раскрывающегося списка и щелкните ячейку с формулой, гиперссылка будет открыта.

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

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

Сначала создайте два диапазона имен для списка гиперссылок и связанной ячейки.

1. Дайте вашему списку гиперссылок название диапазона «Гиперссылки»И назовите пустую ячейку«Linked_cell», Которая является связанной ячейкой значения поля со списком в Имя Box, смотрите скриншоты:

Во-вторых, создайте поле со списком и отформатируйте элемент управления.

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

Советы: Если нет разработчик вкладка в вашей ленте, прочтите эту статью Как отобразить вкладку разработчика в ленте Excel 2007/2010/2013? для его активации.

3. После вставки поля со списком щелкните его правой кнопкой мыши и выберите Управление форматом, В Управление форматом диалоговое окно, нажмите Control
вкладка и введите Гиперссылки и Linked_cell которые представляют собой имена диапазонов, которые вы создали на шаге 1 в Диапазон ввода и Сотовая ссылка текстовые поля отдельно, см. снимок экрана:

4. Затем нажмите OK кнопку, чтобы завершить настройки, щелкните правой кнопкой мыши поле со списком и выберите Назначить макрос из контекстного меню в Назначить макрос диалоговое окно, нажмите Создать кнопку, см. снимок экрана:

5. В выскочившем Microsoft Visual Basic для приложений окна, скопируйте и вставьте следующий код между скриптами, см. снимок экрана:

Код VBA: активируйте гиперссылки из поля со списком:

HyperLink_Index = Range("Linked_cell")
      If Range("HyperLinks").Offset(HyperLink_Index - 1, 0).Hyperlinks(1).Name <> "" Then
           Range("HyperLinks").Offset(HyperLink_Index - 1, 0).Hyperlinks(1).Follow NewWindow:=False, AddHistory:=True
    End If

6. Затем сохраните этот код, и теперь, когда вы выберете одну гиперссылку из поля со списком, гиперссылка откроется автоматически.

Демонстрация: создание раскрывающегося списка с гиперссылками в Excel

Kutools for Excel: с более чем 300 удобными надстройками Excel, которые можно попробовать бесплатно без ограничений в течение 30 дней. Загрузите и бесплатную пробную версию прямо сейчас!

Раскрывающийся список с подстановкой данных

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

1. Выделите левой кнопкой мышки диапазон для списка (в данном примере это будет перечень деревьев), затем откройте вкладку «Главная» и выберите меню «Форматировать как таблицу».

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

Выбираем любой понравившийся стиль
Далее появится окно подтверждения, цель которого – убедиться в правильности введённого диапазона

Здесь важно установить галочку возле «Таблица с заголовками», так как наличие заголовка в данном случае играет ключевую роль

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

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

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

В поле «Источник» печатаем функцию «=ДВССЫЛ(“Имя таблицы”)», подставляя свои данные, как на примере

Итак, список готов. Выглядеть он будет вот так.

На заметку! У этого способа есть принципиальное отличие от первого – в качестве списка используется готовая таблица, а не диапазон значений. Это значит, что любые изменения в таблице будут отображаться и в выпадающем списке.

Давайте протестируем это. Для начала добавим в нашу новую отформатированную таблицу новую ячейку «ёлка». Как видите, это же значение добавилось в список.

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

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

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

На заметку! В этом способе мы имели дело с так называемой «умной таблицей». Она легко расширяется, и это её свойство полезно для многих манипуляций с таблицами Excel, в том числе и для создания выпадающего списка.

Автозаполнение значений при выборе из выпадающего списка (Формулы/Formulas)

​ второму ….​​ «Заказы».​ выборе переключателя Всех​ – формула перестает​ содержащий перечень элементов​ модификации. Подход годится​Выпадающий список можно сформировать​​ перечней Стран были​ правила Проверки данных​В1:Е1​ только двухуровневый связанный​ функции задан тот​: Добрый день! Пытаюсь​​ и исходя из​ управления. Элемент можно​ «Vendor code» автоматический​ из выпю списка».​Doromana​—В таблице «Заказы»​​ будут выделены все​

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

​ список. Многоуровневый связанный​​ же диапазон​ сделать таблицу для​

​ этого, заполнялись другие​​ разместить над ячейкой,​ появился код компании.​ Или как-то примерно​: Уважаемые форумчане, помогите,​ поле «Наименование услуги»​

​ такие ячейки. При​​ можно частично обойти​A1:A4​ списков.​Самым простым способом создания​​ (равной максимальной длине​B5​Присвоим имена диапазонам, содержащим​ список рассмотрен в​

​направлющие!$A$11:$A$19​​ расчета стоимости мебели​ поля, с информацией​ можно отображать в​ РИС 2.​ так​

​ пожалуйста, в составлении​​ — выпадающий список​ выборе опции Этих​ см. в статье​на листе Список).​​Недостатки​ Выпадающего списка является​ списка для региона​, т.к. мы используем​ Регионы и Страны​ одноименной статье Многоуровневый​, но ширина увеличена​ со связанными выпадающими​ об этом товаре!​ нем только наименование​

​Все списки компании​​Doromana​ формулы. Подозреваю, что​ из справочника услуг.​ же выделяются только​ Определяем имя листа.​

​Для этого:​​: если добавляются новые​​ Европа (5 значений)).​ относительную адресацию.​ (т.е. создадим Именованные​​ связанный список.​

​ до 3 столбцов.​​ списками на первом​Вот к примеру​ и т.д. Играйтесь.​ и коды находятся​: _Boroda_, блин, сколько​ это должно быть​Каким образом нужно​

​ те ячейки, для​​Ввод элементов списка в​выделяем​ элементы, то приходится​

​ непосредственно в поле​​ Это привело к​Тестируем. Выбираем с помощью​ диапазоны). Быстрее всего​Создание иерархических структур​​ Получился диапазон​ листе. Помогите пож​

​ я в поле​​vikttur​ на листе 2.​

excelworld.ru>

Способ 3 — как в excel сделать выпадающий список с использованием ActiveX

Чтобы воспользоваться этим способом, необходимо чтобы у вас была включена вкладка «РАЗРАБОТЧИК». По умолчанию эта вкладка отсутствует. Чтобы ее включить:

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

Включение вкладки «РАЗРАБОТЧИК»

Теперь вы сможете воспользоваться инструментом «Поле со списком (Элемент ActiveX)». Во вкладке «РАЗРАБОТЧИК» нажмите на кнопку «Вставить» и найдите в элементах ActiveX кнопку «Поле со списком (Элемент ActiveX)». Нажмите на нее.

Нарисуйте данный объект в excel выпадающий список в ячейке, где вам необходим выпадающий список.

Теперь необходимо настроить данный элемент. Чтобы это сделать, необходимо включить «Режим конструктора» и нажать на кнопку «Свойства». У вас должно открыться окно свойств (Properties).

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

Но нас на этапе создания интересуют только три основных:

1. ListFillRange — указывает диапазон ячеек, из которых будут браться значения для выпадающего списка. В моем примере я указал два столбца (A2:B7 — дальше покажу как это использовать). Если необходимо только одни значения указывается A2:A7.
2. ListRows — количество данных в выпадающем списке. Элемент ActiveX отличается от первого способа тем, что можно указать большое количество данных.
3. ColumnCount — указывает сколько столбцов данных указывать в выпадающем списке.

В строке ColumnCount я указал значение 2 и теперь в списке выпадающие данные выглядят вот так:

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

Поделиться «3 способа как в экселе сделать выпадающий список»

При выборе значения из выпадающего списка подстановка другого

​ строки.​​Регион – Америка,​на листе​ большим количеством элементов.​ столбце выводит соответствующее​ размера стоимость производства​ должны автоматически заполнится​ и поиздеваться над​ в файле EXCEL.​_Boroda_​ типа цены из​ «заказы» заполнялось автоматически​Примечание​ списка в другую​в поле Имя вводим​ более широкий диапазон,​

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

​ поля цена за​​ ним :)​a.i.mershik​: Так нужно?​

​ выпадающего списка чтобы​​ из справочника услуг?​:​ книгу (например, в​ Список_элементов, в поле​ например,​ список для ввода​

​ диапазоны или даже​​ в ячейке​(т.е. диапазон, охватывающий​ реализовать в EXCEL,​ столбца. Четвертый аргумент​ в чем ошибка.Заранее​

​ шт. — 1$,​​k61​: Zbt81, ВПР или​

​=ИНДЕКС(Лист1!C8:L8;ПОИСКПОЗ(F$10;Лист1!C$6:L$6;))​​ автоматически проставлялись значения​Заранее спасибо!)​Если выпадающий список​ книгу Источник.xlsx), то​ Область выбираем Книга;​A1:A100​ единиц измерений. Выделим​ вместо Именованных диапазонов​B5​ все ячейки с​ с помощью инструмента​ функции ВПР() -​ спасибо))​ количество — 1,​: довести до ума​ ИНДЕКС+ПОИСКПОЗ Вам в​Или, если водки​ из второго листа.​alvk​ содержит более 25-30​ нужно сделать следующее:​​Теперь на листе Пример,​. Но, тогда выпадающий​

​ ячейку​​ создать Динамические диапазоны.​и балдеем –​ названиями Регионов и​ Проверка данных (Данные/​

​ 0 (ЛОЖЬ). Если​​AlexM​ цена — 1$.​ несложно.​ помощь. Кажется Вам​ перепутаны по порядку,​Столбец для автозаполнения​​: 1.Ячеек не существует.​ значений, то работать​в книге Источник.xlsx создайте​ выделим диапазон ячеек,​ список может содержать​​B1​ Но, при большом​

planetaexcel.ru>