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

 

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

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

Loading

30 комментариев

  1. И откуда такая уверенность? Я вот точно теперь уверен в том, что Вы неверно её применяете. Скачал надстройку, создал таблицу, ввел функцию - все работает.
    То, что не работает у Вас одного не означает, что не работает вообще.

    Вы указываете, что так не работает:
    {=ВПР2($B$3:$C$877;$C$3:$C$877;1;2;-1)}
    Почему у Вас второй аргумент функции это массив ячеек? Ведь даже в описании указана ОДНА - "указывается ссылка на ячейку". И во второй интерпретации функции Вы тоже используете ссылку лишь на одну ячейку. Может в этом дело-то, а не в функции?

  2. изв.если я не правильно поняла, ВПР2 можно купить? если да, то как? и как ее себе добавить в список формул? если можно по-подробнее опишите-для чайников

  3. Наталья, убедительная просьба перейти по ссылке, которую я дал в предыдущем сообщении и почитать внимательно - там написано и как купить и как установить и где потом функции искать. В одном комментарии я все это не опишу, а там расписал уже все, что можно было.

  4. Здравствуйте! Никак не получается чтобы вышли все найденные значения. Если не выбирать диапазон строк, то выдает в ячейку с формулой первое найденное значение, как только ввожу диапазон строк - выдает FASLE.
    Формула следующая:
    =H2:H156=ВПР2(SAP!A2:M429,A2,1,7,-1)
    Что не так?

  5. Формула точно прописывается для всех выделенных ячеек одна? Вы можете поменять формулу одной ячеки из результирующих? Если правильно ввели формулу, то при попытке поменять формулу только для одной ячейки появится сообщение: Нельзя изменять часть массива.
    Это все, что могу сказать. Только что попробовал - работает без ошибок.

Добавить комментарий

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.