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. Ася:

    возможно мало исходных данных, может в этом дело.
    У меня в одной таблице такие данные:
    rr2314 50
    rr2314 45
    ou65488 1210
    ww65412 650
    ww65412 10
    ww65412 51
    ww65412 5

    В другой такие:
    ou65488 1209
    ww65412 5
    ww65412 61
    ww65412 650
    rr2314 50
    rr2314 45

    и надо по первому столбцу сопоставить данные вторых столбцов

  2. Вы не могли бы мне на e-mail выслать пример данных и того, как Вы вводите формулу? the-Prist@yandex.ru

  3. prepodobny:

    я приобрел надстройку у меня вопрос по впр2 если мне нужно найти в диапазоне не точное вхождение а определенную последовательность?

  4. prepodobny, а что Вы имеете ввиду под последовательностью? В функции можно применять символы подстановки: * и ?

  5. Серёнька:

    Помогите и мне плз.... почти 2 недели ломаю голову и подставляю различные формулы но ничего не помогает либо не так делаю..... вот что есть
    в одном столбце идут значения подтянутые из файла по первым 8 знакам А1=левсимв(А1;8), они дополняются путем поиска по тем же 8 символам с др страницы. но подставляет он только 1 значение (и почему то последнее вот форм. =ВПР(ЛЕВСИМВ('Вставлять в А2'!A21;9);S:S;1;1)) из нескольки.
    задача: сделать так, чтобы каждая повторяющаяся ранее ячейка (например их всего 20) с идентичным содержанием менялась на сл. по совподению на др стр (их например 6). т.е. выглядеть должно так
    1,2,3,4,5,6,1,2,3,4,5,6,1,2,3...и так до 20 этот пример примитивен но надеюсь понятен...
    формула подставления =ВПР(ЛЕВСИМВ('Вставлять в А2'!A11;8);S:S;1;1)
    массив для подставления FR!A1:C4288
    пример совпадения... по первым 8 символам

    XP7200RS/50B
    XP7200RS/50C
    XP7200RS/70D
    XP7200RS/70E
    XP7200RS/70F
    XP7200RS/70G
    XP7200RU/50C
    XP7200RU/70D
    XP7200RU/70E
    XP7200RU/70F
    XP7200RU/70G

    заранее спасибо.

  6. ВПР возвращает только первое совпадение, если аргумент интервальный_просмотр равен 0. У Вас он равен 1. Почитайте справку. И ВПР не умеет возвращать ВСЕ значения, подходящие под критерий. Именно поэтому я написал эту функцию - ВПР2.

  7. kiritani:

    Здравствуйте, никак не могу понять, что я делаю не так:
    первый столбец A с фразами, второй B с номерами объявлений, и есть список фраз в столбце С, которые необходимо найти в первом столбце (они могут там встречаться больше 1 раза) и вывести все номера объявлений, соответствующие этим фразам.
    Функция получилась такая {=ВПР2($A$1:$C$156851;C2;1;2;-1)}, но она выдает #ЗНАЧ
    Пробовала по разному изменять, но в некоторых случаях выдает 0, в большинстве все равно #ЗНАЧ.
    Подскажите, пожалуйста, я прочитала форум и описание к функции, но никаких полезных идей не появилось и исправить ошибку не удалось..

  8. kiritani, я не могу сказать причину без файла. Вышлите файл мне на почту: the-Prist@yandex.ru
    В файле оставьте формулу так, как Вы её вводите.

  9. Igor:

    =ВПР_МН(AC1806:AD1809;AE1802;1;2;2;2)

    AE1802 = 1
    AC1806:AD1809
    1 A
    1 B
    2 C
    3 D

    Ожидаю по вашему оператору получить результат B, но какие-бы варианты не пробовал получаю 0. Что делаю не так?

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

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


Для оформления сообщений Вы можете использовать следующие тэги:
<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 для всех   Войти