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

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

 

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

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

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

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

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

Синтаксис:
=ВПР_ВСЕ_КНИГИ($A$1:$D$20; E1; 1; 2; -1)
=ВПР_ВСЕ_КНИГИ($A$1:$D$20; E1; 1; 2; -1; ; 1; "Лист1"; ИСТИНА)
=ВПР_ВСЕ_КНИГИ($C$1:$F$500; E1; 3; 1; 0; 0; 1; "Продажи"; 1)
=ВПР_ВСЕ_КНИГИ($C$1:$F$500; E1; 3; 1; 2; 4; 0; "Продажи"; 0)
=ВПР_ВСЕ_КНИГИ($C$1:$F$500; E1; 3; 1; 2; 4; 0; "Продажи"; ЛОЖЬ)

---

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

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

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

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

Номер столбца(1) - обязательный. Указывается номер столбца в Диапазоне_значений, в котором следует искать заданное значение - Критерий. Указывается именно порядковый номер в Диапазоне_значений, а не номер столбца на листе.
Например, если в качестве Диапазона_значений указан диапазон C1:F500, а искать критерий необходимо в столбце D, то Номер_столбца указывается равным 2, т.к. порядковый номер столбца D в диапазоне C1:F500 именно 2 (С - 1-ый, D - 2-ой, Е - 3-ий и т.д.).

Номер столбца искомых значений(2) - указывается номер столбца в Диапазон_значений, значения из которого следует вывести в качестве результата. Указывается именно порядковый номер в Диапазоне_значений, а не номер столбца на листе.
Например, если в качестве Диапазона_значений указан диапазон C1:F500, а вывести в качестве результата необходимо значения из столбца E, то Номер_столбца_искомых_значений указывается равным 3, т.к. порядковый номер столбца E в диапазоне C1:F500 именно 3 (С - 1-ый, D - 2-ой, Е - 3-ий и т.д.).

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

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

Номер вхождения() - Необязательный аргумент. Может быть целым числом, либо ссылкой на ячейку с целым числом. Учитывается только если параметр Выводить первое значение равен 2. Если указанный номер вхождения превышает количество найденных значений, то функция вернет значение ошибки #Н/Д(#N/A). Если параметр Выводить_первое_значение равен 2, а номер вхождения не указан или равен 0 - функция вернет значение ошибки #ЧИСЛО!(#NUM!). Если аргумент Выводить_первое_значение не равен 2, то Номер_вхождения допускается не указывать.

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

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

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

Если по заданным условиям не найдено ни одно значение, функция возвращает значение ошибки #Н/Д(#N/A).

Предотвращение циклических зависимостей
Если в качестве диапазона_значений указан диапазон, адрес которого пересекается с ячейкой, в которой записана сама формула - то поиск на листе с формулой не производится, чтобы исключить возникновение циклических ссылок и отказа работы функции. Однако на других листах и книгах поиск будет работать.
Например, на листе "Лист1" в ячейку B2 записана функция:
=ВПР_ВСЕ_КНИГИ(Лист2!$A$1:$E$20;J2;1;2;1;1;0)
В таком виде функция должна пройти по всем листам и найти в диапазоне $A$1:$E$20 значение ячейки J2. И при поиске в листе "Лист1" ячейка В2 будет находиться внутри просматриваемого диапазона $A$1:$E$20. Т.е. функция должна найти значение в диапазоне, в котором сама же и расположена. В этом случае и возникает циклическая ссылка - функция вычисляется и должна вернуть результат. Как только результат получен - изменились входные данные функции(данные просматриваемого диапазона), что вызывает повторное вычисление функции. И так по кругу.
Чтобы предотвратить подобные ситуации функция и пропускает лист с самой функцией, продолжая поиск на других листах. Но пропускается лист только в случае пересечения аргументов функции и ячейки с самой функцией.

Примеры работы функции
Чтобы лучше понять работу и синтаксис функции, разберем несколько несложных примеров. В качестве рабочих возьмем таблицы следующего вида, которые расположены в разных листах (на каждом листе данные за свой квартал):
Таблица на листе "кв 1":

Таблица на листе "кв 2":

Теперь решим несколько задач при помощи функции ВПР_ВСЕ_КНИГИ:

Обращаю внимание на используемый в примерах диапазон просмотра - $A$1:$E$20. Он соответствует адресу таблицы с максимальным количеством строк. В реальных задача так же диапазон следует указывать на основании количества строк в самой большой таблице, либо "с запасом", чтобы все данные всех таблиц могли быть просмотрены.

1. Найдем объем закупок для контрагента "ООО "Союз"" во всех листах текущей книги(при условии, что контрагент записан в ячейке J2 того же листа, в котором записана функция):
   =ВПР_ВСЕ_КНИГИ($A$1:$E$20;J2;1;2;1;1;0)

 

2. Теперь найдем всех контрагентов на всех листах текущей книги, объем закупок для которых превышает 15 млрд.руб и при этом в качестве результата выведем имена листов, в которых найдены контрагенты:
   =ВПР_ВСЕ_КНИГИ($A$1:$E$20;">15000000000";3;1;-1;1;0;;1)
   для того, чтобы отобрать всех контрагентов, 5-ый аргумент(Выводить первое значение) указывается как "-1", а формула вводится в ячейку как формула массива тремя клавишами - Ctrl+Shift+Enter. При этом важно помнить, что перед тем как вводить формулу необходимо заранее выделить не одну ячейку, а сразу несколько ячеек(построчно) в кол-ве, достаточном для записи всех найденных значений. В нашем случае можно было выделить ячейки K2:L5, записать в них формулу =ВПР_ВСЕ_КНИГИ($A$1:$E$20;">15000000000";3;1;-1;1;0;;1) и завершить ввод сочетанием клавиш Ctrl+Shift+Enter.
   В офисе 365 вводить формулу как формулу массива нет необходимости - подробнее в статье: Динамические массивы в Excel.

 

3. Отберем всех контрагентов на всех листах всех открытых книг, объем закупок для которых превышает 15 млрд.руб и при этом в качестве результата выведем имена листов и книг, в которых найдены контрагенты:
   =ВПР_ВСЕ_КНИГИ($A$1:$E$20;">15000000000";3;1;-1;1;1;;1)
   для того, чтобы отобрать всех контрагентов, 5-ый аргумент(Выводить первое значение) указывается как "-1", а формула вводится в ячейку как формула массива тремя клавишами - Ctrl+Shift+Enter. При этом важно помнить, что перед тем как вводить формулу необходимо заранее выделить не одну ячейку, а сразу несколько ячеек(построчно) в кол-ве, достаточном для записи всех найденных значений. В нашем случае можно было выделить ячейки K2:M5, записать в них формулу =ВПР_ВСЕ_КНИГИ($A$1:$E$20;">15000000000";3;1;-1;1;1;;1) и завершить ввод сочетанием клавиш Ctrl+Shift+Enter.
   В офисе 365 вводить формулу как формулу массива нет необходимости - подробнее в статье: Динамические массивы в Excel.

 

4. Отберем всех контрагентов всех открытых книг, но только в листах с именем "1 кв", объем закупок для которых превышает 15 млрд.руб и при этом в качестве результата выведем имена листов и книг, в которых найдены контрагенты:
   =ВПР_ВСЕ_КНИГИ($A$1:$E$20;">15000000000";3;1;-1;1;1;"1 кв";1)
   для того, чтобы отобрать всех контрагентов, 5-ый аргумент(Выводить первое значение) указывается как "-1", а формула вводится в ячейку как формула массива тремя клавишами - Ctrl+Shift+Enter. При этом важно помнить, что перед тем как вводить формулу необходимо заранее выделить не одну ячейку, а сразу несколько ячеек(построчно) в кол-ве, достаточном для записи всех найденных значений. В нашем случае можно было выделить ячейки K2:M3, записать в них формулу =ВПР_ВСЕ_КНИГИ($A$1:$E$20;">15000000000";3;1;-1;1;1;"1 кв";1) и завершить ввод сочетанием клавиш Ctrl+Shift+Enter.
   В офисе 365 вводить формулу как формулу массива нет необходимости - подробнее в статье: Динамические массивы в Excel.

 

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