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

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

  1. Adrienrad Ответить

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

    Это просто красиво, удобно и функционально, так как ячейка списка ещё и защищена от введения посторонних данных.
    Я лично постоянно использую выпадающий список по всем 3 причинам. И она значительно упрощает мне работу с данными, я сознательно сокращаю к 0% возможность ошибки при введении первичных данных.

  2. Magami Ответить

    Видео

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

    Способ 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
    Выбор фото из выпадающего списка
    Автоматическое удаление уже использованных элементов из выпадающего списка
    Выпадающий список с автоматическим добавлением новых элементов

  3. Undead_Army Ответить


    При создании таблиц порой очень удобно использовать выпадающие (иначе говоря — раскрывающиеся) списки. 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. Mokora Ответить

    При заполнении больших документов с множеством параметров появляется желание как-то упростить процесс. Для этого может быть полезным знание, как создать выпадающий список в excel. Это иногда может избавить от необходимости вводить одни и те же параметры. К примеру, когда вы заполняете названия брендов компании, которые повторяются, то в следующей ячейке страницы (без пропусков) нажмите Alt+стрелка вниз. У вас появится перечень введенных ранее данных этого столбца, из которого есть возможность выбрать необходимый вариант.
    Еще один способ, как сделать выпадающий список в Экселе – с использованием инструментов ActiveX. Для их использования нужно сначала сделать доступной вкладку «разработчик». Для этого нужно:
    Открыть вверху пункт «файл».
    Перейти в раздел «Параметры».
    Во вкладке «Настроить ленту» поставить галочку напротив «Разработчик».
    После этого появится дополнительное поле в верхнем меню под названием «Разработчик», где будет кнопка «Вставить». Там будет возможность выбрать пункт «Поле со списком» в подразделе «ActiveX». У вас появится возможность нарисовать поле в любом месте документа Эксель, где необходимо сделать выпадающий перечень. Далее этот элемент следует настроить:
    В той же вкладке перейдите на вкладку «Режим конструктора», нажмите кнопку «Свойства».
    Откроется много параметров, но основными являются только три, которые следует настроить.
    ListFillRange – здесь необходимо задать диапазон используемых значений.
    ListRows – тут задается количество данных для выпадающего меню.
    ColumnCount – здесь указывается количество столбцов, которые будут использованы.

  5. VideoAnswer Ответить

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

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