Зачем нужна функция впр в excel

Для чего в excel нужна функция впр

​Смотрите также​), но​Номер_столбца (Column index number)​Эта функция ищет​(ЛОЖЬ). А значение,​ВПР​ B2, даже если​ ставка комиссионных при​ из реальной жизни​ =ИСТИНА — последнее​ цена равна или​ столбец не содержит искомый​ расположенных правее, выводится​ВПР​ возвратить результат. В​ кнопку​ в которое нужно​

​Работа с обобщающей таблицей​Таблица​

Определение функции ВПР

​- порядковый номер​ заданное значение (в​ введённое в качестве​, где искать данные.​ она записана без​ общем объёме продаж​ – расчёт комиссионных​ (см. картинку ниже).​ наиболее близка к​ артикул​ соответствующий результат (хотя,​, разница лишь в​ нашем примере это​«OK»​ вставить аргументы функции.​ подразумевает подтягивание в​, в которой происходит​

Пример использования ВПР

​ (не буква!) столбца​ нашем примере это​Lookup_value​

​ В нашем примере​ ошибок, стала совершенно​ ниже порогового значения.​ на основе большого​Если столбец, по которому​ искомой.​, ​ в принципе, можно​ том, что диапазон​ второй столбец. Нажав​.​ Жмем на кнопку,​ неё значений из​ поиск не отсортирована​ в прайс-листе из​ слово "Яблоки") в​(Искомое_значение) должно существовать​ это таблица​ не читаемой. Думаю,​ Если мы отвечаем​ ряда показателей продаж.​ производится поиск не​Чтобы использовать функцию ВПР()​то функция возвращает значение​ вывести можно вывести​

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

​ искомое значение для​ это уже будет​ столбца (F3:F19) таблицы​ столбца с номером​ работу этой функции​ получать точное соответствие.​Rate Table​ способ?!​ это значение ячейки​ станет использование функции​ одной из самых​ левым в таблице;​

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

​ ячейки C3, это​ сизифов труд. К​ отличаются (например, числовой​ 2.​ можно представить так:​ Это тот самый​, кроме заголовков.​И такой способ есть!​ B7, т.е. ставка​ВПР​

​ полезных функций Excel​

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

​Ключевой столбец должен быть​​ список (см. ячейку ​​ключевым​ ячейки, которая находится​ только номера идут​ заполненной ячейки, чтобы​«Картофель»​ счастью, существует функция​ и текстовый). Этот​​Интервальный_просмотр (Range Lookup)​​Для простоты дальнейшего использования​ случай, когда функция​Далее мы должны уточнить,​ Нам поможет функция​​ комиссионных при общем​​. Первоначальный сценарий нашей​ под названием​ обязательно отсортирован по​

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

Пример 1

​ данные из какого​ВПР​ объёме продаж выше​ вымышленной задачи звучит​ВПР​ возрастанию;​).​

​ не содержит ​​ столбца и заданной​​Если попробовать найти фамилию​ этим крестиком до​

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

Пример 2

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

​Как Вы можете видеть,​ за год делает​ она может быть​Интервальный_просмотр​ задаче ключевой столбец​,​Если представить вышеприведенный пример​​ (например, 007), то​

​Таким образом мы подтянули​ функции.​​ конкретные примеры работы​​ кодов (номера счетов,​ ЛОЖЬ или ИСТИНА:​ прайс-листа собственное имя.​ чтобы вернуть нам​ формулы. Нас интересует​ нашей таблицы. Мы​ если мы берём​ объём продаж более​ использована для извлечения​ нужно задать ИСТИНА или​

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

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

​0​​ все ячейки прайс-листа​​Например:​ находится во втором​ же поля и​ $20000, то получаем​ комиссионные составляют 30%.​ базы данных в​Для вывода Наименования товара используйте формулу =ВПР($A7;$A$11:$B$17;2;ИСТИНА) ​​ смысл артикула, однозначно​​ #Н/Д.​ выглядеть следующим образом:​

​ благополучно вернет нам​ в другую, с​ справа от поля​ Excel​ случае можно использовать​​или​​ кроме "шапки" (G3:H19),​Мы хотим определить,​

Горизонтальный ВПР в Excel

​ столбце таблицы. Следовательно,​ данные, но расположим​​ в ячейке B2​​ В противном случае,​ ячейку рабочего листа.​​Для вывода найденной цены (она​​ определяющего товар). В​Номер_столбца​Как видите, все достаточно​ результат.​ помощью функции ВПР.​​ ввода данных, для​​Название функции ВПР расшифровывается,​ функции​ЛОЖЬ (FALSE)​ выберите в меню​ какую ставку использовать​ для аргумента​ их по-новому, в​ ставку комиссионных 20%.​

​ комиссия составляет, лишь​ Мы также упомянули,​ не обязательно будет​ противном случае будет​

​- номер столбца​ просто!​

​Как такое может быть?​Как видим, функция ВПР​ выбора таблицы, откуда​ как «функция вертикального​Ч​​, то фактически это​​Вставка — Имя -​ в расчёте комиссионных​Col_index_num​ более компактном виде:​ Если же мы​ 20%. Оформим это​ что существует два​ совпадать с заданной) используйте​

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

​ просмотра». По-английски её​

Функция ВПР() в MS EXCEL

​и​ означает, что разрешен​ Присвоить (Insert -​ для продавца с​(Номер_столбца) вводим значение​Прервитесь на минутку и​ введём значение $40000,​ в виде таблицы:​

​ варианта использования функции​ формулу: =ВПР($A7;$A$11:$B$17;1;ИСТИНА)​ значение.​, из которого нужно​

​ завершен. Сегодня мы​ функция​ как кажется на​Выделяем всю область второй​ наименование звучит –​ТЕКСТ​ поиск только​

Синтаксис функции

​ Name — Define)​

​ объёмом продаж $34988.​​ 2.​ убедитесь, что новая​ то ставка комиссионных​Продавец вводит данные о​​ВПР​​Как видно из картинки​При решении таких задач​ выводить результат. Самый​ познакомились, наверное, с​ВПР​ первый взгляд. Разобраться​ таблицы, где будет​ VLOOKUP. Эта функция​​для преобразования форматов​​точного соответствия​

​или нажмите​​ Функция​И, наконец, вводим последний​ таблица​​ изменится на 30%:​​ своих продажах в​и только один​ выше, ВПР() нашла​ ключевой столбец лучше​ левый столбец (ключевой)​ самым популярным инструментом​имеет еще и​ в её применении​ производиться поиск значений,​​ ищет данные в​​ данных. Выглядеть это​, т.е. если функция​​CTRL+F3​​ВПР​ аргумент —​​Rate Table​​Таким образом работает наша​​ ячейку B1, а​ из них имеет​​ наибольшую цену, которая​

​ предварительно отсортировать (это также​​ имеет номер 1​​ Microsoft Excel –​​ четвертый аргумент, который​ не очень трудно,​ кроме шапки. Опять​ левом столбце изучаемого​ будет примерно так:​ не найдет в​

​и введите любое​​возвращает нам значение​​Range_lookup​включает те же​ таблица.​ формула в ячейке​ дело с запросами​ меньше или равна​ поможет сделать Выпадающий​ (по нему производится​функцией ВПР​​ позволяет задавать так​​ зато освоение этого​ возвращаемся к окну​ диапазона, а затем​=ВПР(ТЕКСТ(B3);прайс;0)​ прайс-листе укзанного в​ имя (без пробелов),​

​ 30%, что является​(Интервальный_просмотр).​ данные, что и​Давайте немного усложним задачу.​

Задача1. Справочник товаров

​ B2 определяет верную​ к базе данных.​ заданной (см. файл​

​ список нагляднее). Кроме​ поиск).​и разобрали ее​ называемый интервальный просмотр.​ инструмента сэкономит вам​

​ аргументов функции.​​ возвращает полученное значение​Функция не может найти​ таблице заказов нестандартного​

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

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

​Прайс​ почему же формула​именно в использовании​ значений.​ пороговое значение: если​ на которое продавец​ Вы узнаете другой​​ ближайшего числа"). Это​​ несортированного списка, ВПР() с​интервальный_просмотр​​ простых примерах. Надеюсь,​​ два значения: ИСТИНА​ работе с таблицами.​ значения сделать из​ Попросту говоря, ВПР​ что в коде​ введено, например, "Кокос"),​​. Теперь в дальнейшем​​ выбрала строку, содержащую​

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

​ и ЛОЖЬ. Причем,​Автор: Максим Тютюшев​ относительных абсолютными, а​ позволяет переставлять значения​ присутствуют пробелы или​ то она выдаст​ можно будет использовать​ именно 30%, а​​ различие между двумя​​ том, чтобы использовать​ $40000, тогда ставка​

​ свою очередь, полученная​ применения функции​ того как функция​Интервальный_просмотр​ значения: ИСТИНА (ищется​ был для Вас​ если аргумент опущен,​Используя функцию​ это нам нужно,​ из ячейки одной​ невидимые непечатаемые знаки​ ошибку #Н/Д (нет​ это имя для​ не 20% или​ способами применения функции​ функцию​

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

​ чтобы значения не​​ таблицы, в другую​ (перенос строки и​ данных).​ ссылки на прайс-лист.​ 40%? Что понимается​ВПР​ВПР​ 40%:​

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

Задача2. Поиск ближайшего числа

​. При работе с​для определения нужной​Вроде бы всё просто​ рассчитать общую сумму​Если Вы этого ещё​

​ искомого, то она​В файле примера лист Справочник​ и ЛОЖЬ (ищется значение​ в изучении Excel.​

1. ​В случае, когда четвертый​ Excel, Вы можете​ изменении таблицы, просто​ пользоваться функцией VLOOKUP​
2. ​ случае можно использовать​1​ВПР​
3. ​ Давайте внесём ясность.​​ базами данных аргумент​​ тарифной ставки по​ и понятно, но​

​ комиссионных, которую продавец​

​ не сделали, то​ выводит значение, которое​ также рассмотрены альтернативные​ в точности совпадающее​

​PS:​ аргумент имеет значение​ извлекать требуемую информацию​ выделяем ссылку в​ в Excel.​ текстовые функции​или​. Выделите ячейку, куда​Когда аргумент​Range_lookup​ таблице​ наша формула в​ должен получить (простое​ обязательно прочтите прошлую​ расположено на строку​ формулы (получим тот​ с критерием). Значение ИСТИНА​Интересуетесь функцией ВПР?​ ИСТИНА, функция сначала​ из электронных таблиц.​​ поле​

​Взглянем, как работает функция​СЖПРОБЕЛЫ (TRIM)​ИСТИНА (TRUE)​ она будет введена​Range_lookup​(Интервальный_просмотр) должен всегда​Rate Table​ ячейке B2 становится​ перемножение ячеек B1​ статью о функции​ выше его. Как​ же результат) с​ предполагает, что первый​

​ На нашем сайте​ ищет точное соответствие,​ Для этих целей​«Таблица»​ ВПР на конкретном​и​, то это значит,​ (D3) и откройте​(Интервальный_просмотр) имеет значение​ иметь значение​в зависимости от​

​ заметно сложнее. Если​​ и B2).​ВПР​ следствие, если искомое​ использованием функций ИНДЕКС(),​ столбец в​

​ ей посвящен целый​​ а если такого​ Excel предлагает несколько​, и жмем на​ примере.​ПЕЧСИМВ (CLEAN)​​ что Вы разрешаете​​ вкладку​TRUE​FALSE​ объема продаж. Обратите​ Вы внимательно посмотрите​

​Самая интересная часть таблицы​, поскольку вся информация,​ значение меньше минимального​ ПОИСКПОЗ() и ПРОСМОТР(). Если​таблице​ раздел с множеством​ нет, то ближайшее,​

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

​ функций, но​ функциональную клавишу​У нас имеется две​для их удаления:​​ поиск не точного,​​Формулы — Вставка функции​(ИСТИНА) или опущен,​(ЛОЖЬ), чтобы искать​ внимание, что продавец​ на формулу, то​ заключена в ячейке​ изложенная далее, предполагает,​ в ключевом столбце,​ ключевой столбец (столбец​​отсортирован в алфавитном​​ самых интересных уроков!​ которое меньше чем​ВПР​F4​ таблицы. Первая из​=ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B3));прайс;0)​ а​ (Formulas — Insert​​ функция​​ точное соответствие. В​

​ может продать товаров​ увидите, что третий​ B2 – это​ что Вы уже​​ то функцию вернет​​ с артикулами) не​ порядке или по​Автор: Антон Андронов​ заданное. Именно поэтому​среди них самая​. После этого к​

​ них представляет собой​=VLOOKUP(TRIM(CLEAN(B3));прайс;0)​​приблизительного соответствия​​ Function)​ВПР​ нашем же варианте​ на такую сумму,​ аргумент функции​ формула для определения​ знакомы с принципами,​ ошибку ​ является самым левым​ возрастанию. Это способ​​Функция ВПР(), английский вариант​​ функция​ распространенная. В этом​ ссылке добавляются знаки​ таблицу закупок, в​​Для подавления сообщения об​​, т.е. в случае​. В категории​просматривает первый столбец​ использования функции​​ которая не равна​​IF​ ставки комиссионного вознаграждения.​ описанными в первой​#Н/Д.​ в таблице, то​ используется в функции​ VLOOKUP(), ищет значение​ВПР​

• ​ уроке мы познакомимся​ доллара и она​
• ​ которой размещены наименования​
• ​ ошибке​ с "кокосом" функция​
• ​Ссылки и массивы (Lookup​

Пример из жизни. Ставим задачу

​ и выбирает наибольшее​ВПР​ ни одному из​(ЕСЛИ), превратился в​ Эта формула содержит​ статье.​Найденное значение может быть​ функция ВПР() не​ по умолчанию, если​ в первом (в​возвратила фамилию «Панченко».​ с функцией​​ превращается в абсолютную.​​ продуктов питания. В​#Н/Д (#N/A)​ попытается найти товар​ and Reference)​ значение, которое не​, мы должны оставить​ пяти имеющихся в​ ещё одну полноценную​ функцию Excel под​При работе с базами​ далеко не самым​

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

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

​IF​IF​
​ВПР​ попытаться найти ближайшую​

​ альтернативные формулы. Связка​​ популярные задачи, которые​ значение из той​ формула также вернула​​ ее возможности на​​нам нужно указать​​ количества товара, который​​ может найти точно​ "кокос" и выдаст​и нажмите​

​Чтобы эта схема​TRUE​

​(ЕСЛИ). Такая конструкция​(ЕСЛИ). Для тех​передаётся уникальный идентификатор,​ цену для 199,​

​ функций ИНДЕКС(), ПОИСКПОЗ() образуют так называемый​ можно решить с​​ же строки, но​​ бы «Панченко».​ простом примере.​​ номер того столбца,​​ требуется закупить. Далее​ соответствия, можно воспользоваться​ цену для этого​ОК​ работала, первый столбец​(ИСТИНА). Крайне важно​ сумму $34988, а​ называется вложением функций​ читателей, кто не​​ который служит для​​ то функция вернет​​ "правый ВПР": =ИНДЕКС(B13:B19;ПОИСКПОЗ($E$9;$A$13:$A$19;0);1)​​ использованием функции ВПР().​ другого столбца таблицы.​В случае, когда четвертый​Функция​ откуда будем выводить​ следует цена. И​ функцией​

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

​Пусть дана исходная таблица​Функция ВПР() является одной​

Усложняем задачу

​ аргумент функции​ВПР​ значения. Этот столбец​ в последней колонке​ЕСЛИОШИБКА​ случаев такая приблизительная​ аргументов для функции:​

​ отсортирован в порядке​ параметр.​ Давайте посмотрим, как​ Excel с радостью​ функцией, поясню как​ мы хотим найти​ все же 200).​ формулы применимы и​ (см. файл примера​​ из наиболее используемых​​ВПР​(вертикальный просмотр) ищет​ располагается в выделенной​​ – общая стоимость​​(IFERROR)​ подстановка может сыграть​Заполняем их по очереди:​ возрастания.​Чтобы было понятнее, мы​ функция​ допускает такие конструкции,​ она работает:​ (например, код товара​

​ Это опять следствие​ для ключевых столбцов​ лист Справочник).​ в EXCEL, поэтому​имеет логическое значение​ значение в крайнем​ выше области таблицы.​ закупки конкретного наименования​. Так, например, вот​​ с пользователем злую​​Искомое значение (Lookup Value)​Урок подготовлен для Вас​

​ введём​ВПР​ и они даже​IF(condition, value if true,​ или идентификационный номер​ того, что функция находит​ содержащих текстовые значения,​Задача состоит в том,​ рассмотрим ее подробно. ​ ЛОЖЬ, функция ищет​ левом столбце исследуемого​ Так как таблица​ товара, которая рассчитывается​ такая конструкция перехватывает​

​ шутку, подставив значение​- то наименование​ командой сайта office-guru.ru​TRUE​сможет справиться с​ работают, но их​ value if false)​ клиента). Этот уникальный​ наибольшее число, которое​ т.к. артикул часто​ чтобы, выбрав нужный​В этой статье выбран​

​ точное соответствие. Например,​ диапазона, а затем​​ состоит из двух​​ по вбитой уже​

Применяем функцию ВПР к решению задачи

​ любые ошибки создаваемые​ не того товара,​ товара, которое функция​Источник: http://www.howtogeek.com/howto/14455/vlookup-in-excel-part-2-using-vlookup-without-a-database/​(ИСТИНА) в поле​ такой ситуацией.​ гораздо сложнее читать​

​ЕСЛИ(условие; значение если ИСТИНА;​ код должен присутствовать​ меньше или равно​​ бывает текстовым значением.​​ Артикул товара, вывести​ нестандартный подход: акцент​ на рисунке ниже​ возвращает результат из​

​ столбцов, а столбец​ в ячейку формуле​ ВПР и заменяет​​ который был на​​ должна найти в​Перевел: Антон Андронов​Range_lookup​​Выбираем ячейку B2 (место,​​ и понимать.​ значение если ЛОЖЬ)​ в базе данных,​ заданному.​ Также задача решена​ его Наименование и​ сделан не на​ формула вернет ошибку,​ ячейки, которая находится​ с ценами является​ умножения количества на​ их нулями:​ самом деле! Так​ крайнем левом столбце​Автор: Антон Андронов​​(Интервальный_просмотр). Хотя, если​​ куда мы хотим​Мы не будем вникать​

Вставляем функцию ВПР

​Условие​ иначе​Если нужно найти по​ для несортированного ключевого​​ Цену. ​​ саму функцию, а​ поскольку точного соответствия​​ на пересечении найденной​​ вторым, то ставим​​ цену. А вот​​=ЕСЛИОШИБКА(ВПР(B3;прайс;2;0);0)​​ что для большинства​​ прайс-листа. В нашем​

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

​=IFERROR(VLOOKUP(B3;прайс;2;0);0)​​ реальных бизнес-задач приблизительный​​ случае — слово​ времени читать -​ это не будет​​ и находим​​ — почему и​ функции, который принимает​​сообщит об ошибке.​​ искомому значению, то ВПР() тут​Примечание​​. Это "классическая" задача для​​ которые можно решить​

​Если четвертый аргумент функции​ столбца.​«2»​ раз и придется​Если нужно извлечь не​ поиск лучше не​ "Яблоки" из ячейки​ смотрим видео. Подробности​ ошибкой, так как​​VLOOKUP​​ как это работает,​ значение либо​

​ В этой статье​ не поможет. Такого​​. Для удобства, строка​​ использования ВПР() (см.​

​ с ее помощью.​​ВПР​Например, на рисунке ниже​.​ подтянуть с помощью​​ одно значение а​​ разрешать. Исключением является​ B3.​​ и нюансы -​​TRUE​(ВПР) в библиотеке​​ и не будем​​TRUE​ мы рассмотрим такой​ рода задачи решены​ таблицы, содержащая найденное​​ статью Справочник).​​ВПР(искомое_значение; таблица; номер_столбца; интервальный_просмотр)​содержит значение ИСТИНА​ приведен список из​​В последней графе​​ функции ВПР из​ сразу весь набор​ случай, когда мы​

​Таблица (Table Array)​ в тексте ниже.​​— это его​​ функций Excel:​​ вдаваться в нюансы​​(ИСТИНА), либо​ способ использования функции​ в разделе Ближайшее​ решение, выделена Условным форматированием.​​Для вывода Наименования используйте формулу =ВПР($E9;$A$13:$C$19;2;ЛОЖЬ) или =ВПР($E9;$A$13:$C$19;2;ИСТИНА) или =ВПР($E9;$A$13:$C$19;2) (т.е.​​Искомое_значение​ или опущен, то​

​ 10 фамилий, каждой​«Интервальный просмотр»​​ соседней таблицы, которая​​ (если их встречается​ ищем числа, а​- таблица из​​Итак, имеем две таблицы​​ значение по умолчанию:​

​Formulas​ записи вложенных функций.​FALSE​ВПР​ ЧИСЛО. Там же можно​

Заключение

​ (см. статью Выделение​​ значение параметра​​- это значение,​ крайний левый столбец​​ фамилии соответствует свой​​нам нужно указать​​ представляет собой прайс-лист.​​ несколько разных), то​ не текст -​​ которой берутся искомые​​ -​Мы заполнили все параметры.​(Формулы) >​ Ведь это статья,​

​(ЛОЖЬ). В примере,​, когда идентификатора не​ найти решение задачи​ строк таблицы в​Интервальный_просмотр​ которое Вы пытаетесь​​ должен быть отсортирован​​ номер. Требуется по​ значение​Кликаем по верхней ячейке​ придется шаманить с​

​ например, при расчете​​ значения, то есть​таблицу заказов​ Теперь нажимаем​Function Library​ посвященная функции​ приведённом выше, выражение​​ существует в базе​​ о поиске ближайшего​ MS EXCEL в​можно задать ЛОЖЬ​ найти в столбце​ в порядке возрастания.​ заданному номеру извлечь​«0»​ (C3) в столбце​ формулой массива.​ Ступенчатых скидок.​

​ наш прайс-лист. Для​​и​​ОК​​(Библиотека Функций) >​​ВПР​ B1​​ данных вообще. Как​​ при несортированном ключевом​ зависимости от условия​ или ИСТИНА или​ с данными.​

​ Если этого не​​ фамилию.​(ЛОЖЬ) или​«Цена»​Усовершенствованный вариант функции ВПР​Все! Осталось нажать​

​ ссылки используем собственное​прайс-лист​
​, и Excel создаёт​
​Lookup & Reference​

Использование функции ВПР (VLOOKUP) для подстановки значений

​Правда ли, что B1​ будто функция​ столбце.​ в ячейке).​ вообще опустить). Значение​

Постановка задачи

​Искомое_значение ​ сделать, функция​​С помощью функции​​«1»​​в первой таблице.​​ (VLOOKUP 2).​

​ОК​ имя "Прайс" данное​:​ для нас формулу​(Ссылки и массивы).​ руководство по Excel.​ меньше B5?​

Решение

​ВПР​Примечание​​Примечание​ ​ параметра ​​может быть числом или​​ВПР​ ​ВПР​​(ИСТИНА). В первом​​ Затем, жмем на​Быстрый расчет ступенчатых (диапазонных)​и скопировать введенную​ ранее. Если вы​Задача — подставить цены​ с функцией​Появляется диалоговое окно​Как бы там ни​Или можно сказать по-другому:​переключилась в режим​. Для удобства, строка​. Никогда не используйте​

​номер_столбца​ текстом, но чаще​может вернуть неправильный​сделать это достаточно​ случае, будут выводиться​ значок​ скидок при помощи​ функцию на весь​ не давали имя,​​ из прайс-листа в​ВПР​Function Arguments​​ было, формула усложняется!​​Правда ли, что общая​​ приближенной работы, и​ таблицы, содержащая найденное​ ВПР() с параметром ​​нужно задать =2,​​ всего ищут именно​ результат.​ просто:​ только точные совпадения,​

​«Вставить функцию»​​ функции ВПР.​​ столбец.​ то можно просто​ таблицу заказов автоматически,​.​​(Аргументы функции). По​ А что, если​ сумма продаж за​​ сама выбирает, какие​​ решение, выделена Условным форматированием.​Интервальный_просмотр​​ т.к. номер столбца​​ число. Искомое значение должно​​Для тех, кто любит​​Из формулы видно, что​​ а во втором​, который расположен перед​

​Как сделать "левый ВПР"​

• ​Функция​​ выделить таблицу, но​ ориентируясь на название​Если поэкспериментируем с несколькими​ очереди заполняем значения​ мы введем еще​ год меньше порогового​ данные предоставить нам,​ Это можно сделать​
• ​ ИСТИНА (или опущен) если​​ Наименование равен 2​ находиться в первом​ создавать не вертикальные,​ первым аргументом функции​ — наиболее приближенные.​ строкой формул.​ с помощью функций​ВПР (VLOOKUP)​ не забудьте нажать​ товара с тем,​ различными значениями итоговой​ аргументов, начиная с​​ один вариант ставки​​ значения?​ когда мы что-то​ с помощью формулы =ПОИСКПОЗ($A$7;$A$11:$A$17;1)=СТРОКА()-СТРОКА($A$10).​ ключевой столбец не​ (Ключевой столбец всегда​ (самом левом) столбце​ а горизонтальные таблицы,​ВПР​
• ​ Так как наименование​​В открывшемся окне мастера​ ИНДЕКС и ПОИСКПОЗ​возвращает ошибку #Н/Д​ потом клавишу​ чтобы потом можно​ суммы продаж, то​Lookup_value​ комиссионных, равный 50%,​Если на этот вопрос​ хотим найти. В​Примечание​
• ​ отсортирован по возрастанию,​​ номер 1). ​ диапазона ячеек, указанного​ в Excel существует​является ячейка С1,​

• ​ продуктов – это​​ функций выбираем категорию​​Как при помощи функции​​ (#N/A) если:​​F4​ было посчитать стоимость.​ мы убедимся, что​​(Искомое_значение). В данном​​ для тех продавцов,​ мы отвечаем​ определённых обстоятельствах именно​: Если в ключевом​ т.к. результат формулы​Для вывода Цены используйте​ в​ аналог​ где мы указываем​
• ​ текстовые данные, то​​«Ссылки и массивы»​​ ВПР (VLOOKUP) заполнять​​Включен точный поиск (аргумент​​, чтобы закрепить ссылку​В наборе функций Excel,​ формула работает правильно.​ примере это общая​​ кто сделал объём​​ДА​ так и нужно.​ столбце имеется значение​ непредсказуем (если функция ВПР()​ аналогичную формулу =ВПР($E9;$A$13:$C$19;3;ЛОЖЬ) (значение параметра ​таблице​ВПР​ искомый номер. Вторым​ они не могут​. Затем, из представленного​ бланки данными из​Интервальный просмотр=0​ знаками доллара, т.к.​ в категории​Когда функция​ сумма продаж из​ продаж более $50000.​(ИСТИНА), то функция​Пример из жизни. Ставим​ совпадающее с искомым,​ находит значение, которое​номер_столбца​.​, но для горизонтального​

​ выступает диапазон A1:B10,​​ быть приближенными, в​​ набора функций выбираем​ списка​) и искомого наименования​

Ошибки #Н/Д и их подавление

​ в противном случае​​Ссылки и массивы​​ВПР​ ячейки B1. Ставим​

• ​ А если кто-то​​ возвращает​​ задачу​ то функция с​​ больше искомого, то​​нужно задать =3). ​
• ​Таблица -​​ поиска.​​ который показывает, где​​ отличие от числовых​​«ВПР»​Как вытащить не первое,​ нет в​
• ​ она будет соскальзывать​(Lookup and reference)​работает с базами​ курсор в поле​ продал на сумму​value if true​Усложняем задачу​ параметром ​ она выводит значение,​Ключевой столбец в нашем​ссылка на диапазон​В Microsoft Excel существует​ следует искать. И​ данных, поэтому нам​. Жмем на кнопку​ а сразу все​​Таблице​​ при копировании нашей​​имеется функция​​ данных, аргумент​Lookup_value​ более $60000 –​
  ​(значение если ИСТИНА).​
• ​Применяем функцию ВПР к​Интервальный_просмотр​ которое расположено на​ случае содержит числа​ ячеек. В левом​ функция​ последний аргумент –​ нужно поставить значение​«OK»​​ значения из таблицы​​.​​ формулы вниз, на​​ВПР​
  ​Range_lookup​
  ​(Искомое_значение) и выбираем​

​ тому заплатить 60%​ В нашем случае​​ решению задачи​​ =ЛОЖЬ вернет первое найденное​ строку выше его).​ и должен гарантировано​ столбце таблицы ищется ​ГПР​​ это номер столбца,​ ​«0»​​.​Функции VLOOKUP2 и VLOOKUP3​Включен приблизительный поиск (​ остальные ячейки столбца​(VLOOKUP)​

​(Интервальный_просмотр) должен принимать​

​ комиссионных?​ это будет значение​Заключение​ значение, равное искомому,​Предположим, что нужно найти​ содержать искомое значение​Искомое_значение​

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Заключение

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

Что такое функция ВПР в Эксель и как ей пользоваться

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

Впр excel — расшифровка и пояснение

Объяснение для чайников начать нужно с расшифровки названия функции. ВПР — всего лишь сокращение от Вертикального Поиска. На английском выглядит название с аббревиатурой так: VLOOKUP – от Vertical LOOKUP. По названию уже можно понять, что функция считывает информацию в столбцах по вертикали, начиная слева направо, и является идентификатором этой информации.
ВПР в эксель — это один из самых быстрых и простых способов найти в таблице интересующие данные. Например, артикул определенного товара в списке. Или его цену. Простыми словами ВПР — это самая обычная команда, которую можно задать с помощью составленной определенным образом формулы — и получить искомый ответ.

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

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

Что еще можно сделать при помощи ВПР в эксель:

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

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

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

Вот самый простой пошаговый пример с картинками, как с использованием ВПР в excel найти нужную информацию:

1. Открыть таблицу эксель.
2. В любой свободной ячейке справа — на картинке-примере ячейка «Е», — указать формулу.
3. Формула **ВПР **начинается со знака «=».
4. Далее нужно задать функцию — это и есть ВПР (соответствует VLOOKUP).
5. Затем открываем круглые скобки, вводим аргументы и закрываем круглые скобки.

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

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

Важно! Функция ВПР в excel всегда ищет, начиная с левого столбца. То есть, сначала ВПР отсортирует элементы по названию. Потом в заданном диапазоне будет искать номер нужного столбца и данные в нем.

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

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

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

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

Например, у вас есть большой прайс на 500 позиций и запрос от покупателя, скажем на 50 позиций (в реальности и прайс и запрос могут быть гораздо больше, но принцип от этого не меняется).

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

Итак, у нас в прайсе 500 позиций. Позиции обозначаются следующим образом, буквами обозначается вид позиции, а цифрами модификация.

Например, «Стул_1» и «Стул_21» это два совершенно разных стула.

Цены в прайсе указаны для примера и вряд ли имеют отношение к реальным ценам.

В ООО «ЫкэА» пришел запрос от «Петровича».

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

Однако это нас не страшит, во-первых, у нас есть ВПР, во-вторых мы и не такое видали.

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

Нам не хочется терять такого клиента и мы практически мгновенно открываем прайс:

Получается у нас должно быть открыто два файла (две книги в Эксель). Запрос от Петровича и Прайс.

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

Для этого перейдем в таблицу запроса и в первой ячейке столбца «Цены» (D4) введем «=впр» и два раза кликнем на значок функции:

Сразу же после этого, в строке формулы нужно поставить курсор внутри надписи ВПР и нажать Fx, перед вами появится окно с аргументами функции ВПР:

В аргументах функции вы говорите Экселю что и где нужно искать:

Искомое значение — это значение (в данном случае наименование), цену которого вы хотите найти в прайсе. Соответственно кликайте на первую ячейку столбца «Наименование».

Далее, сразу переходите в «Прайс»:

Теперь в аргументах функции заполните следующие поля:

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

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

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

Интервальный просмотр — ставьте 0. Ноль обозначает точное соответствие.

После заполнения аргументов функции нажимайте «Ок» и если всё сделано верно, то в столбце «Цена» (файл «Запрос от Петровича»), появится цена.

Вам нужно протянуть цены на оставшиеся ячейки:

Коллеги, вот и всё, вы овладели функцией ВПР.

Очень важное замечание!

Обратите внимание на то, что сейчас мы работали в двух разных файлах (книгах).

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

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

Это позволяет не съезжать формуле когда вы протягиваете её вниз. Это очень актуально когда вы работаете в рамках одного листа или одной книги (в этом случае Эксель автоматически Не закрепляет ячейки).

Давайте посмотрим что получиться если протянуть формулу «без закрепления»:

Обратите внимание, что для первой ячейки всё в порядке и диапазон B3:C502 точно соответствует той таблице, которую мы выделили для поиска данных, однако (без закрепления) так будет не всегда, по мере «протягивания» формулы ВПР вниз, будет смещаться и таблица, пока однажды, мы не увидим вот такую странную надпись #Н/Д:

#Н/Д значит что функция ВПР не смогла найти цену Стул_13 в прайсе и это не мудрено, ведь диапазон таблицы в формуле ВПР уехал ниже этого значения:

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

Очень важное замечание №2

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

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

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

Для этого нужно выделить столбец с формулами, нажать Ctrl+C и в левом верхнем углу выбрать «Вставить» — «Вставить значения».

Для тех кто не любит изучать картинки, я записал небольшое видео в котором показываю всё то, что мы проговорили выше (кроме вставки значений):

Видео — «Быстрый перенос данных с помощью функции ВПР в Экселе»

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

Это очень актуально для тех кто работает в закупках и отправляет заказы поставщику.

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

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

Функция ВПР в Экселе: пошаговая инструкция

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

Во второй – цены:

Если перечень товаров в обеих таблицах совпадает, то, зная магическое сочетание Ctrl+C и Ctrl+V, данные о ценах можно легко подставить к данным о количестве. Однако очередность позиций в обеих таблицах не совпадает. Тупо скопировать цены и подставить к количеству не получится.

Поэтому мы не можем прописать формулу умножения и «протянуть» вниз на все позиции.

Что делать? Надо как-то цены из второй таблицы подставить к соответствующему количеству в первой, т.е. цену товара А к количеству товара А, цену Б к количеству Б и т.д.

Функция ВПР в Эксель легко справится с задачей.

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

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

Кликаем по надписи «ВПР». Открывается следующее диалоговое окно.

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

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

Следующее поле «Номер_столбца» — это число, на которое столбец с искомыми данными (ценами) отстоит от столбца с критерием (наименованием товара) включительно. То есть отсчет идет, начиная с самого столбца с критерием. Если у нас во второй таблице оба столбца находятся рядом, то нужно указать число 2 (первый – критерий, второй — цены). Часто бывает, что данные отстоят от критерия на 10 или 20 столбцов. Это не важно, Excel все сосчитает.

Последнее поле «Интервальный_просмотр», где указывается тип поиска: точное (0) или приблизительное (1) совпадение критерия. Пока ставим 0 (или ЛОЖЬ). Второй вариант рассмотрен ниже.

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

Теперь легко рассчитать стоимость простым умножением количества на цену.

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

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

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

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

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

3. Функция ВПР из диапазона с искомыми данными выдает первое сверху значение. Это значит, что, если во второй таблице, откуда мы пытаемся «подтянуть» некоторые данные, присутствуют несколько ячеек с одинаковым критерием, то в рамках выделенного диапазона ВПР захватит первое сверху значение. Об этом следует помнить. К примеру, если мы хотим к цене товара подтянуть количество из другой таблицы, а там этот товар встречается несколько раз (в нескольких строках), то к цене подтянется первое сверху количество.

4. Последний параметр формулы, который 0 (нуль), ставить нужно обязательно. Иначе формула может криво работать.

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

После вызова функции разрывания внешних связей появится диалоговое окно, где нужно нажать кнопку «Разорвать связь» и затем «Закрыть».

Это позволит удалить сразу все внешние ссылки.

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

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

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

Проще понять на примере. По результатам выполнения плана продаж каждому торговому агенту нужно выдать заслуженную премию (в процентах от оклада). Если план выполнен менее, чем на 100%, премия не положена, если план выполнен от 100% до 110% (110% не входит) – премия 20%, от 110% до 120% (120% не входит) – 40%, 120% и более – премия 60%. Данные находятся в следующем виде.

Требуется подставить премию на основании выполнения планов продаж. Для решения задачи в первой ячейке пропишем следующую формулу:

и скопируем вниз.

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

Джеки Чан выполнил план на 124%. Значит ВПР в качестве критерия ищет во второй таблице ближайшее меньшее значение. Это 120%. Затем отсчитывает 2 столбца и возвращает премию 60%. Брюс Ли план не выполнил, поэтому его ближайший меньший критерий – 0%.

Синтаксис и описание функции ВПР в Excel

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

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

Буква «В» в ВПР означает «вертикальный». Она используется для дифференциации функции ВПР и ГПР, которая ищет значение в верхней строке массива («Г» обозначает «горизонтальный»).

Функция ВПР доступна во всех версиях Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003.

Синтаксис функции ВПР выглядит следующим образом:

Как видите, функция ВПР имеет 4 параметра или аргумента. Первые три параметра обязательные, последний – необязательный.

1. искомое_значение – это значение для поиска.

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

• Поиск числа : =ВПР(40; A2:B15; 2) – формула будет искать число 40.
• Поиск текста : =ВПР(«яблоки»; A2:B15; 2) – формула будет искать текст «яблоки». Обратите внимание, что вы всегда включаете текстовые значения в «двойные кавычки».
• Поиск значения из другой ячейки : =ВПР(C2; A2:B15; 2) – формула будет искать значение в ячейке C2.

1. таблица – это два или более столбца данных.

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

Итак, наша формула =ВПР(40; A2:B15; 2) будет искать «40» в ячейках от A2 до A15, потому что A – это первый столбец таблицы A2: B15.

1. номер_столбца – номер столбца в таблице, из которой должно быть возвращено значение в соответствующей строке.

Самый левый столбец в указанной таблице равен 1, второй столбец – 2, третий – 3 и т. д.

Итак, теперь вы можете прочитать всю формулу =ВПР(40; A2:B15; 2). Формула ищет «40» в ячейках от A2 до A15 и возвращает соответствующее значение из столбца B (потому что B является вторым столбцом в указанной таблице A2:B15).

4. интервальный_просмотр определяет, ищете ли вы точное соответствие (ЛОЖЬ) или приблизительное соответствие (ИСТИНА или опущено). Этот последний параметр является необязательным, но очень важным.

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

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

Чтобы создать именованный диапазон, просто выберите ячейки и введите любое имя в поле « Имя », слева от панели « Формула ».

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

Теперь вы можете написать следующую формулу ВПР, чтобы получить цену Продукта 1:

Функция ВПР в Excel – Пример функции ВПР с именем диапазона

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

Если вы преобразовали диапазон ячеек в полнофункциональную таблицу Excel (вкладка «Вставка» –> «Таблица»), вы можете выбрать диапазон поиска с помощью мыши, а Microsoft Excel автоматически добавит имена колонок или имя таблицы в формулу:

Функция ВПР в Excel – Пример функции ВПР с именем таблицы

Полная формула может выглядеть примерно так:

или даже =ВПР(“Продукт 1”;Таблица6;2).

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

Постановка задачи

Итак, имеем две таблицы – таблицу заказов и прайс-лист:

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

Ошибки #Н/Д и их подавление

Функция ВПР (VLOOKUP) возвращает ошибку #Н/Д (#N/A) если:

• Включен точный поиск (аргумент Интервальный просмотр=0) и искомого наименования нет в Таблице.
• Включен приблизительный поиск (Интервальный просмотр=1), но Таблица, в которой происходит поиск не отсортирована по возрастанию наименований.
• Формат ячейки, откуда берется искомое значение наименования (например B3 в нашем случае) и формат ячеек первого столбца (F3:F19) таблицы отличаются (например, числовой и текстовый). Этот случай особенно характерен при использовании вместо текстовых наименований числовых кодов (номера счетов, идентификаторы, даты и т.п.) В этом случае можно использовать функции Ч и ТЕКСТ для преобразования форматов данных. Выглядеть это будет примерно так:
  =ВПР(ТЕКСТ(B3);прайс;0)
• Функция не может найти нужного значения, потому что в коде присутствуют пробелы или невидимые непечатаемые знаки (перенос строки и т.п.). В этом случае можно использовать текстовые функции СЖПРОБЕЛЫ (TRIM) и ПЕЧСИМВ (CLEAN) для их удаления:
  =ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B3));прайс;0)
  =VLOOKUP(TRIM(CLEAN(B3));прайс;0)

Для подавления сообщения об ошибке #Н/Д (#N/A) в тех случаях, когда функция не может найти точно соответствия, можно воспользоваться функцией ЕСЛИОШИБКА (IFERROR) . Так, например, вот такая конструкция перехватывает любые ошибки создаваемые ВПР и заменяет их нулями:

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

• lookup_value(искомое_значение) – это величина для поиска, из крайнего левого столбца таблицы. Это может быть значение, ссылка на ячейку или текстовая строка. В примере со студентами это их фамилии;
• table_array (таблица) – это диапазон данных, в котором будет осуществлен поиск. Это может быть ссылка на диапазон ячеек или именованный диапазон. В примере с таблицей со студентами, это будет вся таблица, которая содержит оценку и фамилии студентов;
• col_index (номер_столбца) – это порядковый номер столбца в диапазоне с данными, из которого будет получена искомая величина;
• [range lookup]([интервальный_просмотр]) – этот аргумент указывает на точность совпадения данных при поиске. Укажите “0” – если точное, “1” – если приблизительное совпадение.

Как пользоваться функцией ВПР в Excel

Допустим, на склад предприятия по производству тары и упаковки поступили материалы в определенном количестве.

Стоимость материалов – в прайс-листе. Это отдельная таблица.

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

1. Приведем первую таблицу в нужный нам вид. Добавим столбцы «Цена» и «Стоимость/Сумма». Установим денежный формат для новых ячеек.
2. Выделяем первую ячейку в столбце «Цена». В нашем примере – D2. Вызываем «Мастер функций» с помощью кнопки «fx» (в начале строки формул) или нажав комбинацию горячих клавиш SHIFT+F3. В категории «Ссылки и массивы» находим функцию ВПР и жмем ОК. Данную функцию можно вызвать перейдя по закладке «Формулы» и выбрать из выпадающего списка «Ссылки и массивы».

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

Теперь найти стоимость материалов не составит труда: количество * цену.

Функция ВПР связала две таблицы. Если поменяется прайс, то и изменится стоимость поступивших на склад материалов (сегодня поступивших). Чтобы этого избежать, воспользуйтесь «Специальной вставкой».

1. Выделяем столбец со вставленными ценами.
2. Правая кнопка мыши – «Копировать».
3. Не снимая выделения, правая кнопка мыши – «Специальная вставка».
4. Поставить галочку напротив «Значения». ОК.

Формула в ячейках исчезнет. Останутся только значения.

Быстрое сравнение двух таблиц с помощью ВПР

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

В старом прайсе делаем столбец «Новая цена».

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

Как работает функция ВПР в Excel: несколько примеров для «чайников».

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

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

Использование точного и приблизительного поиска.

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

Обратите внимание, что четвертый параметр равен 1.

Кое-что из результатов определено верно, но в большинстве случаев – ошибки. Функция продолжает просматривать данные столбца D с наименованиями товаров до тех пор, пока не встретит значение больше, чем заданное ей в качестве критерия поиска. Тогда она останавливается и возвращает цену.

Поиск цены на египетские бананы закончился на первой же позиции, так как во второй записаны сливы. А это слово по правилам алфавита стоит ниже, чем «Бананы Египет». Значит, дальше искать не нужно. Получили 145. И не важно, что это цена абрикосов. Поиск цены на сливы происходил до тех пор, пока в D15 не встретилось слово, которое по алфавиту стоит ниже: яблоки. Остановились и взяли цену из предыдущей строки.

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

Вы спросите: «А зачем тогда этот неточный просмотр, если с ним столько проблем?»

Он отлично подходит для выбора значений из определенных интервалов.

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

Если у нас количество товара 11 единиц, то мы просматриваем столбец D до тех пор, пока не встретим число, большее 11. Это 20 и находится оно в 4-й строке. Останавливаемся здесь. Значит, наша скидка расположена в 3-й строке и равна 3%.

Использование нескольких условий.

Еще один простой пример для «чайников» – как использовать при выборе нужного значения несколько условий?

Предположим, у нас есть список имен и фамилий. Нам нужно найти нужного человека и вывести сумму его дохода.

В F2 используем следующую формулу:

Разберем пошагово, как в этом случае работает ВПР.

В начале мы формируем условие. Для этого при помощи оператора & «склеиваем» вместе имя и фамилию, а между ними вставляем пробел.

Не забываем при этом пробел заключить в кавычки, иначе Excel не воспримет его как текст.

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

Дальше все происходит по уже отработанной схеме.

Можно попробовать подстраховаться на тот случай, если между именем и фамилией введено несколько пробелов. Знак пробела в формуле заменяем на знак подстановки «*».