Курс: Продающий лендинг для вашего бизнеса Бесплатно
Создайте сайт с нуля за 40 минут и запустите продажи

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

1 комментарий
Функция ВПР в Excel

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

В этой статье мы рассмотрим функцию ВПР (или VLOOKUP, что означает «вертикальный просмотр»). Функция ВПР помогает работать с данными из двух таблиц и подтягивать значения из одной в другую. Использовать ее удобно, когда нужно посчитать выручку или прикинуть бюджет, если в одной таблице указан прайс-лист, а в другой количество проданного товара.

Таблица Excel

Допустим, есть таблица с количеством проданного товара и таблица с ценами на эти товары

Необходимо к каждому товару из таблицы слева добавить цену из прайса справа.

Как создать функцию ВПР в Excel

Необходимая последовательность значений в функции называется синтаксис. Обычно функция начинается с символа равенства «=», затем идет название функции и аргументы в скобках.

Записываем формулу в столбик цены (С2). Это можно сделать двумя способами:

  1. Выделить ячейку и вписать функцию.

  2. Выделить ячейку → нажать на Fx (Shift +F3) → выбрать категорию «Ссылки и массивы» → выбрать функцию ВПР → нажать «ОК».

После этого открывается окно, где можно заполнить ячейки аргументов формулы.

аргументы функции ВПР

Синтаксис функции ВПР выглядит так:

=ВПР(искомое значение;таблица;номер столбца;интервальный просмотр)

В нашем случае получится такая формула:

=ВПР(A2;$G$2:$H$11;2;0)

Аргументы ВПР в Эксель

Аргументы функции ВПР

Сейчас разберемся что и куда писать.

Со знаком равенства «=» и названием «ВПР» все понятно. Поговорим об аргументах. Они записываются в скобках через точку с запятой или заполняются в ячейки в окне функции. Формула ВПР имеет 4 аргумента: искомое значение, таблица, номер столбца и интервальный просмотр.

Искомое значение – это название ячейки, из которой мы будем «подтягивать» данные. Формула ВПР ищет полное или частичное совпадение в другой таблице, из которой берет информацию. 

В нашем случае выбираем ячейку «A2», в ней находится наименование товара. ВПР возьмет это название и будет искать аналогичную ячейку во второй таблице с прайсом.

=ВПР(A2;

Функция ВПР, искомое значение

Таблица – это диапазон ячеек, из которых мы будем «подтягивать» данные для искомого значения. В этом аргументе используем абсолютные ссылки. Это значит, что в формуле таблица будет выглядеть как «$G$2:$H$11» вместо «G2:H11». Знаки «$» можно поставить вручную, а можно выделить «G2:H11» внутри формулы и нажать F4. Если этого не сделать, таблица не зафиксируется в формуле и изменится при копировании. 

В нашем случае – это таблица с прайсом. Формула будет искать в ней совпадение с ячейкой, которую указали в первом аргументе формулы – A2 (Кофе). Нажимаем F4 и делаем ссылку абсолютной.

=ВПР(A2;$G$2:$H$11

Функция ВПР, таблица

Номер столбца – это столбец таблицы, из которой нужно взять данные. Именно из него мы будем «подтягивать» результат. 

  1. Формула сканирует таблицу по вертикали.

  2. Находит в самом левом столбце совпадение с искомым значением.

  3. Смотрит в столбец напротив, очередность которого мы указываем в этом аргументе.

  4. Передает данные в ячейку с формулой.

В нашем случае – это столбец с ценой продуктов в прайсе. Формула ищет искомое значение ячейки A2 (Кофе) в первом столбце прайса и «подтягивает» данные из второго столбца (потому что мы указали цифру 2) в ячейку с формулой.

=ВПР(A2;$G$2:$H$11;2

Функция ВПР, принцип работы

Интервальный просмотр – это параметр, который может принимать 2 значения: «истина» или «ложь». Истина обозначается в формуле цифрой 1 и означает приблизительное совпадение с искомым значением. Ложь обозначается цифрой 0 и подразумевает точное совпадение. Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями. 

В нашем случае искомое значение – это текстовое наименование. Поэтому используем точный поиск – ставим цифру 0 и закрываем скобку.

=ВПР(A2;$G$2:$H$11;2;0)

Автозаполнение

В конце протягиваем формулу вниз до конца, в результате чего происходит автозаполнение.

Автозаполнение ячеек в Excel

Чтобы функция ВПР правильно сработала во время автозаполнения, искомое значение должно быть относительной ссылкой, а таблица – абсолютной. 

  • В нашем случае искомое значение – A2. Это относительная ссылка на ячейку, потому что в ней нет знаков «$». Благодаря этому ссылка на искомое значение меняется относительно каждой строчки, когда происходит автозаполнение в другие ячейки: A2 → A3 → … → A11. Это удобно, когда необходимо повторить формулу на несколько строк, ведь ее не приходится писать заново.

  • Таблица зафиксирована абсолютной ссылкой «$G$2:$H$11». Это означает, что ссылки на ячейки не изменятся во время автозаполнения. Таким образом, расчет каждый раз будет корректным и опираться на таблицу. 

ВПР и приблизительный интервальный просмотр

В предыдущем примере мы «подтягивали» значения из таблицы, используя точный интервальный просмотр. Он подходит для работы с наименованиями. Теперь разберем ситуацию, когда может понадобиться приблизительный интервальный просмотр.

Задача. В магазин привезли товар. Необходимо присвоить каждому товару размер партии, опираясь на его количество.

Задача для Excel, ВПР

Товары такие же, как и в первом примере, но задача изменилась: нужно привязать формулу не к наименованию, а к количеству

Решение. Заполняем формулу ВПР в ячейке «Партия», как было показано в предыдущем примере.

Окно для формулы ВПР в Excel

Разница в том, что теперь искомое значение – число, а интервальный просмотр – истина, что означает приблизительный поиск. Получается вот такой результат:

Сортировка в Excel с помощью ВПР

Что произошло? Аргумент «интервальный просмотр» имеет значение 1. Это значит, что формула ВПР ищет в таблице ближайшее меньшее искомое значение. 

В нашем случае количество товара «Кофе» – 380. ВПР берет это число в виде искомого значения, после чего ищет ближайшее меньшее в соседней таблице – число 300. В конце функция «подтягивает» данные из столбца напротив («Крупная»). Если количество товара «Кофе» = 340 – это «Крупная партия». Важно, чтобы крайний левый столбец таблицы, которая указана в формуле, был отсортирован по возрастанию. В противном случае ВПР не сработает. 

Некорректная работа функции ВПР

Значения и данные во второй таблице отсортированы по убыванию – ВПР не работает

Итоги

  • Функция ВПР означает вертикальный просмотр. Она просматривает крайний левый столбец таблицы сверху вниз. 

  • Синтаксис функции: =ВПР(искомое значение;таблица;номер столбца;интервальный просмотр).

  • Функцию можно вписать вручную или в специальном окне (Shift + F3).

  • Искомое значение – относительная ссылка, а таблица – абсолютная. 

  • Интервальный просмотр может искать точное или приблизительное совпадение с искомым значением. 

  • Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями.

  • Порядок работы с функцией подходит для Гугл-таблиц.

Комментарии

Нейробрат +2
08 сен в 15:21
Спасибо за полезную инфу! Эксель для меня всегда был тёмным лесом, но последние месяцы приходится с ним разбираться по зову работы
С помощью соцсетей
У меня нет аккаунта Зарегистрироваться
Нажимая кнопку «Зарегистрироваться», я даю согласие на обработку своих персональных данных, указанных в форме регистрации.
С помощью соцсетей
У меня уже есть аккаунт Войти
Нажимая кнопку «Зарегистрироваться», я даю согласие на обработку своих персональных данных, указанных в форме регистрации.
Инструкции по восстановлению пароля высланы на Ваш адрес электронной почты.
Пожалуйста, укажите email вашего аккаунта
Ваш баланс 10 ТК
1 ТК = 1 ₽
О том, как заработать и потратить Таймкарму, читайте в этой статье
Чтобы потратить Таймкарму, зарегистрируйтесь на нашем сайте