Выпадающий список в excel

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>

Связанный список

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

1. Для этого нужно подготовить как минимум три столбца. В первом будут заполнены наименования товаров, а во втором и третьем – их возможные единицы измерения. Столбцов с возможными вариациями единиц измерения может быть и больше.
2. Сначала создаем один общий список для всех наименований продуктов, выделив все строки столбца “Наименование”, через контекстное меню выделенного диапазона.
3. Задаем ему имя, например, “Питание”.
4. Затем таким же образом формируем отдельные списки для каждого продукта с соответствующими единицами измерения. Для большей наглядности возьмем в качестве примера первую позицию – “Лук”. Отмечаем ячейки, содержащие все единицы измерения для этого продукта, через контекстное меню присваиваем имя, которое полностью должно совпадать с наименованием.Таким же образом создаем отдельные списки для всех остальных продуктов в нашем перечне.
5. После этого вставляем общий список с продуктами в верхнюю ячейку первого столбца основной таблицы – как и в описанном выше примере, через кнопку “Проверка данных” (вкладка “Данные”).
6. В качестве источника указываем “=Питание” (согласно нашему названию).
7. Затем кликаем по верхней ячейке столбца с единицами измерения, также заходим в окно проверки данных и в источнике указываем формулу “=ДВССЫЛ(A2)“, где A2 – номер ячейки с соответствующим продуктом.
8. Списки готовы. Осталось его только растянуть их все строки таблицы, как для столбца A, так и для столбца B.

Процесс создания списка

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

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

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

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

На основе данных из перечня

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

5

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

Выбрать ячейку, отведенную под будущий перечень.

На ленте найти вкладку «Данные». Там осуществляем нажатие по «Проверка данных».

Найти пункт «Тип данных» и переключить значение на «Список».

В поле, обозначающем опцию «Источник», ввести нужный диапазон

Обратите внимание, что нужно указывать абсолютные ссылки, чтобы при копировании списка информация не смещалась.

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

С ручной записью данных

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

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

1. Нажать по ячейке, отведенной под перечень.
2. Открыть «Данные» и там отыскать знакомый нам раздел «Проверка данных».
3. Снова выбираем тип «Список».
4. Здесь в качестве источника необходимо ввести “Да;Нет”. Видим, что информация при ручном вводе вводится с использованием точки с запятой для перечисления.

После нажатия «ОК» у нас появился следующий результат.

11

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

Выпадающий список с удалением использованных элементов

​Для Производителя мы уже​ с контролем дублирования.​​ списка.​Portugal​

Постановка задачи

​ вкладке​ в другой статье:​, т.к. мы используем​ этого Региона.​Вот и все! Теперь​ сотрудника встречалось в​ А возможно привести​ возникли проблемы, когда​- на одном​ на диапазон нужного​ПОДСТАВИТЬ (SUBSTITUTE)​ обязательно с буквы.​ эту формулу в​ используем формулу, которая​Итак, как сделать два​Инструмент Проверка данных (Данные/​

​, то мы должны​Formulas​ Расширяемый Связанный список.​

Шаг 1. Кто сколько работает?

​ относительную адресацию.​Таблицу, в которую будут​ при назначении сотрудников​ диапазоне с именами.​ пример такого макроса?​ в столбце с​ листе список номеров​ размера, сдвинутый относительно​, т.е. формула будет​ Поэтому если бы​

​ нескольких ячейках -​ подробно описана здесь.​

​ связанных списка в​ Работа с данными/​ обратиться к базе​(Формулы) есть команда​

Шаг 2. Кто еще свободен?

​ПРЕДСТАВЬТЕ СИТУАЦИЮ:​Тестируем. Выбираем с помощью​ заноситься данные с​ на дежурство их​Теперь выясним, кто из​serg14​ номерами машин появились​ машин (бортов) с​ исходной ячейки на​ выглядеть как =ДВССЫЛ(ПОДСТАВИТЬ(F3;»​

​ в одной из​

Шаг 3. Формируем список

​ не забудьте обозначить​ Она выглядит так:​ Excel: категория, подкатегория​ Проверка данных) с​ с индексом​Name Manager​Мы хотим создать​ выпадающего списка в​ помощью Связанного списка,​ имена будут автоматически​ наших сотрудников еще​: Спасибо огромное!!!​

​ буквы. Я в​

​ признаками по условиям​

​ заданное количество строк​

​ «;»_»))​ марок автомобилей присутствовал​ ячейки как абсолютные​Модель — описание для​ и категория более​ условием проверки Список,​3​(Диспетчер имён). Нажав​ в Excel небольшую​ ячейке​ разместим на листе​

Шаг 4. Создаем именованный диапазон свободных сотрудников

• ​ удаляться из выпадающего​ свободен, т.е. не​serg14​​ формуле в диспетчере​ (в приложенном примере​ и столбцов. В​
• ​Надо руками создавать много​ бы пробел (например​ ссылки! И еще​​ этой записи сделаем​​ нижнего уровня. Своими​​ как раз предназначен​

​, в которой хранятся​ на нее, откроется​​ табличку, где можно​​A5​

​Таблица​

​ списка, оставляя только​

​ исчерпал запас допустимых​: Только я не​ имён вместо СЧЁТ​ — лист «Транспорт»).​​ более понятном варианте​​ именованных диапазонов (если​

Шаг 5. Создаем выпадающий список в ячейках

​ Ssang Yong), то​ СУПЕРВАЖНО, чтобы вся​ таким же самым​ словами в данном​ для решения нашей​​ названия городов Португалии.​​ диалоговое окно​

• ​ выбрать страну и​Регион – Америка,​. См. файл примера​​ тех, кто еще​ смен. Добавим еще​​ очень понял как​
• ​ поставил СЧЁТЗ и​ У каждой машины​ синтаксис этой функции​​ у нас много​​ его пришлось бы​​ запись была подтверждена​

​ образом.​ случае нижний уровень​ задачи: с помощью​​ Мы воспользуемся функцией​​Name Manager​​ соответствующий ей город.​​ вызываем связанный список​

​ Связанный_список.xlsx​ свободен.​ один столбец и​ это сделать…не подскажите​ в выпадающем списке​ значения условий меняется​ таков:​ марок автомобилей).​

planetaexcel.ru>

​ заменить в ячейке​

• Excel выделить цветом по условию excel
• Excel два условия в если
• Excel если и несколько условий
• Excel заливка ячейки по условию
• Excel поиск значения по нескольким условиям в excel
• Excel ранг с условием
• Excel счетесли несколько условий
• В excel двойное условие если
• Автоматическая заливка ячеек в excel по условию
• Excel суммирование по условию
• Выделить цветом ячейку в excel по условию
• Если 2 условия если excel

Создание одного списка на основании другого

Готовый пример связанного выпадающего списка Excel

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

Сначала необходимо прописать марки машин (например, Audi, Renault, Nissan) и присвоить этим ячейкам имя («авто»). Чуть ниже нужно написать название запчастей и тоже присвоить имя этим полям. Обязательно нужно, чтобы поля с запчастями Nissan назывались Nissan и т.д.

После этого можно перейти к созданию одного выпадающего перечня на основе другого. Таблица должна состоять из 2 колонок: марка авто и запчасти. Первый столбец делается по знакомому уже принципу: «Данные — Проверка», в качестве источника указывается перечень автомобилей. Теперь можно перейти ко второму столбцу — «Запчасти». Здесь тоже надо щелкнуть в панели меню пункты «Данные — Проверка», но в поле «Источник» нужно написать следующее: =ДВССЫЛ(A5). А5 — это название поля, в котором указан автомобиль из колонки «Марка авто». После того как в первом столбце будет указана марка, во втором столбце можно будет выбрать только те детали, которые подходят к конкретной машине.

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

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

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

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

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

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

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

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

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

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

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

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

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

24

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

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

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

27

Связанные выпадающие списки и формула массива в Excel

​al_anya​ догоняю​ отпишусь!​ ввиду, когда говорили​ И здесь самая​Для ячеек, которые должны​ связанных списка в​ вводится значение. Все​- для значения​vikttur​ Например, «Зимние пальто».​ закладке «Формулы» в​Например, в первом​

Два связанных выпадающих списка с формулой массива

​ ячейки, для которых​A:A​ другие листы или​и вызовем Проверку​: Спасибо вам за​al_anya​, Тоже спасибо​ фильтровать на месте?​ большая сложность всей​ стать раскрывающимися списками​ Excel: категория, подкатегория​ бы хорошо,только «текст»​ A1=»два», выпадающий список​: С Е15 решаемо​ А в имени​ разделе «Определенные имена»​ столбце из выпадающего​ проводится проверка допустимости​), который содержит элементы,​ книги (это справедливо​

​ данных.​

• ​ такой емкий ответ,​: Формулу поменяла, а​
• ​ за помощь.​Sanja​
• ​ формулы.​ в меню «Данные»​

​ и категория более​ не подставляется в​ в ячейке B1​

​ (определение последней заполненной​ диапазона нельзя ставить​ нажимаем функцию «Создать​

1. ​ списка выбрали «Пальто».​
2. ​ данных (заданная с​
3. ​ ведется без пропусков​

​ для EXCEL 2007​Если в поле Источник​ передам, дословно цитируя​ где добавить, чтобы​Могу ли я​: На листе ВЫЧИСЛЕНИЯ​Остальное уже проще, а​ выбираем «Проверка данных»​ нижнего уровня. Своими​ ячейку (E1 из​ будет из вариантов​ ячейки). Но с​ пробел. Имя диапазона​

​ из выделенного фрагмента».​ Во втором столбце​ помощью команды Данные/​ строк (см. файл​ и более ранних).​ указать через точку​steysi​ текст выводился?​ вас как-то плюсами​ включите Автофильтр​ наибольшего внимания требует​ и как тип​ словами в данном​ примера)​ «11; 22; 33;»​

Первый и второй связанный выпадающий список: Тип и Производитель

​ напишем так «Зимние_пальто».​ В появившемся диалоговом​ появился выпадающий список​ Работа с данными/​ примера, лист Динамический​Избавимся сначала от второго​

​ с запятой единицы​: Подскажите пожалуйста как​Pelena​

​ отблагодарить?)​arturnt​ функция СЧЁТЕСЛИМН, которая​ данных выбираем «Список».​

​ случае нижний уровень​И да, пустая​- для значения​ подряд» плохо.​

Третий связывающий выпадающий список: Модель

​ Но формула ДВССЫЛ​ окне оставляем галочку​ размеров этого пальто.​ Проверка данных). При​ диапазон).​ недостатка – разместим​ измерения шт;кг;кв.м;куб.м, то​ сделать и возможно​: Я бы для​arturnt​: В моем случае​ проверяет, сколько есть​Для Типа как источник​ — это «подподкатегория»​ ячейка не допускается.​

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

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

​Нажимаем «ОК». Всё, имена​

​ же ячейки из​ такие ячейки. При​ перечень элементов, расположенных​ листе.​Теперь смотрим, что получилось.​ со следующим условием:​ цена. То есть​haver​ «Вычисления» будет что​ сколько раз в​

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

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

​ то вроде базы,​ списке встречаются такие​

​ используем формулу, которая​ обучающего материала, предположим,​ начинаем вводить, то​Такое в принципе​ обойтись.​Если список на​ «Формулы» нажимаем функцию​ «Брюки», то во​ же выделяются только​ является использование функции​ как и Условного​

​B1​ в оплате 25%,​

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

exceltable.com>

Выпадающий список с данными другого листа или файла Excel

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

1. Активировать ячейку, где размещаем перечень.
2. Открываем уже знакомое нам окно. В том же месте, где мы ранее указывали источники на другие диапазоны, указывается формула в формате =ДВССЫЛ(“Лист1!$A$1:$A$9”). Естественно, вместо Список1 и Лист1 можно вставлять свои имена книги и листа соответственно. 

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

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

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

В открывшемся окне переходим в подраздел «Настройка ленты», и ставим флажок напротив значения «Разработчик». Жмем на кнопку «OK».

После этого, на ленте появляется вкладка с названием «Разработчик», куда мы и перемещаемся. Чертим в Microsoft Excel список, который должен стать выпадающим меню. Затем, кликаем на Ленте на значок «Вставить», и среди появившихся элементов в группе «Элемент ActiveX» выбираем «Поле со списком».

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

Затем мы перемещаемся в «Режим конструктора». Жмем на кнопку «Свойства элемента управления».

Открывается окно свойств элемента управления. В графе «ListFillRange» вручную через двоеточие прописываем диапазон ячеек таблицы, данные которой будут формировать пункты выпадающего списка.

Далее, кликаем по ячейке, и в контекстном меню последовательно переходим по пунктам «Объект ComboBox» и «Edit».

Выпадающий список в Microsoft Excel готов.

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

Как создать связанный список Excel?

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

Это можно сделать разными методами.

Метод 1: Функция ДВССЫЛ

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

Предположим, нам надо составить такой перечень моделей марок Toyota, Ford, Nissan.

Модельный ряд Toyota нужно выделить, начиная ячейкой A2 вплоть до конца перечня и назвать его аналогично. Если используется древняя версия Excel, эта функция реализуется через меню «Вставка – Имя – Присвоить». Если версия Excel не самая старая (начиная с 2007), то нужно перейти во вкладку «Формулы», где отыскать «Диспетчер имен». Аналогичная операция проводится и с перечнем модельного ряда других производителей автомобилей.

Во время присвоения имен важно не забывать: нельзя составлять имена диапазонов с пробелами и знаками препинания. Также нельзя начинать их с цифры

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

Теперь нужно сгенерировать перечень автомобилей. Нужно нажать мышью (левой кнопкой) по пустой ячейке и открыть меню Данные – Проверка для Excel 2003 версии и кнопку «Проверка данных» на вкладке «Данные» для Excel 2007 версии и более современных. Затем в перечне «Тип данных» нужно выбрать Список, и в поле «Источник» написать ячейки с названиями марок.

Теперь следует сформировать второй список, где будут перечисляться машины из модельного ряда. Последовательность действий такая же, но с тем лишь исключением, что в поле Источник нужно указать формулу =ДВССЫЛ(F3). Естественно, в скобках нужно указать адрес подходящей именно в вашем случае ячейки. Все, после того, как вы нажмете ОК, формирование второго перечня будет осуществляться, исходя из данных в первом.

Но такой метод имеет ряд существенных недостатков:

1. Нельзя использовать в качестве зависимых списков те, которые задаются функциями типа СМЕЩ.
2. Названия элементов первичного выпадающего диапазона возможно указывать исключительно без пробелов, поэтому их придется заменять на нижнее подчеркивание.
3. Необходимость создания большого количества именованных диапазонов. особенно это неудобно, если есть множество марок автомобилей.

Использование функции СМЕЩ и ПОИСКПОЗ

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

Для формирования первого списка, который будет влиять на содержимое второго, подойдет и стандартный способ, описанный выше, то есть:

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

А вот для второго списка, на который будет влиять первый, придется выполнять другие действия, а именно создать именованный диапазон с функцией =СМЕЩ, создающей ссылку на ячейку, где указаны исключительно модели определенной марки. А для этого осуществляются следующие действия:

1. Надо нажать комбинацию Ctrl + F3. Также можно открыть диспетчер имен и присвоить имя соответствующему диапазону.
2. Создать новый диапазон вместе с функцией СМЕЩ в ссылке и присвоить ему имя. Оно может быть каким-угодно, например, «Модели».

В нашей ситуации формула будет следующей:

=СМЕЩ($A$1;ПОИСКПОЗ($G$7;$A:$A;0)-1;1;СЧЁТЕСЛИ($A:$A;$G$7);1)

Важно указывать только абсолютные ссылки. То есть, в них надо указать знак $

После нажатия клавиши Enter, Excel автоматически добавит имена листов. Это не должно вас пугать.

Результат будет следующий:

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

1. Выделить ячейку G8.
2. Выбрать команду «Проверка данных» на вкладке «Данные».
3. Там, где источник списка, указать имя диапазона: =Модели.

И все!

Выпадающий список в Excel с данными с другого листа/файла

Когда значения для выпадающего списка расположены на другом листе или в другой книге, стандартный способ не работает. Решить задачу можно с помощью функции ДВССЫЛ: она сформирует правильную ссылку на внешний источник информации.

1. Делаем активной ячейку, куда хотим поместить раскрывающийся список.
2. Открываем параметры проверки данных. В поле «Источник» вводим формулу: =ДВССЫЛ(“Лист1!$A$1:$A$9”).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

А. Простейший выпадающий список — ввод элементов списка непосредственно в поле Источник

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

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

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

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

Недостатки этого подхода: элементы списка легко потерять (например, удалив строку или столбец, содержащие ячейку B1); не удобно вводить большое количество элементов. Подход годится для маленьких (3-5 значений) неизменных списков. Преимущество: быстрота создания списка.

Стандартный метод

1. Выделите ячейки, где указаны данные, которые будут источником для выпадающего списка (в случае с нашей таблицей, это – наименования товаров).
2. Дальнейшие действия зависят от версии Excel. Если она 2003 года (да, ею еще до сих пор пользуются), нужно пройти по пути «Вставка – Имя – Присвоить». В случае использования более новой версии программы нужно нажать по вкладке «Формулы», где находится Диспетчер имен. После клика по ней нужно найти кнопку «Создать» и также нажать левой кнопкой мыши по ней. После этого нужно ввести имя, соответствующее следующим требованиям:
   1. отсутствие пробелов.
   2. начинается со знака !.
3. Подтвердите настройки путем нажатия по клавише «ОК».
4. Выберите диапазон, который будет использоваться в качестве источника данных для списка, и нажмите по кнопке «Данные» на ленте, где вам нужно нажать на пункт «Проверить». Появится список, в котором вам нужно выбрать тип данных. В нашем примере это «Список». Затем укажите в поле символ «=», и название диапазона. В нашем случае =Товары.

Далее нужно кликнуть по кнопке «ОК», и ячейка превратится в список, появляющийся при нажатии по ней.

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

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

Списки этого типа могут связываться с другими.