Выпадающий список для построения графиков эксель. Связанные выпадающие списки

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

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

Создаем раскрывающийся список в Экселе: способ первый

Заметьте, насколько легко пользоваться программой Excel – любые задачи решаются действительно в несколько кликов, достаточно потратить порядка пары минут на изучение функционала. Именно по этой причине MS Office, как пакет офисных программ, является самым продвинутым и популярным на рынке.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Заключение

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

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

И так есть несколько вариантов, как сделать выпадающие списки для работы в Microsoft Office Excel.

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

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

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

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

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

3. На вкладке «Параметры» выберите параметр «Список» в раскрывающемся меню. Убедитесь, что установлены нужные флажки.

4. Затем, нажмите на кнопку. Появится следующее диалоговое окно.

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

6. Выпадающий список в Excel будет создан.

Если ваш список короткий, вы можете ввести элементы непосредственно в «Источник» на вкладке «Настройка» в диалоговом окне «Проверка вводимых значений». Отделите каждый пункт списка разделителями, указанными в региональных настройках.
Если список должен располагаться на другом листе, вы можете воспользоваться параметром «= List» перед указанием диапазона данных.
Как создать выпадающий список в Экселе на основе данных из перечня

Представим, что у нас есть перечень фруктов:
Как сделать выпадающий список в Excel

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

Перейти на вкладку “Данные” => раздел “Работа с данными” на панели инструментов => выбираем пункт “Проверка данных”.

В поле “Источник” ввести диапазон названий фруктов =$A$2:$A$6 или просто поставить курсор мыши в поле ввода значений “Источник” и затем мышкой выбрать диапазон данных:

Если вы хотите создать выпадающие списки в нескольких ячейках за раз, то выберите все ячейки, в которых вы хотите их создать, а затем выполните указанные выше действия. Важно убедиться, что ссылки на ячейки являются абсолютными (например, $A$2), а не относительными (например, A2 или A$2 или $A2).

Как сделать выпадающий список в Excel используя ручной ввод данных

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

Для этого нам потребуется:
Выбрать ячейку, в которой мы хотим создать выпадающий список;
Перейти на вкладку “Данные” => раздел “Работа с данными” на панели инструментов =>
Проверка данных в Excel

Во всплывающем окне “Проверка вводимых значений” на вкладке “Параметры” в типе данных выбрать “Список”:
Проверка вводимых значений в Excel

В поле “Источник” ввести значение “Да; Нет”.
Нажимаем “ОК”
Да - Нет

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

Если вы хотите одновременно создать выпадающий список в нескольких ячейках – выделите нужные ячейки и следуйте инструкциям выше.
Как создать раскрывающийся список в Эксель с помощью функции СМЕЩ

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

Например, у нас есть список с перечнем фруктов:

Для того чтобы сделать выпадающий список с помощью формулы СМЕЩ необходимо сделать следующее:
Выбрать ячейку, в которой мы хотим создать выпадающий список;
Перейти на вкладку “Данные” => раздел “Работа с данными” на панели инструментов => выбрать пункт “Проверка данных”:
Проверка данных в Excel

Во всплывающем окне “Проверка вводимых значений” на вкладке “Параметры” в типе данных выбрать “Список”:
Проверка вводимых значений в Excel

В поле “Источник” ввести формулу: =СМЕЩ(A$2$;0;0;5)
Нажать “ОК”

Система создаст выпадающий список с перечнем фруктов.
Как эта формула работает?

На примере выше мы использовали формулу =СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;[высота];[ширина]).
Эта функция содержит в себе пять аргументов. В аргументе “ссылка” (в примере $A$2) указывается с какой ячейки начинать смещение. В аргументах “смещ_по_строкам” и “смещ_по_столбцам” (в примере указано значение “0”) – на какое количество строк/столбцов нужно смещаться для отображения данных.

В аргументе “[высота]” указано значение “5”, которое обозначает высоту диапазона ячеек. Аргумент “[ширина]” мы не указываем, так как в нашем примере диапазон состоит из одной колонки.
Используя эту формулу, система возвращает вам в качестве данных для выпадающего списка диапазон ячеек, начинающийся с ячейки $A$2, состоящий из 5 ячеек.

Как сделать выпадающий список в Excel с подстановкой данных (с использованием функции СМЕЩ)

Если вы используете для создания списка формулу СМЕЩ на примере выше, то вы создаете список данных, зафиксированный в определенном диапазоне ячеек. Если вы захотите добавить какое-либо значение в качестве элемента списка, вам придется корректировать формулу вручную.

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

Перейти на вкладку “Данные” => раздел “Работа с данными” на панели инструментов => выбрать пункт “Проверка данных”;
Во всплывающем окне “Проверка вводимых значений” на вкладке “Параметры” в типе данных выбрать “Список”;
В поле “Источник” ввести формулу: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
Нажать “ОК”

В этой формуле, в аргументе “[высота]” мы указываем в качестве аргумента, обозначающего высоту списка с данными – формулу СЧЕТЕСЛИ, которая рассчитывает в заданном диапазоне A2:A100 количество не пустых ячеек.

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

Как создать выпадающий список в Excel с автоматической подстановкой данных

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

На панели инструментов нажимаем пункт “Форматировать как таблицу”:

Из раскрывающегося меню выбираем стиль оформления таблицы

Нажав клавишу “ОК” во всплывающем окне, подтверждаем выбранный диапазон ячеек:

Присваиваем имя таблице в правой верхней ячейке над колонкой “А”:

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

Перейти на вкладку “Данные” => раздел “Работа с данными” на панели инструментов => выбрать пункт “Проверка данных”:

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

В поле источник указываем =”название вашей таблицы”. В нашем случае мы ее назвали “Список”:
Поле источник автоматическая подстановка данных в выпадающий список Эксель

Готово! Выпадающий список создан, в нем отображаются все данные из указанной таблицы:

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

Таблица автоматически расширит свой диапазон данных. Выпадающий список соответственно пополнится новым значением из таблицы:
Автоматическая подстановка данных в выпадающий список эксель

Как скопировать выпадающий список в Excel

В Excel есть возможность копировать созданные выпадающие списки. Например, в ячейке А1 у нас есть выпадающий список, который мы хотим скопировать в диапазон ячеек А2:А6.

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

выделите ячейки в диапазоне А2:А6, в которые вы хотите вставить выпадающий список;

нажмите сочетание клавиш на клавиатуре CTRL+V.
Так, вы скопируете выпадающий список, сохранив исходный формат списка (цвет, шрифт и.т.д). Если вы хотите скопировать/вставить выпадающий список без сохранения формата, то:
нажмите левой клавишей мыши на ячейку с выпадающим списком, который вы хотите скопировать;

нажмите сочетание клавиш на клавиатуре CTRL+C;
выберите ячейку, в которую вы хотите вставить выпадающий список;
нажмите правую кнопку мыши => вызовите выпадающее меню и нажмите “Специальная вставка”;
выпадающий список в excel

В появившемся окне в разделе “Вставить” выберите пункт “условия на значения”:

Нажмите “ОК”
После этого, Эксель скопирует только данные выпадающего списка, не сохраняя форматирование исходной ячейки.
Как выделить все ячейки, содержащие выпадающий список в Экселе

Иногда, сложно понять, какое количество ячеек в файле Excel содержат выпадающие списки. Есть простой способ отобразить их. Для этого:

Нажмите на вкладку “Главная” на Панели инструментов;
Нажмите “Найти и выделить” и выберите пункт “Выделить группу ячеек”:

В диалоговом окне выберите пункт “Проверка данных”. В этом поле есть возможность выбрать пункты “Всех” и “Этих же”. “Всех” позволит выделить все выпадающие списки на листе. Пункт “этих же” покажет выпадающие списки схожие по содержанию данных в выпадающем меню. В нашем случае мы выбираем “всех”:
Выпадающий список в Excel. Как найти все списки

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

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

Иногда нам требуется создать несколько выпадающих списков, причем, таким образом, чтобы, выбирая значения из первого списка, Excel определял какие данные отобразить во втором выпадающем списке.
Предположим, что у нас есть списки городов двух стран Россия и США:

Для создания зависимого выпадающего списка нам потребуется:
Создать два именованных диапазона для ячеек “A2:A5” с именем “Россия” и для ячеек “B2:B5” с названием “США”. Для этого нам нужно выделить весь диапазон данных для выпадающих списков:
зависимый выпадающий список в Excel

Перейти на вкладку “Формулы” => кликнуть в разделе “Определенные имена” на пункт “Создать из выделенного”:
Зависимые выпадающие списки в Excel

Во всплывающем окне “Создание имен из выделенного диапазона” поставьте галочку в пункт “в строке выше”. Сделав это, Excel создаст два именованных диапазона “Россия” и “США” со списками городов:
зависимый-выпадающий-список-в-excel

Нажмите “ОК”
В ячейке “D2” создайте выпадающий список для выбора стран “Россия” или “США”. Так, мы создадим первый выпадающий список, в котором пользователь сможет выбрать одну из двух стран.

Теперь, для создания зависимого выпадающего списка:
Выделите ячейку E2 (или любую другую ячейку, в которой вы хотите сделать зависимый выпадающий список);
Кликните по вкладке “Данные” => “Проверка данных”;
Во всплывающем окне “Проверка вводимых значений” на вкладке “Параметры” в типе данных выберите “Список”:
Проверка вводимых значений в Excel

Нажмите “ОК”

Теперь, если вы выберите в первом выпадающем списке страну “Россия”, то во втором выпадающем списке появятся только те города, которые относятся к этой стране. Также и в случае, когда выбираете “США” из первого выпадающего списка.

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

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

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

1. Перейдите на первую пустую клетку после вашего списка.

1. Сделайте правый клик. Затем выберите указанный пункт.

1. В результате этого появится следующий список.

1. Для перехода по нему достаточно нажать на горячие клавиши Alt +↓ .

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

1. Затем для выбора можно использовать только стрелочки (↓ и ). Для того чтобы вставить нужный продукт (в нашем случае), достаточно нажать на клавишу Enter .

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

Обратите внимание на то, что этот метод не работает, если вы выберите клетку, выше которой нет никакой информации.

Стандартный

В этом случае необходимо:

1. Выделить нужные ячейки. Перейти на вкладку «Формулы». Нажать на кнопку «Определенные имена». Выбрать пункт «Диспетчер имён».

1. Затем кликнуть на «Создать».

1. Далее нужно будет указать желаемое имя (нельзя использовать символ тире или пробел). В графе диапазон произойдет автозаполнение, поскольку нужные ячейки были выделены в самом начале. Для сохранения нажмите на «OK».

1. Затем закройте это окно.

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

1. Нажмите на «Тип данных». Необходимо задать значение «Список».

1. Вследствие этого появится поле «Источник». Кликните туда.

1. Затем выделите нужные ячейки. Ранее созданное имя автоматически подставится. Для продолжения нажимаем на «OK».

1. Благодаря этим действиям вы увидите вот такой элемент.

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

Как включить режим разработчика

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

1. Нажмите на меню «Файл».

1. Перейдите в раздел «Параметры».

1. Откройте категорию «Настроить ленту». Затем поставьте галочку напротив пункта «Разработчик». Для сохранения информации кликните на «OK».

Элементы управления

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

1. Выделите свою таблицу данных. Перейдите на вкладку «Разработчик». Кликните на иконку «Вставить». Нажмите на указанный элемент.

1. Также изменится иконка указателя.

1. Выделите какой-нибудь прямоугольник. Именно таких размеров и будет ваша будущая кнопка. Её необязательно делать слишком большой. В нашем случае это только пример.

1. После этого сделайте правый клик мышкой по этому элементу. Затем выберите пункт «Формат объекта».

1. В окне «Форматирование объекта» необходимо:
   • Указать диапазон значений для формирования списка.
   • Выбрать ячейку, в которую будет выводиться результат.
   • Указать количество строк будущего списка.
   • Нажать на «OK» для сохранения.

1. Кликните на этот элемент. После этого вы увидите варианты для выбора.

1. Вследствие этого вы увидите какое-нибудь число. 1 – соответствует первому слову, а 2 – второму. То есть в этой ячейке выводится лишь порядковый номер выбранного слова.

ActiveX

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

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

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

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

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

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

При этом значения и свойства будут меняться в зависимости от того, какой элемент будет активен в данный момент. Здесь вы сможете изменить всё, даже шрифт.

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

1. Далее необходимо кликнуть правой кнопкой мыши по созданному элементу. Выберите «Объект Combobox». Затем – «Edit».

1. Благодаря этим действиям вы увидите, что внешний вид объекта стал другим. Исчезнет возможность изменения размера.

1. Теперь вы можете спокойно выбрать что-нибудь из этого списка.

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

1. Также необходимо закрыть окно свойств.

Убрать объекты ActiveX довольно просто.

1. Перейдите на вкладку «Разработчик».
2. Активируйте «Режим конструктора».

1. Кликните на этот объект.

1. Нажмите на горячую клавишу Delete .
2. И всё сразу же исчезнет.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. Выберите любую клетку. Перейдите на вкладку «Данные» и нажмите на инструмент «Проверка данных».

1. Выбираем нужный тип данных. В источнике указываем необходимый диапазон. Сохраняем при помощи кнопки «OK».

1. Кликните на другую ячейку и повторите описанные ранее действия по вызову такого же окна.

1. Указываем точно такой же тип данных, но в источнике на этот раз указываем следующую формулу.

После сохранения появится ошибка о том, что источник пустой. Нажимаем на кнопку «Да».

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

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

1. Попробуйте выбрать другой город – список изменится автоматически.

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

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

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

1. Сделайте правый клик по названию листа, на котором расположена таблица и будущий выпадающий список.

1. В контекстном меню выберите пункт «Просмотреть код».

1. В появившемся окне вбейте следующий код.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next

If Not Intersect(Target, Range(«B11»)) Is Nothing And Target.Cells.Count = 1 Then

Application.EnableEvents = False

NewSelectWord = Target

Application.Undo

BeforeWord = Target

If Len(BeforeWord) <> 0 And BeforeWord <> NewSelectWord Then

Target = Target & «,» & NewSelectWord

Target = NewSelectWord

If Len(NewSelectWord) = 0 Then Target.ClearContents

Application.EnableEvents = True

1. Затем вернитесь к книге и выберите что-нибудь.

1. Повторите это действие еще раз.

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

1. Для очистки этой клетки нужно использовать клавишу Delete .

Во всех остальных случаях значения будут только накапливаться.

Заключение

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

Видеоинструкция

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

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

В этом материале речь пойдет именно о заполнении таблицы. Здесь вы сможете узнать, как в Эксель сделать выпадающий список, который позволит значительно быстрее заполнять таблицу данными. Инструкция будет актуальна для Excel 2007, 2010, 2013 и 2016.

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

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

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

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

После чего нужно заполнить строку «Источник».

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

После указания источника закрываем окно «Проверка вводимых значений» нажатием на кнопку «Ok». Выпадающий список в Экселе сделан и его можно проверять.

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

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

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

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

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

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

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

После выбора стиля, появится окно «Форматирование таблицы». Здесь нужно установить отметку напротив функции «Таблица с заголовком» и нажать «Ok».

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

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

Данный способ создания выпадающих списков с возможностью добавления новых строк был проверен на Excel 2010, но он также должен работать и в Excel 2007. Как и в более современных версиях Excel, например, Excel 2013 и Excel 2016.