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

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

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

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

Алгоритм действий:

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

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

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

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

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

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



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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Задача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).

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Первый простой пример – имеются 2 таблицы. В одной указываются Товары и их идентификаторы (ID). Во второй, с помощью фильтра по ID, мы хотим получить наименование товара.

После знака равно вводим ВПР, затем Enter и Fx для ввода аргументов.

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

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

Для второго аргумента выделяем диапазон таблицы.

Обязательно необходимо зафиксировать (указать знаки доллара или клавиша F4 для всего диапазона) и сделать абсолютные ссылки для того чтобы диапазон не «сползал», так как в данном случае не указывается имя таблица.

Номер столбца – то, что нужно вернуть. В этом примере требуется вернуть 2 столбец (Товар). Для точного поиска 4 аргумент – .

Введя все значения, жмём кнопку ОК.

Теперь при изменении в фильтре номера ID будет изменяться наименование товара.

Теперь посмотрим другой пример.

Теперь нужно получить партию для каждого наименования товара по критерию Количество.

Например, для мелкой партии количество должно быть от 100 до 200, средней – 200-300 и т.д.

Искомым значением в данном случае будет количество, Таблицу выбираем диапазон Критерий – Партия (фиксируем F4). Номер столбца 2, интервальный просмотр в этом случае должен быть 1 (позволит получить ближайшее меньшее значение к искомому значению).

ВПР по двум и более критериям

Хитрости » 18 Январь 2017 Дмитрий 34258 просмотров

Основные понятия (23) Сводные таблицы и анализ данных (9) Графики и диаграммы (5) Работа с VB проектом (12) Power BI и Power Query (16)

Условное форматирование (5) Списки и диапазоны (5) Макросы(VBA процедуры) (63) Разное (39) Баги и глюки Excel (3)

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

Для большего понимания смысла статьи рекомендую сначала скачать файл с примером данных и формул:
Скачать пример:

Tips_All_VlookupMany.xls (62,5 KiB, 3 370 скачиваний)

Например, имеется файл с таблицей такого вида:

И необходимо получить сумму выручки не только на основании салона, но и на основании бренда. При этом делать это автоматически, например для получения данных в такую табличку:

Т.е. в ячейке B2 из выпадающего списка выбирается Салон, а из B3 — Бренд(подробнее про выпадающие списки можно прочесть в статье Выпадающие списки). И в зависимости от выбора должна рассчитаться сумма Выручки и должен проставиться комментарий.

В приложенном к статье файле исходная таблица находится на листе «Отчет сентябрь», а вторая(с выбором) на листе «Выбор». На самом деле это не сильно принципиально, просто будет более понятна логика формул, приведенных ниже.
Сумму по двум критериям можно найти при помощи той же СУММЕСЛИМН(SUMIFS):
=СУММЕСЛИМН(‘Отчет сентябрь’!$C$2:$C$67;’Отчет сентябрь’!$A$2:$A$67;B2;’Отчет сентябрь’!$B$2:$B$67;B3)
=SUMIFS(‘Отчет сентябрь’!$C$2:$C$67,’Отчет сентябрь’!$A$2:$A$67,Выбор!B2,’Отчет сентябрь’!$B$2:$B$67,Выбор!B3)
Подробнее про поиск сумм по двум и более критериям можно прочитать в статье Функция СУММЕСЛИ, а так же СУММЕСЛИ по двум критериям.
А вот с комментарием сложнее — там содержится текст и СУММЕСЛИМН ничего не найдет. Да и в случае, если в исходных данных есть задвоения и суммировать по определению ничего не надо СУММЕСЛИМН будет не лучшим вариантом — ведь она вернет сумму всех ячеек, удовлетворяющих условиям. А это, повторюсь — не всегда нужно.
Здесь на помощь придет родственная с ВПР функция — ПОИСКПОЗ(MATCH):
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;ПОИСКПОЗ(B2&B3;’Отчет сентябрь’!$A$2:$A$67&’Отчет сентябрь’!$B$2:$B$67;0))
=INDEX(‘Отчет сентябрь’!$D$2:$D$67,MATCH(B2&B3,’Отчет сентябрь’!$A$2:$A$67&’Отчет сентябрь’!$B$2:$B$67,0))
Эта функция является формулой массива. Это означает, что вводить её в ячейки надо нажатием не просто Enter, а сочетанием трех клавиш — Ctrl+Shift+Enter. Теперь рассмотрим подробнее принцип работы этой формулы, чтобы можно было применить на любых данных. На самом деле принцип не так уж сложен. Основной упор сделан на эти две «связки»:

• B2&B3 — здесь объединяем значение выбранного Салона(Санкт-Петербург) и Бренда(Ниссан) в одну строку, чтобы получилось «Санкт-ПетербургНиссан». За объединение двух значений отвечает амперсанд(&).
• ‘Отчет сентябрь’!$A$2:$A$67&’Отчет сентябрь’!$B$2:$B$67 — а здесь мы последовательно объединяем в одну строку значения двух столбцов исходных данных — Салон и Бренд. Т.е. в результате мы получим массив объединенных значений: МоскваBMW, ВоронежBMW, СамараЛексус и т.д.(именно это объединение требует, чтобы формула вводилась как формула массива). И уже среди этих значений ищем «Санкт-ПетербургНиссан». При нахождении совпадения ПОИСКПОЗ вернет позицию строки, в которой нашел. И передаст её в ИНДЕКС. А т.к. ИНДЕКС в нашем случае возвращает значение из заданной строки указанного массива, то мы получаем требуемое.

По шагам это будет выглядеть так:
сначала искомые значения будут объединены в один
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;ПОИСКПОЗ(B2&B3;’Отчет сентябрь’!$A$2:$A$67&’Отчет сентябрь’!$B$2:$B$67;0))
=>
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;ПОИСКПОЗ(Санкт-Петербург&Ниссан;’Отчет сентябрь’!$A$2:$A$67&’Отчет сентябрь’!$B$2:$B$67;0))
=>
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;ПОИСКПОЗ(Санкт-ПетербургНиссан;’Отчет сентябрь’!$A$2:$A$67&’Отчет сентябрь’!$B$2:$B$67;0))
=>
далее так же объединяем построчно значения всех столбцов для поиска значений
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;ПОИСКПОЗ(Санкт-ПетербургНиссан;{«МоскваBMW»:»ВоронежBMW»:»СамараЛексус»:…};0))
=>
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;4)
=>
только допы
Здесь важно следить за тем, чтобы значения в исходном массиве для поиска объединялись в том же порядке, что и искомые значения. На приложенном примере, если первым аргументом(искомое_значение) в ПОИСКПОЗ мы указали сначала Салон(B2), а потом Бренд(B3), то и вторым аргументом(просматриваемый_массив) мы должны указать два столбца в том же порядке — сначала Салон(‘Отчет сентябрь’!$A$2:$A$67), а уже потом Бренд(‘Отчет сентябрь’!$B$2:$B$67). Если их перепутать ошибки как таковой это не вызовет, но и значения найдены не будут.
Так же стоит учитывать, что такая формула будет рассчитываться дольше обычной, поэтому не стоит указывать столбцы полностью(‘Отчет сентябрь’!$A:$A), т.к. это может повлечь за собой расчет всего одной формулы непозволительно долгое время.
При этом очевидно, что просматривать значения можно не только из смежных столбцов, а из любых и уж точно они могут быть и первыми, и последними. И значения возвращаемые могут так же располагаться в любом столбце таблицы. И также хочу заметить, что это в примере используется только два критерия — реально их можно сделать и три, и пять, и десять. Объединяем сколько надо, указываем нужное кол-во столбцов так же с объединением и все. Но тут надо понимать, что в некоторых случаях будет оптимальнее добавить в исходные данные(те, где ищем) еще один столбец, в котором записать все данные, объединив их. Т.е. опять же на примере файла из статьи можно на листе «Отчет сентябрь» добавить формулу в столбец E: =A2&B2
и затем искать можно будет обычной формулой:
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;ПОИСКПОЗ(B2&B3;’Отчет сентябрь’!$E$2:$E$67;0))
=INDEX(‘Отчет сентябрь’!$D$2:$D$67,MATCH(B2&B3,’Отчет сентябрь’!$E$2:$E$67,0))
Больше нюансов, о которых я бы хотел рассказать, нет. Все стандартно, как для обычной связки ИНДЕКС(ПОИСКПОЗ.

В приложенном к статье примере приведен пример и с функцией СУММЕСЛИМН и с функцией ПОИСКПОЗ.
Скачать пример:

Tips_All_VlookupMany.xls (62,5 KiB, 3 370 скачиваний)

P.S. Если кого-то пугает тот факт, что формулу надо вводить как формулу массива(Ctrl+Shift+Enter), то можно модифицировать формулу так:
=ИНДЕКС(‘Отчет сентябрь’!$D$2:$D$67;СУММПРОИЗВ(МАКС((B2=’Отчет сентябрь’!$A$2:$A$67)*(B3=’Отчет сентябрь’!$B$2:$B$67)*(СТРОКА($B$2:$B$67)-1))))
=INDEX(‘Отчет сентябрь’!$D$2:$D$67,SUMPRODUCT(MAX((B2=’Отчет сентябрь’!$A$2:$A$67)*(B3=’Отчет сентябрь’!$B$2:$B$67)*(ROW($B$2:$B$67)-1))))
Здесь вместо ПОИСКПОЗ роль поиска номера строки в массиве ‘Отчет сентябрь’!$D$2:$D$67 играет СУММПРОИЗВ. Сам принцип работы СУММПРОИЗВ я описывал в этой статье — Функция СУММЕСЛИ, а так же СУММЕСЛИ по двум критериям. К этому могу добавить лишь некоторые разъяснения(хотя если хотите понять сам принцип — лучше потратить пару минут на статью).
(B2=’Отчет сентябрь’!$A$2:$A$67) — здесь значение выбранного салона сверяется со списком салонов на листе Отчет сентябрь. Где значения равны получаем значение ИСТИНА(TRUE), где отличаются — ЛОЖЬ(FALSE).
(B3=’Отчет сентябрь’!$B$2:$B$67) — здесь значение выбранного бренда сверяется со списком брендов на листе Отчет сентябрь. Где значения равны получаем значение ИСТИНА(TRUE), где отличаются — ЛОЖЬ(FALSE).
В результате перемножения этих двух выражений мы получим массив 1 и 0(1 будет там, где бренд и салон совпали, 0 — где различаются), т.к. ИСТИНА для Excel по сути =1, а ЛОЖЬ =0.
Далее полученный массив единиц и нулей перемножаем на выражение: СТРОКА($B$2:$B$67). Это выражение дает нам массив номеров строк {2:3:4:5:6:7:8 … и т.д.}т.к. в выражении берется номер строки на листе, а нам нужен номер в диапазоне ‘Отчет сентябрь’!$D$2:$D$67, то мы еще вычитаем 1(т.к. диапазон у нас начинается со второй строки). С таким же успехом можно было не вычитать, а либо все диапазоны взять с первой строки, либо указать так: СТРОКА($B$1:$B$66)
Полученный массив строк перемножаются на массив единиц и нулей салона и бренда. В результате получим номер строки, умноженный на 1 там, где салон и бренд равны искомым, и на ноль там, где отличаются. И в итоге массив номеров строки и нулей. И из этого отбирается максимальный номер строки.
По сути, это уже решает проблему поиска, но если значений, подходящих под условия больше одного, то именно такой результат может быть(даже скорее всего будет) неверным. Чтобы этого избежать мы применяем функцию МАКС(MAX), чтобы в результате из всех строк была отобрана только максимальная. Т.е. в результате мы получим не первое совпадение из всех возможных, а последнее.
Так же рекомендую ознакомиться со статьей Как просмотреть этапы вычисления формул, чтобы можно было самостоятельно поразбираться в принципах работы формул.

В сети можно найти так же решения при помощи макросов(VBA), но далеко не всегда есть возможность использовать макросы в файлах.

Также см.:
Как найти значение в другой таблице или сила ВПР
ВПР с возвратом всех значений
ВПР с поиском по нескольким листам
ВПР_МН
ВПР_ВСЕ_КНИГИ
Функция СУММЕСЛИ, а так же СУММЕСЛИ по двум критериям

Статья помогла? Поделись ссылкой с друзьями!Видеоуроки

Поиск по меткам

Access apple watch Multex Outlook Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Надстройки Настройка Поиск данных Политика Конфиденциальности Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx анализ данных баги и глюки в Excel

Синтаксис и описание функции ВПР в 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 примеры

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

Функция ВПР на разных листах

На практике формулы ВПР редко используются для поиска данных на одном листе. Чаще всего вам придется искать и вытаскивать соответствующие данные с другого листа.

Чтобы использовать функцию ВПР с другого листа Excel, вы должны ввести имя рабочего листа и восклицательный знак в аргументе таблица перед диапазоном ячеек, например, =ВПР(40;Лист2!A2:B15;2). Формула указывает, что диапазон поиска A2:B15 находится в Лист2.

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

Формула, которую вы видите на изображении ниже, ищет текст в ячейке А2 («Продукт 3») в столбце A (1-й столбец диапазона поиска A2:B9) на листе «Цены»:

=ВПР(A2;Цены!$A$2:$B$8;2;ЛОЖЬ)

Функция ВПР в Excel – Функция ВПР на разных листах

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

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

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

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

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

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

=ВПР(«Продукт 1»;Продукты;2)

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

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

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

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

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

=ВПР(«Продукт 1»;Таблица6:];2)

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

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

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

Рассмотрим пример функции ВПР с несколькими условиями. У нас есть следующие исходные данные:

Функция ВПР в Excel – Таблица исходных данных

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

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

Итак на листе «Цены» вставляем столбец и в ячейке А2 вводим следующую формулу:

=B2&C2

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

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

Функция ВПР в Excel – Добавление вспомогательного столбца

1. Теперь в ячейке С2 на листе «Продажи» напишем следующую формулу ВПР:

=ВПР(A2&B2;Цены!$A$1:$D$8;4;ЛОЖЬ)

Заполняем для остальных ячеек и в результате получаем цены для каждого продукта в соответствии с типом:

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

Теперь разберем ошибки функции ВПР.

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

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

Тип ошибки	Причина	Решение
#Н/Д	Неверное расположение столбца, по которому происходит поиск	Столбец таблицы, по которому происходит поиск ОБЯЗАТЕЛЬНО должен быть крайним левым. Перенесите столбец, по которому происходит поиск в крайнее левое положение таблицы. Или создайте вспомогательный дублирующий столбец, слева в таблице.
#Н/Д	Не закреплен диапазон таблицы	Если первое значение было выведено правильно, а после протягивания формулы ВПР в некоторых ячейках встречается ошибка #Н/Д, то диапазон таблицы не закреплен. Используйте ($) для закрепления диапазона таблицы, чтобы при заполнении формула использовала один и тот же диапазон. Или используйте именованный диапазон
#Н/Д	Не удалось найти точное совпадение (если в интервальном просмотре выбран поиск точного значения (0)	–
#Н/Д	В интервальном просмотре выполняется поиск ближайшего значения (1), а таблица, по которой происходит поиск не отсортирована.	Отсортируйте первый столбец таблицы по возрастанию наименований.
#Н/Д	Данные содержат лишние пробелы, недопустимые кавычки или непечатаемые символы.	Используйте функции ПЕЧСИМВ или СЖПРОБЕЛЫ.
#ССЫЛКА!	Значение номер столбца превышает число столбцов в таблице	Проверьте номер столбца, содержащий возвращаемое значение.
#ИМЯ?	В формуле пропущены кавычки	Если вы используете в качестве искомого значения не ссылку на ячейку, а текст, то его необходимо заключить в кавычки. Например: =ВПР(«Продукт 1»; Цены!$A$2:$B$8;2;0)

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