Ввод данных из списка значений. Часть 1: Выпадающий список в EXCEL
history 4 декабря 2012 г.
- Группы статей
- Выпадающий список
- Контроль ввода ОШИБочных значений
- Проверка данных
При заполнении ячеек данными иногда необходимо ограничить возможность ввода определенным списком значений. Например, при заполнении ведомости ввод фамилий сотрудников с клавиатуры можно заменить выбором из определенного заранее списка (табеля).
Одним из вариантов заполнения ячеек является выбор значений из заранее определенного списка в MS EXCEL. Предположим, что в таблицу ведомости необходимо вводить фамилии сотрудников. Чтобы не ошибиться с написанием фамилий можно предварительно создать список всех сотрудников организации, а заполнение ведомости свести к выбору фамилии из этого списка.
Инструмент Проверка данных (Данные/ Работа с данными/ Проверка данных ) с условием проверки Список , как раз предназначен для решения нашей задачи: с помощью него можно создать Выпадающий (раскрывающийся) список (см. Файл примера ).
- создайте список фамилий сотрудников, например в диапазоне D1:D10 ;
- выделите в ячейку D1 заголовок Сотрудники ;
- выделите диапазон D2:D10 , в поле Имя , слева от Строки формул введите Сотрудники и нажмите ENTER , либо, выделив диапазон D1:D10 через команду меню Создать из выделенного фрагмента ( Формулы/ Определенные имена ).
СОВЕТ : Если в будущем потребуется пополнять список сотрудников, то придется вручную модифицировать границы именованного диапазона. Как обойти это неудобство читайте в статье Динамический диапазон .
Теперь создадим Выпадающий список для ввода фамилий в ведомость:
- выделите ячейки ведомости, куда будут вводиться фамилии сотрудников, например А2:А5 ;
- вызовите инструмент Проверка данных ( Данные/ Работа с данными/ Проверка данных );
- установите условие проверки Список ;
- в поле Источник введите =Сотрудники ;
Теперь при выделении любой ячейки из диапазона А2:А5 , справа от ячейки будет появляться кнопка со стрелкой, нажав на которую можно выбрать необходимую фамилию.
Проверку данных можно настроить так, чтобы при вводе фамилий не из списка появлялось окно с описанием ошибки (для этого во вкладке Сообщение для ввода введите необходимый текст).
Недостатком этого решения является то, что у пользователя есть потенциальная возможность ввести в ведомость повторяющиеся фамилии. Для того, чтобы контролировать появление повторяющихся фамилий, можно использовать идеи из статьи Ввод данных из списка значений. Часть 2. Выпадающий список с контролем дублирования .
Как добавить выпадающий список в ячейку Excel
В различных случаях вам может пригодится умение добавлять выпадающие списки в ячейки Excel, и в сегодняшней статье рассмотрим как это делать.
1.Откройте таблицу Excel, в которой хотите сделать ячейки с выпадающими списками. Перечень значений для выпадающего списка сначала нужно где-то записать, можно на этом же листе, но далеко внизу или создать дополнительный лист (как мы делаем в нашем примере) и туда записать значения для создания выпадающего списка. Нажимаем на” +” внизу, чтобы добавить еще один лист.
2.Открываем созданный лист (нажатием по нему левой клавишей мыши) => в любом месте в столбик начинаем вводить значения, которые нужны будут в списке. К примеру, мы в первом столбике начиная с первой строки (можно делать в любом столбике и начиная с любой строки) вводим в каждой ячейке нужные значения (которые будут отображаться в выпадающем списке).
3.Когда введете все значения, выделите ячейки с данными (используя мышь или удерживая клавишу shift и двигаясь стрелками в нужном направлении). Когда ячейки с данными выделите – выше введите имя для диапазона выбранных ячеек и нажмите Enter (смотрите рисунок). В нашем примере мы пишем имя “Цена” и нажимаем Enter.
4.Откройте лист и выберите ячейку в которую вы хотите добавить выпадающий список => на верхней панели выберите “Данные” => откройте левой клавишей мыши “Проверка данных”.
5.В поле “Тип данных” из выпадающего списка выберите “Список”
6.Снимите галочку с “Игнорировать пустые ячейки” и в поле “Источник” напишите = и имя, которое вы присвоили диапазону ячеек в 3ем пункте. Мы, в данном примере, диапазону ячеек присвоили имя “Цена”, по этому в поле “Источник” нам нужно написать =Цена
7. Перейдите во вкладку “Сообщения для ввода”, здесь вы можете написать сообщение, которое будет отображаться при выборе ячейки со списком. После ввода нужного сообщения нажмите “ОК”.
Теперь если вы выберите ячейку для которой создавали выпадающий список, то справа от нее вы увидите стрелку, также если вы в 7ом пункте задали сообщение – оно будет отображаться ниже . Стрелка и сообщение будут отображаться только когда ячейка выбрана.
Нажав на стрелку рядом с ячейкой вы увидите список, который создали в 2ом пункте и сможете выбрать нужное.
Если в будущем у вас возникнет надобность убрать выпадающий список из какой-то ячейки: выберите ячейку с выпадающим списком => на верхней панели выберите “Данные” => откройте левой клавишей мыши “Проверка данных” (описывалось и изображение было в 4ом пункте).
Нажмите левой клавишей мыши на “Очистить всё”.
Пользуясь инструкцией выше, вы можете создать различные выпадающие списки и добавить их в нужные ячейки. На сегодня всё, если у вас есть дополнения – пишите комментарии! Удачи Вам 🙂