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

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

Номер столбца искомых значений - обязательный. Указывается номер столбца в Диапазон_значений, из которого необходимо вывести значения, отвечающее заданным критериям.

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

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

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

КритерийСтолбец(">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.
  6.  

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