Создание поля поиска в Excel
Идея создания окна поиска в Excel, чтобы мы продолжали записывать необходимые данные, и, соответственно, оно будет фильтровать данные и отображать только определенную часть данных. В этой статье мы покажем вам, как создать окно поиска и фильтровать данные в Excel.
15 простых шагов для создания окна динамического поиска в Excel
Вы можете скачать этот шаблон Excel для окна поиска здесь - Шаблон для окна поиска в ExcelЧтобы создать динамическое окно поиска в excel. мы собираемся использовать данные ниже. Вы можете скачать книгу и следовать за нами, чтобы создать ее самостоятельно.
Выполните следующие шаги, чтобы создать динамическое окно поиска в Excel.
- Шаг 1: Сначала нужно создать уникальный список « Город имен» путем удаления дубликатов в новом листе.
- Шаг 2. Для этого уникального списка городов дайте название « CityList ».
- Шаг 3: Перейдите на вкладку «Разработчик» в Excel и из вставки вставьте поле « Поле со списком ».
- Шаг 4: Нарисуйте это поле « Combo » на своем листе, где находятся данные.
- Шаг 5: Щелкните правой кнопкой мыши это поле со списком и выберите параметр « Свойства ».
- Шаг 6: Откроются параметры свойств, подобные приведенному ниже.
- Шаг 7: Здесь у нас есть несколько свойств, поскольку свойство « Связанная ячейка » дает ссылку на ячейку D2 .
- Шаг 8: Для свойства « Диапазон заполнения списка » присвойте имя уникальному списку «Города».
- Шаг 9: Для свойства « Match Entry » выберите 2-fmMatchEntryNone, потому что при вводе имени в поле со списком оно не будет автоматически завершать предложение.
- Шаг 10: Мы закончили с частью свойств «Combo Box». Перейдите на вкладку « Разработчик » и отмените выбор режима « Дизайн » в «Поле со списком».
- Шаг 11: Теперь из поля со списком мы можем видеть названия городов в раскрывающемся списке в Excel.
Фактически, мы можем ввести имя внутри поля со списком, и оно также будет отражать встроенную ячейку D2.
- Шаг 12: Теперь нам нужно написать формулы для фильтрации данных при вводе названия города в поле со списком. Для этого нам нужно иметь три вспомогательных столбца, для первого вспомогательного столбца нам нужно найти номера строк с помощью функции ROWS.
- Шаг 13: Во втором вспомогательном столбце нам нужно найти соответствующие поисковые названия городов, и если они совпадают, нам нужны номера строк этих городов, чтобы ввести следующую формулу.
Эта формула будет искать название города в основной таблице, если оно совпадает, вернет номер строки из столбца «Помощник 1» или же вернет пустую ячейку.
Например, теперь я наберу « Лос-Анджелес » и везде, где название города есть в основной таблице для этих городов, мы получим номер строки.
- Шаг 14: Как только номера строк введенного или выбранного названия города станут доступны, нам нужно склеить эти номера строк один под другим, поэтому в третьем вспомогательном столбце нам нужно сложить все эти номера строк введенного названия города.
Чтобы собрать эти номера строк вместе, мы будем использовать формулу комбинации « ЕСЛИ ОШИБКА в Excel » и функции « МАЛЕНЬКИЙ » в Excel.
Эта формула будет искать наименьшее значение в списке сопоставленных городов на основе фактических номеров строк, и будет складывать первое наименьшее, второе наименьшее, третье наименьшее и так далее. После того, как все маленькие значения сложены вместе, МАЛЕНЬКАЯ функция выдает значение ошибки, поэтому, чтобы избежать этого, мы использовали функцию ЕСЛИОШИБКА, и если будет получено значение ошибки, она вернет в результате пустую ячейку.
- Шаг 15: Теперь создайте идентичный формат таблицы, как показано ниже.
В этой новой таблице нам нужно отфильтровать данные на основе названия города, которое мы вводим в поле поиска Excel. Это можно сделать, используя комбинацию функций ЕСЛИОШИБКА, ИНДЕКС и СТОЛБЦЫ в excel. Ниже приведена формула, которую вам нужно применить.
Скопируйте формулу и вставьте ее во все остальные ячейки новой таблицы.
Хорошо, мы закончили проектирование, давайте узнаем, как им пользоваться.
Введите название города в поле со списком, и наша новая таблица отфильтрует только введенные данные о городе.
Как видите, я просто набрал «LO», и все соответствующие результаты поиска отфильтрованы в новом формате таблицы.
Что нужно помнить здесь
- Вам нужно вставить поле со списком в Excel из «ActiveX Form Control» на вкладке «Разработчик».
- Поле со списком соответствует всем связанным алфавитам и возвращает результат.