как создать поле со списком в excel
history 10 ноября 2014 г.
Для вставки элементов управления на лист необходимо отобразить вкладку Разработчик.
Вставка Поля со списком
Через меню Разработчик/ Элементы управления/ Вставить выберем левой клавишей мыши элемент Поле со списком (см. рисунок ниже).
После этого выпадающее меню закроется, а курсор вместо обычного толстого крестика
превратится в тонкий крестик.
Удерживая левую клавишу мыши проведите курсором вправо и немного вниз, элемент Поле со списком будет помещен на лист.
Выделение Поля со списком
Перемещение Поля со списком и изменение его размеров
Если навести курсор на выделенный элемент Поле со списком (курсор примет форму 4-х направленных в разные стороны стрелок), затем нажать и удерживать левую кнопку мыши, то можно его переместить. Удерживая клавишу ALT можно выровнять Поле со списком по границам ячеек. Выделенный элемент также можно перемещать стрелками с клавиатуры.
Если навести курсор на углы прямоугольника или на маленькие кружки на границе, то можно изменить его размер.
Заполняем Поле со списком элементами
Введите в поле Формировать список по диапазону ссылку на вышеуказанный диапазон.
Нажмите ОК, Поле со списком заполнится элементами.
Теперь пользователь может выбрать требуемый месяц, но Поле со списком пока не способно вернуть выбранное значение в ячейку. О том, как связать Поле со списком с ячейкой читайте ниже.
Связываем Поле со списком с ячейкой
Как было сказано выше, все Элементы управления формы возвращают значение. Это значение помещается в ячейку определенную пользователем. Чтобы связать Элемент управления с ячейкой, кликните на него ПРАВОЙ клавишей мыши, в появившемся контекстном меню выберите Формат объекта. Появится диалоговое окно, выберите вкладку Элемент управления (если такая вкладка отсутствует, то Вы вставили Элемент ActiveX, а не Элемент управления формы, об этом см. выше).
Использование Поля со списком
Поле со списком удобно для выбора единственного значения из заранее подготовленного списка. Выбранное значение может быть использовано для вывода соответствующих значений из той же строки. Например, если в таблице продаж содержатся объемы продаж по месяцам (диапазон F2:G13 на рисунке ниже), то выбирая в Поле со списком месяц, можно вывести соответствующий объем продаж (см. ячейку В3 ).
Необходимо помнить, что Поле со списком возвращает в связанную ячейку не сам элемент, а его позицию в списке (для месяца Май на картинке выше Поле со списком вернуло значение 5). Поэтому, чтобы вывести выбранный месяц, потребуется формула =ИНДЕКС(F2:F13;B1) (ячейка В2 ).
Формула =ИНДЕКС(G2:G13;B1) позволяет вывести объем продаж для выбранного месяца (ячейка В3 ).
Условное форматирование использовано для отображения в таблице выбранного месяца и его продаж.
Имя Элемента управления
Зачем нам знать имя элемента управления? Если Вы не планируете управлять Поля со списком из программы VBA, то имя может потребоваться только для настройки его отображения на листе. Об этом читайте ниже.
Прячем Поле со списком на листе
Включите Область выделения ( Главная / Редактирование/ Найти и выделить )
В Области выделения можно управлять отображением не только Элементов управления, но и других объектов на листе, например рисунков.
Нажмите на изображение глаза напротив имени объекта и объект исчезнет/ появится.
Добавление списка или списка на лист в Excel
Если вам нужно отобразить список значений, которые сможет выбирать пользователь, добавьте на лист список.
Добавление списка на лист
Создайте перечень элементов, которые должны отображаться в списке, как показано на рисунке.
На вкладке Разработчик нажмите кнопку Вставить.
Примечание: Если вкладка Разработчик не отображается, на вкладке Файл выберите Параметры > Настроить ленту. В списке Основные вкладки установите флажок для вкладки Разработчик и нажмите кнопку ОК.
В разделе Элементы управления формы выберите элемент управления Список (элемент управления формы).
Щелкните ячейку, в которой нужно создать список.
Нажмите кнопку Свойства и на вкладке Элемент управления задайте необходимые свойства:
В поле Формировать список по диапазону введите диапазон ячеек, содержащий список значений.
Примечание: Если нужно отобразить в списке больше элементов, можно изменить размер шрифта для текста.
В поле Связь с ячейкой введите ссылку на ячейку.
Совет: Выбираемая ячейка содержит число, связанное с элементом, выбранным в списке. Его можно использовать в формуле для получения фактического элемента из входного диапазона.
В группе Возможен выбор установите переключатель одинарного значения и нажмите кнопку ОК.
Примечание: Если вы хотите выбрать параметр набора значений или списка значений, подумайте о том, чтобы использовать элемент ActiveX «Список».
Добавление поля со списком на лист
Упростите ввод данных для пользователей, позволив им выбирать значение из поля со списком. Поле со списком состоит из текстового поля и списка, которые вместе образуют раскрывающийся список.
Можно добавить поле со списком одного из двух типов: элемент управления формы или элемент ActiveX. Если вы хотите создать поле со полем, которое позволит пользователю редактировать текст в текстовом поле, можно использовать ActiveX поле со ActiveX. Поле со списком ActiveX Control является более универсальным, так как можно изменить свойства шрифта, чтобы текст на листе с увеличенным масштабом было удобнее читать, и с помощью программирования отображать его в ячейках, содержащих список проверки данных.
Выберите столбец, который можно скрыть на листе, и создайте список, введя по одному значению в ячейки.
Примечание: Можно также создать список на другом листе той же книги.
На вкладке Разработчик нажмите кнопку Вставить.
Примечание: Если вкладка Разработчик не отображается, на вкладке Файл выберите Параметры > Настроить ленту. В списке Основные вкладки установите флажок для вкладки Разработчик и нажмите кнопку ОК.
Выберите тип поля со списком, которое нужно добавить:
в разделе Элементы управления формы выберите элемент управления Поле со списком (элемент управления формы);
в разделе Элементы ActiveX выберите элемент управления Поле со списком (элемент ActiveX).
Щелкните ячейку, в которую нужно добавить поле со списком, и нарисуйте его с помощью перетаскивания.
Чтобы изменить размер поля, наведите указатель мыши на один из маркеров изменения размера и перетащите границу элемента управления до достижения нужной высоты и ширины.
Чтобы переместить поле со списком на листе, выделите его и перетащите в нужное место.
Форматирование элемента управления формы «Поле со списком»
Щелкните правой кнопкой мыши поле со списком и выберите команду Формат объекта.
Откройте вкладку Элемент управления и настройте следующие параметры.
Формировать список по диапазону: введите диапазон ячеек, содержащий список элементов.
Связь с ячейкой: поле со списком можно связать с ячейкой, где отображается номер элемента при его выборе из списка. Введите номер ячейки, где должен отображаться номер элемента.
Например, в ячейке C1 отображается значение 3, если выбрать пункт Фруктовое мороженое, так как это третий элемент в списке.
Совет: Чтобы вместо номера отображать сам элемент, можно воспользоваться функцией ИНДЕКС. В нашем примере поле со списком связано с ячейкой B1, а диапазон ячеек для списка — A1:A2. Если в ячейку C1 ввести формулу =ИНДЕКС(A1:A5;B1), то при выборе третьего пункта в ячейке C1 появится текст «Фруктовое мороженое».
Количество строк списка: количество строк, которые должны отображаться, если щелкнуть стрелку вниз. Например, если список содержит 10 элементов и вы не хотите использовать прокрутку, вместо значения по умолчанию введите 10. Если ввести число, которое меньше количества элементов в списке, появится полоса прокрутки.
Форматирование элемента ActiveX «Поле со списком»
На вкладке Разработчик нажмите кнопку Режим конструктора.
Щелкните правой кнопкой мыши поле со списком и выберите пункт Свойства. Откройте вкладку Alphabetic (По алфавиту) и измените нужные свойства.
Вот как можно настроить свойства поля со списком на этом рисунке:
Щелкните свойство BackColor (Цвет фона), щелкните стрелку вниз, откройте вкладку Pallet (Палитра) и выберите цвет.
Тип, начертание или размер шрифта
Щелкните свойство ForeColor (Цвет текста), щелкните стрелку вниз, откройте вкладку Pallet (Палитра) и выберите цвет.
Связь с ячейкой для отображения значения, выбранного в списке
Щелкните свойство LinkedCell (Связанная ячейка).
Связывание поля со списком и списка элементов
Щелкните поле рядом со свойством ListFillRange (Диапазон элементов списка) и укажите диапазон ячеек для списка.
Изменение количества отображаемых элементов списка
Щелкните поле ListRows и введите число элементов.
Закройте область Properties (Свойства) и нажмите кнопку Режим конструктора.
Завершив форматирование, можно щелкнуть правой кнопкой мыши столбец, который содержит список, и выбрать команду Скрыть.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Создание раскрывающегося списка
Чтобы упростить работу пользователей с листом, добавьте в ячейки раскрывающиеся списки. Раскрывающиеся списки позволяют пользователям выбирать элементы из созданного вами списка.
На новом листе введите данные, которые должны отображаться в раскрывающемся списке. Желательно, чтобы элементы списка содержались в таблице Excel. Если это не так, список можно быстро преобразовать в таблицу, выделив любую ячейку диапазона и нажав клавиши CTRL+T.
Почему данные следует поместить в таблицу? Потому что в этом случае при добавлении и удалении элементов все раскрывающиеся списки, созданные на основе этой таблицы, будут обновляться автоматически. Дополнительные действия не требуются.
Выделите на листе ячейку, в которую требуется поместить раскрывающийся список.
На ленте откройте вкладку Данные и нажмите кнопку Проверка данных.
Примечание: Если кнопка Проверка данных недоступна, возможно, лист защищен или является общим. Разблокируйте определенные области защищенной книги или отмените общий доступ к листу, а затем повторите шаг 3.
На вкладке Параметры в поле Тип данных выберите пункт Список.
Щелкните поле Источник и выделите диапазон списка. В примере данные находятся на листе «Города» в диапазоне A2:A9. Обратите внимание на то, что строка заголовков отсутствует в диапазоне, так как она не является одним из вариантов, доступных для выбора.
Если можно оставить ячейку пустой, установите флажок Игнорировать пустые ячейки.
Установите флажок Список допустимых значений
Откройте вкладку Сообщение для ввода.
Если вы хотите, чтобы при выборе ячейки появлялось всплывающее сообщение, установите флажок Отображать подсказку, если ячейка является текущей и введите заголовок и сообщение в соответствующие поля (до 225 символов). Если вы не хотите, чтобы сообщение отображалось, снимите этот флажок.
Откройте вкладку Сообщение об ошибке.
Если вы хотите, чтобы при вводе значения, которого нет в списке, появлялось всплывающее сообщение, установите флажок Выводить сообщение об ошибке, выберите параметр в поле Вид и введите заголовок и сообщение. Если вы не хотите, чтобы сообщение отображалось, снимите этот флажок.
Не знаете, какой параметр выбрать в поле Вид?
Чтобы отобразить сообщение, не препятствующее вводу данных, которые не содержатся в раскрывающемся списке, выберите вариант Сообщение или «Предупреждение». Если выбрать вариант «Сообщение», сообщение будет отображаться со значком 
, а если «Предупреждение» — со значком 
.
Чтобы заблокировать пользователям ввод данных, которые не содержатся в раскрывающемся списке, выберите вариант Остановка.
Примечание: Если вы не добавили заголовок и текст, по умолчанию выводится заголовок «Microsoft Excel» и сообщение «Введенное значение неверно. Набор значений, которые могут быть введены в ячейку, ограничен».
После создания раскрывающегося списка убедитесь, что он работает правильно. Например, рекомендуется проверить, изменяется ли ширина столбцов и высота строк при отображении всех ваших записей.
Если список элементов для раскрывающегося списка находится на другом листе и вы хотите запретить пользователям его просмотр и изменение, скройте и защитите этот лист. Подробнее о защите листов см. в статье Блокировка ячеек.
Если вы решили изменить элементы раскрывающегося списка, см. статью Добавление и удаление элементов раскрывающегося списка.
Чтобы удалить раскрывающийся список, см. статью Удаление раскрывающегося списка.
Предлагаем скачать образец книги с несколькими примерами проверки данных, аналогичными примеру в этой статье. Вы можете воспользоваться ими или создать собственные сценарии проверки данных. Скачать примеры проверки данных Excel.
Ввод данных станет быстрее и точнее, если ограничить значения в ячейке вариантами из раскрывающегося списка.
Сначала создайте на листе список допустимых элементов, а затем выполните сортировку или расположите их в нужном порядке. В дальнейшем эти элементы могут служить источником для раскрывающегося списка данных. Если список небольшой, на него можно легко ссылаться и вводить элементы прямо в средстве проверки данных.
Создайте список допустимых элементов для раскрывающегося списка. Для этого введите элементы на листе в одном столбце или строке без пустых ячеек.
Выделите ячейки, для которых нужно ограничить ввод данных.
На вкладке Данные в группе Инструменты нажмите кнопку Проверка данных или Проверить.
Примечание: Если команда проверки недоступна, возможно, лист защищен или книга является общей. Если книга является общей или лист защищен, изменить параметры проверки данных невозможно. Дополнительные сведения о защите книги см. в статье Защита книги.
Откройте вкладку Параметры и во всплывающем меню Разрешить выберите пункт Список.
Щелкните поле Источник и выделите на листе список допустимых элементов.
Диалоговое окно свернется, чтобы было видно весь лист.
Нажмите клавишу ВВОД или кнопку Развернуть 
, чтобы развернуть диалоговое окно, а затем нажмите кнопку ОК.
Значения также можно ввести непосредственно в поле Источник через запятую.
Чтобы изменить список допустимых элементов, просто измените значения в списке-источнике или диапазон в поле Источник.
Можно указать собственное сообщение об ошибке, которое будет отображаться при вводе недопустимых данных. На вкладке Данные нажмите кнопку Проверка данных или Проверить, а затем откройте вкладку Сообщение об ошибке.
См. также
На новом листе введите данные, которые должны отображаться в раскрывающемся списке. Желательно, чтобы элементы списка содержались в таблице Excel.
Почему данные следует поместить в таблицу? Потому что в этом случае при добавлении и удалении элементов все раскрывающиеся списки, созданные на основе этой таблицы, будут обновляться автоматически. Дополнительные действия не требуются.
Выделите на листе ячейку, в которую требуется поместить раскрывающийся список.
На ленте откройте вкладку Данные и нажмите кнопку Проверка данных.
На вкладке Параметры в поле Разрешить выберите пункт Список.
Если вы уже создали таблицу с элементами раскрывающегося списка, щелкните поле Источник и выделите ячейки, содержащие эти элементы. Однако не включайте в него ячейку заголовка. Добавьте только ячейки, которые должны отображаться в раскрывающемся списке. Список элементов также можно ввести непосредственно в поле Источник через запятую. Например:
Фрукты;Овощи;Зерновые культуры;Молочные продукты;Перекусы
Если можно оставить ячейку пустой, установите флажок Игнорировать пустые ячейки.
Установите флажок Список допустимых значений
Откройте вкладку Сообщение для ввода.
Если вы хотите, чтобы при выборе ячейки появлялось всплывающее сообщение, установите флажок Показывать сообщения и введите заголовок и сообщение в соответствующие поля (до 225 символов). Если вы не хотите, чтобы сообщение отображалось, снимите этот флажок.
Откройте вкладку Сообщение об ошибке.
Если вы хотите, чтобы при вводе значения, которого нет в списке, появлялось всплывающее сообщение, установите флажок Показывать оповещения, выберите параметр в поле Тип и введите заголовок и сообщение. Если вы не хотите, чтобы сообщение отображалось, снимите этот флажок.
После создания раскрывающегося списка убедитесь, что он работает правильно. Например, рекомендуется проверить, изменяется ли ширина столбцов и высота строк при отображении всех ваших записей. Если вы решили изменить элементы раскрывающегося списка, см. статью Добавление и удаление элементов раскрывающегося списка. Чтобы удалить раскрывающийся список, см. статью Удаление раскрывающегося списка.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Выпадающий список в EXCEL
history 21 января 2013 г.
При заполнении ячеек данными, часто необходимо ограничить возможность ввода определенным списком значений. Например, имеется ячейка, куда пользователь должен внести название департамента, указав где он работает. Логично, предварительно создать список департаментов организации и позволить пользователю лишь выбирать значения из этого списка. Этот подход поможет ускорить процесс ввода и уменьшить количество опечаток.
Выпадающий список можно создать с помощью Проверки данных
Выпадающий список можно сформировать по разному.
Предположим, в ячейке B 1 необходимо создать выпадающий список для ввода единиц измерений. Выделим ячейку B 1 и вызовем Проверку данных.
Недостатки этого подхода: элементы списка легко потерять (например, удалив строку или столбец, содержащие ячейку B 1 ); не удобно вводить большое количество элементов. Подход годится для маленьких (3-5 значений) неизменных списков. Преимущество : быстрота создания списка.
Б. Ввод элементов списка в диапазон (на том же листе, что и выпадающий список)
Элементы для выпадающего списка можно разместить в диапазоне на листе EXCEL, а затем в поле Источник инструмента Проверки данных указать ссылку на этот диапазон.
Избавимся сначала от второго недостатка – разместим перечень элементов выпадающего списка на другом листе.
B. Ввод элементов списка в диапазон (на любом листе)
В правилах Проверки данных (также как и Условного форматирования ) нельзя впрямую указать ссылку на диапазоны другого листа (см. Файл примера ):
а диапазон с перечнем элементов разместим на другом листе (на листе Список в файле примера ).
Используем именованный диапазон Создадим Именованный диапазон Список_элементов, содержащий перечень элементов выпадающего списка (ячейки A 1: A 4 на листе Список ) . Для этого:
Используем функцию ДВССЫЛ()
Ввод элементов списка в диапазон ячеек, находящегося в другой книге
Если необходимо перенести диапазон с элементами выпадающего списка в другую книгу (например, в книгу Источник. xlsx ), то нужно сделать следующее:
Примечание : Если выпадающий список содержит более 25-30 значений, то работать с ним становится неудобно. Выпадающий список одновременно отображает только 8 элементов, а чтобы увидеть остальные, нужно пользоваться полосой прокрутки, что не всегда удобно.