Практическое применение функции ГПР для выборки значений из таблиц по условию. Примеры использования функции ГПР для начинающих пользователей.
Описание
Выполняет поиск значения в первой строке таблицы или массива (Массив. Объект, используемый для получения нескольких значений в результате вычисления одной формулы или для работы с набором аргументов, расположенных в различных ячейках и сгруппированных по строкам или столбцам. Диапазон массива использует общую формулу; константа массива представляет собой группу констант, используемых в качестве аргументов.) значений и возвращает значение, находящееся в том же столбце в заданной строке таблицы или массива. Функция ГПР используется, когда сравниваемые значения расположены в первой строке таблицы данных, а возвращаемые — на несколько строк ниже. Если сравниваемые значения находятся в столбце слева от искомых данных, следует использовать функцию ВПР.
Буква Г в аббревиатуре «ГПР» означает «горизонтальный».
Дополнительная информация (источник)
Источник: http://ru.excelfunctions.eu/ГПР/Aнглийском
Что возвращает функция
Возвращает данные, которые вы хотите сопоставить по заданному значению из первой строки таблицы.
Источник: http://excelhack.ru/funkciya-hlookup-gpr-v-excel/
Пошаговые примеры работы функции ГПР в Excel
Пример 1. В таблице содержатся данные о клиента и их контактных номерах телефонов. Определить номер телефона клиента, id записи которого имеет значение 5.
Вид таблицы данных:
Для расчета используем формулу:
Описание аргументов:
- F1 – ячейка, содержащая название поля таблицы;
- A1:C11 – диапазон ячеек, в которых содержится исходная таблица;
- E2+1 – номер строки с возвращаемым значением (для id=5 – шестая строка, поскольку первая строка используется под шапку таблицы).
Возвращаемый результат:
В ячейке F2 автоматически выводится значение соответствующие номеру id в исходной таблице.
Источник: http://exceltable.com/funkcii-excel/primery-funkcii-gpr
Синтаксис
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) – английская версия
=ГПР(искомое_значение;таблица;номер_строки;[интервальный_просмотр]) – русская версия
Источник: http://excelhack.ru/funkciya-hlookup-gpr-v-excel/
Как пользоваться функцией ГПР в Excel: примеры
Для учебных целей возьмем такую табличку:
Применение ГПР на практике ограничено, так как горизонтальное представление информации используется очень редко.
Источник: http://exceltut.ru/funktsiya-hlookup-gpr-v-excel/
Как считать доходность инвестиций в Excel
В Excel для расчета доходности инвестиций с учетом ввода/вывода денег используется функция ЧИСТВНДОХ (XIRR) — это функция, которая возвращает внутреннюю ставку доходности для графика денежных потоков, которые не обязательно носят периодический характер. Как ей пользоваться? Возьмем пример из статьи Арсагеры:
- Инвестор купил акций на сумму 1000 рублей.
- Через 3 месяца он купил еще акций на 500 рублей.
- Еще через 4 месяца он продал часть акций на сумму 300 рублей.
- Через год после первоначального приобретения, стоимость акций составила 1300 рублей.
Доходность портфеля составила 8,004% годовых.
Введем эти данные в Excel. В первой колонке указываем суммы, во второй даты.
- В первой строчке указываем начальную сумму инвестиций 1000 рублей и дату инвестирования, к примеру 01.01.2014.
- Во второй строчке указываем ввод средств 500 рублей и дату 01.03.2014.
- В третьей строчке указываем вывод средств со знаком минус -300 и дату 01.04.2014.
- В четвертой строчке указываем стоимость портфеля на конец года со знаком минус -1300 и дату конец года 31.12.2014.
Теперь выбираем какую-нибудь пустую ячейку и жмем кнопку fx (вставить функцию). Находим функцию ЧИСТВНДОХ. Вводим значения ячеек. В строке «Значения» выбираем ячейки с суммами, в строке «Даты» — ячейки с датами. Теперь выбираем какую-нибудь пустую ячейку и жмем кнопку fx (вставить функцию). Находим функцию ЧИСТВНДОХ. Вводим значения ячеек. В строке «Значения» выбираем ячейки с суммами, в строке «Даты» — ячейки с датами. 
Жмем ОК, получаем доходность — 8,009% годовых.
Если бы мы считали по простой формуле, то получили бы результат (1300-1200)/1200=8,3%. Вроде бы разница небольшая, но в других примерах разница может составить несколько процентов.
Функцию в ячейку так же можно вписать руками. Для этого в пустой ячейке впишите текст: =ЧИСТВНДОХ(A1:A4;B1:B4), номера ячеек укажите свои.
Источник: http://exceltut.ru/funktsiya-hlookup-gpr-v-excel/
Функция ВПР с несколькими условиями
Рассмотрим пример функции ВПР с несколькими условиями. У нас есть следующие исходные данные:
Функция ВПР в Excel – Таблица исходных данных
Пусть нам необходимо использовать функцию ВПР с несколькими условиями. Например, для поиска цены товара по двумя критериями: названию продукта и его типу.
- Для того чтобы использовать функцию ВПР с несколькими условиями необходимо вставить в начало дополнительный столбец, который будет хранить информацию с названием и типом товара.
Итак на листе «Цены» вставляем столбец и в ячейке А2 вводим следующую формулу:
=B2&C2
При помощи этой формулы мы сцепляем значение столбца «Продукт» и «Тип». Заполняем все ячейки.
Теперь таблица для поиска выглядит следующим образом:
Функция ВПР в Excel – Добавление вспомогательного столбца
- Теперь в ячейке С2 на листе «Продажи» напишем следующую формулу ВПР:
=ВПР(A2&B2;Цены!$A$1:$D$8;4;ЛОЖЬ)
Заполняем для остальных ячеек и в результате получаем цены для каждого продукта в соответствии с типом:
Источник: http://exceltut.ru/funktsiya-hlookup-gpr-v-excel/
Примеры использования функции ГПР в Excel
Еще больше полезных приемов в работе со списками данных и функциями в Excel вы узнаете в практическом курсе “От новичка до мастера Excel“. Успей зарегистрироваться по ссылке!
Источник: http://excelhack.ru/funkciya-hlookup-gpr-v-excel/
