Как создать поиск значений в Excel функцией ВПР по нескольким листам?

Добрый день!

Сегодня я хочу расширить границы использования функции ВПР и научить вас использовать эту функцию, что бы произвести поиск значений в Excel по нескольким листам вашего рабочего файла.

Как вы знаете или еще не знаете, я напоминаю, в чистом виде функция ВПР производит поиск только в одной таблице, а о том, что бы чистыми возможностями функции произвести поиск необходимого значения в нескольких листиках, это невозможно. Но, тем не менее, при большой необходимости можно схитрить и произвести поиск по двум листам. Для этого используем возможности логической функции ЕСЛИ и формула поиска будет выглядеть приблизительно так:

=ВПР (C3 ;ЕСЛИ (ЕНД (ВПР (C3 ;Таблица2!C3:D7 ;2; 0)); Таблица3! C3:D7 ;Таблица2! C3:D7 );2; 0).

Но такой вариант работает только с 2 таблицами, а в случае, когда листов больше, нужно увеличивать количество вложений для функции ЕСЛИ. Но при этом:

• во-первых, если много листов, то есть огромный шанс что длина формулы будет больше допустимого размера и перестанет работать;
• во-вторых, это просто непрактично, так как при работе такой мега-формулы возникает значительно риск ошибок и при изменениях придётся переделывать формулу.

Но, как всегда, выход есть. Рассмотрим небольшую хитрость с помощью, которой и будем искать в нужных листах. Начнем работу с создания перечня листов нашей книги, где будем производить поиск значений. В нашем случае это диапазон $E$3:$E$7. Теперь для получения значения в столбик «Найденная стоимость» согласно условию в столбике «Номенклатуру которую ищем» нам нужна формула:

{=ВПР (A3; ДВССЫЛ («’»&ИНДЕКС ($E$3:$E$7; ПОИСКПОЗ (ИСТИНА; СЧЁТЕСЛИ (ДВССЫЛ («’»&$E$3:$E$7 &»‘!C1:C50″) ;A3)> 0;0)) &»‘!C:D» );2;0)}

Как видите, формула выделена фигурными скобками, это означает, что её необходимо вводить как формулу массива с помощью горячего сочетания клавиш Ctrl+Shift+Enter. Это самое главное условие правильной работы этой формулы в других случаях она не будет работать. Формула объемная и требует объяснения принципа её работы. Функция ДВССЫЛ необходима, что бы конвертировать текстовые отображения ссылок на листы нашей книги в действительные. Сам принцип работы функции ДВССЫЛ, я описывать не буду, рассмотрим только необходимую формулу для этапа нашего вычисления: СЧЁТЕСЛИ (ДВССЫЛ («’»&$E$3:$E$7 &»‘! C1:C50″); A3).

Как следствие, при вычислении этого блока у нас формируется массив из некоторого количества значений, которые мы ищем, и которые повторяются на листах нашего списка, и имеет вид: СЧЁТЕСЛИ({2;0;0;0};A3). О работе функции СЧЁТЕСЛИ я писал отдельно и более подробно.

Следующим рассматриваемым блоком нашей композиции будет формула: ПОИСКПОЗ (ИСТИНА; СЧЁТЕСЛИ (ДВССЫЛ («’»&$E$3:$E$7 &»‘! C1:C50″); A3)>0;0), которая и работает с указанным выше блоком такого вида: ПОИСКПОЗ (ИСТИНА; СЧЁТЕСЛИ ({2;0;0;0}; A3)>0;0). Вследствие чего мы узнаем, какую позицию занимает имя листа в нашем массиве списке листов $E$3:$E$7. Теперь же при помощи функции ИНДЕКС мы получаем название листа, и можем применить его имя в структуре функции ДВССЫЛ, а она передаст полученное значение уже далее функции ВПР. Пошагово это будет выглядеть так:

1. =ВПР (A3; ДВССЫЛ («’»&ИНДЕКС ({«Таблица1″; « Таблица2»; « Таблица3»; «Таблица4»; «Таблица5»};1) &»‘! C:D»); 2;0);
2. =ВПР(A2;ДВССЫЛ(«’Таблица1′! C:D»);2;0);
3. =ВПР(A2;’Таблица1′!C:D;2;0).

Ну, вот мы и получили универсальную формулу, которая производит поиск значений в Excel и является очень гибкой и удобной. В случаях, когда возникнет необходимость добавить в рабочую книгу еще листы с таблицами, то необходимо всего на всего прописать их в списке рабочих листов $E$3:$E$7, изменив предварительно ее размер или попросту изначально сделать ее динамическим диапазоном, и править формулу будет не нужно.

Для большего удобства в столбике С, в графе «Где было найдено» можно прописать формулу которая будет наглядно показывать где была взята цифра, с какой таблицы вы получили значение, что значительно облегчает поисковую навигацию. Для получения названия таблицы необходима формула:

{=ИНДЕКС ($E$3:$E$7; ПОИСКПОЗ (ИСТИНА; СЧЁТЕСЛИ (ДВССЫЛ («’»&$E$3 :$E$7&»‘! C1:C50″); A3) >0;0))}

Поиск по нескольким листам с помощью макроса VBA

Для тех, кто хочет производить поиск значений в Excel по своей рабочей книге с помощью макросов или просто сделать рутинную операцию более автоматической предлагаю воспользоваться прописанной функцией пользователя, которая будет искать необходимое значение во всех, без исключения, даже в скрытых, листах рабочей книги, в которую вы ее пропишете. Макрос был найден на сайте excel— vba.ru, который любит такие фишки.

Функция будет иметь следующий вид:

Расшифруются аргументы написанной функции так:

• rTable – прописывается таблица, как в обыкновенной функции ВПР, для поиска значений;
• vCriteria – аргумент, который указывает любое текстовое значение или ссылка на ячейку, которая содержит значение для поиска;
• lColNum – прописывается тот номер столбика из аргумента rTable, значение в котором нам необходимо изъять, возможно, использовать ссылку на столбик с помощью функции СТОЛБЕЦ;
• iPart – аргумент, в котором прописываем необходимый метод просмотра. Когда аргумент не указан или указан аргумент равно 1, в таком случае будет проводиться поиск с полным совпадением значений в ячейках. В таких случаях есть возможность применить символы подстановки: «*» и «?». Если же, в аргументе указано другое значение кроме 1, функция будет искать, и отбирать значения при частичном вхождении.

Я надеюсь, что поиск значений в Excel функцией ВПР по нескольким листам у вас получился, и вы могли быстро собрать нужные данные в ваших таблицах, а также научились создавать удобные и классные отчёты. Если у вас есть чем дополнить меня пишите комментарии, я буду их ждать с нетерпением, ставьте лайки и делитесь полезной статьей в соц.сетях!

Не забудьте подкинуть автору на кофе…