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

В статье мы рассмотрим, как сделать выпадающие списки различного вида в таблице Эксель.

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

Для этого, в ячейки А1:А7 вписываем данные, которые будут отображаться в списке. Теперь выделим ячейку, в которой создадим выпадающий список – В2 .

Переходим на вкладку «Данные» и кликаем по кнопочке «Проверка данных» .

На вкладке «Параметры» в поле «Тип данных» выбираем «Список» . В поле «Источник» можно ввести значения различными способами:

1 – вводим значения для списка вручную, через точку с запятой;

2 – указываем диапазон ячеек, в которые введены данные для выпадающего списка;

3 – выделяем ячейки с именами, кликаем по ним правой кнопкой мыши и выбираем из меню «Присвоить имя» .

Выделяем ячейку В2 и в поле «Источник» ставим «=» , затем пишем созданное имя.

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

Если у Вас есть заголовок для столбца, и значениями нужно заполнять каждую строку, то выделите не одну ячейку, а диапазон ячеек – В2:В9 . Тогда можно будет выбирать из выпадающего списка нужное значение в каждой ячейке.

Добавляем значения в выпадающий список – динамический список

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

Выделяем диапазон ячеек – D1:D8 , затем на вкладке «Главная» нажимаем «Форматировать как таблицу» и выбираем любой стиль.

Подтверждаем расположение данных и ставим галочку в поле «Таблица с заголовками» .

Вверху пишем заголовок таблицы – «Сотрудники» , и заполняем ее данными.

Выделяем ячейку, в которой будет выпадающий список и кликаем по кнопочке «Проверка данных» . В следующем окне, в поле «Источник» , пишем следующее: =ДВССЫЛ(«Таблица1[Сотрудники]») . У меня одна таблица на листе, поэтому пишу «Таблица1» , если будет вторая – «Таблица2» , и так далее.

Теперь добавим новое имя сотрудника в наш список: Ира. В выпадающем списке оно появилось. Если мы удалим любое имя из таблицы, из списка оно тоже удалится.

Выпадающий список со значениями с другого листа

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

На Листе 2, выделяем одну ячейку или диапазон ячеек, затем кликаем по кнопочке «Проверка данных» .

Переходим на Лист 1, ставим курсор в поле «Источник» и выделяем нужный диапазон ячеек.

Теперь можно дописывать имена на Листе 1, они будут добавляться в выпадающие списки на Листе 2.

Создаем зависимые выпадающие списки

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

Первый называем «Имя» , второй – «Фамилия» , третий – «Отч» .

Сделаем еще один диапазон, в котором будут прописаны присвоенные имена. Назовем его «Сотрудники» .

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

В поле «Тип данных» выберите «Список» , в поле источник – или введите «=Сотрудники» , или выделите диапазон ячеек, которому присвоено имя.

Первый выпадающий список создан. Теперь в ячейке F2 создадим второй список, который должен зависеть от первого. Если в первом выберем «Имя» , во втором отобразится список имен, если выберем «Фамилия» – список фамилий.

Выделяем ячейку и кликаем по кнопочке «Проверка данных» . В поле «Тип данных» выбираем «Список» , в поле источник прописываем следующее: =ДВССЫЛ($Е$1) . Здесь Е1 – это ячейка с первым выпадающим списком.

По такому принципу можно делать зависимые выпадающие списки.

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

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

Оценить статью:

(1 оценок, среднее: 5,00 из 5)

Вебмастер. Высшее образование по специальности "Защита информации".. Автор большинства статей и уроков компьютерной грамотности

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

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

Создаем раскрывающийся список в Экселе: способ первый

В данном случае мы воспользуемся инструментом «Проверка данных», который сделает часть работы по созданию списка за нас. Итак, приступим:

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

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

Создаем раскрывающийся список в Excel: способ второй

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

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

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

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

Для начала на отдельном листе (это не обязательно) разместим список допустимых значений в одном столбце или одной строке (рис. 1а); см. также Excel-файл, лист «Список».

Рис. 1. Список фамилия: (а) в произвольном порядке; (б) в алфавитном порядке.

Скачать в формате , примеры в формате

Этот перечень фамилий будет появляться в раскрывающемся списке в том порядке, в котором они расположены в этом столбце. Понятно, что для удобства поиска их лучше отсортировать по алфавиту (рис. 1б).

Присвоим нашему списку имя диапазона. Для этого выделим диапазон; в нашем случае это область А2:А21 и введем имя диапазона, как показано на рис. 2; в нашем случае – это «фамилии»:

Рис. 2. Присвоение диапазону имени

Выберем область, в которой будем вводить фамилии (см. Excel-файл, лист «Ввод»). В нашем примере – А2:А32 (рис. 3). Перейдем на вкладку Данные, группу Работа с данными, выберем команду Проверка данных:

Рис. 3. Проверка данных

В диалоговом окне «Проверка вводимых значений» перейдем на вкладку Параметры (рис. 4). В поле «Тип данных» выберем «Список». В поле «Источник» укажем: (а) область ячеек, в которых хранится список; этот вариант подходит в том случае, если список расположен на том же листе Excel; (б) имя диапазона; этот вариант может использоваться как в том случае, когда список расположен на том же листе Excel, так и в том случае, если список расположен на другом листе Excel (как в нашем случае). В обоих случаях следует убедиться, что перед ссылкой или именем стоит знак равенства (=).

Рис. 4. Выбор источника данных для списка: (а) на том же листе; (б) на любом листе

И еще о двух опциях на вкладке «Параметры»:

• Игнорировать пустые ячейки. Если галочка установлена, Excel позволит оставить ячейку пустой. Если галочка снята, то из ячейки можно выйти только после выбора одной из фамилий списка. Особенность опции заключается в том, что перемещаться между ячейками (например клавишей Ввод или стрелками вверх / вниз) Excel позволит, а вот начать набор, потом стереть все символы и перейти в другую ячейку нельзя.
• Список допустимых ячеек. Если галочки нет, то, когда вы установите курсор в ячейку для ввода, значок списка не появится рядом с ячейкой, и соответственно выбрать из списка не получится. Хотя все остальные свойства работы со списком будут действовать, и Excel не позволит вам ввести произвольное значение в ячейку.

Перейдем в окне «Проверка вводимых значений» на вкладку «Сообщения для ввода». Поставим галочку в поле «Отображать подсказку, если ячейка является текущей». Введем в соответствующие поля заголовок и текст сообщения (рис. 5). В последующем, когда пользователь встанет на одну из ячеек области ввода (в примере на рис. 5 – в ячейку А6), отобразится созданное нами сообщение.

Рис. 5. Установка Сообщения для ввода

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

Рис. 6. Установка Сообщения об ошибке

Допустимые типы сообщений об ошибке (рис. 7):

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

Рис. 7. Выбор типа сообщения об ошибке

Некоторые замечания. 1. Если вы ввели в окне Сообщение вкладки Сообщение об ошибке слишком длинный текст, то окно сообщения об ошибке будет слишком широким (как на рис. 7); используйте перенос строки Shift + Enter в том месте сообщения, где вы хотите разделить строки (рис. 8).

Рис. 8. Окно сообщения об ошибке уменьшенной ширины

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

3. Максимальное число записей в раскрывающемся списке ограничено, правда, не слишком сильно:), а именно числом 32 767.

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

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

При этом в том же окне появится поле «Источник». Введите в него символ «=» и имя выделенного диапазона, которое было задано ячейкам с данными. Для применения установленных параметров нажмите «Enter» или «Ок». Это вариант самого простого выпадающего списка.

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

Выберите на открывшейся панели элементов управления значок «поле со списком» - это и есть выпадающий список. Нарисуйте мышкой прямоугольник в форме поля. Выделите правой кнопкой мыши нарисованный список и укажите команду «Формат объекта…».

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

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

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

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

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

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

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

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

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

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

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