Перейти к содержимому

Как выполнить поиск VLOOKUP по нескольким листам?

Author: Xiaoyang Last Modified: 2025-06-05

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

sample data 1 sample data 2 sample data 3 arrow right sample data 4

Поиск значений VLOOKUP по нескольким листам с помощью формулы массива

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

Поиск значений VLOOKUP по нескольким листам с помощью обычной формулы


Поиск значений VLOOKUP по нескольким листам с помощью формулы массива

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

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

define a range name for the sheets list

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

enter a formula to get the result

Примечания:

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

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

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


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

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

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

После установки Kutools для Excel сделайте следующее:

1. Нажмите «Kutools» > «Супер ПОИСК» > «Многолистовой поиск», см. скриншот:

2. В диалоговом окне «Многолистовой поиск» выполните следующие действия:

  • Выберите ячейки вывода и ячейки для поиска значений из разделов «Область размещения списка» и «Диапазон значений для поиска» соответственно;
  • Затем нажмите кнопку «Добавить», чтобы выбрать и добавить диапазон данных из других листов один за другим в список «Диапазон данных».
  • Наконец, нажмите кнопку OK.
    set options in the LOOKUP Across Multiple Sheets dialog box
Совет: Если вы хотите заменить значение ошибки #N/A другим текстовым значением, просто отметьте опцию «Заменить не найденные значения (#N/A) указанным значением» и введите нужный текст.
set options for error values

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

sample data 1 sample data 2 sample data 3 arrow right get the result by using kutools

Нажмите, чтобы скачать Kutools для Excel и попробовать бесплатно прямо сейчас!


Поиск значений VLOOKUP по нескольким листам с помощью обычной формулы

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

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, соединенных с функцией IFERROR. Если у вас больше листов, вам просто нужно добавить функцию VLOOKUP в сочетании с IFERROR после формулы.

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


Вывод:

Это руководство предоставило три различных метода выполнения VLOOKUP по нескольким листам, удовлетворяя различные потребности пользователей и уровни навыков:

  • Формула массива: мощный метод для продвинутых пользователей, формулы массива позволяют выполнять динамические и гибкие поиски по нескольким листам без необходимости использования дополнительных инструментов. Хотя требуется некоторый опыт работы с формулами, этот метод эффективен и результативен для сложных наборов данных.
  • Kutools для Excel: отличный выбор для пользователей, ищущих удобное автоматизированное решение. Благодаря встроенным инструментам Kutools упрощает процесс поиска по нескольким листам, экономя время и усилия, особенно для тех, кто менее знаком с продвинутыми функциями Excel.
  • Обычная формула: использование вложенных функций IFERROR или IF является практичным и простым способом выполнения поиска по нескольким листам. Этот подход идеально подходит для небольших наборов данных или когда вам нужна быстрая помощь без использования дополнительных инструментов или продвинутых техник.

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


Больше связанных статей:

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

Лучшие инструменты для повышения продуктивности работы с Office

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

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


Office Tab добавляет вкладки в Office и делает вашу работу намного проще

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