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