Функция ВПР в Excel (по-английски — ВПР) по некоторым ключевым полям «перетягивает» данные из одного диапазона в другой. Ключевое поле должно присутствовать в обоих диапазонах данных (как в том, откуда мы «вытягиваем», так и в том, откуда мы берем данные).
Функция ВПР в Экселе: пошаговая инструкция
Представим, что перед нами стоит задача определения себестоимости реализованного товара. Себестоимость рассчитывается как произведение количества и цены. Это очень легко сделать, если количества и цены находятся в соседних столбцах. Однако данные могут быть представлены не в таком удобном виде. Исходная информация может находиться в совершенно разных таблицах и в разном порядке. В первой таблице показано количество проданных товаров:
Во втором — цены:
Если список товаров в обеих таблицах один и тот же, то зная волшебное сочетание Ctrl+C и Ctrl+V, данные о цене можно легко подставить на данные о количестве. Однако порядок позиций в обеих таблицах не совпадает. Тупо копировать цены и подставлять количество не получится.
Поэтому мы не можем прописать формулу умножения и «растянуть» на все позиции.
Сделать? Нужно как-то заменить цены во второй таблице соответствующим количеством в первой, то есть цену товара А на количество товара А, цену товара В на количество В и т.д.
Мне это нравится.
Функция ВПР в Excel легко справляется с поставленной задачей.
Давайте сначала добавим в первую таблицу новый столбец, куда будут подставляться цены из второй таблицы.
Для вызова функции с помощью Помощника необходимо активировать ячейку, где будет записана формула, и нажать кнопку f(x) в начале строки формул. Появится диалоговое окно Мастера, в котором нужно выбрать ВПР из списка всех функций.
Щелкаем по надписи «ВПР». Откроется следующий диалог.
Теперь нужно заполнить предложенные поля. В первом поле «Искомое_значение» нужно указать критерий для ячейки, в которую мы вводим формулу. В нашем случае это ячейка с наименованием товара «А».
Следующее поле — «Таблица». В нем нужно указать диапазон данных, где будет производиться поиск искомых значений. В нашем случае это вторая таблица с ценой. При этом крайний левый столбец выбранного диапазона должен содержать те же критерии, по которым осуществляется поиск (столбец с названиями товаров). Затем таблица подсвечивается справа как минимум до столбца, где находятся искомые значения (цены). Вы можете выбрать больше справа, но это ни на что не влияет. Главное, чтобы выбранная таблица начиналась со столбца с критериями и захватила нужный столбец с данными. Также следует обратить внимание на тип ссылок, они должны быть абсолютными, потому что формула будет скопирована в другие ячейки.
Следующее поле «Колонка_номер» — это номер, которым отделяется колонка с требуемыми данными (ценами) от колонки с критериями (название товара) включительно. То есть отсчет начинается со столбца с самим критерием. Если в нашей второй таблице оба столбца стоят рядом, то нужно указать цифру 2 (первый — критерии, второй — цены). Часто бывает, что данные отстают от критериев на 10 или 20 столбцов. Не беда, Excel все рассчитает.
Последнее поле — «Interval_lookup», в котором указывается тип поиска: точное (0) или приблизительное (1) соответствие критериям. Установите его на 0 (или FALSE) на данный момент. Второй вариант рассмотрен ниже.
Нажимаем Принять. Если все верно и значение критерия есть в обеих таблицах, вместо вновь введенной формулы появится какое-то значение. Остается только растянуть (или просто скопировать) формулу на последнюю строку таблицы.
Теперь легко рассчитать стоимость, просто умножив количество на цену.
Формулу ВПР можно написать вручную, введя аргументы по порядку и разделив их точкой с запятой (см обучающее видео ниже).
Особенности использования формулы ВПР в Excel
Функция ВПР имеет свои особенности, о которых следует знать.
1. Первую особенность можно считать общей для функций, которые используются для многих ячеек, написав формулу в одной из них, а затем скопировав ее в остальные. Здесь следует обратить внимание на относительность и абсолютный характер ссылок. В частности, в функции ВПР критерии (первое поле) должны иметь относительную ссылку (без знаков $), так как каждая ячейка имеет свои собственные критерии. А вот поле «Таблица» должно иметь абсолютную ссылку (адрес диапазона пишется через $). Если этого не сделать, то при копировании формулы диапазон «уйдет вниз» и многие значения просто не будут найдены, так как негде будет искать.
2. Номер столбца, указанный в третьем поле «Колонка_номер» при использовании мастера функций, должен отсчитываться от самого критерия.
3. Функция ВПР диапазона с нужными данными возвращает первое значение сверху. Это означает, что если во второй таблице, откуда мы пытаемся «вытащить» какие-то данные, есть несколько ячеек с одинаковыми критериями, то в пределах выбранного диапазона ВПР захватит первое значение сверху. Это нужно помнить. Например, если мы хотим получить количество из другой таблицы по цене товара, а там этот товар встречается несколько раз (в нескольких строках), то к цене будет добавлено первое количество сверху.
4. Необходимо настроить последний параметр формулы, который равен 0 (ноль). В противном случае формула может работать криво.
5. После использования ВПР лучше сразу удалить формулу, оставив только возвращаемые значения. Делается это очень просто. Выделяем диапазон с полученными значениями, нажимаем «копировать» и вставляем значения на то же место с помощью специальной пасты. Если таблицы расположены в разных книгах Excel, то очень удобно разбивать внешние ссылки (оставляя на своих местах только значения) с помощью специальной команды, которая находится в пути Data Change Links.
После вызова функции разрыва внешних ссылок появится диалоговое окно, в котором необходимо нажать кнопку «Разорвать ссылку», а затем «Закрыть».
Это удалит все внешние ссылки сразу.
Примеры функции ВПР в Excel
Для следующих примеров использования функции ВПР мы возьмем немного другие данные.
Нужно поднять цены второго стола до первого. Код используется здесь как критерий. Шаги для расчета ВПР показаны ниже.
Вторая таблица меньше первой, т.е отсутствуют некоторые коды. Для отсутствующих элементов функция ВПР выдает ошибку #Н/Д.
Появление таких ошибок, кстати, можно использовать, когда нужно найти отличия в таблицах. Но, скорее всего, мешают баги.
Конструкция с функцией ЕСЛИОШИБКА
Наряду с функцией ВПР часто используется функция ЕСЛИОШИБКА, которая «замалчивает» ошибки #Н/Д и вместо этого возвращает некоторое значение. Обычно это 0 или пусто.
Как видите, ошибок больше нет, а вместо них пустые ячейки.
Разные форматы критерия в таблицах
Одной из частых причин ошибок является несовпадение форматов критериев в двух таблицах. Функция ВПР обрабатывает текстовый и числовой форматы как разные значения. Есть два варианта.
В первом случае критерии из первой таблицы хранятся в виде чисел, а критерии из второй таблицы — в виде текста.
В ячейках с числами, сохраненными в виде текста, в верхнем левом углу появляется зеленый треугольник. Вы можете выбрать все эти числа и выбрать «Преобразовать в число» из раскрывающегося списка.
Это решение используется довольно часто. Но не всегда подходит. Например, когда данные во второй таблице регулярно загружаются из какой-то базы 1С. В таких файлах обычно все сохраняется в виде текста. И если мы планируем постоянно использовать такие данные, вставляя их в заранее подготовленный диапазон, то лучше, чтобы формулы работали без дополнительного вмешательства.
Автоматически изменить формат критерия во второй таблице нельзя, потому что ссылка ведет на полный диапазон. Придется мешать ссылку на критерий в первой таблице. Для этого нужно добавить функцию ТЕКСТ, которая изменит числовой формат на текстовый. Синтаксис функции ТЕКСТ требует указания формата. Просто установите формат #. Ниже изображение с готовой формулой.
Есть еще две ошибки, связанные с тем, что этих товаров нет во второй таблице. Чтобы заставить их замолчать, вы снова можете использовать функцию ЕСЛИОШИБКА.
Вторая ситуация заключается в том, что «текст» является критерием первой таблицы. Форматы опять не совпадают.
Как и в прошлый раз, внесем коррективы в функцию ВПР. Преобразование «текста» в «число» еще проще. Просто добавьте 0 к ссылке на критерий «текст» или умножьте на 1.
Существует и третья смешанная ситуация. Это гораздо реже. Это когда в первой и второй таблицах критерии хранятся как число и как текст, смешанные вместе. Здесь вам нужно будет использовать сразу все описанные выше функции: ЕСЛИОШИБКА, ТЕКСТ и +0. Сначала мы пишем ЕСЛИОШИБКА и в качестве первого аргумента этой функции мы пишем ВПР с некоторой конструкцией для изменения формата. Например, ВПР с формулой ТЕКСТ. В качестве второго аргумента (то есть того, что должно быть при ошибке) пишем вторую ВПР с +0. Итак, если ВПР с функцией ТЕКСТ не выдает ошибки, то все в порядке. Но если первая конструкция возвращает ошибку #Н/Д, то функция ЕСЛИОШИБКА заменяет вторую конструкцию ВПР на +0. Другими словами, сначала мы делаем все критерии текстовыми, а затем числовыми. Таким образом, ВПР проверяет оба формата. Один из них будет соответствовать формату второй таблицы. Это немного громоздко, но в целом это работает.
Отсутствующие критерии по-прежнему вызывают ошибку #Н/Д. В этом случае всю формулу можно «обернуть» обратно в ЕСЛИОШИБКА.
Функция СЖПРОБЕЛЫ для чистки текстового критерия
В качестве критерия рекомендуется брать единый код, в котором опечатки, характерные для текста, маловероятны. Но иногда кода еще нет и критерием является текст (названия организаций, имена людей и т.п.). В этом случае возможны случайные опечатки. Одна из самых распространенных ошибок — лишние пробелы. Проблема решается простым использованием функции TRIM для всех критериев. Вы можете сделать это с помощью формулы ВПР или сначала просмотреть все критерии в обеих таблицах. Кому удобнее.
Подсчет номера столбца в большой таблице
Если во второй таблице много столбцов, да еще некоторые из них скрыты или сгруппированы, то напрямую подсчитать количество столбцов между критериями и требуемыми данными очень сложно. Есть хитрость, позволяющая вообще не считать эти столбцы. Для этого при выборе второй таблицы смотрите в правый нижний угол выделенного диапазона. Появится подсказка о количестве выбранных строк и столбцов. Запоминаем количество столбцов и вводим ВПР в формулу.
Отличная экономия времени.
Интервальный просмотр в функции ВПР
Пришло время обсудить последний аргумент функции ВПР. Как правило, я указываю 0, чтобы функция искала точное совпадение критериев. Но есть вариант нечеткого поиска, он называется интервальным поиском.
Рассмотрим алгоритм ВПР при выборе интервального сканирования. Во-первых (это обязательно), столбец критериев в таблице поиска должен быть отсортирован по возрастанию (если числа) или по алфавиту (если текст). ВПР просматривает список критериев выше и находит такой же, а если его нет, то ближайший наименьший к заданным критериям, т.е на одну ячейку выше (поэтому и нужна предварительная сортировка). После нахождения подходящего критерия ВПР считает указанное количество столбцов справа и берет туда содержимое ячейки, которая является результатом формулы.
Легче понять на примере. По результатам выполнения плана продаж каждый торговый агент должен получить заслуженную премию (в процентах от оклада). При выполнении плана менее чем на 100% премия не начисляется, при выполнении плана от 100% до 110% (не включая 110%) — премия 20%, от 110% до 120% (не включая 120%). — 40%, 120% и более — 60% надбавка. Данные имеют следующий вид.
Требуется замена премии по результатам выполнения планов продаж. Чтобы решить задачу, в первой ячейке введите следующую формулу:
=ВПР(B2,$E$2:$F$5,2,1)
и скопируйте вниз.
На следующем рисунке показана диаграмма того, как работает представление диапазона функции ВПР.
Джеки Чан выполнил план на 124%. Это означает, что ВПР в качестве критерия ищет ближайшее наименьшее значение во второй таблице. Это 120%. Затем он считает 2 столбца и возвращает 60% надбавки. Брюс Ли не выполнил план, поэтому его следующий самый низкий критерий — 0%.
Предлагаю посмотреть видеоурок по работе ВПР из курса «Основные функции Excel».
