Как правильно применять формулу ГПР в Excel
В MS Excel предусмотрен ряд инструментов для выборки значений в таблице. Для построчного поиска данных применяется функция ГПР. Этот инструмент может показаться сложным, особенно для начинающих пользователей. В данном материале детально рассмотрим, как работает ГПР в Excel и научим пользоваться данной функцией.
Описание функции
Функция возвращает значение, найденные по первой строке таблицы или диапазона. При этом поиск осуществляется в том же самом столбце. Отсюда следует, что функция ГПР используется при условии, что сравниваемые значения находятся в одной строке. При этом в русской версии программы функция называется ГПР, а английское название функции HLOOKUP. Это необходимо учесть, чтобы исключить возможные проблемы при использовании разных вариантов софта.
Определить, как работает данная формула, проще всего на примере. У нас есть таблица, в которой записаны данные о продажах товаров. Она состоит из 3 столбцов, в которых указаны ID товара, количество проданных за месяц единиц и стоимость одного изделия.
Функция ГПР в таблице Excel поможет найти значение из столбца «Объем продаж» или «Стоимость единицы» по значению ID. Функция очень похожая на ВПР, однако работает несколько иначе. Она ищет значение в горизонтальном направлении, т.е. по строке, в то время как ВПР используется для того, чтобы отыскать данные для заданного значения внутри указанного столбца.
На вход ГПР принимает следующие аргументы:
- Искомое значение — значение, которое нужно найти в первой строке. В нашем случае это заголовок столбца в шапке таблицы.
- Таблица — диапазон, по которому ведется поиск. Также можно указывать имя диапазона.
- Номер строки — порядковое число, соответствующее номеру строчки, из которой возвращается искомое значение.
Перечисленные аргументы являются обязательными. Есть также необязательный параметр — интервальный просмотр. При значении ИСТИНА, которое, к слову, стоит по умолчанию, программа осуществляет поиск примерных соответствий. При значении ЛОЖЬ осуществляется поиск только точных соответствий.
На первый взгляд может показаться, что функция ГПР очень сложная для практической реализации. На самом деле, если понять принцип ее работы и использовать приведенные пошаговые инструкции, применять данный инструмент нетрудно и очень удобно.
Способ применения
Чтобы разобраться, как работает ГПР функция в Excel, рассмотрим примеры ее использования. Они помогут узнать, как работает данный инструмент более детально. Выше уже был показан один из вариантов применения горизонтального поиска в таблице.
Задача состоит в том, чтобы получить значение из столбца «Стоимость» для определенного товара. В нашем случае для обозначения товаров используется идентификатор, прописанный в столбце А. Для примера, найдем стоимость для товара с идентификатором «33333». Сведения по данному товару записаны в таблице в строке под номером 6.
Применяем формулу ГПР следующим образом:
- Выделяем ячейку для записи. Далее вызываем функцию ГПР. Для этого нажимаем комбинацию +. Потом вводим в поисковой строке текст «ГПР».
Вызываем вставку функции и ищем ГПР - Далее вводим аргументы. В первую очередь указываем искомое значение. По условиям задачи нужно найти стоимость единицы товара. Можно прописать это значение вручную в скобках, либо указать аргумент в виде ссылки. Указываем необходимый диапазон для поиска. В нашем случае это вся таблица. После этого указываем номер строки с нужным наименованием. Товар с ID 33333 находится в 6 строке. Поэтому в качестве аргумента указываем цифру 6.
Прописываем аргументы - Заполнив обязательные поля, нажимаем . В результате этого в ячейке будет отображена стоимость, то есть задача решена верно.
Результат выполнения
К слову, для применения ГПР или других поисковых функций, необязательно использовать инструмент вставки. Можно записывать название формулы через равно непосредственно в саму ячейку, указывая аргументы в скобках. Данный способ хотя и не менее удобен, однако лучше подходит для опытных пользователей, которые разбираются в специфике синтаксиса.
Стоит обратить внимание также на один важный нюанс. Чтобы получить искомое значение, необходимо знать номер строки, в которой будет записан этот параметр. В нашем примере таблица небольшая и содержит только 10 строк. Поэтому поиск нужного значения не составляет труда. Если же их значительно больше, можно воспользоваться вспомогательной функций — СТРОКА. Она возвращает номер строчки по ссылке на нее. При ее использовании конструкция для горизонтального поиска будет выглядеть следующим образом.
Отметим, что при использовании этой формулы в Эксель могут возникать ошибки. Чаще всего это связано с неверным указанием аргументов. Пользователь может неверно указать искомое значение или диапазон для поиска. Еще одна возможная причина — наличие ссылочных значений. При наличии таких данных в клетках таблицы может возникать ошибка «#ССЫЛКА!».
Подводим итоги
Функция ГПР или HLOOKUP в Excel — один из инструментов для поиска данных в таблице. Формула принимает 3 обязательных аргумента, необходимых для нахождения данных в строке. Применять данную формулу целесообразно в том случае, если известна строка искомого объекта. По свойствам и синтаксису данная функция похожая на ВПР, с той разницей, что поиск выполняется в горизонтальном, а не в вертикальном направлении.
Загрузка...
