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

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


Если необходимо найти какое-либо значение в большой таблице очень часто применяется функция ВПР. Но ВПР работает только с одной таблицей и нет никакой возможности средствами самой функции просмотреть искомое значение на нескольких листах. Если поиск необходимо осуществить только по двум листам, то можно схитрить:
=ВПР(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 249 скачиваний)

ДВССЫЛ нам нужна для преобразования текстового представления ссылок на листы в действительные. Подробно не буду останавливаться на принципе работы ДВССЫЛ, просто приведу этапы вычислений:
СЧЁТЕСЛИ(ДВССЫЛ("'"&$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 249 скачиваний)

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

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

  1. Такой вопрос а если есть 45 листов нужно найти артикул.
    Пример 2 столбца 1- Артикул; 2-число
    на 45 листах данный артикул может встретится почти в каждом, как тогда суммировать значения 2 столбца?

  2. Ну что за примеры!!!! Везде "А-А" ячейки. А нельзя, что ли, сделать так, чтобы на разных листах были разные столбцы? А то непонятно, к чему относится "Диапазон "'!A1:A50"; "'!A:B" - диапазон для аргумента ВПР - Таблица" - это все на каких листах????

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

      1. Дмитрий, здравствуйте!
        Не получается с VLookUpAllSheets, выдает #ЗНАЧ или #Н/Д.
        С ВПР я дружу и даже по вашему методу освоил поиск по двум листам.
        А вот с VLookUpAllSheets никак не получается, не могу понять в чем ошибка.
        Как и куда я могу файл прикрепить для просмотра?

  3. Здравствуйте! Большое спасибо за эту формулу - оня для меня прям свет в окошке)))
    а я еще попыталась внести условие в раздел столбца, чтобы впр не по номеру столбца возвращала, а искала определенный столбец в каждом листе. Возможно ли вообще такое реализовать для этой формулы для нескольких листов?
    Для простой впр с одной таблицей это отлично работает: =ВПР(А3;Лист2!$A$1:$W$16;ПОИСКПОЗ(Лист1!I$2;Лист2!$A$2:$W$2;0);0)
    Но я никак не осмыслю, что нужно прописать в столбце вместо цифры, чтобы и на нескольких листах искался определенный столбец, и именно из него возвращалось бы значение.
    К слову, на каждом листе этот столбец имеет одинаковое название, но он разный по счету, поэтому мне не подходит просто указать его номер...

  4. здравствуйте! применима ли данная формула в следующем случае: Существует книга с несколькими листами, на первом листе есть таблица (столбец ФИО и несколько столбцов, в которые необходимо искать значения с других листов), на других листах таблицы следующие (первый столбец ФИО другие заполняются одинаково (вводится "провести" это значение и нужно переносить на первый лист) с этим все понятно, но существует "но": ФИО человека может фигурировать во всех таблица, а может только в одной или в двух и так далее

        1. Сергей, с чего Вы взяли, что она должна возвращать все значения? Ну или проблема описана непонятно. Лучше создайте тему на форуме - там можно приложить файл(как правило файл-пример значительно увеличивает шансы на решение проблемы).

  5. Если бы указанная формула работала для нескольких книг(файлов) было бы КРУТО но т.к. для нескольких книг(файлов) необходимо ее значительно усложнять то вводить поиск одновременно и по книгам и по листам(массив) проще ВПР (*;ЕСЛИ(ЕНД***. Большое спасибо помогло разобраться.

  6. Добрый день!
    Подскажите, пжл, как будет выглядеть формула
    =ВПР(A1;ЕСЛИ(ЕНД(ВПР(A1;Лист2!A1:B10;2;0));Лист3!A1:B10;Лист2!A1:B10);2;0)
    для поиска по 3м листам?
    Заранее спасибо!

    1. vasiliy, как минимум можно было подключить логику и справку по функциям :) Элементарная проверка условий:
      =ВПР(A1;ЕСЛИ(ЕНД(ВПР(A1;Лист2!A1:B10;2;0));ЕСЛИ(ЕНД(ВПР(A1;Лист3!A1:B10;2;0));Лист4!A1:B10;Лист3!A1:B10);Лист2!A1:B10);2;0)
      другой вопрос, насколько это все будет наглядно и удобно в дальнейшем.

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

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