Гпр в excel что это

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

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

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

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

Синтаксис

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

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

10-ый денек марафона 30 функций Excel за 30 дней мы посвятим исследованию функции HLOOKUP (ГПР). Эта функция весьма похожа на VLOOKUP (ВПР), лишь она работает с элементами горизонтального перечня.

Злосчастная функция HLOOKUP (ГПР) не так популярна, как её сестра, так как в большинстве случаев данные в таблицах размещены вертикально. Вспомяните, когда в крайний раз Вы желали выполнить поиск по строке? А возвратить значение из такого же столбца, но расположенное в одной из строк ниже?

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

Функция 10: HLOOKUP (ГПР)

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

Как можно применять функцию HLOOKUP (ГПР)?

Так как функция HLOOKUP (ГПР) может отыскать четкое либо приближенное значение в строке, то она сумеет:

Отыскать итоги продаж по избранному региону.
Отыскать показатель, животрепещущий для избранной даты.

Синтаксис HLOOKUP (ГПР)

Функция HLOOKUP (ГПР) имеет последующий синтаксис:

lookup_value (искомое_значение): значение, которое требуется отыскать. Быть может значением либо ссылкой на ячейку.
table_array (таблица): таблица поиска. Быть может ссылкой на спектр либо именованным спектром, содержащим 2 строчки либо наиболее.
row_index_num (номер_строки): строчка, содержащая значение, которое обязано быть возвращено функцией. Задается номером строчки снутри таблицы.
range_lookup (интервальный_просмотр): для поиска четкого совпадения используйте FALSE (ЛОЖЬ) либо 0; для ориентировочного поиска – TRUE (ИСТИНА) либо 1. В крайнем случае строчка, в которой функция делает поиск, обязана быть отсортирована в порядке возрастания.

Интересно почитать: Как в excel страницы объединить в одну

Ловушки HLOOKUP (ГПР)

Как и VLOOKUP (ВПР), функция HLOOKUP (ГПР) может работать медлительно, в особенности когда делает поиск четкого совпадения текстовой строчки в несортированной таблице. По мере способности, используйте ориентировочный поиск в таблице, отсортированной по первой строке по возрастанию. Вы сможете поначалу применить функцию MATCH (ПОИСКПОЗ) либо COUNTIF (СЧЁТЕСЛИ), чтоб убедиться, что разыскиваемое значение совершенно существует в первой строке.

Остальные функции, такие как INDEX (ИНДЕКС) и MATCH (ПОИСКПОЗ), могут быть также применены для извлечения значений из таблицы, и они наиболее эффективны. Мы разглядим их позднее в рамках нашего марафона и увидим, как сильны и гибки они могут быть.

Пример 1: Отыскать значения продаж для избранного региона

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

Имя региона введено в ячейке B7.
Таблица поиска по региону имеет две строчки и занимает спектр C2:F3.
Итоги продаж находятся в строке 2 нашей таблицы.
Крайний аргумент имеет значение FALSE (ЛОЖЬ), чтоб отыскать четкое совпадение при поиске.

Формула в ячейке C7 таковая:

Если заглавие региона в первой строке таблицы не найдено, результатом функции HLOOKUP (ГПР) будет #N/A (#Н/Д).

Пример 2: Отыскать показатель для избранной даты

Обычно при использовании функции HLOOKUP (ГПР) требуется четкое совпадение, но время от времени ориентировочное совпадение подступает больше. К примеру, если характеристики изменяются в начале всякого квартала, а в качестве заголовков столбцов употребляются 1-ые деньки этих кварталов (см. набросок ниже). В таком случае, при помощи функции HLOOKUP (ГПР) и ориентировочного соответствия, Вы найдёте показатель, который животрепещущ для данной даты. В этом примере:

Дата записана в ячейке C5.
Таблица поиска показателя имеет две строчки и размещена в спектре C2:F3.
Таблица поиска отсортирована по строке с датами по возрастанию.
Характеристики записаны в строке 2 нашей таблицы.
Крайний аргумент функции имеет значение TRUE (ИСТИНА), чтоб находить приближенное совпадение.

Формула в ячейке D5 таковая:

Если дата не найдена в первой строке таблицы, функция HLOOKUP (ГПР) отыщет наиблежайшее наибольшее значение, которое меньше аргумента lookup_value (искомое_значение). В данном примере разыскиваемое значение – 15 марта. Его в строке с датами нет, потому формула возьмет значение 1 января и возвратит 0,25.

Интересно почитать: Excel примечание нельзя добавить в режиме совместного доступа

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Интересно почитать: Как в excel начертить таблицу

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

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

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

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

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

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

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

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

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

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

Комбинированные функции ВПР и ГПР — Excel и Гугл Таблицы

Время от времени для вас необходимо создать ссылку на столбец формулы ВПР динамической. Идеальнее всего применять функцию ВПР совместно с функцией ПОИСКПОЗ. См. Эту статью: Композиция ВПР и ПОИСКПОЗ — Excel и Гугл Таблицы, но сейчас мы покажем очередной нужный способ с композицией ВПР-ГПР.

1	= ВПР (G3; B4: E6; ГПР (H3; B2: E3; 2; ЛОЖЬ); ЛОЖЬ)

В этом примере наша формула возвращает количество Медь в Азия, беря во внимание материк и сплав.

Давайте поглядим, как скооперировать эти две пользующиеся популярностью функции.

Функция HLOOKUP

До этого всего, для вас нужна вспомогательная строчка с номерами столбцов (в большинстве случаев у вас ее не будет, но ее вправду просто сделать). Вы сможете применять функцию HLOOKUP, чтоб возвратить номер столбца, который вы желаете применять позднее в конечной формуле VLOOKUP.