Поиск значений по всем листам/книгам
Достаточно часто я встречал на форумах вопрос: "А можно ли сделать так, чтобы ВПР искала значения на всех листах?". Можно. Но формула будет длинной и совершенно неудобной, т.к. надо будет ВПР дублировать для каждого листа, да еще с обработкой ошибок(либо применять формулу массива - подробнее о способах можно почитать в этой статье). А если листов много? А если еще и все книги надо просматривать? Тут формулам туго придется. ВПР_ВСЕ_КНИГИ ищет критерий на всех листах активной книги или всех листах всех открытых книг. Также можно указать конкретное имя листа, на котором просматривать данные. Плюс функция может вывести имена листов и книг, на которых это значение было найдено.
Мастер функций-Категория "MulTEx"- ВПР_ВСЕ_КНИГИ
Сумма/Поиск/Функции - Массивы - ВПР_ВСЕ_КНИГИ
Синтаксис:
=ВПР_ВСЕ_КНИГИ(
=ВПР_ВСЕ_КНИГИ(
=ВПР_ВСЕ_КНИГИ(
=ВПР_ВСЕ_КНИГИ(
Так же данный аргумент может принимать в качестве критерия символы сравнения
">0" - будут отобраны ячейки, значения ячеек критериев для которых больше нуля;">=2" - будут отобраны ячейки, значения ячеек критериев для которых больше или равно двум;"<0" - будут отобраны ячейки, значения ячеек критериев для которых меньше нуля;"<=60" - будут отобраны ячейки, значения ячеек критериев для которых меньше или равно 60;"<>0" - будут отобраны ячейки, значения ячеек критериев для которых не равно нулю;"<>" - будут отобраны ячейки, значения ячеек критериев для которых не пустые.
Вместо нуля может быть любое число или текст. Так же можно добавить ссылку на ячейку со значением:
Например, если в качестве
Например, если в качестве
- 0 - выводит последнее найденное значение - функция вернет только одно последнее найденное в указанном массиве значение;
- 1 - выводит первое найденное значение - функция вернет только одно первое найденное в указанном массиве значение;
- 2 - выводит значение под указанным номером вхождения(если выбран, то необходимо указать Номер вхождения) - функция вернет только одно значение, порядковый номер которого будет равен указанному Вами аргументу Номер вхождения. Т.е. если необходимо вывести только 7-ое найденное значение, указываем Номер вхождения - 7. В качестве данного аргумента можно использовать данные в ячейках. Если номер вхождения не указан - функция вернет значение: Не указан номер вхождения!;
- -1 - выводит все найденные значения (для этого требуется ввести функцию как формулу массива: сначала выделить с сразу несколько ячеек, в которые следует поместить результат. В строку формул ввести данную функцию и одновременно нажать сочетание клавиш
Ctrl +Shift +Enter ). Подробнее про формулы массива можно прочитать здесь.
Если для аргумента ВывестиИмяКнигиЛиста указано значение 1 или ИСТИНА, то следует выделить три столбца, а не один. В первый будут выведены сами найденные значения, во второй имена книг, в третий имена листов.
Если используется версия Office 365 или новее - функцию допускается вводить только в одну ячейку и она распространит все найденные значения на остальные ячейки сама, выделять заранее несколько ячеек нет необходимости.
По умолчанию поиск значений производится по всем листам.
Если по заданным условиям не найдено ни одно значение, функция возвращает значение ошибки
Если в качестве диапазона_значений указан диапазон, адрес которого пересекается с ячейкой, в которой записана сама формула - то поиск на листе с формулой не производится, чтобы исключить возникновение циклических ссылок и отказа работы функции. Однако на других листах и книгах поиск будет работать.
Например, на листе "Лист1" в ячейку B2 записана функция:
В таком виде функция должна пройти по всем листам и найти в диапазоне
Чтобы предотвратить подобные ситуации функция и пропускает лист с самой функцией, продолжая поиск на других листах. Но пропускается лист только в случае пересечения аргументов функции и ячейки с самой функцией.
Чтобы лучше понять работу и синтаксис функции, разберем несколько несложных примеров. В качестве рабочих возьмем таблицы следующего вида, которые расположены в разных листах (на каждом листе данные за свой квартал):
Таблица на листе "кв 1":
Таблица на листе "кв 2":
Теперь решим несколько задач при помощи функции ВПР_ВСЕ_КНИГИ:
Обращаю внимание на используемый в примерах диапазон просмотра -
. Он соответствует адресу таблицы с максимальным количеством строк. В реальных задача так же диапазон следует указывать на основании количества строк в самой большой таблице, либо "с запасом", чтобы все данные всех таблиц могли быть просмотрены. $A$1:$E$20
- Найдем объем закупок для контрагента "ООО "Союз"" во всех листах текущей книги(при условии, что контрагент записан в ячейке J2 того же листа, в котором записана функция):
=ВПР_ВСЕ_КНИГИ( $A$1:$E$20 ;J2 ;1;2;1;1;0) - Теперь найдем всех контрагентов на всех листах текущей книги, объем закупок для которых превышает 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. - Отберем всех контрагентов на всех листах всех открытых книг, объем закупок для которых превышает 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. - Отберем всех контрагентов всех открытых книг, но только в листах с именем "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.
Так же см.:
ВПР_МН
Здравствуйте, Дмитрий.
у меня возникла задача, которая, по моему мнению, отлично бы решилась с помощью функции ВПР_ВСЕ_КНИГИ. Попыталась разобраться с помощью Вашей статьи. Но никак не хочет работать. Результатом выдает #ИМЯ? Не подскажите в чем может быть ошибка?