Впр в гугл таблице инструкция пошагово

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

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

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

На примерах мы рассмотрим, как наиболее правильно и эффективно использовать её в ваших Google таблицах.

Особое внимание мы уделим  ошибкам, возникающим при использовании ВПР (VLOOKUP), а также приёмам, которые позволят сделать её использование простым и вместе с тем эффективным.

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

Запомнить назначение её просто: ВПР (VLOOKUP) означает сокращение “Vertical Look Up” или «Вертикальный ПРосмотр».

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

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

Синтаксис функции позволят нам применять ее для очень большого круга задач, при котором необходим поиск и возврат определённого значения.

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

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

запрос (search_key) : это то наименование, которое ВПР будет искать в первом столбце того диапазона (таблицы), который мы ей укажем.

диапазон (range): это та таблица или диапазон данных, в котором будет происходить поиск. Именно в первом столбце этого диапазона мы и будем искать наш запрос.

Существенным ограничением функции ВПР является то, что она всегда производит поиск в первом (крайнем левом) столбце диапазона данных.

номер_столбца (index): номер столбца, значение из которого вы хотите получить, когда будет найден запрос. Нумерация столбцов всегда начинается с 1. Отсчет начинается слева направо. Слолбец 1 – это всегда столбец , в котором происходит поиск. Столбец 2 – это столбец, находящийся справа от него, и так далее.

отсортировано (is_sorted): необязательный параметр. Он указывает, отсортирован ли первый столбец диапазона, в котором мы будем искать наш запрос. Может принимать два значения – ИСТИНА (TRUE) или ЛОЖЬ (FALSE). Если вы ничего не укажете, то по умолчанию устанавливается значение TRUE.

Если диапазон не отсортирован (FALSE) и в этом случае функция будет искать точное совпадение параметра «запрос» с одним из значений первого столбца диапазона. Как только будет найдено точное совпадение, поиск прекращается.

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

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

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

Если точного совпадения не удастся обнаружить, то будет возвращено сообщение об ошибке (#N/A).

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

Если ваш диапазон поиска не отсортирован, а вы по ошибке указали значение ИСТИНА (TRUE) (либо вообще ничего не указали по забывчивости, что также означает TRUE), то очень велика вероятность, что функция ВПР ничего не найдет и вернет ошибку.

К примеру, если ваш критерий поиска начинается с буквы “A”, а в начале списка находится наименование, начинающееся с буквы “C”, то, оценив это первое наименование, функция решит, что если встретилась буква “C”, то в отсортированном списке букву “A” дальше искать бессмысленно. Поиск прекратится и будет возвращена ошибка (#N/A), несмотря на то, что правильное наименование в вашем диапазоне было. Но вы об этом даже не узнаете.

Может случиться и другое – будет найдено неточное совпадение, то есть найден товар с похожим названием, сотрудник с похожей фамилией. В случае, если вы ищете конкретного человека либо конкретный товар, вряд ли вас устроит такой приблизительный поиск. Но самое плохое заключается в том, что вы не узнаете о том, что найдено не точное, а приблизительное совпадение. А это может привести к ошибкам в принятии решений на основе ваших расчетов.

Поэтому рекомендуется всегда указывать значение ЛОЖЬ (FALSE) в качестве параметра «отсортировано» (is_sorted).

Вы спросите – а зачем же тогда этот параметр, если его значение ИСТИНА (TRUE) приводит к таким проблемам. Ответ заключается в том, что если всё же вы будете применять формулу ВПР (VLOOKUP) на отсортированном массиве, то производительность и скорость поиска возрастут по разным оценкам примерно в 50 (пятьдесят!) раз. При работе с большими таблицами это будет очень заметно.

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

Мы познакомились с синтаксисом функции ВПР (VLOOKUP), теперь рассмотрим особенности её применения на примерах.

Как эффективно использовать ВПР (VLOOKUP) в гугл таблицах?

Давайте начнем с самого простого применения формулы ВПР в Google таблице. Предположим, у нас есть две таблицы. Первая – это прайс лист с наименованиями и ценами. Вторая – это заказ на покупку некоторых из этих товаров. Искать в прайс листе нужный товар и руками вписывать в заказ его цену – занятие очень утомительное. Ведь он может насчитывать сотни строк. Нам необходимо сделать, чтобы всё происходило автоматически.

В ячейке F3 пишем знак равно (=) и начинаем вводить формулу с ее первых букв «вп». Обычно в этот момент появляется подсказка и мы можем просто выбрать необходимую нам функцию. Далее, как обычно, появляются подсказки, которые позволяют нам определить, какой аргумент функции мы сейчас вводим.

Первым аргументом введём “бананы”. Обратите внимание, что любой текст, который мы вводим, должен быть в кавычках.

Ставим запятую, и подсказка нам покажет, что теперь нужно ввести диапазон со значениями (таблица).  В нашем примере нужно ввести это A3:B21. Как и обычно, мы можем просто выделить нужный диапазон мышкой, и он вставится в формулу сам, или ввести его координаты с клавиатуры.

После этого нужно опять поставить запятую и указать номер столбца, значение из которого нам нужно вернуть. В нашем случае это 2.

Последняя запятая, и пишем ЛОЖЬ (FALSE), то есть искать будем точное совпадение.

Наша функция в ячейке F3 будет выглядеть так:

=ВПР(“бананы”;A3:B21;2;ЛОЖЬ)

И она должна вернуть цену 1.9.

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

Давайте теперь внимательно посмотрим, как работает функция ВПР в этом несложном примере.

1 – выбираем для поиска первый, то есть крайний левый, столбец указанного в формуле диапазона.

2 – ищем в этом столбце слово “бананы” с точным соотвествием. Оно находится в 5-й строке.

3 – двигаемся вправо по строке, в которой нашлось искомое слово, до второго столбца (столбец поиска считаем первым).

4 – значение, указанное во втором столбце пятой строки нашего диапазона (1.9), вставляем в ячейку F3, в которую мы ранее вписали формулу ВПР.

Как видите, всё довольно просто.

Однако, согласитесь, что каждый раз руками вписывать в формулу условие поиска очень долго и неэффективно. Я думаю, вы догалались, что слово или число, которое мы будем искать, можно заменить ссылкой на ячейку, в которой они записаны.

А сейчас изменим нашу формулу в ячейке F3:

=ВПР(D3;A3:B21;2;ЛОЖЬ)

Теперь процесс поиска будет выглядеть следующим образом:

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

Поиск по части значения аргумента при использовании ВПР (VLOOKUP).

Если нам нужно найти значение, но мы знаем лишь часть от него, нам нужно использовать знаки подстановки.

Это знакомые нам вопросительный знак (?) и звездочка (*). Напомню, что вопросительный знак заменяет собой любой символ, а звездочка – любое количество символов (в том числе и ноль).

Предположим, нам нужно узнать цену на товар, название которого начинается с “пер”. Мы хотели бы получить цену персика.

Давайте посмотрим на нашем примере, как это будет выглядеть.

Как видим, функция искала в столбце “Товар” значение, начинающееся с “пер”.

Вы спросите: «А почему был выбран “персик”, а не “перец”? Ведь первые три буквы у них одинаковы?». Дело в том, что, как мы уже отмечали, функция ВПР (VLOOKUP) ищет подходящее значение, двигаясь сверху вниз. И как только подходящее совпадение было найдено, дальнейший поиск был прекращён. Поэтому вместо цены персика мы получили цену перца.

Это очень важное ограничение функции ВПР, которое нужно обязательно учитывать.

Как использовать ВПР с данными другой таблицы

Очень часто ВПР используется для того, чтобы заполнить одну таблицу данными, найденными в другой таблице.

Продолжим рассматривать наш пример. Заполним таблицу «Заказ» ценами из таблицы «Прайс лист» и рассчитаем стоимость товаров.

Мы уже нашли в прайс-листе цену для бананов. Напомню, в ячеейке F3 мы записали

=ВПР(D3;A3:B21;2;ЛОЖЬ)

Но для того, чтобы использовать эту формулу для заполнения таблицы, её нужно немного изменить. Добавим абсолютные ссылки, чтобы при копировании формулы получить правильные ссылки.

Теперь наша формула в F3 выглядит следующим образом:

=ВПР(D3;$A$3:$B$21;2;ЛОЖЬ)

Теперь ссылка на диапазон поиска не будет меняться при копировании и перемещении формулы.

В столбце G введите формулу, которая рассчитает итог. К примеру, в ячейке G3

=F3*E3

Теперь скопируйте формулы в ячейки, расположенные ниже. Можно использовать комбинацию клавиш Ctrl+C  Ctrl+V, а можно зацепить мышкой правый нижний угол ячейки и перетащить вниз.

Таким образом, таблица “Заказ” оказалась связанной с таблицей “Прайс лист”. При помощи ВПР мы получаем из нее цены заказанных товаров.

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

В нашей формуле мы должны изменить адрес диапазона, в котором будет происходить поиск.

=ВПР(A3;’прайс-лист’!$A$3:$B$21;2;ЛОЖЬ)

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

Важно! Не забудьте в ссылке на диапазон поиска использовать абсолютные ссылки ($)!

Ну и, наконец, рассмотрим случай, когда таблица с ценами находится не просто на другом листе в том же самом файле, а расположена в другом файле Google таблиц. К примеру, мы собрали все прайс-листы в отдельный файл таблиц – так легче с ними работать.

Здесь нам на помощь придёт функция IMPORTRANGE, которая позволяет получать данные из других файлов Google таблиц.

Вот как будет выглядеть теперь наша функция поиска цены товара в ячейке С3:

=ВПР(A3;IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1LxhjjzG06DAjfdkI5nKKiOr4_Nuem2m4yTlsrYAJiyM/edit#gid=181332360″;”прайс-лист!A3:B21”);2;ЛОЖЬ)

Как видите, вновь изменилась только ссылка на диапазон данных.

В качестве аргументов функции IMPORTRANGE мы используем:

1 аргумент – ссылка на файл Google таблиц. Ее можно получить из адресной строки браузера, открыв эту таблицу в новом окне.

2 аргумент – обычная ссылка на диапазон данных, которая обязательно включает в себя наименование листа (в нашем примере – лист “1”).

Все остальные действия ничем не отличаются от того, что мы с вами уже рассмотрели.

Итак, мы познакомились с синтаксисом функции ВПР, попытались понять логику ее работы и научились составлять формулы.

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

Другие примеры использования функций Google таблиц: