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

Как найти значение в другой таблице или сила ВПР

На самом деле я в данной статье хочу рассказать про возможности не только функции ВПР , но так же хочу затронуть и ПОИСКПОЗ , как очень родственную с ВПР функцию. У каждой из данных функций есть как свои плюсы, так и минусы. Если в двух словах, то ВПР ищет некое указанное нами значение среди множества значений, расположенных в одном столбце. Пожалуй наиболее часто необходимость в ВПР возникает когда надо сравнить данные, найти данные в другой таблице, из одной таблицы добавить данные в другую, опираясь на какой-либо критерий и т.д.
Чтобы чуть лучше понять принцип работы ВПР лучше начать с некоего практического примера. Имеется таблица такого вида:
рис.1

и из первой таблицы необходимо подставить во вторую дату для каждой фамилии. Для трех записей это не проблема и руками сделать - все очевидно. Но в жизни это таблицы на тысячи записей и поиск с подстановкой данных вручную может занять не один час. Плюс еще пара ложек дегтя: мало того, что ФИО расположены совершенно в разном порядке в обеих таблицах и количество записей в таблицах разное, так еще таблицы расположены на разных листах/книгах. Я полагаю, что убедил вас в том, что подстановка данных руками вообще не вариант. Зато ВПР (VLOOKUP) здесь будет незаменима. При этом практически ничего не надо будет делать - только записать в первую ячейку столбца С второй таблицы(туда, куда необходимо подставить даты из первой таблицы) такую формулу:
=ВПР($A2 ;Лист1!$A$2:$C$4 ;3;0)
Записать формулу можно либо непосредственно в ячейку, либо воспользовавшись диспетчером функций , выбрав в категории Ссылки и массивы ВПР и по отдельности указав нужные критерии. Теперь копируем(Ctrl +C ) ячейку с формулой, выделяем все ячейки столбца С до конца данных и вставляем(Ctrl +V ).

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

Описание аргументов ВПР
$A2 - аргумент Искомое_значение (назовем его Критерий для краткости). Это то, что мы ищем. Т.е. для первой записи второй таблицы это будет Петров С.А. Здесь можно указать либо непосредственно текст критерия(в этом случае он должен быть в кавычках - =ВПР("Петров С.А" ;Лист1!$A$2:$C$4;3;0) , либо ссылку на ячейку, с данным текстом(как в примере функции) . Есть небольшой нюанс: так же можно применять символы подстановки: "*" и "?". Это очень удобно, если Вам надо найти значения лишь по части строки. Например, Вы можете не вводить полностью "Петров С.А", а ввести лишь фамилию и знак звездочки - "Петров*". Тогда будет выведена любая запись, которая начинается на "Петров". Если же Вам надо найти запись, в которой в любом месте строки встречается фамилия "Петров" , то можно указать так: "*петров*" . Если хотите найти фамилию Петров и неважно какие инициалы будут у имени-отчества(если ФИО записаны в виде Иванов И.И.), то здесь в самый раз такой вид: "Иванов?.?." . Часто необходимо для каждой строки указать свое значение(в столбце А Фамилии и надо их все найти). В таком случае всегда указываются ссылки на ячейки столбца А. Например, в ячейке A1 записано: Иванов. Так же известно, что Иванов есть в другой таблице, но после фамилии могут быть записаны и имя и отчество(или еще что-то). Но нам нужно найти только строку, которая начинается на фамилию. Тогда необходимо записать следующим образом: A1 &"*" . Эта запись будет равнозначна "Иванов*" . В A1 записано Иванов, амперсанд(&) используется для объединения в одну строку двух текстовых значений. Звездочка в кавычках (как и положено быть тексту внутри формулы). Таким образом и получаем:
A1&"*" =>
"Иванов"&"*" =>
"Иванов*"
Очень удобно, если значений для поиска много.
Если надо определить есть ли хоть где-то слово в строке, то звездочки ставим с обеих сторон: "*"& A1 &"*"

Лист1!$A$2:$C$4 - аргумент Таблица . Указывается диапазон ячеек. Только диапазон должен содержать данные от первой ячейки с данными до самой последней. Это не обязательно должен быть указанный в примере диапазон. Если строк 100, то Лист1!$A$2:$C$100 . Важно помнить три вещи: первое, это Таблица всегда должна начинаться с того столбца, в котором ищем Критерий . И никак иначе. В противном случае ничего найдено не будет или результат будет совсем не тот, которого ожидаете. Второе: аргумент Таблица должен быть "закреплен" . Что это значит. Видите знаки доллара - $? Это и есть закрепление(если точнее, то это называется абсолютной ссылкой на диапазон) . Как это делается. Выделяете текст ссылки(только один диапазон - один критерий) и жмете F4 до тех пор, пока не увидите, что и перед обозначением имени столбца и перед номером строки не появились доллары. Если этого не сделать, то при копировании формулы аргумент Таблица будет "съезжать" и результат опять-таки будет неверным. И последнее - таблица должна содержать столбцы от первого(в котором ищем) до последнего(из которого необходимо возвращать значения). В примере Лист1!$A$2:$C$4 - значит не получится вернуть значение из столбца D(4), т.к. в таблице только три столбца.

3 - Номер_столбца . Здесь просто указываем номер столбца в аргументе Таблица , значения из которого нам необходимо подставить в качестве результата. В примере это Дата принятия - т.е. столбец №3. Если бы нужен был отдел, то указали бы 2, а если бы нам понадобилось просто сравнить есть ли фамилии одной таблицы в другой, то можно было бы указать и 1. Важно: аргумент Номер_столбца не должен превышать кол-во столбцов в аргументе Таблица . Иначе результатом формулы будет ошибка #ССЫЛКА! . Например, если в качестве указан диапазон $B$2:$C$4 и необходимо вернуть данные из столбца С, то правильно указать 2. Т.к. аргумент Таблица ($B$2:$C$4) содержит только два столбца - В и С. Если же попытаться указать номер столбца 3(каким по счету он является на листе), то получим ошибку #ССЫЛКА! , т.к. третьего столбца в указанном диапазоне просто нет.

Практический совет: если аргумент Таблица имеет слишком большое кол-во столбцов и Вам необходимо вернуть результат из последнего столбца, то совсем необязательно высчитывать их кол-во. Можно указать так: =ВПР($A2 ;Лист1! $A$2:$C$4 ;ЧИСЛСТОЛБ(Лист1! $A$2:$C$4);0) . К слову в данном случае Лист1! тоже можно убрать, как лишнее: =ВПР($A2 ;Лист1! $A$2:$C$4 ;ЧИСЛСТОЛБ($A$2:$C$4);0) .

0 - Интервальный_просмотр - очень интересный аргумент. Может быть равен либо ИСТИНА либо ЛОЖЬ. Сразу возникает вопрос: а почему в моей формуле там 0? Все очень просто - Excel в формулах может воспринимать 0 как ЛОЖЬ, а 1 как ИСТИНА. Если в ВПР указать данный параметр равный 0 или ЛОЖЬ, то будет происходить поиск точного соответствия заданному Критерию. Это не имеет никакого отношения к знакам подстановки("*" и "?"). Если же использовать 1 или ИСТИНА(или же вообще не указывать последний аргумент, т.к. по умолчанию он равен ИСТИНА), то...Очень долгая история. Вкратце - ВПР будет искать наиболее похожее значение, подходящее под Критерий . Иногда очень полезно. Правда, если использовать данный параметр, то необходимо, чтобы список в аргументе Таблица был отсортирован по возрастанию. Обращаю внимание на то, что сортировка необходима только в том случае, если аргумент Интервальный_просмотр у Вас равен ИСТИНА или 1. Если же 0 или ЛОЖЬ - сортировка не нужна.

Многие наверняка заметили, что на картинке у меня попутаны отделы для ФИО. Это не ошибка записи. В прилагаемом к статье примере показано, как можно одной формулой подставить и их и даты, не меняя вручную аргумент Номер_столбца . Мне показалось, что подобный пример вполне может пригодиться.

Как избежать ошибки #Н/Д(#N/A) в ВПР?
Еще частая проблема - многие не хотят видеть #Н/Д результатом, если совпадение не найдено. Это легко обойти:
=ЕСЛИ(ЕНД(ВПР($A2 ;Лист1! $A$2:$C$4 ;3;0));"";ВПР($A2 ;Лист1! $A$2:$C$4 ;3;0)))
Теперь если ВПР не найдет совпадения, то ячейка будет пустой.
А пользователям версий Excel 2007 и выше можно использовать ЕСЛИОШИБКА:
=ЕСЛИОШИБКА(ВПР($A2 ;Лист1! $A$2:$C$4 ;3;0);"")

Обещанная ПОИСКПОЗ

Данная функция ищет значение, указанное параметром Искомое_значение в аргументе Просматриваемый_массив . А результатом функции является номер позиции найденного значения в Просматриваемом_массиве . Именно номер позиции, а не само значение. В принципе её я не буду расписывать так же подробно, потому как основные моменты ровно такие же. Если бы мы хотели применить её для таблицы выше, то она была бы такой:
=ПОИСКПОЗ($A2 ;Лист1! $A$2:$A$4 ;0)
$A2 - Искомое_значение. Здесь все ровно так же, как и с ВПР. Так же допустимы символы подстановки и ровно в таком же исполнении.

Лист1! $A$2:$A$4 - Просматриваемый_массив. Основное отличие от ВПР - допускается указать массив лишь с одним столбцом. Это должен быть тот столбец, в котором мы собираемся искать Искомое_значение . Если попытаться указать более одного столбца, то функция вернет ошибку.

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

С основным разобрались. Но ведь нам надо вернуть не номер позиции, а само значение. Значит ПОИСКПОЗ в чистом виде нам не подходит. По крайней мере одна, сама по себе. Но если её использовать вместе с функцией ИНДЕКС - то это то, что нам нужно и даже больше.
=ИНДЕКС(Лист1! $A$2:$C$4 ;ПОИСКПОЗ($A2 ;Лист1! $A$2:$A$4 ;0);2)
Такая формула результатом вернет то же, что и ВПР.

Аргументы функции ИНДЕКС
Лист1! $A$2:$C$4 - Массив. В качестве этого аргумента мы указываем диапазон, из которого хотим получить значения. Может быть как один столбец, так и несколько. В случае, если столбец один, то последний аргумент функции указывать не надо. К слову - данный аргумент может совершенно не совпадать с тем, который мы указываем в аргументе Просматриваемый_массив функции ПОИСКПОЗ.

Далее идут Номер_строки и Номер_столбца. Именно в качестве Номера_строки мы и подставляем ПОИСКПОЗ, которая возвращает нам номер позиции в массиве. На этом все и строится. ИНДЕКС возвращает значение из Массива, которое находится в указанной строке(Номер_строки) Массива и указанном столбце(Номер_столбца), если столбцов более одного. Важно знать, что в данной связке кол-во строк в аргументе Массив функции ИНДЕКС и кол-во строк в аргументе Просматриваемый_массив функции ПОИСКПОЗ должно совпадать. И начинаться с одной и той же строки. Это в обычных случаях, если Вы не преследуете иные цели.
Так же как и в случае с ВПР, ИНДЕКС в случае не нахождения искомого значения возвращает #Н/Д. И обойти подобные ошибки можно так же:
Для всех версий Excel(включая 2003 и раньше):
=ЕСЛИ(ЕНД(ПОИСКПОЗ($A2 ;Лист1! $A$2:$A$4 ;0));"";ИНДЕКС(Лист1! $A$2:$C$4 ;ПОИСКПОЗ($A2 ;Лист1! $A$2:$A$4 ;0);2))
Для версий 2007 и выше:
=ЕСЛИОШИБКА(ИНДЕКС(Лист1! $A$2:$C$4 ;ПОИСКПОЗ($A2 ;Лист1! $A$2:$A$4 ;0);2);"")

Работа с критериями длиннее 255 символов
Есть у ИНДЕКС-ПОИСКПОЗ и еще одно преимущество перед ВПР. Дело в том, что ВПР не может искать значения, длина строки которых содержит более 255 символов . Это случается редко, но случается. Можно, конечно, обмануть ВПР и урезать критерий:
=ВПР(ПСТР($A2 ;1;255);ПСТР(Лист1!$A$2:$C$4 ;1;255);3;0)
но это формула массива . Да и к тому же далеко не всегда такая формула вернет нужный результат. Если первые 255 символов идентичны первым 255 символам в таблице, а дальше знаки различаются - формула этого уже не увидит. Да и возвращает формула исключительно текстовые значения, что в случаях, когда возвращаться должны числа, не очень удобно.

Поэтому лучше использовать такую хитрую формулу:
=ИНДЕКС(Лист1!$A$2:$C$4 ;СУММПРОИЗВ(ПОИСКПОЗ(ИСТИНА;Лист1!$A$2:$A$4 =$A2 ;0));2)
Здесь я в формулах использовал одинаковые диапазоны для удобочитаемости, но в примере для скачивания они различаются от указанных здесь.
Сама формула построена на возможности функции СУММПРОИЗВ преобразовывать в массивные вычисления некоторых функций внутри неё. В данном случае ПОИСКПОЗ ищет позицию строки, в которой критерий равен значению в строке. Подстановочные символы здесь применить уже не получится.

В прилагаемом к статье примере Вы найдете примеры использования всех описанных случаев и пример того, почему ИНДЕКС и ПОИСКПОЗ порой предпочтительнее ВПР.

Скачать пример

(26,0 KiB, 13 776 скачиваний)

{"Bottom bar":{"textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24,"textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance":30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500,"textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100%; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive":"","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40}}

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

1. Таблицы должны располагаться в одной книге Excel.
2. Искать можно только среди статических данных (не формул).
3. Условие поиска должно располагаться в первом столбце используемых данных.

Формула ВПР в Excel

Синтаксис ВПР в русифицированном Excel имеет вид:

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

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

Критерий поиска

Адрес ячейки листа Excel, в которой указываются данные для осуществления поиска в таблице.

Диапазон данных

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

Номер столбца для итогового значения

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

Условие для поиска

Логическое значение (истина/1 или ложь/0), которое указывает приблизительное совпадение искать (1) или точное (0).

ВПР в Excel: примеры функции

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

Найдем количество фактически выпущенной продукции по названию месяца.
Результат выведем справа от таблицы. В ячейке с адресом H3 будем вводить искомое значение. В примере здесь будет указываться название месяца.
В ячейке H4 введем саму функцию. Это можно делать вручную, а можно воспользоваться мастером. Для вызова поставьте указатель на ячейку H4 и нажмите значок Fx около строки формул.


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


Появится окно ВПР для таблицы Excel.


Чтобы указать первый аргумент (критерий), поставьте курсор в первую строку и щелкните по ячейке H3. Ее адрес появится в строке. Для выделения диапазона поставьте курсор во вторую строку и начните выделять мышью. Окно свернется до строки. Это делается для того, чтобы окно не мешало видеть Вам весь диапазон и не мешало выполнять действия.


Как только Вы закончите выделение и отпустите левую кнопку мыши, окно вернется в свое нормальное состояние, а во второй строке появится адрес диапазона. Он вычисляется от левой верхней ячейки до правой нижней. Их адреса разделены оператором «:» - берутся все адреса между первым и последним.


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


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


Результат обескураживает. «Н/Д» означает некорректные данные для функции. Мы не указали значение в ячейке H3, и функция ищет пустое значение.


Введем название месяца и значение изменится.


Только оно не соответствует действительности, ведь настоящее фактическое количество выпущенной продукции в январе равно 2000.
Это влияние аргумента «Условие поиска». Изменим его на 0. Для этого поставьте указатель на ячейку с формулой и снова нажмите Fx. В открывшемся окне введите «0» в последнюю строку.


Нажимайте «ОК». Как видим, результат изменился.


Чтобы проверить второе условие из начала нашей статьи (среди формул функция не ищет) изменим условия для функции. Увеличим диапазон и попробуем вывести значение из столбца с вычисляемыми значениями. Укажите значения как на скриншоте.


Нажмите «Ок». Как видите, результат поиска оказался 0, хотя в таблице стоит значение 85%.


ВПР в Excel «понимает» только фиксированные значения.

Сравнение данных двух таблиц Excel

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

На двух листах мы имеем одинаковые таблицы с разными данными.

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

В ячейку B2 введем функцию ВПР. В качестве первого аргумента укажем ячейку с месяцем на текущем листе, а диапазон выберем с листа «Цех1». Чтобы при копировании диапазон не смещался, нажмите F4 после выбора диапазона. Это сделает ссылку абсолютной.


Растяните формулу на весь столбец.

Аналогично введите формулу в следующий столбец, только диапазон выделяйте на листе «Цех2».


После копирования Вы получите сводный отчет с двух листов.

Подстановка данных из одной таблицы Excel в другую

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


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


В результате столбец второй таблицы будет скопирован в первую.


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

Отличного Вам дня!

Необходимое предисловие

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

Проблема

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

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

Стоимость по коду заказа найти легко - обычный ВПР тут поможет на раз-два. А вот как найти название товара по коду? На тренингах этот вопрос я чаще всего слышу в формулировке "а как сделать левый ВПР"?

Давайте разберем несколько способов.

Способ 1. Лобовая атака

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

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

Способ 2. Виртуальная перестановка столбцов функцией ВЫБОР

Если переставить местами столбцы на листе нельзя, то это можно сделать виртуально, т.е. "на лету" прямо в самой формуле. Для этого нам потребуется функция ВЫБОР (CHOOSE) . Основное ее предназначение – выбирать нужный элемент из списка по заданному номеру. Ее, например, можно использовать для замены номера дня недели на его текстовый аналог:

Ничего сверхъестественного, на первый взгляд, но тут есть пара хитрых моментов.

Во-первых , вместо текстовых названий выбираемых элементов списка ("пн", "вт" и т.д.) можно использовать адреса диапазонов. И тогда функция вернет ссылку на выбранный диапазон. Так, например, формула:

ВЫБОР(2; A1:A10 ; D1:D10 ; B1:B10 )

Во-вторых , вместо простого одиночного номера извлекаемого элемента в первом аргументе функции ВЫБОР можно задать массив констант в фигурных скобках, например, так:

ВЫБОР({1;2} ; A1:A10 ; D1:D10 ; B1:B10 )

Тогда на выходе мы получим два первых диапазона (A1:A10 и D1:D10), склеенных в единое целое.

И вот теперь все это можно вложить внутрь нашей ВПР , чтобы реализовать «левый поиск»:

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

Минусы такого способа - это скорость (примерно в 5-7 раз медленнее обычного ВПР) и некоторая непривычность для коллег (а может это даже плюс!)

Способ 3. Связка функций ИНДЕКС и ПОИСКПОЗ

Если не держаться имеено за функцию ВПР, то можно использовать ее более мощный аналог - связку двух очень полезных функций ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) :

Функция ПОИСКПОЗ ищет заданное значение (С2, т.е. код нужного нам заказа) в одномерном диапазоне (столбце кодов в таблице C10:C25) и выдает в качестве результата порядковый номер ячейки, где нашла искомое - в нашем случае это будет число 4, т.к. код нужного нам заказа четвертый в таблице.

А затем в дело вступает функция ИНДЕКС , которая умеет извлекать данные из вертикального массива-столбца (названия товаров в B10:B25) по порядковому номеру (который предварительно нашла ПОИСКПОЗ ). Таким образом, ИНДЕКС выдаст нам содержимое четвертой ячейки из столбца Товар , что и требовалось.

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

Похожий пример (с видео) я разбирал ранее . А уж про функцию ИНДЕКС можно говорить :)

Способ 4. Функция СУММЕСЛИ(МН)

Если нужно извлечь из таблицы именно число (допустим, объем в литрах), то иногда проще использовать для реализации "левого ВПР" функцию выборочного суммирования СУММЕСЛИ (SUMIF) или ее старшую сестру - функцию СУММЕСЛИМН (SUMIFS) :

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

Большая статья про функции выборочного подсчета по одному или нескольким условиям .

Способ 5. Готовая макрофункция из PLEX

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

• искать по нескольким столбцам сразу (до 3)
• выдавать результаты из любого столбца (левее или правее - не важно)
• выдавать не только первое встретившееся значение, а нужное по порядку
• можно задать, что вывести, если ничего не найдено вместо ошибки #Н/Д

У такого способа два минуса: нужно сохранять файл с поддержкой макросов (XLSM) и скорость у любой макрофункции не очень высокая - на больших таблицах может ощутимо подтормаживать.

Но как один из вариантов - пойдет:)

Ссылки по теме

Отличный способ. Спасибо.
Научите: Как можно с помощью индекс и поискпозиции выбирать значения из первого столбца, когда ищешь во втором столбце, но при этом нужно во втором столбце найти значения с максимальной датой в четвертом?
На вскидку приходит функция наибольший, но куда ее вставить....)))вот такая таблица

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

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

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

Нажмите по верхней ячейке в первой таблице в столбце Цена, а потом кнопочку «fx» в строке формул, чтобы открыть окно мастера функций.

Следующее, что мы делаем – прописываем аргументы в предложенные поля.

Ставьте курсив в поле «Искомое_значение» и выделяйте в первой таблице то значение, которое будем искать. У меня это яблоко.

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

Чтобы после того, как мы напишем формулу и растянем ее по всему столбцу, выбранный диапазон не смещался вниз, нужно сделать ссылки абсолютными: выделите данные в поле и нажмите «F4» . Теперь адрес на ячейки стал абсолютным, к ним добавился знак «$» , и диапазон смещаться не будет.

Там, где номер столбца, поставьте цифру, соответствующую во второй таблице тому столбцу, данные откуда нужно переносить. У меня прайс состоит из фруктов и цены, мне нужно второе, поэтому ставлю цифру «2» .

В «Интервальный_просмотр» пишем «ЛОЖЬ» – если искать нужно точные совпадения, или «Истина» – если значения могут быть приближенные. Для нашего примера выбираем первое. Если ничего не указать в данном поле, то по умолчанию выберется второе. Потом нажимайте «ОК» .

Здесь обратите внимание на следующее, если работаете с числами и указываете «Истина» , то вторая таблица (это наш прайс) обязательно должна быть отсортирована по возрастанию. Например, при поиске 5,25 найдется 5,27 и возьмутся данные с этой строки, хотя ниже может еще быть и число 5,2599 – но формула дальше смотреть не будет, поскольку она думает, что ниже числа только больше.

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

ВПР(А2;$G$2:$H$12;2;ЛОЖЬ)

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

Все цены перенесены из прайса в таблицу закупок в соответствии с названиями фруктов.

Если у Вас в первой таблице есть названия продуктов, которых нет в прайсе, у меня это овощи, то напротив данных пунктов формула ВПР выдаст ошибку #Н/Д .

При добавлении столбцов на лист, данные для аргумента «Таблица» функции автоматически изменятся. В примере прайс сдвинут на 2 столбца вправо. Выделим любую ячейку с формулой и видим, что вместо $G$2:$H$12 теперь $I$2:$J$14 .

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

В открывшемся окне «Тип данных» будет «Список» , ниже указываем область источника – это названия фруктов, то есть тот столбец, который есть и в первой и во второй таблице. Нажимайте «ОК» .

Выделяю F2 и вставляю функцию ВПР. Аргумент первый – это сделанный список (F1 ).

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

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

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

Жмем по любой ячейке в столбце D и вставляем один новый.

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

Вставляем функцию и указываем аргументы. Сначала то, что будем искать, в примере яблоко (А2 ). Для выбора диапазона из нового прайса, поставьте курсор в поле «Таблица» и перейдите на нужный лист, у меня «Лист1» .

Выделяем мышкой необходимые столбцы и строки, без заголовков.

Дальше делаем абсолютные ссылки на ячейки: «Лист1!$A$2:$B$12» . Выделите строчку и нажмите «F4» , чтобы к адресам ячеек добавился знак доллара. Указываем столбец (2 ) и пишем «ЛОЖЬ» .

В конце нажмите кнопку «ОК» .

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

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

(13 оценок, среднее: 5,00 из 5)

Вебмастер. Высшее образование по специальности "Защита информации".. Автор большинства статей и уроков компьютерной грамотности

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

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

Зачем нам это? – спросите Вы. Да, потому что ВПР – это не единственная функция поиска в Excel, и её многочисленные ограничения могут помешать Вам получить желаемый результат во многих ситуациях. С другой стороны, функции ИНДЕКС и ПОИСКПОЗ – более гибкие и имеют ряд особенностей, которые делают их более привлекательными, по сравнению с ВПР .

Базовая информация об ИНДЕКС и ПОИСКПОЗ

Так как задача этого учебника – показать возможности функций ИНДЕКС и ПОИСКПОЗ для реализации вертикального поиска в Excel, мы не будем задерживаться на их синтаксисе и применении.

Приведём здесь необходимый минимум для понимания сути, а затем разберём подробно примеры формул, которые показывают преимущества использования ИНДЕКС и ПОИСКПОЗ вместо ВПР .

ИНДЕКС – синтаксис и применение функции

Функция INDEX (ИНДЕКС) в Excel возвращает значение из массива по заданным номерам строки и столбца. Функция имеет вот такой синтаксис:




Каждый аргумент имеет очень простое объяснение:

• array (массив) – это диапазон ячеек, из которого необходимо извлечь значение.
• row_num (номер_строки) – это номер строки в массиве, из которой нужно извлечь значение. Если не указан, то обязательно требуется аргумент column_num (номер_столбца).
• column_num (номер_столбца) – это номер столбца в массиве, из которого нужно извлечь значение. Если не указан, то обязательно требуется аргумент row_num (номер_строки)

Если указаны оба аргумента, то функция ИНДЕКС возвращает значение из ячейки, находящейся на пересечении указанных строки и столбца.

Вот простейший пример функции INDEX (ИНДЕКС):

INDEX(A1:C10,2,3)
=ИНДЕКС(A1:C10;2;3)

Формула выполняет поиск в диапазоне A1:C10 и возвращает значение ячейки во 2-й строке и 3-м столбце, то есть из ячейки C2 .

Очень просто, правда? Однако, на практике Вы далеко не всегда знаете, какие строка и столбец Вам нужны, и поэтому требуется помощь функции ПОИСКПОЗ .

ПОИСКПОЗ – синтаксис и применение функции

Функция MATCH (ПОИСКПОЗ) в Excel ищет указанное значение в диапазоне ячеек и возвращает относительную позицию этого значения в диапазоне.

Например, если в диапазоне B1:B3 содержатся значения New-York, Paris, London, тогда следующая формула возвратит цифру 3 , поскольку “London” – это третий элемент в списке.

MATCH("London",B1:B3,0)
=ПОИСКПОЗ("London";B1:B3;0)

Функция MATCH (ПОИСКПОЗ) имеет вот такой синтаксис:

MATCH(lookup_value,lookup_array,)
ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])

• lookup_value (искомое_значение) – это число или текст, который Вы ищите. Аргумент может быть значением, в том числе логическим, или ссылкой на ячейку.
• lookup_array (просматриваемый_массив) – диапазон ячеек, в котором происходит поиск.
• match_type (тип_сопоставления) – этот аргумент сообщает функции ПОИСКПОЗ , хотите ли Вы найти точное или приблизительное совпадение:
  • 1 или не указан – находит максимальное значение, меньшее или равное искомому. Просматриваемый массив должен быть упорядочен по возрастанию, то есть от меньшего к большему.
  • 0 – находит первое значение, равное искомому. Для комбинации ИНДЕКС /ПОИСКПОЗ всегда нужно точное совпадение, поэтому третий аргумент функции ПОИСКПОЗ должен быть равен 0 .
  • -1 – находит наименьшее значение, большее или равное искомому значению. Просматриваемый массив должен быть упорядочен по убыванию, то есть от большего к меньшему.

На первый взгляд, польза от функции ПОИСКПОЗ вызывает сомнение. Кому нужно знать положение элемента в диапазоне? Мы хотим знать значение этого элемента!

Позвольте напомнить, что относительное положение искомого значения (т.е. номер строки и/или столбца) – это как раз то, что мы должны указать для аргументов row_num (номер_строки) и/или column_num (номер_столбца) функции INDEX (ИНДЕКС). Как Вы помните, функция ИНДЕКС может возвратить значение, находящееся на пересечении заданных строки и столбца, но она не может определить, какие именно строка и столбец нас интересуют.

Как использовать ИНДЕКС и ПОИСКПОЗ в Excel

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

Ещё не совсем понятно? Представьте функции ИНДЕКС и ПОИСКПОЗ в таком виде:

INDEX(,(MATCH (искомое значение ,столбец в котором ищем ,0))
=ИНДЕКС(столбец из которого извлекаем ;(ПОИСКПОЗ(искомое значение ;столбец в котором ищем ;0))

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



Давайте найдём население одной из столиц, например, Японии, используя следующую формулу:

INDEX($D$2:$D$10,MATCH("Japan",$B$2:$B$10,0))
=ИНДЕКС($D$2:$D$10;ПОИСКПОЗ("Japan";$B$2:$B$10;0))

Теперь давайте разберем, что делает каждый элемент этой формулы:

• Функция MATCH (ПОИСКПОЗ) ищет значение “Japan” в столбце B , а конкретно – в ячейках B2:B10 , и возвращает число 3 , поскольку “Japan” в списке на третьем месте.
• Функция INDEX (ИНДЕКС) использует 3 для аргумента row_num (номер_строки), который указывает из какой строки нужно возвратить значение. Т.е. получается простая формула:

  INDEX($D$2:$D$10,3)
  =ИНДЕКС($D$2:$D$10;3)

  Формула говорит примерно следующее: ищи в ячейках от D2 до D10 и извлеки значение из третьей строки, то есть из ячейки D4 , так как счёт начинается со второй строки.

Вот такой результат получится в Excel:



Важно! Количество строк и столбцов в массиве, который использует функция INDEX (ИНДЕКС), должно соответствовать значениям аргументов row_num (номер_строки) и column_num (номер_столбца) функции MATCH (ПОИСКПОЗ). Иначе результат формулы будет ошибочным.

Стоп, стоп… почему мы не можем просто использовать функцию VLOOKUP (ВПР)? Есть ли смысл тратить время, пытаясь разобраться в лабиринтах ПОИСКПОЗ и ИНДЕКС ?

VLOOKUP("Japan",$B$2:$D$2,3)
=ВПР("Japan";$B$2:$D$2;3)

В данном случае – смысла нет! Цель этого примера – исключительно демонстрационная, чтобы Вы могли понять, как функции ПОИСКПОЗ и ИНДЕКС работают в паре. Последующие примеры покажут Вам истинную мощь связки ИНДЕКС и ПОИСКПОЗ , которая легко справляется с многими сложными ситуациями, когда ВПР оказывается в тупике.

Почему ИНДЕКС/ПОИСКПОЗ лучше, чем ВПР?

Решая, какую формулу использовать для вертикального поиска, большинство гуру Excel считают, что ИНДЕКС /ПОИСКПОЗ намного лучше, чем ВПР . Однако, многие пользователи Excel по-прежнему прибегают к использованию ВПР , т.к. эта функция гораздо проще. Так происходит, потому что очень немногие люди до конца понимают все преимущества перехода с ВПР на связку ИНДЕКС и ПОИСКПОЗ , а тратить время на изучение более сложной формулы никто не хочет.

4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:

1. Поиск справа налево. Как известно любому грамотному пользователю Excel, ВПР не может смотреть влево, а это значит, что искомое значение должно обязательно находиться в крайнем левом столбце исследуемого диапазона. В случае с ПОИСКПОЗ /ИНДЕКС , столбец поиска может быть, как в левой, так и в правой части диапазона поиска. Пример: покажет эту возможность в действии.

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

Например, если у Вас есть таблица A1:C10 , и требуется извлечь данные из столбца B , то нужно задать значение 2 для аргумента col_index_num (номер_столбца) функции ВПР , вот так:

VLOOKUP("lookup value",A1:C10,2)
=ВПР("lookup value";A1:C10;2)

Если позднее Вы вставите новый столбец между столбцами A и B , то значение аргумента придется изменить с 2 на 3 , иначе формула возвратит результат из только что вставленного столбца.

Используя ПОИСКПОЗ /ИНДЕКС , Вы можете удалять или добавлять столбцы к исследуемому диапазону, не искажая результат, так как определен непосредственно столбец, содержащий нужное значение. Действительно, это большое преимущество, особенно когда работать приходится с большими объёмами данных. Вы можете добавлять и удалять столбцы, не беспокоясь о том, что нужно будет исправлять каждую используемую функцию ВПР .

3. Нет ограничения на размер искомого значения. Используя ВПР , помните об ограничении на длину искомого значения в 255 символов, иначе рискуете получить ошибку #VALUE! (#ЗНАЧ!). Итак, если таблица содержит длинные строки, единственное действующее решение – это использовать ИНДЕКС /ПОИСКПОЗ .

Предположим, Вы используете вот такую формулу с ВПР , которая ищет в ячейках от B5 до D10 значение, указанное в ячейке A2 :

VLOOKUP(A2,B5:D10,3,FALSE)
=ВПР(A2;B5:D10;3;ЛОЖЬ)

Формула не будет работать, если значение в ячейке A2 длиннее 255 символов. Вместо неё Вам нужно использовать аналогичную формулу ИНДЕКС /ПОИСКПОЗ :

INDEX(D5:D10,MATCH(TRUE,INDEX(B5:B10=A2,0),0))
=ИНДЕКС(D5:D10;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B5:B10=A2;0);0))

4. Более высокая скорость работы. Если Вы работаете с небольшими таблицами, то разница в быстродействии Excel будет, скорее всего, не заметная, особенно в последних версиях. Если же Вы работаете с большими таблицами, которые содержат тысячи строк и сотни формул поиска, Excel будет работать значительно быстрее, при использовании ПОИСКПОЗ и ИНДЕКС вместо ВПР . В целом, такая замена увеличивает скорость работы Excel на 13% .

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

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

ИНДЕКС и ПОИСКПОЗ – примеры формул

Теперь, когда Вы понимаете причины, из-за которых стоит изучать функции ПОИСКПОЗ и ИНДЕКС , давайте перейдём к самому интересному и увидим, как можно применить теоретические знания на практике.

Как выполнить поиск с левой стороны, используя ПОИСКПОЗ и ИНДЕКС

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

Функции ПОИСКПОЗ и ИНДЕКС в Excel гораздо более гибкие, и им все-равно, где находится столбец со значением, которое нужно извлечь. Для примера, снова вернёмся к таблице со столицами государств и населением. На этот раз запишем формулу ПОИСКПОЗ /ИНДЕКС , которая покажет, какое место по населению занимает столица России (Москва).

Как видно на рисунке ниже, формула отлично справляется с этой задачей:

INDEX($A$2:$A$10,MATCH("Russia",$B$2:$B$10,0))




Теперь у Вас не должно возникать проблем с пониманием, как работает эта формула:

• Во-первых, задействуем функцию MATCH (ПОИСКПОЗ), которая находит положение “Russia” в списке:

  MATCH("Russia",$B$2:$B$10,0))
  =ПОИСКПОЗ("Russia";$B$2:$B$10;0))

• Далее, задаём диапазон для функции INDEX (ИНДЕКС), из которого нужно извлечь значение. В нашем случае это A2:A10 .
• Затем соединяем обе части и получаем формулу:

  INDEX($A$2:$A$10;MATCH("Russia";$B$2:$B$10;0))
  =ИНДЕКС($A$2:$A$10;ПОИСКПОЗ("Russia";$B$2:$B$10;0))

Подсказка: Правильным решением будет всегда использовать абсолютные ссылки для ИНДЕКС и ПОИСКПОЗ , чтобы диапазоны поиска не сбились при копировании формулы в другие ячейки.

Вычисления при помощи ИНДЕКС и ПОИСКПОЗ в Excel (СРЗНАЧ, МАКС, МИН)

Вы можете вкладывать другие функции Excel в ИНДЕКС и ПОИСКПОЗ , например, чтобы найти минимальное, максимальное или ближайшее к среднему значение. Вот несколько вариантов формул, применительно к таблице из :

1. MAX (МАКС). Формула находит максимум в столбце D C той же строки:

INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МАКС($D$2:I$10);$D$2:D$10;0))

Результат: Beijing

2. MIN (МИН). Формула находит минимум в столбце D и возвращает значение из столбца C той же строки:

INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МИН($D$2:I$10);$D$2:D$10;0))

Результат: Lima

3. AVERAGE (СРЗНАЧ). Формула вычисляет среднее в диапазоне D2:D10 , затем находит ближайшее к нему и возвращает значение из столбца C той же строки:

INDEX($C$2:$C$10,MATCH(AVERAGE($D$2:D$10),$D$2:D$10,1))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(СРЗНАЧ($D$2:D$10);$D$2:D$10;1))

Результат: Moscow

О чём нужно помнить, используя функцию СРЗНАЧ вместе с ИНДЕКС и ПОИСКПОЗ

Используя функцию СРЗНАЧ в комбинации с ИНДЕКС и ПОИСКПОЗ , в качестве третьего аргумента функции ПОИСКПОЗ чаще всего нужно будет указывать 1 или -1 в случае, если Вы не уверены, что просматриваемый диапазон содержит значение, равное среднему. Если же Вы уверены, что такое значение есть, – ставьте 0 для поиска точного совпадения.

• Если указываете 1 , значения в столбце поиска должны быть упорядочены по возрастанию, а формула вернёт максимальное значение, меньшее или равное среднему.
• Если указываете -1 , значения в столбце поиска должны быть упорядочены по убыванию, а возвращено будет минимальное значение, большее или равное среднему.

В нашем примере значения в столбце D упорядочены по возрастанию, поэтому мы используем тип сопоставления 1 . Формула ИНДЕКС /ПОИСКПО З возвращает “Moscow”, поскольку величина населения города Москва – ближайшее меньшее к среднему значению (12 269 006).



Как при помощи ИНДЕКС и ПОИСКПОЗ выполнять поиск по известным строке и столбцу

Эта формула эквивалентна двумерному поиску ВПР и позволяет найти значение на пересечении определённой строки и столбца.

В этом примере формула ИНДЕКС /ПОИСКПОЗ будет очень похожа на формулы, которые мы уже обсуждали в этом уроке, с одним лишь отличием. Угадайте каким?

Как Вы помните, синтаксис функции INDEX (ИНДЕКС) позволяет использовать три аргумента:

INDEX(array,row_num,)
ИНДЕКС(массив;номер_строки;[номер_столбца])

И я поздравляю тех из Вас, кто догадался!

Начнём с того, что запишем шаблон формулы. Для этого возьмём уже знакомую нам формулу ИНДЕКС /ПОИСКПОЗ и добавим в неё ещё одну функцию ПОИСКПОЗ , которая будет возвращать номер столбца.

INDEX(Ваша таблица ,(MATCH(,столбец, в котором искать ,0)),(MATCH(,строка в которой искать ,0))
=ИНДЕКС(Ваша таблица ,(MATCH(значение для вертикального поиска ,столбец, в котором искать ,0)),(MATCH(значение для горизонтального поиска ,строка в которой искать ,0))

Обратите внимание, что для двумерного поиска нужно указать всю таблицу в аргументе array (массив) функции INDEX (ИНДЕКС).

А теперь давайте испытаем этот шаблон на практике. Ниже Вы видите список самых населённых стран мира. Предположим, наша задача узнать население США в 2015 году.



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

Итак, начнём с двух функций ПОИСКПОЗ , которые будут возвращать номера строки и столбца для функции ИНДЕКС :

• ПОИСКПОЗ для столбца – мы ищем в столбце B , а точнее в диапазоне B2:B11 , значение, которое указано в ячейке H2 (USA). Функция будет выглядеть так:

  MATCH($H$2,$B$1:$B$11,0)
  =ПОИСКПОЗ($H$2;$B$1:$B$11;0)

  4 , поскольку “USA” – это 4-ый элемент списка в столбце B (включая заголовок).

• ПОИСКПОЗ для строки – мы ищем значение ячейки H3 (2015) в строке 1 , то есть в ячейках A1:E1 :

  MATCH($H$3,$A$1:$E$1,0)
  =ПОИСКПОЗ($H$3;$A$1:$E$1;0)

  Результатом этой формулы будет 5 , поскольку “2015” находится в 5-ом столбце.

Теперь вставляем эти формулы в функцию ИНДЕКС и вуаля:

INDEX($A$1:$E$11,MATCH($H$2,$B$1:$B$11,0),MATCH($H$3,$A$1:$E$1,0))
=ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($H$2;$B$1:$B$11;0);ПОИСКПОЗ($H$3;$A$1:$E$1;0))

Если заменить функции ПОИСКПОЗ на значения, которые они возвращают, формула станет легкой и понятной:

INDEX($A$1:$E$11,4,5))
=ИНДЕКС($A$1:$E$11;4;5))

Эта формула возвращает значение на пересечении 4-ой строки и 5-го столбца в диапазоне A1:E11 , то есть значение ячейки E4 . Просто? Да!



Поиск по нескольким критериям с ИНДЕКС и ПОИСКПОЗ

В учебнике по ВПР мы показывали пример формулы с функцией ВПР для поиска по нескольким критериям . Однако, существенным ограничением такого решения была необходимость добавлять вспомогательный столбец. Хорошая новость: формула ИНДЕКС /ПОИСКПОЗ может искать по значениям в двух столбцах, без необходимости создания вспомогательного столбца!

Предположим, у нас есть список заказов, и мы хотим найти сумму по двум критериям – имя покупателя (Customer) и продукт (Product). Дело усложняется тем, что один покупатель может купить сразу несколько разных продуктов, и имена покупателей в таблице на листе Lookup table расположены в произвольном порядке.



Вот такая формула ИНДЕКС /ПОИСКПОЗ решает задачу:

{=INDEX("Lookup table"!$A$2:$C$13,MATCH(1,(A2="Lookup table"!$A$2:$A$13)*
(B2="Lookup table"!$B$2:$B$13),0),3)}
{=ИНДЕКС("Lookup table"!$A$2:$C$13;ПОИСКПОЗ(1;(A2="Lookup table"!$A$2:$A$13)*
(B2="Lookup table"!$B$2:$B$13);0);3)}

Эта формула сложнее других, которые мы обсуждали ранее, но вооруженные знанием функций ИНДЕКС и ПОИСКПОЗ Вы одолеете ее. Самая сложная часть – это функция ПОИСКПОЗ , думаю, её нужно объяснить первой.

MATCH(1,(A2="Lookup table"!$A$2:$A$13),0)*(B2="Lookup table"!$B$2:$B$13)
ПОИСКПОЗ(1;(A2="Lookup table"!$A$2:$A$13);0)*(B2="Lookup table"!$B$2:$B$13)

В формуле, показанной выше, искомое значение – это 1 , а массив поиска – это результат умножения. Хорошо, что же мы должны перемножить и почему? Давайте разберем все по порядку:

• Берем первое значение в столбце A (Customer) на листе Main table и сравниваем его со всеми именами покупателей в таблице на листе Lookup table (A2:A13).
• Если совпадение найдено, уравнение возвращает 1 (ИСТИНА), а если нет – 0 (ЛОЖЬ).
• Далее, мы делаем то же самое для значений столбца B (Product).
• Затем перемножаем полученные результаты (1 и 0). Только если совпадения найдены в обоих столбцах (т.е. оба критерия истинны), Вы получите 1 . Если оба критерия ложны, или выполняется только один из них – Вы получите 0 .

Теперь понимаете, почему мы задали 1 , как искомое значение? Правильно, чтобы функция ПОИСКПОЗ возвращала позицию только, когда оба критерия выполняются.

Обратите внимание: В этом случае необходимо использовать третий не обязательный аргумент функции ИНДЕКС . Он необходим, т.к. в первом аргументе мы задаем всю таблицу и должны указать функции, из какого столбца нужно извлечь значение. В нашем случае это столбец C (Sum), и поэтому мы ввели 3 .

И, наконец, т.к. нам нужно проверить каждую ячейку в массиве, эта формула должна быть формулой массива. Вы можете видеть это по фигурным скобкам, в которые она заключена. Поэтому, когда закончите вводить формулу, не забудьте нажать Ctrl+Shift+Enter .

Если всё сделано верно, Вы получите результат как на рисунке ниже:



ИНДЕКС и ПОИСКПОЗ в сочетании с ЕСЛИОШИБКА в Excel

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

Синтаксис функции ЕСЛИОШИБКА очень прост:

IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)

Где аргумент value (значение) – это значение, проверяемое на предмет наличия ошибки (в нашем случае – результат формулы ИНДЕКС /ПОИСКПОЗ ); а аргумент value_if_error (значение_если_ошибка) – это значение, которое нужно возвратить, если формула выдаст ошибку.

Например, Вы можете вставить в функцию ЕСЛИОШИБКА вот таким образом:

IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1:$E$1,0)),
"Совпадений не найдено. Попробуйте еще раз!") =ЕСЛИОШИБКА(ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($G$2;$B$1:$B$11;0);ПОИСКПОЗ($G$3;$A$1:$E$1;0));
"Совпадений не найдено. Попробуйте еще раз!")

И теперь, если кто-нибудь введет ошибочное значение, формула выдаст вот такой результат:



Если Вы предпочитаете в случае ошибки оставить ячейку пустой, то можете использовать кавычки (“”), как значение второго аргумента функции ЕСЛИОШИБКА . Вот так:

IFERROR(INDEX(массив,MATCH(искомое_значение,просматриваемый_массив,0),"")
ЕСЛИОШИБКА(ИНДЕКС(массив;ПОИСКПОЗ(искомое_значение;просматриваемый_массив;0);"")

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