Сегодня мы с вами поговорим про одну интересную и довольно популярную функцию, которая имеет название «ГПР». Применяется она для того, чтобы найти значение, указанное в разделе аргументов. Оно должно находиться в указанном диапазоне ячеек. После того как данная область будет просмотрена, функция ГПР в excel вернет подходящее значение ячейки, расположенной в том столбце, на определенное количество строк ниже (определить число строк можно по третьему аргументу функции).
Сравнивая функцию ГПР с функцией ВПР, становится понятно, что между ними есть некоторые сходства. Например, у них одинаковый принцип работы, а синтаксическая запись тоже похожа. Но все же различия между двумя функциями есть – это то, в каком направлении они осуществляют поиск. Сегодня мы более подробно разберем отличия функции ГПР от ВПР, рассмотрим ее синтаксис и на наглядных примерах увидим случаи, в которых уместно ее применять.
На данном сайте вы найдете и другие статьи по работе с программой Excel.
Содержание
Функция ГПР подробное описание
Мы уже упоминали о том, что функции ВПР и ГПР в чем-то схожи. И это действительно так! Они выполняют почти одинаковые действия, но с одним маленьким отличием. ВПР используется пользователями Excel для того, чтобы провести вертикальный поиск. То есть она нужна для того, чтобы отыскать информацию из строк, первого столбца.
А вот ГПР – это про горизонтальный анализ. Функция ГПР применяется значительно реже, чем ее собрат ВПР. Однако несмотря на это, она остается важной функцией для работы в Excel.
Что делает ГПР функция в Excel? Она ищет указанное значение, в первой строке таблицы, по столбцам, после чего пользователю возвращается ячейка, из указанной другой строки, найденного столбца.
ВАЖНО! Если вам нужно сравнить значения, находящихся в других строках первого столбца, то тогда пригодится функция ВПР. Порой даже опытные пользователи Excel путаются.
Синтаксис функции ГПР
Теперь, когда мы разобрались с тем, где применяется функция ГПР, рассмотрим ее синтаксис.
Если коротко, то ее формула состоит из следующих пунктов:
=ГПР(Искомое_значение; Таблица; Желаемый_номер_строки;[В_каком_интервале_проводить_поиск])
Но все же, давайте подробнее поговорим о каждом аргументе, чтобы понимать основы этой записи.
Аргументы
Если так посмотреть, то функция ГПР не содержит в себе большое количество аргументов.
Сейчас мы разберем каждый из них:
«Искомое_значение» — Этот аргумент ни в коем случае нельзя пропускать, так как без него формула попросту не заработает. Здесь мы обозначением, что мы хотим найти. Это значение может быть абсолютно любого типа (ссылка, текст или число).
«Таблица» — Как и в прошлый раз, данный аргумент нельзя пропускать. Пользователю обязательно нужно указать таблицу, в которой программе нужно начать поиск указанных данных. Ввести здесь можно как ссылку на требуемый диапазон, так и наименование самого диапазона. И еще несколько важных моментов, касательно этого аргумента:
- Вписать в этом аргументе разрешается как и текст, так и число. В крайних случаях это может быть логическое значение.
- Текстовые строки не будут учитывать регистр букв. Это значит, что не имеет значения, будете ли вы писать «ЗНАЧЕНИЕ» либо «значение». Результат будет одинаковым.
- Каждое значение программа отсортирует с левой стороны в правую по возрастанию.
- Предположим, что аргумент «Интервальный-просмотр» (который мы ниже обсудим) – это «ИСТИНА», тогда первую строку аргумента «Таблица», нужно расположить в порядке возрастания. То есть: [-2; -1; 0; 1, …, 4, 5], [A, B, C, …, Z], [ЛОЖЬ, ИСТИНА]. Если не соблюдать это правило, то тогда итоговый результат может быть неверным. Однако, если данный аргумент — это ЛОЖЬ, то тогда таблицу вовсе не обязательно сортировать.
«Номер_строки» — Опять же, пропускать данный аргумент нельзя, иначе вся формула не будет работать! Здесь пользователю нужно указать «номер_строки» из которой программа должна вернуть значение найденного из первой строки в найденном столбце «Таблицы».
Допустим, мы указали число 5, то это будет означать, что нам вернется значение из пятой строки, указанной «Таблицы».
В то же время, есть один важный момент. НЕЛЬЗЯ указывать значение ниже «1». В противном случае функция ГПР всегда будет возвращать ошибку «#ЗНАЧ!». А если вы укажете число, которое превышает количество созданных строк (допустим, в вашей таблице 10 строк, а вы указали число «17»), то формула вернет ошибку «#ССЫЛ!».
— «В_каком_интервале_проводить_поиск». Вот мы и добрались до последнего аргумента. И интересно, что именно его необязательно указывать. Данную часть вы можете писать в том случае, если вам нужен более детальный поиск, либо, наоборот приблизительный. Написав значение ИСТИНА или ничего не указав, функция ГПР будет искать примерную схожесть с желаемым результатом. А указав ЛОЖЬ, будет осуществляться более детальный поиск, а в итоге вам вернется результат со 100% схожестью. Если же программа не найдет точного повтора, вам вернется ошибка «#Н/Д».
Вот мы и разобрали все главные и необязательные аргументы функции. Но, прежде чем идти дальше, стоит сделать еще несколько примечаний, к указанной выше информации!
Замечания
Некоторые моменты, которые стоит упомянуть перед тем, как применить полученные знания на деле:
— Когда у функции ГПР не получается отыскать «Искомое_значение», а аргумент «В_каком_интервале_проводить_поиск» имеет значение ИСТИНА, то она воспользуется наибольшим из имеющих значений, которые меньше, чем «Искомое_значение».
— В тех случаях, когда значение у «Искомое_значение», меньше, чем самое маленькое значение аргумента «Таблица, формула будет возвращать ошибку «#Н/Д».
— Когда в аргументе «В_каком_интервале_проводить_поиск» указано значение ЛОЖЬ, а аргумент «Искомое_значение» указан в виде текста, то в этом случае разрешается воспользоваться подстановочными знаками, такими как вопросительным знаком и звездочкой. «?» — будет обозначать любой один знак, а «*» — означает любую последовательность знаков. Если же пользователь хочет отыскать значение с такими же знаками, то перед их использованием ставится тильда (~).
Рассмотрим пример. Допустим, вам нужно найти следующее значение «Капуста?». То в «Искомом_значении» пишем следующее «Капуста~?». Благодаря этому программа выдаст желаемый результат. Прописав в аргументе «Капуста?» — будет дан не тот результат, который вы ожидаете…
Особенности использования функции ГПР
Из-за того, что функция ГПР в Excel так сильно схожа по принципу работы с ВПР. Она используется намного реже. Кроме того, многие пользователи и вовсе могут забыть про ее существование. Однако не стоит, недооценивает ее преимущества с остальными формулами. Давайте на примере рассмотрим ее особенности и преимущества.
Предположим, у нас имеется следующая таблица:
Мы имеем таблицу с номерами, именами клиентов и товарами, которые они заказали. Нам требуется отыскать наименование товара, в котором нуждается клиент под номером 5. Для этого случая отлично подходит функция ГПР. Для этого кликаем по ячейке с вопросом и пишем формулу:
=ГПР(«Товар»;A1:C16;6)
Как видно, мы смогли получить желаемое значение. Но почему вместо значения «5», было указано «6», а программа взяла и подставила нужное значение? Неужели магия и волшебство сыграли свою роль? Вовсе нет. Все дело в том, что наша таблица имеет «шапку» (под шапкой имеется в виду строка, которая содержит в себе подписи «Товар», «Клиент»). Учитывая этот факт, нужно было прибавить к итоговому значению +1.
ГПР для выборки по нескольким условиям
А давайте теперь усложним нашу задачу) В пример возьмем всю ту же таблицу с товарами и клиентами. Но немного поменяем условия нашей задачи. Сможет ли Excel функция ГПР справиться?
Сама таблица нисколько не поменяла, а вот условия претерпело изменения. Теперь нам нужно найти не только наименование товара, но, а также и имя клиента, который сделал заказ. Кажется, что сделать это несколько сложнее, чем в прошлый раз. Но на деле все гораздо проще, чем кажется. Пройдемся по необходимым шагам.
- Давайте вначале, чтобы нам было удобнее, мы сделаем в ячейке E3 выпадающий список. Чтобы сделать, это переходим в раздел «Данные», далее кликаем по пункту «Работа с данными и после переходим в «Проверка данных».
- После этого будет открыто окошко под названием «Проверка вводимых значений. В «Типе данных» мы указываем, что нам нужен «Список». А в разделе «Источник» выделяем диапазон ячеек, который содержит в себе «Номер товара» (это диапазон от A2 до A16). После кликаем на кнопку «ОК».
- Отлично! Мы выполнили большую часть работы. Теперь нам требуется понять имя клиента, которому требуется товар. Для этого применяем формулу ГПР:
=ГПР(«Клиент»;A1:C16;E3+1)
Что означает «+1» в этой формуле? Не забываем про то, что к нашему значению все так же требуется добавлять +1, из-за наличия шапки в таблице. Чтобы функция работала правильно, нам нужно выполнять эту манипуляцию. Что же в итоге мы получаем в ответе?
И да, все отлично сработало! Но у нас остались еще вопросы. Какой товар ищет наша Александра?
- Осталось только найти наименование товара. Для этого мы пишем следующую формулу:
=ЕСЛИ(ГПР(«Товар»;A1:C16;E3+1)=»»;»Не указан»;ГПР(«Товар»;A1:C16;E3+1))
Уф, как запутанная формула получилось. Кроме того, кроме функции ГПР, мы также воспользовались функцией ЕСЛИ. Что же означает эта формула? Функцией ЕСЛИ мы воспользовались, чтобы она проверяла возвращаемое значение. Когда наша ячейка не содержит в себе никаких данных, то есть пустая, то программа вернет в ответ строку «Не указан». В итоге мы сможем получить желаемый ответ. Давайте проверим, так ли это:
Да! Все получилось. Результат получился именно таким, какой мы и ожидали. Как видно, даже имея несколько условий, мы смогли воспользоваться функцией ГПР.
Интерактивный отчет для анализа прибыли и убытков в эксель
Теперь поговорим о другом примере. Предположим, что имеется таблица с месяцами и доходами/расходами. И когда год закончился, настало время подсчитать итоги. В этом нам также может помочь функция ГПР. Рассмотрим все пошагово:
- Вначале для удобства делаем выпадающий список. Если вы забыли, как это сделать, то можете посмотреть выше.
- Теперь в ячейку F3 требуется вписать указанную ниже формулу:
=ABS(ГПР(«Доходы»;A1:C13;E3+1)-ГПР(«Расходы»;A1:C13;E3+1))
В этот раз мы видим новую, неизвестную функцию ABS. Благодаря ей возвращается абсолютное число, которое будет равно разнице результатов, которые мы получаем благодаря функции ГПР. Настало время проверить работу формулы и убедиться в ее продуктивности:
РИС.10
- Теперь нам осталось только, понесли мы убытки или же дело пошло в гору, и мы разбогатели в этом месяце. Для этого вписываем эту формулу:
=ЕСЛИ(ГПР(«Доходы»;A1:C13;Е3+1)>ГПР(«Расходы»;A1:C13;E3+1);»Прибыль»;»Убыток»)
Благодаря функции ЕСЛИ программа сравнит значения, которые, вернутся функцией ГПР и нам будет указано один из вариантов текста, которые мы прописали в формуле. Итак, проверяем:
Как видно, функция ГПР отлично справляется даже с такими задачами, и преимущества ее использования заметны сразу.
Пример 1 – Одно значение
Давайте для примера еще раз проверим формулу в работе. Для начала разберем пример с одним значением. Возьмем таблицу с названиями городов и количеством населения в них.
Допустим, нам с вами нужно узнать количество населения в городе Краснодаре. В этом нам отлично поможет формула ГПР. Воспользуемся формулой ниже для нахождения ответа:
=ГПР(«Города»;A1:С11;F3)
Пример 2 – Несколько значений
А теперь разберем пример, когда имеется сразу несколько значений. Воспользуемся таблицей, в которой у нас имеются клиенты и их номера.
Вначале мы хотим найти имя клиента под номером 3. Для этого вписываем следующую формулу:
=ГПР(«Клиент»;A1:C10;F3+1)
Получаем следующий результат:
У программы верно получилось вычислить нужное имя, а значит нам осталось только узнать номер этого клиента. Применяем следующую формулу и получаем желаемый результат:
=ЕСЛИ(ГПР(«Телефон»;A1:C110;F3+1)=»»;»Не указан»;ГПР(«Телефон»;A1:C10;F3+1))
Более подробно рассмотреть эту формулу можно выше, где мы разбирали каждый аргумент. Смотрим на полученный итог:
Видеоролик примера ГПР
Как видим, все работает на отлично! Если подвести итог, то становится понятно, что функция ГПР очень полезная для любого пользователя Excel. Главное найти ей верное применение. В примерах, которые мы рассматривали выше, были маленькие таблицы, из-за чего могло показаться, что применение этой функции не оправдано и намного лучше было бы сделать это вручную. Но представим, что в таблице 100+ строк, было бы тогда удобно вручную искать нужное значение? Как кажется – нет. Именно поэтому очень полезно знать о наличии функции ГПР и применять ее на деле!
