Анализ данных • 23 октября 2023 • 5 мин чтения

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

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

Зачем нужна функция ВПР и когда её применяют

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

В этой таблице нужно сделать ещё один столбец — с должностями сотрудников

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

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

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

Как работает функция ВПР

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

Формула ВПР в Excel состоит из 4 аргументов:

1. Искомое значение — это столбец с искомыми данными в той таблице, куда нужно перенести данные. В нашем примере — имена сотрудников.
2. Таблица — это диапазон, в котором ВПР будет искать данные, которые нужно перенести. В него войдут ячейки с именами сотрудников и даты их рождения из второй таблицы.
3. Номер столбца — это порядковый номер столбца, из которого нужно забрать данные. В нашем случае это столбец с датами рождения во второй таблице. Причём важно, что это номер столбца в выделенном фрагменте.
4. Интервальный просмотр — это условие поиска; этих условий два — точное совпадение (0, ЛОЖЬ или FALSE) и неточное (1, ИСТИНА или TRUE).

В последнем аргументе, его иногда называют «Сортировка», почти всегда нужно использовать точный поиск. Неточный работает в основном с цифрами — функция будет искать примерное значение, то есть равное числу или меньше него. Если искомому запросу соответствует несколько ячеек, функция выберет только одну — ту, что выше.

Как пользоваться ВПР: руководство с примерами

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

Шаг 1. Создать новый столбец — тот, в который нужно перенести данные. Назовём его «Должность». Дальше можно нажать на кнопку Fx прямо над основным полем таблицы в Excel — ВПР найдётся в открывшемся окне. Или написать =ВПР() в самой ячейке или в строке ввода формул — рядом с кнопкой Fx.

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

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

Если переходить с листа на лист, окно не закроется — в нём можно продолжать работать

Шаг 3. В качестве искомого значения задать координаты первой ячейки столбца, для которого нужно найти данные. В нашем случае это А2 — первая ячейка столбца с фамилиями в таблице, в которую нужно перенести должности.

Шаг 4. Заполнить второе поле — «Таблица». Здесь нужно задать интервал, в котором функция ВПР (VLOOKUP) найдет искомые значения. Для этого нужно переставить курсор во второе поле и перейти на второй лист — в таблицу с должностями. Прямо в таблице выделить интервал. В нашем случае это A2–B15.

Когда интервал задан, нужно его закрепить. В Windows это делают с помощью F4, а на macOS — Cmd+T. После этого в формуле вокруг цифр в интервале появятся знаки доллара — $. Это значит, что можно снова переключаться между листами — на значения в построителе формул это не повлияет.

Чтобы задать интервал для ВПР, нужно просто выделить его курсором

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

Шаг 6. Заполнить последнее поле — «Интервальный просмотр». Здесь два варианта: ЛОЖЬ (0 или FALSE) или ИСТИНА (1 или TRUE). Выбираем ЛОЖЬ, потому что нам нужно точное совпадение. Какой знак ставить в формуле, зависит от версии Excel. В большинстве случаев программа сама подскажет, что использовать.

Итоговая формула ВПР в нашем примере такая: =ВПР(A2;Лист2!$A$2:$B$15;2;0). Когда всё готово, нажимаем на «Ок» в построителе формул.

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

Осталось только протянуть формулу ВПР (VLOOKUP) на весь столбец.

Функция ВПР перенесла все функции в таблицу — задача выполнена

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

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

Почему не работает ВПР в Excel

Ошибка	Решение
❌В итоговом столбце формула ВПР может выдать ошибку #Н/Д. Это значит, что функция на нашла нужных данных в другой таблице. Такое случается, когда в последнем аргумента задана точная сортировка, но точного совпадения формула не видит.	Проверить, нет ли в ячейках скрытых знаков и лишних пробелов.
❌Если данные в таблице дублируются, функция ВПР вернет только первую найденную запись. Она не умеет копировать две одинаковых ячейки и, скорее всего, где-то ошибётся.	Удалить дубли с помощью кнопки Removes Duplicates во вкладке «Данные». Другой вариант — вместо ВПР использовать сводную таблицу.
❌Искомое значение ниже, чем самое низкое в столбце. Такое бывает, когда в последнем аргументе задана сортировка ИСТИНА, то есть поиск может быть неточным. В этом случае ВПР ищет значение, равное заданному или меньше него. Если такого нет — выдаёт ошибку #Н/Д. Например, функции нужно найти в таблице значение 50, сортировка — ИСТИНА. Если в таблице нет значения 50, а самое маленькое число — 51, ВПР ничего на найдёт.	Исправить искомое значение.

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

Главное о ВПР

● Функция ВПР, она же функция Vlookup в Excel, чаще всего используется для объединения данных в двух таблицах. Иногда ВПР используют для сравнения таблиц в Excel. Формула ВПР состоит из четырёх аргументов: искомого значения, таблицы, номера столбца и интервального просмотра.

● Чтобы задать функцию, нужно открыть построитель формул. В первом аргументе ввести номер первой ячейки в столбце с искомыми данными. Во втором — диапазон, в котором ВПР будет их искать. В третьем — номер столбца в этом диапазоне, из которого нужно забрать данные. В четвертом — задать точность сортировки: ЛОЖЬ или ИСТИНА.

● Обе таблицы должны быть в одном файле, но на разных листах. Иначе ВПР не сработает.

● Когда пользователь выбирает диапазон для поиска, данные в нём нужно закрепить: в Windows нажать на F4, в macOS — на Cmd+T. В гуглтаблицах закрепить диапазон можно просто поставив точку с запятой — этот знак разделяет аргументы в формуле.

● Номер столбца, из которого нужно взять данные, это номер внутри заданного диапазона, а не всего листа в Excel.

● Если ВПР не работает, изучить руководство от Microsoft.