Функции ГПР в Excel помогают эффективно работать с табличными данными. Эта полезная функция часто упускается пользователями, хотя может сэкономить много времени. Давайте разберемся, что такое ГПР, как ее использовать и для чего она нужна. После прочтения этой статьи вы научитесь применять ГПР в своих Excel таблицах.
Что такое функция ГПР в Excel и для чего она нужна
Функция ГПР (горизонтальный поиск) в Excel предназначена для поиска заданного значения в первой строке таблицы и возвращения значения из строки, определяемой третьим аргументом.
Отличие ГПР от других поисковых функций (например функция впр и гпр в excel) в том, что она осуществляет поиск не по столбцу, а по строке. Поэтому ее удобно использовать в случае, когда сравниваемые значения расположены в первой строке, а возвращаемые значения – в строках ниже.
Функцию ГПР имеет смысл применять в следующих ситуациях:
- Необходимо получить данные из таблицы по какому-либо идентификатору
- Требуется создать компактную форму для выборки данных из большой таблицы
- Нужно выполнить lookup горизонтально, а не вертикально
К основным преимуществам ГПР относятся:
- Высокая скорость работы по сравнению с вложенными циклами
- Удобство использования вместе с другими функциями, такими как IF, VLOOKUP и т.д.
- Возможность работы как с числовыми, так и текстовыми данными
Синтаксис и аргументы функции ГПР
Синтаксис функции ГПР выглядит следующим образом:
ГПР(искомое_значение; таблица; номер_строки; [интервальный_просмотр])
Рассмотрим подробнее каждый из аргументов:
- Искомое_значение – это то значение, которое мы ищем в первой строке таблицы. Может быть числом, текстом или ссылкой на ячейку.
- Таблица – диапазон ячеек, в котором находится таблица данных. Обычно это имя диапазона или ссылка на ячейки, например A1:D10.
- Номер_строки – номер строки таблицы, значение из которой будет возвращено в случае совпадения в первой строке. Нумерация строк начинается с 1.
- Интервальный_просмотр – необязательный логический аргумент. Если равен ИСТИНА или опущен, возвращается ближайшее меньшее или равное значение. Если ЛОЖЬ – только точное совпадение.
При использовании функции ГПР в Excel следует иметь в виду некоторые особенности:
- Первая строка таблицы должна быть отсортирована по возрастанию
- Регистр текста не учитывается
- Функция ГПР работает значительно быстрее, если аргумент «интервальный_просмотр» равен ИСТИНА
Для повышения производительности рекомендуется использовать именованные диапазоны и ячейки вместо «жестких» ссылок на адреса.
Примеры использования функции ГПР в Excel
функции гпр в excel позволяют решать множество задач при работе с данными. Рассмотрим несколько примеров.
Допустим, у нас есть таблица данных о сотрудниках:
| Имя | Возраст | Отдел | Оклад |
| Иван Иванов | 35 | Продажи | 45 000 |
| Мария Сидорова | 29 | Маркетинг | 38 000 |
Чтобы найти оклад сотрудника по его имени, можно воспользоваться функцией:
=ГПР("Мария Сидорова";$A$1:$D$3;1;0)
В результате будет возвращено значение 38 000.
Другой пример – создание компактной формы выбора данных из таблицы. Предположим, у нас есть объемная база клиентов со следующими данными:
| ID | Имя | Город | Покупки |
| 1 | Иван Петров | Москва | 12 500 |
| 7 | Анна Кузнецова | Санкт-Петербург | 48 000 |
Чтобы получать данные по ID клиента, достаточно создать выпадающий список с ID и рядом записать формулу функции гпр в excel примеры:
=ГПР(B2;$A$1:$D$3;1;0)
Таким образом, при выборе ID из списка будут выводиться соответствующие имя и город клиента.
Еще один распространенный пример – получение данных из таблицы доходов и расходов. Предположим, у нас есть такая таблица за год:
| Месяц | Доходы | Расходы |
| Январь | 150 000 | 120 000 |
| Февраль | 160 000 | 100 000 |
С помощью ГПР можно легко рассчитать прибыль или убыток за любой месяц. Для этого достаточно создать выпадающий список с месяцами и рядом написать формулу:
=ABS(ГПР("Доходы";$A$1:$C$3;1;0)-ГПР("Расходы";$A$1:$C$3;1;0))
ABS вернет абсолютное значение разницы доходов и расходов. Таким образом, в зависимости от выбранного в списке месяца будет выводиться его финансовый результат.
Основные правила и рекомендации по работе с ГПР
Чтобы избежать ошибок при использовании функции гпр excel чайников и повысить эффективность работы, рекомендуется придерживаться следующих правил:
- Первая строка таблицы данных должна быть отсортирована по возрастанию
- Следите за регистром текста в ячейках
- Всегда указывайте аргумент «интервальный_просмотр» равным ИСТИНА
- Используйте именованные диапазоны вместо жестких ссылок на ячейки
Для чайников рекомендуется начинать с простых таблиц данных, постепенно усложняя задачи. Всегда проверяйте правильность работы формул на небольших выборках перед применением к большим массивам данных.
Комбинирование ГПР с другими функциями
Мощность функций гпр в excel проявляется в полной мере при использовании совместно с другими функциями, такими как:
- ЕСЛИ – для возврата альтернативных значений в зависимости от условия
- И – объединение нескольких условий
- ИЛИ – альтернатива между условиями
- ВПР – совместный вертикальный и горизонтальный поиск
Например, чтобы вернуть сообщение в случае отсутствия искомого значения в таблице, можно написать:
=ЕСЛИ(ГПР(A1;$B$1:$D$10;3;0)=""; "Не найдено"; ГПР(A1;$B$1:$D$10;3;0))
А вот формула с использованием ВПР и ГПР:
=ГПР(ВПР(A1;$A$1:$B$10;2;0);$C$1:$D$10;1;0)
Она вернет значение из столбца D таблицы, где в столбце A найдено значение ячейки A1, а в столбце B соответствует условию во вложенной ВПР.
Ответы на частые вопросы по ГПР
Новички часто задают следующие вопросы о функции гпр excel чайников:
- Почему функция возвращает ошибку #Н/Д?
- Как быстрее всего освоить работу с ГПР?
- Можно ли использовать ГПР для поиска по 2 колонкам?
Ответ на первый вопрос – скорее всего, не найдено точное совпадение значения в первой строке или указан неправильный номер строки.
Для быстрого освоения ГПР следует изучить примеры и потренироваться на небольших таблицах. Также полезно комбинировать ГПР с знакомыми функциями.
ГПР может выполнять поиск только по одной строке за раз. Для поиска по 2 колонкам нужно применять вложенные ВПР и ГПР.
