Как в экселе в ячейке сделать выпадающий список?

10 ответов на вопрос “Как в экселе в ячейке сделать выпадающий список?”

  1. Thorgarim Ответить

    Видео

    У кого мало времени и нужно быстро ухватить суть – смотрим обучающее видео:
    Кому интересны подробности и нюансы всех описанных способов – дальше по тексту.

    Способ 1. Примитивный

    Один щелчок правой кнопкой мыши по пустой ячейке под столбцом с данными, команда контекстного меню Выбрать из раскрывающегося списка (Choose from drop-down list) или нажать сочетание клавиш ALT+стрелка вниз. Способ не работает, если ячейку и столбец с данными отделяет хотя бы одна пустая строка или вам нужен товар, который еще ни разу не вводился выше:

    Способ 2. Стандартный

    Выделите ячейки с данными, которые должны попасть в выпадающий список (например, наименованиями товаров).
    Если у вас Excel 2003 или старше – выберите в меню Вставка – Имя – Присвоить (Insert – Name – Define), если Excel 2007 или новее – откройте вкладку Формулы (Formulas) и воспользуйтесь кнопкой Диспетчер имен (Name Manager), затем Создать. Введите имя (можно любое, но обязательно без пробелов и начать с буквы!) для выделенного диапазона (например Товары). Нажмите ОК.
    Выделите ячейки (можно сразу несколько), в которых хотите получить выпадающий список и выберите в меню (на вкладке) Данные – Проверка (Data – Validation). Из выпадающего списка Тип данных (Allow) выберите вариант Список (List) и введите в строчку Источник (Source) знак равенства и имя диапазона (т.е. =Товары).

    Нажмите ОК.
    Все! Наслаждайтесь!

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

    Способ 3. Элемент управления

    Этот способ представляет собой вставку на лист нового объекта – элемента управления “поле со списком” с последующей привязкой его к диапазонам на листе. Для этого:
    В Excel 2007/2010 откройте вкладку Разработчик (Developer). В более ранних версиях – панель инструментов Формы (Forms) через меню Вид – Панели инструментов – Формы (View – Toolbars – Forms). Если этой вкладки не видно, то нажмите кнопку Офис – Параметры Excel – флажок Отображать вкладку Разработчик на ленте (Office Button – Excel Options – Show Developer Tab in the Ribbon)
    Найдите значок выпадающего списка среди элементов управления форм (не ActiveX!). Ориентируйтесь по всплывающим подсказкам – Поле со списком:

    Щелкните по значку и нарисуйте небольшой горизонтальный прямоугольник – будущий список.
    Щелкните по нарисованному списку правой кнопкой мыши и выберите команду Формат объекта (Format control). В появившемся диалоговом окне задайте
    Формировать список по диапазону – выделите ячейки с наименованиями товаров, которые должны попасть в список
    Связь с ячейкой – укажите ячейку куда нужно выводить порядковый номер выбранного пользователем элемента.
    Количество строк списка – сколько строк показывать в выпадающем списке. По умолчанию – 8, но можно больше, чего не позволяет предыдущий способ.
    После нажатия на ОК списком можно пользоваться.
    Чтобы вместо порядкового номера элемента выводилось его название можно дополнительно использовать функцию ИНДЕКС (INDEX), которая умеет выводить содержимое нужной по счету ячейки из диапазона:

    Способ 4. Элемент ActiveX

    Этот способ частично напоминает предыдущий. Основное отличие в том, что на лист добавляется не элемент управления, а элемент ActiveX “Поле со списком” из раскрывающегося набора под кнопкой Вставить (Insert) с вкладки Разработчик (Developer):

    Механизм добавления тот же – выбираем объект из списка и рисуем его на листе. А вот дальше начинаются серьезные отличия от предыдущего способа.
    Во-первых, созданный выпадающий ActiveX список может находится в двух принципиально разных состояниях – режиме отладки, когда можно настраивать его параметры и свойства, двигать его по листу и менять размеры и – режиме ввода, когда единственное, что можно – выбирать из него данные. Переключение между этими режимами происходит с помощью кнопки Режим Конструктора (Design Mode) на вкладке Разработчик (Developer):

    Если эта кнопка нажата, то мы можем настраивать параметры выпадающего списка, нажав соседнюю кнопку Свойства (Properties), которая откроет окно со списком всех возможных настроек для выделенного объекта:

    Самые нужные и полезные свойства, которые можно и нужно настроить:
    ListFillRange – диапазон ячеек, откуда берутся данные для списка. Выделить мышью диапазон он не даст, надо просто вписать его руками с клавиатуры (например, Лист2!A1:A5)
    LinkedCell – связанная ячейка, куда будет выводиться выбранный из списка элемент
    ListRows – количество отображаемых строк
    Font – шрифт, размер, начертание (курсив, подчеркивание и т.д. кроме цвета)
    ForeColor и BackColor – цвет текста и фона, соответственно
    Большим и жирным плюсом этого способа является возможность быстрого перехода к нужному элементу в списке при вводе первых букв с клавиатуры(!), чего нет у всех остальных способов. Приятным моментом, также, является возможность настройки визуального представления (цветов, шрифтов и т.д.)
    При использовании этого способа, также возможно указывать в качестве ListFillRange не только одномерные диапазоны. Можно, например задать диапазон из двух столбцов и нескольких строк, указав дополнительно, что выводить нужно два столбца (свойство ColumnCount=2). Тогда можно получить весьма привлекательные результаты, окупающие все потраченные на дополнительные настройки усилия:

    Итоговая сравнительная таблица всех способов

    Способ 1. Примитивный
    Способ 2. Стандартный
    Способ 3. Элемент управления
    Способ 4. Элемент ActiveX
    Сложность
    низкая
    средняя
    высокая
    высокая
    Возможность настройки шрифта, цвета и т.д.
    нет
    нет
    нет
    да
    Количество отображаемых строк
    всегда 8
    всегда 8
    любое
    любое
    Быстрый поиск элемента по первым буквам
    нет
    нет
    нет
    да
    Необходимость использования дополнительной функции ИНДЕКС
    нет
    нет
    да
    нет
    Возможность создания связанных выпадающих списков
    нет
    да
    нет
    нет

    Ссылки по теме:

    Выпадающий список с данными из другого файла
    Создание зависимых выпадающих списков
    Автоматическое создание выпадающих списков надстройкой PLEX
    Выбор фото из выпадающего списка
    Автоматическое удаление уже использованных элементов из выпадающего списка
    Выпадающий список с автоматическим добавлением новых элементов

  2. ZOMEXU Ответить

    Открывается окно проверки вводимых значений. Во вкладке «Параметры» в поле «Тип данных» выбираем параметр «Список». В поле «Источник» ставим знак равно, и сразу без пробелов пишем имя списка, которое присвоили ему выше. Жмем на кнопку «OK».

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

    Создание выпадающего списка с помощью инструментов разработчика

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

    В открывшемся окне переходим в подраздел «Настройка ленты», и ставим флажок напротив значения «Разработчик». Жмем на кнопку «OK».

    После этого, на ленте появляется вкладка с названием «Разработчик», куда мы и перемещаемся. Чертим в Microsoft Excel список, который должен стать выпадающим меню. Затем, кликаем на Ленте на значок «Вставить», и среди появившихся элементов в группе «Элемент ActiveX» выбираем «Поле со списком».

    Кликаем по месту, где должна быть ячейка со списком. Как видите, форма списка появилась.

    Затем мы перемещаемся в «Режим конструктора». Жмем на кнопку «Свойства элемента управления».

    Открывается окно свойств элемента управления. В графе «ListFillRange» вручную через двоеточие прописываем диапазон ячеек таблицы, данные которой будут формировать пункты выпадающего списка.

    Далее, кликаем по ячейке, и в контекстном меню последовательно переходим по пунктам «Объект ComboBox» и «Edit».

    Выпадающий список в Microsoft Excel готов.

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

    Связанные списки

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

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

    В первой ячейке создаём список точно таким же образом, как делали это ранее, через проверку данных.

    Во второй ячейке тоже запускаем окно проверки данных, но в графе «Источник» вводим функцию «=ДВССЫЛ» и адрес первой ячейки. Например, =ДВССЫЛ($B3).

    Как видим, список создан.

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

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

  3. Sharpcrusher Ответить


    При создании таблиц порой очень удобно использовать выпадающие (иначе говоря — раскрывающиеся) списки. Excel 2010 позволяет это делать несколькими способами. Рассмотрим их.
    Способ 1. Создаем раскрывающийся список в Excel 2010, используя инструмент «Проверка данных»
    Этот способ считается стандартным, поскольку он прост и удобен.
    1. На свободном месте листа запишите все элементы раскрывающегося списка в столбик, каждый элемент — в своей ячейке.
    2. Задайте имя диапазону ячеек. Для этого:
    кликните верхнюю ячейку списка и, зажав левую кнопку мыши, тяните курсор вниз, пока весь список не будет выделен.
    установите курсор в поле «Имя», слева от строки формул;
    введите имя списка и нажмите Enter.

    Обратите внимание, имя списка всегда должно начинаться с буквы и не содержать пробелов.
    3. Выделите ячейку в таблице, где должен будет размещаться выпадающий список.
    4. Откройте вкладку «Данные» и нажмите кнопку «Проверка данных». В открывшемся окне перейдите на вкладку «Параметры». В выпадающем списке «Тип данных» выберите пункт «Список».
    5. В строке «Источник» нужно указать адрес, откуда будет взяты элементы создаваемого списка. Адресом будет имя, которое вы присвоили диапазону ячеек. Задать адрес можно несколькими способами.
    Вписать вручную, поставив перед ним знак «равно», например, «=месяца». Регистр букв не важен.
    Щелкнув мышью в строке «Источник» (для активации), выделить курсором все элементы списка в таблице.

    6. Если нужно создать сообщение для ввода, откройте одноименную вкладку. Напишите текст, который будет появляться рядом с ячейкой с раскрывающимся списком при ее выделении. На соседней вкладке — «Сообщение об ошибке», таким же образом можно написать текст, уведомляющий об ошибках.

    7. Подтвердите ввод нажатием «ОК», и раскрывающийся список готов. Для его открытия нажмите на кнопку со стрелкой вниз, которая появится рядом с ячейкой, содержащей список.
    Способ 2. Быстрое создание выпадающего списка
    Выпадающий список в Excel 2010 можно создавать одним нажатием сочетания клавиш, но он может быть расположен только в одном месте — в ячейке под элементами списка.
    1. Перечислите в столбик все элементы будущего выпадающего списка.
    2. Выделите ячейку, находящуюся под последним элементом и нажмите сочетание клавиш «Alt» + «стрелка вниз» — список будет создан. Этот метод позволит задать ячейке значение одного из элементов.

    Способ 3. Создание выпадающего списка как элемента управления
    Для применения этого метода включите отображение вкладки «Разработчик»: откройте меню «Файл» — «Параметры» — «Настройка ленты». В столбце «Основные вкладки» отметьте галочкой пункт «Разработчик». Подтвердите действие нажатием «ОК» — вкладка будет создана.
    1. Перечислите элементы будущего списка в столбик.
    2. Из меню «Вставить» вкладки «Разработчик» выберите пункт «Элементы управления формы» — «Поле со списком».

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

    5. Подтвердите ввод и нажмите «ОК». Список будет создан.

    Способ 4. Создание выпадающего списка как элемента ActiveX
    Самый сложный метод, но обладающий максимально гибкими настройками.
    1. Создайте список вышеописанным способом.
    2. Из меню «Вставить» вкладки «Разработчик» выберите пункт «Элементы ActiveX» — «Поле со списком».

    3. Нарисуйте на листе будущий раскрывающийся список.
    4. Опция, которая позволяет редактировать выпадающий список называется «Режим конструктора». Если данный режим активен — будет выделена одноименная кнопка в разделе «Элементы управления», рядом с кнопкой «Вставить». Если кнопка не выделена — режим редактирования отключен.
    5. Для задания параметров списка нажмите кнопку «Свойства» того же раздела. Откроется окно настроек «Properties». Обе вкладки этого окна содержат одни и те же настройки, рассортированные в первом случае — по алфавиту, во втором — по категориям.

    6. Большинство настроек можно оставить по умолчанию, а самое необходимое перечислено ниже.
    ListRows — аналог значения «Количество строк в списке», покажет, сколько строк будет отображаться.
    Font — настройки шрифта. Позволяет выбрать шрифт и его начертание.
    ForeColor — выбор из таблицы цвета шрифта.
    BackColor — цвет заднего фона.
    ListFillRange — расположение списка элементов в формате: лист(«!» — разделитель) и диапазон ячеек. Например: Лист2!D2:D6. Прописывается вручную.
    LinkedCell — связь с ячейкой. Вручную указывается адрес ячейки, где будет показываться порядковый номер выделенного элемента списка.

    7. Сохраните настройки и деактивируйте режим конструктора нажатием на одноименную кнопку. Выпадающий список будет создан, а вы сможете проверить, как он работает.

    Смотрите также:

  4. VideoAnswer Ответить

  5. VideoAnswer Ответить

Добавить ответ

Ваш e-mail не будет опубликован. Обязательные поля помечены *