30 функций Excel за 30 дней: ГПР (HLOOKUP)

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

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

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

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

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

Как можно использовать функцию HLOOKUP (ГПР)?

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

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

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

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

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
ГПР(искомое_значение;таблица;номер_строки;интервальный_просмотр)

• 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(B7,C2:F3,2,FALSE)
=ГПР(B7;C2:F3;2;ЛОЖЬ)

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

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

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

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

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

=HLOOKUP(C5,C2:F3,2,TRUE)
=ГПР(C5;C2:F3;2;ИСТИНА)

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