ВПР_МН

Вернуть все найденные значения

 

Если не все, то очень многие знают функцию ВПР(VLookUp) в Excel. При всех её достоинствах у неё есть и недостатки: она ищет значение исключительно в первом столбце таблицы, а возвращать может только ПЕРВОЕ найденное значение(сверху вниз). Плюс искать умеет только по одному критерию.
Функция ВПР_МН способна:

• искать по двум и более критериям(до 124-х)
• критерии могут располагаться в любом столбце, а не только в первом
• возвращать не только первое найденное, но любое указанное(поиск идет сверху-вниз): второе, третье, четвертое и т.д.
• вести поиск заданного значения не только сверху вниз, но и снизу вверх - т.е. найти первое снизу значение
• вернуть массив всех найденных значений.

Вызов команды через стандартный диалог:

Мастер функций -Категория "MulTEx" -ВПР_МН

Вызов с панели MulTEx:

Сумма/Поиск/Функции -Массивы -ВПР_МН

Синтаксис:
=ВПР_МН($A$1:$E$20;J2;2;4;2;1)
=ВПР_МН($A$1:$E$20;J2;2;4;0;1)
=ВПР_МН($A$1:$E$20;J2;2;4;-1;1)
=ВПР_МН($A$1:$E$20;"<30";2;1;-1;1)
=ВПР_МН($A$1:$E$20;J2;5;1;-1;1;">=10000000";3;"<=20000000";3))

---

Диапазон значений($A$1:$E$20) - обязательный. Указывается диапазон, содержащий критерии поиска и искомые значения.

Критерий(J2) - указывается ссылка на ячейку с критерием поиска или непосредственно значение. Допускается применение в критериях символов подстановки - * и ?. Т.е. указав в качестве Критерия "реализация*" в качестве результата будут отобраны ячейки, текст ячеек в столбце критериев которых начинается со слова "реализация".
Так же данный аргумент может принимать в качестве критерия символы сравнения(<, >, =, <>, <=, =>):

• ">0" - будут отобраны ячейки, значения ячеек критериев для которых больше нуля;
• ">=2" - будут отобраны ячейки, значения ячеек критериев для которых больше или равно двум;
• "<0" - будут отобраны ячейки, значения ячеек критериев для которых меньше нуля;
• "<=60" - будут отобраны ячейки, значения ячеек критериев для которых меньше или равно 60;
• "<>0" - будут отобраны ячейки, значения ячеек критериев для которых не равно нулю;
• "<>" - будут отобраны ячейки, значения ячеек критериев для которых не пустые;

Вместо нуля может быть любое число или текст. Так же можно добавить ссылку на ячейку со значением: "<>"&J2

Номер столбца - обязательный. Указывается номер столбца в Диапазоне_значений, в котором следует искать заданное значение - Критерий. Указывается именно порядковый номер в Диапазоне_значений, а не номер столбца на листе.
Например, если в качестве Диапазона_значений указан диапазон C1:F500, а искать критерий необходимо в столбце D, то Номер_столбца указывается равным 2, т.к. порядковый номер столбца D в диапазоне C1:F500 именно 2 (С - 1-ый, D - 2-ой, Е - 3-ий и т.д.).

Номер столбца искомых значений - обязательный. Указывается номер столбца в Диапазон_значений, значения из которого следует вывести в качестве результата. Указывается именно порядковый номер в Диапазоне_значений, а не номер столбца на листе.
Например, если в качестве Диапазона_значений указан диапазон C1:F500, а вывести в качестве результата необходимо значения из столбца E, то Номер_столбца_искомых_значений указывается равным 3, т.к. порядковый номер столбца E в диапазоне C1:F500 именно 3 (С - 1-ый, D - 2-ой, Е - 3-ий и т.д.).

Выводить первое значение - обязательный. Способ вывода найденных значений:

• 0 - выводит последнее найденное значение - функция вернет только одно последнее найденное в указанном диапазоне значение
• 1 - выводит первое найденное значение - функция вернет только одно первое найденное в указанном диапазоне значение
• 2 - выводит значение под указанным номером вхождения(если выбран, то необходимо указать Номер вхождения) - функция вернет только одно значение, порядковый номер которого будет равен указанному аргументу Номер вхождения. Т.е. если необходимо вывести только 7-ое найденное значение, указываем Номер вхождения - 7. В качестве данного аргумента можно использовать данные в ячейках - I2. Если номер вхождения не указан - функция вернет значение ошибки #ЧИСЛО!(#NUM!)
• -1 - выводит все найденные значения (для этого требуется ввести функцию как формулу массива: сначала выделить с сразу несколько ячеек, в которые следует поместить результат. В строку формул ввести данную функцию и одновременно нажать сочетание клавиш Ctrl+Shift+Enter). Подробнее про формулы массива можно прочитать здесь.
  Если используется версия Office 365 или новее - функцию допускается вводить только в одну ячейку и она распространит все найденные значения на остальные ячейки сама, выделять заранее несколько ячеек нет необходимости.

Номер вхождения(K1) - обязательный. Может быть целым числом, либо ссылкой на ячейку с целым числом. Учитывается только в случае, если аргумент Выводить_первое_значение равен 2. Однако обязателен к указанию в любом случае. Даже если аргумент Выводить_первое_значение равен 1 - то Номер_вхождения все равно должен быть указан, но допускается указание любого числа от 1 до 255, на результат это никак не влияет. Если указанный номер вхождения превышает количество найденных значений, то функция вернет значение ошибки #Н/Д(#N/A). Если параметр Выводить первое значение равен 2, а номер вхождения не указан или равен 0 - функция вернет значение ошибки #ЧИСЛО!(#NUM!).

Критерий_НомерСтолбца(">5";3;"реализация*";4) - Необязательный. Дополнительные критерии и номера столбцов, в которых критерии просматривать. Важно! при указании очень важен порядок следования: сначала обязательно указывается критерий и только потом номер столбца, в котором этот критерий просматривать. Если указать в другом порядке - функция выдаст ошибку. Если критерий указан, а номер столбца нет - данный критерий учитываться не будет. Условия применения такие же, что и для описанных выше аргументов Критерий и Номер_столбца. Т.е. допускается использование подстановочных символов и операторов сравнения. Допускается до 124 связок Критерий-Номер_столбца(т.е. всего 248 аргументов - до 124 Критериев и до 124 Номеров столбцов). Все условия объединены между собой по принципу совпадения И. Т.е. все условия поиска значений, заданные перечисленными Критериями, должны выполняться. Если хоть одно условие связки Критерий_НомерСтолбца не выполняется, условия расположенные далее по списку не проверяются, общее условие по строке считается как несоответствующее и данные этой строки не будут включены в результат.

---

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

Теперь решим несколько задач при помощи функции ВПР_МН:

1. Найдем кол-во дней отсрочки для контрагента "ООО "Беркут""(при условии, что контрагент записан в ячейке J2):
   =ВПР_МН($A$1:$E$20;J2;1;2;2;1)
   Это несложная задача и по сути здесь с легкостью справится стандартная ВПР и ВПР_МН здесь не обязательно.

 

2. Теперь найдем кол-во дней отсрочки для контрагента "ООО "Беркут"", но при этом только для 2-го квартала(при условии, что контрагент записан в ячейке J2, а значение квартала - в I2):
   =ВПР_МН($A$1:$E$20;J2;1;2;2;1;I2;4)
   кварталы в нашей таблице записаны в столбце D, который идет 4-м по счету в нашей таблице. Поэтому мы и указываем последовательно I2(2 кв.) и 4(номер столбца, где искать квартал).

 

3. Отберем всех контрагентов с отсрочкой менее 30 дней:
   =ВПР_МН($A$1:$E$20;"<30";2;1;-1;1)
   для того, чтобы отобрать всех контрагентов, 5-ый аргумент(Выводить первое значение) указывается как "-1", а формула вводится в ячейку как формула массива тремя клавишами - Ctrl+Shift+Enter. При этом важно помнить, что перед тем как вводить формулу необходимо заранее выделить не одну ячейку, а сразу несколько ячеек(построчно) в кол-ве, достаточном для записи всех найденных значений. В нашем случае можно было выделить ячейки K2:K3, записать в них формулу =ВПР_МН($A$1:$E$20;"<30";2;1;-1;1) и завершить ввод сочетанием клавиш Ctrl+Shift+Enter.
   В офисе 365 вводить формулу как формулу массива нет необходимости - подробнее в статье: Динамические массивы в Excel.

 

4. Выведем все объёмы закупок за "2 кв." у контрагента "ООО "Беркут""(при условии, что контрагент записан в ячейке J2, а значение квартала - в I2):
   =ВПР_МН($A$1:$E$20;J2;1;3;-1;1;I2;4)
   Так же как и в примере выше - формула вводится в ячейку как формула массива, т.е. сразу в несколько ячеек и сочетанием клавиш Ctrl+Shift+Enter. Отличие в том, что теперь у нас не один критерий поиска, а два.
   В офисе 365 вводить формулу как формулу массива нет необходимости - подробнее в статье: Динамические массивы в Excel.

 

5. И еще одна стандартная задача - отобрать всех покупателей с объемом закупок от 10 до 20 млн. Отбор производится на основании трех критериев: 1-ый - признак контрагента(Покупатель или Поставщик), который в нашей таблице записан в столбце E(в формуле мы этот признак для отбора указываем в ячейке K2); 2-ой - нижнее значение объема закупок в 10 млн; 3-ий - верхнее значение объема закупок в 20 млн:
   =ВПР_МН($A$1:$E$20;J2;5;1;-1;1;">=10000000";3;"<=20000000";3)
   Формула вводится в ячейку как формула массива, т.е. сразу в несколько ячеек и сочетанием клавиш Ctrl+Shift+Enter.
   В офисе 365 вводить формулу как формулу массива нет необходимости - подробнее в статье: Динамические массивы в Excel.
   Так же нижнее и верхнее значение объёма закупок можно указать непосредственно в ячейках и ссылаться в формуле на них. Для примера это будут ячейки I2 и J2, признак контрагента по прежнему в ячейке K2:
   =ВПР_МН($A$1:$E$20;K2;5;1;-1;1;I2;3;J2;3)

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