- Задача и её решение при помощи ВПР
- Описание аргументов ВПР
- Что важно всегда помнить при работе с ВПР
- Как избежать ошибки #Н/Д(#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 077 скачиваний)
Так же см.:
ВПР и интервальный просмотр(range_lookup)
ВПР по двум и более критериям
ВПР с возвратом всех значений
ВПР с поиском по нескольким листам
ВПР_МН
ВПР_ВСЕ_КНИГИ
Как заменить/удалить/найти звездочку?
Андрей, файл скачать не пробовали? И прочитать статью? Там есть такая строка: "Здесь я в формулах использовал одинаковые диапазоны для удобочитаемости, но в примере для скачивания они различаются от указанных здесь."
Я в статье описываю лишь принцип работы формулы, для чего использую одинаковые диапазоны, начиная с А2(т.к. А1 заголовок таблицы и предполагается, что таблицы записаны начиная с А1) - для всех это более логично, чем С5.
Добрый день, очень помогла статья. Использовала поиспоз, вставились нужные цифры, правда целые, а нужны с 2 цифрами после запятой, помогите)
Правая кнопка мыши на ячейках-Формат ячеек-вкладка Число. Установите нужный формат.
Добрый день! Раньше при сверке расчетов пользовалась формулой для сравнения №накл, и суммы, которая выдавала совпадения или отрицание по суммам расхождения. После декрета забыла эту форму, если не сложно помогите...
Ирина, как Вы представляете помощь? Здесь не проводятся курсы по восстановлению памяти. Что Вы применяли и как чего сравнивали никто кроме Вас не знает.
Обратитесь в форум с более детальным описанием задачи - тогда если уж не вспомните, то направление для решения задачи Вам дадут.
Функций для сравнения в зависимости от задач море. Среди них и ВПР(описанная выше), и СУММЕСЛИ, и СОВПАД, и простое равенство и ПОИСКПОЗ, и ПСТР, и СЧЁТЕСЛИ и много какие еще.
Добрый день. У меня есть 3 одинаковых таблицы в разных книгах с одинаковыми значениями в столбцах и строках. Последний столбик в таблицах имеет разное значение(тип строка), с условием что если в Книге1 ячейка J2 заполнена значением, то в Книге2 и Книге3 эта ячейка будет пуста. Как объединить значения со всех книг с столбца J в новую книгу4 в столбик J?
При указании формулы:
=ВПР(A30;'[Соответствие кодов.xlsx]Лист1'!$A$1:$A$75;8;0)
результат выдает #ССЫЛКА!
В чем может быть причина? формат ячеек одинаков. В другой книге подобная формула работает нормально.
Обратите внимание на аргумент таблица, для которого Вы указали один столбец($A$1:$A$75). А возврат значений указываете в 8-ом. Т.е. по сути надо было бы скорее так: $A$1:$H$75
Спасибо! Моя ошибка, по не внимательности.
Дмитрий, помогите разрешить проблему пожалуйста
описание проблемы: у меня есть база (Т1)уда стекается вся информация, из нее данные по падают в другую таблицу (Т2) (которая считает все что в Т1), из Т2 с помощюь формул ИНДЕКС(...ПОИСКПОЗ) попадает в Т3 , но есть загвоздка, у меня есть 2 одинаковых имени, которые изменить я не могу, но они относятся к разным групам например Група_4 и Група_11, из за того что имя одинаковое естественно машина на одном из значений пишет Н\Д....
в Т2 используется в основном формулы СЧЕТЕСЛИМН и СУММЕСЛИМН
Нашла на просторах инета, что можно использовать формулу которой ранее пользовалась ИНДЕКС(...ПОИСКПОЗ) с применение значка & (поиск по двум критериям) попробовала ее прописать пишет ошибку-значен, проверила формулу с помощью функции _ Вычислить формулу, и увидела то, что ексель при поиске обращается имеено к тем номерам строк из Т2, на которых в данный момент стоит формулав Т3. Возможно несколько сумбурно описала, но ооочень надо. Заранее спасибо
Дмитрий, спасибо огромное! Вы создали очень полезный сайт.
Прошу помочь, имеется базовая таблица с ФИО и различными показателями в строке, мне необходимо по заданному неуникальному критерию (показателю) из одного столбца, создавать список из уникальных значений (ФИО), находящихся в другом столбце. Я использовал Вашу формулу:
=ИНДЕКС(Лист1!$C$2:$E$499;ПОИСКПОЗ($B8;Лист1!$C$2:$C$499;0);3)
К сожалению, в требуемом списке появляется только первое найденное значение, как выдать в список все значения.
Может быть это связано с тем, что критерий (показатель) "$B8" выбирается из списка со значением "=ДВССЫЛ(A8)"?
Как решить задачу?
Спасибо Вам за статью! Очень помогла формула с ВПР.
Доброго дня! Подскажите пожалуйста проблема такая. Я выгружаю кампанию из яндекс директ в которой есть url адреса со специальной разметкой. Длина ссылки более 400 символов. После этого из выгружаемого файла копирую url, вставляю в программу и она проверяет эти ссылки на ответ сервера, ответов несколько 200, 404, 301 . Меня интересуют ссылки с ответом 404 чтобы их отключить. В итоге проверки я полусчаю файл с где есть столбец с url и столбец с ответами сервера. Мне желательно в первом файле где содержатся все данные о рекламной кампании добавить столбец и подставить в него ответ сервера. Из за того что ссылки по 400 символов у меня не получается, прочитал все, смотрел пример но не могу никак понять как это применить к моему случаю, можете ли вы помочь в этом ?
Андрей, я Ваших данных не видел, но в статье есть даже специальный блок: РАБОТА С КРИТЕРИЯМИ ДЛИННЕЕ 255 СИМВОЛОВ. Вы пробовали применить то, что там написано? В частности там есть формула:=ИНДЕКС(Лист1!$A$2:$C$4;СУММПРОИЗВ(ПОИСКПОЗ(ИСТИНА;Лист1!$A$2:$A$4=$A2;0));2)
Она прекрасно работает со значениями, которые длиннее 255 символов, в том числе и 400.
Мне не понятно как правильно ввести ф-ю VLOOKUP, чтобы выводилось все цифры в столбце где фигурирует "CRH-DT01-01*". Я ввела формулу ввела, как указано выше,тяну вниз, а он только одно значение находит, а как же другие значение. У меня массив CRH-DT01-01.01, CRH-DT01-01.02, CRH-DT01-01.03, CRH-DT01-01.04. и у всех разные данные.
Айна, никак. ВПР умеет возвращать только одно первое найденное значение. Для поиска и возврата всех надо "шаманить". Я описывал это в другой статье:ВПР с возвратом всех значений
Дмитрий, добрый день!
Прошу Вас помочь решить одну проблему. Есть массив данных в котором в одной из ячеек присутствует кат.номер состоящий из определенного набора цифр и букв. Мне необходимо найти из другого файла имеющего более больший объем информации в котором в искомой ячейке содержится текст к примеру "лампа накаливания (и указан кат.номер на который мне необходимо ссылаться из первоначального массива)" и в соседней ячейке цена. Вот мне необходимо найти цену которая будет находится напротив ячейки из большого массива содержащая текст с моим кат.номером. Очень прошу Вас помочь. За ранее благодарю.
Судя по описанию хватит ВПР с подстановочным символом *(звездочка) к части текста. Больше нечего сказать без примера. Обратитесь нафорум , там можно создать тему с описанием проблемы и приложить пример данных.