Скачать файл с исходными данными, используемый в видеоуроке:

  ВПР по всем листам (43,0 КиБ, 28 899 скачиваний)

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

А когда листов больше? Можно плодить ЕСЛИ(IF) и ЕСЛИОШИБКА(IFERROR). Но это во-первых совсем не наглядно и во-вторых очень непрактично, т.к. при добавлении или удалении листов придется править всю мега-формулу. Да и при работе с количеством листов более 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. Это очень важное условие. Если формулу не вводить в ячейку как формулу массива, то необходимого результата не получить.
Попробую кратенько описать принцип работы данной формулы.

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

  ВПР по всем листам (43,0 КиБ, 28 899 скачиваний)

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

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

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

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

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

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

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

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

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

  ВПР по всем листам (43,0 КиБ, 28 899 скачиваний)

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

ВАЖНО! если в результате записи формулы получаете ошибку #ССЫЛКА!(#REF!), то скорее всего файл, из которого получаете данные, сохранен в формате xlsx(xlsm и т.п.), который содержит более 1млн. строк. А файл с формулой в раннем формате xls. Чтобы ошибки не было сохраните файл с формулой тоже в новом формате(Сохранить как - Книга Excel (.xlsx)), закройте и откройте заново. Формула должна заработать, если записана правильно.
Либо укажите фиксированный диапазон для ВПР, с количеством строк не более 65536. Вместо "'!A:B" должно получиться так: "'!A1:B60000"

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

Function VLookUpAllSheets(vCriteria As Variant, rTable As Range, lColNum As Long, Optional iPart As Integer = 1) As Variant
    Dim rFndRng As Range
    If iPart <> 1 Then iPart = 2
    For i = 1 To Worksheets.Count
        If Sheets(i).Name <> Application.Caller.Parent.Name Then
            With Sheets(i)
                Set rFndRng = .Range(rTable.Address).Resize(, 1).Find(vCriteria, , xlValues, iPart)
                If Not rFndRng Is Nothing Then
                    VLookUpAllSheets = rFndRng.Offset(, lColNum - 1).Value
                    Exit For
                End If
            End With
        End If
    Next i
End Function

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

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

45 комментариев

  1. Пример хороший, но он ищет по одному значению, а если допустим на листе 2 два одинаковых значения? например AB500154. Он выдает только первое значение, то что выше в столбце. А если нам надо оба? Как такое реализовать вообще возможно?

  2. В рамках одной книги все работает, спасибо. А когда беру данные из другого файла, то результат выдает только из первого листа, последующие - #Н/Д
    Можете подсказать в чем дело?

  3. Спасибо, разобрался. Имя книги ставил в формулу, перед названием листа и была вот такая проблема. Прописал имя книги в списке листов для поиска и все заработало. Очень нужная формула, еще раз спасибо...

      1. Да так же, как в обычной ссылке. В общем-то проще всего просто ссылку на другую книгу в отдельной ячейке сделать и оттуда скопировать без равно прям часть ссылки с именем книги и листа. Типа того:
        [Книга.xls]Лист1

  6. Добрый день.

    Не понимаю вот чего, в примере значения для А2 только на лист1.

    Формула СЧЁТЕСЛИ(ДВССЫЛ("'"&$E$2:$E$5&"'!A1:A50");A2) должна считать и значения на других листах, если они там будут? В примере их там нет, но я добавил на лист 3 и лист 4, но формула всё равно считает только с листа 1, т.е. с первого по списку. Если переименовать лист 1 на лист 3, то будет считать из лист3, т.е. тоже первого листа из списка.

    (формулу массива включил).

    Как сделать, чтобы данные считались по всем листам? Что я не учёл?

    1. Все верно. Статья описывает как найти значение среди нескольких листов. Но это же ВПР и возвращать она будет всегда только одно первое совпадение. Так же как и с одним листом. Это надо понимать. Она не вернет Вам все совпадения - исключительно первое найденное. Как только она находит первое совпадение - она завершает поиск даже не глядя, есть ли это значение дальше.

      1. Дмитрий, я не про всю формулу сейчас, а про её часть, СЧЁТЕСЛИ. Счёт если в описанном мной случае должен считать данные только с первого листа или со всех? У меня считает только с первого (((

        1. Значит действительно нет совпадений. Или что-то делаете не так или ищете не там. Если бы совпадение было - СЧЁТЕСЛИ подсчитал бы и на других листах. Убедитесь, что название листов совпадают и искомое значение точно входит в диапазон для поиска.

          1. Дмитрий, правда, не получается. Посмотрите сами, пожалуйста.
            Формула такая:
            {=СЧЁТЕСЛИ(ДВССЫЛ("'"&$E$2:$E$5 &"'!$A$2:$A$50";ИСТИНА);A2)}
            Считает значения только по первому листу из диапазона E2:E5
            по остальным листам - ну не считает! ((((((((((((
            А очень надо...

  7. Вячеслав, как Вы думаете - как можно помочь? Вы вставили формулу, которая описана у меня в статье. Ваших данных я не вижу. Как Вы там включили формулу массива - тоже. Что там вместо имен листов - тоже не вижу. Не совсем понятно, что за формула: ДВССЫЛ("'"&$E$2:$E$5 &"'!$A$2:$A$50";ИСТИНА). Что она должна по-Вашему считать? Я вот не понимаю зачем здесь ИСТИНА в ДВССЫЛ.
    Плюс сама по себе СЧЁТЕСЛИ не будет работать в нужном виде. Правильно будет так:
    =СУММ(СЧЁТЕСЛИ(ДВССЫЛ("'"&$E$2:$E$5&"'!A1:A50");A2))

  8. А если одно и то же название встречается для на разных листах?
    По идее, это уже другая задача, но есть ощущение, что она может быть решена с применением этого же приема.. Или я ошибаюсь и тут уже нужно придумывать что-то другое?...

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

    1. 1. Непонятно какую ошибку выдает. Если НД - значит не найдено
      2. Если ошибка #ЗНАЧ!(#VALUE!) - формулу точно как формулу массива вводите? Это обязательное условие.

      Плюс хотелось бы уточнить что именно в $E$2:$E$5 у Вас записано. Там должны быть перечисления имен книг и листов. И не должно быть пустых ячеек. Так же данные в этих книгах должны быть расположены в столбцах А и В(для приведенной формулы).

  10. Искомое значение Найденное значение В каком листе нашлось Список листов, в которых искать
    AB500154 #ССЫЛКА! [Книга1.xlsх]Лист2 [Книга1.xlsх]Лист2
    AB500155 40 Лист3 Лист3
    AB500156 225 Лист4 Лист4
    AB500157 16546 Лист5 Лист5
    Я работаю полностью в Вашем примере с разницей в том, что значение "AB500154" нет а рабочей книге - оно есть в открытой книге [Книга1.xls]Лист2!A:B(строка вторая). В столбцах $E$2:$E$5 в список включен и [Книга1.xls]Лист2. Конечно же,формулы Ваши не менялись.

Добавить комментарий

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.