Сводка
В этой пошаговой статье описывается, как найти данные в таблице (или диапазоне ячеек) с помощью различных встроенных функций в Microsoft Excel. Для получения одного и того же результата можно использовать разные формулы.
Создание примера листа
В этой статье используется пример листа для иллюстрации встроенных функций Excel. Рассмотрим пример ссылки на имя из столбца A и возврата возраста этого человека из столбца C. Чтобы создать этот лист, введите следующие данные в пустой лист Excel.Вы введите значение, которое нужно найти, в ячейку E2. Формулу можно ввести в любую пустую ячейку на том же листе.
|
A |
B |
C |
D |
E |
||
|
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_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 , щелкните следующий номер статьи, чтобы просмотреть статью в базе знаний Майкрософт:
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 , щелкните следующий номер статьи, чтобы просмотреть статью в базе знаний Майкрософт:
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 , щелкните следующий номер статьи, чтобы просмотреть статью в базе знаний Майкрософт:
