Примеры функции ГПР
Содержание
В 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])
- Lookup_value (обязательный аргумент): это то значение, которое вы хотите найти в первой строке.
- Table_array (обязательный аргумент): это диапазон данных, где производится поиск.
- Row_index_num (обязательный аргумент): указывает, из какого ряда нужно извлечь данные.
- Range_lookup (необязательный аргумент): позволяет задать, какой тип совпадения нужен.
Для лучшего понимания, как работает HLOOKUP, представьте, что у вас есть таблица с информацией о планетах.
Если нужно найти диаметр конкретной планеты, чье имя введено в ячейку B5, HLOOKUP вернёт данные с учетом горизонтального поиска.
Пример формулы HLOOKUP:
Допустим, вам нужно найти значение для планеты, указанной в ячейке B5.
Вот как будет выглядеть формула:
=HLOOKUP(B5; B2:I3; 2; FALSE)
Здесь:
- B5 — планета, которую нужно найти.
- B2:I3 — диапазон данных, где идет поиск.
- 2 — строка, из которой нужно извлечь значение.
- FALSE — точное совпадение.
Важные моменты при использовании HLOOKUP
При работе с функцией HLOOKUP, следует учитывать несколько нюансов:
- HLOOKUP ищет данные только в первой строке.
- Функция не чувствительна к регистру: Excel не различает строчные и заглавные буквы.
- При использовании приблизительного поиска (TRUE) данные в первой строке должны быть отсортированы по возрастанию.
В чём отличие между HLOOKUP и VLOOKUP?
Основное различие между этими двумя функциями заключается в направлении поиска.
HLOOKUP выполняет поиск по горизонтали, начиная с первой строки, тогда как VLOOKUP ищет данные по вертикали, начиная с первого столбца.
Это разница в направлении делает их применимыми для разных задач.
Дополнительные примеры и рекомендации
Теперь, когда основные аспекты HLOOKUP вам известны, можно рассмотреть более сложные примеры.
Например, с помощью HLOOKUP можно осуществить поиск данных на другом листе, использовать подстановочные знаки для частичного совпадения или применять INDEX и MATCH для замены.
Горизонтальный поиск с точным и приближенным совпадением значений
Функция HLOOKUP в Excel предназначена для поиска значения в строках таблицы.
В зависимости от параметра range_lookup, она может выполнять поиск как с точным совпадением, так и с приблизительным.
Этот параметр может принимать два значения:
- Если указан TRUE или параметр пропущен, производится поиск ближайшего совпадения (приближенного).
- Если указано 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 для поиска по частичному совпадению.
Поддерживаются два символа:
- ? — заменяет любой одиночный символ.
- * — заменяет любое количество символов.
Например, в таблице с именами клиентов и их номерами заказов (B1:I2) вы хотите найти номер заказа, зная лишь, что имя клиента начинается с «ace».
Используйте следующий вариант функции: =HLOOKUP("ace*"; B1:I2; 2; FALSE)
Или, чтобы сделать формулу динамичной, введите значение в ячейку B4 и объедините его с подстановочным знаком: =HLOOKUP(B4&"*"; B1:I2; 2; FALSE)
Важно: для работы подстановочных знаков range_lookup должен быть равен FALSE.
Если таблица содержит несколько совпадающих значений, функция вернет первое найденное.
Абсолютные и относительные ссылки в формулах HLOOKUP
Когда вы создаете формулу для одной ячейки в Excel, использование относительных и абсолютных ссылок не требует особого внимания, так как в этом случае подойдет любой из типов ссылок.
Однако, если вы планируете копировать формулу на несколько ячеек, это изменяет дело.
Важно учитывать следующее:
- Обязательно фиксируйте table_array, применяя абсолютные ссылки с символом $, например, $B$1:$I$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 не может осуществлять поиск выше самой верхней строки
Запомните, что HLOOKUP ограничен возможностью поиска только в верхней строке вашего диапазона данных.
Если вы попытаетесь найти значение в строке ниже первой, это приведет к ошибке #N/A.
Чтобы обойти это ограничение, рассмотрите возможность использования комбинации формул INDEX и MATCH.
Разница между точным и приблизительным совпадением
При выполнении поиска в Excel — будь то горизонтальный (через HLOOKUP) или вертикальный (через VLOOKUP) — чаще всего вы хотите найти конкретное значение, что требует точного совпадения.
В случае использования приблизительного совпадения (когда параметр range_lookup установлен на TRUE или вообще опущен) важно помнить о сортировке значений в первой строке по возрастанию.
Дополнительные сведения и примеры формул можно найти в разделе, посвященном HLOOKUP с точным и приблизительным совпадением в Excel.
Изменение ссылки на диапазон таблицы при копировании формулы
Если вы используете несколько функций HLOOKUP для получения информации из строки с данными, необходимо зафиксировать ссылку на table_array, как указано в разделе Абсолютные и относительные ссылки в формулах HLOOKUP.
Влияние добавления или удаления строк
Чтобы понять, как добавление новой строки может нарушить работу формулы HLOOKUP, вспомните, что она получает данные на основе указанного вами номера строки.
-
Например, вы хотите извлечь данные о продажах по идентификатору товара.
-
Эти данные располагаются в строке 4, и вы указываете 4 в параметре row_index_num.
-
Однако если вы добавите новую строку, нужная информация переместится на 5-ю строку, и ваша формула HLOOKUP перестанет работать.
-
Подобные проблемы могут возникнуть и при удалении существующих строк.
-
В качестве решения можно либо заблокировать таблицу для предотвращения добавления строк, либо перейти на использование INDEX и MATCH вместо HLOOKUP.
-
В этих формулах вы ссылаетесь на строки для поиска и возвращения значений через ссылки на диапазон, а не через номера строк, что позволяет Excel автоматически адаптировать эти ссылки.
Таким образом, вы можете без проблем добавлять или удалять столбцы и строки, не беспокоясь о необходимости обновлять каждую формулу в вашей таблице.
Проблемы с дублирующимеся значениями
Функция HLOOKUP в Excel может возвращать только одно значение, причем именно первое, найденное в таблице, которое соответствует критерию поиска.
Если в вашем наборе данных существуют несколько одинаковых записей, вы можете выбрать один из следующих подходов, который наиболее подойдет вам:
-
Удалите дублирующиеся записи, используя встроенные инструменты Excel или наш Duplicate Remover.
-
Если необходимо сохранить дубликаты в вашем наборе данных, создайте сводную таблицу для группировки и фильтрации данных по вашему усмотрению.
-
Альтернативно, вы можете использовать формулу массива для извлечения всех дублирующихся значений в заданном диапазоне.
Пробелы, которые могут создавать проблемы
Если ваша формула HLOOKUP в то время как на первый взгляд кажется корректной, возвращает множество ошибок #N/A, проверьте наличие лишних пробелов в таблице и в значении для поиска. Вы можете быстро избавиться от начальных, конечных и избыточных пробелов между словами, используя функцию TRIM в Excel или наш инструмент Trim Spaces.
Числа, которые записаны как текст
Еще одной распространенной проблемой для формул Excel являются текстовые строки, которые выглядят как числа.
Полное описание этой проблемы и возможные способы её решения можно найти в разделе Почему формулы в Excel могут перестать работать.
Длина значения для поиска превышает 255 символов
Все функции поиска в Excel функционируют корректно только в том случае, если значение для поиска состоит менее чем из 255 символов.
Более длинные строки приведут к ошибке #VALUE!.
Поскольку формула INDEX/MATCH не имеет такого ограничения, её использование может стать решением данной проблемы.
Отсутствие полного пути к книге для поиска
Если вы выполняете HLOOKUP в другой рабочей книге, не забудьте указать полный путь к ней.
Примеры подобных формул можно найти в разделе Как выполнить HLOOKUP из другого листа или рабочей книги.
Ошибки в аргументах
Неоднократно подчеркивалось, что HLOOKUP является функцией, к которой необходимо относиться с вниманием.
Вот несколько распространенных ошибок, возникающих из-за неверных аргументов:
- Если row_index_num меньше 1, формула HLOOKUP выдаст ошибку #VALUE!.
- Если row_index_num превышает количество строк в table_array, вы получите ошибку #REF!.
- При поиске с использованием приблизительного совпадения, если lookup_value меньше минимального значения в первой строке table_array, формула вернёт ошибку #N/A.
Итак, вот основные аспекты использования функции HLOOKUP в Excel. Надеюсь, что представленные здесь сведения окажутся вам полезными.
Оцените статью |
|
22.11.2024
22.11.2024
22.11.2024
22.11.2024
22.11.2024