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

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

Как вызвать функцию ВПР. Функция ВПР в Excel

Прежде всего, давайте посмотрим, как вызвать эту функцию. Выберите вкладку «Формулы». Найдите кнопку «Вставить функцию». И мы его награждаем. Также вы можете вызвать функцию ВПР, нажав Shift + F3.

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

Теперь перейдем непосредственно к параметрам использования функции ВПР.

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

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

Перед вызовом функции ВПР выделим нужную нам ячейку, которая будет содержать нашу формулу функции и, следовательно, значение, которое мы хотим видеть. В нашем случае это ячейка G3. Эта ячейка находится в столбце «Цена» в таблице 2. Функция ВПР позволяет вам взять цену конфеты A из таблицы 1 и ввести ее в столбец «Цена» таблицы 2 напротив Candy A.

Вызовите функцию ВПР, как описано выше.

Аргументы функции. Функция ВПР в Excel.

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

Подстановочное значение, которое должно быть найдено в указанном нами диапазоне в строке таблицы. В нашем примере мы обозначаем Конфеты G (ячейка E3, таблица №2). Так как это значение стоит первым в столбце «Название сладостей» Таблицы 2. (Это несущественно, но удобно). Это значение, которое наша функция будет искать в Таблице №1.

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

Таблица.

Здесь нужно указать диапазон таблицы, в котором будет осуществляться поиск нужного нам значения и данных, которые мы хотим передать. В нашем примере это таблица № 1. Значение, с которым будет производиться поиск, — это название сладостей. Данные, которые мы хотим передать, — это цена шоколадных конфет. Просто ставим курсор в строку таблицы и выбираем нужный нам диапазон. В нашем примере это диапазон из Таблицы 1 — B1: C12. В этом случае ссылки необходимо сделать абсолютными, добавив знак $. Это можно сделать, просто добавив эти символы в ячейки диапазона в строке Таблица — $ B $ 1: $ C $ 12.

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

Как это сделать. Выбираем нужный нам ассортимент. Таблица 1. Выберите вкладку «Формулы» и нажмите кнопку «Установить имя». Щелкните. Появится диалоговое окно «Создать имя». Пишем любое имя. Но вы должны помнить об этом. Например Candy. Щелкните ОК.

В строке Таблица вместо диапазона нужно будет ввести присвоенное нами имя — Candy 

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

Функция ВПР ищет значение в крайнем левом столбце таблицы указанного диапазона поиска. По умолчанию функция присваивает этому столбцу номер 1. В нашем примере крайний левый столбец — это название шоколадных конфет в таблице №1. И в строке Column_number нам нужно указать, какой номер имеет столбец, из которого мы хотим перенести данные. В нашем примере это столбец «Цена» в таблице № 1. Он «второй» по порядку, если считать слева направо, из столбца «Имя» сладостей, таблица 1. Следовательно, в строке Number_column мы пишем число 2. Если в столбце «Цена», то таблица n. 1 располагалась по порядку не на втором месте, а на десятом, то мы должны указать число 10 соответственно в строке Number_column.

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

В этой строке мы пишем цифру ноль «0». Это означает, что функция ВПР будет искать точные совпадения между значениями поиска (Lookup_value) и значениями в крайнем левом столбце диапазона поиска (Таблица). В нашем примере мы будем искать точные совпадения между столбцом Candy Name, Table No. 1 и столбец Candy Name в Таблице № 2.

Если поставить цифру «1», функция будет искать не точное совпадение, а близкое к нашим критериям поиска.

Вот как все это выглядит вместе.

Щелкните ОК.

Мы распространяем формулу на весь столбец «Цена» в таблице 2. Все цены перенесены из таблицы 1 в таблицу 2.

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

У нас есть таблица нет. 1 и таблица № 2. Каждая таблица состоит из столбца. Для понимания алгоритма функции ВПР в данном случае достаточно таких простых таблиц. Столбцы содержат почти одинаковые данные. В то же время нам нужно сравнить их и выяснить, какие данные есть в таблице 2, а не в таблице 1.

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

В диалоговом окне «Аргументы функции» введите следующие данные:

Ценность исследования. Это значение ячейки таблицы n. 2, наличие которого мы проверяем в таблице № 1. В нашем примере это ячейка F3 (значение 9).

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

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

Interval_view. Здесь мы ставим ноль «0», так как мы хотим, чтобы функция искала точные совпадения.

Щелкните OK и распространите функцию на весь столбец. В некоторых ячейках вместо нужного значения отображается ошибка формулы: # Н / Д. В данном случае это означает, что указанные критерии поиска не были найдены в отмеченном столбце.

В нашем примере Таблица No. 2 имеет значение 17 и значение 10. При проверке в ячейках с функцией ВПР вместо нужного значения появилась ошибка № Н / Д. Это означает, что таблица №. 1 не имеет ячейки со значением 17 и значением 10.

Вы можете убедиться с точностью до наоборот. И найдите данные в таблице 1, но не в таблице 2.

Обратите внимание. Функция ВПР в Excel.

Функция ВПР ищет значения (это значения, указанные в строке Lookup_value) в первом (крайнем левом) столбце таблицы, диапазон которой указан в строке таблицы.

Например, в таблице 1 был добавлен столбец Категория, и теперь столбец Candy Name уже не первый, а второй. Если мы укажем в строке таблицы, как диапазон, все ячейки таблицы n. 1, функция ВПР не будет работать (ошибка — # N / A), так как она будет выполнять точный поиск в столбце Категория, Таблица №1, Значения столбца Sweet Name, Таблица № 2. И не найдет точных совпадений.

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

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

Вы можете искать в разных листах. Алгоритм работы такой же. Формула функции будет выглядеть так: = ВПР (E6; Sheet1! $ B $ 1: $ C $ 11; 2; 0). В нашем примере формулы функция ВПР находится на листе 2, а поиск значений и передача данных из диапазона поиска происходит на листе 1. Вместо диапазона вы можете использовать указанное имя. Например Candy. Таким образом, формула функции будет выглядеть так: = ВПР (E6; Candy; 2; 0).

Возможные ошибки.

# N / A — столбец таблицы, в которой выполняется поиск, а не крайний левый столбец в диапазоне поиска.

# N / A — диапазон искомой таблицы не заблокирован. Должны использоваться абсолютные ссылки ($) или диапазон должен называться Указанный.

# Н / Д — функция ВПР не находит точное совпадение в диапазоне поиска на основе указанного значения поиска.

# N / A — возможно, вам потребуется отсортировать диапазон поиска в порядке возрастания.

#СВЯЗЬ! — возможно, что номер столбца, который указан в строке Column_Number, указан неверно, и функция не может найти данные, которые она должна передать.