=ГПР("Mar";A1:E3;2;ЛОЖЬ) (по-английски HLOOKUP) ищет Mar в первой строке A1:E3 и возвращает значение из второй строки того же столбца. Это ВПР, положенная на бок, для таблиц, где подписи идут вдоль верхней строки. Таблицы на этой странице показывают английскую запись, но формулы в них можно вводить и по-русски, с точкой с запятой.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Jan | Feb | Mar | Apr |
| 2 | Sales | 4,200 | 3,900 | 4,800 | 5,100 |
| 3 | Costs | 2,600 | 2,500 | 2,900 | 3,000 |
| 4 | |||||
| 5 | Month | Mar | |||
| 6 | Sales | 4,800 |
=ГПР(B5;A1:E3;2;ЛОЖЬ)B6 ищет Mar в строке 1, находит его в столбце D и возвращает строку 2 этого столбца: 4,800. Выберите Apr в B5, чтобы получить 5,100, или замените 2 в формуле на 3, чтобы получить затраты.
Синтаксис ГПР
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
lookup_value(искомое_значение): что найти в первой строке таблицы.table_array(таблица): таблица. ГПР ищет только в её верхней строке.row_index_num(номер_строки): какую строку вернуть, считая верхнюю строку первой. Число больше высоты таблицы даёт #ССЫЛКА! (по-английски #REF!); 0 даёт #ЗНАЧ! (#VALUE!).range_lookup(интервальный_просмотр):ЛОЖЬдля точного совпадения.ИСТИНАили ничего для приблизительного совпадения по отсортированной строке.
Регистр при поиске не учитывается (mar находит Mar), а с ЛОЖЬ искомое значение может содержать подстановочные знаки * и ?. Значение, которого нет в первой строке, возвращает #Н/Д (#N/A). Таблицы на этой странице показывают ошибки под английскими именами.
Приблизительное совпадение по строке
С ИСТИНА ГПР находит наибольший заголовок, который меньше искомого значения или равен ему. Первая строка должна быть отсортирована слева направо по возрастанию.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Weight from (kg) | 0 | 2 | 5 | 10 |
| 2 | Cost | $4.50 | $6.00 | $9.50 | $14.00 |
| 3 | |||||
| 4 | Parcel (kg) | 7 | |||
| 5 | Cost | $9.50 |
=ГПР(B4;B1:E2;2;ИСТИНА)7 кг нет среди заголовков. Наибольший заголовок, который не больше этого веса, 5, поэтому B5 возвращает $9.50. Замените B4 на 1,5, чтобы получить $4.50, или на 12, чтобы получить $14.00. Таблица в формуле B1:E2, а не A1:E2: она начинается с первого веса, чтобы текстовая подпись в A1 не попала в отсортированную строку.
ПРОСМОТРX по строке
В Excel 2021 и Microsoft 365 на смену ГПР пришла ПРОСМОТРX (XLOOKUP). Она принимает строку поиска и строку результата двумя диапазонами, поэтому считать номер строки не нужно, а диапазон результата высотой в несколько строк возвращает весь столбец.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Jan | Feb | Mar | Apr |
| 2 | Sales | 4,200 | 3,900 | 4,800 | 5,100 |
| 3 | Costs | 2,600 | 2,500 | 2,900 | 3,000 |
| 4 | Profit | 1,600 | 1,400 | 1,900 | 2,100 |
| 5 | |||||
| 6 | Month | Feb | |||
| 7 | Figures | 3,900 | |||
| 8 | 2,500 | ||||
| 9 | 1,400 |
=ПРОСМОТРX(B6;B1:E1;B2:E4)Одна формула в B7 выводит три показателя за Feb вниз по B7:B9: 3,900, 2,500 и 1,400. Если бы в B8 или B9 что-то было, B7 показала бы #ПЕРЕНОС! (#SPILL!). Другие её возможности, например сообщение «не найдено» и последнее совпадение, описаны на странице ПРОСМОТРX.
Или переверните таблицу: ТРАНСП
Иногда лучше сделать вертикальную копию таблицы. =ТРАНСП(A1:D3) (TRANSPOSE) возвращает те же ячейки, поменяв строки и столбцы местами, и остаётся связанной с оригиналом. ВПР, ФИЛЬТР и диаграммы потом работают с ней как обычно.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Month | Jan | Feb | Mar |
| 2 | Sales | 4,200 | 3,900 | 4,800 |
| 3 | Costs | 2,600 | 2,500 | 2,900 |
| 4 | ||||
| 5 | Month | Sales | Costs | |
| 6 | Jan | 4,200 | 2,600 | |
| 7 | Feb | 3,900 | 2,500 | |
| 8 | Mar | 4,800 | 2,900 |
=ТРАНСП(A1:D3)A5 выводит блок 4 на 3: месяцы сбоку, Sales и Costs сверху. Замените продажи Feb в C2 на 4100, и копия изменится. Чтобы сделать разовую копию без формулы, выделите таблицу, скопируйте её, затем выберите «Главная > Вставить > Специальная вставка» и установите флажок «Транспонировать».
Практика: затраты за месяц
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Jan | Feb | Mar | Apr |
| 2 | Sales | 4,200 | 3,900 | 4,800 | 5,100 |
| 3 | Costs | 2,600 | 2,500 | 2,900 | 3,000 |
| 4 | |||||
| 5 | Month | Apr | |||
| 6 | Costs |
Ваша очередь: В B6 с помощью ГПР верните затраты за месяц из B5.
Часто задаваемые вопросы
Чем ВПР отличается от ГПР?
ВПР ищет вниз по первому столбцу таблицы и возвращает значение из столбца правее. ГПР ищет вправо по первой строке и возвращает значение из строки ниже. Аргументы те же, только вместо номера столбца номер строки.
Что такое номер строки в ГПР?
Номер строки, которую нужно вернуть, считая от первой строки таблицы, это строка 1. В =ГПР("Mar";A1:E3;3;ЛОЖЬ) 3 означает третью строку A1:E3. Число больше высоты таблицы возвращает #ССЫЛКА!.
Может ли ПРОСМОТРX заменить ГПР?
Да. ПРОСМОТРX работает в обоих направлениях: =ПРОСМОТРX("Mar";B1:E1;B2:E2) ищет в строке и возвращает значение из другой строки. Ей нужен Excel 2021 или Microsoft 365.
Почему ГПР возвращает #Н/Д?
Искомого значения нет в первой строке таблицы: опечатка, лишний пробел, число, сохранённое как текст, или значение, которое стоит в другой строке. С ИСТИНА в последнем аргументе #Н/Д возвращает и значение меньше первого заголовка.
