Функция гпр в экселе пошаговая инструкция

Функция гпр в экселе пошаговая инструкция

Добрый день читатель!

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

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

Теперь рассмотрим, как работает функция ГПР в Excel более пристально, и начнем эту процедуру с изучения синтаксиса функции:

=ГПР ( искомое нами значение; таблица поиска; номер строки; [интервальный просмотр]), где

  • Искомое нами значение – это то значение, поиском которого мы будем заниматься в первой строчке нашей таблицы (этим аргументом может быть текстовая строка, ссылка или любое значение);
  • Таблица поиска – это наша таблица, где мы будем производить поиск нужных данных (данный диапазон стоит закрепить абсолютными ссылками или же просто вместо адресов указать имя диапазона), аргументы в первой строчке могут быть логическими, простыми числами или текстовые;
  • Номер строки – мы должны указать номер той строчки в таблице, из которой функция вытянет определенное значение. То есть когда мы укажем аргумент 3. в таком случае будет возвращено значение с третьей строчки, а вот в случае, когда значение аргумента будет меньше 1, тогда функция ГПР будет получать значение ошибки #ЗНАЧ!, а если же аргумент будет больше, нежели строк в диапазоне будет возвращено значение ошибки #ССЫЛ!.
  • Интервальный просмотр – в случае кода этот аргумент имеет значение «ИСТИНА», тогда значения первой строки нужно располагать в порядке возрастания: 2, -1, 0, 1, 2, . A-Z, ЛОЖЬ, ИСТИНА, иначе функция ГПР выдаст неправильный результат. А вот если аргумент будет «ЛОЖЬ», то таблица скорее всего не отсортирована.

Обращаю ваше внимание, что регистр букв в текстовых значениях не учитывается, а также есть возможность использовать подстановочные знаки: знак вопроса (?), для замены любого одного знака и звёздочку (*), для замены любой очередности знаков. Если у вас есть необходимость найти один из вышеуказанных знаков, то перед ним вам нужно указать знак тильда (

Читайте также:  Как не попасть в бан инстаграм

А теперь рассмотрим на примере, как используется функция ГПР в Excel, что бы рассмотреть принцип работы этой функции. Итак, у нас есть таблица с видами мебели и их вариациями, вот на основе таких данных и будет экспериментировать: =ГПР(«Диван»;A1:C4;2;ИСТИНА), производим поиск слова «Диван» в строчке 1 и возвращает значение из строки 2, которая находится в том же столбике, результат получаем «Амур». =ГПР(«Кровать»;A1:C4;3;ЛОЖЬ), производим поиск в первой строке и возвращаем значение из 3 строчки, которая находится в том же столбце, в результате получаем «Марроко».

=ГПР(«К»;A1:C4;3;ИСТИНА), производим поиск буквы «К» в первой строке и возвращаем значение из 3 строчки того же столбца. Поскольку эту букву найти не удалось, вернулось ближайшее из меньших значений «Диван», результат получаем «Кром». =ГПР(«Спальня»;A1:C4;4), производим поиск слова «Спальня» в первой строке и возвращаем значение из 4 строчки, которая находится в том же столбике, наш результат будет «Касар». А на этом у меня всё! Я очень надеюсь, что всё о работе с функцией ГПР в Excel вам понятно. С другими функциями вы можете познакомится в «Справочнике функций». Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

Не забудьте поблагодарить автора!

"Деньги — это средство иметь буквально все, за исключением искреннего друга, преданной жены и здоровья.
"П. Вебер

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

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

Например, в таблице с полями «Имя» и «Дата рождения» необходимо получить значение даты рождения для сотрудника, запись о котором является третьей сверху. В этом случае удобно использовать следующую функцию: =ГПР("Дата рождения";A1:B10;4), где "Дата рождения" – наименование столбца таблицы, в котором будет выполнен поиск, A1:B10 – диапазон ячеек, в котором расположена таблица, 4 – номер строки, в которой содержится возвращаемое значение (поскольку таблица содержит шапку, номер строки равен номеру искомой записи +1.

Читайте также:  Как включить нижнюю панель в компасе

Пошаговые примеры работы функции ГПР в Excel

Пример 1. В таблице содержатся данные о клиента и их контактных номерах телефонов. Определить номер телефона клиента, id записи которого имеет значение 5.

Вид таблицы данных:

Для расчета используем формулу:

  • F1 – ячейка, содержащая название поля таблицы;
  • A1:C11 – диапазон ячеек, в которых содержится исходная таблица;
  • E2+1 – номер строки с возвращаемым значением (для – шестая строка, поскольку первая строка используется под шапку таблицы).

В ячейке F2 автоматически выводится значение соответствующие номеру id в исходной таблице.

ГПР для выборки по нескольких условиях в Excel

Пример 2. На основе таблицы из первого примера создать компактное представление таблицы, в которой по номеру id можно получить записи, хранящиеся в столбцах «Клиент» и «Телефон».

Создадим заготовку таблицы:

Для удобного использования в ячейке E2 создадим выпадающий список. Для этого выберите инструмент: «ДАННЫЕ»-«Работа с данными»-«Проверка данных».

В появившемся окне «Проверка вводимых значений» выберите из секции «Тип данных:» в выпадающем меню опцию «Список». А в поле «Источник:» укажите адрес ссылки на диапазон исходных ячеек первого столбца таблицы =$A$2:$A$11, как показано выше на рисунке.

Для выбора клиента используем следующую формулу в ячейке F2:

Для выбора номера телефона используем следующую формулу (с учетом возможного отсутствия записи) в ячейке G2:

Функция ЕСЛИ выполняет проверку возвращаемого значения. Если искомая ячейка не содержит данных, будет возвращена строка «Не указан».

Интерактивный отчет для анализа прибыли и убытков в Excel

Пример 3. В таблице хранятся данные о доходах и расходах мелкого предприятия по номерам месяцев. Создать форму для быстрого расчета абсолютного значения разницы доходов и расходов по номеру месяца, позволяющая определять, был ли закончен отчетный период с прибылью или убытком.

Вид таблиц данных с выпадающим списком в ячейке E2 (как сделать выпадающий список смотрите в примере выше):

В ячейку F2 запишем следующую формулу:

Функция ABS возвращает абсолютное число, равное разнице возвращаемых результатов функций ГПР.

В ячейке G2 запишем формулу:

Функция ЕСЛИ сравнивает возвращаемые функциями ГПР значения и возвращает один из вариантов текстовых строк.

Читайте также:  Что такое учетная запись в айфоне

Особенности использования функции ГПР в Excel

Функция имеет следующую синтаксическую запись:

=ГПР( искомое_значение;таблица;номер_строки; [интервальный_просмотр])

  • искомое_значение – обязательный аргумент, принимающий данные любого типа, характеризующие элемент первой строки константы массива или ячейку, расположенную в первой строке диапазона ячеек. При работе с именованными областями данных (списки, таблицы) в качестве данного аргумента указывают название поля (столбца), в котором содержится возвращаемое значение.
  • таблица – обязательный аргумент, принимающий константу массива или ссылку на диапазон ячеек, в котором находится список или таблица. Если первой строкой диапазона является шапка таблицы с текстовыми названиями полей, при поиске требуемого названия поля не учитывается регистр символов. Для корректной работы функции ГПР необходимо, чтобы содержащиеся в первой строке значения были отсортированы слева направо.
  • номер_строки – обязательный аргумент, характеризующийся числовым значением из диапазона целых положительных чисел, которое соответствует номеру строки относительно первой строки массива или диапазона ячеек, переданных в качестве аргумента таблица. Функция ГПР возвращает результат, взятый из строки таблицы с номером, указанным данным аргументом. Ошибки могут возникать в следующих случаях:
  1. В качестве аргумента номер_строки передано число Примечания:
  1. Если аргумент [интервальный_просмотр] явно не указан или имеет значение ИСТИНА, для получения корректных результатов работы функции ГПР первая строка таблицы или массива, переданных в качестве аргумента таблица, должна быть отсортирована по возрастанию значений слева направо. Для поиска точного совпадения (значение аргумента – ЛОЖЬ) сортировку можно не выполнять.
  2. Если все данные, хранящееся в первой строке массива или таблицы, превышают по значению данные, переданные в качестве аргумента искомое_значение, функция ГПР вернет код ошибки #Н/Д.
  3. При поиске точного соответствия (аргумент [интервальный_просмотр] = ЛОЖЬ) в таблицах с именованными столбцами (полями) можно использовать подстановочные знаки: «?» — замена одного символа, «*» — замена нескольких символов.

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

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

Что возвращает функция

Возвращает данные, которые вы хотите сопоставить по заданному значению из первой строки таблицы.

Синтаксис

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) – английская версия

=ГПР(искомое_значение;таблица;номер_строки;[интервальный_просмотр]) – русская версия

Ссылка на основную публикацию
Adblock detector