Как сделать выпадающий список в Excel с помощью проверки данных

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

Составление исходных списков

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

• картофель, свекла, морковь, редис;
• петрушка, укроп, щавель, шпинат;
• клубника, вишня, черешня, крыжовник.

Первые четыре слова заносим в столбец, начиная с A1, вторые – с B1, третьи – с C1.

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

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

Теперь собственно формируем выпадающий список. Выбираем, например, ячейку D1 на другом листе открытой книги, открываем пункт меню: "Данные" – "Проверка данных". В открывшейся форме "Проверка вводимых значений" во вкладке "Параметры" в поле "Тип данных" выбираем "Список". Ставим курсор в поле "Источник", набираем знак равенства и нажимаем клавишу F3. В появившемся меню "Вставка имени" выбираем имя нужного нам списка, например, "овощи", затем нажимаем "ОК". Проверяем, чтобы стояли галочки в позициях "Игнорировать пустые ячейки" и "Список допустимых значений". Это обеспечивает возможность не заполнять это поле данными и вводить только данные из списка с указанным именем. Жмем "ОК".

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

Другие варианты

При заполнении поля "Источник" формы "Проверка вводимых значений" можно применить еще два варианта.

1. Поставив курсор в это поле, выделить на данном листе книги область допустимых значений списка. Формула появится в поле после знака равенства. Как сделать выпадающий список в Excel с другого листа в этом случае? Надо аналогичный диапазон ячеек выделить на этом листе, а затем, поставив курсор в поле после знака равенства, написать наименование листа со списком и поставить восклицательный знак. Внимание: название листа не должно содержать пробелов!
2. Перечислить допустимые значения в поле, разделяя их точкой с запятой. Это самый простой способ, который не допускает обращение к спискам на другом листе книги.

Расширение диапазона допустимых значений

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

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

Таким образом, мы получили выпадающий список в Excel.

Двойная ссылка

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

Теперь (можно и на другом листе данной книги) выбираем, например, ячейку G1 и задаем для нее выпадающий список со ссылкой на имя "Список 1". Следующую ячейку, например, H1, заполняем с учетом выбранного значения в ячейке G1. Для этого в ячейке H1 формируем выпадающий список, как указано ранее, в поле "Источник" выбираем ячейку G1, но дорабатываем запись в этом поле, добавляя после знака равенства ДВССЫЛ, а остальную часть формулы взяв в круглые скобки: =ДВССЫЛ($G$1).

Теперь при выборе в ячейке G1, например, значения "зелень", для заполнения ячейки H1 будет предложен выбор из значений этого списка.

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