Руководство по функции ВПР (VLOOKUP) в Google Таблицах (с примерами)

Содержание:

Функцию ВПР (VLOOKUP) в Google Таблицах можно использовать для поиска значения в столбце и, когда это значение будет найдено, вернуть значение из той же строки из указанного столбца.

Теперь, если это описание звучит скучно и сложно, вот еще один способ понять, что делает эта функция.

Предположим, вы пошли в дорогой ресторан и просматриваете их меню, чтобы заказать что-нибудь вкусное (но вы также не хотите, чтобы это было тяжелым для вас карманом).

Итак, вы начинаете сканировать меню и, когда находите что-то, что вам нравится, переводите взгляд вправо, чтобы увидеть цену этого блюда.

Именно так работает функция VLOOKUP (ВПР) в Google Таблицах.

Если идет вниз по столбцу списков / элементов, ищет указанный элемент и затем возвращает соответствующее значение из той же строки.

Но как именно работает ВПР? Если вас все еще смущает функция ВПР в Google Таблицах, подождите, пока мы не дойдем до раздела с примерами.

Но перед этим давайте быстро взглянем на синтаксис функции Google Таблиц Vlookup:

Синтаксис функции ВПР (VLOOKUP) в Google Таблицах

Вот как выглядит формула VLOOKUP:

VLOOKUP(search_key, range, index, [is_sorted])

search_key — это значение или элемент, который вы ищете. Например, в случае с рестораном это будет бургер или пицца.
диапазон — это диапазон, который будет использоваться в функции Vlookup. В крайнем левом столбце этого диапазона будет выполняться поиск search_key.
index — это номер столбца, из которого вы хотите получить результат. Первый столбец в диапазоне — 1, второй столбец — 2 и так далее. Обратите внимание, что это значение должно быть от 1 до общего количества столбцов. В противном случае будет возвращено #VALUE! Ошибка.
is_sorted — [ ИСТИНА по умолчанию] — в этом аргументе вы можете указать, ищете ли вы точное или приблизительное совпадение. Вы можете использовать FALSE для точного совпадения и TRUE для приблизительного совпадения. Когда вы используете ИСТИНА, список необходимо отсортировать по возрастанию. Если вы не укажете здесь значение, по умолчанию будет ИСТИНА. Обратите внимание, что для использования

Теперь давайте рассмотрим несколько примеров, чтобы понять, как использовать функцию Google Sheets Vlookup в реальных сценариях.

ВПР для чайников: как работает VLOOKUP в реальном мире?

Пример 1: поиск оценок учащихся из списка

В приведенном ниже примере у меня есть имена студентов и их оценка по предмету (скажем, по математике).

Как учителю, вам может потребоваться быстро вычеркнуть оценки учеников из списка, который может быть огромным.

В таких случаях полезно знать ВПР.

Вот формула, по которой вы получите оценки указанных учеников.

=VLOOKUP(E2,$A$2:$B$10,2,False)

Теперь, когда вы меняете имя в ячейке E2, формула будет автоматически обновляться и возвращать оценки этого ученика.

Совет от профессионала: вы также можете создать раскрывающийся список студентов, чтобы вам не приходилось вводить имя вручную.

Пример 2: Найдите оценку учащегося с помощью функции ВПР в Google Таблицах

В примере 1 вы искали точное совпадение имени, чтобы получить отметки.

В этом примере позвольте мне показать вам, как использовать приблизительное соответствие, чтобы получить оценки учащихся на основе их оценок.

Ниже приведена таблица оценок, по которой определяется оценка ученика:

В этом примере нам нужно найти оценки в столбце C на основе оценок (в столбце B). Шкала оценок — E2: F7.

Теперь, прежде чем использовать это, вам нужно знать, что диапазон оценок должен быть в заданном формате. Например, здесь не может быть 0-33, 33-50, 50-70 и т. д. Вам нужно, чтобы числа были отсортированы в порядке возрастания.

Вот формула, по которой вы получите оценку:

=VLOOKUP(B2,$E$2:$F$7,2)

Как это работает: функция ВПР (VLOOKUP) ищет указанную оценку (которая в данном случае является ключом поиска) и ищет ее в столбце «Диапазон оценок» (который является крайним левым столбцом диапазона поиска). Он идет сверху вниз и, когда находит число, которое больше его самого, возвращает оценку из предыдущей строки. Например, если оценка равна 44, функция будет просматривать числа в E2: E7. Поскольку 0 меньше 44, он переходит к 33, который снова ниже 44, поэтому он переходит к 50, что выше. Таким образом, он возвращается к предыдущему значению (33) и возвращает свою оценку (то есть E).

Пример 3: двусторонний поиск с использованием функции Vlookup в Google Таблицах

До сих пор мы видели использование Vlookup для возврата значения из одного столбца, поскольку мы жестко запрограммировали значение. Например, в случае примера 1 он всегда будет возвращать оценку из столбца 2, поскольку мы жестко запрограммировали значение 2 в формуле.

Однако предположим, что у вас есть набор данных, как показано ниже:

Здесь вы можете использовать технику двустороннего поиска, чтобы получить оценки для Брэда (в ячейке F4) в математике (в ячейке G3).

Вот формула, которая сделает это:

=VLOOKUP(F4,A2:D10,MATCH(G3,$A$1:$D$1,0),0)

Как это работает: в этом случае, чтобы сделать предметную часть динамической, мы использовали функцию MATCH (ПОИСКПОЗ) в функции VLOOKUP (ВПР). Функция MATCH ищет имя субъекта в A1: D1 и возвращает номер столбца, в котором находит совпадение. Этот номер столбца затем используется в функции ВПР для получения оценок указанного учащегося по этому предмету.

Мы надеемся, что эта статья ВПР(VLOOKUP) для чайников помогла объяснить, как ВПР работает в Google Таблицах.

Вас могут заинтересовать следующие статьи: