Выпадающий список в ячейке
| Категория: Выпадающие списки, просмотров: 133631, опубликовано: 04.04.2010 |
Способ 1. Примитивный
Один щелчок правой кнопкой мыши по пустой ячейке под столбцом с данными, команда контекстного меню Выбрать из раскрывающегося списка (Choose from drop-down list) или нажать сочетание клавиш Alt+стрелка вниз. Способ не работает, если ячейку и столбец с данными отделяет хотя бы одна пустая строка.
Способ 2. Стандартный
- Выделите ячейки с данными, которые должны попасть в выпадающий список (например, наименованиями товаров).
- Выберите в меню Вставка - Имя - Присвоить (Insert - Name - Define) и введите имя (можно любое, но обязательно без пробелов!) для выделенного диапазона (например Товары). Нажмите ОК.
- Выделите ячейки (можно сразу несколько), в которых хотите получить выпадающий список и выберите в меню Данные - Проверка (Data - Validation). На первой вкладке Параметры из выпадающего списка Тип данных выберите вариант Список и введите в строчку Источник знак равно и имя диапазона (т.е. =Товары).
Нажмите ОК.
Все! Наслаждайтесь!
Важный нюанс. В качестве источника данных для списка может выступать и динамический именованный диапазон, например прайс-лист. Тогда при дописывании новых товаров к прайсу, они будут автоматически добавляться к выпадающему списку.
Способ 3. Элемент управления
Этот способ представляет собой вставку на лист нового объекта - элемента управления "поле со списком" с последующей привязкой его к диапазонам на листе. Для этого:
- В Excel 2007 откройте вкладку Разработчик (Developer). В более ранних версиях - панель инструментов Формы (Forms) через меню Вид - Панели инструментов - Формы (View - Toolbars - Forms). Если этой вкладки не видно, то нажмите кнопку Офис - Параметры Excel - флажок Отображать вкладку Разработчик на ленте (Office Button - Excel Options - Show Developer Tab in the Ribbon)
- Найдите значок выпадающего списка среди элементов управления форм (не ActiveX!). Ориентируйтесь по всплывающим подсказкам - Поле со списком:
- Щелкните по значку и нарисуйте небольшой горизонтальный прямоугольник - будущий список.
- Щелкните по нарисованному списку правой кнопкой мыши и выберите команду Формат объекта (Format control). В появившемся диалоговом окне задайте
- Формировать список по диапазону - выделите ячейки с наименованиями товаров, которые должны попасть в список
- Связь с ячейкой - укажите ячейку куда нужно выводить порядковый номер выбранного пользователем элемента.
- Количество строк списка - сколько строк показывать в выпадающем списке. По умолчанию - 8, но можно больше, чего не позволяет предыдущий способ.
После нажатия на ОК списком можно пользоваться.
Чтобы вместо порядкового номера элемента выводилось его название можно дополнительно использовать функцию ИНДЕКС (INDEX), которая умеет выводить содержимое нужной по счету ячейки из диапазона:
Ссылки по теме:
- Выпадающий список с данными из другого файла
- Создание зависимых выпадающих списков
- Автоматическое создание выпадающих списков при помощи инструментов надстройки PLEX
- Выбор фото из выпадающего списка
- Автоматическое удаление уже использованных элементов из выпадающего списка
- Создание динамических именованных диапазонов
- Выпадающий список с автоматическим добавлением новых элементов
Комментарии:
| светланка 24.11.2006 |
круто все понятно!. |
| Александр 25.03.2007 |
А если я в любую ячейку столбца "а" захотел написать, например строку "мандарины", и чтобы в выпадающий список она дабавилась. Как это сделать? . |
| Николай 27.03.2007 |
Это можно реализовать макросом на VBA.. |
| Александр 28.03.2007 |
Если не секрет, то как?. |
| tolikt 10.04.2007 |
Однако, не всё так просто, если в качестве Источника указан именованный диапазон. В этом случае в ячейку вручную можно завести любое значение и проверка на список не работает. По крайней мере, в Excel 97 так. . |
| Николай 14.04.2007 |
Tolkit, а у вас в окне Проверка вводимых значений на вкладке Сообщение об ошибке стоит галочка? Скорее всего нет, поэтому и можно вручную вписать недопустимые значения.. |
| Аркадий. 01.08.2007 |
Все до боли просто оказалось. Спасибо.. |
| Владимир 08.08.2007 |
Только во втором случае, если список получится большой (больше ста значений), выбирать данные при помощи мышки не очень удобно, а поиск с клавиатуры не работает. |
| Владимир 19.11.2007 |
как -то про списки пробельчик есть...с диапазоном данных с одного и того-же листа есть, с другого файла то же. А с другого листа этого же файла? Описанным выше образом не получается... . |
| Игорь 24.12.2007 |
Как в выпадающем списке создать поиск элемента по первым буквам и отображением его в определённой ячейке. |
| Александр 01.02.2008 |
Тому, кто выложил эту тему - респект! Искал, спрашивал - все тупят.. |
| Михаил 06.02.2008 |
То: Владимир А ты в поле "Источник" "Проверки вводимых значений" используй формулу, только вместо другой книги укажи лист текущей книги "=ДВССЫЛ("Лист1!$A$2:$A$6")". |
| Отцы 12.02.2008 |
Пасиба....подрочили. |
| Юрий 19.02.2008 |
Меня так же очень сильно интересует вопрос Игоря "Как в выпадающем списке создать поиск элемента по первым буквам и отображением его в определённой ячейке". Или создать такой поиск прямо в ячейке. А у меня этот пример не работает, т.к. использую тысячи уникальных записей, плюс они не по алфавиту.. |
| MaGong 26.02.2008 |
А как быть в такой ситуации: Есть список: дата отгрузки, модель, цена... Список пополняется в течение месяца. Мне нужно создать новый выпадающий список из дат отгрузки, но даты в списке повторяющиеся. Как сделать чтобы в выпадающем списке показывались только уникальные?. |
| Мария 09.04.2008 |
завела два разных списка на двух разных листах чтобы вставлять в таблице на первом листе. Дает вставить только какой-то один список.... |
| Мария 09.04.2008 |
хорошая тема). |
| Сергей 16.04.2008 |
Создать список - это удобно, только теперь автофильтр не работает!!!!, что делать ?. |
| Shavkat 21.07.2008 |
В поле источник ввел =Товары, но поле Товары у меня длинный и пополняется постепенно, и в выпадающем меню выводятся пустые элементы списка. Как сделать так чтоб эти пустые элементы не выводились, при этом в столбце Товары оставались бы пустые ячейки. Заранее благодарю. |
| Дмитрий 12.08.2008 |
Спасибо автору. |
| supewrong 13.08.2008 |
как по выпадающему списку быстро переходить в нужный раздел, если это список товаров, я кликаю на одно из наименований товаров при этом оно просто появляется в общем заголовке, но не переходит к строке об этом товаре.. |
| kay 28.09.2008 |
А как увеличить длинну выпадающего списка, что бы отображалось не 8 значений и ползунок сбоку, а например штук 20 значений. Облегчило бы выбор? Всем спасибо. |
| хуй 15.10.2008 |
а може це все хуйня?. |
| Андрей 24.10.2008 |
А зачем манипулировать с именем диапазона? Убираем 1 и 2 шаг. А на 3 шаге вместо вставки имени просто выделяем ячейки для списка.. |
| forXpen 01.12.2008 |
Большое спасибо!!! . |
| Павел 04.12.2008 |
Спасибо. |
| Влад 20.12.2008 |
Спасибо за помощь!!!!!!. |
| Гарик 24.12.2008 |
kay спрашивал: А как увеличить длинну выпадающего списка, что бы отображалось не 8 значений и ползунок сбоку, а например штук 20 значений. Облегчило бы выбор? Всем спасибо. Меня тоже интересует. И от меня спасибо.. |
25.12.2008 |
Это для Excel 2003. А то же самое для 2007?. |
25.12.2008 |
Виноват, нашел.. |
| qwerty 30.01.2009 |
Подскажите как увеличить размер (шрифт) текста в выпадающем списке, никак не догоню. |
| qwerty 30.01.2009 |
у меня шрифт выпадающего списка 24, а шрифт источника - 14 (хотя может это и не важно), короче мне бы в списке шрифи покрупнее бы - подскажите. |
| Тилиуленшпигель 10.02.2009 |
Ща кончу. |
| BasTaller 10.02.2009 |
Обновление данных в списке после обновления данных в именнованном диапазоне. Спасибо! Статья написана в отличном, и главное - понятном стиле. Однако возникает вопрос к автору - возможно ли в Excel сделать так, чтобы при обновлении данных в именованном диапазоне (ну, скажем, вначале написали "апИльсины", а потом, когда уже есть куча списков с выбранным пунктом "апИльсины", заменить их на "Апельсины") данные обновились не только в диапазоне, но и в самих ячейках, на которые наложен список (в самом-то списке значение обновится, а вот в ячейке...) . |
| Станислав 19.02.2009 |
А как выпадающий список увеличить, т.е. кол-во сторочек чтоб было не 6, а 15, например? Кто знает - подскажите!. |
| karmasoer 06.08.2009 |
У меня задача: в 1 столбце есть список отделов, во втором должны быть фамилии сотрудников, но вбивать их надо вручную(это для описания тех, кто улетел в командировку). Как сделать так, чтобы при выборе элемента из 1ого столбца во втором появлялась пустая строка для заполнения фамилии, а если фамилий несколько, то строки выпадают дальше автомотически. При этом, в 1ом столбце надо тоже при выборе 1ого элемента сделать так. чтоб автомотически выпадала строка для следующего отдела и т.д. (потому что могут улететь сотрудники из нескольких отделов. а оставлять пустые строки нехорошо). В общем, пробую всё: СМЕЩ, ВПР, ПОИСКПОЗ, ИНДЕКС, ГПР (в различных комбинациях) - ничего не выходит. Помогите, пожалуйста. Работаю в Excel 2007. Заранее спасибо! . |
| karmasoer 06.08.2009 |
Кстати, статью про зависимые выпадающие списки не предлагать - там я решения не нашла)))) . |
| Grooooovy 12.08.2009 |
karmasoer, очень непонятно пишете. Особенно второе предложение.. |
| karmasoer 14.08.2009 |
Спасибо, я вроде вижу выход: Range("F12").Select Selection.Offset(1, 0).EntireRow.Insert Это позволяет мне писать выбирать из списка любой отдел и вписывать сотрудников в заранее добавляемые строки. В следующий раз буду писать понятнее)))). |
| Const 30.08.2009 |
Не нашел ответ на вопрос об увеличении списка - подскажите, плиз, как увеличить размер выпадающего списка Excel, заранее благодарю.. |
| Антон 10.09.2009 |
Спасибо!. |
| ZloBist 24.09.2009 |
Каким образом привязать данные одной ячейки при выборе другой? Пример: Есть несколько фирм, у каждой есть свой адрес и котактное лицо. Задача: При выборе одного поставщика в соседней ячейке (нижней) автоматически привязывается соответсвующий фирме адрес и котактное лицо.. |
| BigFoot 02.10.2009 |
функции СМЕЩ и ПОИСКПОЗ в помощь! коротко сам принцип: если в ПОИСКПОЗ передадим значение выбранное из списка, и сссылку на сам список фирм, функция вернет позицию этого значения в списке. Передаем полученную позицию и первую ячейку диапазона адресов фирм в СМЕЩ, она возвращает адрес фирмы, стоящий в этой позиции. за синтаксисом функций - в хелп!. |
| asas 06.11.2009 |
Это для Excel 2003. А то же самое для 2007?. |
| Виталий 11.11.2009 |
BigFoot - не все так просто оказалось.. |
| Виталий 11.11.2009 |
ZloBist -вот что у меня получилось. СМЕЩ(ДВССЫЛ(АДРЕС(ПОИСКПОЗ(D4;Штрих_код;0);1;4;;"base"));0;1) D4 - ячейка в которую заносятся данные из списка, который на листе 'base' Штрих_код - диапазон ячеек списка, на листе 'base' . |
| Юлия 27.11.2009 |
информация не в бровь а в глаз просто понятно и вообще отлично, спасибо . |
| Витка 02.12.2009 |
Чтобы можно было ДОБАВЛЯТЬ ПОЗИЦИИ В СПИСОК для их автоматического попадания в выборку я при регистрации диапазона списка беру не только заполненные ячейки, но и добавляю вниз строк сколько хочу (знаю, например, что у меня будет там 300 позиций, а сейчас всего 100 - выделяю еще 200 пустых). Когда в эти 200 пустых добавляешь позиции - они автоматом выходят в списке. Может быть тупо, но работает. :). |
| Дмитрий 03.12.2009 |
многое из всего не работает в 2007. |
| Витка 03.12.2009 |
Автору ОГРОМНОЕ СПАСИБО. Много задач умею решать с помощью моего любимого приложения, но здесь еще больше возможностей. Вы профи!!! Удачи и ждем ваших новых находок!. |
| Тимур 07.12.2009 |
Я видел комент что кто-то уже НАШЕЛ как тоже самое повторить в 2007... но я ничего похожего кроме "имя диапазона" найти не смог подскажите плз где искать эти "имя" и "присвоить"? . |
| Андрей 08.12.2009 |
to Тимур: А чего ты не нашел? Выделяешь диапазон с данными, переходишь на вкладку Формулы, там задаешь Имя (Присвоить имя). Далее переходишь на вкладку Данные (Работа с данными) и уже там находишь кнопку "Проверить данные". Дальше думаю разберешься сам. |
| Тимур 08.12.2009 |
Да, нашел уже как (не сам правда а по еще одной статье) Просто лично у меня никак не вяжется фраза "проверка данных" с созданием элемента управления.... ^_________^. |
| Андрей... 22.12.2009 |
prikolno. |
| дима 13.01.2010 |
спасибо. |
| tol 13.01.2010 |
спасибо. |
| Alex 14.01.2010 |
office 2007 как сделать ссылку на другой лист? спасибо. |
| Alex 14.01.2010 |
спасибо разобрался. |
| ave1 15.01.2010 |
спасибо!. |
| Сергей 09.02.2010 |
А по поводу автоподстановки значения из списка пока нет вариантов? действительно, если список большой, то необходимость тащиться мышью не облегчает, а увеличивает время.. |
| Павел 25.02.2010 |
Подскажите, как сделать что бы подтягивалось форматирование текста из диапазона (цвет, шрифт)?!. |
| Ренат 26.02.2010 |
Есть ли возможность увеличить границы выпадающего списка, или они ограничиваются только шириной ячейки?. |
| ЮлЯшКа 01.03.2010 |
Большое спасибо. |
| Виталий 07.03.2010 |
Как сделать, чтобы стрелка не исчезала, чтобы всегда было понятно что эта ячейка содержит список?. |
| Владимир 17.03.2010 |
Подскажите пожалуйста! Как можно сделать, чтоб при выборе определённого значения из выподающего списка менялась вся таблица и этот список был на новой таблице?. |
| Mic 04.04.2010 |
А как на одном листе сделать выбор из разных списков. По фамилии или отделу или по дню рождения?. |
| Иван 10.04.2010 |
Как решить проблему со размером шрифта тож не нашел, зато нашел макрос,который при клике на ячейку со списком увеличивает масштаб листа, ну а при клике по любой другой ячейке, соответственно, уменьшает. Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error Resume Next: res = ActiveCell.Validation.Type ActiveWindow.Zoom = IIf(res = 3, 150, 100) End Sub Лично у меня работает. А, цифирка 150 и есть масштаб. Мона подставить любое другое значение. Вставляется макрос так: ПКМ по ярлыку листа (внизу), выбрать "исходный текст" и в появившемся окне вставить код. И просто закрыть VB. |
| atuy 17.04.2010 |
Спасибо за выложенную информацию, но... вставку на лист нового объекта - элемента управления "поле со списком" вы описали с использованием Excel 2007. А как это сделать в версии 2003. Нигде не нашел: Формировать список по диапазону, Связь с ячейкой и Количество строк списка. Подскажите, пожалуйста, как мне выйти из этого положения. Заранее благодарен. |
| Сергей 17.05.2010 |
Спасибо большое за информацию!. |
| Ирина 18.05.2010 |
Люди, подскажите, пожалуйста, как сделать так, чтоб список предлагал выбрать не только одну позицию, а несколько? Заранее спасибо!. |
| giawww 30.05.2010 |
Подскажите,пожалуйста,как в выппадающем списке(если он очень длиный)сделать так,чтобы при наборе первой буквы список перемещался в сегмент с этой заглавной буквы Email: giawww@yahoo.com. Заранее Спасиво . |
| Надежда 02.06.2010 |
Вопрос такой же как у giawww Как сделать так чтоб при наборе первых букв список перемещался в соответствующий сегмент. Email Nadejda-rabota@yandex.ru Заранее спасибо!. |
| S. Crow 09.06.2010 |
Спасибо! Отличный сайт! Благодаря вам - я полюбил Excel )). |
| lala 19.06.2010 |
123. |
| Андрей 21.06.2010 |
реально помогли! спс!. |
| Yar 23.06.2010 |
Молодцы! Спасибо - сэкономили кучу времени!. |
| я 30.06.2010 |
Спасибо большое! Все получилось!!! :))). |
13.07.2010 |
Спасибо автору!. |
| ПаХа 20.07.2010 |
Добрый день всем! Такой вопрос. А как сделать так, чтоб при выборе из выпадающего списка элемента, кроме него, заполнялись и другие строки первой таблицы!. |
26.07.2010 |
Спасибо за информацию!!! Все работает 100%. |
| Debugger 27.07.2010 |
Спасибо большое!!!! Очень помогло. . |
| Таня 29.07.2010 |
Помогите, пожалуйста избежать повторений у меня связанные выпадающие списки Бюджет =ЕСЛИ(H5="";БюджетList;G5) потом Статья =ЕСЛИ(I5="";СМЕЩ(БюджетStart;ПОИСКПОЗ(G5;БюджетColumn;0)-1;1;СЧЁТЕСЛИ(БюджетColumn;G5);1);H5) потом Подстатья =СМЕЩ(СтатьяStart;ПОИСКПОЗ(G5;БюджетColumn;0)-1+ПОИСКПОЗ(H5;СМЕЩ(БюджетStart;ПОИСКПОЗ(G5;БюджетColumn;0)-1;1;СЧЁТЕСЛИ(БюджетColumn;G5);1);0)-1;1;СЧЁТЕСЛИ(СМЕЩ(БюджетStart;ПОИСКПОЗ(G5;БюджетColumn;0)-1;1;СЧЁТЕСЛИ(БюджетColumn;G5);1);H5);1) берут данные из такой таблицы Бюджет Статья Подстатья МТО Связь Мобильная МТО Связь Стационарная МТО Прочие Прочие Мне нужно чтобы статья не повторялась в выпадающем списке. Помогите!!! . |