Как в экселе сделать поиск по листам

Как в экселе сделать поиск по листам
Как в экселе сделать поиск по листам
Как в экселе сделать поиск по листам
Как в экселе сделать поиск по листам
Как в экселе сделать поиск по листам

VPR v neskolkih listah 1 Как создать поиск значений в Excel функцией ВПР по нескольким листам?     Добрый день!

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

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

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

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

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

     Но, как всегда, выход есть. Рассмотрим небольшую хитрость с помощью, которой и будем искать в нужных листах. Начнем работу с создания перечня листов нашей книги, где будем производить поиск значений. В нашем случае это диапазон $E:$E. VPR v neskolkih listah 2 Как создать поиск значений в Excel функцией ВПР по нескольким листам?    Теперь для получения значения в столбик «Найденная стоимость» согласно условию в столбике «Номенклатуру которую ищем» нам нужна формула:

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

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

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

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

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

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

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

        {=ИНДЕКС ($E:$E; ПОИСКПОЗ (ИСТИНА; СЧЁТЕСЛИ (ДВССЫЛ («'»&$E :$E&"'! C1:C50"); A3) >0;0))} VPR v neskolkih listah 4 Как создать поиск значений в Excel функцией ВПР по нескольким листам?

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

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

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

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

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

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

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

Статья помогла? Поделись ссылкой с друзьями, твитни или лайкни!

Как в экселе сделать поиск по листам Как в экселе сделать поиск по листам Как в экселе сделать поиск по листам Как в экселе сделать поиск по листам Как в экселе сделать поиск по листам Как в экселе сделать поиск по листам Как в экселе сделать поиск по листам Как в экселе сделать поиск по листам Как в экселе сделать поиск по листам Как в экселе сделать поиск по листам Как в экселе сделать поиск по листам

Читать далее:




Как сделать и оформить канал на ютубе




Как сделать крем на вафельные трубочки




Как упаковать ремень своими руками




Схемы вязания спицами кофточек для малышек




Открытка с днем рождения другу с машинами