Функция ВПР в Excel на простых примерах

Используя функцию ВПР при работе в Excel, Вы можете извлекать требуемую информацию из электронных таблиц. Для этих целей Excel предлагает несколько функций, но ВПР среди них самая распространенная. В этом уроке мы познакомимся с функцией ВПР, а также рассмотрим ее возможности на простом примере.

Функция ВПР (вертикальный просмотр) ищет значение в крайнем левом столбце исследуемого диапазона, а затем возвращает результат из ячейки, которая находится на пересечении найденной строки и заданного столбца.

Пример 1

Например, на рисунке ниже приведен список из 10 фамилий, каждой фамилии соответствует свой номер. Требуется по заданному номеру извлечь фамилию.

Функция ВПР в Excel

С помощью функции ВПР сделать это достаточно просто:

Функция ВПР в Excel

Из формулы видно, что первым аргументом функции ВПР является ячейка С1, где мы указываем искомый номер. Вторым выступает диапазон A1:B10, который показывает, где следует искать. И последний аргумент – это номер столбца, из которого необходимо возвратить результат. В нашем примере это второй столбец. Нажав Enter, мы получим нужный результат:

Функция ВПР в Excel

Пример 2

Рассмотрим еще один пример. На рисунке ниже представлены те же 10 фамилий, что и раньше, вот только номера идут с пропусками.

Функция ВПР в Excel

Если попробовать найти фамилию для несуществующего номера (например, 007), то формула вместо того, чтобы выдать ошибку, благополучно вернет нам результат. Как такое может быть?

Функция ВПР в Excel

Дело в том, что функция ВПР имеет еще и четвертый аргумент, который позволяет задавать так называемый интервальный просмотр. Он может иметь два значения: ИСТИНА и ЛОЖЬ. Причем, если аргумент опущен, то это равносильно истине.

В случае, когда четвертый аргумент имеет значение ИСТИНА, функция сначала ищет точное соответствие, а если такого нет, то ближайшее, которое меньше чем заданное. Именно поэтому функция ВПР возвратила фамилию "Панченко". Если бы мы задали "008", то формула также вернула бы "Панченко".

Функция ВПР в Excel

В случае, когда четвертый аргумент функции ВПР имеет логическое значение ЛОЖЬ, функция ищет точное соответствие. Например, на рисунке ниже формула вернет ошибку, поскольку точного соответствия не найдено.

Функция ВПР в Excel

Если четвертый аргумент функции ВПР содержит значение ИСТИНА или опущен, то крайний левый столбец должен быть отсортирован в порядке возрастания. Если этого не сделать, функция ВПР может вернуть неправильный результат.

Для тех, кто любит создавать не вертикальные, а горизонтальные таблицы, в Excel существует аналог ВПР, но для горизонтального поиска.

Горизонтальный ВПР в Excel

В Microsoft Excel существует функция ГПР (горизонтальный просмотр), которая очень похожа на ВПР, разница лишь в том, что диапазон просматривается не по вертикали, а по горизонтали. ГПР ищет заданное значение в верхней строке исследуемого диапазона и возвращает результат из ячейки, которая находится на пересечении найденного столбца и заданной строки.

Если представить вышеприведенный пример в горизонтальной форме, то формула будет выглядеть следующим образом:

Функция ВПР в Excel

Как видите, все достаточно просто!

На этом наш урок завершен. Сегодня мы познакомились, наверное, с самым популярным инструментом Microsoft Excel – функцией ВПР и разобрали ее возможности на нескольких простых примерах. Надеюсь, что этот урок был для Вас полезным. Всего Вам доброго и успехов в изучении Excel.

PS: Интересуетесь функцией ВПР? На нашем сайте ей посвящен целый раздел с множеством самых интересных уроков!

23.01.2015 18:09
129767

4 комментария

01.03.2015 13:42
Здравствуйте, Андрей. Хотел спросить насчет функций ВПР и ПРОСМОТР. Надо ли закреплять в этих функциях диапазон искомых значений? Если не закреплять у меня почему то не выходит нужные данные.
Честно скажу, не совсем понял вопрос, поскольку не вижу задачи, которая у вас не получается. Попробую ответить в целом, возможно угадаю. )))
Насколько я понял, вы имеете ввиду не искомое значение, а исследуемый диапазон.

Здесь могут быть минимум 2 ситуации:
1. Вы не используете автозаполнение ячеек
2. Вы используете автозаполнение ячеек.

Например:
У нас есть такая таблица:

Наша задача по номеру ID вытаскивать данные из этой таблицы:

Мы меняем ID, меняются данные:

Все прекрасно работает! И использовать в такой ситуации абсолютную ссылку на исследуемый диапазон не обязательно, хотя хуже от этого не будет:

Теперь представим, что необходимо вывести данные не по 1-му ID, а сразу по 3-м… Первую строку мы уже ввели и теперь хотим скопировать созданные ранее формулы в остальные ячейки:

Если ссылку на исследуемый диапазон сделать абсолютной, то все будет хорошо:

А если оставить относительной, то в скопированных формулах диапазон поплывет:

В действительности исследуемый диапазон имеет адрес — A10:D14, а в формуле оказалось — A12:D16.

Если я правильно понял ваш вопрос, то читайте про абсолютные и относительные ссылки в Excel, как они работают, для чего нужны и т.д. А дальше уже сами думайте где и что применять.

Для удобства сделал гиперссылки в тексте на необходимые статьи.

PS: При необходимости можете объяснить задачу более подробно, постараюсь помочь. Если будете публиковать скриншоты, старайтесь оставлять только самую суть. Скрипт на сайте сжимает размер картинок, поэтому становится плохо видно. Пока не занимался комментариями, позже подумаю, что с этим можно сделать.
02.03.2015 14:54
Угадали. У меня был второй случай. Необходимо было использовать автозаполнение. Получается, если использовать автозаполнение то всегда нужно использовать абсолютные ссылки?
p.s. Когда написал «закреплять ячейки» имел ввиду абсолютные ссылки
Получается, если использовать автозаполнение то всегда нужно использовать абсолютные ссылки?

Касаемо исследуемого диапазона: Думаю, что в 99,9% случаях ДА нужно использовать абсолютные ссылки. Возможно, бывают ситуации, которые требуют обратного. Но это уже экзотика )))

Касаемо других аргументов — по ситуации.

Абсолютные и относительные ссылки при копировании работают одинаково везде — хоть в обычной формуле, хоть в качестве аргументов функции ВПР, хоть ПРОСМОТР… Без разницы.
  • Абсолютная ссылка ($A$1) — не изменяется при копировании.

  • Относительная ссылка (A1) — изменяется при копировании.

  • Еще есть смешанные (A$1 или $A1) — что-то среднее.