Как выполнить функцию ВПР на нескольких листах?

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

Значения VLOOKUP из нескольких рабочих листов с помощью формулы массива

Значения VLOOKUP из нескольких листов с помощью Kutools для Excel

Значения ВПР из нескольких рабочих листов по обычной формуле

Значения VLOOKUP из нескольких рабочих листов с помощью формулы массива

Чтобы использовать эту формулу массива, вы должны дать этим трем листам имя диапазона, перечислите имена ваших листов на новом листе, например, как показано на следующем снимке экрана:

1. Дайте этим рабочим листам имя диапазона, выберите имена листов и введите имя в поле «Имя», которое находится рядом со строкой формул, в данном случае я введу Sheetlist в качестве имени диапазона, а затем нажму клавишу Enter.

2. А затем вы можете ввести следующую длинную формулу в свою конкретную ячейку:

=VLOOKUP(A2,INDIRECT("'"&INDEX(Sheetlist,MATCH(1,--(COUNTIF(INDIRECT("'"&Sheetlist&"'!$A$2:$B$6"),A2)>0),0))&"'!$A$2:$B$6"),2,FALSE)

3. Затем нажмите клавиши Ctrl + Shift + Enter вместе, чтобы получить первое соответствующее значение, затем перетащите маркер заполнения вниз к ячейкам, к которым вы хотите применить эту формулу, все относительные значения каждой строки будут возвращены следующим образом:

Значения VLOOKUP из нескольких листов с помощью Kutools для Excel

Kutools for Excel предоставляет вам эффективную и простую функцию - "LOOKUP Across Multiple Sheets", помогающую вам легко выполнять запросы VLOOKUP на нескольких листах. Без необходимости в сложных формулах вы можете быстро искать и извлекать требуемые данные из нескольких листов. Всего несколькими щелчками мыши вы можете выполнить сопоставление и интеграцию данных из нескольких таблиц, значительно повышая эффективность работы. Кроме того, Kutools поддерживает сохранение ранее использованных схем, что делает их удобными для непосредственного использования в будущих задачах.

После установки Kutools для Excel, пожалуйста, сделайте так:

1. Нажмите «Kutools» > «Super Lookup» > «LOOKUP Across Multiple Sheets», см. снимок экрана:

2. В диалоговом окне «ПРОСМОТР на нескольких листах» выполните следующие операции:

• Выберите выходные ячейки и ячейки искомых значений из разделов «Выходной диапазон» и «Искомые значения» по отдельности;
• Затем нажмите кнопку «Добавить», чтобы выбрать и добавить диапазон данных с других листов по одному в список «Диапазон данных».
• Наконец, нажмите кнопку ОК.
  

Tип: Если вы хотите заменить значение ошибки #N/A другим текстовым значением, вам просто нужно установить флажок «Заменить значение ошибки #N/A указанным значением», а затем ввести нужный текст.

Результат: Все соответствующие записи были возвращены, см. скриншоты:

Нажмите, чтобы загрузить Kutools for Excel и бесплатную пробную версию сейчас!

Значения ВПР из нескольких рабочих листов по обычной формуле

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

1. Введите в нужную ячейку следующую формулу:

=IFERROR(VLOOKUP($A2,Sheet1!$A$2:$B$6,2,FALSE),IFERROR(VLOOKUP($A2,Sheet2!$A$2:$B$6,2,FALSE),VLOOKUP($A2,Sheet3!$A$2:$B$6,2,FALSE)))

2. Затем перетащите дескриптор заполнения вниз к диапазону ячеек, который вы хотите содержать эту формулу, см. Снимок экрана:

Вывод:

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

• Формула массива: мощный метод для продвинутых пользователей, формулы массива позволяют динамически и гибко выполнять поиск по нескольким листам без необходимости в дополнительных инструментах. Хотя это требует некоторых знаний формул, это эффективно и действенно для сложных наборов данных.
• Kutools для Excel: Отличный выбор для пользователей, ищущих удобное автоматизированное решение. Благодаря встроенным инструментам Kutools упрощает процесс поиска на нескольких листах, экономя время и усилия, особенно для тех, кто не так хорошо знаком с расширенными функциями Excel.
• Нормальная формула: использование вложенных формул ЕСЛИОШИБКА или ЕСЛИ — практичный и простой способ выполнения многолистового поиска. Этот подход идеально подходит для небольших наборов данных или когда вам нужно быстрое решение без дополнительных инструментов или продвинутых методов.

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

Больше относительных статей: