Lost your password?


MulTEx »

ВПР_МН

Данная функция является частью надстройки MulTEx

MulTEx

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

 

Если не все, то очень многие знают функцию ВПР(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.  

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

  5. Отберем всех контрагентов с отсрочкой менее 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.
  6.  

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

  9. И еще одна стандартная задача - отобрать всех покупателей с объемом закупок от 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)

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


Расскажи друзьям, если статья оказалась полезной:
  Плейлист   Видеоинструкции по использованию надстройки MulTEx
Обсуждение: 30 комментариев
  1. Александр:

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

    Не могу разобраться, как это работает.

  2. Попробуйте в поисковике набрать: формула массива. Кратко все уже описано - не знаю, как Вам пояснить понятнее. Может так яснее будет: создали формулу в ячейке. Скопировали формулу из строки формул. Выделили диапазон, перешли в строку формул и вставили скопированную формулу. Нажали сочетание клавиш Ctrl+Shift+Enter.

  3. Олег:

    Можно ли вывести результат массива в одной ячейке?
    При параметре Выводить первое значение:-1?
    Например: {2009;2010;2011}
    Цель: т.к. вывод можно сделать только в столбец, а хотелось бы вывести все найденные значения в строку. Если получить результат как в примере можно потом раскидать полученную строку.

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

  5. Сергей:

    -1 после вставки формулы массива выводит во всех ячейках первые найденные значения

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

  6. Андрей:

    Здравствуйте!

    Скажите, а где найти данную формулу???
    или возможно код???

    Заранее большое спасибо.

  7. analitics:

    собственно, либо я туплю, либо функция не работает
    Столбец В и С. ищу значение С в столбце В и надо вывести все значения С, соответствующие значению В.
    {=ВПР2($B$3:$C$877;$C$3:$C$877;1;2;-1)}
    в таком случаи показывает знач!, если же
    {=ВПР2($B$3:$C$877;C3;1;2;-1)}
    то показывает как обычно впр - первое значение столько раз столько ячеек принято в массив.

  8. Собственно - сколько ячеек выделяете перед вводом формулы? Выделяете в строку или в столбец? Надо в столбец.

  9. analitics:

    выделяю весь диапазон д3:п877, т.е. я подразумеваю что может быть найдено нужных значений от столбца д до столбца п

  10. analitics:

    если можно пример в екселе работы данной функции на trufic@bk.ru ? либо здесь выложите свободно для скачки, ибо чую, что где-то затык ))

Поделитесь своим мнением

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


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2024 Excel для всех   Войти