MulTEx »

ВПР_ВСЕ_КНИГИ

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

Описание, установка, удаление и обновление Полный список команд и функций MulTEx Часто задаваемые вопросы по MulTEx   Скачать MulTEx

---

Поиск значений по всем листам/книгам

 

Достаточно часто я встречал на форумах вопрос: "А можно ли сделать так, чтобы ВПР искала значения на всех листах?". Можно. Но формула будет длинной и совершенно неудобной, т.к. надо будет ВПР дублировать для каждого листа, да еще с обработкой ошибок(либо применять формулу массива - подробнее о способах можно почитать в этой статье). А если листов много? А если еще и все книги надо просматривать? Тут формулам туго придется. ВПР_ВСЕ_КНИГИ ищет критерий на всех листах активной книги или всех листах всех открытых книг. Также можно указать конкретное имя листа, на котором просматривать данные. Плюс функция может вывести имена листов и книг, на которых это значение было найдено.

Вызов команды через стандартный диалог:

Мастер функций-Категория "MulTEx"- ВПР_ВСЕ_КНИГИ

Вызов с панели MulTEx:

Сумма/Поиск/Функции - Массивы - ВПР_ВСЕ_КНИГИ

Синтаксис:
=ВПР_ВСЕ_КНИГИ($A$1:$D$20;E1;1;2;-1;;1;"Лист1";ИСТИНА)

---

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

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

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

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

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

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

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

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

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

ПоВсемКнигам(1) - Необязательный аргумент. Если указан как ИСТИНА или 1, то значения просматриваются в диапазоне($A$1:$D$20) всех открытых книг. Если не указан, указан как ЛОЖЬ или 0, то значения будут просматриваться во всех листах активной книги. По умолчанию принимает значение ЛОЖЬ. Если какая-либо из книг будет закрыта, то при пересчете функция вернет результат уже без учета значений закрытой книги.

ИмяЛиста("Лист1") - Необязательный аргумент. Ссылка на ячейку или непосредственно текст. Если указан, то поиск значений будет производиться только по тем листам, имя которых совпадает с указанным. Регистр не учитывается. Если пусто или не указан, то поиск значений производится по всем листам. По умолчанию поиск значений производится по всем листам.

ВывестиИмяКнигиЛиста(ИСТИНА) - Необязательный аргумент. Если указан как ИСТИНА или 1, то вместе с самими найденными значениями будут выведены имена книг и листов, на которых эти значения были найдены. Применяется только если аргумент Выводить первое значение указан как -1. По умолчанию ЛОЖЬ, т.е. имена не выводятся.

Так же см.:
ВПР_МН