Как использовать гпр в excel
Функция ГПР в Excel
Добрый день читатель!
В этой статье мы изучим еще одну функцию с раздела «Массивы и ссылки», это функция ГПР в Excel. Эта функция является зеркальным отражением функции ВПР, хотя и используется в работе не так часто, хотя в некоторых случаях обойтись без этой функции невозможно.
Если вы знаете принцип работы функции ВПР, то можете догадаться что функция ГПР, отличается только диапазоном поиска, «Г» — означает горизонтальный, а значит и поиск будет горизонтальным. Функция ГПР производит поиск нужных значений в первой строчке вашей таблицы (или же диапазона данных который вы поименовали) и возвращает значение, которое находится в указанной строке таблицы в том же столбике. То есть функцию ГПР нужно использовать в том случае, когда значение, которые нам нужно сравнить располагаются в первой строчке вашей таблицы данных, а значение, которые нужно возвращать находятся в строках ниже. Как видите с описания, поиск идет по строкам, то есть в горизонтальной плоскости.
Теперь рассмотрим, как работает функция ГПР в Excel более пристально, и начнем эту процедуру с изучения синтаксиса функции:
=ГПР ( искомое нами значение; таблица поиска; номер строки; [интервальный просмотр]), где
Обращаю ваше внимание, что регистр букв в текстовых значениях не учитывается, а также есть возможность использовать подстановочные знаки: знак вопроса (?), для замены любого одного знака и звёздочку (*), для замены любой очередности знаков. Если у вас есть необходимость найти один из вышеуказанных знаков, то перед ним вам нужно указать знак тильда (
А теперь рассмотрим на примере, как используется функция ГПР в Excel, что бы рассмотреть принцип работы этой функции. Итак, у нас есть таблица с видами мебели и их вариациями, вот на основе таких данных и будет экспериментировать: 
=ГПР(«Диван»;A1:C4;2;ИСТИНА), производим поиск слова «Диван» в строчке 1 и возвращает значение из строки 2, которая находится в том же столбике, результат получаем «Амур». 
=ГПР(«Кровать»;A1:C4;3;ЛОЖЬ), производим поиск в первой строке и возвращаем значение из 3 строчки, которая находится в том же столбце, в результате получаем «Марроко». 
=ГПР(«К»;A1:C4;3;ИСТИНА), производим поиск буквы «К» в первой строке и возвращаем значение из 3 строчки того же столбца. Поскольку эту букву найти не удалось, вернулось ближайшее из меньших значений «Диван», результат получаем «Кром».
=ГПР(«Спальня»;A1:C4;4), производим поиск слова «Спальня» в первой строке и возвращаем значение из 4 строчки, которая находится в том же столбике, наш результат будет «Касар». 
А на этом у меня всё! Я очень надеюсь, что всё о работе с функцией ГПР в Excel вам понятно. С другими функциями вы можете познакомится в «Справочнике функций». Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!
Не забудьте поблагодарить автора!
«Деньги — это средство иметь буквально все, за исключением искреннего друга, преданной жены и здоровья.
«П. Вебер
ГПР (функция ГПР)
В этой статье описаны синтаксис формулы и использование функции ГПР в Microsoft Excel.
Описание
Выполняет поиск значения в первой строке таблицы или массив значений и возвращает значение, находящееся в том же столбце в заданной строке таблицы или массива. Функция ГПР используется, когда сравниваемые значения расположены в первой строке таблицы данных, а возвращаемые — на несколько строк ниже. Если сравниваемые значения находятся в столбце слева от искомых данных, используйте функцию ВПР.
Буква Г в аббревиатуре «ГПР» означает «горизонтальный».
Синтаксис
Аргументы функции ГПР описаны ниже.
Искомое_значение — обязательный аргумент. Значение, которое требуется найти в первой строке таблицы. «Искомое_значение» может быть значением, ссылкой или текстовой строкой.
Таблица — обязательный аргумент. Таблица, в которой производится поиск данных. Можно использовать ссылку на диапазон или имя диапазона.
Значения в первой строке аргумента «таблица» могут быть текстом, числами или логическими значениями.
В текстовых строках регистр букв не учитывается.
Значения сортируются слева направо по возрастанию. Дополнительные сведения см. в разделе Сортировка данных в диапазоне или таблице.
Номер_строки — обязательный аргумент. Номер строки в аргументе «таблица», из которой будет возвращено соответствующее значение. Если значение аргумента «номер_строки» равно 1, возвращается значение из первой строки аргумента «таблица», если оно равно 2 — из второй строки и т. д. Если значение аргумента «номер_строки» меньше 1, функция ГПР возвращает значение ошибки #ЗНАЧ!; если оно больше, чем количество строк в аргументе «таблица», возвращается значение ошибки #ССЫЛ!.
Интервальный_просмотр — необязательный аргумент. Логическое значение, которое определяет, какое соответствие должна искать функция ГПР — точное или приблизительное. Если этот аргумент имеет значение ИСТИНА или опущен, возвращается приблизительное соответствие; при отсутствии точного соответствия возвращается наибольшее из значений, меньших, чем «искомое_значение». Если этот аргумент имеет значение ЛОЖЬ, функция ГПР ищет точное соответствие. Если найти его не удается, возвращается значение ошибки #Н/Д.
Примечание
Если функция ГПР не может найти «искомое_значение» и аргумент «интервальный_просмотр» имеет значение ИСТИНА, используется наибольшее из значений, меньших, чем «искомое_значение».
Если значение аргумента «искомое_значение» меньше, чем наименьшее значение в первой строке аргумента «таблица», функция ГПР возвращает значение ошибки #Н/Д.
Если аргумент «интервальный_просмотр» имеет значение ЛОЖЬ и аргумент «искомое_значение» является текстом, в аргументе «искомое_значение» можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому одному знаку; звездочка — любой последовательности знаков. Чтобы найти какой-либо из самих этих знаков, следует указать перед ним знак тильды (
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Примеры функции ГПР в Excel пошаговая инструкция для чайников
Функция ГПР в Excel используется для поиска значения, указанного в качестве одного из ее аргументов, которое содержится в просматриваемом массиве или диапазоне ячеек, и возвращает соответствующее значение из ячейки, расположенной в том же столбце, на несколько строк ниже (число строк определяется в качестве третьего аргумента функции).
Функция ГПР схожа с функцией ВПР по принципу работы, а также своей синтаксической записью, и отличается направлением поиска в диапазоне (построчный, то есть горизонтальный поиск).
Например, в таблице с полями «Имя» и «Дата рождения» необходимо получить значение даты рождения для сотрудника, запись о котором является третьей сверху. В этом случае удобно использовать следующую функцию: =ГПР(«Дата рождения»;A1:B10;4), где «Дата рождения» – наименование столбца таблицы, в котором будет выполнен поиск, A1:B10 – диапазон ячеек, в котором расположена таблица, 4 – номер строки, в которой содержится возвращаемое значение (поскольку таблица содержит шапку, номер строки равен номеру искомой записи +1.
Пошаговые примеры работы функции ГПР в Excel
Пример 1. В таблице содержатся данные о клиента и их контактных номерах телефонов. Определить номер телефона клиента, id записи которого имеет значение 5.
Вид таблицы данных:
Для расчета используем формулу:
В ячейке F2 автоматически выводится значение соответствующие номеру id в исходной таблице.
ГПР для выборки по нескольких условиях в Excel
Пример 2. На основе таблицы из первого примера создать компактное представление таблицы, в которой по номеру id можно получить записи, хранящиеся в столбцах «Клиент» и «Телефон».
Создадим заготовку таблицы:
Для удобного использования в ячейке E2 создадим выпадающий список. Для этого выберите инструмент: «ДАННЫЕ»-«Работа с данными»-«Проверка данных».
В появившемся окне «Проверка вводимых значений» выберите из секции «Тип данных:» в выпадающем меню опцию «Список». А в поле «Источник:» укажите адрес ссылки на диапазон исходных ячеек первого столбца таблицы =$A$2:$A$11, как показано выше на рисунке.
Для выбора клиента используем следующую формулу в ячейке F2:
Для выбора номера телефона используем следующую формулу (с учетом возможного отсутствия записи) в ячейке G2:
Функция ЕСЛИ выполняет проверку возвращаемого значения. Если искомая ячейка не содержит данных, будет возвращена строка «Не указан».
Интерактивный отчет для анализа прибыли и убытков в Excel
Пример 3. В таблице хранятся данные о доходах и расходах мелкого предприятия по номерам месяцев. Создать форму для быстрого расчета абсолютного значения разницы доходов и расходов по номеру месяца, позволяющая определять, был ли закончен отчетный период с прибылью или убытком.
Вид таблиц данных с выпадающим списком в ячейке E2 (как сделать выпадающий список смотрите в примере выше):
В ячейку F2 запишем следующую формулу:
Функция ABS возвращает абсолютное число, равное разнице возвращаемых результатов функций ГПР.
В ячейке G2 запишем формулу:
Функция ЕСЛИ сравнивает возвращаемые функциями ГПР значения и возвращает один из вариантов текстовых строк.
Особенности использования функции ГПР в Excel
Функция имеет следующую синтаксическую запись:
=ГПР( искомое_значение;таблица;номер_строки; [интервальный_просмотр])
Функция ГПР в Excel — полная инструкция по использованию с примерами
Функция ГПР – подробное описание
Допустим, у нас есть электронная таблица, в которой есть две колонки с названиями «Имя» и «Дата рождения». Перед нами стоит задача, основываясь на этих данных, получить дату рождения третьего по счету сверху сотрудника. В таком случае синтаксис будет таким:
Естественно, вам нужно поставить свое название столбцов, колонок и так далее. Единственное, что нужно, так это оставить последний аргумент, если необходимо осуществить поиск определенного по счету сотрудника.
Синтаксис функции ГПР
=ГПР(искомое_значение, таблица, номер_строки, [интервальный_просмотр])
Давайте рассмотрим его более подробно: по аргументам, а также рассмотрим некоторые нюансы при использовании этой функции.
Аргументы
Давайте более детально рассмотрим каждый из этих аргументов:
Замечания
При работе с функцией ГПР необходимо обратить внимание на следующие нюансы:
Особенности использования функции ГПР
На самом деле, каждая функция поиска уникальная и предназначена для выполнения разных задач. То есть, функции ГПР и ВПР не являются единственными. Давайте более детально рассмотрим вообще, какие функции поиска существуют и сравним их между собой. Все они похожи по предназначению, но могут отличаться по синтаксису и возможностям, которые дают. Также они могут сочетаться в различных комбинациях, давая похожий функционал, но с некоторыми особенностями.
Детальное рассмотрение каждой из этих функций – это тема отдельной статьи. Но в целом, мы видим, что каждая из похожих на первый взгляд функций на самом деле заслуживает нахождения в совсем другой нише.
ГПР для выборки по нескольким условиям
Предположим, у нас есть такая прекрасная табличка, и нам нужно получить данные о клиенте и телефоне, исходя из его айди.
Чтобы упростить использование функции, давайте в ячейке E2 создадим выпадающий список. Чтобы это сделать, необходимо воспользоваться инструментом «Работа с данными – Проверка данных». Его можно найти на вкладке «Данные».
2
Далее нам нужно сделать список в соответствующих местах (там, где будет указываться айди клиента). Для этого нужно в перечне с типами данных найти пункт «Список». А в поле, подписанном как «Источник» пропишите диапазон адресов ячеек первого столбца.
3
Как мы видим, в колонке, где описываются клиенты, была прописана следующая формула:
С ее помощью программа автоматически выдает клиента, основываясь на его идентификационном номере.
Чтобы осуществить поиск номера телефона клиента, базируясь на информации о его айди, можно использовать эту формулу. Как вы можете увидеть, она позволяет не просто определить номер телефона, но и проверить, указан ли он в базе.
Давайте более подробно распишем эту формулу. С помощью функции ЕСЛИ мы осуществляем проверку значения, которое возвращается в первую ячейку (ту, в которую записывается идентификатор). Если оказывается, что номера нет, то возвращается сообщение, что клиент его не указал.
Вот некоторые простые примеры, которые можно использовать для большей наглядности.
4 
5
Интерактивный отчет для анализа прибыли и убытков в Эксель
Предположим, у нас есть таблица, содержащая информацию о доходах и расходах за разные месяцы. Перед нами руководство поставило задачу добавить в этот документ небольшую форму, с помощью которой можно определить сальдо торгового баланса. Простыми словами, мы получили прибыль или убытки за этот период.
Нами была создана таблица, где номер месяца можно выбрать с помощью выпадающего списка. Более подробно узнать, как создавать выпадающий список, можно в инструкции выше.
6
После этого записываем в ячейку F2 следующую последовательность команд (она отображается на скриншоте в строке формул).
7
Теперь давайте запишем формулу со скриншота ниже.
8
Примеры использования функции ГПР
Давайте для наглядности приведем еще один пример, немного проще. Предположим, перед нами поставили задачу найти номер клиента, пронумерованного цифрой 5. Выглядит эта таблица с исходными данными, которые будут использоваться в вычислениях, следующим образом.
9
Далее используем нашу формулу с аргументами, приведенными на этом скриншоте.
10
Давайте более детально распишем, что каждый из приведенных аргументов означает.
В результате, у нас получается такая таблица. Видим, что на этом примере номер айди – это ячейка F2. То есть, результат, соответствующий номеру этого клиента, выводится в эту ячейку.
11
Таким образом, возможностей для поиска нужных значений в программе Excel действительно огромное количество. И с каждой новой версией офисного пакета этот набор только увеличивается. Что нас ждет дальше? Неизвестно. Но точно нужно быть уверенным, что все будет отлично. Ведь как говорят эксперты по финансам, Excel и Powerpoint – две самые популярные и эффективные программы для того, чтобы генерировать деньги. Если правильно научиться пользоваться электронными таблицами, то специалист будет невероятно востребованным, независимо от сферы, в которой он работает.
Функции ВПР и ГПР в Excel с примерами их использования
Функции ВПР и ГПР среди пользователей Excel очень популярны. Первая применяется для вертикального анализа, сопоставления. То есть используется, когда информация сосредоточена в столбцах.
ГПР, соответственно, для горизонтального. Так как в таблицах редко строк больше, чем столбцов, функцию эту вызывают нечасто.
Синтаксис функций ВПР и ГПР
Функции имеют 4 аргумента:
! Если значения в диапазоне отсортированы в возрастающем порядке (либо по алфавиту), мы указываем ИСТИНА/1. В противном случае – ЛОЖЬ/0.
Как пользоваться функцией ВПР в Excel: примеры
Для учебных целей возьмем таблицу с данными:
| Формула | Описание | Результат |
| Функция ищет значение ячейки F5 в диапазоне А2:С10 и возвращает значение ячейки F5, найденное в 3 столбце, точное совпадение. |  | |
| Нам нужно найти, продавались ли 04.08.15 бананы. Если продавались, в соответствующей ячейке появится слово «Найдено». Нет – «Не найдено». |  | |
| Если «бананы» сменить на «груши», результат будет «Найдено» |  | |
| Когда функция ВПР не может найти значение, она выдает сообщение об ошибке #Н/Д. Чтобы этого избежать, используем функцию ЕСЛИОШИБКА. |  Мы узнаем, были ли продажи 05.08.15 | |
| Если необходимо осуществить поиск значения в другой книге Excel, то при заполнении аргумента «таблица» переходим в другую книгу и выделяем нужный диапазон с данными. |  Мы захотели узнать, кто работал 8.06.15. | |
| Поиск приблизительного значения. |  |
Как пользоваться функцией ГПР в Excel: примеры
Для учебных целей возьмем такую табличку:
