Производительность

4 функции поиска в Excel для эффективного поиска в таблицах

4 функции поиска в Excel для эффективного поиска в таблицах

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

Откройте БЕСПЛАТНУЮ шпаргалку «Essential Excel Formulas» прямо сейчас!

Это подпишет вас на нашу рассылку

Введите адрес электронной почты

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

1. Функция VLOOKUP

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

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

=VLOOKUP([lookup_value], [table_array], [col_index_num], [range_lookup]) 
  • [lookup_value] это часть информации, которая у вас уже есть. Например, если вам нужно знать, в каком штате находится город, это будет название города.
  • [table_array] позволяет указать ячейки, в которых функция будет искать и возвращать значения. При выборе диапазона убедитесь, что первый столбец, включенный в ваш массив, будет содержать значение поиска!
  • [col_index_num] — номер столбца, который содержит возвращаемое значение.
  • [range_lookup] является необязательным аргументом и принимает 1 или 0. Если вы введете 1 или пропустите этот аргумент, функция ищет введенное вами значение или следующий наименьший номер. Таким образом, на изображении ниже VLOOKUP, который ищет оценку SAT 652, вернет 646, так как это самое близкое число в списке, которое меньше 652, и [range_lookup] по умолчанию равно 1.

ВПР-округление

Давайте посмотрим, как вы можете использовать это. Эта электронная таблица содержит идентификационные номера, имена и фамилии, город, штат и SAT. Допустим, вы хотите найти оценку SAT человека с фамилией «Винтерс». VLOOKUP облегчает задачу. Вот формула, которую вы бы использовали:

 =VLOOKUP("Winters", C2:F101, 4, 0) 

Поскольку баллы SAT являются четвертым столбцом после столбца с фамилией, 4 является аргументом индекса столбца. Обратите внимание, что когда вы ищете текст, установка [range_lookup] в 0 — это хорошая идея. Без этого вы можете получить плохие результаты.

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

ВПР-первенствует

Он вернул 651 балл SAT, принадлежащий студенту по имени Кеннеди Уинтерс, который находится в строке 92 (показано на вставке выше). Для поиска имени потребовалось бы намного больше времени, чем для быстрого ввода синтаксиса!

Примечания о VLOOKUP

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

Кроме того, имейте в виду, что VLOOKUP будет возвращать только одно значение. Если бы вы использовали «Грузию» в качестве значения для поиска, она бы вернула счет первого студента из Грузии и не указала бы, что на самом деле есть два студента из Грузии.

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, чтобы искать только точные совпадения.

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

 =HLOOKUP(2013, A1:P51, 24) 

Как вы можете видеть на картинке ниже, результат возвращается:

ГПР-первенствует

Миннесотцы в среднем набрали 1014 баллов в 2013 году. Обратите внимание, что 2013 не в кавычках, потому что это число, а не строка. Кроме того, 24 из Миннесоты находятся в 24-м ряду.

Заметки о HLOOKUP

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

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

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

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

 =INDEX([array], [row_number], [column_number]) 
  • [массив] — это массив, в котором вы будете искать.
  • [row_number] и [column_number] могут быть использованы для сужения вашего поиска (мы рассмотрим это чуть позже .)
 =MATCH([lookup_value], [lookup_array], [match_type]) 
  • [lookup_value] — это поисковый термин, который может быть строкой или числом.
  • [lookup_array] — это массив, в котором Microsoft Excel будет искать поисковый запрос.
  • [match_type] является необязательным аргументом, который может быть 1, 0 или -1. 1 вернет наибольшее значение, которое меньше или равно вашему поисковому запросу. 0 вернет только ваш точный термин, а -1 вернет наименьшее значение, которое больше или равно вашему поисковому запросу.

Может быть неясно, как мы будем использовать эти две функции вместе, поэтому я изложу это здесь. MATCH принимает поисковый запрос и возвращает ссылку на ячейку. На рисунке ниже видно, что при поиске значения 646 в столбце F MATCH возвращает 4.

матч-пример

INDEX, с другой стороны, делает обратное: он берет ссылку на ячейку и возвращает значение в ней. Здесь вы можете видеть, что, когда велено вернуть шестую ячейку столбца City, INDEX возвращает значение «Anchorage», значение из строки 6.

Индекс-пример

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

 =INDEX(F:F, MATCH("Waters", C:C, 0)) 

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

Индекс матча

Как видно из вставки, Оуэн Уотерс набрал 1720, число, которое появляется при запуске функции. Это может показаться не таким уж полезным, если вы можете просто просмотреть несколько столбцов, но представьте, сколько времени вы бы сэкономили, если бы вам пришлось делать это 50 раз в большой таблице базы данных который содержал несколько сотен столбцов!

Пусть поиск в Excel начинается

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

Если вы действительно хотите освоить Microsoft Excel, вы могли бы по-настоящему извлечь выгоду из ведения шпаргалки Essential Excel. Шпаргалка по под рукой!

Изображение предоставлено: Cico / Shutterstock

Похожие посты
Производительность

Excel против Google Sheets: какой из них лучше для вас?

Производительность

Как улучшить управление документами с помощью свойств в Microsoft Office

ИнтернетПроизводительность

5 инструментов для извлечения изображений из файлов PDF

AndroidПроизводительность

Adobe Reader X добавляет защищенный режим для пользователей Windows, Android получает новые функции [Новости]