Продолжаю серию уроков про встроенные функции Excel. На этот раз рассмотрим пару очень необходимых функций ВПР и ГПР, которые очень полезны при переносе данных из разных таблиц или поиска значений в больших таблицах. Например, у Вас имеется первая таблица со столбцами «Код товара», «Название товара», во второй таблице данные с продажами, в которых «Дата продажи», «Продавец», «Код товара», «Сумма товара». Нам необходимо во вторую таблицу подставить название товара по его коду. Если таблица с десятком строк, то ничего страшного, можно и вручную, а вот если в таблице несколько десятков тысяч строк, да еще и наименований товара несколько тысяч, то тут уж вручную очень долго придется мучиться, а ошибок будет еще сколько. Для Excel есть пара функций ВПР и ГПР. По принципу работы эти функции идентичны с той лишь разницей что, ВПР работает по вертикали, справа налево, а ГПР по горизонтали, сверху вниз.
Синтаксис функции ВПР и ГПР
Синтаксис функции ВПР и ГПР одинаков:
ВПР(искомое_значение; таблица; номер_столбца; интервальный_просмотр)
Искомое значение — поиск этого аргумента производится в первом столбце «таблица». Искомое значение может ссылаться на ячейку(и) или быть текстовой строкой.
Таблица — содержит информацию, в которой ведется поиск и из которой производится подстановка значения. Можно указывать ссылки на диапазоны ячеек листа.
Примечание: Значения, которые находятся в первом столбце аргумента «таблица», являются числовыми, логическими или текстовыми. При сравнении текстовых строк регистр букв не учитывается.
Номер_столбца — аргумент указывает номер столбца «Таблица» из которого будет возвращено значение.
Интервальный_просмотр — имеет параметры ИСТИНА или ЛОЖЬ. Если ИСТИНА(или пропущен), то будет возвращаться приблизительно-похожее значение. Если ЛОЖЬ, то будет поиск точного совпадения значения. Если значение не найдено, то функция возвратит значение #Н/Д.
Примеры работы функции ВПР() и ГПР()
Давайте теперь рассмотрим на примерах работу функций ВПР и ГПР!?
Пример 1. Функция ВПР
В таблице «Журнал продаж», содержится несколько полей: «Дата», «Код Товара», «Кол-во». В таблице «Номенклатура» поля: «Код Товара» и «Наименование товара». Необходимо в таблицу «Журнал продаж», по коду товара, добавить наименование товара.
Таблица имеет следующие значения:
Добавим пустой столбец в таблицу «Журнал продаж» между столбцами «Код Товара» и «Кол-во». Ставим в курсор в новый столбец, ячейка С2 и жмем добавить функцию. Функция ВПР находится в категории «Ссылки и массивы», выбираем функцию ВПР и задаем ей следующие параметры:
Обратите внимание на аргумент «Таблица», для того чтобы при «протяжке» формулы ниже по столбцу, адрес на диапазон в таблице «Номенклатура» не «съехал», его необходимо зафиксировать. Фиксация ячеек осуществляется знаком «$». Мы фиксируем и столбцы и строки. Все. Жмем ОК и протягиваем формулу.
Результат:
Работа функции ГПР аналогична ВПР. Ознакомиться с ее принципом работы Вы можете в приложенном файле, или просмотрев видео-демонстрацию урока. А на этом все. До встреч.
Прикрепленный файл: func_vpr_gpr.zip
Видео: Работа с функцией ВПР и ГПР в Excel