Функции ВПР и ГПР в Excel с примерами их использования

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

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

Синтаксис функций ВПР и ГПР

Функции имеют 4 аргумента:

ЧТО ищем – искомый параметр (цифры и/или текст) либо ссылка на ячейку с искомым значением;
ГДЕ ищем – массив данных, где будет производиться поиск (для ВПР – поиск значения осуществляется в ПЕРВОМ столбце таблицы; для ГПР – в ПЕРВОЙ строке);
НОМЕР столбца/строки – откуда именно возвращается соответствующее значение (1 – из первого столбца или первой строки, 2 – из второго и т.д.);
ИНТЕРВАЛЬНЫЙ ПРОСМОТР – точное или приблизительное значение должна найти функция (ЛОЖЬ/0 – точное; ИСТИНА/1/не указано – приблизительное).

! Если значения в диапазоне отсортированы в возрастающем порядке (либо по алфавиту), мы указываем ИСТИНА/1. В противном случае – ЛОЖЬ/0.

Как пользоваться функцией ВПР в Excel: примеры

Для учебных целей возьмем таблицу с данными:

Формула	Описание	Результат
	Функция ищет значение ячейки F5 в диапазоне А2:С10 и возвращает значение ячейки F5, найденное в 3 столбце, точное совпадение.
	Нам нужно найти, продавались ли 04.08.15 бананы. Если продавались, в соответствующей ячейке появится слово «Найдено». Нет – «Не найдено».
	Если «бананы» сменить на «груши», результат будет «Найдено»
	Когда функция ВПР не может найти значение, она выдает сообщение об ошибке #Н/Д. Чтобы этого избежать, используем функцию ЕСЛИОШИБКА.	Мы узнаем, были ли продажи 05.08.15
	Если необходимо осуществить поиск значения в другой книге Excel, то при заполнении аргумента «таблица» переходим в другую книгу и выделяем нужный диапазон с данными.	Мы захотели узнать, кто работал 8.06.15.
	Поиск приблизительного значения.

Это важно:

Функция ВПР всегда ищет данные в крайнем левом столбце таблицы со значениями.
Регистр не учитывается: маленькие и большие буквы для Excel одинаковы.
Если искомое меньше, чем минимальное значение в массиве, программа выдаст ошибку #Н/Д.
Если задать номер столбца 0, функция покажет #ЗНАЧ. Если третий аргумент больше числа столбцов в таблице – #ССЫЛКА.
Чтобы при копировании сохранялся правильный массив, применяем абсолютные ссылки (клавиша F4).

Как пользоваться функцией ГПР в Excel: примеры

Для учебных целей возьмем такую табличку:

Формула	Описание	Результат
	Поиск значения ячейки I16 и возврат значения из третьей строки того же столбца.
	Еще один пример поиска точного совпадения в другой табличке.

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

Символы подстановки в функциях ВПР и ГПР

Случается, пользователь не помнит точного названия. Задавая искомое значение, он может применить символы подстановки:

«?» - заменяет любой символ в текстовой или цифровой информации;
«*» - для замены любой последовательности символов.

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

Нам нужно отыскать название компании, которое заканчивается на - "uda". Поможет следующая формула: .

Найдем компанию, название которой начинается на "Ce" и заканчивается на –"sef". Формула ВПР будет выглядеть так: .

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

Как сравнить листы с помощью ВПР и ГПР

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

Как сравнить листы с помощью ВПР в Excel?

Решим проблему 1: сравним наименования товаров в январе и феврале. Так как в феврале их больше, вводить формулу будем на листе «Февраль».

Решим проблему 2: сравним продажи по позициям в январе и феврале. Используем следующую формулу:

Как сравнить листы с помощью ГПР в Excel?

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

Задача – сравнить продажи по позициям за январь и февраль.

Создаем новый лист «Сравнение». Это не обязательное условие. Сопоставлять данные и отображать разницу можно на любом листе («Январь» или «Февраль»).

Формула:

Результат:

Проанализируем части формулы:

«Половина» до знака «-»:

. Искомое значение – первая ячейка в таблице для сравнения. Анализируемый диапазон – таблица с продажами за февраль. Функция ГПР «берет» данные из 2 строки в «точном» воспроизведении.

После знака «-»:

. Все то же самое. Кроме диапазона. Здесь берется таблица с продажами за январь.

Скачать примеры использования функций ВПР и ГПР

Когда мы вводим формулу, Excel подсказывает, какой сейчас аргумент нужно ввести.