ВПР2 — поиск по четырем критериям, вывод всех совпадений
8427
Надстройка MyAddin
Если не все, то очень многие из Вас знают функцию ВПР(VLookUp) в Excel(если не знаете — прочитайте статью — Как найти значение в другой таблице или сила ВПР). Несомненно очень удобная и нужная функция. Но, думаю, у многих появлялось желание сделать так, чтобы эта чудо-функция возвращала бы не только ПЕРВОЕ найденное значение, но и второе, и третье, и четвертое, и т.д. Короче все найденные значения. И работала бы не с одним критерием, а с несколькими. Как минимум с двумя. Я не был исключением из множества и именно поэтому решил создать аналог ВПР, но с возможностью поиска по двум и более критериям(до 4-х), и возможностью вернуть массив всех найденных значений. Либо n-ное найденное значение. Решил и создал. Более оригинального названия, чем ВПР2 я ей не придумал… Итак, ВПР2.
Диспетчер функций-Категория:Функции MyAddin-ВПР2
Синтаксис функции:
=ВПР2($A$1:$E$20;J1;1;5;2;K1;2;L1;3;M1;4;7)
Диапазон значений($A$1:$E$20) — указывается диапазон, содержащий критерии поиска и искомые значения(аналог аргумента Таблица в стандартном ВПР).
Критерий1(J1), Критерий2(K1), Критерий3(L1), Критерий4(M1) — указывается ссылка на ячейку с критерием поиска(аналог аргумента Искомое_значение в стандартном ВПР). Если критерий поиска только один, то остальные поля следует оставить пустыми. Допускается применение в критериях символов подстановки — «*» и «?».
Номер столбца1, Номер столбца2, Номер столбца3, Номер столбца4 — указывается номер столбца в диапазоне значений, в котором следует искать соответствующий критерий(аналог аргумента Номер_столбца в стандартном ВПР). Если только один критерий, то остальные поля оставляем пустыми. Для Критерия1 указываем номер столбца в Диапазоне значений(именно номер в Диапазоне значений, а не номер столбца на листе). В примере для Критерия1 указан Номер столбца1 — 1, Критерий2 — 2, Критерий3 — 3 и Критерий4 — 4.
Номер столбца искомых значений — указывается номер столбца, значения из которого следует найти. В примере — 5.
Выводить первое значение — способ вывода найденных значений(аналог аргумента Интервальный просмотр в стандартном ВПР).
- 0 — выводит последнее найденное значение — функция вернет только одно последнее найденное в указанном массиве значение;
- 1 — выводит первое найденное значение — функция вернет только одно первое найденное в указанном массиве значение;
- 2 — выводит значение под указанным номером вхождения(если выбран, то необходимо указать Номер вхождения) — функция вернет только одно значение, порядковый номер которого будет равен указанному Вами аргументу Номер вхождения. Т.е. если необходимо вывести только 7-ое найденное значение, указываем Номер вхождения — 7(как в примере). В качестве данного аргумента можно использовать данные в ячейках. Если номер вхождения не указан — функция вернет значение Не указан номер вхождения!;
- -1 — выводит все найденные значения (для этого требуется ввести формулу массива: Выделить диапазон строк, в которые следует поместить результат. В строку формул ввести данную функцию и нажать Ctrl+Shift+Enter).
Номер вхождения — может быть целым числом, либо ссылкой на ячейку с целым числом. Обязателен только если параметр «Выводить первое значение» равен 2. Если указанный номер вхождения превышает количество найденных значений, то функция вернет 0.
Также см.:
→Как найти значение в другой таблице или сила ВПР
-1 – выводит все найденные значения (для этого требуется ввести формулу массива: Выделить диапазон строк, в которые следует поместить результат. В строку формул ввести данную функцию и нажать Ctrl+Shift+Enter).
Не могу разобраться, как это работает.
Попробуйте в поисковике набрать: формула массива. Кратко все уже описано — не знаю, как Вам пояснить понятнее. Может так яснее будет: создали формулу в ячейке. Скопировали формулу из строки формул. Выделили диапазон, перешли в строку формул и вставили скопированную формулу. Нажали сочетание клавиш Ctrl+Shift+Enter.
Можно ли вывести результат массива в одной ячейке?
При параметре Выводить первое значение:-1?
Например: {2009;2010;2011}
Цель: т.к. вывод можно сделать только в столбец, а хотелось бы вывести все найденные значения в строку. Если получить результат как в примере можно потом раскидать полученную строку.
Непосредственно в функции не получится(не вмешиваясь в код). Но можно вывести в столбец, а затем полученные значения транспонировать(функцией ТРАНСП или Специальной вставкой).
-1 после вставки формулы массива выводит во всех ячейках первые найденные значения
Если Вы пытаетесь вывести значения, выделив строку — то так и будет. Выделите ячейки в столбце — тогда будут выведены именно все значения в нужном виде.
Здравствуйте!
Скажите, а где найти данную формулу???
или возможно код???
Заранее большое спасибо.
собственно, либо я туплю, либо функция не работает
Столбец В и С. ищу значение С в столбце В и надо вывести все значения С, соответствующие значению В.
{=ВПР2($B$3:$C$877;$C$3:$C$877;1;2;-1)}
в таком случаи показывает знач!, если же
{=ВПР2($B$3:$C$877;C3;1;2;-1)}
то показывает как обычно впр — первое значение столько раз столько ячеек принято в массив.
Собственно — сколько ячеек выделяете перед вводом формулы? Выделяете в строку или в столбец? Надо в столбец.
выделяю весь диапазон д3:п877, т.е. я подразумеваю что может быть найдено нужных значений от столбца д до столбца п
если можно пример в екселе работы данной функции на ? либо здесь выложите свободно для скачки, ибо чую, что где-то затык ))
надо выделять ОДИН столбец. Только один. Т.е. D3:D877.
теперь точно уверен — не работает функция.
И откуда такая уверенность? Я вот точно теперь уверен в том, что Вы неверно её применяете. Скачал надстройку, создал таблицу, ввел функцию — все работает.
То, что не работает у Вас одного не означает, что не работает вообще.
Вы указываете, что так не работает:
{=ВПР2($B$3:$C$877;$C$3:$C$877;1;2;-1)}
Почему у Вас второй аргумент функции это массив ячеек? Ведь даже в описании указана ОДНА — «указывается ссылка на ячейку». И во второй интерпретации функции Вы тоже используете ссылку лишь на одну ячейку. Может в этом дело-то, а не в функции?