Функция ГПР в Excel ищет значение в первой строке таблицы и возвращает число из той же колонки, но из строки ниже. Формула выглядит так: =ГПР(искомое_значение;таблица;номер_строки;ЛОЖЬ). Например, =ГПР("Мар";B1:G4;2;ЛОЖЬ) найдёт столбец «Мар» в шапке и вернёт выручку за март из второй строки.
ГПР расшифровывается как «горизонтальный просмотр». Работает она как функция ВПР, только повёрнутая: ВПР ищет сверху вниз по столбцу, а ГПР слева направо по строке. Пригодится, когда месяцы, филиалы или артикулы идут по горизонтали. В строительстве ГПР означает график производства работ, а у геологов георадар, но здесь речь только о формуле Excel. Другие основные функции Excel собраны в отдельной шпаргалке.
CheckroiПодборка курсов по Microsoft Excel684 курса • 64 школыСравните цены, школы, программу и найдите выгодные предложения по обучениюСравнить→
Синтаксис функции ГПР
У ГПР четыре аргумента, три обязательных. Названия и правила взяты из справки Microsoft по функции ГПР.
| Аргумент | Что в него писать | Пример |
|---|---|---|
| искомое_значение | что ищем в первой строке: текст в кавычках, число или ссылку на ячейку | "Мар" или B6 |
| таблица | диапазон, в первой строке которого лежат заголовки | B1:G4 |
| номер_строки | какую строку таблицы вернуть, считая шапку первой | 2 |
| интервальный_просмотр | ЛОЖЬ для точного совпадения, ИСТИНА для приблизительного; если не указать, Excel считает ИСТИНА | ЛОЖЬ |
Номер строки считается внутри диапазона, а не по листу. Если таблица начинается с девятой строки листа, её шапка всё равно строка 1.
Как сделать ГПР в Excel: пошаговая инструкция
Возьмём отчёт по месяцам: в первой строке «Янв», «Фев», «Мар» и дальше до «Июн», ниже выручка, расходы и число заказов. Выручка указана в тысячах рублей. Нужно вытащить выручку за месяц, который записан в ячейке B6.
- Встаньте в ячейку, где нужен ответ, например B7.
- Наберите
=ГПР(и щёлкните ячейку B6 с названием месяца. - Поставьте точку с запятой и выделите таблицу вместе с шапкой, но без подписей слева:
B1:G4. - Укажите номер строки. Выручка лежит во второй строке таблицы, значит
2. - Последним аргументом напишите
ЛОЖЬ, закройте скобку и нажмите Enter. ЛОЖЬ стоит писать всегда, кроме шкал и интервалов, о них ниже.
Готовая формула: =ГПР(B6;B1:G4;2;ЛОЖЬ). В B6 стоит «Мар», формула вернула 510. Поменяйте месяц в B6 на «Май», и ответ сам станет 560.
Скриншоты сделаны в Google Таблицах: там функция тоже называется ГПР и работает так же. Отличие одно: аргументы на кадрах разделены запятой, потому что так настроен регион файла. В русском Excel между аргументами ставится точка с запятой.
Чтобы месяц не приходилось печатать руками, сделайте в B6 выпадающий список с названиями из шапки. Тогда опечатка вроде «Март» вместо «Мар» просто не случится.
Канал основателя Checkroi Вани Буявца3 700 человек читают мой Телеграм-канал про нейросетиСобрал промпты для Claude Code и ChatGPT, разборы ИИ-инструментов и лайфхаки по продвижению бизнеса в одном местеПрисоединитьсяПримеры ГПР для рабочих таблиц
Номер строки через ПОИСКПОЗ
Писать номер строки цифрой неудобно: вставили в отчёт строку «Возвраты», и все формулы съехали. Номер можно искать той же формулой. ПОИСКПОЗ находит позицию подписи в левом столбце и подставляет её в ГПР.
=ГПР(B6;$B$1:$G$4;ПОИСКПОЗ(A8;$A$1:$A$4;0);ЛОЖЬ)В A8 написано «Заказы». ПОИСКПОЗ находит это слово в четвёртой строке диапазона A1:A4 и отдаёт 4, ГПР берёт четвёртую строку таблицы. Для марта выходит 171 заказ.
Подписи слева и шапка таблицы должны начинаться с одной строки листа, иначе номера разъедутся на единицу. У нас оба диапазона начинаются с первой строки. Больше способов найти ячейку по значению есть в разборе поиска в Excel.
Шкала скидок с приблизительным поиском
ИСТИНА в последнем аргументе нужна, когда ищется не точное значение, а интервал. Классика: скидка зависит от суммы заказа. В первой строке пороги 0, 10 000, 30 000 и 50 000 ₽, во второй скидки 0 %, 3 %, 5 % и 8 %.
=ГПР(B13;B10:E11;2;ИСТИНА)Заказ на 32 500 ₽ точного совпадения в шапке не находит. Тогда ГПР берёт наибольший порог, который меньше искомого, то есть 30 000, и возвращает скидку 5 %. Дальше её можно перевести в рубли, как в инструкции про процент от числа.
- Пороги в первой строке обязаны идти по возрастанию слева направо, иначе ответ будет неверным.
- Первый порог ставьте 0. Если сумма окажется меньше самого маленького значения в шапке, ГПР вернёт #Н/Д.
Одна формула на несколько месяцев
Допустим, в квартальном отчёте в ячейках J1:L1 стоят «Апр», «Май», «Июн», и под каждым нужна выручка. Напишите формулу один раз в J2 и протяните вправо:
=ГПР(J$1;$B$1:$G$4;2;ЛОЖЬ)Знаки доллара закрепляют таблицу, а J$1 при протягивании превращается в K1 и L1. Получится 470, 560 и 605.
Поиск по части слова
При точном поиске в тексте можно ставить подстановочные знаки: звёздочка заменяет любое количество символов, вопросительный знак один символ. Формула =ГПР("Ма*";B1:G4;2;ЛОЖЬ) найдёт первый заголовок, который начинается на «Ма». В нашей таблице это «Мар», ответ 510. Регистр букв ГПР не различает.
Чем ГПР отличается от ВПР
Логика у функций одна, разное только направление. Если данные удобнее повернуть, таблицу можно транспонировать и работать привычной ВПР.
| ГПР | ВПР | |
|---|---|---|
| Где ищет | в первой строке таблицы | в первом столбце таблицы |
| Что возвращает | значение из строки ниже | значение из столбца правее |
| Третий аргумент | номер строки | номер столбца |
| Типичная таблица | месяцы, филиалы, годы по горизонтали | список товаров или сотрудников по вертикали |
| Английское название | HLOOKUP | VLOOKUP |
Ошибки ГПР и как их исправить
Большая часть проблем связана с тем, что значение в шапке выглядит так же, как искомое, а на деле отличается пробелом, буквой или форматом.
- #Н/Д: значения нет в первой строке. Проверьте написание («Март» против «Мар»), лишние пробелы и формат: число 2024, сохранённое как текст, с числом 2024 не совпадёт. В Google Таблицах эта же ошибка выглядит как #N/A. Какие значения не совпадают, быстро покажет сравнение двух столбцов.
- #ЗНАЧ!: номер строки меньше 1, например 0.
- #ССЫЛКА!: номер строки больше, чем строк в таблице. В диапазоне B1:G4 четыре строки, и номер 5 даст эту ошибку.
- Чужое число без ошибки: забыли ЛОЖЬ. Без четвёртого аргумента ГПР ищет приблизительно и ждёт отсортированную шапку, а месяцы по алфавиту не отсортированы. На нашей таблице формула
=ГПР("Мар";B1:G4;2)в Google Таблицах вернула 605, выручку за июнь, и никакой ошибки не показала. - Сползает диапазон: при копировании
B1:G4превращается вC1:H4. Закрепите его знаками доллара:$B$1:$G$4.
Если в отчёте #Н/Д допустим и нужно вместо него показать понятный текст, оберните формулу в ЕСЛИОШИБКА. Она из той же группы логических функций, что и ЕСЛИ:
=ЕСЛИОШИБКА(ГПР(B6;$B$1:$G$4;2;ЛОЖЬ);"нет такого месяца")Про сами значения ИСТИНА и ЛОЖЬ и то, как Excel сравнивает их в формулах, подробно написано в статье про знак «не равно».
Чем заменить ГПР: ПРОСМОТРX и ИНДЕКС с ПОИСКПОЗ
У ГПР два ограничения: она ищет только в верхней строке и возвращает только то, что ниже. Обе замены их снимают.
ПРОСМОТРX
Функция берёт строку, где искать, и строку, откуда вернуть ответ, отдельными диапазонами. Точное совпадение включено по умолчанию, а текст на случай, когда ничего не найдено, пишется прямо в формуле.
=ПРОСМОТРX(B6;B1:G1;B2:G2;"нет данных")Ответ для марта тот же, 510. По справке Microsoft о ПРОСМОТРX, функции нет в Excel 2016 и Excel 2019, она появилась в более новых версиях и в Microsoft 365. Если файл откроют в старом Excel, надёжнее оставить ГПР.
ИНДЕКС и ПОИСКПОЗ
Связка работает в любой версии Excel. ПОИСКПОЗ находит номер столбца с нужным месяцем, ИНДЕКС берёт значение из строки выручки:
=ИНДЕКС(B2:G2;ПОИСКПОЗ(B6;B1:G1;0))Такую формулу удобно использовать, когда сверяете два отчёта с разной структурой. Другие способы собраны в инструкции, как сравнить две таблицы в Excel.
Канал основателя Checkroi Вани БуявцаЗабирайте промпты и обучение по нейросетям в моём Телеграм-каналеБольше 3 700 человек уже применяют Claude Code, ChatGPT и другие нейросети в работе, учёбе, бизнесе и жизниПерейти в каналГПР в Google Таблицах, Р7-Офисе и Яндекс Таблицах
Microsoft с 4 марта 2022 года приостановила новые продажи в России. Установленный Excel работает как раньше, а в других редакторах функция называется так же:
- Google Таблицы: ГПР и HLOOKUP принимаются обе, все кадры этой статьи оттуда. С чего начать работу в сервисе, есть в гайде по Google Таблицам.
- Р7-Офис: синтаксис совпадает с Excel, шаги описаны в справке Р7 по функции ГПР.
- Яндекс Таблицы: те же четыре аргумента, примеры есть в справке Яндекс 360.
Если Excel на компьютере нет, файл с формулами можно открыть одним из способов из статьи про Excel онлайн.
Где освоить Excel системно
ГПР закрывает одну задачу: достать число из горизонтальной таблицы. Отчёты, сводные таблицы и формулы, которые не ломаются при каждом новом столбце, требуют системной практики. Чем занимается человек, который делает это профессионально, рассказали в материале о специалисте по Excel.
Ниже курсы из каталога курсов по Excel: от базовых формул до анализа данных.
| Курс | Школа | Стоимость со скидкой | В рассрочку | Длительность | Обзор курса от Checkroi |
|---|---|---|---|---|---|
| Excel Перейти на сайт курса |  Нетология | 31 900 ₽ | 1691 ₽/мес. | 8 недель | Обзор курса |
| Power BI & Excel PRO Перейти на сайт курса | Нетология | 38 500 ₽ | 3208 ₽/мес. | 2 месяца | Обзор курса |
| Excel для работы Перейти на сайт курса |  Эдюсон | 16 500 ₽ | 1375 ₽/мес. | 2 недели | Обзор курса |
| Excel + Google Таблицы с нуля до PRO Перейти на сайт курса |  Skillbox | 29 899 ₽ | 2965 ₽/мес. | 4 месяца | Обзор курса |
| Excel для аналитика Перейти на сайт курса |  Moscow Business Academy | 17 200 ₽ | 2866 ₽/мес. | 2 месяца | Обзор курса |
Больше программ — в полном каталоге курсов по Microsoft Excel
