Выпадающие списки в Microsoft Excel незаменимы при создании объемных таблиц, работе с базами данных. В чем же конкретно заключается удобство этого инструмента?
Если при заполнении таблицы некоторые данные периодически повторяются, нет необходимости каждый раз вбивать вручную постоянное значение — например, наименование товара, месяц, ФИО сотрудника. Достаточно один раз закрепить повторяющийся параметр в списке.
Ячейки списка защищены от введения посторонних значений, что снижает вероятность допустить ошибку в работе.
Таблица, оформленная таким образом, выглядит аккуратно.
В статье я расскажу, как в Экселе сделать выпадающий список в ячейк
е и как с ним работать.
Один из распространенных примеров использования выпадающих списков – интернет-магазины, в которых вся продукция распределена по категориям – такая структура облегчает пользователям поиск по сайту.
Рассмотрим наглядно:
Все товары, перечисленные в таблице, нужно отнести к категории «Одежда». Чтобы создать выпадающий список для этого перечня, потребуется выполнить следующие действия:
Выделить любую ячейку, в которой будет создан список.
Зайти на вкладку «Данные», в раздел «Проверка данных».
В открывшемся окне выбрать вкладку «Параметры», а в перечне «Тип данных» вариант – «Список».
В появившейся строке необходимо указать все имеющиеся наименования списка. Сделать это можно двумя способами: выделить мышкой диапазон данных в таблице (в примере – ячейки А1-А7) или вбить названия вручную через точку с запятой.
Выделить все ячейки с нужными значениями, и, щелкнув правой кнопкой мыши, выбрать в контекстном меню пункт «Присвоить имя».
В строке «Имя» указать наименование списка – в данном случае, «Одежда».
Выделить ячейку, в которой создан список, и вписать созданное имя в строку «Источник» со знаком «=» вначале.
Итоговый результат выглядит так. Это самый простой вариант выпадающего списка. В зависимости от версии Excel, действий может быть больше или меньше, но в целом, инструкция универсальна для любой программы.
Иногда возникает необходимость дополнить уже имеющийся перечень. В раскрывающемся списке все новые пункты отображаются автоматически при добавлении. Однако чтобы связать диапазон ячеек с добавленным вновь элементом, список требуется оформить в виде таблицы. Для этого нужно выделить диапазон значений, найти на вкладке «Главная» пункт «Форматировать как таблицу» и выбрать любой понравившийся стиль, например.
Для работы с большим количеством данных, проведения различных расчетов и оформления информации в виде таблиц пользователи в основном используют программу Excel. Для проведения расчетов и оформления все необходимые данные вводятся вручную в соответствующую ячейку на листе и для некоторого упрощения этого процесса можно сделать выпадающий список в Excel. Но его настройка также связана с первоначальным ручным вводом всех данных и далее мы более подробно рассмотрим, как сделать выпадающий список в Excel, причем, чтобы сам список допустимых значений находился на другом листе, не загромождая основной лист с данными.
Раскрывающийся список Excel создается в первую очередь для предотвращения ввода неверной информации, предоставляя для ввода только значения из созданного списка. Сделать выпадающий список в Экселе довольно просто. Самое сложное это подготовить полный список допустимых значений для ввода, оформив его в виде одного столбца или строки без пустых ячеек.
Рассмотрим для начала простой пример, как сделать выпадающий список в Excel 2010, создав список допустимых значений на том же листе. Для примера создадим список, в котором обозначен определенный диапазон чисел, ограничивающий устанавливаемые числа в необходимой нам ячейке. После этого устанавливаем курсор в том месте, где должен быть раскрывающийся список Excel, и переходим на вкладку «Данные» , где в секторе «Работа с данными» находим пункт «Проверка данных» . Если вы нажмете на стрелочку, появится дополнительное меню, в котором есть такой же пункт «Проверка данных» .
После выбора этого пункта появляется окошко, в котором необходимо настроить проверку вводимых данных в закладке «Параметры» . Для этого в графе «Тип данных:» выбираем «Список» , и нажав на соответствующий значок в графе «Источник:» , выделяем диапазон ячеек с нашим подготовленным списком допустимых значений. После этого при активации выбранной нами ячейки будет появляться стрелка, при нажатии на которую появляется список допустимых значений, которые можно выбрать.
При желании можно также настроить различные подсказки, что именно указывается в ячейке с выпадающим списком. При настройке выпадающего списка в Экселе доступно два пункта «Заголовок:» и «Сообщение:» , которые можно ввести в закладке «Сообщение для ввода» после выбора в меню пункта «Проверка данных» .
Теперь рассмотрим, как сделать выпадающий список в Excel 2010, если список допустимых значений для ввода расположен на другом листе этого же документа Эксель. На самом деле все очень просто. Проделываем все то же самое, как описано выше, и когда доходим до выбора источника данных, просто переходим на необходимый лист и выделяем диапазон ячеек.
Есть и более замороченный способ. Диапазону ячеек на другом листе можно присвоить имя. Для этого их выделяем, переходим на вкладку «Формулы» и выбираем «Присвоить имя» . Теперь для выбора диапазона допустимых значений достаточно указать имя диапазона ячеек после знака равенства.
Выпадающие списки упрощают и позволяют вводить данные в электронные таблицы. Просто нажмите стрелку и выберите опцию. Вы можете добавлять раскрывающиеся списки в ячейки Excel , содержащие такие параметры, как «Да» и «Нет», «Мужчины и женщины» или любой другой собственный список опций.
Добавить раскрывающийся список в ячейку в Excel достаточно просто, но процесс интуитивно не понятен. Выпадающие списки создаются с использованием функции проверки данных. Мы собираемся создать раскрывающийся список с набором возрастных диапазонов, чтобы показать вам, как это делается.
Для начала введите список диапазонов возраста в последовательные ячейки по столбцу или по строке. Мы ввели наши возрастные диапазоны в ячейки от A9 до A13 на том же рабочем листе, как показано ниже. Вы также можете добавить свой список параметров на другой рабочий лист в той же книге.
Теперь мы назовем наш диапазон ячеек, чтобы упростить их добавление в раскрывающийся список. Для этого выделите все ячейки, содержащие элементы раскрывающегося списка, а затем введите имя диапазона ячеек в поле «Имя» над сеткой. Мы назвали наш диапазон Возраст .
Теперь выберите ячейку, в которую вы хотите добавить раскрывающийся список, и перейдите на вкладку «Данные».
В разделе «Инструменты данных» на вкладке Данные нажмите кнопку Проверка данных .
Откроется диалоговое окно «Проверка данных». На вкладке «Параметры» выберите «Список» в раскрывающемся списке «Тип данных».
Теперь мы будем использовать Имя, которое мы назначили для диапазона ячеек, содержащих параметры нашего раскрывающегося списка. Введите =Возраст в поле «Источник» (если вы назвали диапазон ячеек как-то по-другому, замените «Возраст» на это имя). Убедитесь, что флажок Игнорировать пустые ячейки отмечен.
Вы можете добавить всплывающее сообщение, которое отображается, когда выбрана ячейка, содержащаяся в раскрывающемся списке. Для этого нажмите вкладку Сообщение для ввода в диалоговом окне «Проверка данных». Убедитесь, что флажок Отображать подсказку, если ячейка является текущей установлен. Введите сообщение в поля «Заголовок» и «Сообщение», а затем нажмите кнопку ОК .
Когда ячейка, содержащая раскрывающийся список, будет выбрана, вы увидите кнопку со стрелкой вниз справа от ячейки. Если вы добавили входное сообщение, оно отобразится под ячейкой. Кнопка со стрелкой вниз отображается только при выборе ячейки.
Нажмите кнопку со стрелкой вниз, чтобы вывести список параметров и выбрать один из них.
Если вы решите, что хотите удалить раскрывающийся список из ячейки, откройте диалоговое окно «Проверка данных», как описано выше, и нажмите кнопку Очистить все , которая доступна независимо от того, какая вкладка выбрана в диалоговом окне.
Параметры в диалоговом окне «Проверка данных» сбрасываются до значений по умолчанию. Нажмите ОК , чтобы удалить раскрывающийся список и восстановить ячейку по умолчанию.
Если была выбрана опция, то при удалении раскрывающегося списка ячейка заполнится значением этой опции.
Следуйте этому процессу, чтобы добавить раскрывающиеся списки в другие ячейки, по мере необходимости. Вы можете использовать один и тот же список для нескольких раскрывающихся списков.
Если у Вас много выпадающих списков, которые нужно добавить на лист, рекомендуем разместить списки параметров на отдельном рабочем листе Excel, а лист скрыть, чтобы предотвратить изменение параметров.
Добавление раскрывающегося списка в таблицу Excel поможет сделать ввод данных более быстрым, предлагая для пользователей список элементов, из которых следует выбрать требуемое вместо того, чтобы выполнять ввод информации каждый раз. Когда вы помещаете такие данные в ячейку электронной таблицы, в ней начинает отображаться стрелка. Вы вводите данные, нажимая на стрелку, после чего выбираете нужную запись. Можно настроить в программе "Эксель" раскрывающийся список в течение нескольких минут, тем самым значительно улучшив скорость ввода данных. Ниже представлено описание этого процесса.
Откройте таблицу, на странице которой вы желаете создать раскрывающийся список. Создайте перечень пунктов, которые должны быть отражены в списке. Впишите данные в той последовательности, в которой они будут впоследствии открываться. Записи должны быть выполнены в одной и той же строке или столбце, и не должны содержать пустых ячеек.
Чтобы сделать список нужных элементов в отдельном листе, следует щелкнуть ярлычок листа, где вы хотите ввести данные. Выберите тип и затем выделите содержимое, которое появится в списке. Нажмите правой кнопкой мыши по выделенному диапазону ячеек и введите название для диапазона в поле «Имя», после чего выберите «OK». Вы можете защитить или скрыть лист, чтобы другие пользователи не могли вносить изменения в список.
Нажмите на ячейку, которую вы выбрали для этой цели. Перейдите во вкладку «Данные» в ленте Microsoft Excel. Появится под названием «Проверка данных». Перейдите во вкладку «Настройки» и нажмите «Список» из меню «Разрешить», расположенного в раскрывающемся списке. Нажмите на кнопку-переключатель в меню «Источник». Выберите список элементов, которые вы хотите включить в ваш раскрывающийся список в «Экселе».
Если вы создали имя диапазона в поле «Источник», введите значок равенства, после чего впишите название диапазона.
Установите или снимите флажок в графе «Игнорировать пустые» в зависимости от того, может ли ячейка, содержащая раскрывающийся список, оставаться пустой.
Перейдите на вкладку "Ввод сообщения", чтобы отобразить окно сообщения, открывающееся при нажатии Убедитесь, что флажок напротив пункта меню "Показать входное сообщение при выборе ячейки" установлен, и введите заголовок. Так вы выполните все указания по тому, как в "Экселе" сделать раскрывающийся список.
Перейдите на вкладку "Ошибка оповещения" для отображения сообщения об ошибке, если будут введены неправильные данные. Убедитесь, что предупреждение «Показать ошибку» включено путем установки соответствующего флажка.
Нажмите "OK" для того, чтобы были сохранены критерии проверки и создан раскрывающийся список.
Если выпадающая запись в списке больше, чем размер клетки, содержащей данный список, вы можете изменить ширину ячейки, чтобы просматривать весь текст.
Чтобы удалить раскрывающейся список, выберите ячейку, содержащую его. Перейдите на вкладку «Данные» в ленте Microsoft Excel. Нажмите кнопку «Проверка данных» из группы «Работа с данными». Перейдите на вкладку «Настройки», нажмите кнопку «Очистить все», а затем - «OK».
Как можно увидеть, руководство о том, как в «Экселе» сделать раскрывающийся список, является достаточно простым. Данная инструкция не требует каких-либо специальных знаний, необходимо лишь на начальном уровне уметь работать с электронными таблицами. Также стоит отметить, что в различных версиях программы «Эксель» пункты меню могут иметь некоторые различия с вышеуказанными, это следует учитывать при работе.
При заполнении таблиц нередко приходится вводить повторяющиеся значения. Excel позволяет существенно облегчить эту работу и при этом уменьшить количество ошибок при вводе. С этой целью используются выпадающие списки. Мы создаем необходимый список один раз, а потом в любой ячейке вставляем выпадающий список, из которого можем выбирать нужное значение.
Для примера я создал список городов Московской области. Выделяем список и создаем именованный диапазон. Для этого после щелчка правой кнопки мыши выбираем в контекстном меню «Имя диапазона».
Задаем имя «Город_М_О» и жмем «ОК».
Теперь переходим в ту ячейку, где мы хотим иметь выпадающий список и переходим на закладку «Данные» верхней панели. Здесь нам понадобится кнопка «Проверка данных», расположенная в группе «Работа с данными». Выбираем пункт «Проверка данных».
В появившемся окне выбираем тип данных «Список» и в поле «Источник» вводим «=Город_М_О», то есть заданное нами имя диапазона, который содержит список.
Вот собственно и все. В заданной нами ячейке появился выпадающий список, из которого мы можем выбирать любое значение. При необходимости мы можем использовать тот же список и в других ячейках.
А исходный список мы можем и спрятать, к примеру, на другом листе, чтобы не портил вида таблицы. Система все равно будет работать, потому что список у нас находится в именованном диапазоне, видимом в пределах всей книги Excel.
Здесь, чтобы присвоить имя диапазону нам потребуется зайти в меню «Вставка»
И окно присвоения имени выглядит немного по-другому.
Так же переходим в нужную нам ячейку и в меню «Данные» выбираем «Проверка». А открывшееся окно будет таким же, как и в Excel 2007.
Покоряйте Excel и до новых встреч!
