Функция ВПР в Excel чрезвычайно полезна при поиске данных. Вот что вам нужно знать об использовании функции ВПР в Excel .
Вы хотите проверить определенное значение в электронной таблице Excel и найти информацию о нем. В этом случае запуск VLOOKUP сэкономит вам значительные усилия. Это один из лучших способов выполнить вертикальный запрос в Microsoft Excel. Статья расскажет вам значение, использование и наглядные примеры функции ВПР в Microsoft 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: Функция ВПР для оценки рейтингов студентов и сотрудников
Предположим, у вас есть табели успеваемости студента следующего содержания:
| Нет | Имя и фамилия | Средний балл | Классификация |
| первый | Нгуен Хоанг Ань | 9.1 | |
| 2 | Тран Ван Ань | 8.3 | |
| 3 | Нгуен Куан Винь | 9,5 | |
| 4 | Чан Хонг Куанг | 8,6 | |
| 5 | До Тхань Хоа | 5.0 | |
| 6 | Ле Хонг Нгок | 4,5 | |
| 7 | Доан Тхань Ван | 7.2 | |
| 8 | Нго Нгок Бич | 0,0 | |
| 9 | Хоанг Тху Тао | 6.6 | |
| десять | Динь Минь Дык | 8,7 |
Таблица регулирования ранжирования выглядит следующим образом:
| Положение о рейтинге | |
| 0 | Слабый |
| 5,5 | Середина |
| 7 | Скорее |
| 8,5 | Хороший |
Теперь мы воспользуемся функцией ВПР для ввода оценок учащихся. Вы заметите, что таблица ранжирования выстроена в порядке от низкого к высокому (от слабого к хорошему), поэтому в данном случае мы можем использовать относительный поиск.
В 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
|
ВПР |
Xlookup |
|
|
Точное совпадение по умолчанию |
Н |
Да |
|
Возвращает значение справа от искомых данных. |
Да |
Да |
|
Возвращает значение слева от искомых данных. |
Н |
Да |
|
Возвращает более одного значения |
Н |
Да |
|
Столбец поиска необходимо отсортировать |
Только приблизительные совпадения |
Только двоичный поиск |
|
Поиск сверху вниз |
Да |
Да |
|
Сердце снизу вверх |
Н |
Да |
|
Бинарный поиск |
Н |
Да |
|
Настройте сообщение «значение поиска не найдено» |
Н |
Да |
|
Найти подстановочные знаки |
Да |
Да |
|
Точный поиск |
Да |
Да |
|
Приблизительный поиск возвращает следующее меньшее значение. |
Да |
Да |
|
Приблизительный поиск возвращает следующее большее значение. |
Н |
Да |
|
Ложное значение может быть возвращено, если искомое значение не отсортировано. |
Y – приблизительный |
Н – приблизительный, Y — двоичный |
|
Добавление новых столбцов для поиска таблиц может нарушить работу формул. |
Да |
Н |
Д - Да
Н - Нет
Узнать больше:
Зная, как использовать VLookup, вам также необходимо знать распространенные ошибки и способы их устранения.
Распространенные ошибки при использовании функции ВПР в Excel
Искомое значение находится в неправильном столбце
Одна из наиболее распространенных ошибок, с которыми вы можете столкнуться при использовании формулы ВПР, заключается в том, что функция возвращает только значение #Н/Д. Эта ошибка возникает, когда не удается найти искомое значение, которое вы запросили в VLOOKUP.
В некоторых случаях вместо ВПР можно использовать функцию ХПР, однако эту ошибку легко исправить только в функции ВПР.
Как устранить проблему:
Причина, по которой VLOOKUP не нашел нужное значение, может заключаться в том, что этого значения нет в таблице. Однако если он возвращает все значения #Н/Д, а искомое значение представляет собой текст, то, скорее всего, это связано с тем, что оно может читаться только слева направо.
Создавая таблицу в Excel для ВПР, обязательно поместите значение поиска слева от значения, которое вы хотите вернуть. Самый простой способ сделать это — поместить его в первый столбец таблицы.
Теперь формула вернет значение:
В целом, VLOOKUP — отличный способ быстрее запрашивать данные в Microsoft Excel. Хотя запросы VLOOKUP выполняются вертикально по столбцам и имеют ряд других ограничений, Microsoft постоянно обновляет функции поиска Excel, чтобы расширить их применимость. Например, HLOOKUP, XLOOKUP... может помочь вам искать данные в разных направлениях. LuckyTemplates.com продолжит предоставлять вам информацию о том, как использовать эту функцию, в следующих статьях.

















