Как сделать ВПР в Excel

Что такое ВПР и когда её используют

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

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

Курс «Microsoft Excel» от НАДПО

Школа	НАДПО
Стоимость	29 500 руб
Цена в рассрочку	2 458 руб/мес
Длительность курса
Программа трудоустройства	Отсутствует
Формат	Запись лекций

Курс «Excel + Google Таблицы с нуля до PRO + ИИ» от Skillbox

Школа	Skillbox
Стоимость	19 559 руб
Цена в рассрочку	889 руб/мес
Длительность курса
Программа трудоустройства	Отсутствует
Формат	Запись лекций

Курс «Excel и Google таблицы с нуля» от Академия «Синергия»

Школа	Академия «Синергия»
Стоимость	17 520 руб
Цена в рассрочку	1 460 руб/мес
Длительность курса
Программа трудоустройства	Отсутствует
Формат	Запись лекций

Из каких аргументов состоит формула ВПР

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

1. Искомое значение. Представляет собой ссылку на ячейку с критерием поиска в традиционном для Excel формате, например, A Важным требованием становится необходимость ссылаться на столбец с уникальными значениями в каждой из сравниваемых таблиц.
2. Таблица. Аргумент обозначает диапазон ячеек для поиска совпадений и последующего переноса данных. Обязательным условием работы функции становится присутствие искомого значения в первом столбце заданного диапазона.
3. Порядковый номер столбца. Причем последний должен находиться внутри заданного диапазона (но не всей таблицы). Именно из этого столбца возвращается значение найденного показателя.
4. Интервальный просмотр. Демонстрирует тип совпадения: для точного (чаще всего – текстового) указывается 0 или ЛОЖЬ, для приблизительного (обычно – числового) – 1 или ИСТИНА.

Пример написания формулы ВПР, включающий четыре описанных выше аргумента, выглядит так: =ВПР (А2; Лист 2!$А$2:$B$15; 2; 0).

Как сделать ВПР в Excel: пошаговая инструкция

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

1. Проверка присутствия в одном файле двух таблиц для сравнения, размещенных на разных листах.
2. Запуск опции «Форматировать как таблицу» для обоих листов (если это не было сделано ранее).
3. Добавление к первой таблице нового столбца.
4. Выделение в нем ячейки для размещения формулы ВПР.
5. Нажатие кнопки f(x) для открытия окна вставки функции.
6. Указание первого аргумента (обычно А2, например, с названием или номером рекламной кампании).
7. Выбор диапазона данных из второй таблицы, которая станет источником информации (включает оба столбца – и название показателя, и его конкретные значения).
8. Закрепление диапазона поиска (чтобы избежать сбивания ссылок, подробнее – ниже).
9. Ввод третьего аргумента (номер столбца второй таблицы, откуда берутся значения для переноса в первую).
10. Указание типа совпадения (приблизительного – для чисел и точного – для текстов).
11. Нажатие кнопки «ОК» для вставки функции.

Результатом описанных действий выступает перенос подходящих значений из второго листа таблицы Excel в первый. Главным преимуществом использования функции становится простота вставки и оперативность выполнения заданных операций. Что особенно актуально, если выполняется работа с большими базами данных.

ВПР с несколькими критериями поиска

Дополнительным плюсом инструмента является возможность поиска по нескольким критериям (то есть столбцам). Она не заложена в функции, но с легкостью может быть добавлена с помощью объединения данных во вспомогательном столбце. Он создается в обеих таблицах. Объединение значений из исходных столбцов происходит предельно просто:

• сначала в ячейке ставится знак «равно» (=), что открывает строку формулы;
• далее указывается ссылка на соответствующую ячейку первого из объединяемых столбцов;
• затем прописывается значок «объединения» (&);
• после чего вставляется ссылка на ячейку второго столбца (в результате формула объединения приобретает примерно такой вид: «=C2&D2»);
• в завершении формула переносится на все строки вспомогательного столбца.

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

Как закрепить диапазон поиска

Беспроблемная работа ВПР предусматривает обязательное закрепление диапазона, где будет происходить поиск данных. Это необходимо для того, чтобы ссылки формулы не сбивались. Закрепление обозначается в Excel значком доллара ($). Причем он должен стоять со значением и столбца, и строки конкретной ячейки.

Закрепление диапазона поиска происходит по-разному в зависимости от операционной системы, используемой на персональном компьютере:

• для Windows требуется нажать клавишу F4 после выделения искомого диапазона;
• для MacOS нужно воспользоваться сочетанием клавиш Cmd+T.

Почему ВПР не работает: типичные ошибки

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

1. Указание искомого значения не в первом столбце диапазона (приводит к выводу на экран ошибки #Н/Д).
2. Отсутствие закрепления диапазона поиска (из-за этого сбиваются ссылки для всех последующих ячеек обеих таблица – кроме первой из указанных, что становится причиной некорректной работы функции).
3. Ошибки в данных электронных таблиц (лишние пробелы, числа вместо текста или наоборот, разные форматы дат и чисел, невидимые символы и т.д.)
4. Присутствие в таблице с источниками данных дубликатов искомого значения (по итогу ВПР выдаст данные из первого совпадения и проигнорирует все остальные аналогичные значения).

Когда ВПР не подходит для задачи

1. Нужно найти значение в столбце левее искомого — ВПР ищет только правее первого столбца диапазона, для обратного поиска нужна функция ИНДЕКС+ПОИСКПОЗ.
2. В таблице есть дубли искомых значений — ВПР вернёт только первую найденную запись, а не все совпадения.
3. Нужен поиск сразу по нескольким критериям без сложных формул — стандартная ВПР ищет только по одному критерию, для нескольких нужна дополнительная функция ЕСЛИ.
4. Структура таблицы часто меняется, столбцы добавляются или удаляются — номер столбца в ВПР указывается вручную и собьётся при изменении структуры.
5. Нужно искать данные по строкам, а не по столбцам — для этого существует отдельная функция ГПР

Таблица сценариев: какую функцию выбрать вместо ВПР

Запрос пользователя	Оптимальная функция таблицы Excel	Обоснование рекомендации
Требуется перенос по одному критерию из столбца, расположенного правее искомого значения	ВПР	Классика электронных таблиц Excel
Необходим поиск в столбце, расположенном левее искомого значения	ИНДЕКС+ПОИСКПОЗ	Универсальная функция поиска и переноса, работающая в любом направлении
Нужен поиск не по столбцам, а по строкам	ГПР (или горизонтальный просмотр)	Аналог функции ВПР, предназначенный для горизонтальных таблиц
В исходных данных много дубле и требуются все совпадения	Сводная таблица	Позволяет вывести все значения-дубликаты (а не только первое из них)
Поиск ведется в таблице с частой сменой структуры и добавлением столбцов	ИНДЕКС+ПОИСКПОЗ	Функция минимизирует риск сбоев в формулах при изменении структуры таблицы

Как работает ВПР в Google Таблицах и МойОфис

До недавнего времени другие электронные таблицы не составляли реальной конкуренции Excel. В течение нескольких последних лет все большую популярность получают аналоги продукта из пакета MS Office.

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

Поэтому разработчики конкурентов Excel внесли ее в собственные продукты, причем практически без изменений. То есть с тем же синтаксисом и принципом работы, как в первоисточнике.

FAQ

Что такое функция ВПР простыми словами?

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

Из каких аргументов состоит формула ВПР?

Функция предусматривает использование четырех аргументов: искомое значение, диапазон поиска (таблица), номер столбца и интервальный просмотр.

Чем ВПР отличается от ГПР и ИНДЕКС+ПОИСКПОЗ?

Функция ВПР ведет вертикальный просмотр при поиске, то есть позволяет найти значения в столбце справа от искомого. ГПР выполняет аналогичное действие, но по горизонтали, то есть работать для горизонтальных таблиц. Функция ИНДЕКС+ПОИСКПОЗ более универсальна, чем ВПР, так как ведет поиск во всех возможных направлениях.

Что будет, если в таблице есть дубли искомых значений?

Функция будет выводить только значение первого дубликата и игнорировать все остальные.

Кому подходит ВПР, а кому нужна другая функция?

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

Как научиться работать с Excel и ВПР?

Проще всего пройти обучение на онлайн-курсах с соответствующей программой подготовки.

Выбрать подходящий учебный курс по Excel и ВПР можно на edu.sravni.ru.