- Задача и её решение при помощи ВПР
- Описание аргументов ВПР
- Что важно всегда помнить при работе с ВПР
- Как избежать ошибки #Н/Д(#N/A) в ВПР?
- Как при помощи ВПР искать значение по строке, а не столбцу?
- Решение при помощи ПОИСКПОЗ
- Работа с критериями длиннее 255 символов
Если в двух словах, то ВПР позволяет сравнить данные двух таблиц на основании значений из одного столбца.
Чтобы чуть лучше понять принцип работы ВПР лучше начать с некоего практического примера. Возьмем две таблицы:
На картинке выше для удобства они показаны рядом, но на самом деле могут быть расположены на разных листах и даже в разных книгах. Таблицы по сути одинаковые, но фамилии в них расположены в разном порядке, и к тому же в одной заполнены все столбцы, а во второй столбцы ФИО и Отдел. И из первой таблицы необходимо подставить во вторую дату для каждой фамилии. Для трех записей это не проблема и руками сделать - все очевидно. Но в жизни это таблицы на тысячи записей и поиск с подстановкой данных вручную может занять не один час. Вот где ВПР
Записать формулу можно либо непосредственно в ячейку, либо воспользовавшись диспетчером функций, выбрав в категории Ссылки и массивы
Теперь разберем поподробнее саму функцию, её аргументы и некоторые особенности.
ВПР ищет заданное нами значение(аргумент
ВПР может вернуть только одно значений - первое, подходящее под критерий. Если искомое значение не найдено(отсутствует в таблице), то результатом функции будет ошибка
Искомое_значение ( ) - это то значение из одной таблицы, которые мы ищем в другой таблице. Т.е. для первой записи второй таблицы это будет$A2 . Здесь можно указать либо непосредственно текст критерия(в этом случае он должен быть в кавычках -Петров С.А. =ВПР( , либо ссылку на ячейку, с данным текстом(как в примере функции). Есть небольшой нюанс: так же можно применять символы подстановки:"Петров С.А" ;Лист1!$A$1:$C$4;3;0)"*" и"?" . Это очень удобно, если необходимо найти значения лишь по части строки. Например, можно не вводить полностью "Петров С.А", а ввести лишь фамилию и знак звездочки - "Петров*". Тогда будет выведена любая запись, которая начинается на "Петров". Если же надо найти запись, в которой в любом месте строки встречается фамилия"Петров" , то можно указать так:"*петров*" . Если хотите найти фамилиюПетров и неважно какие инициалы будут у имени-отчества(если ФИО записаны в виде Иванов И.И.), то здесь в самый раз такой вид:"Иванов ?.?." .
Часто необходимо для каждой строки указать свое значение(в столбце А Фамилии и надо их все найти). В таком случае всегда указываются ссылки на ячейки столбца А. Например, в ячейке записано:A2 Иванов . Так же известно, чтоИванов есть в другой таблице, но после фамилии могут быть записаны и имя и отчество(или еще что-то). Но нам нужно найти только строку, которая начинается на фамилию. Тогда необходимо записать следующим образом: . Эта запись будет равнозначнаA2 &"*""Иванов*" . В записаноA2 Иванов , амперсанд(& ) используется для объединения в одну строку двух текстовых значений. Звездочка в кавычках (как и положено быть тексту внутри формулы). Таким образом и получаем:
A2&"*" =>
"Иванов"&"*"=>
"Иванов*"
А полная формула в итоге будет выглядеть так:=ВПР( A2&"*" ;Лист1!$A$1:$C$4 ;3 ;0)
Очень удобно, если значений для поиска много.
Если надо определить есть ли хоть где-то слово в строке, то звездочки ставим с обеих сторон:"*"& A1 &"*"Таблица( - указывается диапазон ячеек, в первом столбце которых будет просматриваться аргументЛист1!$A$1:$C$4 )Искомое_значение . Диапазон должен содержать данные от первой ячейки с данными до самой последней. Это не обязательно должен быть указанный в примере диапазон. Если строк 100, то . Диапазон в аргументеЛист1!$A$2:$C$100 таблица всегда должен быть "закреплен" , т.е. содержать знаки доллара( ) перед названием столбцов и перед номерами строк($ Лист1! ).$ A$ 1:$ C$ 4Номер_столбца(3) - указывается номер столбца в аргументеТаблица , значения из которого нам необходимо записать в итоговую ячейку в качестве результата. В примере это Дата принятия - т.е. столбец №3. Если бы нужен был отдел, то необходимо было бы указать номер столбца 2, а если бы нам понадобилось просто сравнить есть ли фамилии одной таблицы в другой, то можно было бы указать и 1. Номер столбца всегда указывается числом и не должен быть больше числа столбцов в аргументеТаблица .
если аргумент Таблица имеет слишком большое кол-во столбцов и необходимо вернуть результат из последнего столбца, то совсем необязательно высчитывать их количество. Можно использовать формулу, которая подсчитывает количество столбцов в указанном диапазоне:=ВПР( . К слову в данном случае Лист1! тоже можно убрать, т.к. функция ЧИСЛОСТОЛБ просто подсчитывает количество столбцов в переданном ей диапазоне и неважно на каком он листе:$A2 ;Лист1!$A$1:$C$4 ;ЧИСЛСТОЛБ(Лист1!$A$1:$C$4 );0)=ВПР( .$A2 ;Лист1!$A$1:$C$4 ;ЧИСЛСТОЛБ($A$1:$C$4 );0)Интервальный_просмотр (0) - очень интересный аргумент. Может быть равен либо ИСТИНА либо ЛОЖЬ. Так же допускается указать 1 или 0. 1 = ИСТИНА, 0 = ЛОЖЬ. Если в ВПР указать данный параметр равный 0 или ЛОЖЬ, то будет происходить поиск точного соответствия заданномуИскомому_значению . Это не имеет никакого отношения к знакам подстановки("*" и "?"). Если же использовать 1 или ИСТИНА, то...Совсем в двух словах не объяснить. Если вкратце - ВПР будет искать наиболее похожее значение, подходящее подИскомомое_значение . Иногда очень полезно. Правда, если использовать данный параметр, то необходимо, чтобы список в аргументеТаблица был отсортирован по возрастанию. Обращаю внимание на то, что сортировка необходима только в том случае, если аргументИнтервальный_просмотр равен ИСТИНА или 1. Если же 0 или ЛОЖЬ - сортировка не нужна. Этот аргумент необходимо использовать осторожно - не стоит указывать 1 или ИСТИНА, если нужно найти точное соответствие и уж тем более не стоит использовать, если не понимаете принцип его работы.
Подробнее про работу ВПР с интервальным просмотром, равным 1 или ИСТИНА можно ознакомиться в статье ВПР и интервальный просмотр(range_lookup)
 
Таблица всегда должна начинаться с того столбца, в котором ищем Искомое_значение . Т.е. ВПР не умеет искать значение во втором столбце таблицы, а значение возвращать из первого. В лучшем случае ничего найдено не будет и получим ошибку#Н/Д , а в худшем результат будет совсем не тот, который должен быть(#N/A) - аргумент
Таблица должен быть "закреплен" , т.е. содержать знаки доллара( ) перед названием столбцов и перед номерами строк($ Лист1! ). Это и есть закрепление(если точнее, то это называется абсолютной ссылкой на диапазон). Как это делается. Выделяете текст ссылки и жмете клавишу$ A$ 1:$ C$ 4F4 до тех пор, пока не увидите, что и перед обозначением имени столбца и перед номером строки не появились доллары. Если этого не сделать, то при копировании формулы из одной ячейки в остальные аргументТаблица будет "съезжать" и результат может быть совсем не таким, какой ожидался(в лучшем случае получите ошибку#Н/Д (#N/A) номер_столбца не должен превышать общее кол-во столбцов в аргументетаблица , а самаТаблица соответственно должна содержать столбцы от первого(в котором ищем) до последнего(из которого необходимо возвращать значения). В примере указана - всего 3 столбца(A, B, C). Значит не получится вернуть значение из столбца D(4), т.к. в таблице только три столбца. Т.е. если мы запишем формулу так:Лист1!$A$1:$C$4 =ВПР( - мы получим ошибку$A2 ;Лист1!$A$1:$C$4 ;4 ;0)#ССЫЛКА! .(#REF!)
Если аргументомТаблица указан диапазон и необходимо вернуть данные из столбца С, то правильно будет указать$B$1:$C$4 номер столбца 2. Т.к. аргументТаблица ( ) содержит только два столбца - В и С. Если же попытаться указать номер столбца 3(каким по счету он является на листе), то получим ошибку$B$1:$C$4 #ССЫЛКА! , т.к. третьего столбца в указанном диапазоне просто нет.(#REF!)
Многие наверняка заметили, что на картинке у меня попутаны отделы для ФИО(в обеих таблицах ФИО относятся к разным отделам). Это не ошибка записи. В прилагаемом к статье примере показано, как можно одной формулой подставить и отделы и даты, не меняя вручную аргумент Номер_столбца:
=ВПР( . Такой подход сработает, если в обеих таблицах одинаковый порядок столбцов.$A2 ;Лист1!$A$1:$C$4 ;СТОЛБЕЦ();0)
Еще частая проблема - многие не хотят видеть #Н/Д результатом, если совпадение не найдено. Это можно обойти при помощи специальных функций.
Для пользователей Excel 2003 и старше:
Теперь если ВПР не найдет совпадения, то ячейка будет пустой.
А пользователям версий Excel 2007 и выше будет удобнее использовать функцию
Подробнее про различие между использованием
Но я бы не рекомендовал использовать
- искомое значение состоит более чем из 255 символов(решение этой проблемы приведено ниже в этой статье: Работа с критериями длиннее 255 символов)
- искомое значение является числом с большим кол-вом знаков после запятой. Excel не может правильно воспринимать такие числа и в итоге ВПР может вернуть ошибку. Правильным решением здесь будет округлить искомое значение хотя бы до 4-х или 5-ти знаков после запятой(конечно, если это допустимо):
=ВПР(ОКРУГЛ( $A2 ;5);Лист1!$A$1:$C$4 ;3;0)
=VLOOKUP(ROUND($A2,2),Лист1!$A$1:$C$4,3,0) - искомое значение содержит специальные или непечатаемые символы.
В этом случае придется либо избавиться от непечатаемых символов в искомом аргументе:
=ВПР(ПЕЧСИМВ( $A2 );Лист1!$A$1:$C$4 ;3;0)
=VLOOKUP(CLEAN($A2),Лист1!$A$1:$C$4,3,0)
либо добавить перед всеми специальными символами(такими как звездочка или вопр.знак) знак тильды(~), чтобы сделать эти знаки просто знаками, а не знаками специального значения(так же работа со специальными(служебными) символами описывалась в статье: Как заменить/удалить/найти звездочку). Добавить символ перед знаком той же тильды можно при помощи функцииПОДСТАВИТЬ :(SUBSTITUTE)
=ВПР(ПОДСТАВИТЬ( $A2 ;"~";"~~");Лист1!$A$1:$C$4 ;3;0)
=VLOOKUP(SUBSTITUTE(A2,"~","~~"),Лист1!$A$1:$C$4,3,0)
Если необходимо добавить тильду сразу перед несколькими знаками, то делает это обычно так(на примере подстановки одновременно для тильды и звездочки):
=ВПР(ПОДСТАВИТЬ(ПОДСТАВИТЬ( $A2 ;"~";"~~");"*";"~*");Лист1!$A$1:$C$4 ;3;0)
=VLOOKUP(SUBSTITUTE(SUBSTITUTE(A2,"~","~~"),"*","~*"),Лист1!$A$1:$C$4,3,0)
На самом деле ответ будет коротким - ВПР всегда ищет сверху вниз. Слева направо она не умеет. Но зато слева направо умеет искать её сестра ГПР(HLookup) - Горизонтальный
ГПР ищет заданное значение(аргумент
Если надо найти значение "Иванов" в строке 2 и вернуть значение из строки 5 в таблице
Все правила и синтаксис функции точно такие же, как у ВПР:
-в искомом значении можно применять символы астерикса(*) и вопр.знака(?) - "Иванов*";
-таблица должна быть закреплена -
-интервальный просмотр работает по тому же принципу(0 или ЛОЖЬ точный просмотр слева-направо, 1 или ИСТИНА - интервальный).
Общий принцип работы
Искомое_значение( - непосредственно значение или ссылка на ячейку с искомым значением. Если опираться на пример выше - то это ФИО. Здесь все ровно так же, как и с ВПР. Так же допустимы символы подстановки$A2 )* и? и ровно в таком же исполнении.Просматриваемый_массив( - указывается ссылка на столбец, в котором необходимо найти искомое значение. В отличии от той же ВПР, где указывается целая таблица, это должен быть именно один столбец, в котором мы собираемся искатьЛист1!$A$1:$A$4 )Искомое_значение . Если попытаться указать более одного столбца, то функция вернет ошибку.Справедливости ради надо отметить, что можно указать либо столбец, либо строку Тип_сопоставления(0) - то же самое, что иИнтервальный_просмотр в ВПР. С теми же особенностями. Отличается разве что возможностью поиска наименьшего от искомого или наибольшего.
С основным разобрались. Но ведь нам надо вернуть не номер позиции, а само значение. Значит ПОИСКПОЗ в чистом виде нам не подходит. По крайней мере одна, сама по себе. Но если её использовать вместе с функцией
Такая формула результатом вернет то же, что и ВПР.
Далее идут
Так же как и в случае с ВПР, ИНДЕКС в случае не нахождения искомого значения возвращает #Н/Д. И обойти подобные ошибки можно так же:
Есть у ИНДЕКС-ПОИСКПОЗ и еще одно преимущество перед ВПР. Дело в том, что ВПР не может искать значения, длина строки которых содержит более 255 символов. Это случается редко, но случается. Можно, конечно, обмануть ВПР и урезать критерий:
но это формула массива. Да и к тому же далеко не всегда такая формула вернет нужный результат. Если первые 255 символов идентичны первым 255 символам в таблице, а дальше знаки различаются - формула этого уже не увидит. Да и возвращает формула исключительно текстовые значения, что в случаях, когда возвращаться должны числа, не очень удобно.
Поэтому лучше использовать такую хитрую формулу:
Здесь я в формулах использовал одинаковые диапазоны для удобочитаемости, но в примере для скачивания они различаются от указанных здесь.
Ну и все же я рекомендовал бы Вам прочитать подробнее про данные функции в справке.
В прилагаемом к статье примере Вы найдете примеры использования всех описанных случаев и пример того, почему ИНДЕКС и ПОИСКПОЗ порой предпочтительнее ВПР.
Tips_All_VLookUp.xls (26,0 КиБ, 18 047 скачиваний)
Так же см.:
ВПР и интервальный просмотр(range_lookup)
ВПР по двум и более критериям
ВПР с возвратом всех значений
ВПР с поиском по нескольким листам
ВПР_МН
ВПР_ВСЕ_КНИГИ
Как заменить/удалить/найти звездочку?
Иван, возможно надо было обратиться в форум, раз вопросов конкретно по данной статье нет. Но может подойдет другая статья:Как сцепить несколько значений в одну ячейку по критерию? СцепитьЕсли
Дмитрий! Спасибо. Все получилось!!!!
@Ольга
Добрый день! А возможно ли применить функцию ВПР таким образом, чтобы только часть "искомого значения" (часть текста в ячейке) была определена как соответствующая значению в "таблице" и, таким образом найдя соответствие, возвращало значение, равное другому столбцу в таблице. Лучше на примере. Искомое значение "1185 Альфа М". Нужно, чтобы рядом со всеми ячейками, содержащими "альфа" возвращалось значение Коллекция Альфа. Искомое значение "Сигма 4893 м-1". Нужно возвратить значение - М-1 Коллекция Сигма. Если бы коллекций было немного, то можно было бы воспользоваться ЕСЛИ (=ЕСЛИ(ЕЧИСЛО(ПОИСК....). Однако возможных коллекций около 150, а искомых значений - около 70 000. Может, подскажете? Спасибо:)
Евгения, а подстановочные символы типа звездочка и вопросительный знак, применение которых описано в статье, не подходит?
была задача подставить количество соответствующее артикулу товара из 1-й таблицы в другую.
формула получилась: =ВПР(RC[-4];'[2.xls]TDSheet'!R5C2:R77C2;'[2.xls]TDSheet'!C5;0)
но в итоге выводится только Н/Д и либо #ЗНАЧ!, либо #ССЫЛКА!
количество товара так и не удалось перенести
Сергей, что Вы хотели этим сказать? Где-то Вы неверно задаете параметр(и получаете #ЗНАЧ!, либо #ССЫЛКА!) или данные действительно не совпадают и получается НД.
Вы всего один столбец указываете в качестве таблицы(второй аргумент), а значит третий аргумент можно стопроцентно ставить 1 и не городить огород со ссылкой на ячейку. Ссылку можно ставить в случае, если из разных столбцов таблицы надо занчения вытянуть. А у Вас только один столбец. Указав третьим аргументом значение больше 1 получите #ССЫЛКА! без вариантов.
Дмитрий, та же проблема, что и у Евгении.
Большой объем данных, нужно найти совпадения и присвоить номера из одной табл в другую.
Проблема в том, что с одной табл ячейка типа "NLL AGENDA IKJH", а в другой
" AGENDA BR HDNSJILM" (собственно аналогично, как у Евгении)
Как написать формулу,чтобы искать одинаковые слова одной таблицы в другой? формула для всей таблицы?
спасибо.
Добрый день! В ячейках 1,2,3 (условно) использовала =ЕСЛИОШИБКА(ВПР($A2;Лист1!$A$2:$C$4;3;0);"").
Затем при сложении данных из этих ячеек - опять с помощью функции ВПР - получается # знач.
Как сделать так, чтобы получалось значение?
Чтобы получалось значение, надо не текст складывать:
=ЕСЛИОШИБКА(ВПР($A2;Лист1!$A$2:$C$4;3;0);0)
Ну посмотрите, что ВПР возвращает: точно число или текст. Если текст - воспользуйтесь функцией Ч:
=Ч(ЕСЛИОШИБКА(ВПР($A2;Лист1!$A$2:$C$4;3;0);0))
Извините,но написано непонятно .Вначале рисунки без номеров строк и столбцов,потом вырисовывается $A2 . Почему не $С5 ?