Примеры функции ГПР

В Microsoft Excel доступно три функции для поиска информации: LOOKUP, VLOOKUP и HLOOKUP.

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

В данном руководстве основное внимание уделено функции HLOOKUP, а также полезным примерам её использования в Excel.

Что представляет собой функция HLOOKUP?

HLOOKUP — это функция для горизонтального поиска данных в таблице Excel.

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

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

HLOOKUP доступен во всех версиях Excel, начиная с более старых версий, таких как Excel 2007, до последних выпусков, включая Excel 2016 и новее.

Как работает HLOOKUP и какие аргументы использует?

HLOOKUP принимает четыре аргумента:

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

1. Lookup_value (обязательный аргумент): это то значение, которое вы хотите найти в первой строке.
2. Table_array (обязательный аргумент): это диапазон данных, где производится поиск.
3. Row_index_num (обязательный аргумент): указывает, из какого ряда нужно извлечь данные.
4. Range_lookup (необязательный аргумент): позволяет задать, какой тип совпадения нужен.

Для лучшего понимания, как работает HLOOKUP, представьте, что у вас есть таблица с информацией о планетах.

Если нужно найти диаметр конкретной планеты, чье имя введено в ячейку B5, HLOOKUP вернёт данные с учетом горизонтального поиска.

Пример формулы HLOOKUP:

Допустим, вам нужно найти значение для планеты, указанной в ячейке B5.

Вот как будет выглядеть формула:

=HLOOKUP(B5; B2:I3; 2; FALSE)

Здесь:

1. B5 — планета, которую нужно найти.
2. B2:I3 — диапазон данных, где идет поиск.
3. 2 — строка, из которой нужно извлечь значение.
4. FALSE — точное совпадение.

Важные моменты при использовании HLOOKUP

При работе с функцией HLOOKUP, следует учитывать несколько нюансов:

1. HLOOKUP ищет данные только в первой строке.
2. Функция не чувствительна к регистру: Excel не различает строчные и заглавные буквы.
3. При использовании приблизительного поиска (TRUE) данные в первой строке должны быть отсортированы по возрастанию.

В чём отличие между HLOOKUP и VLOOKUP?

Основное различие между этими двумя функциями заключается в направлении поиска.

HLOOKUP выполняет поиск по горизонтали, начиная с первой строки, тогда как VLOOKUP ищет данные по вертикали, начиная с первого столбца.

Это разница в направлении делает их применимыми для разных задач.

Дополнительные примеры и рекомендации

Теперь, когда основные аспекты HLOOKUP вам известны, можно рассмотреть более сложные примеры.

Например, с помощью HLOOKUP можно осуществить поиск данных на другом листе, использовать подстановочные знаки для частичного совпадения или применять INDEX и MATCH для замены.

Горизонтальный поиск с точным и приближенным совпадением значений

Функция HLOOKUP в Excel предназначена для поиска значения в строках таблицы.

В зависимости от параметра range_lookup, она может выполнять поиск как с точным совпадением, так и с приблизительным.

Этот параметр может принимать два значения:

1. Если указан TRUE или параметр пропущен, производится поиск ближайшего совпадения (приближенного).
2. Если указано FALSE, поиск будет производиться только с точным совпадением значения.

Хотя HLOOKUP ориентирован на поиск приближенных значений, сначала он всегда старается найти точное совпадение.

Если этого не удается, то при TRUE в качестве параметра формула найдет ближайшее значение, которое меньше или равно искомому.

В случае FALSE и отсутствия точного совпадения Excel вернет ошибку #N/A.

Для лучшего понимания, рассмотрим примеры использования функции HLOOKUP с разными типами совпадений.

HLOOKUP с приблизительным совпадением

Предположим, у вас есть данные о планетах во 2-й строке (B2:I2), и их средние температуры расположены в 1-й строке (B1:I1).

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

Когда точное значение температуры неизвестно, можно получить ближайшее совпадение.

Например, чтобы найти планету с температурой, близкой к -340 °F, примените формулу с range_lookup по умолчанию (или с TRUE):

=HLOOKUP(B4; B1:I2; 2)

При этом следует помнить, что значения в первой строке (температуры) должны быть отсортированы от меньшего к большему.

Если порядок не соблюдается, формула HLOOKUP может вернуть ошибочный результат.

HLOOKUP с точным совпадением

Если вы точно знаете значение, которое ищете, задайте range_lookup как FALSE для поиска только точного совпадения:

=HLOOKUP(B4; B1:I2; 2; FALSE)

Преимущество этого подхода — отсутствие необходимости сортировать данные в первой строке, что делает его более гибким в использовании.

Однако, если точное совпадение отсутствует, формула вернет #N/A.

Совет: Чтобы сделать вывод более дружелюбным для пользователя и избежать ошибок #N/A, примените формулу IFERROR.

Например, замените ошибку своим сообщением:

=IFERROR(HLOOKUP(B4; B1:I2; 2; FALSE), "Значение не найдено")

Использование HLOOKUP для поиска на других листах или в рабочих книгах

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

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

Пример:

=HLOOKUP(B$1; Diameters!$B$1:$I$2; 2; FALSE)

Если имя листа включает проб елы, заключите его в одинарные кавычки:

=HLOOKUP(B$1; 'Диаметры планет'!$B$1:$I$2; 2; FALSE)

Для ссылок на другую книгу, обрамите имя книги в квадратные скобки:

=HLOOKUP(B$1; [Book1.xlsx]Diameters!$B$1:$I$2; 2; FALSE)

Если же книга закрыта, необходимо указать полный путь к файлу:

=HLOOKUP(B$1; 'D:\Отчеты\[Book1.xlsx]Diameters'!$B$1:$I$2; 2; FALSE)

HLOOKUP с частичным совпадением: использование подстановочных знаков

Как и VLOOKUP, функция HLOOKUP позволяет применять подстановочные знаки в качестве аргумента lookup_value для поиска по частичному совпадению.

Поддерживаются два символа:

1. ? — заменяет любой одиночный символ.
2. * — заменяет любое количество символов.

Например, в таблице с именами клиентов и их номерами заказов (B1:I2) вы хотите найти номер заказа, зная лишь, что имя клиента начинается с «ace».

Используйте следующий вариант функции: =HLOOKUP("ace*"; B1:I2; 2; FALSE)

Или, чтобы сделать формулу динамичной, введите значение в ячейку B4 и объедините его с подстановочным знаком: =HLOOKUP(B4&"*"; B1:I2; 2; FALSE)

Важно: для работы подстановочных знаков range_lookup должен быть равен FALSE.

Если таблица содержит несколько совпадающих значений, функция вернет первое найденное.

Абсолютные и относительные ссылки в формулах HLOOKUP

Когда вы создаете формулу для одной ячейки в Excel, использование относительных и абсолютных ссылок не требует особого внимания, так как в этом случае подойдет любой из типов ссылок.

Однако, если вы планируете копировать формулу на несколько ячеек, это изменяет дело.

Важно учитывать следующее:

1. Обязательно фиксируйте table_array, применяя абсолютные ссылки с символом $, например, $B$1:$I$2.
2. Обычно ссылка на lookup_value должна быть относительной или смешанной, что зависит от вашей логики и цели.

Чтобы прояснить данный момент, давайте более подробно рассмотрим формулу, которая получает данные с другого листа:

=HLOOKUP(B$1; Diameters!$B$1:$I$2; 2;, FALSE)

В этой формуле мы используем абсолютные ссылки ($B$1:$I$2) для table_array, так как диапазон должен оставаться неизменным, даже когда формула будет перемещена в другие ячейки.

Что касается lookup_value (B$1), здесь применяется смешанная ссылка: столбец остается относительным, а строка фиксирована.

Это объясняется тем, что значения для поиска (например, названия планет) располагаются в одной строке (строка 1), но занимают разные столбцы (от B до I).

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

Используя правильные ссылки, мы обеспечиваем корректное функционирование формулы HLOOKUP в нескольких ячейках одновременно.

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

INDEX/MATCH — более мощная альтернатива HLOOKUP в Excel

Как известно, функция HLOOKUP в Excel обладает рядом ограничений, включая невозможность поиска данных, кроме как в самой верхней строке, а также требование сортировки значений для поиска с приблизительным совпадением.

К счастью, есть более универсальная и мощная альтернатива как для VLOOKUP, так и для HLOOKUP — комбинация функций INDEX и MATCH, которая может быть представлена в следующем виде:

INDEX(где вернуть значение, MATCH(значение для поиска, где искать, 0))

Предположим, что значение для поиска располагается в ячейке B7, вы хотите найти соответствие в строке 2 (B2:I2) и вернуть значение из строки 1 (B1:I1).

В таком случае формула будет выглядеть следующим образом:

=INDEX(B1:I1; MATCH(B7; B2:I2; 0))

На иллюстрации ниже вы можете увидеть две формулы HLOOKUP, которые ищут значения в первой и второй строках, и в каждом из этих случаев INDEX MATCH демонстрирует одинаково хорошую работоспособность.

Как выполнить чувствительный к регистру HLOOKUP в Excel

Как уже отмечалось в начале этого руководства, функция HLOOKUP в Excel не учитывает регистр букв.

Когда требуется, чтобы регистр имел значение, можно использовать функцию EXACT, которая выполняет точное сравнение ячеек, и встроить её в формулу INDEX MATCH, которую мы рассмотрели ранее:

INDEX(строка для возврата значения, MATCH(TRUE, EXACT(строка для поиска, значение для поиска); 0))

Допустим, значение для поиска размещено в ячейке B4, диапазон для поиска — B1:I1, а диапазон для возврата — B2:I2.

Тогда формула будет следующей: =INDEX(B2:I2; MATCH(TRUE, EXACT(B1:I1, B4); 0))

Важно помнить! Эта формула является формулой массива, поэтому для её завершения необходимо нажать комбинацию клавиш Ctrl + Shift + Enter.

Приведенный выше пример представляет собой один из методов, который я предпочитаю, но не единственный способ выполнения чувствительного к регистру HLOOKUP в Excel.

Если вам интересно узнать о других вариантах, не стесняйтесь ознакомиться с этим руководством: 4 способа выполнить чувствительный к регистру VLOOKUP в Excel.

Уверен, вы сможете адаптировать формулы VLOOKUP для горизонтального поиска без особых проблем.

10 основных причин, по которым функция HLOOKUP может не работать в Excel

Вы, безусловно, уже осведомлены о том, что HLOOKUP является важной и мощной функцией для поиска данных в Excel.

Тем не менее, она также может быть довольно сложной, и из-за множества нюансов ошибки, такие как #N/A, #VALUE! или #REF!, могут возникать с завидной частотой.

Если ваша формула HLOOKUP не функционирует должным образом, скорее всего, причина кроется в одной из следующих проблем.

Запомните, что HLOOKUP ограничен возможностью поиска только в верхней строке вашего диапазона данных.

Если вы попытаетесь найти значение в строке ниже первой, это приведет к ошибке #N/A.

Чтобы обойти это ограничение, рассмотрите возможность использования комбинации формул INDEX и MATCH.

При выполнении поиска в Excel — будь то горизонтальный (через HLOOKUP) или вертикальный (через VLOOKUP) — чаще всего вы хотите найти конкретное значение, что требует точного совпадения.

В случае использования приблизительного совпадения (когда параметр range_lookup установлен на TRUE или вообще опущен) важно помнить о сортировке значений в первой строке по возрастанию.

Дополнительные сведения и примеры формул можно найти в разделе, посвященном HLOOKUP с точным и приблизительным совпадением в Excel.

Если вы используете несколько функций HLOOKUP для получения информации из строки с данными, необходимо зафиксировать ссылку на table_array, как указано в разделе Абсолютные и относительные ссылки в формулах HLOOKUP.

Влияние добавления или удаления строк

Чтобы понять, как добавление новой строки может нарушить работу формулы HLOOKUP, вспомните, что она получает данные на основе указанного вами номера строки.

1. Например, вы хотите извлечь данные о продажах по идентификатору товара.

2. Эти данные располагаются в строке 4, и вы указываете 4 в параметре row_index_num.

3. Однако если вы добавите новую строку, нужная информация переместится на 5-ю строку, и ваша формула HLOOKUP перестанет работать.

4. Подобные проблемы могут возникнуть и при удалении существующих строк.

5. В качестве решения можно либо заблокировать таблицу для предотвращения добавления строк, либо перейти на использование INDEX и MATCH вместо HLOOKUP.

6. В этих формулах вы ссылаетесь на строки для поиска и возвращения значений через ссылки на диапазон, а не через номера строк, что позволяет Excel автоматически адаптировать эти ссылки.

Таким образом, вы можете без проблем добавлять или удалять столбцы и строки, не беспокоясь о необходимости обновлять каждую формулу в вашей таблице.

Проблемы с дублирующимеся значениями

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

Если в вашем наборе данных существуют несколько одинаковых записей, вы можете выбрать один из следующих подходов, который наиболее подойдет вам:

1. Удалите дублирующиеся записи, используя встроенные инструменты Excel или наш Duplicate Remover.

2. Если необходимо сохранить дубликаты в вашем наборе данных, создайте сводную таблицу для группировки и фильтрации данных по вашему усмотрению.

3. Альтернативно, вы можете использовать формулу массива для извлечения всех дублирующихся значений в заданном диапазоне.

Пробелы, которые могут создавать проблемы

Если ваша формула HLOOKUP в то время как на первый взгляд кажется корректной, возвращает множество ошибок #N/A, проверьте наличие лишних пробелов в таблице и в значении для поиска. Вы можете быстро избавиться от начальных, конечных и избыточных пробелов между словами, используя функцию TRIM в Excel или наш инструмент Trim Spaces. 

Числа, которые записаны как текст

Еще одной распространенной проблемой для формул Excel являются текстовые строки, которые выглядят как числа.

Полное описание этой проблемы и возможные способы её решения можно найти в разделе Почему формулы в Excel могут перестать работать.

Длина значения для поиска превышает 255 символов

Все функции поиска в Excel функционируют корректно только в том случае, если значение для поиска состоит менее чем из 255 символов.

Более длинные строки приведут к ошибке #VALUE!.

Поскольку формула INDEX/MATCH не имеет такого ограничения, её использование может стать решением данной проблемы.

Если вы выполняете HLOOKUP в другой рабочей книге, не забудьте указать полный путь к ней.

Примеры подобных формул можно найти в разделе Как выполнить HLOOKUP из другого листа или рабочей книги.

Неоднократно подчеркивалось, что HLOOKUP является функцией, к которой необходимо относиться с вниманием.

Вот несколько распространенных ошибок, возникающих из-за неверных аргументов:

1. Если row_index_num меньше 1, формула HLOOKUP выдаст ошибку #VALUE!.
2. Если row_index_num превышает количество строк в table_array, вы получите ошибку #REF!.
3. При поиске с использованием приблизительного совпадения, если lookup_value меньше минимального значения в первой строке table_array, формула вернёт ошибку #N/A.

Итак, вот основные аспекты использования функции HLOOKUP в Excel. Надеюсь, что представленные здесь сведения окажутся вам полезными.