Hlookup Excel как использовать

Обновлено: 20.11.2024

Описание

Функция ГПР Microsoft Excel выполняет горизонтальный поиск, ища значение в верхней строке таблицы и возвращая значение в том же столбце на основе номер_индекса. .

Функция ГПР – это встроенная функция Excel, относящаяся к категории Функция поиска/справки. Его можно использовать как функцию рабочего листа (WS) в Excel. В качестве функции рабочего листа функцию ГПР можно ввести как часть формулы в ячейку рабочего листа.

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

Синтаксис

Синтаксис функции ГПР в Microsoft Excel:

Параметры или аргументы

значение Значение для поиска в первой строке таблицы. таблица Две или более строк данных, отсортированных в порядке возрастания. index_number Номер строки в table, из которой должно быть возвращено совпадающее значение. Первая строка равна 1. приблизительное_соответствие Необязательный. Введите FALSE, чтобы найти точное совпадение. Введите TRUE, чтобы найти приблизительное совпадение. Если этот параметр опущен, по умолчанию используется TRUE.

Возврат

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

Применимо к

Excel для Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 для Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Тип функции

Пример (как функция рабочего листа)

Давайте рассмотрим, как использовать функцию ГПР в качестве функции рабочего листа в Microsoft Excel:

Исходя из приведенной выше электронной таблицы Excel, будут возвращены следующие примеры HLOOKUP:

Теперь давайте рассмотрим пример =HLOOKUP(10251, A1:G3, 2, FALSE), который возвращает значение 16,80 долларов США, и разберемся, почему.

Первый параметр

Первый параметр функции ГПР — это значение для поиска в таблице данных.

В этом примере первый параметр — 10251. Это значение, которое HLOOKUP будет искать в первой строке таблицы данных.

Второй параметр

Второй параметр функции ГПР — это таблица или источник данных, в котором должен выполняться горизонтальный поиск.

В этом примере вторым параметром является A1:G3. HLOOKUP использует первую строку в этом диапазоне (например, A1:G1) для поиска значения 10251.

Третий параметр

Третий параметр — это номер позиции в таблице, где можно найти возвращаемые данные. Значение 1 указывает на первую строку в таблице. Вторая строка — 2 и т. д.

В этом примере третий параметр равен 2. Это означает, что во второй строке таблицы мы найдем возвращаемое значение. Поскольку диапазон таблицы установлен на A1:G3, возвращаемое значение будет во второй строке где-то в диапазоне A2:G2.

Четвертый параметр

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

В этом примере четвертый параметр равен FALSE. Параметр FALSE означает, что HLOOKUP ищет ТОЧНОЕ совпадение для значения 10251. Параметр TRUE означает, что будет возвращено «близкое» совпадение. Поскольку HLOOKUP может найти значение 10251 в диапазоне A1:G1, он возвращает соответствующее значение из A2:G2, которое составляет 16,80 долларов США.

Точное соответствие и приблизительное соответствие

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

Давайте найдем значение, которого нет в наших данных, чтобы продемонстрировать важность этого параметра!

Точное соответствие

Используйте FALSE, чтобы найти точное соответствие:

Приблизительное соответствие

Используйте TRUE, чтобы найти приблизительное соответствие:

Если совпадений не найдено, возвращается следующее меньшее значение, которое в данном случае равно 14 долларов США.

ПРОСМОТР с другого листа

Вы можете использовать HLOOKUP для поиска значения, когда таблица находится на другом листе.Давайте изменим наш пример выше и предположим, что таблица находится в другом листе с именем Sheet2 в диапазоне A1: G3. Мы могли бы переписать исходный пример, в котором мы ищем значение 10251, следующим образом:

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

ГПР с другого листа с пробелами в имени листа

Добавим еще одно усложнение. Что произойдет, если имя вашего листа содержит пробелы? Если в имени листа есть пробелы, вам нужно будет изменить формулу дальше.

Предположим, что таблица находится на листе под названием "Тестовый лист" в диапазоне A1:G3. Нам потребуется изменить нашу формулу следующим образом:

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

Зачем использовать абсолютные ссылки?

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

Итак, если бы у вас была следующая формула в ячейке J1:

А затем вы скопировали эту формулу из ячейки J1 в ячейку K2, это изменило бы формулу HLOOKUP на это:

Поскольку ваша таблица находится в диапазоне A1:G3, а не в диапазоне B2:H4, ваша формула вернет ошибочные результаты в ячейке K2. Чтобы убедиться, что ваш диапазон не изменился, попробуйте сослаться на диапазон вашей таблицы, используя абсолютную ссылку следующим образом:

Теперь, если вы скопируете эту формулу в другую ячейку, диапазон вашей таблицы останется $A$1:$G$3.

Например, если бы у вас была следующая формула:

Эти формулы используют функции ЕСНА, ЕСЛИОШИБКА и ЕСЛИНА для возврата "Не найдено", если совпадение не найдено функцией ГПР.

Это отличный способ украсить вашу электронную таблицу, чтобы вы не видели традиционных ошибок Excel.

Функция ГПР в Excel находит и извлекает значение из данных в горизонтальной таблице. «H» в HLOOKUP означает «горизонтальный», и значения поиска должны появляться в первой строке таблицы, перемещаясь по горизонтали вправо. HLOOKUP поддерживает приблизительное и точное совпадение, а также подстановочные знаки (* ?) для поиска частичных совпадений.

lookup_value — значение для поиска.
table_array – таблица, из которой извлекаются данные.
row_index – номер строки, из которой извлекаются данные.
range_lookup — [необязательно] логическое значение для указания точного или приблизительного совпадения. По умолчанию = TRUE = приблизительное совпадение.

Функция ГПР может находить и извлекать значение из данных в горизонтальной таблице. Подобно букве «V» в функции ВПР, которая означает «вертикальный», буква «Н» в функции ВПР означает «горизонтальный». Значения поиска должны отображаться в первой строке таблицы, перемещаясь по горизонтали вправо. HLOOKUP поддерживает приблизительное и точное совпадение, а также подстановочные знаки (* ?) для поиска частичных совпадений.

HLOOKUP ищет значение в первой строке таблицы. Когда он находит совпадение, он извлекает значение в этом столбце из заданной строки. Используйте HLOOKUP, когда значения поиска находятся в первой строке таблицы. Используйте функцию ВПР, когда значения поиска находятся в первом столбце таблицы.

HLOOKUP принимает четыре аргумента. Первый аргумент, называемый lookup_value, — это значение для поиска. Второй аргумент, table_array, представляет собой диапазон, содержащий таблицу поиска. Третий аргумент, номер_индекса_строки, – это номер строки в таблице, из которой извлекается значение. В показанном примере функция ГПР используется для поиска значений в строке 2 (уровень) и строке 3 (бонус) в таблице. Четвертый и последний аргумент, range_lookup, управляет сопоставлением. Используйте TRUE или 1 для приблизительного совпадения и FALSE или 0 для точного совпадения.

В показанном примере цель состоит в том, чтобы найти правильный Уровень и Бонус для сумм продаж в C5:C13. Таблица поиска находится в H4: J6, который является именованным диапазоном «таблица». Обратите внимание, что это примерный сценарий матча. Для каждой суммы в C5:C13 цель состоит в том, чтобы найти наилучшее совпадение, а не точное совпадение. Чтобы найти уровень, формула в ячейке D5, скопированная вниз, выглядит следующим образом:

Чтобы получить Бонус, скопируйте формулу в E5:

Обратите внимание, что единственное различие между этими двумя формулами – номер индекса строки: уровень – это строка 2 таблицы поиска, а бонус – строка 3.Режим соответствия был задан явным образом для приблизительного соответствия путем указания последнего аргумента range_lookup равным 1.

На приведенном ниже экране цель состоит в том, чтобы найти правильный уровень для числового рейтинга 1–4. В ячейке D5 скопированная формула ГПР выглядит следующим образом:

где таблица — это именованный диапазон G4:J5. Обратите внимание, что для последнего аргумента range_lookup установлено значение FALSE, чтобы требовалось точное совпадение.

ГПР в Excel означает "горизонтальный просмотр". Это функция, которая заставляет Excel искать определенное значение в строке (так называемый «табличный массив»), чтобы вернуть значение из другой строки в том же столбце.

Определение функции ГПР

Функция ГПР в Excel состоит из 4 компонентов:

Значение, которое вы хотите найти;
Диапазон, в котором вы хотите найти значение и возвращаемое значение;
Номер строки в заданном диапазоне, содержащей возвращаемое значение;
0 или FALSE для точного совпадения с искомым значением; 1 или TRUE для приблизительного совпадения.

Синтаксис: HLOOKUP([значение], [диапазон], [номер строки], [ложь или истина])

Пример ГПР

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

Сначала выберите ячейку, в которой вы хотите опубликовать количество проданных апельсинов. Начнем с «Дня 1»:

Затем мы выбираем значение (из строки 1), которое нужно найти, в данном случае это "оранжевый" в ячейке F1.

Это значение можно вставить в виде строки: «Оранжевый» (изображение 1) или в виде ссылки на ячейку: F1 (изображение 2):

Изображение 1. Значение в виде строки

Изображение 2. Значение в качестве ссылки на ячейку

Далее мы вводим ‘;’ и выбираем диапазон (или массив таблиц), в котором мы хотим найти искомое значение и возвращаемое значение. В данном случае это диапазон (B1:H8):

Затем мы вводим ‘;’ и номер строки в пределах определенного диапазона, в котором мы хотим искать возвращаемое значение. В данном случае это строка 2 (для дня 1), за которой следуют «;» и «0» или «ЛОЖЬ» для точного совпадения со значением поиска «Оранжевый»:

Когда мы нажимаем Enter, мы получаем количество апельсинов, проданных в первый день:

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

Эту процедуру можно повторить для каждой формулы в диапазоне формул B12:H12.

В этом примере у нас есть только небольшой список дат, но представьте, что у вас есть длинный список, который публикуется за несколько лет, и вы хотите использовать определенное значение в других рабочих листах! Используя HLOOKUP (и VLOOKUP), пользователь должен изменить определенное значение только на одном листе, и оно будет автоматически изменено во всех других соответствующих местах.

Но что, если вы не уверены в своей поисковой ценности? Затем вы можете рассмотреть приблизительное совпадение (установите для False значение True).

Функция ГПР ищет значение по горизонтали, по всей строке, чтобы найти точное или ближайшее совпадение. Затем он возвращает значение из другой строки в том же столбце, где он нашел значение. HLOOKUP похож на VLOOKUP, который используется для вертикального поиска

Видео: функция ГПР

Функция ГПР может найти точное совпадение в строке поиска или ближайшее совпадение (приблизительное совпадение). В этом коротком видео показаны оба типа HLOOKUP:

Точное совпадение: поиск общего объема продаж в определенном регионе с названиями регионов в первой строке.
Приблизительное совпадение: найти процентную ставку на определенную дату с датами в первой строке.

Когда использовать HLOOKUP

Используйте функцию ГПР, когда вам нужно:

ищите значение по горизонтали — поперек строки на листе. H в HLOOKUP означает горизонтальный
возвратить значение из другой строки в том же столбце, где было найдено значение
используйте версию Excel, в которой нет более новой функции поиска XLOOKUP (ссылка на сайт Microsoft)

Примечание. Если вам нужно искать значение по вертикали, вниз по столбцу, используйте вместо этого функцию ВПР или XПР.

Как использовать ГПР

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

При создании формулы Excel автоматически показывает синтаксис функции после ввода ее имени и открывающей квадратной скобки.

Синтаксис ГПР

Функция ГПР имеет следующий синтаксис с 3 обязательными аргументами и 1 необязательным аргументом:

HLOOKUP(искомое_значение, табличный_массив, номер_индекса_строки, [диапазон_искателя])

ПРИМЕЧАНИЕ. Аргументы в квадратных скобках необязательны

Обязательные аргументы

Вот 3 обязательных аргумента для HLOOKUP в том порядке, в котором их нужно вводить:

<р>1. lookup_value: какое значение должен искать HLOOKUP в первой строке диапазона поиска (table_array). Этот аргумент может быть значением, которое вы вводите в формулу, или ссылкой на ячейку, содержащую искомое значение.

<р>2. table_array: Где находится таблица поиска, в которой значения поиска находятся в первой строке? Это может быть ссылка на диапазон, имя диапазона или имя таблицы с двумя или более столбцами.

<р>3. row_index_num: В таблице поиска (table_array), какая строка имеет значения, которые должен возвращать HLOOKUP? Этот номер может отличаться от номера строки рабочего листа, если таблица поиска не начинается в строке 1 рабочего листа.

Необязательный аргумент

Вот необязательный аргумент для HLOOKUP и его порядок в синтаксисе функции:

<р>4. [range_lookup]: Должен ли HLOOKUP находить точное или приблизительное совпадение? Существует три варианта ввода этого аргумента:

Опустить: если этот аргумент не указан, функция ГПР ищет приблизительное совпадение.
TRUE: найти приблизительное соответствие искомому значению. Значения в первой строке должны быть отсортированы в порядке возрастания слева направо. Примечание. Вместо TRUE можно ввести 1 (один)
FALSE: найти точное совпадение для искомого значения. Примечание. Вместо FALSE можно ввести 0 (ноль)

Предупреждения HLOOKUP

Функция ГПР может работать медленно, особенно при поиске точного совпадения текстовой строки в несортированной таблице. Чтобы получить лучшие результаты, попробуйте следующие предложения:

По возможности используйте таблицу поиска, отсортированную по значениям в первой строке в порядке возрастания слева направо, и используйте приблизительное совпадение вместо точного.
Используйте функцию ПОИСКПОЗ или функцию СЧЁТЕСЛИ, чтобы сначала проверить значение и убедиться, что оно находится в первой строке таблицы.
Используйте более быстрые функции, такие как ИНДЕКС и ПОИСКПОЗ или XLOOKUP, для возврата значений из таблицы.

Точное соответствие HLOOKUP

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

название региона вводится в ячейку B7
таблица поиска регионов состоит из двух строк и находится в диапазоне C2:F3
Общая сумма продаж указана во второй строке таблицы.
ЛОЖЬ используется в последнем аргументе, чтобы найти точное совпадение с искомым значением.

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

=ГПР(B7,C2:F3,2,ЛОЖЬ)

Приблизительное соответствие HLOOKUP

Обычно при использовании ГПР требуется точное совпадение, но иногда приблизительное совпадение работает лучше. В этом примере ставки меняются в начале каждого квартала, и эти даты вводятся в качестве заголовков столбцов.

Установив HLOOKUP для приблизительного совпадения, вы можете найти курс, действовавший на любую дату. В этом примере:

дата вводится в ячейку C5
таблица поиска ставок состоит из двух строк и находится в диапазоне C2:F3
таблица поиска отсортирована по строке даты в порядке возрастания
скорость находится во второй строке таблицы.
TRUE используется в последнем аргументе, чтобы найти приблизительное соответствие искомому значению.

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

=HLOOKUP(C5,C2:F3,2,ИСТИНА)

Если точная дата не найдена в первой строке таблицы поиска, формула ГПР возвращает скорость для следующей наибольшей даты, которая меньше искомого_значения.

Значением поиска в этом примере является 15 марта. Эта дата отсутствует в строке даты, поэтому возвращается значение на 1 января (0,25).

ГПР с 2 критериями

В предыдущих примерах в таблице поиска было 2 строки:

Значения поиска в строке 1.
Значения, возвращаемые в строке 2.

В этих формулах HLOOKUP порядковый номер строки (2) вводился в формулу в качестве третьего аргумента. Например:

Вычисление номера индекса строки

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

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

В этой таблице поиска количества продаж продукта:

Существует столбец для каждого из 4 регионов.
Для названий предметов есть две строки: столы и стулья.
Проданное количество вводится для каждого региона и продукта.
Есть 2 ячейки критериев: "Регион" (B7) и "Элемент" (C7).

Найти правильную строку для выбранного элемента

Если бы нам всегда требовалось количество стульев, номер строки (3) можно было бы ввести в формулу ГПР:

В данном случае мы хотим найти правильный порядковый номер строки на основе имени элемента в ячейке C7. Для этого используйте функцию ПОИСКПОЗ, чтобы найти позицию выбранного элемента в ячейках B2:B4.

Вот формула в ячейке D7, где результат ГПР основан на 2 критериях, с ПОИСКПОЗОМ в 3-м аргументе:

=ГПР(B7,C2:F4, ПОИСКПОЗ(C7,B2:B4,0), ЛОЖЬ)

Совет. Чтобы узнать больше о функции ПОИСКПОЗ и увидеть другие примеры ее использования, перейдите на страницу ИНДЕКС и ПОИСКПОЗ.

ГПР и СЧЁТЕСЛИ

Как упоминалось в разделе "Предупреждения HLOOKUP" (выше), функция HLOOKUP может работать медленно, особенно если вы ищете точное совпадение для текстовой строки в несортированной таблице.

В этом примере используются следующие методы для ускорения работы HLOOKUP:

Имена регионов в строке заголовка таблицы отсортированы по алфавиту.
Функция ГПР использует приблизительное совпадение (ЛОЖЬ)
Функция СЧЁТЕСЛИ сначала проверяет значение, чтобы убедиться, что оно находится в первой строке таблицы.

Вот таблица поиска с этой формулой ГПР/СЧЁТЕСЛИ в ячейке C5:

=ЕСЛИ(СЧЁТЕСЛИ(C2:F2,B5),ПРПР(B5,C2:F3,2,ИСТИНА),"Не найдено")

Как работают формулы ГПР и СЧЁТЕСЛИ

1) Во-первых, функция СЧЁТЕСЛИ подсчитывает, сколько раз слово "Центральный" встречается в ячейках C2:F2

СЧЁТЕСЛИ(C2:F2,B5)
- ПРИМЕЧАНИЕ. Это краткая версия проверки того, больше ли счетчик нуля.
- Можно использовать более длинную версию, чтобы упростить понимание этой части формулы: СЧЁТЕСЛИ(C2:F2,B5)>0
2) Затем функция ЕСЛИ проверяет результат функции СЧЁТЕСЛИ

3) Затем функция ЕСЛИ возвращает свой результат:
- Если результат СЧЁТЕСЛИ был ЛОЖЬ, результатом ЕСЛИ будет "Не найдено"
- Если результат СЧЁТЕСЛИ был ИСТИНА, отображается результат функции ГПР.
Ошибки ГПР

Иногда формула ГПР возвращает значение ошибки вместо ожидаемого результата. Вот некоторые значения ошибок и их значение:

Вы можете получить эту ошибку, если аргумент row_index_number ссылается на ячейку на листе, и эта ячейка пуста или содержит ноль.

Вы можете получить эту ошибку, если строки были удалены из таблицы поиска, а аргумент row_index_number не был обновлен.

Вы можете получить эту ошибку, если в искомом значении есть опечатка или если некоторые значения являются реальными датами или числами, а другие – текстовыми датами или числами. См. пример в следующем разделе — Проблема: совпадение дат или чисел.

Проблема: совпадение дат или чисел

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

Таблица поиска с датами

В этом примере есть таблица поиска с
- Даты в строке заголовка
- Цели продаж и % бонуса за каждую дату
Этот список отформатирован как таблица Excel и называется RatesLU.

Формула HLOOKUP с датами

Под таблицей поиска есть 2 ячейки с формулами HLOOKUP, чтобы найти цель (строка 2) и процент бонуса (строка 3) для даты, введенной в ячейку B8.

Совет. Дополнительные сведения о ссылках на структурированные таблицы см. на сайте Microsoft.

Ошибки формулы HLOOKUP

Проблема HLOOKUP с датами

В этом примере возникла проблема с сопоставлением дат, потому что:
- Дата в ячейке B8 сохраняется как ЧИСЛО.
- Даты в строке заголовка таблицы поиска являются текстовыми значениями.
Excel рассматривает числовые и текстовые даты как разные значения, даже если они выглядят одинаково.

Заголовки таблиц всегда текстовые

Изначально даты в строке 3 вводились как реальные даты (числа). Однако, когда таблица поиска была отформатирована как именованная таблица Excel, они автоматически изменились на текст.
- Предупреждение. Excel автоматически форматирует даты и числа как ТЕКСТ, если они находятся в строке заголовка именованной таблицы. Это изменение вносится автоматически, без уведомления.
Исправить проблему HLOOKUP с датами

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

В каждой формуле введите два знака минус (двойной унарный) перед ссылкой на таблицу поиска:

Как это работает
1. Знак минус — это оператор, поэтому добавление этого вычисления преобразует текстовые числа в действительные числа.
2. Второй знак минус изменяет результаты на положительные числа.
Другие проблемы HLOOKUP

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

Загрузить образец файла

Чтобы ознакомиться с формулами, используемыми в этих примерах, загрузите пример рабочей книги функции ГПР. Заархивированный файл Excel имеет формат xlsx и не содержит макросов.

Читайте также: