Содержание
В большинстве случаев найти ячейку в таблице Excel довольно легко; если вы не можете просто просмотреть строки и столбцы, вы можете использовать CTRL + F для его поиска. Но если вы работаете с действительно большой таблицей
, это может сэкономить много времени, чтобы использовать одну из этих четырех функций поиска. Независимо от размера вашего документа Microsoft Excel, они будут намного эффективнее.
Функция 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.
Давайте посмотрим, как мы можем это использовать. Как и в моих статьях о булевых операторах в Microsoft Excel
и расширенный подсчет и добавление
Я буду использовать электронную таблицу, созданную на сайте generateata.com. Он содержит идентификационные номера, имена и фамилии, город, штат и SAT. Допустим, я хочу найти оценку SAT человека с фамилией «Винтерс». VLOOKUP облегчает задачу. Вот формула, которую мы использовали:
=VLOOKUP("Winters", C2:F101, 4, 0)Поскольку оценки SAT являются четвертым столбцом после столбца с фамилией, я использовал 4 в качестве аргумента индекса столбца. Обратите внимание, что когда вы ищете текст, установка [range_lookup] на 0 — хорошая идея; без этого вы можете получить плохие результаты. Вот что дает нам Microsoft Excel:
Он вернул 651 балл SAT, принадлежащий студенту по имени Кеннеди Уинтерс, который находится в строке 92 (показано на вставке выше). Для поиска имени потребовалось бы намного больше времени, чем для быстрого ввода синтаксиса!
VLOOKUP также может быть очень полезным, если вы используете Microsoft Excel для уплаты налогов
,
Примечания о VLOOKUP
Несколько вещей полезно помнить, когда вы используете VLOOKUP. Во-первых, как я упоминал ранее, убедитесь, что первый столбец в вашем диапазоне — это тот, который включает в себя значение поиска. Если его нет в первом столбце, функция вернет неверные результаты. Если ваши столбцы хорошо организованы, это не должно быть проблемой.
Второе, что нужно иметь в виду, это то, что VLOOKUP будет возвращать только одно значение. Если бы мы использовали «Грузию» в качестве искомого значения, она вернула бы оценку первого студента из Грузии и не указала бы, что на самом деле есть два студента из Грузии.
Функция HLOOKUP
Если VLOOKUP находит соответствующие значения в другом столбце, HLOOKUP находит соответствующие значения в другой строке. Поскольку обычно легче сканировать заголовки столбцов до тех пор, пока вы не найдете правильный заголовок и не воспользуетесь фильтром, чтобы найти то, что вы ищете, HLOOKUP лучше всего использовать, когда у вас действительно большие таблицы или вы работаете со значениями, организованными по времени. , Вот синтаксис:
=HLOOKUP([lookup_value], [table_array], [row_index_num], [range_lookup])
[lookup_value], опять же, это значение, которое вы знаете и хотите найти соответствующее значение. [table_array] — это ячейки, в которых вы хотите искать. [row_index_num] указывает строку, из которой будет получено возвращаемое значение. И [range_lookup] такой же, как и выше; оставьте это поле пустым, чтобы получить ближайшее значение, когда это возможно, или введите 0, чтобы искать только точные совпадения.
Для этого примера я создал новую электронную таблицу на сайтеataata.com. Эта строка содержит строку для каждого штата, а также оценку SAT (мы будем говорить, что это средняя оценка для штата) в 2000–2014 годах. Мы будем использовать HLOOKUP для определения среднего балла в Миннесоте в 2013 году. Вот как мы это сделаем:
=HLOOKUP(2013, A1:P51, 24)
(Обратите внимание, что 2013 не в кавычках, потому что это число, а не строка; кроме того, 24 прибывает из Миннесоты, находящейся в 24-й строке.) Как вы можете видеть на рисунке ниже, результат возвращается:
Миннесотцы в среднем набрали 1014 баллов в 2013 году.
Заметки о HLOOKUP
Как и в случае с VLOOKUP, значение поиска должно быть в первой строке массива таблицы; это редко проблема с HLOOKUP, так как вы обычно будете использовать заголовок столбца для поиска. HLOOKUP также возвращает только одно значение.
Функции INDEX и MATCH
INDEX и MATCH — две разные функции, но при совместном использовании они могут значительно ускорить поиск в большой таблице. Обе функции имеют недостатки, но, комбинируя их, мы будем опираться на сильные стороны обеих. Сначала, однако, синтаксис:
=INDEX([array], [row_number], [column_number]) =MATCH([lookup_value], [lookup_array], [match_type])
В INDEX [массив] — это массив, в котором вы будете искать. [row_number] и [column_number] могут быть использованы для сужения вашего поиска; мы посмотрим на это через мгновение.
[Lookup_value] в MATCH — это поисковый термин, который может быть строкой или числом; [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 раз в большой электронной таблице базы данных.
это содержало несколько сотен столбцов!
Пусть поиск начнется
Microsoft Excel имеет много чрезвычайно мощных функций
и четыре из перечисленных выше просто поцарапать поверхность. Однако даже при этом кратком обзоре вы сможете сэкономить много времени, работая с большими таблицами. Если вы хотите узнать, сколько еще можно сделать с помощью INDEX, ознакомьтесь с «Впечатляющим индексом» в Microsoft Excel Hero.
Как вы проводите поиск в больших таблицах? Как вы нашли эти функции поиска полезными? Поделитесь своими мыслями и опытом ниже!
Кредиты изображений: увеличительное стекло на электронной таблице через Shutterstock
