Как работает впр в гугл таблицах

Функция ВПР (VLOOKUP) — как работает и почему, примеры, типовые ошибки и чем заменить

Общая информация о ВПР (VLOOKUP)
Функция ВПР — это одна из наиболее популярных функций ссылок и массивов. В англоязычном Excel, а также в Google Sheets, LibreOffice, OpenOffice, эта функция называется VLOOKUP.

Уровень сложности по шкале BRP ADVICE — 3 из 7 .
ВПР (VLOOKUP) позволяет найти в таблице с данными значение из нужной строки и столбца. При этом нужную строчку ВПР (VLOOKUP) найдет сам, а вот столбец вам придется указать самостоятельно.

Чтобы разобраться с ВПР (VLOOKUP), сначала надо разобраться с тем, как работают все функции ссылок и массивов.

Как работают функции ссылок и массивов
В Excel, Google Sheets, LibreOffice, OpenOffice и других табличных документах вы можете ссылаться на ячейку, чтобы получить ее значение или использовать в расчетах. Обычно, в функциях вы указываете ссылки на ячейки вида A1, B17, G34, Z52. Некоторые привыкли работать со ссылками вида R1C1, R17C2, R34C7, R52C26. При этом вы указываете номер строки и букву/номер столбца с начала листа. Указав ячейку, вы даете программе точное указание, что вам нужно значение именно этой ячейки, находящейся на пересечении нужной строки и столбца. То есть выглядит все это примерно так:

Функции ссылок и массивов работают немного по-другому. В функциях ссылок и массивов вы задаете новую таблицу на листе, которая может находиться, где угодно. Назовем эту новую таблицу — внутренняя таблица. Вся нумерация строк и столбцов в этой внутренней таблице начинается заново. От 1 и до последней строки, от 1 и до последнего столбца. При этом совершенно не важно, где начинается внутренняя таблица: в самом начале листа, в его середине или ближе к концу. Всегда первая ячейка этой таблицы образует пересечение первой строки и первого столбца. Вот как это можно схематически изобразить:

К сожалению, ни один табличный редактор не нарисует вам такой подсказки. Все это и вам, и программе придется держать в голове.
Как указать, где находится внутренняя таблица? Для этого вам понадобится указать ее адрес при помощи стандартных ссылок вида A1 или R1C1. На картинке выше адрес внутренней таблицы — это диапазон J17:O25.
Что же происходит в этой внутренней таблице? Каждая ячейка получает новый адрес, который состоит из номера строки и номера столбца. Именно так: сначала номер строки, затем номер столбца.
Что же делают функции ссылок и массивов? Их конечная цель — получить значение по его внутреннему адресу. При этом функции ссылок и массивов могут как найти нужную строку и нужный столбец сами, так и использовать введенные пользователем значения. Для разных задач используются разные функции. Но в конечном счете получается примерно так, Excel , Google Sheets, LibreOffice, OpenOffice определяют, что вам нужна пятая строка и третий столбец и выдают значение из такой ячейки:
Как работает ВПР (VLOOKUP)
Самый простой способ разобраться с ВПР (VLOOKUP) — это рассмотреть его на примерах. Рассмотрим один пример с точным поиском, второй пример — с приблизительным поиском.
Пример 1 — точный поиск Ссылка на файл-пример приведена в конце описания этого примера. Допустим, есть таблица по сотрудникам вашей организации и их окладам. В этой таблице указан каждый сотрудник (его ФИО) и его оклад. Первый столбец этой таблицы — ФИО сотрудника, второй — оклад. То есть ваша таблица выглядит так:
ВПР (VLOOKUP) позволит вам найти оклад, указывая ФИО сотрудника. Конечно, когда у вас в таблице 5 строк и задача разовая, глазами вы найдете нужное значение очень быстро. А если у вас сотрудников 200? Или 5000? ВПР (VLOOKUP) поможет упростить вам жизнь.
Что же нужно сделать, чтобы найти оклад, например, Иванова С.А.?
Надо написать формулу =ВПР(«Иванов С.А.»;C3:D9;2;ЛОЖЬ) или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:
=VLOOKUP(«Иванов С.А.»;C3:D9;2;FALSE) . Кстати, в некоторых версиях Excel, вместо «;» должна использоваться «,».
После этого программа вернет вам ответ 21 000.
Что значат все аргументы ВПР (VLOOKUP)? 1. Искомое значение. В нашем примере это «Иванов С.А.» — это значение, которое ВПР будет искать в первом столбце внутренней таблицы. Обратите внимание, это первый столбец именно внутренней таблицы, а не первый столбец листа. И это всегда первый столбец. ВПР не может искать во втором, третьем или любом другом столбце таблицы — только в первом столбце внутренней таблицы.
Кстати, «Иванов С.А.» у нас написано в кавычках, потому что любой текст внутри формулы должен быть написан в кавычках. Исключением являются только названия функций и именованных диапазонов. В остальных случаях всегда ставьте текст в кавычки.
2. Таблица. В нашем примере это C3:D9. Это координаты или адрес той самой внутренней таблицы. Именно в первом столбце этой таблицы Excel будет пытаться найти искомое значение (смотри пункт выше).

3. Номер столбца. В нашем примере это 2. Это номер столбца, в котором содержится информация, которую вы ищете (в нашем случае — оклад). Посчитать вам его нужно вручную. Начинайте подсчет с первого столбца внутренней таблицы: первый — это ФИО, второй — оклад. Значит ставим цифру 2.
4. Интервальный просмотр. В нашем примере это ЛОЖЬ (FALSE). По сути, это ответ на вопрос «Мы же приблизительно ищем, верно?». ИСТИНА (TRUE) означает «да, приблизительно», ЛОЖЬ (FALSE) — «нет, мы ищем именно это». То есть ИСТИНА (TRUE) означает, что нам подойдет и Иванов С.А., и Иванов С.И., и Иванова О.П., и может быть кто-то еще (кого из них выберет Excel, смотрите в Примере №2). Сейчас нам нужен именно Иванов С.А., поэтому мы поставили ЛОЖЬ (FALSE). Кстати, некоторые используют значения 1 и 0 вместо ИСТИНА (TRUE) и ЛОЖЬ (FALSE) соответственно. Работать будет, но ваших коллег вы можете запутать. Так что мы советуем всегда писать слово, а не цифру.
Что именно делает ВПР (VLOOKUP) в этом примере? ВПР (VLOOKUP) просматривает каждую ячейку первого столбца сверху вниз. Он смотрит в первой строке «Газоев И.В.»=»Иванов С.А.»? Видит, что нет. Тогда смотрит вторую: «Ромашкина Б.О.»=Иванов С.А.»? И так далее, пока не доходит до нужной строки. Когда ВПР (VLOOKUP) видит, что «Иванов С.А.»=»Иванов С.А.», он останавливается и запоминает номер строки, в которой это произошло. В нашем примере это 4 строка. Четвертая, потому что подсчет начинается не с начала листа, а с начала внутренней таблицы. И, наконец, ВПР (VLOOKUP) возвращает значение внутренней таблицы, которое находится на пересечении 4 строки (то, что он нашел) и 2 столбца (то, что мы указали ему аргументом). А это значение 21 000. Все, задача решена.
А вот этот текст — это ссылка на скачивание примера в Excel 2010-2013. Хотите решить пример онлайн? Оставьте заявку, мы уже работаем над этим. Как всегда, наши упражнения работают в Excel 2007-2013, а расширенный функционал можно использовать в Excel 2010 и 2013. С его помощью можно начать любое упражнение с начала всего одной кнопкой «Начать заново» на вкладке BRP ADVICE, появляющейся в Excel при открытии наших упражнений. Только не забудьте включить макросы.
Пример 2 — приблизительный поиск Ссылка на файл-пример приведена в конце описания этого примера.
Допустим, у вас в компании менеджер по продажам получает премию в зависимости от объема продаж. Если продажи за месяц больше 100, то премия составляет 4% от продаж. Если продажи больше 200, то премия — 5%. Больше 300 — 6%. Больше 400 — 7%. И при любых продажах больше 500 — 8%. При продажах меньше 100, менеджер премию не получает. При помощи ВПР (VLOOKUP) можно быстро узнать, какую премию получит менеджер при его фактических продажах.
Для этого вначале нам потребуется составить таблицу с уровнями плана и ставками премии. Такая таблица будет выглядеть вот так:
Теперь мы можем найти премию Иванова С.А. в зависимости от его фактических результатов. Основным отличием от предыдущего примера является то, что фактические продажи Иванова могут быть не только ровно 100, 200, 300, 400 или 500. Они могут находиться между указанными нами значениями. Например, фактические продажи Иванова составят 350.
Для того, чтобы найти ставку премии при продажах 350, можно использовать такую формулу: =ВПР(350;C3:D8;2;ИСТИНА) или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:
=VLOOKUP(350;C3:D9;2;TRUE) . Не забывайте, что в некоторых версиях Excel, вместо «;» должна использоваться «,».
После этого программа вернет вам ответ 6%. Все что вам остается сделать, это перемножить продажи 350 и ставку премии 6%. Это и будет премия Иванова С.А. при продажах равных 350.

Что значат все аргументы ВПР (VLOOKUP)? 1. Искомое значение. В нашем примере это 350 — это значение, которое ВПР будет искать в первом столбце внутренней таблицы. Обратите внимание, это первый столбец именно внутренней таблицы, а не первый столбец листа. И это всегда первый столбец. ВПР не может искать во втором, третьем или любом другом столбце таблицы — только в первом столбце внутренней таблицы.

Кстати, на этот раз 350 у нас написано без кавычек, потому что 350 — это число, а в кавычки мы берем только текст.
2. Таблица. В нашем примере это C3:D8. Это координаты или адрес той самой внутренней таблицы. Именно в первом столбце этой таблицы Excel будет пытаться найти искомое значение (смотри пункт выше).

3. Номер столбца. В нашем примере это 2. Это номер столбца, в котором содержится информация, которую вы ищете (в нашем случае — ставка премии). Посчитать его нужно вручную. Начинайте подсчет с первого столбца внутренней таблицы: первый — это «При продажах больше», второй — «ставка премии». Значит ставим цифру 2.
4. Интервальный просмотр. На этот раз нам нужна ИСТИНА (TRUE). По сути, это ответ на вопрос «Мы же приблизительно ищем, верно?». ИСТИНА (TRUE) означает «да, приблизительно», ЛОЖЬ (FALSE) — «нет, мы ищем именно это». Мы используем ИСТИНА (TRUE), потому что нам нужно найти не только значения 100, 200 и другие, прямо указанные в таблице, но и все, что находится между ними. То есть ИСТИНА (TRUE) означает, что нам подойдет и 300, и 350, и 380 и так далее, и может быть что-то еще (какое из них выберет Excel, смотрите ниже).
Кстати, некоторые используют значения 1 и 0 вместо ИСТИНА (TRUE) и ЛОЖЬ (FALSE) соответственно. Работать будет, но ваших коллег вы можете запутать. Так что мы советуем всегда писать слово, а не цифру.
Что именно делает ВПР (VLOOKUP) в этом примере? Как и в прошлый раз ВПР (VLOOKUP) последовательно просматривает все ячейки первого столбца нашей таблицы. Но на этот раз он не ищет точного соответствия, а выполняет такие проверки: 100, указанное в таблице, меньше 350? Да, — отвечает ВПР (VLOOKUP), — тогда запоминаем строку 1 и смотрим следующее значение. 200 меньше 350? Да, тогда забываем строку 1, запоминаем 2 и смотрим следующее. 300 меньше 350? Да, тогда забываем строку 2, запоминаем 3 и смотрим следующее. 400 меньше 350? Нет! Тогда возвращаемся к строке 3. И, наконец, ВПР (VLOOKUP) возвращает значение, указанное на пересечении 3 строки (то, что он нашел) и 2 столбца (то, что мы указали в функции).
Примерно вот так, можно описать логику вычислений ВПР (VLOOKUP) при работе с приблизительным поиском. Обратите внимание, что ВПР (VLOOKUP) не просматривает всю таблицу, а смотрит до тех пор пока значения не оказываются больше искомого. Иными словами, ВПР (VLOOKUP) ищет ближайшее наименьшее. Но для того, чтобы все работало правильно, вам нужно отсортировать таблицу по возрастанию значений в первом столбце.

А вот этот текст — это ссылка на скачивание примера в Excel 2010-2013. Хотите решить пример онлайн? Оставьте заявку, мы уже работаем над этим. Как всегда, наши упражнения работают в Excel 2007-2013, а расширенный функционал можно использовать в Excel 2010 и 2013. С его помощью можно начать любое упражнение с начала всего одной кнопкой «Начать заново» на вкладке BRP ADVICE, появляющейся в Excel при открытии наших упражнений. Только не забудьте включить макросы.
Типичные ошибки Какие ошибки мы чаще всего встречаем при работе с ВПР (VLOOKUP)? 1. Это ошибки, связанные с неверной работой с 4 аргументом, с интервальным просмотром. Часто путаются в разнице между ИСТИНА (TRUE) и ЛОЖЬ (FALSE). Запомните такой вопрос: «Мы ищем приблизительно такое же значение, верно?», пусть это будет вам подсказкой. Часто еще случайно не указывают четвертый аргумент вообще. И это может приводить к совершенно разным последствиям. Первый случай, когда вы работаете с ВПР (VLOOKUP) как будто в нем всего 3 аргумента. То есть в формуле вы ставите только две точки с запятой. Например, формула выглядит так: =ВПР(«Иванов С.А.»;C3:D9;2)
или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:
=VLOOKUP(«Иванов С.А.»;C3:D9;2) .

В этом случае Excel решает, что надо использовать значение 4 аргумента по умолчанию и подставляет в качестве него ИСТИНА (TRUE). Тогда вы можете найти не Иванова С.А., а кого-то чья фамилия будет примерно похожа на его (смотри как работает ВПР (VLOOKUP) с приблизительным поиском). Второй случай, когда вы ставите третью точку с запятой, но не указываете сам аргумент ИСТИНА (TRUE) или ЛОЖЬ (FALSE). Например, формула выглядит так: =ВПР(350;C3:D8;2;)
или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:
=VLOOKUP(350;C3:D9;2;) . В этом случае ВПР (VLOOKUP) решит, что 4 аргумент равен нулю. А ноль ВПР (VLOOKUP) превратит в ЛОЖЬ (FALSE) и будет искать именно 350 в вашей таблице, а не ближайшее меньшее.

Наш совет, чтобы не допускать таких ошибок, всегда указывайте последний, четвертый, аргумент функции ВПР (VLOOKUP). И пишите его словом, не заменяйте его на цифры, так вашу формулу будет легче прочитать и понять, что же она делает.
2. Другая частая ошибка — это попытка применять ВПР (VLOOKUP), чтобы найти второе или более позднее совпадение во внутренней таблице. Например, есть таблица для ведения складского учета, в которой отражаются поступающие материалы и цены на них. Выглядеть она будет примерно так: При помощи ВПР (VLOOKUP) иногда пытаются вернуть цену в последнем поступлении. Например, сколько стоили гвозди в последней закупке. Но ВПР (VLOOKUP) вернет вам результат только для первого совпадения. Он не покажет вам данные о второй, третьей или более поздней строчке, только первое совпадение. Поэтому использовать ВПР (VLOOKUP) в такой задаче и в задачах складского учета нужно очень аккуратно. Да, в сочетании с еще несколькими функциями и промежуточными вычислениями, вы сможете получить нужный результат, но мы называем такие подходы «танцы с бубном».
3. Иногда неправильно указывают номер столбца (третий аргумент ВПР (VLOOKUP)). Номер столбца не может быть меньше 1 и не может быть больше, чем столбцов во внутренней таблице. Если номер столбца указан неверно, то ВПР (VLOOKUP) возвращает ошибку #ССЫЛКА! (#REF!). Когда вы видите такую ошибку, пересчитайте количество столбцов в таблице, которую вы указали в функции, и убедитесь, что это значение не меньше, чем номер столбца, указанный третьим аргументом функции ВПР (VLOOKUP).
Что происходит, когда ВПР (VLOOKUP) не находит значение?
В случаях, когда Excel , Google Sheets, LibreOffice, OpenOffice не может найти точное совпадение при 4 аргументе ЛОЖЬ (FALSE), ВПР (VLOOKUP) возвращает ошибку #Н/Д ( #N/A). Такую же ошибку ВПР (VLOOKUP) вернет, если вы используете приблизительный поиск и ваша таблица начинается со значения, которое больше искомого.

Как убрать ошибку #Н/Д ( #N/A)
Во-первых, проверьте адрес внутренней таблицы. Действительно искомое значение находится в первом столбце внутренней таблицы. Во-вторых, проверьте правильно ли вы указали тип интервального просмотра: ИСТИНА (TRUE) или ЛОЖЬ (FALSE). В-третьих, если вы все сделали правильно, но во внутренней таблице нет нужного значения, дополните формулу функцией ЕСЛИОШИБКА (IFERROR). Например, так: =ЕСЛИОШИБКА(ВПР(«Иванова А.О.»;C3:D9;2);»нет такого сотрудника»)
или для англоязычного Excel, Google Sheets, LibreOffice, OpenOffice:
=IFERROR(VLOOKUP(«Иванова А.О.»;C3:D9;2);»нет такого сотрудника») .
В этом случае Excel , Google Sheets, LibreOffice, OpenOffice вместо #Н/Д (#N/A) будут писать, что такого сотрудника нет. Это поможет сделать ваши расчеты более информативными и надежными.
Чем дополнить и заменить ВПР (VLOOKUP)?
Дополнить ВПР (VLOOKUP) можно функцией ЕСЛИОШИБКА (IFERROR) и функцией ПОИСКПОЗ (MATCH). В особо сложных случаях возможно использование в комбинации с функцией СМЕЩ (OFFSET). Основные варианты замены функции ВПР (VLOOKUP): функция ПРОСМОТР (LOOKUP), комбинация функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH), а также в некоторых случаях — функция ГПР (HLOOKUP), функция СУММЕСЛИ (SUMIF). При использовании функции ВПР (VLOOKUP) большую автоматизацию и надежность вашим файлам может добавить проверка данных и выпадающие списки, а также условное форматирование.
Быстрые ссылки на файлы-примеры: Пример 1 — применение ВПР с точным поиском
Пример 2 — применение ВПР с приблизительным поиском

Остались вопросы? Пишите нам в форму обратной связи и записывайтесь на интенсив по Excel или курс по функциям Excel.

Понравилась статья? Узнайте больше раньше других: заходите на нашу страницу в ВКонтакте и подписывайтесь на новости.

Желаем вам успешной работы!
Ваш Виктор Рыбцев и команда Учебного центра BRP ADVICE.

ВПР в Excel и Гугл Таблицах – функция VLOOKUP

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

ВПР в Excel
ВПР в Гугл Таблицах
Возможные ошибки новичков
Что почитать по теме
Подведем итоги

ВПР в Excel

Алгоритм работы функции:

Указываем, что искать (абсолютное значение или что-то в ячейке).
Указываем, где искать (некий диапазон в другой таблице).
Указываем, какое значение возвращать. Это – самая сильная сторона ВПР. Когда функция находит совпадение (например, фамилию работника), она возвращает не саму фамилию, а значение, которое находится в той же строке. То есть мы можем вернуть зарплату этого работника, его фамилию, дату рождения – в общем, все, что захотим, при условии, что все данные в одной строке принадлежат одному работнику (данные упорядочены).
Указываем точность совпадения. Эксель позволяет вернуть как точное совпадение, так и частичное. Рекомендуем всегда пользоваться точным совпадением, потому что «частичное совпадение» – вещь весьма расплывчатая, Эксель может взять вообще не те данные, которые вам на самом деле нужны. Как-то повлиять на алгоритм выбора простыми методами нельзя.

На практике это выглядит вот так: ВПР(B2;’Дневная потребность’!$A$3:$B$12;2;0), где:

B2 – что мы будем искать.
‘Дневная потребность’!$A$3:$B$12 – где мы будем искать. Указываем таблицу на другом листе.
2 – из какого столбца мы будем возвращать данные, когда найдем соответствие.
0 – используем точное соответствие (1 – использовать приблизительное, не рекомендуется).

Примеры

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

1. Поиск неизвестного в общей таблице.

Это простейшая задача научит быстро обнаруживать необходимые данные в больших таблицах.

Исходная информация:

Есть таблица в Excel с перечнем лекарственных препаратов, их производителем и стоимостью.

Задача: найти стоимость препарата Хепилор.

Решение состоит в последовательности следующих действий:

Выбор критерия: в ячейку В12 вводим название Лекарственного препарата «Хепилор».
Выбор массива: выбираем диапазон начала и конца таблицы, где должен осуществляться поиск: В3:D10.
Выбор номера столбца: указываем номер столбца, из которого должна быть считана информация в одной строке с названием препарата. В нашем примере это 3, т. к. столбец №1 расположен вне области нужного нам диапазона.
Ставим функцию «0» или «Ложь».

Аналогично можно произвести поиск производителя Хепилора. Для этого потребуется заменить номер столбца, где расположены необходимые данные, т. е. 3 на 2.

2. Пример расчета неизвестного показателя из исходных данных.

Предположим, что у нас есть приют для котиков. Нам нужно посчитать, сколько пакетиков корма нам нужно купить на завтра и сколько денег на это нужно. У нас есть 2 таблицы на разных страницах:

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

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

Нам нужно заполнить аргументы функции:

Искомое значение – это то значение, по которому мы будем искать. В нашем случае это «Вес кота», поэтому указываем B2. Таблица – это место, где мы будем искать. Нужно выбрать всю таблицу за исключением заголовка и номеров колонок.

Кроме того, нужно зафиксировать значения диапазона таблицы, чтобы она не «поехала», когда мы будем распространять формулу на другие ячейки основной таблицы. Для этого выделяем диапазон в конструкторе формулы и жмем F4.

Далее указываем номер столбца, из которого нужно брать данные. В нашем случае – второй столбец, поскольку в нем указано количество пакетиков. В «Интервальный просмотр» ставим 0, чтобы искать по точному соответствию.

Применяем – получаем результат. Растягиваем его на остальные ячейки.

Пакетик корма стоит 60 рублей, поэтому высчитываем стоимость как D2=C2*60 (и растягиваем на остальные пустые ячейки), в C9 пишем =СУММ(C2:C8), в D10 пишем =СУММ(D2:D8).

Получаем 14.5 пакетиков в день на сумму 870 рублей, по факту придется купить 15 на сумму 900 рублей, ибо половину пакетика никто не продаст.

3. Комбинирование таблиц с ВПР.

Исходные данные: имеем 2 таблицы.

«Отчет о количестве товара» и «Отчет о цене за единицу товара».

Задача: объединить данные двух таблиц.

Порядок действий:

Выбираем ячейку для вставления данных (D3) и пишем функцию: ВПР (В3;F3:G14;2;0), где:

выбор критерия:В3;
выбор диапазона: F3:G14;
№ столбца: 2;
стандартно: «0» или «Ложь».

Алгоритм решения:

Поиск совпадений с верхней ячейки первого столбца.
Поиск соответствия установленному критерию сверху вниз.
После того, как найден Хепилор, производится отсчет столбцов вправо.
ВПР выдает искомое значение, в нашем случае это цена – 86,90.

Чтобы в столбец D первой таблицы вставить данные не по одной строке, а в целом, нужно скопировать функцию до последнего критерия. Но, для избежания «съезжания» массива вниз, нужно использовать абсолютные ссылки для диапазона в ячейке D3. Для этого нужно выделить диапазон F3:G14 и нажать клавишу F4, далее завершить копирование формулы.

Итоговая таблица будет такая:

ВПР в Гугл Таблицах

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

Примеры с пошаговыми инструкциями

1. Пример. Осуществляем поиск данных из списка.

Дана таблица с именами и оценками учащихся.

Требуется быстро найти оценку конкретного ученика, например, Martha.

Алгоритм: =VLOOKUP(E2,$A$2:$B$10,2,False)

При введении в ячейку Е2 любого имени, в Е3 будет отображаться значения его оценки.

Это очень удобно, когда исходных данных много, таблицы большие, а информация нужна срочно.

2. Пример.

Создаем 2 таблицы – основную и ту, в которой мы будем искать информацию.

Выбираем первую ячейку, пишем =ВПР(. После этого Гугл предлагает нам ввести или выбрать данные. Данные разделяются знаком ;.

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

Вводим номер столбца и 0, чтобы искать точное совпадение. Распространяем формулу на остальные ячейки, считаем цену для каждого кота и общие суммы.

Возможные ошибки новичков

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

Что почитать по теме

Справка от Майкрософт.
Справка от Гугла.

FAQ

Какой результат выдаст функция, если найдет несколько вхождений в таблице, в которой мы ищем данные?

Функция вернет результат из первого найденного вхождения.

Можно ли писать внутри функции формулы?

Да, вы можете как написать формулу внутри одного из параметров функции, так и передать результаты работы функции в формулу. Например, =ECЛИ(ВПР(“Иванов”;’сотрудники’!$B$3:$B$203;3;0)=1;”Есть”;”Не найден”) будет писать «Есть», если такой сотрудник есть в базе (и в специальной колонке ему присвоено значение 1), и «Не найден» в противном случае.

Подведем итоги

ВПР позволяет вам вставить какие-то данные из другого листа (или с этого же, если они попадают под определенные критерии).
Чтобы написать функцию, вам нужно указать: какие данные нужно искать; где их искать; из какой колонки таблицы брать результат; искать ли по точному совпадению.
В Excel и Google Sheets – одинаковый синтаксис для ВПР, единственная разница – Excel позволяет создать функцию через мастера создания функций.