Lost your password?


Хитрости »
Основные понятия (27)
Сводные таблицы и анализ данных (10)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (23)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (68)
Разное (43)
Баги и глюки Excel (5)

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


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

  ВПР по всем листам (43,0 KiB, 27 384 скачиваний)


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

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

Так же можно искать по нескольким листам разных книг, а не только по нескольким листам одной книги. Для этого необходимо будет в списке листов вместе с именами листов добавить имена книг в квадратных скобках:[Книга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 указать "при", то совпадением будет считаться и слово "прибыль"(первый буквы совпадают) и "неприятный"(в середине встречается "при"). Но в этом случае знаки * и ? будут восприниматься "как есть". Может пригодиться, если в искомом тексте присутствуют символы звездочки и вопросительного знака и надо найти совпадения, учитывая эти символы.

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


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

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

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

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

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

      • Рамиля:

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

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

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

  3. Евгений:

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

  4. Гость:

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

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

      • olegfetis@yandex.ru:

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

  5. Алина:

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

  6. Сергей:

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

    • Сергей, зависит от того, что Вы хотите получить в итоге. Если только одно(первое найденное) значение - то формула сработает. Если все - то нет.

      • Сергей:

        не понятно почему, но формула работает неправильно... возвращает не все значения из заданных диапазонов

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

  7. Анастасия:

    Добрый день! А если нужна сумма по всем листам? SUMIF вместо COUNTIF, конечно, не помог. В массивах я не сильна.

  8. Макс:

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

  9. vasiliy:

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

    • 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)
      другой вопрос, насколько это все будет наглядно и удобно в дальнейшем.

      • vasiliy:

        Большое спасибо!
        Это формулу напишется всего 1 раз, а дальше раз в месяц будут манятся значения одних и тех же переменных.

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

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


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

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2024 Excel для всех   Войти