ВПР в Excel: разбираемся как правильно использовать функцию

Привет!

Заранее оговоримся, что все, что рассказываем в этой статье на примере Excel, будет справедливо и для Google Sheets, и для Р7.

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

В этой статье:

• Что такое ВПР простыми словами
• При чем тут справочники
• Наглядный пример и разбор ВПР по шагам
• Типичные ошибки
• Где и когда может потребоваться ВПР

Поехали!

Представьте, что у вас есть помощник или ассистент. Вы выполняете простую задачу: вы проходите по списку сотрудников какого-то отдела (например, отдела продаж) и вслух называете помощнику имя и фамилию, а ваш помощник смотрит в базу (или в таблицу) и в ответ называет вам оценку, которую этому сотруднику поставили по результатам годовой аттестации, после чего вы записываете оценку напротив сотрудника в вашем списке. Эту работу легко может сделать один человек, но вдвоем это делается гораздо быстрее — две пары глаз, две пары рук. Таких ситуаций — море. Если простыми словами, то ВПР — это как раз ваш такой помощник в подобных задачах в Excel.

Логика работы ВПР примерно такая же, как описано выше: поиск некоторого нужного вам значения в таблице-справочнике по известному имени-запросу и возврат какой-то характеристики для этого значения.

Вы буквально говорите ей:
1. «Найди мне Андрея Кузнецова» (например).
2. «Ищи вот в этом большом справочнике, где много информации про каждого».
3. «Когда найдешь его, скажи мне его оценку, но только её, а ничего больше».
4. «Найди мне именно Андрея Кузнецова, а не кого-то похожего».

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

Давайте разберем на простом примере. Он синтетический, но отлично, прозрачно и наглядно показывает, как ВПР справится с задачей. Итак, официант кофейни принял заказ новых посетителей за столиком и записал его:

• Капучино
• Капучино
• Раф

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

Итого, у нас есть две таблицы: Меню (это наш справочник) и Заказ. Наша задача: рассчитать сумму счета и вместо нуля в строке «Итого» получить точное число (там уже заранее стоит простая формула суммирования желтых ячеек из Заказа).

Синтаксис функции ВПР (напомним, в англоязычном Excel она называется VLOOKUP) выглядит так (вы можете видеть это в подсказках Excel, которые всплывают, стоит вам только начать писать функцию):

На первый взгляд страшно, но по факту — нет. Давайте разберем по шагам для нашего примера. Кстати, как выглядят шаги визуально, можно посмотреть в слайдере с изображениями ниже (там используется функция VLOOKUP — это англоязычное название функции ВПР, они ничем не отличаются). Встаем на ячейку J7 начинаем писать формулу.

Шаг 0. Пишем =ВПР(
Это начало функции и в этот момент Excel подкидывает всплывающую подсказку, что он ожидает дальше получить.
Формула должна выглядеть так: =ВПР(

Шаг 1. Что ищем — искомое_значение
Это так называемый «ключ», по которому Excel поймет, какое именно значение функция ВПР будет искать в справочнике. Это значение должно быть общим и уникальным для обеих таблиц: Меню и Заказ. В нашем примере — это Напиток/Позиция (не важно, как они называются в заголовке, важно, чтобы совпадали названия самих напитков в двух таблицах). Кликаем мышкой на ячейку с первым напитком в нашем Заказе — I7. После этого ставим точку с запятой (или запятую, если ваши настройки системы таковы, что Excel ожидает запятую в качестве разделителя — он покажет это во всплывающей подсказке), чтобы показать Excel, что с первым аргументом мы закончили
Формула теперь должна выглядеть так: =ВПР(I7;

Шаг 2. Где ищем — таблица (она же наш справочник)
Это та самая таблица-справочник, откуда мы будем брать недостающие в Заказе данные — цену. Выделяем мышкой диапазон с C7 по D17 (если у вас тачпад, то можно просто нажать на C7, а дальше за уголок обводки потянуть и растянуть диапазон до D17). После этого нажимаем F4 (или fn+F4), чтобы появились «якоря» ($) в формуле (закрепляем диапазон, получаем т.н. «абсолютную ссылку»). «Якоря» нам потребуются для того, чтобы диапазон, указывающий на справочник, зафиксировался и не менялся, когда мы будем в конце протягивать нашу формулу ВПР. После чего снова ставим точку с запятой. Если хотите, можете просто на клавиатуре написать эту часть формулы $C$7:$D$17 и не двигать и не кликать никакой мышкой.
Формула теперь должна выглядеть так: =ВПР(I7;$C$7:$D$17;

ВАЖНЕЙШЕЕ ПРАВИЛО ВПР: cтолбец, по которому мы будем искать напитки в меню, должен быть ПЕРВЫМ в выделяемой таблице.

Шаг 3. Что хотим получить, когда нашли искомое значение — номер_столбца
Теперь ему нужно сказать, информацию из какого по счету столбца нашего справочника ему забрать, когда он найдет нужный напиток в справочнике. В нашем справочнике (Меню) всего два столба: первый — это название напитка, второй — это цена. В общем случае справочник может быть и больше, и нужно посчитать какой по порядку номер столба начиная с самого первого (левого) нам нужен. В нашем случае нам нужен второй столбец, поэтому в формуле пишем цифру 2. После чего снова ставим точку с запятой.
Формула теперь выглядит так: =ВПР(I7;$C$7:$D$17;2;

Шаг 4. Точность поиска — интервальный_просмотр
Этот аргумент может показаться сложным, но для финансиста все просто: в 99.9% случаев вам нужно точное совпадение. Чтобы сказать это Excel, пишем цифру 0. Закрываем скобку. Наша итоговая формула: =ВПР(I7;$C$7:$D$17;2;0)

Нажимаем Enter и видим результат. Теперь просто «протяните» формулу вниз на желтые ячейки, потянув за маленький квадратик в правом нижнем углу ячейки с формулой. Готово! Посмотрите в галерее, как это просто:

Ошибка #Н/Д (или #N/A)
Самая частая. Означает «Нет данных». Обычно причина кроется в одном из следующего:

• Либо искомого значения (напитка в примере выше) нет в справочнике. Кстати, когда искомое значение — это пустая ячейка (это будет, например, если протянуть формулу из примера выше целиком на все строки заказа, даже пустые), то пустую ячейку функция тоже не найдет.
• Либо опечатка, лишние пробелы в одной из ячеек.

Опечатку можно поправить, а справочник расширить, если, конечно, это разрешено и имеет смысл. В остальных случаях можно «скрасить» результат. Чтобы он был симпатичнее, можно «обернуть» ВПР в функцию ЕСЛИОШИБКА. Получится двойная формула вида: =ЕСЛИОШИБКА(ВПР(...);0). Вместо нуля можно подставить любое значение или текст, который будет свидетельствовать, что что-то пошло не так. Что поставить вместо ошибки, зависит от того, что вы планируете делать дальше с этим результатом, как он будет у вас обрабатываться: суммироваться, фильтроваться, сортировать и т.п. Среди вариантов: можно поставить 0, или -1, или пустой текст в виде двух двойных кавычек подряд без пробела (""), или написать «Не найден артикул», или что-то другое, что поможет вам обработать результаты.

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

Искомое значение — не в первом столбце
ВПР ищет только в крайнем левом столбце выделенной таблицы. Искать по-другому она не умеет. Если ваш справочник устроен иначе, то поменяйте столбцы местами или воспользуйтесь другой функцией, например, ПРОСМОТР.

В любой момент, когда вам нужно подтянуть в один набор информации какие-то характеристики или значения, которых вам в нем не хватает. Когда вам нужно из двух таблиц склеить отчет. Вот несколько реальных примеров из повседневной работы.

Например, отчет о продажах в пересчете на 1м² площади
У вас есть реестр всех продаж за прошлый месяц в сети кофеен с указанием всего двух чисел: артикула товара (кода позиции) и идентификационного номера кофейни; а также у вас есть два отдельных справочника: в первом — артикулы и все сопутствующие характеристики товаров (название, розничная цена, вес и т.д.); а во втором — идентификационные номера кофеен и все их характеристики (название, адрес, площадь, имя управляющего, ставка аренды и т.д.). Вам нужно сделать красивый отчет-таблицу, в котором свести продажи в кофейнях по позициям и показать эффективность.

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

Например, сверка оплат
Тот же план-факт, но по другому бизнес-процессу. У вас есть реестр счетов к оплате, а из банка вы выгрузили выписку с оплатами за прошедшую неделю. Как отделить оплаченные счета от неоплаченных? С помощью ВПР можно быстро пройти по реестру счетов и найти в банковской выписке те, оплата которых прошла в банке.

Например, подготовка управленческой отчетности
Нужно объединить данные из разных систем? Например, по табельному номеру сотрудника подтянуть в финансовый отчет его грейд, отдел и размер оклада, результаты отдела, его плановые КПЭ (KPI) и т.п. ВПР потенциально сэкономит уйму времени и снизит риск человеческой ошибки.

Как видите, ВПР — это не особо-то и сложно. Эта функция — основа упрощения и автоматизации в Excel, если вы работаете с отчетами, большим количеством таблиц и справочников. Потратьте 15 минут на изучение этой функции и практику, начинайте применять и увидите, насколько проще станет ваша работа. Мы уверены, вы справитесь!

ВПР — это аббревиатура от словосочетания «Вертикальный ПРосмотр». Функция относится к «семейству» функций поиска порядкового номера вхождения искомого значения в одном столбце/строке и нахождению элемента того же порядкового номера в другом столбце/строке. Для такого же, как и ВПР, но только горизонтального поиска есть функция ГПР (от словосочетания «Горизонтальный ПРосмотр», на английском — HLOOKUP). А в общем случае они происходят от функции ПРОСМОТР (на английском — LOOKUP), которая и реализует поиск искомого элемента в массиве и выдачу значения с аналогичным порядковым номером из другого массива. ПРОСМОТР иногда даже удобнее, поскольку не ограничена «первым столбцом справочника» и может работать в обратную сторону, то есть искать значения в правом столбце и возвращать то же по порядковому номеру значение из левого столбца (да хоть из строки, это уже не важно). Для более полного использования (чтобы не ловить ошибки) есть и более продвинутая функция ПРОСМОТРХ (XLOOKUP) в современных версиях Excel. Но об этом напишем в другом материале.

Удачи в работе с ВПР (и ГПР)!