Lost your password?


MulTEx »

ВПР_МН

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

MulTEx

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

 

Если не все, то очень многие из Вас знают функцию ВПР(VLookUp) в Excel. Несомненно очень удобная и нужная функция. Но, думаю, у многих появлялось желание сделать так, чтобы эта чудо-функция возвращала бы не только ПЕРВОЕ найденное значение, но и второе, и третье, и четвертое, и т.д. Короче все найденные значения. И работала бы не с одним критерием, а с несколькими. Как минимум с двумя. Я не был исключением из множества и именно поэтому решил создать аналог ВПР, но с возможностью поиска по двум и более критериям(до 124-х), и возможностью вернуть массив всех найденных значений. Либо n-ное найденное значение..

Вызов команды через стандартный диалог:
Мастер функций-Категория "MulTEx"- ВПР_МН
Вызов с панели MulTEx:
Сумма/Поиск/Функции - Массивы - ВПР_МН
Синтаксис:
=ВПР_МН($A$1:$E$20;J1;1;5;2;K1;">5";3;"реализация*";4)


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

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

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

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

Номер столбца(1) - указывается номер столбца в диапазоне значений, в котором следует искать соответствующий критерий(аналог аргумента Номер_столбца в стандартном ВПР). Если только один критерий, то остальные поля оставляем пустыми. Для Критерия указываем номер столбца в Диапазоне значений(именно номер в Диапазоне значений, а не номер столбца на листе).

Номер столбца искомых значений(5) - указывается номер столбца, значения из которого следует найти. В примере - 5.

Выводить первое значение(2) - способ вывода найденных значений(аналог аргумента Интервальный просмотр в стандартном ВПР).

  • 0 - выводит последнее найденное значение - функция вернет только одно последнее найденное в указанном массиве значение;
  • 1 - выводит первое найденное значение - функция вернет только одно первое найденное в указанном массиве значение;
  • 2 - выводит значение под указанным номером вхождения(если выбран, то необходимо указать Номер вхождения) - функция вернет только одно значение, порядковый номер которого будет равен указанному Вами аргументу Номер вхождения. Т.е. если необходимо вывести только 7-ое найденное значение, указываем Номер вхождения - 7. В качестве данного аргумента можно использовать данные в ячейках - K1. Если номер вхождения не указан - функция вернет значение: Не указан номер вхождения!;
  • -1 - выводит все найденные значения (для этого требуется ввести функцию как формулу массива: Выделить диапазон строк, в которые следует поместить результат. В строку формул ввести данную функцию и нажать Ctrl+Shift+Enter). Подробнее про формулы массива можно прочитать здесь.

Номер вхождения(K1) - Необязательный аргумент. Может быть целым числом, либо ссылкой на ячейку с целым числом. Учитывается только если параметр "Выводить первое значение" равен 2. Если указанный номер вхождения превышает количество найденных значений, то функция вернет 0.

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

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


Расскажи друзьям, если статья оказалась полезной:
  Плейлист   Видеоинструкции по использованию надстройки 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 Яндекс.Метрика
© 2021 Excel для всех   Войти