Создание выпадающего перечня в Excel

Проще всего эту задачку выполнить последующим образом. Нажатием правой клавиши по ячейке под столбцом с данными вызываем контекстное меню. Тут интересует поле Избрать из раскрывающегося перечня. То же самое можно создать, нажав комбинацию кнопок Alt+Стрелка вниз.

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

Обычный метод

Будет нужно выделить спектр ячеек, из которых будет сотворен выпадающий список, опосля чего же Вставка – Имя – Присвоить (Excel 2003). В наиболее новейшей версии (2007, 2010, 2013, 2016) перейдите на вкладку Формулы, где в разделе Определенные имена обнаружите клавишу Диспетчер имен.

Нажимаем клавишу Сделать, вносите имя, можно хоть какое, опосля чего же ОК.

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

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

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

Используем элементы управления

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

Изберите вкладку Разраб (для Excel 2007/2010), в остальных версиях будет нужно активировать эту вкладку на ленте в параметрах – Настроить ленту.

Перебегаем на эту вкладку – нажимаем клавишу Вставить. В элементах управления избираем Поле со перечнем (не ActiveX) и нажмите по значку. Нарисуйте прямоугольник.

Правой клавишей по нему – Формат объекта.

Дальше изберите Сформировывать по спектру и выделите требуемые поля.

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

Применение частей ActiveX

Все, как и в прошлом лишь избираем Поле со перечнем (ActiveX).

Отличия в последующем: элемент ActiveX может находиться в 2-ух вариантах — режиме отладки, позволяющем изменять характеристики, и — режиме ввода, можно лишь создавать подборку данных из него. Смена режимов осуществляется клавишей Режим Конструктора во вкладке Разраб.

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

Динамические выпадающие списки Excel

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

Говоря о динамических выпадающих перечнях, предполагается 2 варианта:

• Растущие;
• Зависимые.

Возрастающий выпадающий список

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

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

На любом листе располагается список будущих пт. Дальше обычным образом настраивается правило проверки данных: Вкладка «Данные» -> Область «Работа с данными» -> Клавиша «Проверка данных» -> Тип данных «Список». Для источника нужно сделать последующую формулу:

=СМЕЩ($A$1;;;СЧЁТЗ($A:$A)), где:

• $A$1 – ячейка со значением первого пт перечня;
• $A:$A – столбец с списком всех пт перечня.

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

Характеристики динамического выпадающего перечня:

Связанные (зависимые) выпадающие списки

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

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

• Бытовая_техника (пробел в заглавии специально заменен нижним подчеркиванием, т.к. диспетчер имен, который будет применен дальше, не воспринимает знак пробела);
• Электроника;
• Мебель.

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

В окончании задаем для ячеек сделанные нами связанные выпадающие списки при помощи проверки данных (вкладка «Данные» -> Область «Работа с данными»).

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

=ДВССЫЛ($A$1), где:

• $A$1 – ячейка с основным перечнем.

На этом зависимые выпадающие списки сделаны, а так смотрится готовый итог:

• < Вспять
• Вперёд >

• Проверка данных Excel

• Аспект Манна-Уитни
• Подключение MySQL в Excel
• Подключение Excel к SQL Server

Если материалы office-menu.ru Для вас посодействовали, то поддержите, пожалуйста, проект, чтоб я мог развивать его далее.

Комменты

Хороший денек, Александр!

Такое может быть выполнить средством макросов. Других методов я не понимаю.

ITGuides.ru

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

Пошаговое управление по созданию выпадающих списков в Excel

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

Видео по связанным выпадающим перечням Excel

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

Пример готового выпадающего перечня в Excel

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

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

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

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

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

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

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

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

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

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

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

Создание 1-го перечня на основании другого

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

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

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

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

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

Как работать с выпадающим перечнем в Excel

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

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

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

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

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

Техно сторона вопросца

Перед тем, как создать выпадающий список в Excel, сформируйте на листе в спектре ячеек нужные варианты. Проследите за тем, чтоб в этом списке не было пустых строк, по другому Эксель не сумеет сделать подходящий объект на листе. Введённые значения в строчках можно отсортировать по алфавиту. Для этого найдите в Ленте опций вкладку данные и нажмите на «Сортировку». Когда работа с данными у вас завершится, выделите подходящий спектр. В нём не обязано быть пустых строк, это принципиально! Программка не сумеет сделать список с пустым элементом снутри себя, поэтому что пустая строчка не будет восприниматься в качестве данных для выбора. При всем этом список данных вы сможете сформировать и на другом листе, не только лишь на том, где будет размещаться поле ввода. Допустим, вы не желаете, чтоб они были доступны для редактирования иным юзерам. Тогда имеет смысл расположить их на сокрытом листе.

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

При выделении данной ячейки рядом с ней покажутся несколько клавиш. Чтоб упростить юзеру задачку ввода, вы сможете при помощи данной клавиши задать имя ячейки. То же самое вы сможете создать выше, рядом с окном ввода формул есть соответственный пункт. Так список будет понятнее, ведь юзеру не придётся гадать по его значениям, что конкретно здесь необходимо избрать. Не считая того, в диалоговом окне можно внести сообщение-подсказку, которое будет отображено при наведении курсора на ячейку. Если ячейка не обязана оставаться пустой, уберите галочку «Игнорировать пустые значения». Флаг «Список допустимых значений» должен быть установлен в любом случае.

Удаление перечня

Когда выпадающий список больше не нужен, его можно удалить из документа. Для этого выделите ячейку на листе Excel, содержащую его, и перейдите в Ленте опций на вкладку «Данные» — «Проверка данных». Там во вкладке характеристик нажмите на клавишу «Очистить всё». Объект будет удалён, но при всем этом спектр данных остается без конфигураций, другими словами значения не будут удалены.

Заключение

Метод сотворения таковых объектов прост. Перед тем, как сделать в Excel раскрывающийся список, сформируйте список значений, по мере необходимости отформатируйте так, как для вас комфортно. Направьте внимание на 2 аспекта. 1-ый: длина спектра данных ограничена, пороговое значение 32767 частей, 2-ой: длина всплывающего окна будет определена длиной пт из списка. Располагая этот объект на страничке, вы упростите ввод данных от остальных юзеров. Их внедрение благоприятно влияет на скорость и точность работы, помогает упростить формулы, работающие в документе, и разрешает делему неодинакового форматирования текстовых данных. Но если вы используете в книжке Экселя Microsoft Share Point, сделать выпадающий список будет нереально, что соединено с ограничениями в работе публикующей программки.