Функция ГПР в Excel

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

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

Ниже коротко рассмотрена работа функции ГПР, её синтаксис (для ручного написания, а также понимания аргументов функции) и рассмотрен простой пример применения ГПР на практике.

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

[нажмите на картинку для увеличения]
Справка: как сохранять фото с сайтов

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

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

Обобщённый синтаксис формулы ГПР следующий:
ГПР(искомое_значение; таблица; номер_строки; [интервальный_просмотр])

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

• искомое_значение
  Адрес ячейки в первой таблице (где наша функция ГПР), которое нужно найти в аналогичной по содержанию строке второй таблицы. Вместо адреса ячейки можно указать текст или число, но обычно это не имеет значения, так как для разных ячеек искомое значение меняется. Это обязательный аргумент. В нашем примере для ячейки B5 искомым значением является наименование должности в ячейке B4.
• таблица
  Диапазон ячеек, в которых ищется совпадение по значению, указанное в аргументе 1. Диапазон должен быть указан таким образом, чтобы строка, в которой производится поиск совпадений, была первой (считая сверху вниз). Заголовки таблицы не нужно включать в диапазон. В нашем примере это $B8:$D9 (обратите внимание на символ доллара — он нужен чтобы диапазон оставался неизменным при копировании формулы в другие ячейки первой таблицы).
• номер_строки
  Порядковый номер строки в указанном диапазоне ячеек (параметр «таблица»), значение из которого будет являться результатом работы функции ГПР. Именно это значение будет возвращено функцией (вставлено в ячейку), если совпадение искомого значения найдено. В нашем примере для ячейки B5 это 2 (строки нумеруются начиная с 1 от верхнего края диапазона).
• интервальный_просмотр
  Значение 0 или 1. Число 0 означает поиск точного совпадения искомого значения; 1 — приблизительный. В примере мы ищем точное совпадение, поскольку нам требуется найти соответствие зарплаты по должности.

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

Как работает функция ГПР в Excel

Формула ГПР выполняет «горизонтальный» поиск в указанном диапазоне. Если в первой строке диапазона найдено совпадение с искомым значением, то функция возвращает значение из строки с указанным в аргументе 3 номером и вставляет его в ячейку с функцией.

Если совпадение не найдено, то ГПР возвращает значение «#Н/Д».

Почему не работает функция ГПР

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

• Неверно указан диапазон для поиска (в том числе стоит проверить, что диапазон не изменяется при копировании формулы в другие ячейки).
• Неверно указан номер столбца в диапазоне (например, столбца с таким номером нет или есть, но там не те данные).
• Не задан интервальный просмотр (вообще-то это не обязательный параметр, но практика показывает, что лучше его указывать явно).

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

В нашем примере для ячейки B5 во второй таблице будет найдено значение зарплаты по значению должности. Алгоритм поиска работает так:

• В ячейке B4 указана должность «Директор»;
• Функция ГПР просматривает таблицу 2 (нижнюю, см. скриншот) и в первой строке ищет слово «Директор»;
• Если в первой строке второй таблицы (там, где написаны должности) будет найдено совпадение, то функция вернёт значение зарплаты из второй строки таблицы.

Итого в результате для ячейки B5 результат работы ГПР будет такой: «40000»

Для функции ГПР важно правильно указать параметры в первой формуле перед её копированием, чтобы убедиться в том, что всё работает. И только потом скопировать функцию в другие ячейки!

ГПР в Excel, примеры

Показанный выше пример рассмотрен на видео. Также Вы можете скачать Excel файл с этим примером.

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

Комментарии по практическому применению ГПР в Эксель можно добавить после статьи. Также приветствуются Ваши примеры по применению данной формулы.