Перейти к основному содержанию

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

Автор: Сяоян Последнее изменение: 2025 июля 04 г.

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

пример данных 1 пример данных 2 пример данных 3 стрелку вправо пример данных 4

Значения 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 вместе, чтобы получить первое соответствующее значение, затем перетащите маркер заполнения вниз к ячейкам, к которым вы хотите применить эту формулу, все относительные значения каждой строки будут возвращены следующим образом:

введите формулу, чтобы получить результат

Примечание:

1. В приведенной выше формуле:

  • A2: ссылка на ячейку, относительное значение которой вы хотите вернуть;
  • Sheetlist: это имя диапазона имен рабочих листов, которые я создал на шаге 1;
  • A2:B6: диапазон данных рабочих листов, в которых необходимо выполнить поиск;
  • 2: указывает номер столбца, в котором возвращается совпадающее значение.

2. Если конкретное значение, которое вы ищете, не существует, будет отображаться значение # N / A.


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

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

Kutools for Excel предлагает более 300 расширенных функций для оптимизации сложных задач, повышения креативности и эффективности. Интеграция с возможностями ИИKutools точно автоматизирует задачи, упрощая управление данными. Подробная информация о Kutools для Excel...         Бесплатная пробная версия...

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

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

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

  • Выберите выходные ячейки и ячейки искомых значений из разделов «Выходной диапазон» и «Искомые значения» по отдельности;
  • Затем нажмите кнопку «Добавить», чтобы выбрать и добавить диапазон данных с других листов по одному в список «Диапазон данных».
  • Наконец, нажмите кнопку ОК.
    установите параметры в диалоговом окне «ПОИСК на нескольких листах»
Tип: Если вы хотите заменить значение ошибки #N/A другим текстовым значением, вам просто нужно установить флажок «Заменить значение ошибки #N/A указанным значением», а затем ввести нужный текст.
задать параметры для значений ошибок

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

пример данных 1 пример данных 2 пример данных 3 стрелку вправо получить результат с помощью kutools

Нажмите, чтобы загрузить 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. Затем перетащите дескриптор заполнения вниз к диапазону ячеек, который вы хотите содержать эту формулу, см. Снимок экрана:

Примечание:

1. В приведенной выше формуле:

  • A2: ссылка на ячейку, относительное значение которой вы хотите вернуть;
  • Sheet1, Sheet2, Sheet3: имена листов, которые включают данные, которые вы хотите использовать;
  • A2:B6: диапазон данных рабочих листов, в которых необходимо выполнить поиск;
  • 2: указывает номер столбца, в котором возвращается совпадающее значение.

2. Для более легкого понимания этой формулы, длинная формула состоит из нескольких функций vlookup и связана с функцией ЕСЛИОШИБКА. Если у вас есть другие листы, вам просто нужно добавить функцию vlookup в сочетании с IFERROE после формулы.

3. Если конкретное значение, которое вы ищете, не существует, будет отображаться значение # N / A.


Вывод:

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

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

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


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

  • Значение соответствия Vlookup снизу вверх в Excel
  • Обычно функция Vlookup может помочь вам найти данные сверху вниз, чтобы получить первое совпадающее значение из списка. Но иногда вам нужно выполнить vlookup снизу вверх, чтобы извлечь последнее соответствующее значение. Есть ли у вас какие-нибудь хорошие идеи для решения этой задачи в Excel?
  • Vlookup и возврат всей / всей строки совпадающего значения в Excel
  • Обычно вы можете vlookup и вернуть соответствующее значение из диапазона данных с помощью функции Vlookup, но пытались ли вы когда-нибудь найти и вернуть всю строку данных на основе определенных критериев, как показано на следующем снимке экрана.
  • Vlookup и объединение нескольких соответствующих значений в Excel
  • Как все мы знаем, функция Vlookup в Excel может помочь нам найти значение и вернуть соответствующие данные в другом столбце, но в целом она может получить только первое относительное значение, если есть несколько совпадающих данных. В этой статье я расскажу о том, как выполнить vlookup и объединить несколько соответствующих значений только в одной ячейке или вертикальном списке.
  • Vlookup на нескольких листах и ​​суммирование результатов в Excel
  • Предположим, у меня есть четыре листа с одинаковым форматированием, и теперь я хочу найти телевизор в столбце Product на каждом листе и получить общее количество заказов на этих листах, как показано на следующем снимке экрана. Как я могу решить эту проблему с помощью простого и быстрого метода в Excel?
  • Vlookup и возврат соответствующего значения в отфильтрованном списке
  • Функция VLOOKUP может помочь вам найти и вернуть первое совпадающее значение по умолчанию, будь то обычный диапазон или отфильтрованный список. Иногда вам просто нужно выполнить vlookup и вернуть только видимое значение, если есть отфильтрованный список. Как бы вы могли справиться с этой задачей в Excel?

Лучшие инструменты для офисной работы

🤖 Kutools AI Помощник: Революционный анализ данных на основе: Интеллектуальное исполнение   |  Генерировать код  |  Создание пользовательских формул  |  Анализ данных и создание диаграмм  |  Вызов функций Kutools...
Популярные опции: Найдите, выделите или определите дубликаты   |  Удалить пустые строки   |  Объедините столбцы или ячейки без потери данных   |   Раунд без формулы ...
Супер поиск: Множественный критерий VLookup    VLookup с несколькими значениями  |   VLookup по нескольким листам   |   Нечеткий поиск ....
Расширенный раскрывающийся список: Быстрое создание раскрывающегося списка   |  Зависимый раскрывающийся список   |  Выпадающий список с множественным выбором ....
Менеджер столбцов: Добавить определенное количество столбцов  |  Переместить столбцы  |  Переключить статус видимости скрытых столбцов  |  Сравнить диапазоны и столбцы ...
Рекомендуемые функции: Сетка Фокус   |  Просмотр дизайна   |   Большой Формулный Бар    Менеджер книг и листов   |  Библиотека ресурсов (Авто текст)   |  Выбор даты   |  Комбинировать листы   |  Шифровать/дешифровать ячейки    Отправлять электронные письма по списку   |  Суперфильтр   |   Специальный фильтр (фильтровать жирным шрифтом/курсивом/зачеркиванием...) ...
15 лучших наборов инструментов12 Текст Инструменты (Добавить текст, Удалить символы, ...)   |   50+ График Тип (Диаграмма Ганта, ...)   |   40+ Практических Формулы (Рассчитать возраст по дню рождения, ...)   |   19 Вносимые Инструменты (Вставить QR-код, Вставить изображение из пути, ...)   |   12 Конверсия Инструменты (Числа в слова, Конверсия валюты, ...)   |   7 Слияние и разделение Инструменты (Расширенные ряды комбинирования, Разделить клетки, ...)   |   ... и более

Улучшите свои навыки работы с Excel с помощью Kutools for Excel и почувствуйте эффективность, как никогда раньше. Kutools for Excel предлагает более 300 расширенных функций для повышения производительности и экономии времени.  Нажмите здесь, чтобы получить функцию, которая вам нужна больше всего...


Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!