Функция ВПР

September 11, 2014

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

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

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

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

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

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

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

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

Расположена формула ВПР во вкладке "Мастер функций" и разделе "Ссылки и массивы". Диалоговое окно функции имеет следующий вид:

Аргументы в формулу вносятся в порядке очереди:

• Искомое значение - то, что должна найти функция, и вариантами которого являются значения ячейки, ее адрес, имя, заданное ей оператором. В нашем случае - это фамилия и имя менеджера.
• Таблица - диапазон строк и столбцов, в котором ищется критерий.
• Номер столбца - его порядковое число, в котором располагается сумма продаж, то есть результат работы формулы.
• Интервальный просмотр. Он вмещает значение либо ЛОЖЬ, либо ИСТИНА. Причем ЛОЖЬ возвращает только точное совпадение, ИСТИНА - разрешает поиск приблизительного значения.

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

Наглядный пример организации таблицы

Формула, записанная в Д, будет выглядеть так: =ВПР (С1; А1:В5; 2; 0), то есть =ВПР (искомое значение; диапазон данных таблицы; порядковый номер столбца; 0). В качестве четвертого аргумента вместо 0 можно использовать ЛОЖЬ.

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

Закрепить область рабочего диапазона данных можно при помощи абсолютных ссылок. Для этого вручную проставляются знаки $ перед буквенными и численными значениями адресов крайних левых и правых ячеек таблицы. В нашем случае формула принимает вид: =ВПР (С1; $А$1:$В$5; 2; 0).

Функция ВПР не работает, и тогда появляется сообщение в столбце вывода результата об ошибке (#N/A или #Н/Д). Это происходит в таких случаях:

1. Формула введена, а столбец искомых критериев не заполнен (в данном случае колонка С).
2. В столбец С внесено значение, которое отсутствует в колонке А (в диапазоне поиска данных). Для проверки наличия искомого значения следует выделить столбец критериев и во вкладке меню "Правка" - "Найти" вставить данную запись, запустить поиск. Если программа не находит его, значит оно отсутствует.
3. Форматы ячеек колонок А и С (искомых критериев) различны, например, у одной - текстовый, а у другой - числовой. Изменить формат ячейки можно, если перейти в редактирование ячейки (F2). Такие проблемы обычно возникают при импортировании данных с других прикладных программ. Для избежания подобного рода ошибок в формулу ВПР есть возможность встраивать следующие функции: ЗНАЧЕН или ТЕКСТ. Выполнение данных алгоритмов автоматически преобразует формат ячеек.
4. В коде функции присутствуют непечатные знаки или пробелы. Тогда следует внимательно проверить формулу на наличие ошибок ввода.
5. Задан приблизительный поиск, то есть четвертый аргумент функции ВПР имеет значение 1 или ИСТИНА, а таблица не отсортирована по восходящему значению. В этом случае столбец искомых критериев требуется отсортировать по возрастанию.

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

Ошибка под №5 является довольно распространенной и наглядно изображена на рисунке ниже.

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

При применении 1 или ИСТИНЫ в четвертом аргументе нужно следить, чтобы столбец с искомыми критериями был отсортирован по возрастанию. При использовании 0 или ЛЖИ данная необходимость отпадает, но также отсутствует тогда возможность интервального просмотра.

Просто следует учитывать, что особенно важно сортировать интервальные таблицы. Иначе функция ВПР будет выводить в ячейки неправильные данные.

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

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

Другой вариант - озаглавить - подразумевает выделение диапазона данных, потом переход в меню "Вставка"- "Имя"- "Присвоить".

Для того чтобы использовать данные, размещенные на другом листе рабочей книги, при помощи функции ВПР, необходимо во втором аргументе формулы прописать расположение диапазона данных. Например, =ВПР (А1; Лист2!$А$1:$В$5; 2; 0), где Лист2! - является ссылкой на требуемый лист книги, а $А$1:$В$5 - адрес диапазона поиска данных.

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

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

Функция ВПР отлично справляется с решением данной задачи. В столбце G под заголовком "Оценки" записывается соответствующая формула: =ВПР (Е4, В3:С13, 2, 0). Ее нужно скопировать на всю колонку таблицы.

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

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

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

Применение функции ВПР

Функция ВПР. пожалуй, одна из самых красивых функций в наборе Excel, она относится к функциям ссылок и подстановок. Вариантов ее применения множество. Основное применение - поиск совпадений в разных списках (сравнение баз данных). Синтаксис написания формулы следующий:

Рассмотрим параметры функции ВПР:

A1 - это относительная ссылка на ячейку листа Excel, в которой находится искомое значение. Под относительной ссылкой понимается то, что при копировании формулы по столбцам/строкам ссылка будет меняться соответственно. Чтобы при копировании формулы в другие столбцы/строки ссылка на столбец/строку не изменялась ее можно сделать абсолютной по одному/обоим параметрам. Например:
$A$1 - абсолютная ссылка по столбцу и по строке;
$A1 - абсолютная по столбцу, относительная по строке;
A$1 - относительная по столбцу, абсолютная по строке;
A1 - относительная ссылка по столбцу и строке.
Значек $ можно вставить в формулу вручную, либо выделить в строке формул ссылку и последовательно нажимая F4 добиться нужного результата.

База_данных - имя области данных в первом столбце которой, производится поиск совпадения значения с параметром A1. Столбец по которому производится поиск всегда должен быть первым. Область данных можно также задать указав адрес левой верхней и правой нижней ячейки. Например:

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

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

Третий параметр функции ВПР - "2 " - номер столбца откуда функция будет брать данные при нахождении искомого значения A1. Если номер столбца указан неверно (напрмер в области всего 8 столбцов а указали 9), функция ВПР вернет значение #ССЫЛКА! .

Параметр ЛОЖЬ означает что функция ВПР будет искать точное совпадение значения в ячейке A1 со значениями в первом столбце области данных. При нахождении такого значения функция вернет значение из заданного столбца (в данном примере - второй столбец) области данных. Если значение A1 не найдено, функция ВПР вернет #Н/Д - нет данных. Вторым значением этого параметра может быть ИСТИНА. В этом случае функция ВПР будет искать ближайшее к искомому значению A1. Честно говоря механизм определения этого ближайшего значения до конца не ясен, особенно когда искомое значение текст. Поэтому применение функции ВПР с параметром "ИСТИНА" встречается достаточно редко.

Применение функции ВПР

ВПР excel

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

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

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

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

Мы будем производить поиск по артикулу,
в Искомом значении выставляем: F3 — что соответствует самому первому артикулу в таблице Б
в поле Таблица пишем: ААА — что соответствует имени той таблицы, что мы создали выше
в поле Номер столбца пишем: 4 — что соответствует 4той колонке с ценами в таблице А
в поле Интервальный просмотр пишем:0
Нажмите ОК .

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

Как видите, всё очень просто.

ВПР и ГПР - Формулы рабочего листа - Excel - Каталог статей - Perfect Excel

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

искомое_значение - константа или ссылка на ячейку, значение которой вы собираетесь искать

массив - диапазон ячеек, состоящий, как минимум, из двух столбцов (в противном случае данная формула теряет всякий смысл) и, как правило, большого количества строк

номер_столбца - номер столбца, указанного масива, из которого будут возвращаться данные

тип_поиска - переключатель типа поиска. Если вы укажите 0 или ЛОЖЬ (FALSE), то функция будет искать точное соответствие с искомым значением, а если укажете 1 или ИСТИНА (TRUE), то формула вернёт максимальное значение, которое МЕНЬШЕ или РАВНО искомого параметра.

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

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

Поиск оснанавливается на первом совпадении результата, если тип поиска = 0 (точное совпадение) и на последнем, если тип поиска = 1 (примерное совпадение). Таким образом у вас есть возможность при помощи ВПР возвращать либо первую совпавшую строку, либо последнюю. Однако имейте в виду, что при нечётком поиске (тип поиска = 1) первый столбец массива должен быть отсортирован по возрастанию.

ВПР поддерживает использование символов подстановки ( * и. ) в параметре искомого значения. ". " - заменяет собой любой один символ, а " * " - заменяет любое количество любых символов. Если используете символы подстановки, то четвёртый параметр должен быть 0 (ЛОЖЬ).

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

При поиске текстовых строк ВПР не видит разницы в регистре букв.

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

Функция ГПР ( Г оризонтальный ПР осмотр) - это тот же самый ВПР. в котором строки заменили на столбцы и наоборот. ГПР имеет следующий синтаксис:

искомое_значение - константа или ссылка на ячейку, значение которой вы собираетесь искать

массив - диапазон ячеек, состоящий, как минимум, из двух строк (в противном случае данная формула теряет всякий смысл) и, как правило, большого количества столбцов

номер_строки - номер строки, указанного масива, из которой будут возвращаться данные

тип_поиска - переключатель типа поиска. Если вы укажите 0 или ЛОЖЬ (FALSE), то функция будет искать точное соответствие с искомым значением, а если укажете 1 или ИСТИНА (TRUE), то формула вернёт максимальное значение, которое МЕНЬШЕ или РАВНО искомого параметра.

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

У функции ГПР один недостаток - она никому не нужна. Дело в том, что она противоречит концепции Excel: ведь при грамотном использовании таблиц искать надо в основном в вертикальном столбце, а не в горизонтальной строке. А в тех не частых случаях, когда всё-таки надо что-то найти в строке, есть более удобоваримая и универсальная функция ПОИСКПОЗ (MATCH). За сим рассмотрение ГПР заканчиваю. Хотя, собственно, все замечания справедливые для ВПР. справедливы и для ГПР.

Функция впр в excel: примеры проведения расчетов

Работать с Excel легко только в том случае, если вы уже «собаку на этом съели» или же вам нужно выполнить какую-то элементарную задачу. Ну, скажем, вам нужно нарисовать простенькую табличку или же график.

Однако в тех случаях, когда нужно произвести расчеты, Эксель уже не кажется легким в исполнении. Оказывается, что у него много функций, и для того чтобы понять, когда какую применять, нужно иметь какой-то опыт и багаж знаний. Ранее я уже рассказывал вам о некоторых функциях (СУММЕСЛИ. ЕСЛИ и пр.), но вот пришло время поговорить о функции ВПР. Что ж, усаживайтесь поудобнее, далее мы поговорим о том, что собой представляет функция впр в excel, примеры и способы ее применения.

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

Выполняется функция впр в excel 2007 очень просто:

1. Зайдите в Excel и выберите ячейку, для которой нужно применить функцию ВПР.
2. Теперь перейдите во вкладку «Формулы» — «Вставить функции».
3. Перед вами появится окошко, в котором будут отображены все действующие формулы Excel. Чтобы не тратить время на поиски нужной, просто введите в соответствующем поле ее название и нажмите «ОК».
   

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

Функция ВПР в Excel для чайников: как пользоваться Vlookup

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

У нас есть две таблицы — товары и накладная. Необходимо, используя формулу, заполнить накладную. Используем для этого следующую формулу:

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

Важно! Аргументы функции отделяются друг от друга символом «;».

В нашем случае формула приобретает следующее значение:

• A3 – искомое значение, это надпись «деревянные кольца», именно ее мы ищем в таблице «Товары»;
• F3:H13 – границы таблицы в которой мы ищем данные;
• 3 – номер столбца;
• 0 – параметр, отвечающий за точность совпадения. Для получения точного совпадения ставим значение 0.

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

Читайте также: Неисправности работы приложения Магазин в Windows 10

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

С несколькими условиями ВПР используется для нахождения товара по двум или более параметрам. Для этого:

1. Создайте два условия для поиска.
2. Добавьте новый столбец, в нем будут объединятся столбцы с теми данными по которым нам нужно осуществить поиск.
3. Таким образом, мы получим один нужный нам столбец и сможем воспользоваться формулой как обычно.

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

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

Если у вас есть вопрос по работе с продуктами Microsoft, спросите об этом. Вместе мы найдем ответ.

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

Использование функции вертикального просмотра (ВПР) для организации связи между таблицами

Задание: Создайте две таблицы на разных листах EXCEL. Одна таблица – справочная (дайте листу название – «Прейскурант»). Она содержит цены за 1 день пребывания в стране:

Второй лист переименуйте – Путевки. На этом листе расположите таблицу:

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

Устанавливаем курсор в ячейку D2 и вызываем Вставка – Функция. Выбираем категорию «Ссылки и массивы», а затем функцию ВПР. В появившемся окне (см. ниже пример) задаем искомое значение – ячейку В2. Далее переходим к аргументу «Таблица». Переходим на лист «Прейскурант» и выделяем всю справочную таблицу вместе с заголовком. И нажимаем клавишу F4. Далее – аргумент «номер столбца» ставим цифру 2, т.к. во втором столбце аргумента «таблица» расположены цена за 1 день пребывания в стране. И последний аргумент «интервальный просмотр» пишем ложь. т.к. во-первых нам необходимо найти точное соответствие искомому значению, и, во-вторых, таблица поиска не отсортирована по алфавиту. Вид окна функции ВПР должен быть:

Обратите внимание, что если все введено правильно – в окне функции уже виден результат поиска – число 12, что соответствует 1 дню пребывания в Китае. Нажимаем ОК.

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

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