Note: The other languages of the website are Google-translated. Back to English

Как создать выпадающий список с возможностью поиска в Excel?

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

Создайте раскрывающийся список с возможностью поиска в Excel
Легко создавайте раскрывающийся список с возможностью поиска с помощью замечательного инструмента

Дополнительные руководства для раскрывающегося списка ...


Создайте раскрывающийся список с возможностью поиска в Excel

Предположим, у вас есть список названий стран в столбце A, как показано на снимке экрана ниже, и теперь вы хотите использовать их в качестве исходных данных для раскрывающихся списков поиска. Для этого вы можете сделать следующее.

Вам нужно вставить поле со списком вместо раскрывающегося списка проверки данных на листе.

1. Если разработчик вкладка не отображается на ленте, вы можете включить разработчик вкладку следующим образом.

1). В Excel 2010 или более поздних версиях щелкните Файл > Доступные опции. И в Параметры Excel диалоговое окно, нажмите Настроить ленту на левой панели. Перейдите к списку «Настроить ленту», установите флажок разработчик поле, а затем щелкните OK кнопка. Смотрите скриншот:

2). В Excel 2007 щелкните Офисы кнопка> Параметры Excel. В Параметры Excel диалоговое окно, нажмите Популярное на левой панели проверьте Показать вкладку разработчика на ленте поле и, наконец, щелкните OK кнопку.

2. После показа разработчик вкладку нажмите разработчик > Вставить > Поле со списком

3. Нарисуйте поле со списком на листе, щелкните его правой кнопкой мыши и выберите Свойства из контекстного меню.

4. в Свойства диалоговое окно, вам необходимо:

1). Выбрать Ложь в AutoWordSelect поле;
2). Укажите ячейку в LinkedCell поле. В этом случае мы вводим A12;
3). Выбрать 2-fmMatchEntryNone в MatchEntry поле;
4). Тип Раскрывающийся список в ListFillRange поле;
5). Закрой Свойства диалоговое окно. Смотрите скриншот:

5. Теперь выключите режим дизайна, нажав разработчик > Режим проектирования.

6. Выберите пустую ячейку, например C2, введите приведенную ниже формулу и нажмите Enter. Они перетаскивают его дескриптор автозаполнения в ячейку C9, чтобы автоматически заполнить ячейки одной и той же формулой. Смотрите скриншот:

=--ISNUMBER(IFERROR(SEARCH($A$12,A2,1),""))

Заметки:

1. 12 австралийских доллара это ячейка, которую вы указали как LinkedCell на шаге 4;
2. Выполнив вышеуказанные шаги, теперь вы можете проверить: введите букву C в поле со списком, и тогда вы увидите, что ячейки формулы, которые ссылаются на ячейки, содержащие символ C, заполнены числом 1.

7. Выберите ячейку D2, введите приведенную ниже формулу и нажмите Enter. Затем перетащите его дескриптор автозаполнения вниз в ячейку D9.

=IF(C2=1,COUNTIF($C$2:C2,1),"")

8. Выберите ячейку E2, введите приведенную ниже формулу и нажмите Enter. Затем перетащите его дескриптор автозаполнения вниз до E9, чтобы применить ту же формулу.

=IFERROR(INDEX($A$2:$A$9,MATCH(ROWS($D$2:D2),$D$2:$D$9,0)),"")

9. Теперь вам нужно создать диапазон имен. Пожалуйста, нажмите Формула > Определить имя.

10. в Новое имя диалоговое окно, введите Раскрывающийся список в Имя и фамилия поле, введите приведенную ниже формулу в поле Относится к поле, а затем щелкните OK кнопку.

=$E$2:INDEX($E$2:$E$9,MAX($D$2:$D$9),1)

11. Теперь включите режим дизайна, нажав разработчик > Режим проектирования. Затем дважды щелкните поле со списком, чтобы открыть Microsoft Visual Basic для приложений окно.

12. Скопируйте и вставьте приведенный ниже код VBA в редактор кода.

Код VBA: сделать выпадающий список доступным для поиска

Private Sub ComboBox1_GotFocus()
	ComboBox1.ListFillRange = "DropDownList"
	Me.ComboBox1.DropDown
End Sub

13. нажмите другой + Q ключи, чтобы закрыть Microsoft Visual Basic для приложений окно.

Отныне, когда символ вводится в поле со списком, он выполняет нечеткий поиск, а затем перечисляет соответствующие значения в списке.

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


Легко создавайте раскрывающийся список с возможностью поиска с помощью замечательного инструмента

Кажется, что описанный выше метод непрост. Вот рекомендую удобную функцию - Раскрывающийся список с возможностью поиска of Kutools for Excel чтобы упростить создание раскрывающегося списка с возможностью поиска в Excel.

Перед применением Kutools for Excel, Пожалуйста, сначала скачайте и установите.

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

1. Нажмите Кутулс > Раскрывающийся список > Раскрывающийся список с возможностью поиска > Включить раскрывающийся список с возможностью поиска.

2. В дебюте Kutools for Excel диалоговое окно (это диалоговое окно появляется только при первом применении этой функции), нажмите кнопку Да кнопку.

3. Затем в Раскрывающийся список с возможностью поиска диалоговое окно необходимо настроить следующим образом.

3.1). Обращаться к раздел, вы можете указать диапазон диапазона для применения этой функции:
A: Указанный диапазон (ы): Поддержка одного или нескольких раскрывающихся списков;
B: Указанный объем: Поддержка текущего листа, текущей книги или всех книг.
3.2). Доступные опции разделе есть два варианта, вы можете выбрать один из них, оба или ни один из них в соответствии с вашими потребностями:
A: Соответствует только началу слова: Если этот параметр отмечен, отображаются только элементы, начинающиеся с введенного символа, при этом первый отображаемый элемент будет заполнен автоматически; Если этот параметр не отмечен, будут отображаться элементы, содержащие введенный символ;
B: Деликатный случай: Если этот параметр отмечен, отображаются только элементы, соответствующие регистру введенного символа; Если этот параметр не отмечен, элементы, содержащие введенный символ, будут отображаться без учета регистра.
3.3). режим В разделе выберите режим добавления элементов раскрывающегося списка в ячейки.
A: Добавить: Если вы выберете этот переключатель, в ячейку будет разрешено добавлять несколько искомых элементов (включая дубликаты). После добавления первого искомого элемента в ячейку, если вы снова выполните новый поиск в этой ячейке, новый искомый элемент будет добавлен в конец существующего.
В папке Разделитель текстовое поле, введите разделитель для разделения добавленных элементов;
В папке Направление текста выберите направление для отображения добавленных элементов в ячейке раскрывающегося списка.
B: Изменить: Если вы выберете эту кнопку-переключатель, добавленный позже элемент перезапишет существующий. Одновременно в ячейке может отображаться только один элемент.
3.3) Нажмите OK.

4. Затем нажмите Кутулс > Раскрывающийся список > Раскрывающийся список с возможностью поиска > Включить раскрывающийся список с возможностью поиска для включения этой функции.

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

Если вы выбрали Горизонтально в Направление текста раздел: все добавленные элементы будут отображаться горизонтально в ячейке. См. приведенную ниже демонстрацию:

Если вы выбрали Вертикально в Направление текста раздел: все добавленные элементы будут отображаться вертикально в ячейке. См. приведенную ниже демонстрацию:

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

Нажмите, чтобы узнать больше об этой функции.

  Если вы хотите получить бесплатную (30-дневную) пробную версию этой утилиты, пожалуйста, нажмите, чтобы загрузить это, а затем перейдите к применению операции в соответствии с указанными выше шагами.


Статьи по теме:

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

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

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

Автоматическое заполнение других ячеек при выборе значений в раскрывающемся списке Excel
Допустим, вы создали раскрывающийся список на основе значений в диапазоне ячеек B8: B14. При выборе любого значения в раскрывающемся списке необходимо, чтобы соответствующие значения в диапазоне ячеек C8: C14 автоматически заполнялись в выбранной ячейке. Для решения проблемы методы, описанные в этом руководстве, окажут вам услугу.

Дополнительные инструкции для раскрывающегося списка ...


Лучшие инструменты для работы в офисе

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

  • Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма ...
  • Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
  • Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы... Предотвращение дублирования ячеек; Сравнить диапазоны...
  • Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
  • Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
  • Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии...
  • Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом ...
  • Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
  • Более 300 мощных функций. Поддерживает Office / Excel 2007-2019 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
вкладка kte 201905

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
офисный дно
Сортировать комментарии по
Комментарии (67)
Оценок пока нет. Оцените первым!
Этот комментарий был сведен к минимуму модератором на сайте
очень красиво объяснил. Очень понравилось. Спасибо !!
Этот комментарий был сведен к минимуму модератором на сайте
Отличный пост. Не могли бы вы объяснить, как скопировать один и тот же раскрывающийся список в несколько ячеек. Я хочу создать отчет о расходах и хочу иметь возможность выбирать разные расходы в каждой строке из того же раскрывающегося списка. Спасибо.
Этот комментарий был сведен к минимуму модератором на сайте
у меня такая же потребность
Этот комментарий был сведен к минимуму модератором на сайте
По какой-то причине, когда я выбираю вариант из раскрывающегося списка после ввода нескольких символов, основное значение раскрывающегося списка становится пустым... есть идеи, почему это может произойти и как это остановить? У меня есть командная кнопка, которую я хочу щелкнуть, чтобы затем поместить выделение в следующую доступную ячейку в заданном диапазоне, но снова значение исчезает, когда я нажимаю на нее.
Этот комментарий был сведен к минимуму модератором на сайте
У меня точно такая же проблема. Я все сделал правильно, но ярлык выпадающего списка просто гаснет каждый раз, когда я нажимаю ввод. Если разобрались, поделитесь!
Этот комментарий был сведен к минимуму модератором на сайте
Мой не работает. Моя метка раскрывающегося списка не работала в «свойствах» для поля со списком. Каждый раз, когда я входил в него, он исчезал. Поэтому вместо этого я использовал «тест». Я поправил макрос со словом test вместо dropdowmlist. Дайте мне знать, если есть что-то еще, что я могу сделать? Поиск не работает.
Этот комментарий был сведен к минимуму модератором на сайте
[quote]У меня не работает. Моя метка раскрывающегося списка не работала в «свойствах» для поля со списком. Каждый раз, когда я входил в него, он исчезал. Поэтому вместо этого я использовал «тест». Я поправил макрос со словом test вместо dropdowmlist. Дайте мне знать, если есть что-то еще, что я могу сделать? Поиск не работает.Имад[/quote] Я видел это «как сделать автозаполнение / автоматически предлагать DDL / поле со списком» на нескольких разных сайтах, и ВСЕ они хотят, чтобы вы поместили «что-то» в поле ListFillRange ДО того, как они заставят вас создать диапазон имен с помощью щелкнув «Формула»> «Определить имя», и ListFillRange всегда будет пустым в окне «Свойства», ПОКА вы не определите имя («Формула»> «Определить имя»), ПОЭТОМУ я думаю, что у IMAD выше и у MAARTEN ниже была проблема - не уверен на 100%.
Этот комментарий был сведен к минимуму модератором на сайте
Итак, я, наконец, заставил его работать! Я подключил связанную ячейку к vlookup и получил всю информацию, вытянутую в строку. Мне было интересно, может ли быть какое-либо расширение на vba, чтобы фактически фильтровать таблицу по мере того, как мы печатаем?
Этот комментарий был сведен к минимуму модератором на сайте
Привет, я не могу заполнить «DropDownList» в «ListFillRange».... В чем подвох? Я не понимаю решение imad. Спасибо.
Этот комментарий был сведен к минимуму модератором на сайте
попробуйте вместо =--ISNUMBER(IFERROR(SEARCH($A$12,$A$2,1),"")) ввести =--ISNUMBER(IFERROR(SEARCH($A$12,A2,1),"") ) на шаге 6
Этот комментарий был сведен к минимуму модератором на сайте
[quote]Привет, я не могу заполнить «DropDownList» в «ListFillRange».... В чем подвох? Я не понимаю решение imad. Спасибо.Маартен[/quote] Я разместил этот ответ выше для IMAD и увидел это сообщение здесь для MAARTEN, поэтому я решил опубликовать это и для него. Я видел это «как сделать автозаполнение / автоматически предлагать DDL / поле со списком» на нескольких разных сайтах, и они ВСЕ хотят, чтобы вы поместили «что-то» в поле свойств ListFillRange. ДО у них есть ты создать именованный диапазон щелкнув Формула > Определить имя ....... и ListFillRange всегда будет оставаться пустым в окне свойств. ПОКА вы не определите имя («Формула» > «Определить имя»), ПОЭТОМУ я думаю, что у IMAD выше и у MAARTEN ниже (здесь) возникла проблема, хотя и не уверен на 100%.
Этот комментарий был сведен к минимуму модератором на сайте
Привет. Большое спасибо за ваше решение. Я уже бросил, но попробую еще раз.
Этот комментарий был сведен к минимуму модератором на сайте
Спасибо .. Очень полезно .. Да благословит вас Бог
Этот комментарий был сведен к минимуму модератором на сайте
Я, как и Кристина выше, также хотел бы знать, как сделать несколько полей со списком для одного листа. Я пытался, но когда я начинаю печатать во втором поле со списком, происходят две вещи: 1. раскрывающийся список не появляется, и 2. простое действие ввода в поле со списком2 активирует выбор из моего исходного поля со списком1 и выделяет его в раскрывающемся списке из поля со списком1. Я проверил, чтобы убедиться, что все мои коды говорят, что combobox2 для combobox2 и т. д. для других полей, но есть разрыв, который я не могу понять.
Этот комментарий был сведен к минимуму модератором на сайте
У меня точно такая же проблема, вы еще не нашли решение??
Этот комментарий был сведен к минимуму модератором на сайте
Привет Херб, Что, если я создал раскрывающийся список из другого рабочего листа? формула "=--ISNUMBER(IFERROR(SEARCH($A$2,H2,1),""))" имеет неправильную ссылку, и когда я ее редактирую, она не позволяет поместить правильную ячейку. что ты посоветуешь? благодарю вас
Этот комментарий был сведен к минимуму модератором на сайте
Привет, Как сделать ту же программу поиска для непрерывного rwo, я пробовал, и она работает только с одной строкой, я хочу сделать то же самое для строки ниже и для другого имени
Этот комментарий был сведен к минимуму модератором на сайте
Пожалуйста, помогите мне, я не могу ввести формулу в строку формул, когда я вставляю эту формулу и вставляю это =--ISNUMBER(IFERROR(SEARCH($A$12,A2,1),"")) дайте мне error.type :(
Этот комментарий был сведен к минимуму модератором на сайте
Спасибо, я использовал выше, и он работает отлично .... Пока у вас нет двух полей со списком на одном листе. Когда вы хотите ввести второе поле со списком, оно выделяет текст в первом поле со списком и не хочет искать Если я оставлю первое поле пустым, второе поле будет работать нормально, пожалуйста, помогите
Этот комментарий был сведен к минимуму модератором на сайте
Привет, ваше руководство очень полезно, но я все еще сталкиваюсь с одной последней проблемой. Я пытаюсь сделать простой счет-фактуру и сделать раскрывающийся список для ячейки с именем моего клиента, должен ли мой список клиентов находиться на том же рабочем листе, что и мой рабочий лист счета-фактуры? Возможно ли, что у меня есть два листа: «счет-фактура» и «имя клиента», и сделать выпадающий список для имени клиента на листе «счет-фактура»? Спасибо
Этот комментарий был сведен к минимуму модератором на сайте
Спасибо за эту разбивку, чтобы сделать поле со списком доступным для поиска. Я даже заставил трех из них работать на одной странице. Моя проблема, с которой я столкнулся, заключается в том, что когда я начинаю вводить информацию для поиска, а информация сужается, если я нажимаю клавишу со стрелкой вниз, чтобы выбрать элемент в списке, Excel аварийно завершает работу. У кого-нибудь было такое, и если да, то нашли ли вы способ решить эту проблему.
Этот комментарий был сведен к минимуму модератором на сайте
Здравствуйте,
Упомянутая вами проблема не возникает в моем случае. Не могли бы вы предоставить версию вашего офиса?
Этот комментарий был сведен к минимуму модератором на сайте
Привет. Как и на форуме, мне нужно иметь раскрывающийся список с возможностью поиска для столбцов со 2 по 500. Пожалуйста, дайте мне знать, как я могу это сделать, поскольку вторая комбинация повторяет то же самое в первой, что я не хочу
Этот комментарий был сведен к минимуму модератором на сайте
Уважаемый Джелбин,
Не могу справиться с этим. Извини за это.
Этот комментарий был сведен к минимуму модератором на сайте
4. В диалоговом окне «Свойства» вам необходимо: 1). Выберите False в поле AutoWordSelect; 2). Укажите ячейку в поле LinkedCell. В этом случае мы вводим A12; Почему А12? спасибо
Этот комментарий был сведен к минимуму модератором на сайте
Здравствуйте,
Эта ячейка выбирается необязательно, что может помочь завершить всю операцию. Вы можете выбрать любой, как вам нужно.
Этот комментарий был сведен к минимуму модератором на сайте
У меня была постоянная проблема со всеми документами, для которых я использовал этот метод. Тень раскрывающегося списка появляется под ним каждый раз, когда я нажимаю на другую ячейку в электронной таблице и начинаю печатать. Это больше, чем просто неприятность, потому что, когда тень падает, она не позволяет использовать любые дополнительные выпадающие окна с возможностью поиска. Пожалуйста помоги!!! Это затрагивает несколько документов, которые мы используем в нашей организации.
Этот комментарий был сведен к минимуму модератором на сайте
Добрый день,
Извините, что отвечаю так поздно. Проблема, которую вы описали, не проявляется в моем случае. Было бы неплохо, если бы вы могли предоставить свою версию Office. Благодарю вас!
Этот комментарий был сведен к минимуму модератором на сайте
есть ли способ сделать так, чтобы поле поиска поместило верхний результат, если оставить его пустым? в случае этого примера он автоматически поставит Китай, если оставить его пустым
Этот комментарий был сведен к минимуму модератором на сайте
Дорогой Дэйв,
Не могли бы вы предоставить скриншот вашей электронной таблицы, показывающий, что именно вы пытаетесь сделать?
Этот комментарий был сведен к минимуму модератором на сайте
Здравствуйте, спасибо за урок! У меня возникла проблема, когда каждый раз, когда я печатаю в поле со списком, «DropDownList1» исчезает из свойства «ListFillRange». Пока я не ввожу текст в поле, если я повторно ввожу «DropDownList1» в свойстве, в поле будут отображаться предложения. Я все перерыл и ошибок не нашел. Является ли это распространенной проблемой, и есть ли способ ее исправить? Спасибо за ваше время!
Этот комментарий был сведен к минимуму модератором на сайте
Дорогой Бен,
Меня также смущает исчезновение «DripDownList» из свойства «ListFillRange».
Но это не влияет на окончательный результат выпадающего списка.
Этот комментарий был сведен к минимуму модератором на сайте
Я чувствую себя глупо, но сразу после публикации я понял, что, вероятно, не добавил 1 в DropDownList1 в VBA, и, конечно же, проблема была в этом! Спасибо, в любом случае!
Здесь еще нет комментариев
Загрузить ещё
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места