Загрузите образец книги
В этом руководстве показано, как использовать Функция ГПР в Excel в Excel, чтобы найти значение.
Обзор функции HLOOKUP
Функция HLOOKUP Hlookup означает горизонтальный поиск. Он ищет значение в верхней строке таблицы. Затем возвращает значение на указанное количество строк ниже найденного значения. Это то же самое, что и vlookup, за исключением того, что он ищет значения по горизонтали, а не по вертикали.
(Обратите внимание, как появляются входные данные формулы)
Синтаксис и ввод функции HLOOKUP:
| 1 | = HLOOKUP (значение_просмотра, массив_таблиц, номер_индекса_строки, поиск_пространства) |
lookup_value - Значение, которое вы хотите найти.
table_array -Таблица, из которой можно получить данные.
row_index_num - Номер строки, из которой нужно получить данные.
range_lookup - [необязательно] Логическое значение, указывающее на точное или приблизительное совпадение. По умолчанию = ИСТИНА = приблизительное совпадение.
Что такое функция HLOOKUP?
Как одна из самых старых функций в мире электронных таблиц, функция HLOOKUP используется для ЧАСгоризонтальный Поиск. У него есть несколько ограничений, которые часто преодолеваются с помощью других функций, таких как INDEX / MATCH. Кроме того, большинство таблиц построено вертикально, но в некоторых случаях полезно выполнять поиск по горизонтали.
Базовый пример
Давайте посмотрим на образец данных из журнала успеваемости. Мы рассмотрим несколько примеров извлечения информации для конкретных учащихся.
Если мы хотим узнать, в каком классе находится Боб, мы должны написать формулу:
| 1 | = HLOOKUP ("Боб"; A1: E3; 2; ЛОЖЬ) |
Важно помнить, что искомый элемент (Боб) должен находиться в первой строке нашего диапазона поиска (A1: E3). Мы сказали функции, что хотим вернуть значение из 2nd строка диапазона поиска, которая в данном случае является строкой 2. Наконец, мы указали, что хотим выполнить точное совпадение поместив False в качестве последнего аргумента. Здесь ответ будет «Чтение».
Боковой совет: Вы также можете использовать число 0 вместо False в качестве последнего аргумента, поскольку они имеют одинаковое значение. Некоторые люди предпочитают это, потому что так писать быстрее. Просто знайте, что оба приемлемы.
Сдвинутые данные
Чтобы добавить некоторые пояснения к нашему первому примеру, элемент поиска не обязательно должен находиться в строке 1 вашей электронной таблицы, а должен находиться только в первой строке диапазона поиска. Давайте использовать тот же набор данных:
А теперь давайте найдем оценку за естествознание. Наша формула была бы
| 1 | = ГПР ("Наука"; A2: E3; 2; ЛОЖЬ) |
Это по-прежнему действующая формула, поскольку первая строка нашего диапазона поиска - это строка 2, в которой будет найден наш поисковый запрос «Наука». Мы возвращаем значение из 2nd строка диапазона поиска, которая в данном случае является строкой 3. Тогда ответ будет «A-».
Использование подстановочных знаков
Функция HLOOKUP поддерживает использование подстановочных знаков «*» и «?». при поиске. Например, предположим, что мы забыли, как пишется имя Фрэнка, и просто хотели найти имя, которое начинается с буквы «F». Мы могли бы написать формулу
| 1 | = HLOOKUP ("F *"; A1: E3; 2; ЛОЖЬ) |
Это позволит найти имя Фрэнк в столбце E, а затем вернуть значение из 2nd относительный ряд. В этом случае ответ будет «Наука».
Неточное совпадение
В большинстве случаев вам нужно убедиться, что последний аргумент в HLOOKUP равен False (или 0), чтобы получить точное совпадение. Однако в некоторых случаях вы можете искать неточное совпадение. Если у вас есть список отсортированных данных, вы также можете использовать HLOOKUP, чтобы вернуть результат для элемента, который является либо таким же, либо следующим наименьшим. Это часто используется при работе с увеличивающимся диапазоном чисел, например, в налоговой таблице или комиссионных бонусах.
Допустим, вы хотите найти налоговую ставку для дохода, указанного в ячейке H2. Формула в H4 может быть:
| 1 | = ГПР (H2; B1: F2; 2; ИСТИНА) |
Разница в этой формуле в том, что наш последний аргумент - «Верно». В нашем конкретном примере мы видим, что когда наш человек вводит доход в размере 45 000 долларов, у него будет ставка налога в размере 15%.
Примечание: Хотя обычно нам нужно точное совпадение с False в качестве аргумента, если вы забыли указать 4th аргумент в HLOOKUP, значение по умолчанию - True. Это может привести к неожиданным результатам, особенно при работе с текстовыми значениями.
Динамический ряд
HLOOKUP требует, чтобы вы указали аргумент, указывающий, из какой строки вы хотите вернуть значение, но может возникнуть ситуация, когда вы не знаете, где будет находиться строка, или вы хотите разрешить пользователю изменить, из какой строки следует вернуться. В этих случаях может быть полезно использовать функцию ПОИСКПОЗ для определения номера строки.
Давайте снова рассмотрим наш пример с журналом оценок с некоторыми входными данными в G2 и G4. Чтобы получить номер столбца, мы могли бы написать формулу
| 1 | = ПОИСКПОЗ (G2; A1: A3; 0) |
Это попытается найти точное положение «Grade» в диапазоне A1: A3. Ответ будет 3. Зная это, мы можем подключить его к функции HLOOKUP и написать формулу в G6 следующим образом:
| 1 | = ГПР (G4; A1: E3; ПОИСКПОЗ (G2; A1: A3; 0); 0) |
Таким образом, функция ПОИСКПОЗ вернет 3, и это говорит ГПР вернуть результат из 3rd строка в диапазоне A1: E3. В целом, мы получаем желаемый результат «C». Наша формула теперь динамична в том смысле, что мы можем изменить либо строку для просмотра, либо имя для поиска.
Ограничения HLOOKUP
Как упоминалось в начале статьи, самым большим недостатком HLOOKUP является то, что он требует, чтобы поисковый запрос находился в самом левом столбце диапазона поиска. Хотя есть несколько хитрых уловок, которые можно использовать, чтобы преодолеть это, распространенной альтернативой является использование ИНДЕКС и ПОИСКПОЗ. Эта комбинация дает вам больше гибкости, а иногда даже может быть более быстрым вычислением.
HLOOKUP в Google Таблицах
Функция HLOOKUP работает в Google Таблицах точно так же, как и в Excel:
Дополнительные замечания
Используйте функцию HLOOKUP для горизонтального поиска. Если вы уже знакомы с функцией ВПР, ГПР работает точно так же, за исключением того, что поиск выполняется по горизонтали, а не по вертикали. HLOOKUP ищет точное совпадение (range_lookup = FALSE) или ближайшее совпадение, которое равно или меньше lookup_value (range_lookup = ИСТИНА, только числовые значения) в первой строке table_array. Затем он возвращает соответствующее значение, количество строк на n ниже совпадения.
При использовании HLOOKUP для поиска точного совпадения сначала вы определяете идентифицирующее значение, которое хотите найти, как lookup_value. Это идентифицирующее значение может быть SSN, идентификатором сотрудника, именем или другим уникальным идентификатором.
Затем вы определяете диапазон (называемый table_array), который содержит идентификаторы в верхней строке и любые значения, которые вы в конечном итоге хотите найти в строках под ней. ВАЖНО: Уникальные идентификаторы должны быть в верхней строке. Если это не так, вы должны либо переместить строку наверх, либо использовать ПОИСКПОЗ / ИНДЕКС вместо ГПР.
В-третьих, определите номер строки (row_index) принадлежащий table_array что вы хотите вернуться. Имейте в виду, что первая строка, содержащая уникальные идентификаторы, - это строка 1. Вторая строка - это строка 2 и т. Д.
Наконец, вы должны указать, следует ли искать точное совпадение (FALSE) или ближайшее совпадение (TRUE) в range_lookup. Если выбрана опция точного совпадения, а точное совпадение не найдено, возвращается ошибка (# N / A). Чтобы формула возвращала пустое значение, «не найдено» или любое другое значение вместо значения ошибки (# Н / Д), используйте функцию ЕСЛИОШИБКА с ГПР.
Чтобы использовать функцию HLOOKUP для получения приблизительного набора соответствий: range_lookup = ИСТИНА. Этот параметр доступен только для числовых значений. Значения необходимо отсортировать по возрастанию.
Примеры HLOOKUP в VBA
Вы также можете использовать функцию HLOOKUP в VBA. Тип:application.worksheetfunction.hlookup (lookup_value, table_array, row_index_num, range_lookup)
Выполнение следующих операторов VBA
| 123456 | Диапазон ("G2") = Application.WorksheetFunction.HLookup (Диапазон ("C1"), Диапазон ("A1: E3"), 1)Диапазон ("H2") = Application.WorksheetFunction.HLookup (Диапазон ("C1"), Диапазон ("A1: E3"), 2)Диапазон ("I2") = Application.WorksheetFunction.HLookup (Диапазон ("C1"), Диапазон ("A1: E3"), 3)Диапазон ("G3") = Application.WorksheetFunction.HLookup (Диапазон ("D1"), Диапазон ("A1: E3"), 1)Диапазон ("H3") = Application.WorksheetFunction.HLookup (Диапазон ("D1"), Диапазон ("A1: E3"), 2)Диапазон ("I3") = Application.WorksheetFunction.HLookup (Диапазон ("D1"), Диапазон ("A1: E3"), 3) |
даст следующие результаты
Для аргументов функции (lookup_value и т. Д.) Вы можете либо ввести их непосредственно в функцию, либо определить переменные для использования вместо них.
Вернуться к списку всех функций в Excel
