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

Содержание

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

Все мы используем Google как часть нашей повседневной жизни. Одна из его функций - поисковые подсказки, когда Google действует умно и дает нам список предложений, пока мы печатаем.

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

Ниже приведено видео этого урока (на случай, если вы предпочитаете просмотр видео, а не чтение текста).

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

Для этого урока я использую данные 20 ведущих стран по ВВП.

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

Что-то вроде того, что показано ниже:

Чтобы продолжить, загрузите файл примера отсюда

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

Настройка поля поиска.
Установка данных.
Написание короткого кода VBA, чтобы он работал.

Шаг 1 - Настройка окна поиска

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

Вот как это сделать:

Перейдите на вкладку разработчика -> Вставить -> Элементы управления ActiveX -> Поле со списком (элемент управления ActiveX).
- Возможно, вы не найдете вкладку разработчика на ленте. По умолчанию он скрыт, и его необходимо включить. Щелкните здесь, чтобы узнать, как получить вкладку разработчика на ленте в Excel.
Переместите курсор в область рабочего листа и щелкните в любом месте. Будет вставлено поле со списком.
Щелкните правой кнопкой мыши поле со списком и выберите «Свойства».
В диалоговом окне свойств внесите следующие изменения:
- AutoWordSelect: Ложь
- LinkedCell: B3
- ListFillRange: DropDownList (мы создадим именованный диапазон с этим именем на шаге 2)
- MatchEntry: 2 - fmMatchEntryNone

(Ячейка B3 связана с полем со списком, что означает, что все, что вы вводите в поле со списком, вводится в поле B3)

Перейдите на вкладку «Разработчик» и нажмите «Режим проектирования». Это позволит вам ввести текст в поле со списком. Кроме того, поскольку ячейка B3 связана с полем со списком, любой текст, который вы вводите в поле со списком, также будет отражен в B3 в режиме реального времени.

Шаг 2 - Установка данных

Теперь, когда поле поиска настроено, нам нужно получить данные на месте. Идея состоит в том, что как только вы вводите что-либо в поле поиска, отображаются только те элементы, в которых есть этот текст.

Для этого мы будем использовать

Три вспомогательных столбца.
Один динамический именованный диапазон.

Столбец помощника 1

Поместите следующую формулу в ячейку F3 и перетащите ее на весь столбец (F3: F22)

= - ЕЧИСЛО (ЕСЛИОШИБКА (ПОИСК ($ B $ 3; E3,1); ""))

Эта формула возвращает 1, если текст в поле со списком присутствует в названии страны слева. Например, если вы введете UNI, то только значения для Uniтед государства и United Kingdom равны 1, а все остальные значения равны 0.

Столбец помощника 2

Поместите следующую формулу в ячейку G3 и перетащите ее на весь столбец (G3: G22)

= ЕСЛИ (F3 = 1, СЧЁТЕСЛИ ($ F $ 3: F3,1), "")

Эта формула возвращает 1 для первого вхождения, когда текст поля со списком соответствует названию страны, 2 для второго вхождения, 3 для третьего и так далее. Например, если вы введете UNI, в ячейке G3 будет отображаться 1, так как она соответствует США, а G9 отобразит 2, поскольку она соответствует Соединенному Королевству. Остальные ячейки будут пустыми.

Столбец помощника 3

Поместите следующую формулу в ячейку H3 и перетащите ее на весь столбец (H3: H22)

= ЕСЛИОШИБКА (ИНДЕКС ($ E $ 3: $ E $ 22, ПОИСКПОЗ (СТРОКИ ($ G $ 3: G3), $ G $ 3: $ G $ 22,0)), "")

Эта формула складывает все совпадающие имена вместе без пустых ячеек между ними. Например, если вы введете UNI, в этом столбце будут показаны 2 и 9 вместе, а остальные ячейки будут пустыми.

Создание динамического именованного диапазона

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

Примечание: На шаге 1 мы ввели DropDownList в параметр ListFillRange. Теперь создадим именованный диапазон с таким же именем..

Вот шаги по его созданию:

Перейдите в Формулы -> Диспетчер имен.
В диалоговом окне диспетчера имен нажмите «Создать». Откроется диалоговое окно «Новое имя».
В поле имени введите DropDownList.
В поле «Ссылается на» введите формулу: = $ H $ 3: ИНДЕКС ($ H $ 3: $ H $ 22, MAX ($ G $ 3: $ G $ 22), 1)

Шаг 3 - Запуск кода VBA на работу

Мы почти на месте.

Последняя часть - написать короткий код VBA. Этот код делает раскрывающийся список динамическим, так что он показывает совпадающие элементы / имена, когда вы вводите текст в поле поиска.

Чтобы добавить этот код в свою книгу:

Щелкните правой кнопкой мыши вкладку «Рабочий лист» и выберите «Просмотреть код».

В окне VBA скопируйте и вставьте следующий код:

Частный суб ComboBox1_Change () ComboBox1.ListFillRange = "DropDownList" Me.ComboBox1.DropDown Конец подписки

Вот и все!!

У вас все настроено на собственную панель поиска типа Google, которая показывает совпадающие элементы по мере того, как вы вводите ее.

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

Чтобы продолжить, загрузите файл отсюда

Что вы думаете? Сможете ли вы использовать этот раскрывающийся список предложений поиска в своей работе? Сообщите мне свои мысли, оставив комментарий.

Если вам понравился этот урок, я уверен, что вам также понравятся следующие уроки по Excel: