Гпр в 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. В крайнем случае строчка, в которой функция делает поиск, обязана быть отсортирована в порядке возрастания.
Ловушки 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 употребляется для поиска значения, обозначенного в качестве 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, как показано выше на рисунке.
Для выбора клиента используем последующую формулу в ячейке F2:
Для выбора номера телефона используем последующую формулу (с учетом вероятного отсутствия записи) в ячейке G2:
Функция ЕСЛИ делает проверку возвращаемого значения. Если разыскиваемая ячейка не содержит данных, будет возвращена строчка «Не указан».
Интерактивный отчет для анализа прибыли и убытков в Excel
Пример 3. В таблице хранятся данные о доходах и расходах маленького компании по номерам месяцев. Сделать форму для резвого расчета абсолютного значения различия доходов и расходов по номеру месяца, позволяющая определять, был ли закончен отчетный период с прибылью либо убытком.
Вид таблиц данных с выпадающим перечнем в ячейке E2 (как создать выпадающий перечень смотрите в примере выше):
В ячейку F2 запишем последующую формулу:
Функция ABS возвращает абсолютное число, равное разнице возвращаемых результатов функций ГПР.
В ячейке G2 запишем формулу:
Функция ЕСЛИ ассоциирует возвращаемые функциями ГПР значения и возвращает один из вариантов текстовых строк.
Комбинированные функции ВПР и ГПР — Excel и Гугл Таблицы
Время от времени для вас необходимо создать ссылку на столбец формулы ВПР динамической. Идеальнее всего применять функцию ВПР совместно с функцией ПОИСКПОЗ. См. Эту статью: Композиция ВПР и ПОИСКПОЗ — Excel и Гугл Таблицы, но сейчас мы покажем очередной нужный способ с композицией ВПР-ГПР.
1 | = ВПР (G3; B4: E6; ГПР (H3; B2: E3; 2; ЛОЖЬ); ЛОЖЬ) |
В этом примере наша формула возвращает количество Медь в Азия, беря во внимание материк и сплав.
Давайте поглядим, как скооперировать эти две пользующиеся популярностью функции.
Функция HLOOKUP
До этого всего, для вас нужна вспомогательная строчка с номерами столбцов (в большинстве случаев у вас ее не будет, но ее вправду просто сделать). Вы сможете применять функцию HLOOKUP, чтоб возвратить номер столбца, который вы желаете применять позднее в конечной формуле VLOOKUP.
1 | = HLOOKUP («Медь»; B2: E3,2; FALSE) |
Функция ВПР
Получив номер столбца, возвращенный функцией HLOOKUP, вы сможете применять его в формуле VLOOKUP в качестве аргумента col_index_num.
1 | = ВПР (значение_просмотра, массив_таблиц, номер_столбца, [поиск_диапазона]) |
1 | = ВПР (G3; B4: E6; J3; ЛОЖЬ) |
ВПР и HLOOKUP объединены в Гугл Таблицах
Все приведенные выше примеры работают в таблицах Гугл так же, как и в Excel.