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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. Найдем текст, который начинается или заканчивается определенным набором символов. Предположим, нам нужно отыскать название компании. Мы забыли его, но помним, что начинается с Kol. С задачей справится следующая формула: .
2. Нам нужно отыскать название компании, которое заканчивается на - "uda". Поможет следующая формула: .
3. Найдем компанию, название которой начинается на "Ce" и заканчивается на –"sef". Формула ВПР будет выглядеть так: .

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

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

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

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

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

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

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

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

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

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

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

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

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