Формула ВПР: как посчитать в таблицах

Если нужно объединить данные в нескольких таблицах, используют функцию ВПР. Она помогает не запутаться в большом объёме информации и не переносить значения вручную. А ещё благодаря ВПР можно избежать ошибок в подсчётах. Рассказываем, как работает формула на примере таблиц в Яндекс Документах.

Что такое функция ВПР и для чего она нужна

ВПР, или VLOOKUP, — функция вертикального просмотра. Она находит данные в одной таблице и переносит их в другую.

Когда пригодится ВПР:

• В бухгалтерии — чтобы прикинуть выручку или посчитать бюджет. Например, когда нужно «подтянуть» данные о стоимости за единицу товара в таблицу с количеством проданного.
• В продажах — чтобы узнать, по какой цене заказали товар. Можно также рассчитать размер клиентской скидки или премии сотрудника, если она зависит от количества закрытых сделок.
• В аналитике — чтобы сравнить показатели за разные периоды. Например, если нужно узнать, с чем связано падение спроса на определённые позиции товаров, была ли такая же ситуация в прошлом году.

ВПР используют там, где работают с большим массивом данных и несколькими таблицами. Например, в кадрах — для учёта рабочего времени сотрудников или в логистике — для расчёта оптимального маршрута и времени доставки.

Как подготовить данные для ВПР

Рассмотрим на примере, как работает функция ВПР. Представим небольшой розничный магазин, менеджер которого ведёт две таблицы: «Количество проданных товаров» и «Остаток запасов на складе». Ему нужно обновить информацию об остатках на складе, используя информацию о продажах. Для этого нужно перенести данные из одной таблицы в другую.

Что нужно сделать, прежде чем приступить к работе с функцией ВПР:

1. Добавляем новую колонку в таблицу с количеством проданных товаров. Называем её «Остаток на складе».
2. Выбираем ячейку напротив первой товарной позиции.
3. В строке функций или самой ячейке набираем «=ВПР».

Как прописать аргументы функции ВПР

Когда подготовили таблицу, нужно заполнить аргументы функции. Их четыре:

1. Искомое значение — координаты первой ячейки из столбца, для которого ищем информацию. По ним функция ВПР ориентируется, когда берёт данные из другой таблицы.
2. Таблица — диапазон ячеек, в котором ВПР ищет нужные данные.
3. Номер столбца — порядковый номер столбца из указанного диапазона. Оттуда «подтягивается» результат в таблицу.
4. Сортировка или интервальный просмотр — значение, с помощью которого настраиваем точность функции. Выбираем «0» для точного совпадения и «1» — для приближённого.

В случае с неточным совпадением функция возьмёт первое значение, примерно похожее на запрос, — равное или меньше его. Поэтому чаще используют точный поиск.

Если нужен поиск по словам, а не по числам, нужно обязательно выбирать аргумент «0», «1» — не сработает.

Что нужно прописать в аргументах, чтобы добавить в таблицу информацию по остатку на складе:

1. Отмечаем ячейку, для которой ищем данные. В нашем случае это A2.

2. Выделяем интервал, откуда возьмём искомые значения. Берём таблицу с количеством проданных товаров, значение — A2:C18.

3. Закрепляем выбранный диапазон. Для этого перед каждым символом надо поставить значок доллара: $A$2:$C$18. Это нужно, чтобы можно было переключаться между листами — на формулу ВПР это не повлияет.

Чтобы не проставлять знак «$» вручную, можно выделить значение в строке формул и нажать сочетание Fn + F4 для Windows и Cmd + T для macOS.

4. Вводим номер столбца. Он считается от левого края таблицы: от номера 1 и дальше. Наш искомый столбец — 3, количество проданных товаров.

5. Прописываем следующий аргумент — 0. Он означает, что мы не сортировали данные в исходной таблице и нам нужно точное значение.

Важно: значения аргументов отделяем между собой знаком «;».

6. Проверяем окончательный вид функции, у нас это: =ВПР(A2;'Остаток запасов на складе'!$A$2:$C$18;3;0). После этого нажимаем Enter.

7. Растягиваем получившееся значение до конца таблицы. Для этого нужно: навести курсор на нижний правый угол ячейки → подождать, пока толстый белый крестик превратится в тонкий чёрный → нажать и протянуть вниз.

Результат: что узнали с помощью ВПР

В итоге каждому наименованию товара соответствует два значения: остаток на складе и количество проданных позиций. Нам остаётся только вычесть одно из другого.

Добавляем в таблицу ещё одну колонку. Её можно назвать, например, «Обновление по остаткам». В строке формулы вводим «=» и вычитаем количество проданных позиций из остатков. Это выглядит так: =E2-D2. Растягиваем значение до конца таблицы.

У нас появилась колонка, из которой менеджер может узнать, какое количество позиций на данный момент осталось на складе. Далее он может принять решение, например, закупить товар, который скоро закончится.

Подводим итоги: как пользоваться VLOOKUP

Итак, с функцией ВПР можно:

• Автоматически переносить данные из одной таблицы в другую — это экономит время. При этом, если внести изменения в таблице, из которой брали данные, информация в искомой тоже поменяется.
• Минимизировать вероятность ошибки — по сравнению с ручной обработкой данных.
• Свести и хранить информацию в одном месте, чтобы сравнивать между собой разные параметры и анализировать их.

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