Функция ВПР в Excel

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

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

Также рекомендуем потом прочитать про функцию ГПР, по смыслу очень похожую и не менее часто используемую.

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

Если коротко, то можно просто запомнить следующее:

Функция ВПР применяется для совмещения данных из разных таблиц.

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

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

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

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

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

Смысл этих аргументов следующий:

• Искомое_значение
  Обязательный параметр. Это то самое значение, которое должна искать функция в первом(!) столбце таблицы, заданной аргументом 2 (см. ниже). Здесь обычно указывается адрес ячейки, в которой находится нужное нам значение (в примере, рассмотренном ниже, это адрес ячейки в одной из строк колонки «Должность»). Указывать простой текст или числовую константу не имеет смысла, так как функция ВПР используется для обработки больших массивов данных. Если искомое_значение меньше, чем наименьшее значение в первом столбце аргумента таблица, функция ВПР возвращает значение ошибки #Н/Д.
• Таблица
  Тоже обязательный параметр. Это та таблица, данные из которой мы собираемся добавить. Таблица указывается как диапазон ячеек (в примере, рассмотренном ниже, это диапазон А10:B12). Обратите внимание, что диапазон ячеек должен быть указан таким образом, чтобы в первом(!) его столбце находились данные, которые мы ищем (в нашем примере это столбец «Должность»). Значения в первом столбце могут быть текстовыми, числовыми или логическими. Текстовые значения в нижнем и верхнем регистре считаются эквивалентными.
• Номер_столбца
  Указывать обязательно. Это порядковый номер столбца, данные из которого нужно вернуть функции ВПР в качестве ответа, если в первом столбце второй таблицы будет найдено искомое значение (аргумент 1). Столбцы нумеруются начиная с 1 (единица), так что в нашем примере (см. далее) это столбец 2 («Зарплата»). Итого: если номер_столбца = 1, то возвращается значение из первого столбца таблицы; если номер_столбца = 2 — значение из второго столбца таблицы и т. д.
• интервальный_просмотр
  Варианты значений: 0 или 1. Если указать 0, то функция будет искать точное совпадение для параметра 1 (искомое значение); если указать 1, то поиск будет производиться приближённо. Чаще всего используется именно 0, поскольку требуется найти именно то, что у нас в аналогичном столбце первой таблицы (в нашем примере это должность сотрудника).

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

В простом примере ниже эти аргументы рассмотрены на практике.

Функция ВПР, пошаговая инструкция

Итак, изначально у нас есть две таблицы, которые Вы можете видеть на скриншоте ниже. Общим столбцом для обеих таблиц является «Должность».

[нажмите на картинку для увеличения]
Справка: как сохранять фото с сайтов

Цель: добавить в первую (верхнюю) таблицу в столбец «Зарплата» данные из аналогичного столбца второй (нижней) таблицы.

Если Вы подумали, что это можно сделать и вручную, то это большая ошибка, поскольку строк в таблицах может быть многие тысячи, а порядок их следования в обеих таблицах вовсе не обязан совпадать!

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

Функция ВПР, инструкция

• Добавим ВПР в ячейку.
  Здесь это ячейка С4. Вставить формулу можно либо вручную (соблюдая синтаксис, описанный ранее), либо при помощи Мастера функций.
• Указываем параметр «Искомое значение»
  В нашем примере нужно указать Excel, что мы будем искать во второй таблицы. Поскольку общим столбцом в обеих таблицах является Должность, то в качестве аргумента укажем адрес ячейки, содержащей должность (в примере это B4).
• Указываем параметр «Таблица».
  Указываем диапазон ячеек, в котором нужно искать должность из первой таблицы. В нашем примере это будет «A$10:B$12». Ещё раз обращаем внимание на то, что заголовки таблицы в диапазон попадать не должны.
• Указываем параметр «Номер столбца».
  Поскольку мы хотим в первую таблицу добавить значение зарплаты, то укажем номер столбца 2 («Должность» это первый столбец указанной в предыдущем аргументе таблицы, а «Зарплата» — второй столбец).
• Указываем параметр «Интервальный просмотр».
  В данном случае нас интересует именно точное совпадение названия должности в обеих таблицах поэтому укажем интервальный просмотр 0.

Обратите внимание на символ «$» перед номерами строк в диапазоне (аргумент ВПР номер 2). Это нужно для того, чтобы номера строк оставались неизменными при копировании формулы в другие ячейки.

В результате для ячейки C4 мы получим следующую формулу:
=ВПР(B4; A$10:B$12; 2; 0)

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

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

Поскольку в качестве параметра «Номер столбца» мы указали 2, то функция ВПР вернёт в результате то, что указано в колонке «Зарплата».

Итого полученный результат: «40 000»

Это значение и будет результатом выполнения функции ВПР в данном конкретном случае. На скриншоте выше Вы можете видеть, что значение «40 000» вписано в ячейку C4 первой таблицы.

Скачать файл Excel (*.xlsx) с этим примером Вы можете после статьи.

Заполним аналогичными формулами остальные ячейки

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

В данном случае лишь необходимо учесть, что диапазон ячеек, в котором производится поиск (аргумент 2), не должен меняться. Именно поэтому перед номерами строк в диапазоне поставлены символы «$». Для первого аргумента (искомое значение) это, напротив, не нужно, поскольку для каждой строки первой таблицы искомое значение будет в разной ячейке (в нашем примере: A4, A5, A6).

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

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

• Не указали символ доллара в диапазоне (аргумент 2).
  В этом случае первая формула, введённая вручную, будет верной, а скопированные в другие ячейки — нет, поскольку диапазон поиска смещается в соответствии с правилами Эксель при копировании формул.
• Неверно указан диапазон поиска.
  Опять же это ошибка в аргументе 2. Диапазон ячеек нужно указывать так, чтобы столбец, в котором ищется совпадение, был первым в выделенном диапазоне. Иначе конечно ничего найдено не будет.
• Неверно указан номер столбца (аргумент 3).
  Например, можно указать столбец с таким номером, которого нет в выделенном диапазоне. Или номер столбца указан так, что в этом столбце находятся не те данные (напоминаем, что столбцы нумеруются с единицы).

Если у Вас не работает функция ВПР, то значит скорее всего Вы неверно задали её параметры. Это бывает чаще всего, а не «глючит Excel».

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

Функция ВПР в Excel, примеры

Более сложный пример использования функции ВПР можно посмотреть во втором прикреплённом файле после статьи, он называется «Использование функции ВПР на примере.zip». В архиве файл формата *.xlsx, в котором рассмотрен пример совмещения двух таблиц, находящихся на разных листах Эксель.

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

Также в примере задействованы и некоторые другие формулы, например функция ЕСЛИ.

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

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