Как выполнить поиск VLOOKUP по нескольким листам?
VLOOKUP — одна из самых широко используемых функций в Excel для поиска и получения данных. Однако, при работе с несколькими листами, прямое использование функции VLOOKUP не подойдет, так как она работает только в пределах одного диапазона. Предположим, у меня есть три листа с данными, и я хочу получить часть соответствующих значений на основе критериев из этих трех листов. Это руководство объясняет, как реализовать многостраничный VLOOKUP в Excel различными методами.
 |  |  |  |  |
Поиск значений VLOOKUP по нескольким листам с помощью формулы массива
Поиск значений VLOOKUP по нескольким листам с помощью Kutools для Excel
Поиск значений VLOOKUP по нескольким листам с помощью обычной формулы
Поиск значений 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 для Excel предоставляет вам эффективную и простую функцию — «Многолистовой поиск», которая помогает легко выполнять запросы VLOOKUP по нескольким листам. Без необходимости использования сложных формул вы можете быстро найти и извлечь необходимые данные из нескольких листов. Всего несколькими кликами можно завершить сопоставление и интеграцию данных из нескольких таблиц, значительно повышая производительность работы. Кроме того, Kutools поддерживает сохранение ранее использованных схем, что удобно для повторного использования в будущих задачах.
После установки Kutools для Excel сделайте следующее:
1. Нажмите «Kutools» > «Супер ПОИСК» > «Многолистовой поиск», см. скриншот:
2. В диалоговом окне «Многолистовой поиск» выполните следующие действия:
- Выберите ячейки вывода и ячейки для поиска значений из разделов «Область размещения списка» и «Диапазон значений для поиска» соответственно;
- Затем нажмите кнопку «Добавить», чтобы выбрать и добавить диапазон данных из других листов один за другим в список «Диапазон данных».
- Наконец, нажмите кнопку OK.
Результат: Все совпадающие записи были возвращены, см. скриншоты:
| | | |  |
Нажмите, чтобы скачать 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 и вернуть соответствующее значение из диапазона данных с помощью функции VLOOKUP, но пробовали ли вы найти и вернуть всю строку данных на основе конкретных критериев, как показано на следующем скриншоте.
- Поиск VLOOKUP и конкатенация нескольких соответствующих значений в Excel
- Как всем известно, функция VLOOKUP в Excel может помочь нам найти значение и вернуть соответствующие данные из другого столбца, но обычно она может получить только первое относительное значение, если существует несколько совпадающих данных. В этой статье я расскажу о том, как выполнить поиск VLOOKUP и конкатенировать несколько соответствующих значений в одной ячейке или вертикальном списке.
- Поиск VLOOKUP по нескольким листам и суммирование результатов в Excel
- Предположим, у меня есть четыре листа с одинаковым форматированием, и теперь я хочу найти телевизор в столбце «Продукт» каждого листа и получить общее количество заказов по этим листам, как показано на следующем скриншоте. Как я могу решить эту проблему легким и быстрым способом в Excel?
- Поиск VLOOKUP и возврат соответствующего значения в отфильтрованном списке
- Функция VLOOKUP может помочь вам найти и вернуть первое совпадающее значение по умолчанию, будь то нормальный диапазон или отфильтрованный список. Иногда вы хотите выполнить поиск VLOOKUP и вернуть только видимое значение, если имеется отфильтрованный список. Как вы можете справиться с этой задачей в Excel?
Лучшие инструменты для повышения продуктивности в Office
Повысьте свои навыки работы в Excel с помощью Kutools для Excel и ощутите эффективность на новом уровне. Kutools для Excel предлагает более300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы выбрать функцию, которая вам нужнее всего...
Office Tab добавляет вкладки в Office и делает вашу работу намного проще
- Включите режим вкладок для редактирования и чтения в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна вместо новых отдельных окон.
- Увеличьте свою продуктивность на50% и уменьшите количество щелчков мышью на сотни ежедневно!
Все надстройки Kutools. Один установщик
Пакет Kutools for Office включает надстройки для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro — идеально для команд, работающих в разных приложениях Office.
- Комплексный набор — надстройки для Excel, Word, Outlook и PowerPoint плюс Office Tab Pro
- Один установщик, одна лицензия — настройка занимает считанные минуты (MSI-совместимо)
- Совместная работа — максимальная эффективность между приложениями Office
- 30-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек