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

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

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

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

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

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

Перейдите на вкладку Данные на ленте, а затем — Проверка данных.

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

На вкладке Параметры в поле Разрешить выберите Список.

Выберите в поле Источник , а затем выберите диапазон списка. В примере данные находятся на листе «Города» в диапазоне A2:A9. Обратите внимание на то, что строка заголовков отсутствует в диапазоне, так как она не является одним из вариантов, доступных для выбора.

Если пользователи не могут оставить ячейку пустой, проверка пустое поле Игнорировать.

Установите флажок в раскрывающемся списке В ячейке .

Перейдите на вкладку Входное сообщение .

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

Перейдите на вкладку Оповещение об ошибке .

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

Не знаете, какой вариант выбрать в поле Стиль ?

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

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

Примечание: Если вы не добавили заголовок и текст, по умолчанию выводится заголовок «Microsoft Excel» и сообщение «Введенное значение неверно. Набор значений, которые могут быть введены в ячейку, ограничен».

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

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

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

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

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

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

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

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

Выделите ячейки, для которых нужно ограничить ввод данных.

На вкладке Данные в разделе Сервис выберите Проверка данных или Проверка.

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

Перейдите на вкладку Параметры , а затем во всплывающем меню Разрешить выберите Список.

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

Диалоговое окно свернется, чтобы было видно весь лист.

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

Можно также ввести значения, разделенные запятой, непосредственно в поле Источник .

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

Можно указать собственное сообщение об ошибке, которое будет отображаться при вводе недопустимых данных. На вкладке Данные выберите Проверка данных или Проверить, а затем перейдите на вкладку Оповещение об ошибке .

См. также

На новом листе введите данные, которые должны отображаться в раскрывающемся списке. Желательно, чтобы элементы списка содержались в таблице Excel.

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

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

Перейдите на вкладку Данные на ленте, а затем выберите Проверка данных.

На вкладке Параметры в поле Разрешить выберите Список.

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

Фрукты;Овощи;Зерновые культуры;Молочные продукты;Перекусы

Если пользователи не могут оставить ячейку пустой, проверка пустое поле Игнорировать.

Установите флажок в раскрывающемся списке В ячейке .

Перейдите на вкладку Входное сообщение .

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

Перейдите на вкладку Оповещение об ошибке .

Если вы хотите, чтобы сообщение отображалось, когда кто-то вводит что-то, чего нет в вашем списке, проверка флажок Показывать оповещение, выберите параметр в поле Тип и введите название и сообщение. Если вы не хотите, чтобы сообщение отображалось, снимите этот флажок.

Нажмите кнопку ОК.

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

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

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

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

Вариант 1: Группировка существующего списка

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

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

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

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

В качестве источника задайте те самые ячейки, выделив их левой кнопкой мыши. Нажмите «‎ОК» для применения настроек.

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

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

Вариант 2: Ручное добавление элементов списка

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

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

Выберите в качестве типа данных список, а в «‎Источник» перечислите значения, которые желаете в него добавить. Используйте «‎;», чтобы отделить одно значение от другого.

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

Вариант 3: Выбор массива с именем

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

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

Измените имя массива на любое, начинающееся с буквы.

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

В качестве источника укажите созданный массив, написав его название после «‎=».

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

Вариант 4: Создание выпадающего списка из таблицы

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

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

Выберите подходящий для вас тип оформления.

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

Вы автоматически окажетесь на вкладке «‎Конструктор таблицы», где можете изменить ее имя на любое удобное, начинающееся с буквы.

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

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

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

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

Основы Excel: работаем с выпадающим списком. Пошаговая инструкция со скриншотами

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

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

Разберёмся на примере с каталогом авто, как сделать выпадающие списки. У нас есть перечень автомобилей. Нужно заполнить столбцы с их характеристиками: тип коробки передач, тип привода и положение руля. Значения будут повторяться, поэтому выпадающие списки ускорят заполнение таблицы. Можно заполнить её полностью в два шага: сначала создаём значения выпадающего списка, потом выбираем нужные.

Шаг 1

Создаём выпадающий список

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

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

Переходим на вкладку «Данные» и кликаем по кнопке «Проверка данных».

Открывается окно «Проверка данных». На вкладке «Параметры» в поле «Разрешить» выбираем пункт «Список».

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

На листе с данными для списка выделяем столбец с нужными параметрами. В нашем случае значения столбца «Коробка передач» — автомат, механика, вариатор.

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

Шаг 2

Заполняем таблицу готовыми данными

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

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

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

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

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

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

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

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

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

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

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

Шаг 1. Перейдите во вкладку «Данные», которая расположена на верхней панели, затем в блоке «Работа с данными» выберите инструмент проверки данных (на скриншоте показано, какой иконкой он изображен).

Шаг 2. Теперь откройте самую первую вкладку «Параметры», и установите «Список» в перечне типа данных.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Зависимые раскрывающиеся списки

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

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

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