- Синтаксис функций ВПР и ГПР
- Как пользоваться функцией ВПР в Excel: примеры
- Как пользоваться функцией ГПР в Excel: примеры
- Как же конкретно работает формула ВПР
- Следствия для формул вида I
- Следствия для формул вида II
- Как считать доходность инвестиций в Excel
- Расчет внутренней ставки доходности облигаций в Excel
- Пример расчёта
- Функция ВПР с несколькими условиями
- Функция ВПР в Excel – Таблица исходных данных
- Функция ВПР в Excel – Добавление вспомогательного столбца
- Аргументы функции
- Дополнительная информация
- Интерактивный отчет для анализа прибыли и убытков в Excel
- Особенности использования функции ГПР в Excel
- Найти значения продаж для выбранного региона
- Найти показатель для выбранной даты
Синтаксис функций ВПР и ГПР
Функции имеют 4 аргумента:
- ЧТО ищем – искомый параметр (цифры и/или текст) либо ссылка на ячейку с искомым значением;
- ГДЕ ищем – массив данных, где будет производиться поиск (для ВПР – поиск значения осуществляется в ПЕРВОМ столбце таблицы; для ГПР – в ПЕРВОЙ строке);
- НОМЕР столбца/строки – откуда именно возвращается соответствующее значение (1 – из первого столбца или первой строки, 2 – из второго и т.д.);
- ИНТЕРВАЛЬНЫЙ ПРОСМОТР – точное или приблизительное значение должна найти функция (ЛОЖЬ/0 – точное; ИСТИНА/1/не указано – приблизительное).
! Если значения в диапазоне отсортированы в возрастающем порядке (либо по алфавиту), мы указываем ИСТИНА/1. В противном случае – ЛОЖЬ/0.
Как пользоваться функцией ВПР в Excel: примеры
Для учебных целей возьмем таблицу с данными:
Формула | Описание | Результат |
Функция ищет значение ячейки F5 в диапазоне А2:С10 и возвращает значение ячейки F5, найденное в 3 столбце, точное совпадение. | ![]() |
|
Нам нужно найти, продавались ли 04.08.15 бананы. Если продавались, в соответствующей ячейке появится слово «Найдено». Нет – «Не найдено». | ![]() |
|
Если «бананы» сменить на «груши», результат будет «Найдено» | ![]() |
|
Когда функция ВПР не может найти значение, она выдает сообщение об ошибке #Н/Д. Чтобы этого избежать, используем функцию ЕСЛИОШИБКА. | ![]() Мы узнаем, были ли продажи 05.08.15 |
|
Если необходимо осуществить поиск значения в другой книге Excel, то при заполнении аргумента «таблица» переходим в другую книгу и выделяем нужный диапазон с данными. | ![]() Мы захотели узнать, кто работал 8.06.15. |
|
Поиск приблизительного значения. | ![]() |
Это важно:
- Функция ВПР всегда ищет данные в крайнем левом столбце таблицы со значениями.
- Регистр не учитывается: маленькие и большие буквы для Excel одинаковы.
- Если искомое меньше, чем минимальное значение в массиве, программа выдаст ошибку #Н/Д.
- Если задать номер столбца 0, функция покажет #ЗНАЧ. Если третий аргумент больше числа столбцов в таблице – #ССЫЛКА.
- Чтобы при копировании сохранялся правильный массив, применяем абсолютные ссылки (клавиша F4).
Как пользоваться функцией ГПР в Excel: примеры
Для учебных целей возьмем такую табличку:
Формула | Описание | Результат | |
Поиск значения ячейки I16 и возврат значения из третьей строки того же столбца. | ![]() |
||
Еще один пример поиска точного совпадения в другой табличке. | ![]() |
Применение ГПР на практике ограничено, так как горизонтальное представление информации используется очень редко.
Как же конкретно работает формула ВПР
- Вид формулы I. Если последний параметр опущен или указан равным 1, то ВПР предполагает, что первый столбец отсортирован по возрастанию, поэтому поиск останавливается на той строке, которая непосредственно предшествует строке, в которой находится значение, превышающее искомое. Если такой строки не найдено, то возвращается последняя строка диапазона.
- Вид формулы II. Если последний параметр указан равным 0, то ВПР последовательно просматривает первый столбец массива и сразу останавливает поиск, когда найдено первое точное соответствие с параметром <ЧТО>, в противном случае возвращается код ошибки #Н/Д (#N/A).
Следствия для формул вида I
- Формулы можно использовать для распределения значений по диапазонам.
- Если первый столбец <ГДЕ> содержит повторяющиеся значения и правильно отсортирован, то будет возвращена последняя из строк с повторяющимися значениями.
- Если искать значение заведомо большее, чем может содержать первый столбец, то можно легко находить последнюю строку таблицы, что бывает довольно ценно.
- Данный вид вернёт ошибку #Н/Д, только если не найдёт значения меньше или равное искомому.
- Понять, что формула возвращает неправильные значения, в случае если ваш массив не отсортирован, довольно затруднительно.
Следствия для формул вида II
Если искомое значение встречается в первом столбце массива несколько раз, то формула выберет первую строку для последующего извлечения данных.
Как считать доходность инвестиций в 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), номера ячеек укажите свои.
Расчет внутренней ставки доходности облигаций в Excel
Пример 2. В таблице Excel внесены данные об операциях с облигацией (покупка, выплата купонов, погашение). Выплаты производились несистематично. Определить прибыль и внутреннюю ставку доходности облигации за указанный период.
Вид таблицы данных:
Для определения прибыли используем формулу:
=СУММ(C4:C21)-(C3*(-1))
То есть, определяем разницу между суммарными выплатами по купонам + погашением и расходами на приобретение облигации:
Для определения ставки доходности используем формулу:
=ЧИСТВНДОХ(C3:C21;B3:B21)
Результаты вычислений:
На практике подобные расчеты имеют смысл для достаточно больших периодов времени, поскольку на рынке зачастую возникают кризисные явления. Оптимальный период расчета доходности инвестиционного портфеля с использованием функции ЧИСТВНДОХ – от 5 лет и более.
Пример расчёта
Для расчёта нам будет необходима табличная программа. Это может быть Microsoft Excel, бесплатный OpenOfiice Calc, Apple Numbers, или какая-нибудь другая. Сперва нам будет необходимо подготовить таблицу с датами вводов и выводов на счёт, и суммами. Обычно такую таблицу легко получить из брокерского отчёта. Вводы на счёт необходимо ввести со знаком минус, а выводы ( доход) — со знаком плюс. В последней строке таблицы необходимо поставить текущую ликвидационную стоимость портфеля. Выглядеть это может примерно следующим образом:
Здесь приведён выдуманный пример инвестирования в течении шести лет. За весь срок на счёт было положено 475 тысяч рублей, а выведено 30 тысяч на срочные нужды. Далее нужно просто использовать функцию XIRR (ЧИСТВНДОХ по-русски), которая даст значение среднегодовой доходности. Первым параметром необходимо указать суммы, а вторым — даты. Посмотрим, какой у нас получится результат:
Оказалось, что доходность составила 9,35% годовых. На самом деле наш инвестор не был слишком успешен, ведь за указанный период его доходность была чуть больше средней официальной инфляции.
Теперь вы сможете посчитать доходность своих инвестиций даже в случае, если они делались в разное время, разными суммами, а также иногда совершались выводы денег. Полезно иногда делать такой расчёт, чтобы сравнивать свои результаты с инвестиционным планом, да и просто убеждаться, что был выбран правильный подход к инвестированию.
Функция ВПР с несколькими условиями
Рассмотрим пример функции ВПР с несколькими условиями. У нас есть следующие исходные данные:
Функция ВПР в Excel – Таблица исходных данных
Пусть нам необходимо использовать функцию ВПР с несколькими условиями. Например, для поиска цены товара по двумя критериями: названию продукта и его типу.
- Для того чтобы использовать функцию ВПР с несколькими условиями необходимо вставить в начало дополнительный столбец, который будет хранить информацию с названием и типом товара.
Итак на листе «Цены» вставляем столбец и в ячейке А2 вводим следующую формулу:
=B2&C2
При помощи этой формулы мы сцепляем значение столбца «Продукт» и «Тип». Заполняем все ячейки.
Теперь таблица для поиска выглядит следующим образом:
Функция ВПР в Excel – Добавление вспомогательного столбца
- Теперь в ячейке С2 на листе «Продажи» напишем следующую формулу ВПР:
=ВПР(A2&B2;Цены!$A$1:$D$8;4;ЛОЖЬ)
Заполняем для остальных ячеек и в результате получаем цены для каждого продукта в соответствии с типом:
Аргументы функции
- lookup_value (искомое_значение) – это искомое число, которое вы собираетесь искать в первой строке таблицы;
- table_array (таблица) – это диапазон таблицы, в которой вы будете искать данные. Аргументом может быть как ссылка на диапазон так и именной диапазон;
- row_index (номер_строки) – это номер строки из которой вы хотите найти и сопоставить данные;
Если аргумент row_index (номер_строки) равен “1”, это означает что функция выдаст результат из первой строки диапазона таблицы (из строки поиска).
Если row_index (номер строки) равен “2”, то функция выдаст результат из строки, следующей за первой строкой диапазона поиска.
- [range_lookup] ([интервальный_просмотр])– не обязательный аргумент. В нем вы указываете, нужно ли вам точное совпадение данных или приблизительное соответствие. “1” – приблизительное соответствие, “0” – точное совпадение.
Дополнительная информация
- Функция может сопоставить данные как приблизительно (1), так и точно (0);
- При приблизительном сопоставлении убедитесь, что список отсортирован в порядке возрастания (слева направо), иначе результат вычисления может быть неточным;
- Когда range_lookup (интервальный _просмотр) имеет значение TRUE (приблизительный поиск), данные сортируются по возрастанию:
- Если функция HLOOKUP (ГПР) не может найти значение, она возвращает наибольшее значение, которое меньше значения аргумента lookup_value (искомое_значение)
- Функция возвращает ошибку #N/A, если значение lookup_value (искомое_значение) меньше наименьшего значения диапазона данных;
- Если lookup_value (искомое_значение) является текстом, то в таком случае в функции могут использоваться подстановочные знаки (*,?).
Интерактивный отчет для анализа прибыли и убытков в Excel
Пример 3. В таблице хранятся данные о доходах и расходах мелкого предприятия по номерам месяцев. Создать форму для быстрого расчета абсолютного значения разницы доходов и расходов по номеру месяца, позволяющая определять, был ли закончен отчетный период с прибылью или убытком.
Вид таблиц данных с выпадающим списком в ячейке E2 (как сделать выпадающий список смотрите в примере выше):
В ячейку F2 запишем следующую формулу:
Функция ABS возвращает абсолютное число, равное разнице возвращаемых результатов функций ГПР.
В ячейке G2 запишем формулу:
Функция ЕСЛИ сравнивает возвращаемые функциями ГПР значения и возвращает один из вариантов текстовых строк.
Примеры расчетов:
Особенности использования функции ГПР в Excel
Функция имеет следующую синтаксическую запись:
=ГПР(искомое_значение;таблица;номер_строки;[интервальный_просмотр])
Описание аргументов:
- искомое_значение – обязательный аргумент, принимающий данные любого типа, характеризующие элемент первой строки константы массива или ячейку, расположенную в первой строке диапазона ячеек. При работе с именованными областями данных (списки, таблицы) в качестве данного аргумента указывают название поля (столбца), в котором содержится возвращаемое значение.
- таблица – обязательный аргумент, принимающий константу массива или ссылку на диапазон ячеек, в котором находится список или таблица. Если первой строкой диапазона является шапка таблицы с текстовыми названиями полей, при поиске требуемого названия поля не учитывается регистр символов. Для корректной работы функции ГПР необходимо, чтобы содержащиеся в первой строке значения были отсортированы слева направо.
- номер_строки – обязательный аргумент, характеризующийся числовым значением из диапазона целых положительных чисел, которое соответствует номеру строки относительно первой строки массива или диапазона ячеек, переданных в качестве аргумента таблица. Функция ГПР возвращает результат, взятый из строки таблицы с номером, указанным данным аргументом. Ошибки могут возникать в следующих случаях:
- В качестве аргумента номер_строки передано число <1. В этом случае будет возвращен код ошибки #ЗНАЧ!.
- Передано число, превышающее значение номера последней записи (строки) в таблице или массиве. Возвращаемое значение – код ошибки #ССЫЛ!.
- [интервальный_просмотр] – необязательный для заполнения аргумент, принимающий логические значения:
- ИСТИНА – поиск ближайшего меньшего значения в случае, если точное совпадение с искомым значением отсутствует. Используется по умолчанию, если аргумент явно не указан.
- ЛОЖЬ – поиск точного совпадения с искомым значением. Возврат кода ошибки #Н/Д, если точного соответствия нет.
Примечания:
- Если аргумент [интервальный_просмотр] явно не указан или имеет значение ИСТИНА, для получения корректных результатов работы функции ГПР первая строка таблицы или массива, переданных в качестве аргумента таблица, должна быть отсортирована по возрастанию значений слева направо. Для поиска точного совпадения (значение аргумента – ЛОЖЬ) сортировку можно не выполнять.
- Если все данные, хранящееся в первой строке массива или таблицы, превышают по значению данные, переданные в качестве аргумента искомое_значение, функция ГПР вернет код ошибки #Н/Д.
- При поиске точного соответствия (аргумент [интервальный_просмотр] = ЛОЖЬ) в таблицах с именованными столбцами (полями) можно использовать подстановочные знаки: «?» – замена одного символа, «*» – замена нескольких символов.
Найти значения продаж для выбранного региона
Еще раз напомню, что функция HLOOKUP (ГПР) ищет значение только в верхней строке таблицы. В этом примере мы найдём итоги продаж для выбранного региона. Нам важно получить правильное значение, поэтому используем такие настройки:
- Имя региона введено в ячейке B7.
- Таблица поиска по региону имеет две строки и занимает диапазон C2:F3.
- Итоги продаж находятся в строке 2 нашей таблицы.
- Последний аргумент имеет значение FALSE (ЛОЖЬ), чтобы найти точное совпадение при поиске.
Формула в ячейке C7 такая:
=HLOOKUP(B7,C2:F3,2,FALSE)
=ГПР(B7;C2:F3;2;ЛОЖЬ)
Если название региона в первой строке таблицы не найдено, результатом функции HLOOKUP (ГПР) будет #N/A (#Н/Д).
Найти показатель для выбранной даты
Обычно при использовании функции HLOOKUP (ГПР) требуется точное совпадение, но иногда приблизительное совпадение подходит больше. Например, если показатели меняются в начале каждого квартала, а в качестве заголовков столбцов используются первые дни этих кварталов (см. рисунок ниже). В таком случае, с помощью функции HLOOKUP (ГПР) и приблизительного соответствия, Вы найдёте показатель, который актуален для заданной даты. В этом примере:
- Дата записана в ячейке C5.
- Таблица поиска показателя имеет две строки и расположена в диапазоне C2:F3.
- Таблица поиска отсортирована по строке с датами по возрастанию.
- Показатели записаны в строке 2 нашей таблицы.
- Последний аргумент функции имеет значение TRUE (ИСТИНА), чтобы искать приближенное совпадение.
Формула в ячейке D5 такая:
=HLOOKUP(C5,C2:F3,2,TRUE)
=ГПР(C5;C2:F3;2;ИСТИНА)
Если дата не найдена в первой строке таблицы, функция HLOOKUP (ГПР) найдет ближайшее наибольшее значение, которое меньше аргумента lookup_value (искомое_значение). В данном примере искомое значение – 15 марта. Его в строке с датами нет, поэтому формула возьмет значение 1 января и вернет 0,25.
- https://exceltable.com/funkcii-excel/funktsii-vpr-i-gpr-v-excel
- https://Lifehacker.ru/vlookup/
- https://activeinvestor.pro/kak-rasschitat-dohodnost-investitsij-s-uchetom-vvoda-vyvoda-sredstv/
- https://exceltable.com/funkcii-excel/dokhodnost-denezhnyh-potokov-chistvndoh
- https://zen.yandex.ru/media/id/5c4c2029f6778500aeceb3e9/5cc85da7bf32e000b08b8a52
- https://naprimerax.org/funktciia-vpr-v-excel
- https://excelhack.ru/funkciya-hlookup-gpr-v-excel/
- https://exceltable.com/funkcii-excel/primery-funkcii-gpr
- https://office-guru.ru/excel/30xl30d-gpr-hlookup-133.html