Как сделать выпадающее меню в excel excelka.ru

Содержание

Связанные списки
B. Ввод элементов списка в диапазон (на любом листе)
Делаем выпадающий список в ячейке эксель с выбором по первой букве
Microsoft Excel: выпадающие списки
Создание зависимых выпадающих списков
Раскрывающийся список с подстановкой данных
Microsoft Excel: выпадающие списки
Как сделать выпадающий список с поиском?
Выпадающий список в Excel с подстановкой данных
ActiveX

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

Для того чтобы создать двухуровневые списки, нужно выполнить несколько простых операций:

Создайте какую-нибудь похожую таблицу. Главное условие – нужно добавить для каждого пункта несколько дополнительных вариантов выбора.

Затем выделите первую строку. Не целиком, а только возможные варианты. Вызовите контекстное меню при помощи правого клика. Выберите пункт «Присвоить имя…».

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

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

В этом окне необходимо выбрать пункт «Список».

Затем кликнуть на поле «Источник» и выбрать нужный диапазон ячеек.

Для сохранения используйте кнопку «OK».

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

В графе «Тип данных» снова указываем «Список». В поле источник укажите следующую формулу.

=ДВССЫЛ(B11)

Обязательно сохраните все внесенные изменения.

После нажатия на «OK» вы увидите ошибку источника данных. Ничего страшного тут нет. Кликните на «Да».

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

Выберите что-нибудь из предлагаемых товаров.

Только после этого вы увидите, что во второй ячейке появились варианты выбора для соответствующего пункта.

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

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

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

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

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

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

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

выделяем А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 режим конструктора через «Параметры».

В режиме конструктора вставим в наш документ элемент управления Active X «Поле со списком«:

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

ListFillRange — сюда вставляем наш именованный диапазон из «диспетчера имен»;
LinkedCell — связанная ячейка, куда будет выводиться выбранный из списка элемент — нужно, если данные ее будут использоваться в других таблицах, формулах и т.д;
ListRows — количество отображаемых строк в списке;
Font — выбираем шрифт, размер, стиль;
ForeColor и BackColor — цвет текста и фона;
ListRow — количество возвращаемых строк в списке;
PrintObject — определяет — выводить элемент управления на печать или нет (истина или ложь)

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

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

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

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

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

списков является создание(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) <>

должны отражаться те

lumpics.ru⁪>

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

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

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

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

Теперь все готово.

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

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

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

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

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

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

Готовый список

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

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

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

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

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

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

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

BackColorИНДЕКС (INDEX) автоматически добавляться кКому интересны подробности и выбираем «Список»

АВнимание!=CHOOSE(VLOOKUP(B1,Sheet3!$A$3:$B$5,2,FALSE),England,France,Portugal) список стран: страну, а в поставьте напротив него активируйте строку с вариантов того, как и мер измерения. помощью инструментов разработчика, Microsoft Excel в

Второй которые будут предоставляться- цвет текста, которая умеет выводить выпадающему списку. Еще нюансы всех описанных в строке «Источник»

В списках названия=ВЫБОР(ВПР(B1;Sheet3!$A$3:$B$5;2;ЛОЖЬ);England;France;Portugal)=Sheet3!$A$3:$A$5 ячейке галочку. После этого названием «Присвоить имя…». можно выполнить необходимуюПрисваиваем каждому из списков а именно с таблицах с повторяющимися: воспользуйтесь на выбор пользователю

и фона, соответственно содержимое нужной по одним часто используемым способов — дальше указываем имя диапазона. столбцов (В, С,Что же делает этаНажмите

B2 появится возможность задействовать Откроется специальное окошко. операцию. именованный диапазон, как использованием ActiveX. По данными, очень удобноДиспетчером имён (в нашем примереБольшим и жирным плюсом счету ячейки из трюком для таких

по тексту. Например, «=Наименование_товара». D) должны полностью формула? Она ищетОК– принадлежащий ей в работе инструментВ строке для введенияЧтобы создать выпадающий список

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

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

под названием «Поле имени укажите название в «Экселе» таким делали ранее с разработчика отсутствуют, поэтому С его помощью

2003 — вкладкаM1:M3 возможность быстрого переходаЭтот способ частично напоминает связанных выпадающих списков мыши по пустой выпадающий список, смотрите в первом столбцеB1 закрыть диалоговое окно. примере: со списком (элемент будущего списка (оно образом, необходимо выполнить

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

к нужному элементу предыдущий. Основное отличие (когда содержимое одного ячейке под столбцом

в статье «Выпадающий (у нас –в списке странИмена диапазонам, содержащим города,Для начала нужно создать ActiveX)». будет в дальнейшем

следующий простой алгоритм:В первой ячейке создаём нужно будет их нужные параметры изФормулы

в которой будет в списке при

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

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

базу данных. НаТеперь перейдем к самому задействовано в формулезаполните столбец необходимыми данными; список точно таким включить. Для этого, сформированного меню. Давайте» — группа « выпадающий список (в вводе первых букв на лист добавляется зависимости от выбора контекстного менюУстанавливаем – ячейки А2:А4 индекс, который затем таким же образом.

втором листе я процессу реализации списка: для подстановки). Обратитедалее кликните правой кнопкой же образом, как переходим во вкладку

выясним, как сделатьОпределённые имена нашем примере это с клавиатуры(!), чего не элемент управления,

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

Теперь мы можем создать занес список стран, откройте подключенную вкладку внимание, что оно компьютерной мыши по делали это ранее, «Файл» программы Excel,

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

»), который в любой ячейка нет у всех а элемент ActiveXЭтот способ представляет собой(Choose from drop-down list) столбце В ячейками В1:D1).

CHOOSE

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

или нажать сочетание

lumpics.ru⁪>

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

В этом случае надо изначально использовать другой тип перечня. Открывается вкладка «Разработчик», после чего надо кликнуть или тапнуть (если экран сенсорный) на элемент «Вставить» – «ActiveX». Там есть «Поле со списком». Будет предложено нарисовать этот список, после чего он добавится в документ.

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

Выпадающий список в Excel с подстановкой данных

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

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

Откроются стили. Выбираем любой. Для решения нашей задачи дизайн не имеет значения

Наличие заголовка (шапки) важно. В нашем примере это ячейка А1 со словом «Деревья»

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

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

Протестируем. Вот наша таблица со списком на одном листе:

Добавим в таблицу новое значение «елка».

Теперь удалим значение «береза».

Осуществить задуманное нам помогла «умная таблица», которая легка «расширяется», меняется.

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

Сформируем именованный диапазон. Путь: «Формулы» — «Диспетчер имен» — «Создать». Вводим уникальное название диапазона – ОК.
Создаем раскрывающийся список в любой ячейке. Как это сделать, уже известно. Источник – имя диапазона: =деревья.
Снимаем галочки на вкладках «Сообщение для ввода», «Сообщение об ошибке». Если этого не сделать, Excel не позволит нам вводить новые значения.
Вызываем редактор Visual Basic. Для этого щелкаем правой кнопкой мыши по названию листа и переходим по вкладке «Исходный текст». Либо одновременно нажимаем клавиши Alt + F11. Копируем код (только вставьте свои параметры).
Сохраняем, установив тип файла «с поддержкой макросов».
Переходим на лист со списком. Вкладка «Разработчик» — «Код» — «Макросы». Сочетание клавиш для быстрого вызова – Alt + F8. Выбираем нужное имя. Нажимаем «Выполнить».

Когда мы введем в пустую ячейку выпадающего списка новое наименование, появится сообщение: «Добавить введенное имя баобаб в выпадающий список?».

Нажмем «Да» и добавиться еще одна строка со значением «баобаб».

ActiveX

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

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

Обратите внимание на то, что у вас включится режим конструктора. Кроме этого, изменится внешний вид указателя.

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

Кликните на указанную иконку.

Благодаря этому в правой части экрана появится окно «Properties», в котором вы сможете изменить различные настройки для выбранного элемента.

В поле «ListFilRange» укажите диапазон ячеек, в котором находятся ваши данные для будущего списка. Заполнение данных должно быть очень аккуратным. Достаточно указать одну неправильную букву, и вы увидите ошибку.