6 функций поиска Microsoft Excel для более эффективного поиска в электронных таблицах
Продуктивность

6 функций поиска Microsoft Excel для более эффективного поиска в электронных таблицах

Поиск в большой электронной таблице Excel не всегда прост. Используйте формулы поиска для экономии времени и эффективного поиска в электронных таблицах

Поиск в электронной таблице Microsoft Excel может показаться простым. Хотя Ctrl + F может помочь вам найти большинство вещей в электронной таблице, вы захотите использовать более сложные инструменты для поиска и извлечения данных на основе определенных значений. Мы поможем вам сэкономить массу времени с помощью нашего списка расширенных функций поиска

Как только вы узнаете, как искать в Excel с помощью поиска, не будет иметь значения, насколько большими становятся ваши электронные таблицы, вы всегда сможете найти то, что вам нужно!

1. Функция VLOOKUP

Функция VLOOKUP позволяет найти определенное значение в столбце и извлечь значения из соответствующей строки в соседних столбцах. Два примера, где это можно сделать: (1) поиск фамилии сотрудника по его номеру или (2) поиск номера телефона по фамилии

Вот синтаксис функции:

=VLOOKUP( lookup_value , table_array , col_index_num , range_lookup )

  • lookup_value – это часть информации, которая у вас уже есть. Например, если вам нужно узнать, в каком штате находится город, это будет название города.
  • table_array позволяет указать ячейки, в которых функция будет искать искомые и возвращаемые значения. При выборе диапазона убедитесь, что первый столбец, включенный в массив, будет тем, в котором будет находиться искомое значение!.
  • col_index_num – это номер столбца, в котором содержится возвращаемое значение.
  • range_lookup является необязательным аргументом и принимает значение 1 или 0, хотя можно также ввести TRUE или FALSE. Если вы введете 1 или опустите этот аргумент, функция будет искать приблизительное значение, но мы обнаружили, что это не всегда удается. В приведенном ниже примере VLOOKUP, ищущая значение 100 баллов, возвращает 90. При поиске меньшего значения, например 88, выдается ошибка.

Давайте посмотрим, как это можно использовать. Эта электронная таблица содержит имена студентов и оценки за четыре разных теста. Допустим, вы хотите найти оценку №4 для студента с фамилией ‘Дэвидсон’. С помощью VLOOKUP это сделать легко

Вот формула, которую вы используете:

=VLOOKUP('Davidson

Поскольку четвертый показатель является пятым столбцом после искомой фамилии, 5 – это аргумент индекса столбца. Обратите внимание, что при поиске текста рекомендуется установить range_lookup в 0. Без этого вы можете получить плохие результаты

Вот результат:

Он выдал результат 79, что является оценкой №4 студента, которого мы запрашивали

Заметки о VLOOKUP

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

Также следует помнить, что VLOOKUP возвращает только одно значение. Был еще один студент с фамилией ‘Дэвидсон’, но VLOOKUP вернет результат только для первой записи, не показывая, что есть более одного совпадения

Похожие: Как выполнить поиск с помощью VLOOKUP в Excel

2. Функция HLOOKUP

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

Вот синтаксис функции:

=HLOOKUP( lookup_value , table_array , row_index_num , range_lookup )

  • lookup_value – это значение, которое вы знаете и для которого хотите найти соответствующее значение.
  • table_array – это ячейки, в которых вы хотите произвести поиск.
  • row_index_num указывает строку, из которой будет получено возвращаемое значение.
  • range_lookup – такой же, как в VLOOKUP, оставьте его пустым, чтобы получить ближайшее значение, когда это возможно, или введите 0, чтобы искать только точные совпадения.

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

=HLOOKUP('Score 4'

Как показано на рисунке ниже, возвращается оценка:

Ученик в ряду 6, Томас Дэвидсон, набрал 68 баллов на четвертом тесте

Связанное Как рассчитать средневзвешенное значение в Excel

Заметки о HLOOKUP

Как и в случае с VLOOKUP, значение поиска должно находиться в первой строке массива таблицы. Это редко является проблемой для HLOOKUP, так как обычно для значения поиска используется заголовок столбца.HLOOKUP также возвращает только одно значение

3-4. Функции INDEX и MATCH

INDEX и MATCH – это две разные функции, но при совместном использовании они могут значительно ускорить поиск в большой электронной таблице. Обе функции имеют недостатки, но, объединив их, мы будем использовать сильные стороны обеих

Сначала о синтаксисе обеих функций:

=INDEX( array , row_number , column_number )

  • массив – это массив, в котором вы будете искать.
  • номер_строки и номер_столбца могут быть использованы для сужения поиска (мы рассмотрим это в ближайшее время).

=MATCH( lookup_value , lookup_array , match_type )

  • lookup_value – это поисковый термин, который может быть строкой или числом.
  • массив_поиска – это массив, в котором Microsoft Excel будет искать искомый термин.
  • тип_соответствия – необязательный аргумент, который может быть равен 1, 0 или -1. 1 вернет наибольшее значение, которое меньше или равно вашему поисковому термину. 0 будет возвращать только точный термин, а -1 будет возвращать наименьшее значение, которое больше или равно вашему поисковому термину.

.

Возможно, не совсем понятно, как мы будем использовать эти две функции вместе, поэтому я расскажу об этом здесь.MATCH принимает поисковый запрос и возвращает ссылку на ячейку. На рисунке ниже видно, что при поиске фамилии ‘Дэвидсон’ в столбце B MATCH возвращает 2

INDEX, с другой стороны, делает обратное: он берет ссылку на ячейку и возвращает значение в ней. Здесь видно, что при указании вернуть вторую строку столбца B, INDEX возвращает ‘Davidson’, значение из второй строки

Мы собираемся объединить эти два метода, чтобы MATCH возвращал ссылку на ячейку, а INDEX использовал эту ссылку для поиска значения в ячейке. Допустим, вы помните, что был студент с фамилией Таунсенд, и хотите узнать, какой четвертный балл был у этого студента. Вот формула, которую мы будем использовать:

=INDEX(F:F, MATCH('Townsend', B:B, 0))

Вы заметите, что тип совпадения здесь установлен на 0. Когда вы ищете строку, вы захотите использовать именно это значение. Вот что мы получим, запустив эту функцию:

Как видно из вставки, Ральф Таунсенд набрал 68 баллов в своем четвертом тесте – число, которое появляется, когда мы запускаем функцию. Это может показаться не слишком полезным, когда можно просто просмотреть несколько столбцов, но представьте, сколько времени вы сэкономите, если вам придется делать это 50 раз на большой электронной таблице базы данных, содержащей несколько сотен столбцов!

5. Функция FIND

Статья о поиске чего-либо в Excel была бы неполной без функции FIND. Но это может быть не то, что вы ожидаете. Вы можете использовать функцию FIND в Excel, чтобы определить положение строки текста внутри другой строки текста

Допустим, мы хотим найти первое вхождение буквы ‘x’ во фразе ‘Лиса перепрыгнула через забор’. Это будет наша функция:

=FIND('x', 'The brown fox jumped over the fence')

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

Примечания по FIND

Как и VLOOKUP, HLOOKUP и другие функции, FIND определяет только первое вхождение строки. Обратите внимание, что FIND чувствительна к регистру. Вы можете использовать ее для поиска нескольких символов. И хотя в нашем примере мы использовали букву, она также работает с числами

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

FIND vs. ПОИСК

Мы не можем рассказать о FIND, не упомянув о функции SEARCH. По сути, это то же самое, что и FIND, за исключением того, что она не чувствительна к регистру. Она также позволяет использовать подстановочные знаки, что означает, что вы можете искать не точные совпадения

Excel поддерживает три знака подстановки:

  • Звездочка (*), которая является заполнителем для любого количества символов, включая ноль.
  • Вопросительный знак (?), который может заменить любой один символ.
  • Тильда (~), которая превращает символы ‘звездочка’ и ‘вопросительный знак’ в буквальные символы, то есть отменяет их функцию подстановочного знака. Вы можете использовать его как ~* или ~?.

Похожие: Советы по работе с текстом и текстовыми функциями в Excel

6. Функция XLOOKUP

XLOOKUP – это новая функция, призванная заменить VLOOKUP. Как и VLOOKUP, вы можете использовать ее для поиска значений в таблице или диапазоне путем поиска известного значения. Она отличается от VLOOKUP тем, что позволяет искать значения, расположенные в столбцах слева или справа от запрашиваемого значения; в VLOOKUP вы можете найти данные только справа от запрашиваемого столбца

Вот синтаксис функции:

=XLOOKUP(lookup_value, lookup_array, return_array, if_not_found , match_mode , search_mode )

  • lookup_value – это значение, которое вы ищете; т.е.ваш запрос.
  • lookup_array – массив или диапазон для поиска.
  • return_array – это массив или диапазон для возврата. Это первое отличие от VLOOKUP.
  • if_not_found – необязательный аргумент, который возвращает сообщение по вашему выбору, если совпадение не найдено.
  • match_mode – еще один необязательный аргумент, позволяющий найти точное совпадение (0), следующий меньший элемент (-1), следующий больший элемент (1) или подстановочный знак (2).
  • search_mode является необязательным параметром и позволяет вам контролировать порядок поиска. По умолчанию (1) поиск начинается с первого элемента. Можно также начать с последнего элемента (-1), выполнить бинарный поиск, который зависит от того, отсортирован ли массив lookup_array по возрастанию (2) или по убыванию (-2).

Давайте возьмем наш пример VLOOKUP и изменим порядок поиска. Это позволит нам найти оценку второго студента по имени Дэвидсон. Вот формула:

=XLOOKUP(G2,B2:B25,F2:F25,,,-1)

Обратите внимание, что мы извлекаем имя из столбца G2, а не записываем его непосредственно в формулу. Ниже показано, как это выглядит

На этот раз формула вернула результат Томаса Дэвидсона, а не Эйдана Дэвидсона. Но она по-прежнему не может вернуть более одного результата

Похожие: Что такое функция XLOOKUP в Excel и как ее использовать?

Начнем поиск в Excel

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

Об авторе

Алексей Белоусов

Привет, меня зовут Филипп. Я фрилансер энтузиаст . В свободное время занимаюсь переводом статей и пишу о потребительских технологиях для широкого круга изданий , не переставая питать большую страсть ко всему мобильному =)

Комментировать

Оставить комментарий