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

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

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

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

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

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

1. Создайте именованный диапазон: Этот шаг необходим для создания списка, который мы будем использовать в раскрывающемся списке.
2. Добавить проверку данных: Это не столь очевидный шаг, когда вы заполняете ячейку для отображения выпадающего списка.
3. Добавить именованный диапазон к проверке данных: Наконец, вы соберете все вместе и определите источник раскрывающегося списка (т. Е. Ранее названный вами диапазон).
4. Установить входное сообщение для проверки данных: Этот шаг не является обязательным. Это позволяет вам добавить всплывающее сообщение для руководства ваших пользователей электронных таблиц.

Я люблю Excel достаточно, что даже простая проверка данных + условное форматирование радует меня

– х – лилия (@ungilded) 25 октября 2017 г.

Excel не легок

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

1. Создайте именованный диапазон

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

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

2. Добавьте проверку данных

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

Убедитесь, что настройки вкладка активна на Проверка данных чат.

3. Добавить именованный диапазон к проверке данных

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

=Food

Заменить «питаниеС любым именем, которое вы дали вашему диапазону клеток. щелчок Ok,

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

Советы Тома для Excel: используйте проверку данных вместо комментария для выбранной ячейки: Alt + D + L> вкладка «Входное сообщение»> введите msg> Ck Показать входные сообщения…

– Том Уртис / Атлас (@TomUrtis) 22 января 2017 г.

4. Установите входное сообщение для проверки данных

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

щелчок Ok,

Когда вы выбираете ячейку

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

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

Расширенные параметры выпадающего списка

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

Изменить или удалить именованный диапазон

Если вам нужно отредактировать или удалить именованный диапазон, вы должны использовать Имя менеджера, Нажмите на Формулы вкладка, а затем нажмите Имя менеджера в Определенные имена раздел.

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

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

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

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

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

Зависимый раскрывающийся список – это список, в котором параметры меняются в зависимости от выбора в другом раскрывающемся списке.

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

Только что узнал, как создать зависимый выпадающий список в Excel, например, cell1-state, cell2-city в этом состоянии. РЕБЯТА! Это меняет жизнь.

– Жизнь в Делойте (@lifeatdeloitte) 4 февраля 2014 г.

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

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

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

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

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

Введите следующий текст в Источник коробка. Заменить «$ B $ 2»Со ссылкой на ячейку, содержащую ваш основной выпадающий список. Держите знаки доллара в ячейке ссылки. Это указывает на абсолютную ссылку на ячейку, которая не изменится, даже если вы скопируете или переместите формулу, которая ссылается на эту ячейку.

=INDIRECT($B$2)

Функция INDIRECT возвращает ссылку, указанную в текстовой строке, в данном случае текст из опции, выбранной в основном раскрывающемся списке в ячейке B2. Например, если вы выберете китайский язык из основного выпадающего списка, = ДВССЫЛ ($ B $ 2) возвращает китайский язык ссылка. В результате второй выпадающий список содержит китайский язык Предметы.

щелчок Ok,

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

Скопируйте и вставьте выпадающий список

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

Если вы просто хотите скопировать выпадающий список с проверкой данных, но НЕ с форматированием, выберите ячейку и скопируйте ее, как правило, используя Ctrl + C, Затем перейдите к Дом вкладка и нажмите Вставить в буфер обмена раздел. Выбрать Специальная паста,

На Специальная паста диалоговое окно, выберите Проверка в Вставить раздел. Затем нажмите Ok,

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

Замечания: Будьте осторожны при работе с выпадающими списками в Excel. При копировании ячейки, в которой нет раскрывающегося списка, в ячейку, содержащую раскрывающийся список, раскрывающийся список теряется. Excel не предупреждает вас и не запрашивает подтверждение действия. Тем не менее, вы можете отменить действие, используя Ctrl + Z,

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

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

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

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

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

щелчок Ok,

Наши два выпадающих списка выбраны.

Теперь вы можете отформатировать эти ячейки

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

Сделать стрелку выпадающего списка всегда видимой

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

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

Начать, скачать раскрывающийся-arrow.png файл (щелкните правой кнопкой мыши по этой ссылке и выберите Сохранить ссылку как). Затем выберите ячейку справа от выпадающего списка и перейдите к Вставить Вкладка.

Затем нажмите иллюстрации и выберите Фотографий,

На Вставить картинку диалоговое окно, перейдите туда, где вы сохранили раскрывающийся-arrow.png файл и выберите файл. Затем нажмите Вставить,

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

Удалить выпадающий список из ячейки

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

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

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

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

Заставьте Dropdown Lists работать на вас

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

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

еще?