Выпадающий список в ячейке листа
Видео
У кого мало времени и нужно быстро ухватить суть - смотрим обучающее видео:
Кому интересны подробности и нюансы всех описанных способов - дальше по тексту.
Способ 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
- Выбор фото из выпадающего списка
- Автоматическое удаление уже использованных элементов из выпадающего списка
- Выпадающий список с автоматическим добавлением новых элементов
Спасибо..
Искал, спрашивал - все тупят..
Можно воспользоваться формулой "ВПР" не знаю насчет ранних версий excel начиная с 2007 года должна быть.
Если в столбце "А" - наименования товаров, "B" - цена, "C" - ячейки с выпадающим списком, то в столбце "D" можно вставить формулу =ВПР(C3;$A$3:$B$11;2;ЛОЖЬ) при условии что товары и цена расположены в диапазоне A3:B11
Если вам зрительно мешают ошибки #Н/Д (из-за наличия пустых ячеек в столбце "С") можно дополнить формулу логикой: =ЕСЛИ(E3="";"";ВПР(C3;$A$3:$B$11;2;ЛОЖЬ)), далее скопировать и вставить в ячейки ниже.
Более подробно о функции ВПР можно прочесть в справке по этой функции
А-Преподаватель
В-Группа
С-Студент
D3-выпадающий список Студентов (то есть, выборка из столбца С)
Можно как-нибудь сделать так, чтобы в ячейке в зависимости от выбранного Студента вписывались данные о том, кто его обучает и в какой он группе? То есть, в формате [Преподаватель],[Группа]?
Не могу вывести даже просто Преподавателя.
=ВПР(D3;$A$3:$C$11;1;ЛОЖЬ) выдаёт сущую ерунду. Думаю, тут проблема в том, что он у меня фамилию Студента ищет среди фамилий Преподавателя, то есть, только по первому столбцу. Это так?
и еще, формулу нужно будет поправить =ВПР(D3;$A$3:$C$11;2;ЛОЖЬ) это будут преподаватели, если Вам нужны в одной ячейке перподаватель и через запятую с пробелом группа, тогда =ВПР(D3;$A$3:$C$11;2;ЛОЖЬ)&", "&ВПР(D3;$A$3:$C$11;3;ЛОЖЬ)
выпадающий список появляется не только в первой пустой нижней ячейке, но и:
1)в первой пустой верхней ячейке
2)в средней пустой ячейке! (если два списка прерываются пустой ячейкой, то в ней можно видеть выпадающий список из двух смежных списков!
Также выпадающий список появляется если нажать ПКМ на любом элементе списка и, к сожалению, выпадающий список не работает с числами.
Обязательно дам ссылку своим студентам.
Успехов!
При печати объект не будет выводиться на лист, а также его содержимое.
Но для этого у вас есть LinkedCell.К этой ячейке все и привязывайте.
И есть ВОПРОС. Вы начали разговор в "4 способе..." о создании списка из ДВУХ столбов. Всё ли сказано? Я всё сделал как говорилось, задал ListFillRange и ColumnCount=2, сто раз перепроверил, а второй столбец не появляется... А надо.
Может, есть ещё какой-то нюанс?
надо на лист со списком добавить код на КомбоБокс
.
тоже столкнулась с подобной проблемой, ответ SVA нашла и прочитала. но, к сожалению, до конца не поняла, куда следует вписать и как))) этот код. очень прошу, если возможно, прислать картинку.
с уважением,
Мне надо, что бы выпадающий список показывал текст, а в ячейку вставлялся код к этому тексту. Как это можно реализовать?
Скромный вопрос - работаем с элементами ActiveX на рабочем листе, код:
работать не будет и лучше не стараться???? или есть способ??
В первом можно - в окне Проверки данных снять флажок Список допустимых значений.
Спасибо
О качестве сайта и надстройки сказано много, повторяться не буду - ЭТО КЛАСС!
Появилась необходимость использовать выпадающий список в такой форме: в выпадающем списке содержатся данные словами типа (D8AIII), но для расчёта мне требуются числа. Т.е. выбираю из списка диаметр арматуры, а в формуле, для расчёта, используется её масса типа (0,395). В формулу я подставляю значение ячейки для расчёта массы.
Очень много полезного приобрел и применил на практике из Вашего сайта. За что отдельное спасибо!
Возможно, я просто не нашёл ответ на свой вопрос на Вашем сайте или задам его первым.
У меня есть файл с большим количеством листов. С помощью гиперссылок я один из листов сделал главным. С него можно перейти на любой лист, а с листа вернуться на главный. Но по мере свободного времени на работе я пытаюсь упростить файл для использования (им пользуюсь не только я).
Сейчас я эту главную страницу обрабатываю как интернетовский сайт. Корпоративный стиль и т.д.
Одна из моих задумок состоит в следующем.
Можно ли сделать так, что когда из выпадающего списка я буду выбирать необходимую категорию, то будет появляться список гиперссылок на необходимые листы. Не просто данные с названиями этих листов, а именно сами гиперссылки.
Надеюсь, я понятно выразил суть вопроса. ))
С уважением,
Олег
Ребята, помогите пожалуйста.
Такой вопрос.
Необходимо использовать выпадающие списки во многих (во всех) ячейках таблицы.
По функциям подходит список на базе Элемента ActiveX.
Собственно, по нему и вопросы:
1. его что, надо в каждой ячейке, где необходимо исп. выпадающие списки, вставлять и настраивать отдельно?
2. Сам элемент постоянно виден и заграмождает таблицу (в отличии от обычного вып. списка на базе имени диапазона и проверки данных). Тут что то можно сделать - чтобы он появлялся тольк при необходимости ввода значения ?
Спасибо.
2. Нет.
Советую вариант 2 - наиболее универсальный для больших таблиц.
Или каким то образом создать условие, при помощи которого выбирая что то из списка, вся строка окрашивалась в соотв. цвет
Подскажите, пожалуйста, какой нужен макрос , что бы следующее выбранное значение из списка (создан выпадающий список с помощью элемента ActiveX) не заменяло уже существующее, а добавлялось в следующую ячейку (до ∞)
Заранее благодарю за помощь!
Горизонтальный вариант похож, спасибо!
За ранее спасибо.
При использовании 4 метода для создания выпадающего списка на основе именнованного диапазона с числами, среди которых есть десятичные дроби (разделитель целой и дробной части - запятая) столкнулся с "трудностями перевода".
Дробные значения вставляются в связанную ячейку как число отформатированное как текст (разделителем становится точка). Соответственно вычисления на основе таких значений просто некорректны.
Что интересно, при создании элемента управления "Список" для этого же диапазона значений получаем чёткую подстановку, но, как понимаете, не то что нужно изначально.
Вопросы:
1. Подходит ли 4 метод для данных такого типа?
2. Как справиться с этой трудностью? В настройках параметров ComboBox'a решение найти не удалось.
Возможно ли с в четвертом способе (Элемент ActiveX) создать диапазон по условию, что возможно сделать во втором способе???
- Я хочу использовать Элемент ActiveX, у меня уже есть отлаженный динамический диапазон данных "К" я могу вписать сюда имя диапазона или же нужно ссылаться на сам диапазон в формате Лист2!R1C2:R17C2 ? Если второй вариант, то можно ли вписать СМЕЩ(Лист2!R1C2;0;0;СУММ(Лист2!R1C4:R17C4);1), чтобы диапазон воспринимался как динамический?
Спасибо!
В готовом листе excel 2010 не могу установить раскрывающийся список.
Что-то блокирует "Проверку данных"на вкладке "Данные" и кнопку "Вставить" на вкладке "Разработчик".
Подскажите пожалуйста какие настройки надо проверить и изменить.
Заранее благодарен за помощь!
Спасибо вам за подробную статью, с помощью неё я смог решить большую проблему.
Но у меня сейчас возникла другая..
Дело в том, что я создал документ Excel для оприходования товаров в магазине (1с использовать не стал - маленький магазин).
И я использовал Элемент ActiveX, т.к. нужен был быстрый поиск по первым буквам.
Но трудность возникла позже.
1 файл представляет собой 1 месяц. В нем около 15 листов - по дням. В каждом листе примерно по 400 полей ActiveX с выпадающим списком, значения для которых берутся из таблицы с прайс-листом (в каждом листе своя таблица).
Таким образом файл тормозит жутко, даже на неплохих ПК, а иногда и вовсе портится, думаю вследствие нехватки ресурсов (ошибка возникает постоянно).
Николай, подскажите пожалуйста, как можно оптимизировать Элемент ActiveX, чтобы не такая большая нагрузка была. И может есть вариант использования другого способа создания выпадающего списка в данном случае?
Очень нужна ваша помощь.
Заранее огромное спасибо!