Что такое впр в excel

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

Важно: Попробуйте использовать новую функцию ПРОСМОТРX, улучшенную версию функции ВПР, которая работает в любом направлении и по умолчанию возвращает точные совпадения, что делает ее проще и удобнее в использовании, чем предшественницу. Эта функция доступна только при наличии подписки на Microsoft 365.. Если вы являетесь подписчиком Microsoft 365, убедитесь, что у вас установлена последняя версия Office.

Ссылка для пробного использования или покупки Microsoft 365

Ваш браузер не поддерживает видео. Установите Microsoft Silverlight, Adobe Flash Player или Internet Explorer 9.

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

Использование ВПР

В строке формул введите =ВПР().
В скобках введите значение подстановки, а затем запятую. Это может быть фактическое значение или пустая ячейка, которая будет содержать значение : (H2,
Введите массив таблицы или таблицу подстановки, диапазон данных, которые требуется выполнить поиск, и запятую (H2, B3:F25,
Введите номер индекса столбца. Это столбец, в котором вы думаете ответы, и он должен быть справа от ваших значений поиска: (H2,B3:F25;3,
Введите значение подстановки диапазона : TRUE или FALSE. TRUE находит частичные совпадения, FALSE — точные совпадения. Готовая формула выглядит примерно так: =VLOOKUP(H2;B3:F25;3;FALSE)

Краткий справочник: функция ВПР

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Еще. Меньше

Совет: Попробуйте использовать новую функцию ПРОСМОТРX , улучшенную версию функции ВЗПРОБЕЛО, которая работает в любом направлении и по умолчанию возвращает точные совпадения, что упрощает и удобнее в использовании, чем предшественницу.

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

Если вам нужен только синтаксис функции ВПР, вот он:

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

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

Кнопка

Эскиз краткого справочника по функции ВПР

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Функция ВПР в Excel. Что это такое и как пользоваться

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

Кстати, в образовательном центре “РУНО” есть практический курс Microsoft Excel 2016/2019. Уровень 2. Расширенные возможности, на котором можно узнать всё про вычисления в программе с помощью функций, про условное форматирование, функциях ВПР, ГПР и множестве других полезных инструментов.

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

1. Что такое ВПР

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

В английском интерфейсе для её обозначения используется термин VLOOKUP, означающий то же самое, или VPR, являющийся калькой русской аббревиатуры. Она поддерживает приблизительное и точное сопоставление, а также подстановочные знаки (* и ?). Значения поиска должны отображаться в первом столбце таблицы, а столбцы поиска находятся правее.

2. Зачем нужна функция ВПР в Excel

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

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

3. Разбираем на примере

Рассмотрим пример: допустим, клиент разместил заказ на отгрузку овощей. Он указал название товара в столбце “Товар” и желаемое количество в столбце “Вес”.

После чего, ожидаемо, клиент хочет увидеть итоговую стоимость своего заказа. И наша задача как-ра рассчитать эту итоговую стоимость заказа.

Что для этого нужно? В первую, очередь, нам необходимо заполнить столбец “Цена”.

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

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

Открывается окно “Аргументы функции”, где необходимо заполнить четыре аргумента.

Что же мы будем искать? Допустим, необходимо найти стоимость товара “Мытая морковка”.

Для этого ставим курсор в графу “Искомое значение” и нажимаем на ячейку с названием товара.

Далее, где мы будем искать этот товар? Логично, что нужно обратиться к прайс-листу. Для корректного поиска мы должны обозначить весь диапазон документа для поиска — весь прайс-лист. Итак, выделяем диапазон и видим, что в графе “Таблица” появилось искомое значение. Далее нажимаем клавишу F4, чтобы в аргументе прописался весь диапазон прайс-листа.

ВАЖНО!
Три первых аргумента являются обязательными. Главное требование к организации данных — искомое значение должно находится в первом столбце таблицы для поиска.

Освоив курс Microsoft Excel 2016/2019. Уровень 2. Расширенные возможности, Вы научитесь профессионально работать в программе Excel, будете уверены в точности своих вычислений и сможете не тратить время на операции вручную. Пройдите пробный курс на нашем сайте.

Далее для завершения операции мы должны заполнить номер столбца. Как мы уже говорили, ищем наименование товара мы по первому столбцу, а вот подставить в нашу таблицу нам нужно те значения, которые указаны во втором столбце — “Цена”. Поэтому в этом аргументе пишем цифру два.

Аргумент “Интервальный просмотр” заполняем как “0” и нажимаем ОК. Теперь мы видим цену нашего товара в первой ячейке. Чтобы применить действие, для всех последующих товаров, скопируем формулу вниз, потянув ячейку за правый нижний угол.

Таким образом, мы заполнили всю таблицу.

Полезно знать!

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

Пройдя курсы Excel дистанционно, вы сможете в короткие сроки освоить работу с продуктом Microsoft и успешно применять полученные навыки на практике.

Применять продвинутые инструменты вычисления;
Эффективно работать с большими табличными массивами;
Анализировать данные с помощью сводных таблиц;
Применять новые диаграммы Excel 2019;
Применять альтернативные методики форматирования;
Защищать данные книги.

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

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

Более сложные ситуации на конкретных примерах мы рассматриваем на дистанционном практическом курсе Microsoft Excel 2016/2019. Уровень 2. Расширенные возможности. Записавшись на наш курс вы овладеете всеми возможными навыками, облегчающими и ускоряющими работу с данными.

СМОТРИТЕ ВИДЕОУРОКИ ПО ТЕМЕ:

каталог курсов по excel:

Как сделать ВПР в Excel: пошаговая инструкция со скриншотами

Как перенести данные из одной таблицы в другую, если строки идут не по порядку? Разбираемся на примере каталога авто — переносим цены.

Иллюстрация: Meery Mary для Skillbox Media

Ксеня Шестак

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

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

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

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

Представьте, что вы продаёте автомобили. У вас есть каталог с характеристиками авто и их стоимостью. Также у вас есть таблица с данными клиентов, которые забронировали эти автомобили.

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

Просто скопировать и вставить эту колонку не получится. Искать каждое авто вручную и переносить цены — долго.

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

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

Важно!

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

Шаг 1

Готовимся к работе с функцией ВПР в Excel

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

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

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

В нашем случае нужно перенести цены на авто из каталога в список клиентов. Для этого добавим пустой столбец «Цена, руб.» в таблицу с клиентами и выберем ячейку напротив первого клиента.

Дальше открываем окно для построения функции ВПР. Есть два способа сделать это. Первый — перейти во вкладку «Формулы» и нажать на «Вставить функцию».

Второй способ — нажать на «fx» в строке ссылок на любой вкладке таблицы.

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

Появляется окно для ввода аргументов функции. Как их заполнять — разбираемся ниже.

Шаг 2

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

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

Искомое значение — название ячейки с одинаковыми данными для обеих таблиц, по которым функция будет искать данные для переноса. В нашем примере это модель авто. Функция найдёт модель в таблице с каталогом авто, возьмёт оттуда стоимость и перенесёт в таблицу с клиентами.

Порядок действий, чтобы указать значение, выглядит так:

Ставим курсор в окно «Искомое значение» в построителе формул.
Выбираем первое значение столбца «Марка, модель» в таблице с клиентами. Это ячейка A2.

Выбранное значение переносится в построитель формул и одновременно появляется в формуле строки ссылок: fx=ВПР(A2).

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

В нашем случае нужно перенести цены автомобилей. Поэтому в диапазон обязательно нужно включить столбцы «Марка, модель» (искомое значение) и «Цена, руб.» (переносимое значение).

Важно!

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

Порядок действий для указания диапазона:

Ставим курсор в окно «Таблица» в построителе формул.
Переходим в таблицу «Каталог авто».
Выбираем диапазон, в который попадают столбцы «Марка, модель» и «Цена, руб.». Это A2:E19.
Закрепляем выбранный диапазон. На Windows для этого выбираем значение диапазона в строке ссылок и нажимаем клавишу F4, на macOS — выбираем значение диапазона в строке ссылок и нажимаем клавиши Cmd + T. Закрепить диапазон нужно, чтобы можно было протянуть функцию вниз и она сработала корректно во всех остальных строках.

Выбранный диапазон переносится в построитель формул и одновременно появляется в формуле строки ссылок: fx=ВПР(A2;’каталог авто’!$A$2:$E$19).

Номер столбца — порядковый номер столбца в первой таблице, в котором находится переносимое значение. Считается по принципу: номер 1 — самый левый столбец, 2 — столбец правее и так далее.

В нашем случае значение для переноса — цена — находится в пятом столбце слева.

Чтобы задать номер, установите курсор в окно «Номер столбца» в построителе формул и введите значение. В нашем примере это 5. Это значение появится в формуле в строке ссылок: fx=ВПР(A2;’каталог авто’!$A$2:$E$19;5).

Интервальный просмотр — условное значение, которое настроит, насколько точно сработает функция:

Если нужно точное совпадение при поиске ВПР, вводим 0.
Если нужно приближённое соответствие при поиске ВПР, вводим 1.

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

Ставим курсор в окно «Интервальный просмотр» в построителе формул и вводим значение: 0. Одновременно это значение появляется в формуле строки ссылок: fx=ВПР(A2;’каталог авто’!$A$2:$E$19;5;0). Это окончательный вид функции.

Шаг 3

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

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

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

Получилась таблица с ценами — можно звонить клиентам и сообщать о стоимости авто. Данные перенесены без ошибок, а потратили мы на это несколько минут.

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

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

Например, у нас несколько одинаковых моделей с разным цветом.

И по традиции есть таблица с клиентами, которые эти модели забронировали.

Если идти по классическому пути ВПР, получится такая функция: fx=ВПР(A29;’каталог авто’!$A$29:$E$35;5;0). В таком виде ВПР найдёт первую совпавшую модель и подтянет её стоимость. Параметр цвета не будет учтён.

Соответственно, цены у всех Nissan Juke будут 1 850 000 рублей, у всех Subaru Forester — 3 190 000 рублей, у всех Toyota C-HR — 2 365 000 рублей.

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

Дописываем в формулу фразу ЕСЛИ(‘каталог авто’!$B$29:$B$35=B29, где:

‘каталог авто’!$B$29:$B$35 — закреплённый диапазон цвета автомобилей в таблице, откуда нужно перенести данные. Это весь столбец с ценами.
B29 — искомое значение цвета автомобиля в таблице, куда мы переносим данные. Это первая ячейка в столбце с цветом — дополнительным параметром для поиска.

Итоговая функция такая: fx=ВПР(A29;ЕСЛИ(‘каталог авто’!$B$29:$B$35=B29;’каталог авто’!$A$29:$E$35);5;0). Теперь значения цен переносятся верно.

Как использовать ВПР в «Google Таблицах»? В них тоже есть функция Vlookup, но нет окна построителя формул. Поэтому придётся прописывать её вручную. Перечислите через точку с запятой все аргументы и не забудьте зафиксировать диапазон. Для фиксации поставьте перед каждым символом значок доллара. В готовой формуле это будет выглядеть так: =ВПР(A2;’Лист1′!$A$2:$C$5;3;0).

Другие материалы Skillbox Media для менеджеров

Статья: что такое матрица БКГ и как она помогает определить, какие проекты стоит развивать
Опрос руководителей о методах тайм-менеджмента: как сделать так, чтобы сотрудники всё успевали
Подборка из десяти неочевидных ошибок руководителя команды на удалёнке
Разбор инструмента: ищем первопричины проблем с помощью рыбьих костей Исикавы
Статья про управление персоналом: что это такое и зачем оно нужно