ВПР и ГПР в Excel и Google Sheets: как работают функции и как ими пользоваться

Привет!

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

В этой статье:

• Что такое ВПР простыми словами
• Наглядный пример и разбор ВПР по шагам
• Где и когда может потребоваться ВПР
• ГПР в Excel: тот же ВПР, только по горизонтали
• ВПР и ГПР в Google Sheets
• Типичные ошибки
• Бонус-раздел и частые вопросы (FAQ)

Поехали!

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

Вы буквально говорите Вите, равно как и функции ВПР:
1. «Найди мне Андрея Кузнецова».
2. «Посмотри вот в этом справочнике, где много информации про каждого».
3. «Когда найдешь его, скажи мне его оценку, но только её, а ничего больше».
4. «Найди мне именно Андрея Кузнецова, а не кого-то похожего».

Функция ВПР работает именно так: берет ваш запрос, ищет его в таблице-справочнике и, если находит, подтягивает что-то конкретное из найденных данных.

Теперь задача. Пример синтетический, но прозрачно показывает, как ВПР справится с делом. Официант кофейни принял заказ новых посетителей за столиком и записал его:

• Капучино
• Капучино
• Раф

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

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

Задача — с помощью ВПР вместо нуля в строке «Итого» получить точную сумму счета. Там уже стоит простая формула суммирования жёлтых ячеек из Заказа. Нужно только заполнить сами жёлтые ячейки.

Четыре аргумента — это те условные фразы помощнику Вите из предыдущей секции. Встаем на ячейку J7 и пишем формулу аргумент за аргументом:

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

Интервальный_просмотр во всех случаях один — 0, точное совпадение.

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

В сверке оплат полученный результат в виде #Н/Д будет не ошибкой, а ответом: оплаты по этому счету в выписке не найдены. Так ВПР быстро отделяет оплаченные счета от неоплаченных.

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

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

Отличие от ВПР одно — третий аргумент: вместо номера столбца указываем номер строки. Правила те же: искать можно только в первой строке таблицы, справочник лучше закрепить «якорями» $, а для точного совпадения надо ставить в конце 0.

ВПР и ГПР в Google Sheets работают точно так же, как и в Excel. Хоть названия аргументов отличаются, использование функции идентичное:

• первым аргументом запрос указать искомое значение;
• вторым аргументом диапазон подставить таблицу (лучше с якорями);
• третьим аргументом индекс указать относительный номер столбца или строки, из которого требуется вернуть значение;
• четвертым аргументом [отсортировано] почти всегда финансисту нужно указать 0.

Четвертый аргумент [отсортировано] в Google Sheets опициональный, о чем говорят квадратные скобки в синтаксисе. По умолчанию он установлен как 1 или ИСТИНА, если вы не указываете его явно. Будьте внимательны.

Подробное описание действия функций и их аргументов можно посмотреть в справке Google: ВПР (VLOOKUP) и ГПР (HLOOKUP).

Подробнее про #Н/Д. Обычно причина в одном из трех:

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

Там, где ошибку не устранить, можно обернуть ВПР в функцию ЕСЛИОШИБКА: =ЕСЛИОШИБКА(ВПР(...);0). Вместо нуля можно подставить любое значение или текст, который покажет, что что-то пошло не так. Что именно поставить, зависит от того, что вы планируете делать с результатом дальше: суммировать, фильтровать, сортировать. Среди типичных вариантов: 0, -1, пустой текст в виде двух двойных кавычек подряд без пробела ("") или, например, надпись «Не найдено».

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

• ПРОСМОТР (LOOKUP) — общий случай. Ищет элемент в одном массиве и возвращает значение с тем же порядковым номером из другого. Не ограничена первым столбцом справочника и умеет работать в обратную сторону: искать в правом столбце и возвращать значение из левого. Но у нее есть условие: просматриваемые значения должны быть отсортированы по возрастанию, иначе функция может молча вернуть неверное значение.
• ВПР (VLOOKUP) — вертикальный просмотр: ищет в первом столбце таблицы.
• ГПР (HLOOKUP) — «Горизонтальный ПРосмотр»: то же, что ВПР, только ищет в первой строке.
• ПРОСМОТРX (XLOOKUP) — более продвинутая функция в современных версиях Excel: ищет в любом направлении и помогает не ловить ошибки. Но о ней напишем в другом материале.

Как видите, ВПР — это не особо-то и сложно. Эта функция — основа упрощения и автоматизации в Excel, если вы работаете с отчетами, большим количеством таблиц и справочников. Потратьте несколько минут на практику: скачайте файл с примером выше и доведите заказ в кофейне до конца. Мы уверены, вы справитесь!

Удачи в работе с ВПР (и ГПР)!