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

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

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

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

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

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

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

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

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

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

Важно! Параметр 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» вписано в ячейку C4 первой таблицы.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Уникальная метка публикации: E196CFD0-BACD-89F6-F4CC-6B7DD0AF035B
Источник: //artemvm.info/information/uchebnye-stati/microsoft-office/funkcziya-vpr-v-excel/

Смотреть видео
Функция ВПР в Excel

Прикреплённые документы

Вы можете просмотреть любой прикреплённый документ в виде PDF файла. Все документы открываются во всплывающем окне, поэтому для закрытия документа пожалуйста не используйте кнопку «Назад» браузера.

Файлы для загрузки

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

Скорее всего, ваши таблицы будут в разных файлах. Для удобства, скопируйте их на разные листы одной книги, как в примере выше. Нам нужно чтобы в таблице 2016 года в третьем столбце появились данные из 2015 года. Выбираем ячейку С2 и пишем «=ВПР(» без кавычек, Excel сразу выведет подсказку:

Функция ВПР в Excel примеры использования советы (2019)

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

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

ВПР Excel — что это такое? Узнайте, как работает функция ВПР в Excel

Общая информация

Функция ВПР Excel – что это такое? Её также называют VLOOKUP в англоязычной версии. Это одна из самых распространённый функций массивов и ссылок. Специалисты, составляющие шкалу BRP ADVICE, выставили уровень сложности, приравниваемый к 3 или 7.

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

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

Иногда ссылка подаётся в другом виде (R1C1). Одним словом, отмечается столбец и строчка, на пересечении которых и находится нужная для пользователя информация. Программа получает точное указание, где ей надо искать эти данные.

Параметры функции на примере

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

1. А1. Это приблизительная ссылка на ячейку. В ней может указываться любое значение, в зависимости от результата, который пользователь хочет получить.
2. База_данных. Имя той области информации, которую предстоит искать. Понятие не настолько обширное, как предыдущее. Его можно использовать только по первым строкам или столбцам.
3. 2. Это порядковый номер столбца, откуда программа будет черпать информацию.
4. ЛОЖЬ. Указывает на поиск точного совпадения. Иногда указываются другие дополнительные параметры этого слова. И программа при этом будет искать все совпадения и определять ближайшие значения.

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

Аргументы ВПР

Другой аргумент – таблица. Она может указываться в координатной системе. И непосредственно в этой таблице, первом её столбце функция попытается найти искомый элемент. Он указывается изначально (см. выше).

Третий аргумент – номер столбца. Здесь указывается информация, которую ищет пользователь. К примеру, он может посмотреть должность, соответствующую фамилии из первого столбца, его заработную плату, отметки и так далее. Всё зависит от сферы деятельности пользователя.

И последний аргумент – интервальный просмотр. Здесь указывается «1» и «0» либо «Ложь» и «Правда». В первом случае данные будут означать, что заданный поиск является приблизительным. Тогда программа начнёт искать все совпадения. Если применяется второй вариант, тогда функция будет обращать внимание только на точные значения.

Распространённые ошибки

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

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

И, в-третьих, нередко неправильно указывается номер столбца, откуда нужна информация. В этом случае нужно перепроверить данные.

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

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

Помните о главном: функция ищет информацию по вертикали, то есть – по столбцам. Её применяют в разных ситуациях:

• Когда надо найти информацию в большой таблице либо отыскать все повторяющиеся данные и совпадения.
• В преподавательской среде. Учитель всегда может по фамилии своих учеников в считаные мгновения найти их посещаемость, успеваемость и другую информацию. В особенности полезной она является, когда список студентов большой, а преподаватель не может запомнить каждого из них.
• В розничной торговле. Если использовать функцию для поиска цены, состава или артикула товара, то человек сможет быстро ответить на расспросы покупателей.

Одним словом, в любой среде, когда нужно найти данные из таблицы, можно использовать ВПР.

Как использовать ВПР в таблице?

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

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

ВПР на английском Excel и на русском аналоге применяется одинаково. Но есть пара советов от профессионалов. Чтобы функция работала лучше, особенно после изменения данных, рекомендуется вводить значок доллара между массивами. Например, не A1, а А$1$. Когда вбиваются первичные значения, то никаких символов и пробелов между названиями строк и столбцов не нужно ставить.

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

Заключение

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

Здесь обратите внимание на следующее, если работаете с числами и указываете «Истина» , то вторая таблица (это наш прайс) обязательно должна быть отсортирована по возрастанию. Например, при поиске 5,25 найдется 5,27 и возьмутся данные с этой строки, хотя ниже может еще быть и число 5,2599 – но формула дальше смотреть не будет, поскольку она думает, что ниже числа только больше.

Функция ВПР. Использование функции ВПР. Excel — ВПР

1. А1. Это приблизительная ссылка на ячейку. В ней может указываться любое значение, в зависимости от результата, который пользователь хочет получить.
2. База_данных. Имя той области информации, которую предстоит искать. Понятие не настолько обширное, как предыдущее. Его можно использовать только по первым строкам или столбцам.
3. 2. Это порядковый номер столбца, откуда программа будет черпать информацию.
4. ЛОЖЬ. Указывает на поиск точного совпадения. Иногда указываются другие дополнительные параметры этого слова. И программа при этом будет искать все совпадения и определять ближайшие значения.

Примечание. Если ввести вручную крайние фигурные скобки в строку формул то это не приведет ни ка какому результату. Выполнить функцию циклическим массивом можно только через комбинацию горячих клавиш: CTRL+SHIFT+ENTER.

Как пользоваться VLOOKUP Excel? Функция ВПР в Excel для «чайников» и не только

Сущность и назначение функции ВПР в Excel

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

Аргументы функции

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

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

Аргумент «Искомое значение»

В первое поле нужно ввести то, что нужно искать. Здесь может быть число, текст, дата. Можно вводить абсолютным значением, можно проставить ссылку на ячейку с нужным значением. Искомое значение теоретически должно присутствовать в обеих таблицах. В примере, приведенном выше, таким значением может стать код товара. Во избежание ошибок лучше воспользоваться ссылкой.

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

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

Перевести в числовой формат просто — нужно умножить массив на единицу.

Аргумент «Таблица»

Диапазоном здесь служит правая таблица. Искомое значение в столбце справа, там Excel будет искать значение 3187849428, значение, которое необходимо найти и подставить как результат формулы — слева. Для получения точного результата лучше зафиксировать диапазон, выделив его и нажав клавишу F4, ссылка на массив станет абсолютной.

Аргумент «Номер столбца»

Здесь необходимо цифрой проставить, в каком по счету столбце, от самого левого, необходимо взять значение для подстановки как результат исчисления. В вышеуказанном примере — это второй столбец, в строку аргументов необходимо проставить цифру 2. Если бы между столбцом «Код» и «Цена» был бы еще один столбец, то нужно было бы проставить цифру 3 и так далее.

Аргумент «Интервальный просмотр»

При введении в поле аргумента значения 0 функция вернет только значение, соответствующее равному искомому. При наличии в диапазоне поиска повторений — функция возьмет первое совпадение. При использовании формулы с аргументом 0 функция работает намного дольше, однако намного точнее.

Особенности использования ВПР по нескольким условиям

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

Выпадающий список для облегчения работы с ВПР

Пример использования функции VLOOKUP в Excel

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

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

• «Н/Д» — самый распространенный тип ошибки. Может возникнуть по нескольким причинам.

• «ССЫЛКА» — данная ошибка часто возникает при неправильном указании номера столбца, если столько столбцов нет в выбранном диапазоне. В таких случаях необходимо помнить, что номер проставляют, считая с левого столбца выделенного диапазона, а не таблицы в целом.
• «ИМЯ» — ошибка возвращается часто при неправильном занесении текста в «Искомое значение». Текст необходимо прописывать в кавычках.

В результате поиска формулы вы увидите её нахождение в перечне. Нажав на элемент, внизу экрана отобразится его формула. За скобками указывается название функции, а внутри скобок – её параметры. Внутри формулы каждый отдельный параметр прописывается в угловых скобах. Общий вид описания для ВПР выглядит так:

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

• «Н/Д» — самый распространенный тип ошибки. Может возникнуть по нескольким причинам.

• «ССЫЛКА» — данная ошибка часто возникает при неправильном указании номера столбца, если столько столбцов нет в выбранном диапазоне. В таких случаях необходимо помнить, что номер проставляют, считая с левого столбца выделенного диапазона, а не таблицы в целом.
• «ИМЯ» — ошибка возвращается часто при неправильном занесении текста в «Искомое значение». Текст необходимо прописывать в кавычках.

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