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