Как сделать в эксель выпадающий список с выбором?

13 ответов на вопрос “Как сделать в эксель выпадающий список с выбором?”

  1. Bezdomny Ответить

    Кстати, получившийся результат можно копировать в нижние ячейки -как обычные формулы. Работаем далее. Сначала займемся доработкой наших трех списков на листе «База». Доработаем сначала графу «Полуфабрикаты». Сделаем это, чтобы позже можно было добавлять в «базу» новые позиции, а они автоматом отображались бы в списках.

    Ставим курсор в строку из  «Полуфабрикаты,  затем открываем вкладку «Главная» и жмем на  кнопку «Форматировать как таблицу». Дизайн выбираем любой.  Задаем по столбцу нужный вертикальный диапазон, включая  только строки с наименованиями;  ничего кроме диапазона не меняем.
    В процессе этих действий нам стал доступен «конструктор таблиц». Обязательно присваиваем нашей( как бы созданной) таблице имя  (в левом верхнем углу) «Полуфабрикаты».

    Повторяем действо для граф «Рыба» и «Хлебобулочные».
    Завешающий этап.  Встаем в  нужную строку таблицы, как обычно идем в «Данные»-«Проверка данных», выбираем тип «Список» и в качестве диапазона указываем на  соседнюю ячейку , т.е на ранее созданный список, но…

    … с использованием в «Источнике» функции ДВССЫЛ. «=ДВССЫЛ($Е$6)». Эта функция возвращает ссылку на наши диапазоны т.е. предыдущие списки, а не содержимое ячейки.  Мы работаем с данными, в функцию просто их подставили. Наименование вводится вручную.
    Обратите внимание на «источник». Указана абсолютная адресация столбца и ячейки (через знаки «$»). Чтобы распространить в таком виде на остальные строки, нужно будет указывать для каждой строки свой источник данных. Это долго. Можно аккуратно убрать абсолютную адресацию руками из «источника» и после скопировать ячейку на остальные строки ниже. Тогда «формула»  распространится корректно.
    Попробуйте добавить в базу новые позиции; проверьте — появляются ли они теперь автоматически в списке после указании категорий? Должно получиться.

    Делаем выпадающий список в ячейке  эксель с выбором по первой букве

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

    В режиме конструктора вставим в наш документ элемент управления Active X «Поле со списком«:

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

    ListFillRange —  сюда вставляем наш именованный диапазон из «диспетчера имен»;
    LinkedCell — связанная ячейка, куда будет выводиться выбранный из списка элемент — нужно, если данные ее будут использоваться в других таблицах, формулах и т.д;
    ListRows — количество отображаемых строк в списке;
    Font —  выбираем шрифт, размер, стиль;
    ForeColor и BackColor — цвет текста и фона;
    ListRow —  количество возвращаемых  строк в списке;
    PrintObject —  определяет — выводить элемент управления на печать или нет (истина или ложь)
    Пока  активен режим конструктора — вы не сможете работать со списком как пользователь. Но вы можете изменять его размеры , свойства , положение на листе. После окончания настроек нужно выключить режим конструктора.
    Попробуйте найти имя по букве, посмотрите  что получается. Можно скопировать и разместить на нужных местах созданный нами элемент управления. Такой способ создания списков хорош для серьезных проектов в Excek, может не всем подойти, но у него есть свои плюсы, которых нет в других способах.

  2. Tosha905 Ответить

    Как сделать выпадающий список в Excel 2010 или 2016 с помощью одной командой на панели инструментов? На вкладке «Данные» в разделе «Работа с данными» найдите кнопку «Проверка данных». Нажмите на нее и выберите первый пункт.

    Откроется окно. Во вкладке «Параметры» в выпадающем разделе «Тип данных» выберите «Список».

    Снизу появится строка для указания источников.

    Указывать информацию можно по-разному.
    Ручной ввод
    Введите перечень через точку с запятой.

    Выбор диапазона значений с листа Excel
    Для этого начните выделять ячейки мышью.

    Как отпустите – окно снова станет нормальным, а в строке появятся адреса.

    Создание выпадающего списка в Excel с подстановкой данных
    Сначала назначим имя. Для этого создайте на любом листе такую таблицу.

    Выделите ее и нажмите правую кнопку мыши. Щелкните по команде «Присвоить имя».

    Введите имя в строку сверху.

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

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

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

    Подстановка динамических данных Excel

    Если Вы добавите какое-то значение в диапазон данных, которые подставляются в перечень, то в нем изменения не произойдет, пока вручную не будут указаны новые адреса. Чтобы связать диапазон и активный элемент, необходимо оформить первый как таблицу. Создайте вот такой массив.

    Выделите его и на вкладке «Главная» выберите любой стиль таблицы.

    Обязательно поставьте галочку внизу.

    Вы получите такое оформление.

    Создайте активный элемент, как было описано выше. В качестве источника введите формулу
    =ДВССЫЛ(“Таблица1[Города]”)

    Чтобы узнать имя таблицы, перейдите на вкладку «Конструктор» и посмотрите его. Можете поменять имя на любое другое.

    Функция ДВССЫЛ создает ссылку на ячейку или диапазон. Теперь ваш элемент в ячейке привязан к массиву данных.
    Попробуем увеличить количество городов.

    Обратная процедура – подстановка данных из выпадающего списка в таблицу Excel, работает очень просто. В ячейку, куда надо вставить выбранное значение из таблицы, введите формулу:
    =Адрес_ячейки Например, если перечень данных находится в ячейке D1, то в ячейке, куда будут выведены выбранные результаты введите формулу
    =D1

    Как убрать (удалить) выпадающий список в Excel

    Откройте окно настройки выпадающего списка и выберите «Любое значение» в разделе «Тип данных».


    Ненужный элемент исчезнет.

    Зависимые элементы

    Иногда в Excel встречается необходимость создания нескольких перечней, когда один зависит от другого. Например, каждый город имеет несколько адресов. При выборе в первом мы должны получить только адреса выбранного населенного пункта.

    В этом случае дайте имя каждому столбцу. Выделите без первой ячейки (названия) и нажмите правую кнопку мыши. Выберите «Присвоить имя».

    Это будет название города.

    При именовании Санкт-Петербурга и Нижнего Новгорода Вы получите ошибку, так как имя не может содержать пробелов, символов подчеркивания, специальных символов и т.д.

    Поэтому переименуем эти города, поставив нижнее подчеркивание.

    Первый элемент в ячейке A9 создаем обычным образом.

    А во втором пропишем формулу:
    =ДВССЫЛ(A9)
    Сначала Вы увидите сообщение об ошибке. Соглашайтесь.
    Проблема в отсутствии выбранного значения. Как только в первом перечне будет выбран город, второй заработает.

  3. voronov_dp Ответить


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

    Нажмите кнопку ОК.

    Форматирование элемента ActiveX “Поле со списком”

    На вкладке Разработчик нажмите кнопку Режим конструктора.
    Щелкните правой кнопкой мыши поле со списком и выберите пункт Свойства. Откройте вкладку Alphabetic (По алфавиту) и измените нужные свойства.
    Вот как можно настроить свойства поля со списком на этом рисунке:


    Настраиваемое свойство
    Действие
    Цвет заливки
    Щелкните свойство BackColor (Цвет фона), щелкните стрелку вниз, откройте вкладку Pallet (Палитра) и выберите цвет.

    Тип, начертание или размер шрифта
    Щелкните свойство Font (Шрифт), нажмите кнопку … и выберите тип, размер или начертание шрифта.

    Цвет шрифта
    Щелкните свойство ForeColor (Цвет текста), щелкните стрелку вниз, откройте вкладку Pallet (Палитра) и выберите цвет.
    Связь с ячейкой для отображения значения, выбранного в списке
    Щелкните свойство LinkedCell (Связанная ячейка).
    Связывание поля со списком и списка элементов
    Щелкните поле рядом со свойством ListFillRange (Диапазон элементов списка) и укажите диапазон ячеек для списка.
    Изменение количества отображаемых элементов списка
    Щелкните поле ListRows и введите число элементов.
    Закройте область Properties (Свойства) и нажмите кнопку Режим конструктора.
    Завершив форматирование, можно щелкнуть правой кнопкой мыши столбец, который содержит список, и выбрать команду Скрыть.

    Дополнительные сведения

    Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

    См. также

    Общие сведения о формах, элементах управления форм и элементах ActiveX на листе
    Добавление флажков и переключателей (элементы управления формы)

  4. TmW Ответить

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

    Способ 1. Если у вас Excel 2007 или новее

    Простой и удобный способ почти без формул. Использует новую возможность последних версий Microsoft Excel начиная с 2007 версии – “Умные Таблицы”. Суть его в том, что любой диапазон можно выделить и отформатировать как Таблицу. Тогда он превращается, упрощенно говоря, в “резиновый”, то есть сам начинает отслеживать изменения своих размеров, автоматически растягиваясь-сжимаясь при добавлении-удалении в него данных.
    Выделите диапазон вариантов для выпадающего списка (A1:A5 в нашем примере выше) и на Главной (Home) вкладке нажмите кнопку Форматировать как таблицу (Home – Format as Table). Дизайн можно выбрать любой – это роли не играет:

    Обратите внимание на то, что таблица должна иметь строку заголовка (в нашем случае это А1 со словом Сотрудники). Первая ячейка играет роль “шапки” и содержит название столбца. На появившейся после превращения в Таблицу вкладке Конструктор (Design) можно изменить стандартное имя таблицы на свое (без пробелов!). По этому имени мы сможем потом адресоваться к таблице на любом листе этой книги:

    Теперь выделите ячейки где вы хотите создать выпадающие списки (в нашем примере выше – это D2) и выберите в старых версиях Excel в меню Данные – Проверка (Data – Validation), а в новых нажмите кнопку Проверка данных (Data Validation) на вкладке Данные (Data). В открывшемся окне на вкладке Параметры (Settings) выберите вариант Список (List) и введите в поле Источник (Source) вот такую формулу:

    =ДВССЫЛ(“Таблица1[Сотрудники]”)
    =INDIRECT(“Таблица1[Сотрудники]”)
    Смысл этой формулы прост. Выражение Таблица1[Сотрудники] – это ссылка на столбец с данными для списка из нашей умной таблицы. Но проблема в том, что Excel почему-то не хочет понимать прямых ссылок в поле Источник (Source), т.е. нельзя написать в поле Источник выражение вида =Таблица1[Сотрудники]. Поэтому мы идем на тактическую хитрость – вводим ссылку как текст (в кавычках) и используем функцию ДВССЫЛ (INDIRECT), которая преобразовывает текстовую ссылку в настоящую, живую.
    Осталось только нажать на ОК. Если теперь дописать к нашей таблице новые элементы, то они будут автоматически в нее включены, а значит – добавятся к нашему выпадающему списку. С удалением – то же самое.
    Если вам лень возиться с вводом формулы ДВССЫЛ, то можно чуть упростить процесс. После создания умной таблицы просто выделите мышью диапазон с элементами для выпадающего списка (A2:A5) и введите в поле адреса имя для этого диапазона (без пробелов), например Стажеры, и нажмите на Enter:

    Фактически, этим мы создаем именованный динамический диапазон, который ссылается на данные из нашей умной таблицы. Теперь имя этого диапазона можно ввести в окне создания выпадающего списка в поле Источник (Source):

    Способ 2. Если у вас Excel 2003 или старше

    В старых версиях Excel до 2007 года не было замечательных “умных таблиц”, поэтому придется их имитировать своими силами. Это можно сделать с помощью именованного диапазона и функции СМЕЩ (OFFSET), которая умеет выдавать ссылку на динамический диапазон заданного размера.
    Откройте меню Вставка – Имя – Присвоить (Insert – Name – Define) или нажмите Ctrl+F3. В открывшемся окне нажмите кнопку Добавить (New), введите имя диапазона (любое, но без пробелов и начинающееся с буквы, например – Люди) и в поле Ссылка (Reference) введите вот такую формулу:

    =СМЕЩ(A2;0;0;СЧЁТЗ(A2:A100);1)
    =OFFSET(A2;0;0;COUNTA(A2:A100);1)
    Функция СЧЁТЗ (COUNTA) подсчитывает количество непустых ячеек в столбце с фамилиями, т.е. количество строк в диапазоне для выпадающего списка. Функция СМЕЩ (OFFSET) формирует ссылку на диапазон с нужными нам именами и использует следующие аргументы:
    A2 – начальная ячейка
    0 – сдвиг начальной ячейки по вертикали вниз на заданное количество строк
    0 – сдвиг начальной ячейки по горизонтали вправо на заданное количество столбцов
    СЧЁТЗ(A2:A100) – размер получаемого на выходе диапазона по вертикали, т.е. столько строк, сколько у нас занятых ячеек в списке
    1 – размер получаемого на выходе диапазона по горизонтали, т.е. один столбец
    Теперь выделите ячейки, где вы хотите создать выпадающие списки, и выберите в старых версиях Excel в меню Данные – Проверка (Data – Validation). В открывшемся окне на вкладке Параметры (Settings) выберите вариант Список (List) и введите в поле Источник (Source) вот такую формулу:
    =Люди
    После нажатия на ОК ваш динамический список в выделенных ячейках готов к работе.

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

    4 способа создания выпадающих списков в ячейках листа Excel
    Связанные выпадающие списки
    Быстрое создание выпадающих списков с помощью надстройки PLEX

  5. chronefag Ответить

    Пример использования зависимого выпадающего списка для создания удобной формы заполнения документов, с помощью которых продавцы заказывали товары. Из всего ассортимента они должны были выбрать те продукты, которые они собирались продать.
    Каждый продавец сначала определял товарную группу, а затем конкретный товар из этой группы. Форма должна включать полное имя группы и определенный индекс товара. Поскольку набирать это вручную было бы слишком трудоемким (и раздражающим) занятием, я предложил очень быстрое и простое решение – 2 зависимых выпадающих списка.
    Первым был список всех категорий продуктов, второй – список всех продуктов, находящихся в выбранной категории. Поэтому я создал выпадающий список, зависимый от выбора, сделанного в предыдущем списке (здесь вы найдете материал о том, как создать два зависимых раскрывающихся списка).
    Тот же самый результат хочет получить пользователь шаблона домашнего бюджета где нужна категория и подкатегория расходов. Пример данных находится на рисунке ниже:

    Так, например, если мы выберем категорию Развлечения, то в списке подкатегорий должно быть: Кинотеатр, Театр, Бассейн. Очень быстрое решение, если в своем домашнем бюджете вы хотите проанализировать более подробную информацию.
    

  6. Nedaa Ответить

    Создать выпадающий список можно и другим образом – через инструменты разработчика с применением технологии ActiveX. Метод несколько сложнее описанного выше, но он предлагает более широкий набор инструментов по настройке списка: можно будет задать количество элементов, размер и внешний вид самого окна со списком, необходимость соответствия значения в ячейке с одним из значений списка и многое другое.
    В первую очередь, эти инструменты нужно найти и активировать, так как по умолчанию они выключены. Переходим в меню “Файл”.
    В перечне слева находим в самом низу пункт “Параметры” и щелкаем по нему.
    Переходим в раздел “Настроить ленту” и в области “Основные вкладки” ставим галочку напротив пункта “Разработчик”. Инструменты разработчика будут добавлены на ленту программы. Кликаем OK, чтобы сохранить настройки.
    Теперь в программе есть новая вкладка под названием “Разработчик”. Через нее мы и будем работать. Сначала создаем столбец с элементами, которые будут источниками значений для нашего выпадающего списка.
    Переключаемся во вкладу “Разработчик”. В подразделе “Элементы управления” нажимаем на кнопку “Вставить”. В открывшемся перечне в блоке функций “Элементы ActiveX” кликаем по значку “Поле со списком”.
    Далее нажимаем на нужную ячейку, после чего появится окно со списком. Настраиваем его размеры по границам ячейки. Если список выделен мышкой, на панели инструментов будет активен “Режим конструктора”. Нажимаем на кнопку “Свойства”, чтобы продолжить настройку списка.
    В открывшихся параметрах находим строку “ListFillRange”. В столбце рядом  через двоеточие пишем координаты диапазона ячеек, составляющих наш ранее созданный список. Закрываем окно с параметрами, щелкнув на крестик.
    Затем кликаем правой кнопкой мыши по окну списка, далее – по пункту “Объект ComboBox” и выбираем “Edit”.
    В результате мы получаем выпадающий список с заранее определенным перечнем.
    Чтобы вставить его в несколько ячеек, наводим курсор  на правый нижний угол ячейки со списком, и как только он поменяет вид на крестик, зажимаем левую кнопку мыши и тянем вниз до самой нижней строки, в которой нам нужен подобный список.

    Связанный список

    У пользователей также есть возможность создавать и более сложные взаимозависимые списки (связанные). Это значит, что список в одной ячейке будет зависеть от того, какое значение мы выбрали в другой. Например, в единицах измерения товара мы можем задать килограммы или литры. Если вы выберем в первой ячейке кефир, во второй на выбор будет предложено два варианта – литры или миллилитры. А если в первую ячейки мы остановимся на яблоках, во второй у нас будет выбор из килограммов или граммов.
    Для этого нужно подготовить как минимум три столбца. В первом будут заполнены наименования товаров, а во втором и третьем – их возможные единицы измерения. Столбцов с возможными вариациями единиц измерения может быть и больше.
    Сначала создаем один общий список для всех наименований продуктов, выделив все строки столбца “Наименование”, через контекстное меню выделенного диапазона.
    Задаем ему имя, например, “Питание”.
    Затем таким же образом формируем отдельные списки для каждого продукта с соответствующими единицами измерения. Для большей наглядности возьмем в качестве примера первую позицию – “Лук”. Отмечаем ячейки, содержащие все единицы измерения для этого продукта, через контекстное меню присваиваем имя, которое полностью должно совпадать с наименованием.Таким же образом создаем отдельные списки для всех остальных продуктов в нашем перечне.
    После этого вставляем общий список с продуктами в верхнюю ячейку первого столбца основной таблицы – как и в описанном выше примере, через кнопку “Проверка данных” (вкладка “Данные”).
    В качестве источника указываем “=Питание” (согласно нашему названию).
    Затем кликаем по верхней ячейке столбца с единицами измерения, также заходим в окно проверки данных и в источнике указываем формулу “=ДВССЫЛ(A2)“, где A2 – номер ячейки с соответствующим продуктом.
    Списки готовы. Осталось его только растянуть их все строки таблицы, как для столбца A, так и для столбца B.

    Заключение

    Благодаря выпадающим спискам можно значительно облегчить ведение баз данных в Microsoft Excel. Несмотря на кажущуюся сложность в выполнении данной задачи, на деле все оказывается куда проще. Главное – строго следовать очередности описанных выше действий, в зависимости от выбранного метода, и тогда все непременно получится.

  10. VideoAnswer Ответить

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

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