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

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

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

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

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

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

Таблица — ссылка на спектр ячеек. В левом столбце таблицы ищется Искомое_значение , а из столбцов расположенных правее, выводится соответственный итог (хотя, в принципе, можно вывести можно вывести значение из левого столбца (в этом случае это будет само искомое_значение )). Нередко левый столбец именуется главным . Если 1-ый столбец не содержит искомое_значение , то функция возвращает значение ошибки #Н/Д.

Номер_столбца — номер столбца Таблицы , из которого необходимо выводить итог. Самый левый столбец (главный) имеет номер 1 (по нему делается поиск).

Параметр интервальный_просмотр может принимать 2 значения: ИСТИНА (ищется значение наиблежайшее к аспекту либо совпадающее с ним) и ЛОЖЬ (ищется значение в точности совпадающее с аспектом). Значение ИСТИНА подразумевает, что 1-ый столбец в таблице отсортирован в алфавитном порядке либо по возрастанию. Это метод употребляется в функции по дефлоту, если не указан иной.

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

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

Пусть дана начальная таблица (см. файл примера лист Справочник ).

Задачка состоит в том, чтоб, выбрав подходящий Артикул продукта, вывести его Наименование и Стоимость .

Примечание . Это «традиционная» задачка для использования ВПР() (см. статью Справочник ).

Для вывода Наименования используйте формулу =ВПР($E9;$A$13:$C$19;2;ЛОЖЬ) либо = ВПР($E9;$A$13:$C$19;2;ИСТИНА) либо = ВПР($E9;$A$13:$C$19;2) (т.е. значение параметра Интервальный_просмотр можно задать ЛОЖЬ либо ИСТИНА либо совершенно опустить). Значение параметра номер_столбца необходимо задать =2, т.к. номер столбца Наименование равен 2 (Главный столбец постоянно номер 1).

Для вывода Цены используйте аналогичную формулу =ВПР($E9;$A$13:$C$19;3;ЛОЖЬ) (значение параметра номер_столбца необходимо задать =3).

Главный столбец в нашем случае содержит числа и должен гарантировано содержать разыскиваемое значение (условие задачки). Если 1-ый столбец не содержит разыскиваемый артикул , то функция возвращает значение ошибки #Н/Д. Это может произойти, к примеру, при опечатке при вводе артикула. Чтоб не ошибиться с вводом искомого артикула можно употреблять Выпадающий перечень (см. ячейку Е9 ).

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

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

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

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

Примечание . Для удобства, строчка таблицы, содержащая отысканное решение, выделена Условным форматированием . (см. статью Выделение строк таблицы в MS EXCEL в зависимости от условия в ячейке ).

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

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

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

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

Главный столбец, по которому должен выполняться поиск, должен быть самым левым в таблице;
Главный столбец должен быть непременно отсортирован по возрастанию;
Значение параметра Интервальный_просмотр необходимо задать ИСТИНА либо совершенно опустить.

Для вывода Наименования продукта используйте формулу =ВПР($A7;$A$11:$B$17;2;ИСТИНА)

Для вывода отысканной цены (она не непременно будет совпадать с данной) используйте формулу: =ВПР($A7;$A$11:$B$17;1;ИСТИНА)

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

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

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

Интересно почитать: Excel vba функции

Примечание . Для удобства, строчка таблицы, содержащая отысканное решение, выделена Условным форматированием . Это можно создать при помощи формулы =ПОИСКПОЗ($A$7;$A$11:$A$17;1)=СТРОКА()-СТРОКА($A$10) .

Примечание : Если в главном столбце имеется значение совпадающее с разыскиваемым, то функция с параметром Интервальный_просмотр =ЛОЖЬ возвратит 1-ое отысканное значение, равное разыскиваемому, а с параметром =ИСТИНА — крайнее (см. картину ниже).

Если столбец, по которому делается поиск не самый левый, то ВПР() не поможет. В этом случае необходимо употреблять функции ПОИСКПОЗ() + ИНДЕКС() либо ПРОСМОТР() .

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

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

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

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

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

Если кратко, то можно просто уяснить последующее:

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

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

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

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

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

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

Смысл этих аргументов последующий:

Искомое_значение
Неотклонимый параметр. Это то самое значение, которое обязана находить функция в первом(!) столбце таблицы, данной аргументом 2 (см. ниже). Тут обычно указывается адресок ячейки, в которой находится необходимое нам значение (в примере, рассмотренном ниже, это адресок ячейки в одной из строк колонки «Должность»). Указывать обычный текст либо числовую константу не имеет смысла, потому что функция ВПР употребляется для обработки огромных массивов данных. Если искомое_значение меньше, чем меньшее значение в первом столбце аргумента таблица, функция ВПР возвращает значение ошибки #Н/Д.
Таблица
Тоже неотклонимый параметр. Это та таблица, данные из которой мы собираемся добавить. Таблица указывается как спектр ячеек (в примере, рассмотренном ниже, это спектр А10:B12). Направьте внимание, что спектр ячеек должен быть указан таковым образом, чтоб в первом(!) его столбце находились данные, которые мы отыскиваем (в нашем примере это столбец «Должность»). Значения в первом столбце могут быть текстовыми, числовыми либо логическими. Текстовые значения в нижнем и верхнем регистре числятся эквивалентными.
Номер_столбца
Указывать непременно. Это порядковый номер столбца, данные из которого необходимо возвратить функции ВПР в качестве ответа, если в первом столбце 2-ой таблицы будет найдено разыскиваемое значение (аргумент 1). Столбцы нумеруются начиная с 1 (единица), так что в нашем примере (см. дальше) это столбец 2 («Заработная плата»). Итого: если номер_столбца = 1, то ворачивается значение из первого столбца таблицы; если номер_столбца = 2 — значение из второго столбца таблицы и т. д.
интервальный_просмотр
Варианты значений: 0 либо 1. Если указать 0, то функция будет находить четкое совпадение для параметра 1 (разыскиваемое значение); если указать 1, то поиск будет выполняться приближённо. Почаще всего употребляется конкретно 0, так как требуется отыскать конкретно то, что у нас в аналогичном столбце первой таблицы (в нашем примере это должность сотрудника).

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

В ординарном примере ниже эти аргументы рассмотрены на практике.

Функция ВПР, пошаговая {инструкция}

Итак, вначале у нас есть две таблицы, которые Вы сможете созидать на снимке экрана ниже. Общим столбцом для обеих таблиц является «Должность».

[нажмите на картинку для увеличения]
Справка: как сохранять фото с веб-сайтов

Цель: добавить в первую (верхнюю) таблицу в столбец «Заработная плата» данные из аналогичного столбца 2-ой (нижней) таблицы.

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

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

Интересно почитать: Как в excel убрать функцию

Функция ВПР, {инструкция}

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

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

В итоге для ячейки C4 мы получим последующую формулу:
=ВПР(B4; A$10:B$12; 2; 0)

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

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

Так как в качестве параметра «Номер столбца» мы указали 2, то функция ВПР вернёт в итоге то, что обозначено в колонке «Заработная плата».

Итого приобретенный итог: «40 000»

Это значение и будет результатом выполнения функции ВПР в данном определенном случае. На снимке экрана выше Вы сможете созидать, что значение «40 000» вписано в ячейку C4 первой таблицы.

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

Заполним подобными формулами другие ячейки

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

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

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

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

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

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

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

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

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

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

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

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

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

Глядеть видео

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

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

Функция ВПР {инструкция}.pdf

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

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

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

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

Функция ВПР в Excel дозволяет данные из одной таблицы переставить в надлежащие ячейки 2-ой. Ее английское наименование – VLOOKUP.

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

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

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

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

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

Приведем первую таблицу в подходящий нам вид. Добавим столбцы «Стоимость» и «Стоимость/Сумма». Установим валютный формат для новейших ячеек.
Выделяем первую ячейку в столбце «Стоимость». В нашем примере – D2. Вызываем «Мастер функций» при помощи клавиши «fx» (в начале строчки формул) либо нажав комбинацию жарких кнопок SHIFT+F3. В группы «Ссылки и массивы» находим функцию ВПР и нажимаем ОК. Данную функцию можно вызвать перейдя по закладке «Формулы» и избрать из выпадающего перечня «Ссылки и массивы».
Раскроется окно с аргументами функции. В поле «Разыскиваемое значение» — спектр данных первого столбца из таблицы с количеством поступивших материалов. Это те значения, которые Excel должен отыскать во 2-ой таблице.
Последующий аргумент – «Таблица». Это наш прайс-лист. Ставим курсор в поле аргумента. Перебегаем на лист с ценами. Выделяем спектр с наименованием материалов и ценами. Показываем, какие значения функция обязана сравнить.
Чтоб Excel ссылался конкретно на эти данные, ссылку необходимо зафиксировать. Выделяем значение поля «Таблица» и жмем F4. Возникает значок $.
В поле аргумента «Номер столбца» ставим цифру «2». Тут находятся данные, которые необходимо «подтянуть» в первую таблицу. «Интервальный просмотр» — ЛОЖЬ. Т.к. нам необходимы четкие, а не ориентировочные значения.

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

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

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

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

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

Резвое сопоставление 2-ух таблиц при помощи ВПР

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

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

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

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

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

Таблица для примера:

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

Дело осложняется тем, что от 1-го поставщика поступает несколько наименований.

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

Разглядим формулу детально:

Что отыскиваем.
Где отыскиваем.
Какие данные берем.

Функция ВПР и выпадающий перечень

Допустим, какие-то данные у нас изготовлены в виде раскрывающегося перечня. В нашем примере – «Материалы». Нужно настроить функцию так, чтоб при выбирании наименования появлялась стоимость.

Поначалу создадим раскрывающийся перечень:

Ставим курсор в ячейку Е8, где и будет этот перечень.
Заходим на вкладку «Данные». Меню «Проверка данных».
Избираем тип данных – «Перечень». Источник – спектр с наименованиями материалов.
Когда нажмем ОК – сформируется выпадающий перечень.

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

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

Изменяем материал – изменяется стоимость:

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