Пошаговая демонстрация применения функции ВПР

У табличного редактора Microsoft Excel в наличии имеется большое количество разных функций, которые упрощают процесс работы в нем. Благодаря им получается экономить время и избегать ошибки – будет достаточно лишь верно записать формулу и вставить необходимые значения.

В данной статье будет рассмотрена функция ВПР (или по-другому VLOOKUP excel), которая буквально переводится, как «вертикальный просмотр». Эта опция разрешает извлекать данные сразу из 2 таблиц и переносить нужные значения. Например, с примерами ВПР будет удобна, чтобы рассчитать доход за месяц либо узнать необходимый бюджет для реализации плана, когда, например, одна таблица хранит в себе прайс-лист, а вторая указывает кол-во проданных товаров. Давайте рассмотрим как работает впр.

На данном сайте вы найдете и другие статьи по работе с программой Excel.

Зачем нужна функция ВПР и когда ее используют

Что такое ВПР?

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

Приведем пример. Допустим, у вас имеется таблица на Листе 1 документа, в которой указаны имеющиеся в продаже мотоциклы.

Еще в этом же документе хранится таблица на Листе 2, в которой записаны все имеющиеся клиенты и их заказы.

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

Делать эту вручную будет неудобно (вдруг таких заказов будет больше 100?). Работа вручную будет занимать кучу времени, и не всегда этот процесс будет эффективным.

Как раз впр функция excel отлично поможет в таком случае. Она легко скопирует нужные данные из первой таблицы и перенесет их во вторую.

Применение

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

Основная идея функции заключается в том, что она сравнивает искомое значение с полученной информацией из указанного диапазона и находит соответствующую строку. Затем в зависимости от указанного номера столбца, функция VPR возвращает значение из этой строки. Если искомое значение не найдено, функция excel впр может вернуть ошибку, которую можно обработать или игнорировать.

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

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

ВНИМАНИЕ! ВПР не будет работать, когда необходимые таблицы будут располагаться в разных документах. Поэтому лучшим решением будет заранее делать таблицы в одном документе, на разных страницах.

Готовимся к работе с функцией впр в excel

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

Как правильно выставить нужные параметры, разберем немного ниже. А сейчас вернемся, к примеру, с продажей мотоциклов. Где же находится эта опция в excel vlookup и как приступить к работе с ней?

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

Например, нам нужно, чтобы стоимость мотоцикла перенеслась в ячейку, где указаны ФИО клиентов. Чтобы это сделать, выбираем ячейку из столбца «Стоимость».

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

Для первого метода нам необходимо в Эксель открыть раздел «Формулы», в котором находим кнопку «Вставить функцию».

Второй метод предлагает, чтобы пользователь нажал на значок «fx», который располагается возле строки формул.

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

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

Заполняем аргументы функции

Теперь поговорим про аргументы, которые нужно будет заполнить: Искомое_значение, Таблица, Номер_столбца и Интервальный_просмотр.

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

Здесь указываем название ячейки, в которой расположены одинаковые данные в обоих таблицах. Именно по ним excel функция впр начнет поиск информации. Если вернутся к нашему примеру, то здесь «искомым значением» станет модель мотоцикла. Именно по этому значению функция будет находить данные, чтобы перенести их.

Чтобы верно указать искомое значение, советуется сделать следующее:

— В открывшемся окне кликаем на раздел «Искомое_значение».

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

Сделав такие шаги, программа автоматически построит формулу excel впр, которую можно будет увидеть в строке формул. В нашем случае получилось

=ВПР(Лист2!B2)»

такое выражение.

СОВЕТ! Конечно, все данные можно вводить самостоятельно, но намного легче будет просто нажимать на нужные ячейки.

Таблица

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

Опять же, вернемся к нашему примеру. Нам требуется переносить стоимость указанных моделей мотоциклов. А значит, что в нашем диапазоне нужно, чтобы был столбец с марками (что стало искомым значением) и стоимостью моделей (что является переносимым значением).

ВНИМАНИЕ! Чтобы функция vpr работала правильно, потребуется, чтобы столбик с искомым значением желательно был первым в указанном диапазоне. Допустим, в нашем примере, искомое значение располагается в ячейке B2, а значит, нужно, чтобы весь диапазон начинался со столбца B.

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

— Кликаем на раздел «Таблица»;

— Теперь нужно перейти на лист, где находится таблица, в которой указан каталог;

— После требуется выделить все, где расположены столбцы «Марка» и «Стоимость». То есть в этом случае – это А2:С8.

— Выделенный диапазон нужно закрепить. Если вы работаете на ОС Windows, то нужно выделить желаемый диапазон и нажать на кнопку F4. Если же ваша операционная система Mac, то нужно сделать все то же самое, но нажать на комбинацию клавиш Cmd+T. Закрепление диапазона необходимо, чтобы потом получалось протягивать функцию и она могла продолжать оптимально работать на других строчках.

Указанный диапазон автоматически будет перенесен в окно аргументов. А также появится excel формула впр в строке формул. У нас получилось

=ВПР(Лист2!B2;Лист1!A2:C8)

такое выражение.

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

Здесь нужно указать, в каком по счету столбце из первой таблицы располагается нужное значение для переноса. Считать нужно по следующему принципу: Номер 1 – это самый первый столбик, Номер 2 – столбик, расположенный правее, Номер 3 – еще правее и т.д.

Если вернутся, к примеру, то нужный нам столбец («Стоимость») расположен под номером 3.

Для указания номера в окне аргументов нужно сделать следующие действия:

— Кликаем на раздел «Номер_столбца» и указываем значение. В нашем случае требуется указать «3». Также получившуюся формулу можно будет увидеть в строке формул,

=ВПР(Лист2!B2;Лист1!A2:C8;3)

это еще не итоговая формула.

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

Какое значение указывать здесь будет зависеть от того, насколько точно вам нужно, чтобы работала функция:

— Например, если вам хочется, чтобы во время поиска впр формула нашла абсолютное совпадение – нужно ввести значение «0».

— Если же нужно, чтобы результат поиска был приблизительный, то достаточно указать значение «1».

Если вернутся к нашему примеру, то полумеры тут не подойдут. Нам нужно точное совпадение и точная цена к определенной марке мотоцикла. А значит, нам нужно написать «0».

Чтобы это сделать, выполняем следующие шаги:

— Кликаем на раздел «Интервальный_просмотр» в открытом окне и вписываем туда значение «0».

Итоговая формула впр в excel для сравнения двух таблиц появится в строке формул:

=ВПР(Лист2!B2;Лист1!A2:C8;3;0)

Что ж, на этом все! Нас можно поздравить, поскольку мы смогли узнать, что означает каждый аргумент в функции ВПР

Получаем результат ВПР

Теперь настал ответственный момент. Нам нужно посмотреть на итоговый результат, который выдаст функция впр в эксель. Кликаем на кнопку «ОК» в окошке «Аргументы функции». Если все было сделано правильно, то в указанной ячейке появится желаемое значение (в нашем примере – это стоимость марки мотоцикла).

Как видим, впр в excel формула отработала на «ура!» и мы получили желаемый результат. Чтобы вместо знаков «?» получить цены на мотоциклы – будет достаточно потянуть за правый нижний уголок ячейки с формулой вниз.

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

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

Что делать, если нужен поиск сразу по двум критериям?

Если вернуться к прошлому примеру с мотоциклами, то здесь функция ВПР будет отлично работать. Но есть одно большое «НО». В прошлый раз в таблице было только по одной модели, благодаря чему функция прекрасно справлялась со своей работой. Но что, если, допустим, клиент хочет приобрести Taco 250 PR, но синюю модель, а не красную?

Для проверки вначале составим новый каталог, где добавим те же модели мотоциклов, но с разными цветами покраски.

И добавим тот же список с покупателями.

Если мы воспользуемся прошлой формулой, то программа нам выдаст не тот ответ… Например, Иванов А.С. хотел модель «Taco 250 PR» синего цвета, но при этом в программе отобразилась стоимость модели красной окраски.

Как видно, на данный момент функция VPR учитывает только один параметр – это название марки мотоцикла. Как же сделать, чтобы она учитывала сразу два параметра?

Чтобы добиться такого результата, нам, нужно будет немного изменить уже имеющиеся таблицы. Рассмотрим шаги подробнее:

— Прежде всего нам нужно будет объединить 2 критерия по поиску в каталоге. Для этого мы создаем еще одну колонку и называем ее, например, «Поиск».

— Теперь в первой ячейке прописываем следующую формулу:

=B2&C2

Такой формулой мы сможем объединить марку и цвет автомобиля в один критерий. Значок «&» буквально обозначает «и» или объединение параметров.

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

— Теперь такие же шаги нужно сделать и во второй таблице с покупателями.

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

— Остался последний шаг! Зажимаем правый нижний угол ячейки и опускаем его вниз, чтобы значения были посчитаны для всего сразу.

Готово! Благодаря таким советам получится выполнять поиск при помощи впр в экселе сразу по двум или нескольким критериям. Однако важно отметить, что все действия нужно выполнять точно и правильно, т.к. даже одна маленькая ошибка в формуле может мешать программе отображать желаемый результат!

Автозаполнение

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

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

ВАЖНО! Чтобы пользователь мог использовать автозаполнение, нужно, чтобы аргумент «Искомое_значение» из функции ВПР был относительным, а аргумент «Таблица» был абсолютным.

Объясним это на примере:

— В нашем случае, когда мы заполняли эти аргументы, то в «Искомое_значение» мы указали значение «А2». То есть это значение будет считаться относительным, поскольку в нем отсутствует знак «$». Если данный знак был, то тогда значение указывало на определенную строку, которая  никак не изменялась во время автозаполнения. Но в нашем случае благодаря тому, что значение было «А2», то во время автозаполнения оно легко могло переходить в «А3», «А4», «А5» и т.д. в остальных ячейках.

— Таблицу же, наоборот, нужно зафиксировать, чтобы во время автозаполнения она не съезжала вниз, а оставалась на одном месте. Такие ссылки называются абсолютными – то есть они не будут изменяться.

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

ВПР и приблизительный интервальный просмотр

В прошлых примерах мы рассмотрели vlookup excel как пользоваться, чтобы точно отыскать нужную нам модель мотоцикла и узнать его стоимость. Но что, если пользователю, например, нужно отсортировать каталог примерно? Допустим, у него имеется таблица, в которой записаны все привезенные в магазин товары (сахар, вода, мука и т.д.) И пользователь хочет, чтобы программа отсортировала, какого товара привезли маленькой партией, а какой товар был привезен в большом количестве.

Получаем примерно такие 2 таблицы: в первой мы указываем наименование товара и его количество, а во второй мы подробно описываем, как определить, является партия мелкой или крупной. Конечно же, нам самим все это заполнять не хочется, потому что этот процесс длительный и трудоемкий. Поэтому мы разберем, как можно это сделать легко при помощи приблизительного интервального просмотра.

Для выполнения задачи делаем следующие действия:

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

1. В «Искомое_значение» указываем ячейку, где хранится желаемый объект.

   

2. В «Таблица» указываем нашу таблицу, которую мы сделали для определения размера партии.

   

3. В «Номер_столбца» указываем столбик, в котором расположено нужное значение для переноса.
4. В «Интервальный_просмотр» ставим цифру «1», поскольку нам нужен лишь приблизительный поиск.

Заполнив все аргументы, нажимаем на клавишу «ОК»

Выполнив все описанные выше действия — пользуемся опцией автозаполнения и протягиваем формулу вниз на все ячейки. В итоге получаем такой результат:

Программа рассчитала все точно и без ошибок.

Функция ВПР и выпадающий список

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

Разберем это на примере. Предположим, что у нас имеется таблица с перечислением имеющихся в наличии продукции и ее стоимости.

Для применения функции ВПР в выпадающем списке нужно сделать описанные ниже шаги:

• Выбираем ячейку, где нужно установить список. Далее открываем раздел «Данные» и находим пункт «Меню проверки данных».

• В открывшемся окне с названием «Проверка вводимых значений» нам нужно указать желаемый тип данных – «Список», а в разделе «Источник» указываем столбик с перечислением имеющейся продукции. И нажимаем на «ОК».

  

• Теперь кликаем на соседнюю ячейку, в которой мы хотим, чтобы отображалась цена. Далее переходим в раздел «Формулы» и нажимаем на «Вставить функцию».

  

• В открывшемся окне «Вставка функции» выбираем ВПР и кликаем на кнопку «ОК».

  

• Теперь нам нужно правильно заполнить аргументы. В «Искомое_значение» выбираем ячейку, в которой мы сделали выпадающий список. В «Таблица» выделяем диапазон ячеек с нашей таблицей. В «Номер_столбца» выбираем номер столбца, в котором расположены цены на продукцию. А в «Интервальный_просмотр» указываем цифру «0», потому что здесь нам нужны точные данные.

  

• Кликаем на «ОК» и проверяем результат:

Как видно, при выборе в выпадающем списке пункта «Соль», столбец с ценой точно отображает данные. Попробуем теперь выбрать «Печенье»:

Все опять корректно указано.

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

Ошибки, из-за которых функция ВПР не будет работать

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

— Ошибка #Н/Д. Эта надпись обозначает, что у функции не получилось найти нужные данные в указанной таблице. Еще такое происходит, когда в аргументе «Интервальный_просмотр» пользователь указал «0», но при этом программе не удается отыскать точные совпадения. Чтобы решить проблему, советуем еще раз тщательно проверить – не были ли допущены ошибки во время ввода диапазона ячеек (нет ли там лишних знаков).

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

— Значение аргумента «Искомое_значение» ниже, чем то, что есть в таблице. Если функции было указано найти точное совпадение, то она не сможет выполнить свою задачу. И в таком случае она просто выдаст ошибку «#Н/Д». Например, ВПР надо было найти в таблице число «50», но при этом в таблице имеется только число «51». В таком случае программа ничего не сможет найти… Чтобы решить проблему, достаточно просто исправить «Искомое_значение».

Вот такие основные ошибки, которые могут возникнуть во время работы с функцией ВПР.