Сводка
В этой пошаговой статье описывается, как найти данные в таблице (или диапазоне ячеек) с помощью различных встроенных функций в Microsoft Excel. Для получения одного и того же результата можно использовать разные формулы.
Создание примера листа
В этой статье используется пример листа для иллюстрации встроенных функций Excel. Рассмотрим пример ссылки на имя из столбца A и возврата возраста этого человека из столбца C. Чтобы создать этот лист, введите следующие данные в пустой лист Excel.
Вы введите значение, которое нужно найти, в ячейку E2. Формулу можно ввести в любую пустую ячейку на том же листе.
| A | B | В | Г | Д | ||
|---|---|---|---|---|---|---|
| 1 | Имя | Отдел | Возраст | Найти значение | ||
| 2 | Генри | 501 | 28 | Марта | ||
| 3 | Стэн | 201 | 19 | |||
| 4 | Марта | 101 | 22 | |||
| 5 | Ларри | 301 | 29 |
Определения терминов
В этой статье используются следующие термины для описания встроенных функций Excel:
| Термин | Определение | Пример |
|---|---|---|
| Массив таблиц | Вся таблица подстановки | A2:C5 |
| Lookup_Value | Значение, которое будет найдено в первом столбце Table_Array. | E2 |
| Lookup_Array ИЛИ Lookup_Vector |
Диапазон ячеек, содержащих возможные значения подстановки. | A2:A5 |
| Col_Index_Num | Номер столбца в Table_Array, для которых должно быть возвращено соответствующее значение. | 3 (третий столбец в Table_Array) |
| Result_Array ИЛИ Result_Vector |
Диапазон, содержащий только одну строку или столбец. Он должен иметь тот же размер, что и Lookup_Array или Lookup_Vector. | C2:C5 |
| Range_Lookup | Логическое значение (TRUE или FALSE). Если этот аргумент имеет значение ИСТИНА или опущен, возвращается приблизительное соответствие; Если значение FALSE, оно будет искать точное совпадение. | ЛОЖЬ |
| Top_cell | Это ссылка, на основе которой необходимо создать смещение. Top_Cell должны ссылаться на ячейку или диапазон смежных ячеек. В противном случае функция OFFSET возвращает #VALUE! (значение ошибки). | |
| Offset_Col | Это количество столбцов слева или справа, на которое будет ссылаться верхняя левая ячейка результата. Например, "5" в качестве аргумента Offset_Col указывает, что верхняя левая ячейка в ссылке представляет собой пять столбцов справа от ссылки. Offset_Col может быть положительным (то есть справа от начальной ссылки) или отрицательным (что означает слева от начальной ссылки). |
Функции
LOOKUP()
Функция LOOKUP находит значение в одной строке или столбце и сопоставляет его со значением в той же позиции в другой строке или столбце.
Ниже приведен пример синтаксиса формулы LOOKUP:
=LOOKUP(Lookup_Value;Lookup_Vector;Result_Vector)
Следующая формула находит возраст Марии в образце листа:
=LOOKUP(E2;A2:A5;C2:C5)
Формула использует значение "Mary" в ячейке E2 и находит "Mary" в векторе подстановки (столбец A). Затем формула сопоставляет значение в той же строке в векторе результата (столбец C). Так как "Mary" находится в строке 4, функция LOOKUP возвращает значение из строки 4 в столбце C (22).
ПРИМЕЧАНИЕ: Функция LOOKUP требует сортировки таблицы.
Чтобы получить дополнительные сведения о функции LOOKUP , щелкните следующий номер статьи, чтобы просмотреть статью в базе знаний Майкрософт:
Использование функции LOOKUP в Excel
ВПР()
Функция ВПР или Вертикальная подстановка используется, когда данные перечислены в столбцах. Эта функция ищет значение в самом левом столбце и сопоставляет его с данными в указанном столбце в той же строке. Вы можете использовать функцию ВПР для поиска данных в отсортированных или несортированных таблицах. В следующем примере используется таблица с несортированных данных.
Ниже приведен пример синтаксиса формулы ВПР :
=ВПР(Lookup_Value;Table_Array;Col_Index_Num;Range_Lookup)
Следующая формула находит возраст Марии в образце листа:
=ВПР(E2;A2:C5;3;FALSE)
Формула использует значение "Mary" в ячейке E2 и находит "Mary" в самом левом столбце (столбец A). Затем формула сопоставляет значение в той же строке в Column_Index. В этом примере в качестве Column_Index используется значение "3" (столбец C). Так как "Mary" находится в строке 4, функция ВПР возвращает значение из строки 4 в столбце C (22).
Чтобы получить дополнительные сведения о функции VLOOKUP , щелкните следующий номер статьи, чтобы просмотреть статью в базе знаний Майкрософт:
Использование ВПР или HLOOKUP для поиска точного совпадения
INDEX() и MATCH()
Функции INDEX и MATCH можно использовать вместе, чтобы получить те же результаты, что и при использовании функции LOOKUP или VLOOKUP.
Ниже приведен пример синтаксиса, объединяющего INDEX и MATCH , чтобы получить те же результаты, что и LOOKUP и VLOOKUP в предыдущих примерах:
=INDEX(Table_Array;MATCH(Lookup_Value;Lookup_Array;0),Col_Index_Num)
Следующая формула находит возраст Марии в образце листа:
=INDEX(A2:C5;MATCH(E2;A2:A5;0);3)
Формула использует значение "Mary" в ячейке E2 и находит "Mary" в столбце A. Затем он соответствует значению в той же строке в столбце C. Так как "Mary" находится в строке 4, формула возвращает значение из строки 4 в столбце C (22).
ПРИМЕЧАНИЕ: Если ни одна из ячеек в Lookup_Array не соответствует Lookup_Value ("Мэри"), эта формула возвращает #N/A.
Чтобы получить дополнительные сведения о функции INDEX , щелкните следующий номер статьи, чтобы просмотреть статью в базе знаний Майкрософт:
Использование функции INDEX для поиска данных в таблице
OFFSET() и MATCH()
Функции OFFSET и MATCH можно использовать вместе, чтобы получить те же результаты, что и функции в предыдущем примере.
Ниже приведен пример синтаксиса, который объединяет OFFSET и MATCH для получения одинаковых результатов, что и LOOKUP и VLOOKUP:
=OFFSET(top_cell;MATCH(Lookup_Value;Lookup_Array;0);Offset_Col)
Эта формула находит возраст Марии в образце листа:
=OFFSET(A1;MATCH(E2;A2:A5;0);2)
Формула использует значение "Mary" в ячейке E2 и находит "Mary" в столбце A. Затем формула сопоставляет значение в той же строке, но два столбца справа (столбец C). Так как "Mary" находится в столбце A, формула возвращает значение в строке 4 в столбце C (22).
Чтобы получить дополнительные сведения о функции OFFSET , щелкните следующий номер статьи, чтобы просмотреть статью в базе знаний Майкрософт: