Как использовать функцию ВПР в Excel

Оглавление:

2024 Автор: Abigail Brown | [email protected]. Последнее изменение: 2023-12-17 06:59

Ключевые выводы

Функция ВПР в Excel используется для поиска значения в электронной таблице.
Синтаксис и аргументы:

В этой статье объясняется, как использовать функцию ВПР во всех версиях Excel, включая Excel 2019 и Microsoft 365.

Что такое функция ВПР?

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

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

ВПР Синтаксис и аргументы функции ВПР

Есть четыре возможных части этой функции:

=ВПР(значение_поиска, таблица_просмотра, номер_столбца, [приблизительное_соответствие])

search_value - это значение, которое вы ищете. Он должен быть в первом столбце lookup_table.
lookup_table - это диапазон, в котором вы ищете. Сюда входит search_value.
column_number - это число, которое показывает, сколько столбцов в lookup_table слева должно быть столбцом, из которого функция ВПР возвращает значение.
приблизительное_сопоставление является необязательным и может принимать значения TRUE или FALSE. Он определяет, следует ли найти точное совпадение или приблизительное совпадение. Если этот параметр опущен, по умолчанию используется значение TRUE, что означает, что будет найдено приблизительное совпадение.

Примеры функций ВПР

Вот несколько примеров, демонстрирующих функцию ВПР в действии:

Найти значение рядом со словом из таблицы

=ВПР("Лимоны", A2:B5, 2)

Это простой пример функции ВПР, где нам нужно найти, сколько лимонов у нас есть на складе, из списка, состоящего из нескольких предметов. Диапазон, который мы просматриваем, - это A2:B5, а число, которое нам нужно извлечь, находится в столбце 2, поскольку «На складе» - это второй столбец в нашем диапазоне. Результат здесь 22.

Найти номер сотрудника по имени

=ВПР(A8, B2:D7, 3)

=ВПР(A9, A2:D7, 2)

Вот два примера, где мы пишем функцию ВПР немного по-другому. Они оба используют одинаковые наборы данных, но поскольку мы извлекаем информацию из двух отдельных столбцов, 3 и 2, мы делаем это различие в конце формулы - первый захватывает положение человека в A8 (Финли), а вторая формула возвращает имя, соответствующее номеру сотрудника в ячейке A9 (819868). Поскольку формулы ссылаются на ячейки, а не на конкретную текстовую строку, мы можем опустить кавычки.

Использование оператора IF с функцией ВПР

=ЕСЛИ(ВПР(A2, Лист4!A2:B5, 2)>10, "Нет", "Да")

ВПР также можно комбинировать с другими функциями Excel и использовать данные из других листов. В этом примере мы делаем и то, и другое, чтобы определить, нужно ли нам заказывать больше элемента в столбце A. Мы используем функцию ЕСЛИ, поэтому, если значение в позиции 2 в Sheet4!A2:B5 больше 10, мы пишем Нет чтобы указать, что нам не нужно заказывать больше.

Найти ближайшее число в таблице

=ВПР(D2, $A$2:$B$6, 2)

В этом последнем примере мы используем функцию ВПР для определения процента скидки, который следует использовать для различных оптовых заказов обуви. Скидка, которую мы ищем, находится в столбце D, диапазон, который включает информацию о скидке, - A2:B6, и в этом диапазоне находится столбец 2, содержащий скидку. Поскольку функции ВПР не требуется находить точное совпадение, параметр приблизительное_соответствие остается пустым, чтобы указать ИСТИНА. Если точное совпадение не найдено, функция использует следующую меньшую сумму.

Вы можете видеть, что в первом примере из 60 заказов скидка не найдена в таблице слева, поэтому используется следующая меньшая сумма 50, что составляет скидку 75%. Колонка F - это окончательная цена после подсчета скидки.

ВПР Ошибки и правила

Вот что следует помнить при использовании функции ВПР в Excel:

Если search_value является текстовой строкой, она должна быть заключена в кавычки.
Excel вернет NO MATCH, если ВПР не сможет найти результат.
Excel вернет NO MATCH, если в lookup_table нет числа, которое больше или равно search_value.
Excel вернет REF! если column_number больше, чем количество столбцов в lookup_table.
search_value всегда находится в крайней левой позиции lookup_table и занимает позицию 1 при определении column_number.
Если вы укажете FALSE для приблизительного_соответствия, а точное совпадение не будет найдено, функция ВПР вернет Н/Д.
Если вы укажете TRUE для приблизительного_соответствия и точное совпадение не будет найдено, будет возвращено следующее меньшее значение.
Несортированные таблицы должны использовать значение FALSE для приблизительного_соответствия, чтобы возвращалось первое точное совпадение.
Если приблизительное_соответствие имеет значение ИСТИНА или опущено, первый столбец необходимо отсортировать по алфавиту или по числам. Если он не отсортирован, Excel может вернуть непредвиденное значение.
Использование абсолютных ссылок на ячейки позволяет автоматически заполнять формулы без изменения таблицы поиска.

Другие функции, такие как функция ВПР

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

Функция XLOOKUP аналогична, но работает в любом направлении.