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

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

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





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



1. Функция ВПР.

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





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

=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('Winters', C2:F101, 4, 0)

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

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



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

Заметки о ВПР

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



Также имейте в виду, что функция ВПР всегда возвращает только одно значение. Если бы вы использовали «Джорджия» в качестве поискового значения, он бы возвратил оценку первого студента из Джорджии и не дал бы никаких указаний на то, что на самом деле есть два студента из Джорджии.

2. Функция HLOOKUP

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

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

=HLOOKUP([lookup_value], [table_array], [row_index_num], [range_lookup])
  • [lookup_value] - это значение, которое вы знаете и для которого хотите найти соответствующее значение.
  • [table_array] это ячейки, в которых вы хотите искать.
  • [row_index_num] указывает строку, из которой будет получено возвращаемое значение.
  • [range_lookup] то же самое, что и в режиме ВПР, оставьте поле пустым, чтобы получить ближайшее значение, когда это возможно, или введите 0, чтобы искать только точные совпадения.

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

=HLOOKUP(2013, A1:P51, 24)

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

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

Вот как рассчитать средневзвешенное значение в Excel .

Примечания к HLOOKUP

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

3-4. Функции ИНДЕКС и ПОИСКПОЗ

ИНДЕКС и ПОИСКПОЗ - это две разные функции, но когда они используются вместе, они могут значительно ускорить поиск в большой электронной таблице. Обе функции имеют недостатки, но, объединив их, мы будем развивать сильные стороны обеих.

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

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

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

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

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

как установить Linux на Mac
=INDEX(F:F, MATCH('Waters', C:C, 0))

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

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

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

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

Если вы действительно хотите освоить Microsoft Excel, вам будет действительно полезно держать под рукой Essential Excel Cheat Sheet!

Кредит изображения: Cico / Shutterstock

Делиться Делиться Твитнуть Эл. адрес Canon против Nikon: какой бренд камеры лучше?

Canon и Nikon - два крупнейших имени в индустрии фотоаппаратов. Но какой бренд предлагает лучшую линейку камер и объективов?

Читать далее Похожие темы
  • Продуктивность
  • Таблица
  • Майкрософт Эксель
  • Уловки поиска
Об авторе Ян Бакли(Опубликовано 216 статей)

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

Ещё от Ian Buckley

Подписывайтесь на нашу новостную рассылку

Подпишитесь на нашу рассылку, чтобы получать технические советы, обзоры, бесплатные электронные книги и эксклюзивные предложения!

Нажмите здесь, чтобы подписаться