Содержание
- 1 Раскрывающиеся списки
- 2 Удаление раскрывающегося списка
- 3 Раскрывающийся список в Excel для заполнения таблицы.
- 4 Выпадающий список в ячейке листа
- 5 Выбор из раскрывающегося списка
- 6 Видео
- 7 Дополнительные сведения
- 8 Создание раскрывающегося списка
- 9 Выпадающий список в Excel с подстановкой данных
- 10 Выпадающий список в Excel с данными с другого листа/файла
- 11 Как сделать зависимые выпадающие списки
- 12 Выбор нескольких значений из выпадающего списка Excel
- 13 Выпадающий список с поиском
Раскрывающиеся списки
- Смотрите также
любое вводе первых буквЕсли эта кнопка нажата, содержимое нужной по Ribbon) диапазон, например прайс-лист.если Excel 2007В Excel можно нам нужно написатьЩелкнитеCTRL> пользователям выбрать элемент
Содержание курса
1:01 Вот код который меня будет много я имел вБыстрый поиск элемента по с клавиатуры(!), чего то мы можем счету ячейки изНайдите значок выпадающего списка Тогда при дописывании или новее - сделать выпадающий список фамилию «Петров». Мы
Данные.Проверка данных раскрывающегося списка или4:37 я написал но и цветов ДСП виду. Проблемая такая. первым буквам нет у всех настраивать параметры выпадающего
диапазона: среди элементов управления новых товаров к откройте вкладку с картинками, фото. нажимаем правой мышкой>Щелкните. просто сообщить, что2:47 но не работает и кромок, получится есть например цвет
нет остальных способов. Приятным списка, нажав соседнююЭтот способ частично напоминает форм (не ActiveX!). прайсу, они будутФормулы (Formulas) Читайте статью "Раскрывающийся на ячейку А16.Проверка данныхДанныеНа вкладке ячейка содержит раскрывающийся6:20
Private Sub Worksheet_SelectionChange(ByVal огромная таблица. Поэтому ДСП серый. у
нет моментом, также, является кнопку
Удаление раскрывающегося списка
предыдущий. Основное отличие Ориентируйтесь по всплывающим
автоматически добавляться ки воспользуйтесь кнопкой
список в Excel Из контекстного меню
.>Параметры список, можно добавитьРаботу с листом можно Target As Range)
хочу перечислить все него есть толькода возможность настройки визуальногоСвойства (Properties)
в том, что подсказкам - выпадающему списку. ЕщеДиспетчер имен (Name Manager) с картинками".
выбираем функцию «ВыбратьНа вкладкеПроверка данных
нажмите кнопку подсказку, которая будет сделать более эффективной Dim Found_color As варианты в раскрывающемся определенные цвета кромокНеобходимость использования дополнительной функции представления (цветов, шрифтов, которая откроет окно на лист добавляетсяПоле со списком одним часто используемым, затемУ кого мало времени из раскрывающегося списка».Параметры.Очистить все отображаться при выделении с помощью раскрывающихся Range Dim s1 списке, а потом
(они перечислены вИНДЕКС и т.д.) со списком всех не элемент управления,:
трюком для такихСоздать
и нужно быстро Выбираем нужное слово,нажмите кнопкуНа вкладке. ячейки. Кроме того,
списков. Пользователю вашего As String Dim этот список как-то раскр. списке). Теперьнет
При использовании этого способа, возможных настроек для а элемент ActiveXЩелкните по значку списков является создание
. Введите имя (можно ухватить суть - фамилию.
Очистить всеПараметрыНажмите кнопку можно указать сообщение листа достаточно будет Color As String подставить. Возможно ли если я выбираюда также возможно указывать выделенного объекта:"Поле со списком" и нарисуйте небольшой связанных выпадающих списков любое, но обязательно смотрим обучающее видео:Второй вариант.нажмите кнопкуОК об ошибке, отображаемое щелкнуть стрелку и
Dim Color_1 As сделать так? этот цвет внет в качествеСамые нужные и полезные
из раскрывающегося набора горизонтальный прямоугольник -
(когда содержимое одного без пробелов иКому интересны подробности и.Нажмите кнопку
Очистить все. при неправильном выборе. выбрать запись в String With Worksheets("Цветовой
AlexM цветовом шаблоне, тоВозможность создания связанных выпадающих
ListFillRange свойства, которые можно под кнопкой будущий список. списка меняется в начать с буквы!)
Раскрывающийся список в Excel для заполнения таблицы.
Выпадающий список в ячейке листа
Видео
этого ДСП.And_rey123 нескольких строк, указав для списка. Выделить
Механизм добавления тот же. В появившемся диалоговом нового объекта -ОК
Способ 1. Примитивный
ячейке под столбцом Выбираем функцию "Выбрать нем, см. статью все проверки данных где они находятся, лист с данными. ими. = Found_color.Offset(, 1).Validation.Formula1 подставлять.Иначе сделайте простоAnd_rey123: Здравствуйте, помогите организовать дополнительно, что выводить мышью диапазон он – выбираем объект окне задайте элемента управления "поле. с данными, команда из раскрывающегося списка".
Способ 2. Стандартный
- Добавление и удаление с листа, включая воспользуйтесь диалоговым окном При этом их
- Раскрывающиеся списки Worksheets("Цветовой шаблон").Cells(6, 6) второй список и: Видимо нужен зависимый выбор из раскрывающегося нужно два столбца не даст, надо из списка иФормировать список по диапазону со списком" сВыделите ячейки (можно сразу контекстного меню Но в списке элементов раскрывающегося списка. раскрывающиеся списки, ноВыделить группу ячеек легко отобразить снова,(1:01) = Split(Color, ";") выбирайте сами как выпадающий список списка сделанного через (свойство просто вписать его рисуем его на- выделите ячейки
- последующей привязкой его несколько), в которыхВыбрать из раскрывающегося списка будут и словаРассмотрим еще один вы не знаете,. Для этого нажмите если вам потребуетсяВводить данные проще и End Sub и в первом(независимо)OLEGOFF проверку данных. СутьColumnCount руками с клавиатуры листе. А вот с наименованиями товаров, к диапазонам на хотите получить выпадающий
(Choose from drop-down list) из ячеек, расположенных вариант
где они находятся,
клавиши что-нибудь исправить. Кроме быстрее, если наборAlexMAnd_rey123: Да нужен зависимый проблемы: есть 2-е=2). Тогда можно получить (например, Лист2!A1:A5) дальше начинаются серьезные которые должны попасть листе. Для этого: список и выберитеили нажать сочетание выше пустой ячейкираскрывающегося списка в Excel. воспользуйтесь диалоговым окномCTRL+G
Способ 3. Элемент управления
того, можно заблокировать допустимых записей ограничен: посмотрите решение в: Попробую сформулировать по список, как его таблицы, одна со весьма привлекательные результаты,LinkedCell
- отличия от предыдущего в списокВ Excel 2007/2010 откройте в меню (на клавиш и ниже этойЭтот список образуетсяВыделить группу ячеек, в открывшемся диалоговом и защитить паролем с помощью раскрывающегося файле другому. Все таки можно сделать? списком цветов дсп, окупающие все потраченные- связанная ячейка, способа.Связь с ячейкой вкладку вкладке)ALT+стрелка вниз ячейки, где стоит в Excel автоматически
- . Для этого нажмите окне нажмите кнопку отдельные ячейки или списка. В этомВернеее работает, но нужен VBA код.And_rey123
каждому цвету соответствуют на дополнительные настройки куда будет выводитьсяВо-первых, созданный выпадающий ActiveX - - укажите ячейкуРазработчик (Developer)Данные – Проверка (Data. Способ не работает, курсор. и предназначен для
- клавишиВыделить даже весь лист. случае при выделении в ячейку Cells(6,
- где в свойстве: Зависимые выпадающие списки определенные цвета кромок, усилия: выбранный из списка
- список может находится куда нужно выводить. В более ранних – Validation) если ячейку иЭтот способ может помочь заполнения ячеек таблицы.CTRL+G
, выберите пунктСводка курса Краткое описание ячейки появляется направленная
6) вставляется не листа "цветовой шаблон",Тут посмотрите выпадают из раскрывающегосяСпособ 1. элемент в двух принципиально порядковый номер выбранного версиях – панель
Способ 4. Элемент ActiveX
. Из выпадающего списка столбец с данными при заполнении таблицы. В этом выпадающем, в открывшемся диалоговомПроверка данных ключевых моментов этого вниз стрелка списка, список, а только при изменении значенияOLEGOFF списка. (см страницуПримитивный
ListRows разных состояниях - пользователем элемента. инструментовТип данных (Allow) отделяет хотя бы Вариантов заполнения ячеек списке перечислены слова
окне нажмите кнопку, а затем — курса. которую можно щелкнуть, первое значение из в ячейки D6: Тут еще несколько "Цвета"). А наСпособ 2.- количество отображаемых режиме отладки, когдаКоличество строк спискаФормы (Forms)выберите вариант одна пустая строка есть больше – из вышестоящих ячеекВыделитьВсехДополнительные курсы см. на чтобы выбрать один
списка. Как это , производился поиск вариантов решений Использование странице "Цветовой шаблон"Стандартный строк можно настраивать его- сколько строкчерез менюСписок (List)
или вам нужен с помощью формул, таблицы до пустой
- , выберите пунктили сайте Обучение работе из элементов. исправить? в на листе адреса по которому необходимо чтобы приСпособ 3.
- Font параметры и свойства, показывать в выпадающемВид – Панели инструментови введите в
- товар, который еще др. Здесь речь ячейки.
- Проверка данныхЭтих же с Microsoft Office.Элементы раскрывающегося списка (4:37)AlexM, да так
- "Цвета" в диапазоне получает данные конкретная указании какаго либоЭлемент управления- шрифт, размер,
двигать его по списке. По умолчанию – Формы (View строчку ни разу не идет о простомИтак,, а затем —. Далее повторите действия,Ненужный раскрывающийся список на Чтобы добавить элементы тоже работает, но B6:B10. по значению
ячейка для иных из цветов, чтобыСпособ 4. начертание (курсив, подчеркивание листу и менять – 8, но – Toolbars -Источник (Source) вводился выше: написании текста враскрывающийся список в ExcelВсех описанные выше. листе можно удалить. в раскрывающийся список, хотелось бы попробовать внесенному в ячейку операций
Итоговая сравнительная таблица всех способов
в ячейку "цветЭлемент ActiveX | и т.д. кроме размеры и - | можно больше, чего Forms) | знак равенства иВыделите ячейки с данными, |
ячейке. | для заполнения таблицы | или | Если вместо удаления раскрывающегося |
Windows macOS Online можно использовать список | копировать именно сам | D6, и производилось | And_rey123 |
кромки", подставлялись только | Сложность | цвета) | |
режиме ввода, когда не позволяет предыдущий | . Если этой вкладки | имя диапазона (т.е. | которые должны попасть |
а) Или, начать. | Этих же | списка вы решили | |
с разделителями-запятыми, а раскрывающийся список.Из Е6 | бы копирование ячейки | : OLEGOFF, причитал эту | цвета кромки именно |
Выбор из раскрывающегося списка
низкаяForeColor единственное, что можно способ. не видно, то=Товары в выпадающий список печатать слово вМы заполняем столбец,. Далее повторите действия, изменить параметры вВыделите ячейку, в которой также обычный или раскрывающийся список скопирован (т.е раскрывающегося списка ветку, вариант с для этого цветасредняяи – выбирать изПосле нажатия на нажмите кнопку). (например, наименованиями товаров).
ячейке до тех данные в котором описанные выше. нем, читайте статью
есть раскрывающийся список. именованный диапазон ячеек. в Е7. Можете в ячейке на использованием СМЕЩ мне дсп. Файл свысокаяBackColor него данные. ПереключениеОКОфис – Параметры ExcelНажмитеЕсли у вас Excel пор, пока не повторяются. Чтобы неЕсли вместо удаления раскрывающегося Добавление и удалениеЕсли вы хотите удалить
В этом видео и дальше его против найденной из
не совсем подходит. примером прикладываю. ЗаранееВозможность настройки шрифта, цвета- цвет текста
между этими режимамисписком можно пользоваться.
-
ОК 2003 или старше выйдет подсказка именно писать их каждый списка вы решили элементов раскрывающегося списка. несколько таких ячеек,
показано, как использовать копировать. диапазона) и все Я рассматривал такой спасибо и т.д. и фона, соответственно происходит с помощьюЧтобы вместо порядкового номерафлажок. – выберите в нужного нам слова, раз, мы их изменить параметры вВыделите ячейку, в которой выделите их, удерживая обычные и именованныеЦвет кромок в что скопировано должно вариант, но тогдаOLEGOFF
нетБольшим и жирным плюсом кнопки элемента выводилось егоОтображать вкладку Разработчик наВсе! Наслаждайтесь! меню особенно если похожих возьмем из раскрывающегося нем, читайте статью
есть раскрывающийся список. нажатой клавишу диапазоны. таблице на листе вставиться в ячейку необходимо делать дополнительную: And_rey123,в ячейку F6нет этого способа являетсяРежим Конструктора (Design Mode) название можно дополнительно ленте (Office ButtonВажный нюанс. В качествеВставка – Имя - слов несколько. списка. Мы составляем Добавление и удалениеЕсли вы хотите удалитьCTRLПодсказки при вводе и "Цвета" можно добавлять F6 Листа "цветовой таблицу, в которой подставьте формулу Кодда
возможность быстрого переходана вкладке использовать функцию
– Excel Options источника данных для Присвоитьб) Или, выбрать список в столбце элементов раскрывающегося списка. несколько таких ячеек,. сообщения об ошибках или убирать. Список шаблон". Как теперь нужно перечислять все =ВПР(D6;Цвета!B$6:C$10;2;0)Количество отображаемых строк к нужному элементуРазработчик (Developer)ИНДЕКС (INDEX)
– Show Developer списка может выступать(Insert – Name -
из раскрывающегося списка А.Выделите ячейки, в которых выделите их, удерживаяЩелкните (2:47) Чтобы помочь сам подстроится.
это описать кодом варианты цветом иAnd_rey123всегда 8 в списке при:, которая умеет выводить Tab in the и динамический именованный
Define), Excel нужное слово.В следующей ячейке А16 есть раскрывающиеся списки. нажатой клавишу
Чтобы упростить работу пользователей с листом, добавьте в ячейки раскрывающиеся списки. Раскрывающиеся списки позволяют пользователям выбирать элементы из созданного вами списка.
Видео
На новом листе введите данные, которые должны отображаться в раскрывающемся списке. Желательно, чтобы элементы списка содержались в таблице Excel. Если это не так, список можно быстро преобразовать в таблицу, выделив любую ячейку диапазона и нажав клавиши CTRL+T.
Почему данные следует поместить в таблицу? Потому что в этом случае при добавлении и удалении элементов все раскрывающиеся списки, созданные на основе этой таблицы, будут обновляться автоматически. Дополнительные действия не требуются.
Выделите на листе ячейку, в которую требуется поместить раскрывающийся список.
На ленте откройте вкладку Данные и нажмите кнопку Проверка данных.
Примечание: Если кнопка Проверка данных недоступна, возможно, лист защищен или является общим. Разблокируйте определенные области защищенной книги или отмените общий доступ к листу, а затем повторите шаг 3.
На вкладке Параметры в поле Тип данных выберите пункт Список.
Щелкните поле Источник и выделите диапазон списка. В примере данные находятся на листе "Города" в диапазоне A2:A9. Обратите внимание на то, что строка заголовков отсутствует в диапазоне, так как она не является одним из вариантов, доступных для выбора.
Если можно оставить ячейку пустой, установите флажок Игнорировать пустые ячейки.
Установите флажок Список допустимых значений
Откройте вкладку Подсказка по вводу.
Если вы хотите, чтобы при выборе ячейки появлялось всплывающее сообщение, установите флажок Отображать подсказку, если ячейка является текущей и введите заголовок и сообщение в соответствующие поля (до 225 символов). Если вы не хотите, чтобы сообщение отображалось, снимите этот флажок.
Откройте вкладку Сообщение об ошибке.
Если вы хотите, чтобы при вводе значения, которого нет в списке, появлялось всплывающее сообщение, установите флажок Выводить сообщение об ошибке, выберите параметр в поле Вид и введите заголовок и сообщение. Если вы не хотите, чтобы сообщение отображалось, снимите этот флажок.
Не знаете, какой параметр выбрать в поле Вид?
Чтобы отобразить сообщение, не препятствующее вводу данных, которые не содержатся в раскрывающемся списке, выберите вариант Сообщение или "Предупреждение". Если выбрать вариант "Сообщение", сообщение будет отображаться со значком , а если "Предупреждение" — со значком .
Чтобы заблокировать пользователям ввод данных, которые не содержатся в раскрывающемся списке, выберите вариант Остановка.
Примечание: Если вы не добавили заголовок и текст, по умолчанию выводится заголовок "Microsoft Excel" и сообщение "Введенное значение неверно. Набор значений, которые могут быть введены в ячейку, ограничен".
После создания раскрывающегося списка убедитесь, что он работает правильно. Например, рекомендуется проверить, изменяется ли ширина столбцов и высота строк при отображении всех ваших записей.
Если список элементов для раскрывающегося списка находится на другом листе и вы хотите запретить пользователям его просмотр и изменение, скройте и защитите этот лист. Подробнее о защите листов см. в статье Блокировка ячеек.
Если вы решили изменить элементы раскрывающегося списка, см. статью Добавление и удаление элементов раскрывающегося списка.
Чтобы удалить раскрывающийся список, см. статью Удаление раскрывающегося списка.
Предлагаем скачать образец книги с несколькими примерами проверки данных, аналогичными примеру в этой статье. Вы можете воспользоваться ими или создать собственные сценарии проверки данных. Скачать примеры проверки данных Excel.
Ввод данных станет быстрее и точнее, если ограничить значения в ячейке вариантами из раскрывающегося списка.
Сначала создайте на листе список допустимых элементов, а затем выполните сортировку или расположите их в нужном порядке. В дальнейшем эти элементы могут служить источником для раскрывающегося списка данных. Если список небольшой, на него можно легко ссылаться и вводить элементы прямо в средстве проверки данных.
Создайте список допустимых элементов для раскрывающегося списка. Для этого введите элементы на листе в одном столбце или строке без пустых ячеек.
Выделите ячейки, для которых нужно ограничить ввод данных.
На вкладке Данные в группе Инструменты нажмите кнопку Проверка данных или Проверить.
Примечание: Если команда проверки недоступна, возможно, лист защищен или книга является общей. Если книга является общей или лист защищен, изменить параметры проверки данных невозможно. Дополнительные сведения о защите книги см. в статье Защита книги.
Откройте вкладку Параметры и во всплывающем меню Разрешить выберите пункт Список.
Щелкните поле Источник и выделите на листе список допустимых элементов.
Диалоговое окно свернется, чтобы было видно весь лист.
Нажмите клавишу ВВОД или кнопку Развернуть , чтобы развернуть диалоговое окно, а затем нажмите кнопку ОК.
Значения также можно ввести непосредственно в поле Источник через запятую.
Чтобы изменить список допустимых элементов, просто измените значения в списке-источнике или диапазон в поле Источник.
Можно указать собственное сообщение об ошибке, которое будет отображаться при вводе недопустимых данных. На вкладке Данные нажмите кнопку Проверка данных или Проверить, а затем откройте вкладку Сообщение об ошибке.
См. также
На новом листе введите данные, которые должны отображаться в раскрывающемся списке. Желательно, чтобы элементы списка содержались в таблице Excel.
Почему данные следует поместить в таблицу? Потому что в этом случае при добавлении и удалении элементов все раскрывающиеся списки, созданные на основе этой таблицы, будут обновляться автоматически. Дополнительные действия не требуются.
Выделите на листе ячейку, в которую требуется поместить раскрывающийся список.
На ленте откройте вкладку Данные и нажмите кнопку Проверка данных.
На вкладке Параметры в поле Разрешить выберите пункт Список.
Если вы уже создали таблицу с элементами раскрывающегося списка, щелкните поле Источник и выделите ячейки, содержащие эти элементы. Однако не включайте в него ячейку заголовка. Добавьте только ячейки, которые должны отображаться в раскрывающемся списке. Список элементов также можно ввести непосредственно в поле Источник через запятую. Например:
Фрукты;Овощи;Зерновые культуры;Молочные продукты;Перекусы
Если можно оставить ячейку пустой, установите флажок Игнорировать пустые ячейки.
Установите флажок Список допустимых значений
Откройте вкладку Сообщение для ввода.
Если вы хотите, чтобы при выборе ячейки появлялось всплывающее сообщение, установите флажок Показывать сообщения и введите заголовок и сообщение в соответствующие поля (до 225 символов). Если вы не хотите, чтобы сообщение отображалось, снимите этот флажок.
Откройте вкладку Сообщение об ошибке.
Если вы хотите, чтобы при вводе значения, которого нет в списке, появлялось всплывающее сообщение, установите флажок Показывать оповещения, выберите параметр в поле Тип и введите заголовок и сообщение. Если вы не хотите, чтобы сообщение отображалось, снимите этот флажок.
Нажмите кнопку ОК.
После создания раскрывающегося списка убедитесь, что он работает правильно. Например, рекомендуется проверить, изменяется ли ширина столбцов и высота строк при отображении всех ваших записей. Если вы решили изменить элементы раскрывающегося списка, см. статью Добавление и удаление элементов раскрывающегося списка. Чтобы удалить раскрывающийся список, см. статью Удаление раскрывающегося списка.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.
Под выпадающим списком понимается содержание в одной ячейке нескольких значений. Когда пользователь щелкает по стрелочке справа, появляется определенный перечень. Можно выбрать конкретное.
Очень удобный инструмент Excel для проверки введенных данных. Повысить комфорт работы с данными позволяют возможности выпадающих списков: подстановка данных, отображение данных другого листа или файла, наличие функции поиска и зависимости.
Создание раскрывающегося списка
Путь: меню «Данные» – инструмент «Проверка данных» – вкладка «Параметры». Тип данных – «Список».
Ввести значения, из которых будет складываться выпадающий список, можно разными способами:
- Вручную через «точку-с-запятой» в поле «Источник».
- Ввести значения заранее. А в качестве источника указать диапазон ячеек со списком.
- Назначить имя для диапазона значений и в поле источник вписать это имя.
Любой из вариантов даст такой результат.
Выпадающий список в Excel с подстановкой данных
Необходимо сделать раскрывающийся список со значениями из динамического диапазона. Если вносятся изменения в имеющийся диапазон (добавляются или удаляются данные), они автоматически отражаются в раскрывающемся списке.
- Выделяем диапазон для выпадающего списка. В главном меню находим инструмент «Форматировать как таблицу».
- Откроются стили. Выбираем любой. Для решения нашей задачи дизайн не имеет значения. Наличие заголовка (шапки) важно. В нашем примере это ячейка А1 со словом «Деревья». То есть нужно выбрать стиль таблицы со строкой заголовка. Получаем следующий вид диапазона:
- Ставим курсор в ячейку, где будет находиться выпадающий список. Открываем параметры инструмента «Проверка данных» (выше описан путь). В поле «Источник» прописываем такую функцию:
Протестируем. Вот наша таблица со списком на одном листе:
Добавим в таблицу новое значение «елка».
Теперь удалим значение «береза».
Осуществить задуманное нам помогла «умная таблица», которая легка «расширяется», меняется.
Теперь сделаем так, чтобы можно было вводить новые значения прямо в ячейку с этим списком. И данные автоматически добавлялись в диапазон.
- Сформируем именованный диапазон. Путь: «Формулы» – «Диспетчер имен» – «Создать». Вводим уникальное название диапазона – ОК.
- Создаем раскрывающийся список в любой ячейке. Как это сделать, уже известно. Источник – имя диапазона: =деревья.
- Снимаем галочки на вкладках «Сообщение для ввода», «Сообщение об ошибке». Если этого не сделать, Excel не позволит нам вводить новые значения.
- Вызываем редактор Visual Basic. Для этого щелкаем правой кнопкой мыши по названию листа и переходим по вкладке «Исходный текст». Либо одновременно нажимаем клавиши Alt + F11. Копируем код (только вставьте свои параметры).
- Сохраняем, установив тип файла «с поддержкой макросов».
- Переходим на лист со списком. Вкладка «Разработчик» – «Код» – «Макросы». Сочетание клавиш для быстрого вызова – Alt + F8. Выбираем нужное имя. Нажимаем «Выполнить».
Когда мы введем в пустую ячейку выпадающего списка новое наименование, появится сообщение: «Добавить введенное имя баобаб в выпадающий список?».
Нажмем «Да» и добавиться еще одна строка со значением «баобаб».
Выпадающий список в Excel с данными с другого листа/файла
Когда значения для выпадающего списка расположены на другом листе или в другой книге, стандартный способ не работает. Решить задачу можно с помощью функции ДВССЫЛ: она сформирует правильную ссылку на внешний источник информации.
- Делаем активной ячейку, куда хотим поместить раскрывающийся список.
- Открываем параметры проверки данных. В поле «Источник» вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).
Имя файла, из которого берется информация для списка, заключено в квадратные скобки. Этот файл должен быть открыт. Если книга с нужными значениями находится в другой папке, нужно указывать путь полностью.
Как сделать зависимые выпадающие списки
Возьмем три именованных диапазона:
Это обязательное условие. Выше описано, как сделать обычный список именованным диапазоном (с помощью «Диспетчера имен»). Помним, что имя не может содержать пробелов и знаков препинания.
- Создадим первый выпадающий список, куда войдут названия диапазонов.
- Когда поставили курсор в поле «Источник», переходим на лист и выделяем попеременно нужные ячейки.
- Теперь создадим второй раскрывающийся список. В нем должны отражаться те слова, которые соответствуют выбранному в первом списке названию. Если «Деревья», то «граб», «дуб» и т.д. Вводим в поле «Источник» функцию вида =ДВССЫЛ(E3). E3 – ячейка с именем первого диапазона.
Выбор нескольких значений из выпадающего списка Excel
Бывает, когда из раскрывающегося списка необходимо выбрать сразу несколько элементов. Рассмотрим пути реализации задачи.
- Создаем стандартный список с помощью инструмента «Проверка данных». Добавляем в исходный код листа готовый макрос. Как это делать, описано выше. С его помощью справа от выпадающего списка будут добавляться выбранные значения.
- Чтобы выбранные значения показывались снизу, вставляем другой код обработчика.
- Чтобы выбираемые значения отображались в одной ячейке, разделенные любым знаком препинания, применим такой модуль.
Private Sub Worksheet_Change( ByVal Target As Range)
On Error Resume Next
If Not Intersect(Target, Range( "C2:C5" )) Is Nothing And Target.Cells.Count = 1 Then
Application.EnableEvents = False
newVal = Target
Application.Undo
oldval = Target
If Len(oldval) <> 0 And oldval <> newVal Then
Target = Target & "," & newVal
Else
Target = newVal
End If
If Len(newVal) = 0 Then Target.ClearContents
Application.EnableEvents = True
End If
End Sub
Не забываем менять диапазоны на «свои». Списки создаем классическим способом. А всю остальную работу будут делать макросы.
Выпадающий список с поиском
- На вкладке «Разработчик» находим инструмент «Вставить» – «ActiveX». Здесь нам нужна кнопка «Поле со списком» (ориентируемся на всплывающие подсказки).
- Щелкаем по значку – становится активным «Режим конструктора». Рисуем курсором (он становится «крестиком») небольшой прямоугольник – место будущего списка.
- Жмем «Свойства» – открывается перечень настроек.
- Вписываем диапазон в строку ListFillRange (руками). Ячейку, куда будет выводиться выбранное значение – в строку LinkedCell. Для изменения шрифта и размера – Font.
При вводе первых букв с клавиатуры высвечиваются подходящие элементы. И это далеко не все приятные моменты данного инструмента. Здесь можно настраивать визуальное представление информации, указывать в качестве источника сразу два столбца.