Функция ВПР (VLOOKUP) в Excel для чайников

Старт:22.10.2024

Срок обучения:2 месяца

Excel: от основ до анализа данных

Курс Excel в Нетологии: освоите продвинутые функции Excel, Power Query и Power Pivot для анализа данных. Научитесь работать с большими объемами информации, строить дашборды, сводные таблицы и визуализировать данные. Получите реальные проекты в портфолио и сертификат о повышении квалификации.

25 130 ₽35 900 ₽

2 094₽/мес рассрочка

Подробнее

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

Функция ВПР в Экселе: пошаговая инструкция

Представьте, что перед нами стоит задача определения стоимости проданных товаров. Стоимость рассчитывается как произведение количества и цены. Это очень легко сделать, если количество и цены указаны в соседних столбцах. Однако данные могут быть представлены не так удобно. Исходная информация может быть в совершенно разных таблицах и в другом порядке. В первой таблице указано количество проданных товаров:

Во втором - цены:

Если список товаров в обеих таблицах совпадает, то, зная волшебную комбинацию Ctrl + C и Ctrl + V, данные о ценах можно легко заменить данными о количестве. Однако порядок элементов в обеих таблицах неодинаков. Глупое копирование цен и замена их количеством не сработает.

Поэтому мы не можем написать формулу умножения и «растянуть» до всех позиций.

Что делать? Необходимо как-то заменить цены во второй таблице на соответствующее количество в первой, например, цену товара A на количество товара A, цену B на количество B и т.д.

Функцией ВПР в Excel легко управлять.

Сначала добавим в первую таблицу новый столбец, в котором будут заменены цены из второй таблицы.

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

Щелкаем по слову «ВПР». Появится следующий диалог.

Теперь вам необходимо заполнить предложенные поля. В первом поле «Search_value» необходимо указать критерий для ячейки, в которую мы вставляем формулу. В нашем случае это ячейка с названием продукта «А».

Следующее поле - «Таблица». В нем нужно указать диапазон данных, в котором будет производиться поиск нужных значений. В нашем случае это вторая таблица с ценой. В этом случае крайний левый столбец выбранного диапазона должен содержать те же критерии, по которым ведется поиск (столбец с названиями товаров). Затем таблица подсвечивается справа хотя бы до столбца, в котором находятся требуемые значения (цены). Вы можете выбрать дальше вправо, но это ни на что не влияет. Главное, чтобы выбранная таблица начиналась со столбца с критериями и попадала в нужный столбец с данными. Также стоит обратить внимание на тип ссылок, они должны быть абсолютными, так как формула будет скопирована в другие ячейки.

Следующее поле «Column_number» - это номер, которым столбец с необходимыми данными (ценами) отделяется от столбца с включенным критерием (названием продукта). То есть отсчет времени начинается с самого столбца с критерием. Если в нашей второй таблице оба столбца расположены рядом друг с другом, вам нужно указать цифру 2 (первая - критерий, вторая - цены). Часто бывает, что данные отстоят от критерия на 10 или 20 столбцов. Не беда, Excel все посчитает.

Последнее поле - «Interval_View», которое указывает тип поиска: точное (0) или приблизительное (1) соответствие критерия. На данный момент установите 0 (или FALSE). Второй вариант обсуждается ниже.

Щелкните ОК. Если все верно и значение критерия есть в обеих таблицах, то вместо только что введенной формулы появится значение. Осталось только удлинить (или просто скопировать) формулу до последней строки таблицы.

Теперь легко рассчитать стоимость, просто умножив количество на цену.

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

Особенности использования формулы ВПР в Excel

У функции ВПР есть свои особенности, о которых вам следует знать.

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

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

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

4. Должен быть установлен последний параметр формулы, равный 0 (нулю). В противном случае формула может работать неправильно.

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

После вызова функции разрыва внешних ссылок появится диалоговое окно, в котором нужно нажать кнопку «Разорвать ссылку», а затем «Закрыть».

Это удалит все внешние ссылки одновременно. 

Примеры функции ВПР в Excel

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

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

Вторая таблица меньше первой, например, некоторые коды отсутствуют. ВПР возвращает ошибку # Н / Д для отсутствующих элементов.

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

Конструкция с функцией ЕСЛИОШИБКА

В сочетании с функцией ВПР часто используется функция SEERROR, которая «удаляет» ошибки # N / A и вместо этого возвращает значение. Обычно это 0 или пусто. 

Как видите, ошибок больше нет и на их месте остались пустые ячейки.

Разные форматы критерия в таблицах

Одна из наиболее частых причин ошибок - несоответствие формата критериев в двух таблицах. ВПР обрабатывает текстовый и числовой форматы как разные значения. Есть две возможности.

В первом случае критерии в первой таблице хранятся в виде чисел, а критерии во второй таблице хранятся в виде текста.

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

Этот раствор используется довольно часто. Но не всегда получается. Например, когда данные из второй таблицы регулярно загружаются из базы данных типа 1С. В таких файлах обычно все сохраняется в виде текста. И если мы планируем постоянно использовать такие данные, помещая их в заранее подготовленный диапазон, лучше, чтобы формулы работали без дополнительного вмешательства. 

автоматически изменить формат критерия во второй таблице невозможно, так как ссылка ведет на весь диапазон. Придется вмешаться в ссылку на критерий в первой таблице. Для этого нужно добавить функцию ТЕКСТ, которая изменит формат числа на текст. Синтаксис функции ТЕКСТ требует спецификации формата. Просто установите формат #. Ниже фото с готовой формулой.

Две ошибки по-прежнему связаны с отсутствием этих товаров во второй таблице. Чтобы заставить их замолчать, вы можете снова использовать функцию ЕСЛИОШИБКА.

Вторая ситуация состоит в том, что «текст» является критерием первой таблицы. Форматы снова не совпадают.

Как и в прошлый раз, мы собираемся внести некоторые изменения в функцию ВПР. Преобразовать «текст» в «число» еще проще. Просто добавьте 0 или умножьте на 1 ссылку к «тексту.

Есть и третья неоднозначная ситуация. Это встречается гораздо реже. Это когда в первой и второй таблицах критерии хранятся как в числовом, так и в текстовом, смешанном виде. Здесь нужно одновременно использовать все функции, описанные выше: ESLIOSHIBKA, TEXT и +0. Сначала мы пишем ESLIOSHIBKA и в качестве первого аргумента этой функции пишем VLOOKUP с некоторой конструкцией для изменения формата. Например, ВПР с формулой ТЕКСТ. В качестве второго аргумента (т.е что должно быть в случае ошибки) мы пишем вторую конструкцию ВПР с +0. Итак, если ВПР с функцией ТЕКСТ не выдаёт ошибку, значит, все в порядке. Но если первая конструкция возвращает ошибку # N / A, функция ЕСЛИОШИБКА заменяет вторую конструкцию - ВПР на +0. Другими словами, мы сначала заставляем все критерии быть текстовыми, а затем числовыми. Поэтому ВПР проверяет оба формата. Один из них будет соответствовать формату второй таблицы. Немного громоздко, но в целом все работает.

Отсутствие критериев по-прежнему будет вызывать ошибку # Н/Д. В этом случае всю формулу можно снова «завернуть» в ESLIOSHIBKA.

Функция СЖПРОБЕЛЫ для чистки текстового критерия

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

Подсчет номера столбца в большой таблице

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

Это отличная экономия времени.

Интервальный просмотр в функции ВПР

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

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

это легче понять на примере. По результатам выполнения плана продаж каждый торговый агент должен получить заслуженную премию (в процентах от заработной платы). Если план выполнен менее чем на 100%, премия не требуется, если план выполняется от 100% до 110% (без учета 10%) - премия 20%, от 110% до 120% (120%). % не входит) - 40%, 120% и более - 60% премии. Данные представлены в следующей форме.

премия должна быть заменена на основании выполнения планов продаж. Для решения задачи в первую ячейку запишем следующую формулу:

= ВПР (B2; $ E $ 2: $ F $ 5; 2; 1)

и скопируйте его.

На следующем рисунке показана диаграмма того, как работает режим просмотра диапазона функции ВПР.

Джеки Чан выполнил план на 124%. Это означает, что ВПР в качестве критерия ищет ближайшее нижнее значение во второй таблице. Это 120%. Таким образом, он считает 2 столбца и возвращает 60% премию. Брюс Ли не выполнил план, поэтому его ближайший нижний критерий - 0%.

Предлагаю посмотреть видеоурок о том, как работает ВПР, из курса «Основные функции Excel».