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

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

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

• Это самые основные функции Excel, которые вам необходимо понимать.

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

• Что такое функция ВПР?
• Формула функции ВПР в Excel 2016, 2013, 2010, 2007, 2003
• Видеоурок по использованию функции ВПР
• Пример 1. Используйте функцию ВПР для оценки рейтингов студентов и сотрудников:
• Пример 2. Используйте ВПР для абсолютного обнаружения и получения данных.
• Пример 3. Использование ВПР для извлечения данных
• Пример 4. Используйте функцию ВПР на двух разных листах Excel.
• Для чего использовать функцию ВПР?
• Что следует помнить о функции ВПР

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

Функция ВПР — это функция поиска данных в Excel. Я предполагаю, что у вас есть опыт работы с Excel и вы можете использовать базовые функции, такие как СУММА, СРЗНАЧ и СЕГОДНЯ. Одной из наиболее распространенных функций ВПР является функция данных, то есть она работает с таблицами базы данных или, проще говоря, со списками элементов. Список очень разнообразен. Вы можете создать таблицу о персонале вашей компании, категориях продуктов, списках клиентов или чем-либо еще. Ниже приведен список продуктов, которые компания А продает на рынке:

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

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

С другой стороны, функция ВПР ограничена и имеет опасные значения по умолчанию. В отличие от ИНДЕКС и ПОИСКПОЗ (или XПРОМ), для функции ВПР требуется полная таблица со значениями поиска в первом столбце. Это затрудняет использование функции ВПР с несколькими критериями. Кроме того, стандартное поведение VLOOKUP по сопоставлению позволяет легко получить неверные результаты. Но не беспокойся! Ключом к успешному использованию функции ВПР является освоение ее основ.

ВПР имеет 4 аргумента: искомое_значение, табличный_массив, столбец_индекс_номер и диапазон_просмотра. Lookup_value — это значение для поиска, а table_array — это вертикальный диапазон данных для поиска. Первый столбец table_array должен содержать искомые значения для поиска. Аргумент columns_index_num — это номер столбца извлекаемого значения, в котором первый столбец table_array — это столбец 1.

Наконец, range_lookup управляет поведением поиска совпадений. Если range_lookup имеет значение TRUE, функция ВПР выполняет приблизительное совпадение. Если range_lookup имеет значение FALSE, функция ВПР выполнит точное совпадение. Важно отметить, что параметр range_lookup является необязательным и по умолчанию имеет значение TRUE, поэтому VLOOKUP по умолчанию выполнит приблизительное совпадение.

Формула функции ВПР в Excel

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

=VLOOKUP(искомое_значение,table_array,col_index_num ,[range_lookup] )

Там:

• ВПР: имя функции
• Параметры, выделенные жирным шрифтом, являются обязательными.
• искомое_значение: значение, используемое для поиска.
• table_array: Таблица, содержащая искомое значение, указанное в абсолютном значении со знаком $ впереди, например: $A$3:$E$40.
• col_index_num: порядок столбца, содержащего искомое значение в table_array. Например, в таблице $A$3:$E$40 столбец B содержит искомое значение, тогда col_index_num здесь будет 2; таблица $C$3:$F$40, столбец E содержит искомое значение, тогда col_index_num здесь равен 3.
• range_lookup: диапазон поиска, TRUE эквивалентно 1 (относительный поиск), FALSE эквивалентно 0 (абсолютный поиск). Этот параметр не всегда требуется в формуле.

Относительный поиск можно применять только тогда, когда значения для поиска в table_array расположены в порядке (по возрастанию, убыванию или в алфавитном порядке). С такими таблицами вы можете использовать относительный поиск, который аналогичен использованию бесконечной функции ЕСЛИ . Для значений, которые невозможно найти или которые не отсортированы, используйте абсолютный поиск, чтобы найти точное значение.

Видеоурок по использованию функции ВПР

Пример 1: Функция ВПР для оценки рейтингов студентов и сотрудников

Предположим, у вас есть табели успеваемости студента следующего содержания:

Таблица регулирования ранжирования выглядит следующим образом:

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

В Excel эти две таблицы представлены следующим образом:

Здесь обнаруживаемое значение находится в столбце C, начиная со строки 6. Диапазон поиска: $A$18:$B$21, порядок столбца, содержащего обнаруженное значение, равен 2.

В ячейку D6 введите формулу: =ВПР($C6,$A$18:$B$21,2,1). Это формула относительного поиска. При желании вы можете выполнить абсолютный поиск (или потому, что рейтинг не отсортирован), добавив 0 к формуле следующим образом: =VLOOKUP($C6 ,$A$18:$B$21,2, 0). Нажмите Ввод.

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

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

Вам интересно, почему $ необходимо использовать перед C6? $ сохранит столбец C фиксированным, изменяя строки только при перетаскивании формулы вниз по таблице. Также есть $A$18:$B$21, который помогает исправить таблицу правил ранжирования, чтобы она не менялась при перетаскивании формулы.

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

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

Предположим, у вас есть следующая таблица сотрудников и городов их проживания:

Теперь вы хотите заполнить информацию о родном городе для сотрудников. В ячейку D4 введите формулу абсолютного поиска следующим образом: =Vlookup($A4,$A$16:$C$25,2,0)

Затем нажмите Enter. Щелкните маленький квадрат, который появляется в углу ячейки D4, и перетащите его вниз по таблице, чтобы скопировать формулу другим сотрудникам.

Чтобы заполнить информацию о квалификации сотрудников, в ячейке E4 введите формулу абсолютного поиска: =VLOOKUP($A4,$A$16:$C$25,3,0)

Продолжаем нажимать Enter и прокручиваем вниз, чтобы скопировать формулу остальным сотрудникам, получим следующий результат:

Пример 3. Использование ВПР для извлечения данных

Продолжая использовать набор данных из примера 2, теперь мы найдем родные города трех сотрудников: Нгуен Хоанг Ань, Чан Ван Ань и Нгуен Куанг Винь. Я извлек его в новую таблицу F15:G18.

Я выполню этот поиск в таблице A3:E13, указав свой родной город и квалификацию. Теперь введите следующую формулу абсолютного поиска в ячейку G16: =VLOOKUP($F16,$B$3:$E$13,3,0) > нажмите Enter.

Скопировав формулу для оставшихся 2-х сотрудников, получим следующий результат:

Обратите внимание, что в этом примере значение обнаружения находится в столбце B, поэтому область обнаружения рассчитывается на основе столбца B, а не на основе столбца A.

Пример 4. Используйте функцию ВПР на двух разных листах Excel.

Возвращаясь к набору данных из примера 2, после заполнения квалификации сотрудника и родного города назовем лист QTM.

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

Чтобы найти данные о «Квалификации» сотрудника, введите формулу: =VLOOKUP($B4,QTM!$B$3:$E$13,4,0) в ячейку C4 листа QTM1.

Там:

• B4 — столбец, содержащий значение, используемое для обнаружения.
• КТМ! — имя листа, содержащего таблицу со значением, которое нужно обнаружить. После имени листа добавьте !
• $B$3:$E$13 — это таблица, содержащая искомые значения, и лист, содержащий таблицу (QTM).
• 4 – порядковый номер графы «Квалификация», рассчитываемый по графе «ФИО» листа QTM.
• 0 — абсолютное обнаружение.

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

Чтобы найти данные «Должность» сотрудника, в ячейку D4 листа QTM1 введите формулу: =VLOOKUP($B4,QTM!$B$3:$E$13,2,0), нажмите Enter.

Скопировав формулу для остальных сотрудников, получим следующее:

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

Сначала нам нужно найти точный ответ на вопрос «для чего мы используем функцию ВПР?».

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

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

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

Что следует помнить о функции ВПР

Вот список важных вещей, которые следует помнить о функции ВПР в Excel:

• Если аргумент range_lookup опущен, VLOOKUP разрешит неточные совпадения, но все равно будет использовать точные совпадения, если они доступны.
• Самым большим ограничением функции является то, что она всегда принимает значение справа. Функция будет извлекать данные из столбцов справа от первого столбца таблицы.
• Если столбец поиска содержит повторяющиеся значения, функция ВПР будет соответствовать только первому значению.
• Функция не чувствительна к регистру.
• Предположим, что на листе существует формула ВПР. В этом случае формулы могут сломаться, если вы вставите столбец в таблицу, поскольку значения индекса столбца жестко запрограммированы и не изменяются автоматически при вставке или удалении столбца.
• ВПР позволяет использовать подстановочные знаки, например звездочки (*) или вопросительные знаки (?).
• Предположим, в таблице вы работаете с функцией, которая содержит числа, введенные в виде текста. Если вы просто получаете числа в виде текста из столбца таблицы, это не имеет значения. Но если первый столбец таблицы содержит числа, введенные в виде текста, то #N/A! будет отображаться, если искомое значение также не является текстом.
• Ошибка № Н/Д! Происходит, если функция ВПР не находит совпадения для предоставленного искомого_значения.
• Ошибка #ССЫЛКА! произойдет, если:
  • Аргумент col_index_num превышает количество столбцов в предоставленном table_array.
  • Формула попыталась ссылаться на несуществующие ячейки.
• Ошибка #VALUE! произойдет, если:
  • Аргумент col_index_num меньше 1 или не распознается как числовое значение.
  • Аргумент range_lookup не распознается как одно из логических значений TRUE или FALSE.

Ключевые различия между функциями ВПР и XПРОМ в Excel

Д - Да

Н - Нет

Узнать больше:

• Как использовать функцию ЕСЛИ в Excel
• Как использовать функцию СРЗНАЧЕСЛИ в Excel

Зная, как использовать VLookup, вам также необходимо знать распространенные ошибки и способы их устранения.

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

Искомое значение находится в неправильном столбце

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

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

Как устранить проблему:

Причина, по которой VLOOKUP не нашел нужное значение, может заключаться в том, что этого значения нет в таблице. Однако если он возвращает все значения #Н/Д, а искомое значение представляет собой текст, то, скорее всего, это связано с тем, что оно может читаться только слева направо.

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

Теперь формула вернет значение:

В целом, VLOOKUP — отличный способ быстрее запрашивать данные в Microsoft Excel. Хотя запросы VLOOKUP выполняются вертикально по столбцам и имеют ряд других ограничений, Microsoft постоянно обновляет функции поиска Excel, чтобы расширить их применимость. Например, HLOOKUP, XLOOKUP... может помочь вам искать данные в разных направлениях. LuckyTemplates.com продолжит предоставлять вам информацию о том, как использовать эту функцию, в следующих статьях.