Строка поиска в Excel

Этот пример научит вас создавать собственную строку поиска в Excel.

Вот так выглядит таблица. Если ввести поисковый запрос в ячейку B2, Excel найдет совпадения в столбце E и выдаст результат в столбце B.

Строка поиска в Excel

Чтобы создать эту строку поиска, следуйте инструкции ниже:

  1. Выделите ячейку D4 и вставьте функцию SEARCH (ПОИСК), как показано ниже, указав абсолютную ссылку на ячейку В2.

    =SEARCH($B$2,E4)
    =ПОИСК($B$2;E4)

  2. Дважды кликните по маркеру автозаполнения, который находится в правом нижнем углу ячейки D4, чтобы быстро скопировать формулу во все оставшиеся ячейки столбца D.

    Строка поиска в Excel

    Объяснение: Функция SEARCH (ПОИСК) ищет начальную позицию искомого значения в строке. Функция SEARCH (ПОИСК) не учитывает регистр. В слове "Tunisia" строка "uni" имеет начальное положение 2, а в слове "United States" начальное положение равно 1. Чем меньше значение, тем выше оно должно располагаться.

  3. И "United States", и "United Kingdom" возвращают значение 1. Как быть? Чуть позже мы присвоим всем данным уникальные значения с помощью функции RANK (РАНГ), но для этого нам нужно слегка скорректировать результат формулы в ячейке D4, как показано ниже:

    IFERROR(SEARCH($B$2,E4)+ROW()/100000,"")
    ЕСЛИОШИБКА(ПОИСК($B$2;E4)+СТРОКА()/100000;"")

  4. Снова дважды кликните по правому нижнему углу ячейки D4, чтобы быстро скопировать формулу в остальные ячейки столбца.

    Строка поиска в Excel

    Объяснение: Функция ROW (СТРОКА) возвращает номер строки ячейки. Если мы разделим номер строки на крупное число и прибавим это значение к результату функции SEARCH (ПОИСК), у нас всегда будут получаться уникальные значения, а небольшой прирост не повлияет на ранжирование. Теперь значение для "United States" составляет 1,00006, а для "United Kingdom" – 1,00009. Кроме этого мы добавили функцию IFERROR (ЕСЛИОШИБКА). Если ячейка содержит ошибку, к примеру, когда строка не может быть найдена, возвращается пустая строка ("").

  5. Выберите ячейку C4 и вставьте функцию RANK (РАНГ), как показано ниже:

    =IFERROR(RANK(D4,$D$4:$D$197,1),"")
    =ЕСЛИОШИБКА(РАНГ(D4;$D$4:$D$197;1);"")

  6. Дважды щелкните по правому углу ячейки С4, чтобы быстро скопировать формулу в другие ячейки.

    Строка поиска в Excel

    Объяснение: Функция RANK (РАНГ) возвращает порядковый номер значения. Если третий аргумент функции равен 1, Excel выстраивает числа по возрастанию: от наименьшего к большему. Поскольку мы добавили функцию ROW (СТРОКА), все значения в столбце D стали уникальными. Как следствие, числа в столбце C тоже уникальны.

  7. Мы почти закончили. Функцию VLOOKUP (ВПР) мы будем использовать, чтобы извлечь найденные страны (наименьшее значение первым, второе наименьшее вторым, и т.д.) Выделите ячейку B4 и вставьте функцию VLOOKUP (ВПР), как показано ниже.

    =IFERROR(VLOOKUP(A4,$C$4:$E$197,3,FALSE),"")
    =ЕСЛИОШИБКА(ВПР(A4;$C$4:$E$197;3;ЛОЖЬ);"")

  8. Дважды щелкните по правому нижнему углу ячейки B4, чтобы быстро скопировать формулу в другие ячейки.

    Строка поиска в Excel

  9. Измените цвет чисел в столбце А на белый и скройте столбцы С и D.

Результат: Ваша собственная строка поиска в Excel.

Строка поиска в Excel

11.06.2015 14:11
2036

Комментарии

Нет комментариев. Ваш будет первым!