Содержание:
10-й день марафона 30 функций Excel за 30 дней мы посвятим изучению функции ГПР (георадар). Эта функция очень похожа на ВПР (ВПР), только работает с элементами горизонтального списка.
Неудачная функция ГПР (GLOW) не так популярен, как его собрат, поскольку в большинстве случаев данные в таблицах расположены вертикально. Помните, когда вы в последний раз хотели выполнить поиск строки? А как насчет возврата значения из того же столбца, но расположенного в одной из строк ниже?
В любом случае, давайте дадим характеристики ГПР (ГПР) заслуженная минута славы и ознакомьтесь с информацией об этой функции, а также с примерами ее использования. Помните: если у вас есть интересные идеи или примеры, поделитесь ими в комментариях.
Функция 10: ПРОСМОТР
Функция ГПР (HLOOKUP) ищет значение в первой строке таблицы и возвращает другое значение из того же столбца таблицы.
Как я могу использовать функцию HLOOKUP (ХПР)?
Так как функция ГПР (HLOOKUP) может найти точное или приблизительное значение в строке, а затем:
- Найдите общий объем продаж для выбранного региона.
- Найдите показатель, актуальный для выбранной даты.
Синтаксис ГПР
Функция ГПР (HLOOKUP) имеет следующий синтаксис:
HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
ГПР(искомое_значение;таблица;номер_строки;интервальный_просмотр)
- искомое_значение (искомое_значение): значение, которое необходимо найти. Может быть значением или ссылкой на ячейку.
- таблица_массив (таблица): таблица поиска. Может быть ссылкой на диапазон или именованным диапазоном, содержащим две или более строк.
- row_index_num (номер_строки): строка, содержащая значение, возвращаемое функцией. Задается номером строки в таблице.
- диапазон_поиска (range_lookup): используйте FALSE или 0, чтобы найти точное совпадение; для приближенного поиска — TRUE (ИСТИНА) или 1. В последнем случае строку, в которой ищет функция, необходимо отсортировать по возрастанию.
Ловушки HLOOKUP (GPR)
Подобно ВПР (ВПР), функция ГПР (HLOOKUP) может работать медленно, особенно при поиске точного совпадения текстовой строки в несортированной таблице. По возможности используйте приблизительный поиск в таблице, отсортированной по первой строке в порядке возрастания. Сначала вы можете применить функцию МАТЧ (Более раскрыто) или COUNTIF (СЧЁТЕСЛИ), чтобы убедиться, что искомое значение существует даже в первой строке.
Другие функции, такие как ИНДЕКС (ИНДЕКС) и МАТЧ (MATCH) также можно использовать для получения значений из таблицы и они более эффективны. Мы рассмотрим их позже в нашем марафоне и увидим, насколько мощными и гибкими они могут быть.
Пример 1. Найдите объем продаж для выбранного региона.
Еще раз напомню, что функция ГПР (HLOOKUP) ищет значение только в верхней строке таблицы. В этом примере мы найдем итоги продаж для выбранного региона. Нам важно получить правильное значение, поэтому используем следующие настройки:
- Название региона вводится в ячейку B7.
- Региональная таблица поиска состоит из двух строк и охватывает диапазон C2:F3.
- Итоги продаж указаны во второй строке нашей таблицы.
- Последний аргумент имеет значение FALSE, чтобы найти точное совпадение при поиске.
Формула в ячейке C7:
=HLOOKUP(B7,C2:F3,2,FALSE)
=ГПР(B7;C2:F3;2;ЛОЖЬ)
Если название региона не найдено в первой строке таблицы, результат функции ГПР (георадар) будет #В (#Н/Д).
Пример 2. Поиск показателя для выбранной даты
Обычно при использовании функции ГПР (HLOOKUP) требует точного совпадения, но иногда более подходящим является приблизительное совпадение. Например, если показатели меняются в начале каждого квартала, а в качестве заголовков столбцов используются первые дни этих кварталов (см. рисунок ниже). В этом случае с помощью функции ГПР (HLOOKUP) и приблизительное совпадение, вы найдете показатель, актуальный на данную дату. В этом примере:
- Дата записывается в ячейку C5.
- Таблица поиска индикатора имеет две строки и находится в диапазоне C2:F3.
- Таблица поиска сортируется по строке даты в порядке возрастания.
- Показатели записаны во второй строке нашей таблицы.
- Последний аргумент функции имеет значение TRUE для поиска приблизительного совпадения.
Формула в ячейке D5:
=HLOOKUP(C5,C2:F3,2,TRUE)
=ГПР(C5;C2:F3;2;ИСТИНА)
Если дата не найдена в первой строке таблицы, функция ГПР (HLOOKUP) найдет ближайшее наибольшее значение, меньшее аргумента. искомое_значение (искомое_значение). В этом примере желаемое значение Март 15. Его нет в строке даты, поэтому формула примет значение 1 января и вернуться 0,25.
