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

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

Делаем пошагово

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

Реализуется это просто:

Сначала формируете сам список данных.

Выделяете те поля, которые планируете заполнять данными.

Затем заходите во вкладку Данные, команда Проверка данных – Тип данных (Список), в поле Источник ставите диапазон.

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

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

Отмечаете галочкой поле «Игнорировать пустые ячейки».

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

Нюанс – при таком методе в ячейку можно ввести значения, ТОЛЬКО содержащиеся в списке.

Нюансы

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

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

Второй способ

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

Для этого надо зайти на вкладку Разработчик (Excel 2007-2010). По умолчанию эта вкладка не показывается. Чтобы ее открыть надо зайти в Параметры (Офис/Параметры Excel/Основные/Показывать вкладку "Разработчик" на ленте).

На вкладке "Разработчик" есть кнопка "Вставить", где есть элементы формы. Нам нужно "Поле со списком".

Обведите мышкой контур кнопки, затем правой кнопкой мыши нажимаете на получившемся поле и выбираете "Формат объекта".

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

Потом нажимаете "Ок" и пользуетесь.

«Глаза боятся, а руки делают»

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

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

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

Техническая сторона вопроса

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

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

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

Удаление списка

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

В данной статье рассмотрим, как создать раскрывающийся список в excel 2007. Возьмем пример, когда нам нужно в ячейке выбрать заданные значения от 1 до 5 из выпадающего списка. Создаем сам список и выделяем его левой кнопкой мыши. Кликаем правой кнопкой мыши в выделенной области и выбираем пункт Имя диапазона .

В открывшемся окошке в поле Имя вводим название нашего списка, назовем Значение . В поле Область из выпадающего списка выберем Книга (либо номер Листа к которому желаете применить список). Жмем Ок.

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

Список создан. Теперь применим этот список к ячейке.

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

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

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

Можно создать , минуя присвоение названия списка. То есть:

создаем сам список;
переходим в Ленте на вкладку Данные , жмем кнопку Проверка данных;
в открывшемся окошке, во вкладке Параметры в поле Тип данных выбираем Список;
в поле Источник кликаем левой кнопкой мыши для активации данного поля. Далее выделяем ячейки образующие список;
жмем Ок.

Все, раскрывающийся список в excel 2007 готов.

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

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

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

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

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

В разделе «Инструменты данных» на вкладке Данные нажмите кнопку Проверка данных .

Откроется диалоговое окно «Проверка данных». На вкладке «Параметры» выберите «Список» в раскрывающемся списке «Тип данных».

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

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

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

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

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

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

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

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

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

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

Кликните по кнопке ниже для загрузки файла с примерами выпадающих списков в Excel:

Видеоурок

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

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

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

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

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

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

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

Как сделать выпадающий список в 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 .

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

нажмите левой клавишей мыши на ячейку с выпадающим списком, которую вы хотите скопировать;
CTRL+C ;
выделите ячейки в диапазоне А2:А6 , в которые вы хотите вставить выпадающий список;
нажмите сочетание клавиш на клавиатуре CTRL+V .

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

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

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

Нажмите “ОК “

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

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

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

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

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