Хитрости »
Основные понятия (22)Здесь собраны статьи, в которых разъясняются базовые понятия работы в Excel и VBA, а так же проблемы, с которыми сталкивается большинство начинающих
Сводные таблицы и анализ данных (5) Раздел поможет изучить сводные таблицы и научиться их использовать "на полную"
Графики и диаграммы (4) Раздел поможет научиться создавать диаграммы и графики в Excel, в том числе нестандартные
Работа с VB проектом (10) С помощью статей раздела вы научитесь создавать процедуры программно и выполнять различные операции с объектами самого VBA
Power BI и Power Query (5) Здесь собраны статьи, раскрывающие различные возможности мощнейшего инструмента для визуализаций бизнесс-процессов Power BI и надстройки для Excel Power Query
Условное форматирование (5) Этот раздел поможет поближе познакомиться с Условным форматированием на примерах различных ситуаций
Списки и диапазоны (5) Статьи, посвященные работе не только с выпадающими списками, но и с диапазонами и хитростями их применения в рабочих файлах
Макросы(VBA процедуры) (59) Статьи раздела направлены на изучение VBA с детальным разбором кодов. Множество статей с примерами кодов под всевозможные ситуации с комментариями и пояснениями
Разное (34) Собраны статьи, которые не подходят ни под одну из представленных выше категорий или входят сразу в несколько. Но эти статье не менее полезные!

ВПР с поиском по нескольким листам

 

Если необходимо найти какое-либо значение в большой таблице очень часто применяется функция ВПР. Но ВПР работает только с одной таблицей и нет никакой возможности средствами самой функции просмотреть искомое значение на нескольких листах. Если поиск необходимо осуществить только по двум листам, то можно схитрить:
=ВПР(A1;ЕСЛИ(ЕНД(ВПР(A1;Лист2!A1:B10;2;0));Лист3!A1:B10;Лист2!A1:B10);2;0)

А когда листов больше? Можно плодить ЕСЛИ. Но это во-первых совсем не наглядно и во-вторых очень непрактично, т.к. при добавлении или удалении листов придется править всю мега-формулу. Да и при работе с количеством листов более 10 есть большой шанс, что длина формулы выйдет за пределы допустимой.

Есть небольшой прием, который поможет искать значение в указанных листах. Для начала необходимо создать на листе список листов книги, в которых искать значение. В приложенном к статье примере они записаны в диапазоне $E$2:$E$5.
=ВПР(A2;ДВССЫЛ("'"&ИНДЕКС($E$2:$E$5;ПОИСКПОЗ(ИСТИНА;СЧЁТЕСЛИ(ДВССЫЛ("'"&$E$2:$E$5 &"'!A1:A50");A2)>0;0))&"'!A:B");2;0)

Формула вводится в ячейку как формула массива - т.е. сочетанием клавиш Ctrl+Shift+Enter. Это очень важное условие. Если формулу не вводить в ячейку как формулу массива, то необходимого результата не получить.
Попробую кратенько описать принцип работы данной формулы.

Перед чтением дальше советую скачать пример:

  Tips_All_VlookupAllSheets.xls (37,5 KiB, 3 768 скачиваний)

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

в результате чего мы получаем позицию имени листа в массиве имен листов $E$2:$E$5, с помощью ИНДЕКС получаем имя листа и подставляем это имя уже к ДВССЫЛ, а она в ВПР:
=ВПР(A2;ДВССЫЛ("'"&ИНДЕКС({"Лист2";"Лист3";"Лист4";"Лист5"};1)&"'!A:B");2;0)
=ВПР(A2;ДВССЫЛ("'Лист2'!A:B");2;0)
=ВПР(A2;'Лист2'!A:B;2;0)

Что нам и требовалось. Теперь если в книгу будут добавлены еще листы, то необходимо будет всего лишь дописать их к диапазону $E$2:$E$5 и при необходимости этот диапазон расширить. Так же можно задать диапазон $E$2:$E$5 как динамический и тогда необходимость в правке формулы отпадет вовсе.

Используемые в формуле величины:
A2 - ссылка на ячейку с искомым значением. Т.е. указывается то значение, которое требуется найти на листах.

$E$2:$E$5 - диапазон с именами листов, в которых требуется осуществлять поиск указанного значения (A1).

Диапазон "'!A1:A50" - это диапазон, в котором СЧЁТЕСЛИ ищет совпадения. Поэтому указывается только один столбец данных. При необходимости следует расширить или изменить. Можно указать так же "'!A:A", но при этом следует учитывать, что указание целого столбца может привести к значительному увеличению времени выполнения функции. Поэтому имеет смысл просто задать диапазон с запасом, например "'!A1:A10000".

"'!A:B" - диапазон для аргумента ВПР - Таблица. В первом столбце этого диапазона на каждом из указанных листов ищется указанное значение (A2). При нахождении возвращается значение из указанного столбца. Читать подробнее про ВПР>>

В примере к статье так же можно посмотреть формулу, которая для каждого значения подставляет имя листа, в котором это значение было найдено.

Скачать пример:

  Tips_All_VlookupAllSheets.xls (37,5 KiB, 3 768 скачиваний)

Так же можно искать по нескольким листам разных книг, а не только по нескольким листам одной книги. Для этого необходимо будет в списке листов вместе с именами листов добавить имена книг в квадратных скобках:[Книга1.xlsb]Май
[Книга1.xlsb]Июнь
[Книга2.xlsb]Май
[Книга2.xlsb]Июнь

Перечисленные книги обязательно должны быть открыты


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

Функция попроще, чем ВПР - последний аргумент(интервальный_просмотр) выполняет несколько иные, чем в ВПР функции. Хотя полагаю немногие его используют в классическом варианте.
rTable - указывается таблица для поиска значений(как в стандартной ВПР)
vCriteria - указывается ссылка на ячейку или текстовое значение для поиска
lColNum - указывается номер столбца в таблице rTable, значение из которого необходимо вернуть - может быть ссылкой на столбец - СТОЛБЕЦ().
iPart - указывается метод просмотра. Если не указан, либо указана цифра 1, то поиск осуществляется по полному совпадению с ячейкой. Но в таком варианте допускается применение подстановочных символов * и ?. Если указано значение, отличное от 1, то совпадение будет отбираться по части вхождения. Если в vCriteria указать "при", то совпадением будет считаться и слово "прибыль"(первый буквы совпадают) и "неприятный"(в середине встречается "при"). Но в этом случае знаки * и ? будут восприниматься "как есть". Может пригодиться, если в искомом тексте присутствуют символы звездочки и вопросительного знака и надо найти совпадения, учитывая эти символы.

Так же см.:
Что такое формула массива
Как найти значение в другой таблице или сила ВПР
Как подсчитать количество повторений
Динамические именованные диапазоны


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

Поиск по меткам

Access Multex Outlook Power BI Power Query и Power BI VBA работа в редакторе VBA управление кодами Бесплатные надстройки Дата и время Диаграммы и графики Записки Защита Защита данных Интернет Картинки и объекты Листы и книги Макросы и VBA Настройка Поиск данных Почта Программы Работа с приложениями Работа с файлами Разработка приложений Сводные таблицы Списки Тренинги и вебинары Финансовые Форматирование Формулы и функции Функции Excel Функции VBA Ячейки и диапазоны акции MulTEx вебинар ссылки статьи тренинг
Обсуждение: оставлено 27 коммент.
  1. Рамиля:

    Описка -вместо [Книга1.xls]Лист2 везде у меня [Книга1.xlsх]Лист2.

    • Без файла даже гадать не хочется - 100% где-то ошибаетесь и даже не замечаете этого. А как найдете будете смеяться над тем, насколько очевидна и нелепа ошибка. Вышлите мне на почту файл - посмотрю и тогда можно будет сказать в чем причина. Только высылайте и книгу с формулой и книгу со значениями([Книга1.xlsx]Лист2 которая)

      • Рамиля:

        "100% где-то ошибаетесь и даже не замечаете этого. А как найдете будете смеяться над тем, насколько очевидна и нелепа ошибка".)После Ваших слов все удалила,заново создала все связи и все заработало! Но ошибку свою так и не поняла.( Спасибо!

  2. Александр:

    Все красиво, достойно работает!
    Вопрос: как добавить поиск позиции и по столбцу?

Поделитесь своим мнением

Комментарии, не имеющие отношения к комментируемой статье, могут быть удалены без уведомления и объяснения причин. Если есть вопрос по личной проблеме - добро пожаловать на Форум


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code class="" title="" data-url=""> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre class="" title="" data-url=""> <span class="" title="" data-url="">

Логин
Наши партнеры
Перейти
Перейти
Счетчики
Анализ сайта

Яндекс.Метрика
© 2016 Excel для всех  Войти