Как пользоваться функцией ВПР (VLOOKUP) в Excel: пошаговая инструкция с примерами, картинками, формулами

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

Давайте разберемся, что именно представляет собой ВПР в Excel.

Название функции ВПР

Название функции ВПР расшифровывается как Вертикальный ПРосмотр (VLOOKUP — Vertical Lookup).

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

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

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

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

Типичные сценарии использования функции ВПР:

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

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

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

Пошаговая инструкция использования функции ВПР

Сейчас мы пошагово используем функцию ВПР в экселе.

Определяем, что мы хотим получить

Предположим, что у нас на одном листе есть две таблицы. Таблица 1 содержит название товаров и их количество на складе. Таблица 2 — название тех же товаров в другом порядке и цену каждого товара за килограмм.

Наша задача — дополнить первую таблицу данными о цене из второй.

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

Составляем формулу ВПР

Добавим в первую таблицу третий столбец с ценами из второй таблицы. В нужную ячейку, с которой начнётся вставка цен, начнём вводить формулу =ВПР(

Всплывёт подсказка, содержащая аргументы функции.

Давайте рссмотрим каждый из них.

Синтаксис функции ВПР

Функция ВПР имеет следющий вид:

ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр])

Искомое значение

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

В нашем случае искомым значением будет первая ячейка столбца с названием товара A3 — капуста. После выбора ячейки ставим точку с запятой.

Таблица

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

Важно, чтобы диапазон включал столбец с искомым значением (он должен быть первым в диапазоне) и столбец, откуда будут браться данные для переноса.

Поэтому мы выделяем и столбец с ценой, и основной столбец с названием товара второй таблицы.

Если между основным столбцом второй таблицы и столбцом с ценой были бы другие столбцы, их тоже нужно было бы добавить в выделение.

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

Ставим точку с запятой.

Номер столбца

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

В нашем примере во второй таблице всего два столбца. Название — первый столбец, цена — второй. Значит. в аргументе «номер_столбца» указываем число 2 и ставим точку с запятой.

Интервальный просмотр

Этот аргумент отвечает за точность поиска значений функцией ВПР:

0 — точное совпадение, ВПР ищет только введенное значение.

1 — приблизительное совпадение, ВПР найдет ближайшее значение, если введенного нет.

Нам нужно точное совпадение цены, поэтому мы ставим 0, закрываем скобку ) и нажимаем Enter.

Результат использования функции ВПР

И вот наша цена на капусту оказалась в нужном нам столбце.

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

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

Давайте усложним задачу. В прошлом примере мы в четвёртый аргумент установили значение 0. Это значит, что формула ищет точное соответствие цене.

В новом примере у нас есть две таблицы:

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

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

Например, если план выполнен менее чем на 100%, то премия не полагается.

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

Важное условие: данные из второй таблицы, где указаны интервалы, должны быть отсортированы по возрастанию критерия — крайнего левого столбца.

Формула работает следующим образом: берёт значение выполнения плана продаж из первой таблицы (например, 124% у Александра) и ищет его среди всех значений второй таблицы сверху вниз.

Найдя первое превышающее этот критерий значение (в нашем случае 110% из второй таблицы), формула делает шаг выше.

И смещается на указанное значение столбцов вправо. То есть, ищет ближайшее меньшее значение для искомого критерия.

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

Давайте напишем формулу ВПР для нового примера.

Искомое значение — ячейка B2.

Таблица — ячейки F2:G5. После выделения этого диапазона зафиксируем их клавишей F4, чтобы они не смещались от протягивания функции.

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

Последний аргумент — интервальный просмотр — укажем 1 (приблизительное совпадение).

Нажимаем на Enter и видим, как функция просмотрела все значения второй таблицы, дошла до последнего (120%). Так как дальше значений не было, то ВПР забрала самую большую (30%) премию и перенесла её в первую таблицу. Два раза кликнем на зелёный квадратик, чтобы данные протянулись по всей таблице.

Проверим, всё ли правильно. Мария и Ольга не выполнили план и не получат премию.

Александр единственный выполнил более 120% плана и получит максимальную премию в 30%. Всё верно.

Использование функции ВПР для сравнения двух таблиц

В этом примере мы будем сравнивать две таблицы с помощью ВПР.

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

Для этого мы в новом прайсе делаем активной ячейку J4 и начинаем строить функцию ВПР, как мы это делали в пошаговой инструкции выше.

Искомое значение — ячейка E4.

Таблица — ячейки A4:B18. После выделения этого диапазона зафиксируем их клавишей F4, чтобы они не смещались от протягивания функции.

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

Интервальный просмотр — укажем 0 (точное совпадение).

Далее жмём Enter и протягиваем функцию, потянув вниз зелёный квадратик внизу справа ячейки и два раза кликнув по нему.

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

Использование функции ВПР с несколькими условиями (поиск по двум столбцам)

В этом примере мы будем осуществлять поиск функцией ВПР по нескольким критериям.

Например, у нас есть таблица цен товаров с разбивкой по месяцам. Нам нужно получить цену нужного товара за нужный месяц. Функция ВПР ищет по одному столбцу, а нам нужно осуществить поиск по двум (Товар и Месяц)

Чтобы обойти это ограничение, мы добавим слева новый столбец «Ключ», в котором мы будем склеивать содержимое ячеек столбцов «Товар» и «Месяц», используя оператор & (=B2&C2).

Протянем формулу двойным щелчком по зелёному крестику внизу справа ячейки. Получаем столбец с уникальным ключом (например, «ПерсикЯнварь»), по которому потом будет работать ВПР.

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

Получим такую же склеенную ячейку.

И теперь задача сводится к обычному применению формулы ВПР.

Для этого мы в новом прайсе делаем активной ячейку L3 и начинаем строить функцию ВПР, как мы это делали в пошаговой инструкции выше. Пишем =ВПР( и начинаем указывать аргументы:

Искомое значение — ячейка G3.

Таблица — ячейки A1:D161. После выделения этого диапазона зафиксируем их клавишей F4, чтобы они не смещались от протягивания функции.

Номер столбца укажем 4, потому что функция должна подставить во вторую таблицу значения столбца 4 (Цена) из первой таблицы.

Интервальный просмотр — укажем 0 (точное совпадение).

Нажимаем Enter и видим, что, действительно, функция посчитала значение верно.

Советы по эффективному использованию функции ВПР в Excel

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

1. Понимание синтаксиса функции ВПР:
   Прежде чем начать использовать функцию ВПР, убедитесь, что вы полностью понимаете ее синтаксис. Функция ВПР принимает три обязательных аргумента: искомое значение, диапазон для поиска и столбец для извлечения данных. Ознакомьтесь с этими аргументами и убедитесь, что они корректно указаны в вашей формуле.
2. Использование абсолютных ссылок:
   Если вы планируете копировать свою функцию ВПР в разные ячейки, используйте абсолютные ссылки для фиксации диапазонов поиска и извлечения данных. Это предотвратит их изменение при копировании формулы.
3. Обработка ошибок с помощью Функции ЕСЛИОШИБКА:
   Иногда функция ВПР может вернуть ошибку, если искомое значение не найдено. Чтобы избежать проблем, используйте функцию ЕСЛИОШИБКА для обработки ошибок и вывода пользовательских сообщений или значений по умолчанию.
4. Сортировка данных:
   Перед использованием функции ВПР убедитесь, что ваши данные отсортированы в правильном порядке. Функция ВПР ищет значение в диапазоне и возвращает первое совпадение. Если данные не отсортированы, результат может быть неправильным.
5. Использование диапазонов именованных ячеек:
   Если вы работаете с большими диапазонами данных, рассмотрите возможность именовать эти диапазоны. Это сделает формулу более читаемой и упростит ее обслуживание.
6. Экспериментирование с вариантами сравнения:
   Функция ВПР предоставляет несколько вариантов сравнения, таких как точное совпадение, приближенное совпадение и другие. Используйте разные варианты сравнения, чтобы точно настроить поиск ваших данных.
7. Обработка дубликатов и ошибок:
   При использовании функции ВПР, особенно в больших наборах данных, могут возникать дубликаты или ошибки. Рассмотрите внедрение дополнительных проверок и фильтров, чтобы обнаруживать и устранять такие проблемы.
8. Документирование формул:
   Не забывайте документировать ваши формулы, особенно если вы делитесь файлами с коллегами. Добавьте комментарии или ячейки с пояснениями, чтобы другие пользователи могли легко разобраться в вашем коде.

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

Не забывайте, что:

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

Ограничения функции ВПР в Excel

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

Ошибки при использовании функции ВПР в Excel и их причины

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

Ошибка #Н/Д в ВПР

• Причина: Эта ошибка возникает, когда функция ВПР не может найти искомое значение в указанном диапазоне.
• Решение: Убедитесь, что значение, которое вы ищете, существует в указанном диапазоне. Проверьте, правильно ли указан аргумент [Искомое_значение], и убедитесь, что он соответствует формату ячеек в диапазоне, который вы ищете.

Ошибка #ССЫЛКА! в ВПР

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

Ошибка #ЗНАЧ! в ВПР

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

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

Часто задаваемые вопросы

Как искать значения для ВПР на нескольких разных листах?

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

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

Таким образ, отличие заключается только в том, что к диапазону поиска добавится ссылка на второй лист ‘Липецкоблгаз’!.

Может ли ВПР искать значения по горизонтали?

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

Каково ограничение диапазона для ВПР?

Ограничение диапазона для ВПР в Excel составляет примерно 1 048 576 строк, что является максимально допустимым количеством строк в рабочем листе Excel.

В чем разница между ВПР и ПРОСМОТР в Excel?

В Excel VLOOKUP и LOOKUP — это две разные функции, используемые для поиска и извлечения данных из набора данных. Основное различие между ними заключается в их функциональности и синтаксисе.

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

Может ли ВПР работать с несколькими рабочими листами?

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

Что такое формула ГПР?

Формула ГПР — это встроенная функция Excel, используемая для горизонтального поиска. Она ищет значение в первой строке диапазона таблицы и возвращает соответствующее значение из другой строки того же столбца.

Как избежать ошибок при использовании ВПР?

Чтобы избежать ошибок, необходимо:

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

Как работать с дубликатами в ВПР?

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

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

Что делать, если функция ВПР пишет Н/Д в ячейке

Предположим, что во второй таблице нет данных о чесноке и яблоках. Тогда при построении нового столбца в первой таблице функция ВПР выведет следующие символы «#Н/Д», что означает нет данных.

Это значит, что функция ВПР не нашла по критерию поиска данные о цене чеснока и яблок.

Если вас это не устраивает и вместо #Н/Д вам нужно другое значение (например. «пусто» или «0»), то давайтем научимся оборачивать функцию ВПР другой функцией, которая и сделает такую замену.

Для этого нажимаем на ячейку с формулой ВПР (C3) и редактируем её следующим образом:

Получившийся вариант: =ЕСЛИОШИБКА(ВПР(A3;$G$3:$H$21;2;0);0)

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

Жмём Enter, опять протягиваем данные зелёным квадратиком в углу ячейки — и видим, что #Н/Д заменилось на нужный нам ноль.

Итоги

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

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

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

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