Функции ВПР и ГПР среди пользователей Excel очень популярны. Первая применяется для вертикального анализа, сопоставления. То есть используется, когда информация сосредоточена в столбцах.
ГПР, соответственно, для горизонтального. Так как в таблицах редко строк больше, чем столбцов, функцию эту вызывают нечасто.
Синтаксис функций ВПР и ГПР
Функции имеют 4 аргумента:
Шаг 5: Для массива таблицы выберите мини-таблицу, которую вы создали, когда вы вставили строку для определения заголовков столбцов. Эта таблица должна быть только двумя строками с точки зрения высоты. Шаг 6: Для ссылки на строку, вход Это связано с тем, что ваша мини-таблица имеет только две строки, и вы хотите, чтобы она возвращала значение из второй строки.
Это связано с тем, что мы хотим получить точное соответствие для обоих наших поисковых запросов. Перемещаясь горизонтально в верхней части массива таблицы, остановитесь на значении поиска и верните значение в соответствующей второй строке. В этом случае формула разрешается до 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: примеры
Для учебных целей возьмем такую табличку:
Применение ГПР на практике ограничено, так как горизонтальное представление информации используется очень редко.
Символы подстановки в функциях ВПР и ГПР
Случается, пользователь не помнит точного названия. Задавая искомое значение, он может применить символы подстановки:
- «?» - заменяет любой символ в текстовой или цифровой информации;
- «*» - для замены любой последовательности символов.
Когда проблемы с памятью устранены, можно работать с данными, используя все те же функции.
Как сравнить листы с помощью ВПР и ГПР
У нас есть данные о продажах за январь и февраль. Эти таблицы необходимо сравнить с помощью формул ВПР и ГПР. Для наглядности мы пока поместим их на один лист. Но будем работать в условиях, когда диапазоны находятся на разных листах.
Как сравнить листы с помощью ВПР в Excel?
Решим проблему 1 : сравним наименования товаров в январе и феврале. Так как в феврале их больше, вводить формулу будем на листе «Февраль».
Решим проблему 2 : сравним продажи по позициям в январе и феврале. Используем следующую формулу:
Как сравнить листы с помощью ГПР в Excel?
Для демонстрации действия функции ГПР возьмем две «горизонтальные» таблицы, расположенные на разных листах.
Задача – сравнить продажи по позициям за январь и февраль.
Создаем новый лист «Сравнение». Это не обязательное условие. Сопоставлять данные и отображать разницу можно на любом листе («Январь» или «Февраль»).
.
Результат:
Проанализируем части формулы:
«Половина» до знака «-»:
. Искомое значение – первая ячейка в таблице для сравнения. Анализируемый диапазон – таблица с продажами за февраль. Функция ГПР «берет» данные из 2 строки в «точном» воспроизведении.
После знака «-»:
. Все то же самое. Кроме диапазона. Здесь берется таблица с продажами за январь.
Когда мы вводим формулу, Excel подсказывает, какой сейчас аргумент нужно ввести.
Используя функцию ВПР при работе в Excel, Вы можете извлекать требуемую информацию из электронных таблиц. Для этих целей Excel предлагает несколько функций, но ВПР среди них самая распространенная. В этом уроке мы познакомимся с функцией ВПР , а также рассмотрим ее возможности на простом примере.
Функция ВПР (вертикальный просмотр) ищет значение в крайнем левом столбце исследуемого диапазона, а затем возвращает результат из ячейки, которая находится на пересечении найденной строки и заданного столбца.
Пример 1
Например, на рисунке ниже приведен список из 10 фамилий, каждой фамилии соответствует свой номер. Требуется по заданному номеру извлечь фамилию.
С помощью функции ВПР сделать это достаточно просто:
Из формулы видно, что первым аргументом функции ВПР является ячейка С1, где мы указываем искомый номер. Вторым выступает диапазон A1:B10, который показывает, где следует искать. И последний аргумент – это номер столбца, из которого необходимо возвратить результат. В нашем примере это второй столбец. Нажав Enter , мы получим нужный результат:
Пример 2
Рассмотрим еще один пример. На рисунке ниже представлены те же 10 фамилий, что и раньше, вот только номера идут с пропусками.
Если попробовать найти фамилию для несуществующего номера (например, 007), то формула вместо того, чтобы выдать ошибку, благополучно вернет нам результат. Как такое может быть?
Дело в том, что функция ВПР имеет еще и четвертый аргумент, который позволяет задавать так называемый интервальный просмотр. Он может иметь два значения: ИСТИНА и ЛОЖЬ. Причем, если аргумент опущен, то это равносильно истине.
В случае, когда четвертый аргумент имеет значение ИСТИНА, функция сначала ищет точное соответствие, а если такого нет, то ближайшее, которое меньше чем заданное. Именно поэтому функция ВПР возвратила фамилию "Панченко". Если бы мы задали "008", то формула также вернула бы "Панченко".
В случае, когда четвертый аргумент функции ВПР имеет логическое значение ЛОЖЬ, функция ищет точное соответствие. Например, на рисунке ниже формула вернет ошибку, поскольку точного соответствия не найдено.
Если четвертый аргумент функции ВПР содержит значение ИСТИНА или опущен, то крайний левый столбец должен быть отсортирован в порядке возрастания. Если этого не сделать, функция ВПР может вернуть неправильный результат.
Для тех, кто любит создавать не вертикальные, а горизонтальные таблицы, в Excel существует аналог ВПР , но для горизонтального поиска.
Горизонтальный ВПР в Excel
В Microsoft Excel существует функция ГПР (горизонтальный просмотр), которая очень похожа на ВПР , разница лишь в том, что диапазон просматривается не по вертикали, а по горизонтали. ГПР ищет заданное значение в верхней строке исследуемого диапазона и возвращает результат из ячейки, которая находится на пересечении найденного столбца и заданной строки.
Если представить вышеприведенный пример в горизонтальной форме, то формула будет выглядеть следующим образом:
Как видите, все достаточно просто!
На этом наш урок завершен. Сегодня мы познакомились, наверное, с самым популярным инструментом Microsoft Excel – функцией ВПР и разобрали ее возможности на нескольких простых примерах. Надеюсь, что этот урок был для Вас полезным. Всего Вам доброго и успехов в изучении Excel.
PS: Интересуетесь функцией ВПР? На нашем сайте ей посвящен целый раздел с множеством самых интересных уроков!
