Как выполнить поиск с помощью функции ВПР сразу по нескольким рабочим листам?
ВПР — одна из самых популярных функций Excel для поиска и извлечения данных. Однако при работе с несколькими рабочими листами стандартная функция ВПР оказывается недостаточной, поскольку она ограничена одним диапазоном. Предположим, у вас есть три рабочих листа с разными диапазонами данных, и вы хотите получить соответствующие значения на основе определённых критериев сразу из этих трёх листов. В этом руководстве показано, как организовать поиск с помощью функции ВПР по нескольким листам в Excel, используя различные методы.
 |  |  |  |  |
Поиск значений по нескольким листам с помощью формулы массива
Поиск значений по нескольким листам с помощью Kutools для Excel
Поиск значений по нескольким листам с помощью обычной формулы
Поиск значений по нескольким листам с помощью формулы массива
Чтобы использовать эту формулу массива, необходимо присвоить этим трём листам имя ячейки. Перечислите имена ваших листов на новом листе, как показано на следующем снимке экрана:
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. Если искомое конкретное значение не существует, будет отображаться ошибка #Н/Д.
Поиск значений по нескольким листам с помощью Kutools для Excel
Kutools для Excel предоставляет вам эффективную и простую функцию — «Многолистовой поиск», которая помогает легко выполнять запросы ВПР по нескольким рабочим листам. Без необходимости использовать сложные формулы вы можете быстро искать и извлекать требуемые данные с нескольких листов. Всего за несколько кликов можно завершить сопоставление и объединение данных из нескольких таблиц, значительно повысив эффективность работы. Кроме того, Kutools поддерживает сохранение ранее использованных схем, что удобно для их повторного применения в будущем.
После установки Kutools для Excelвыполните следующие действия:
1. Нажмите «Kutools» > «Супер ПОИСК» > «Многолистовой поиск» (см. снимок экрана):
2. В диалоговом окне «Многолистовой поиск» выполните следующие действия:
- Выберите ячейки для вывода и ячейки со значением поиска из разделов «Область размещения списка» и «Диапазон значений поиска» соответственно;
- Затем нажмите кнопку «Добавить», чтобы поочерёдно выбрать диапазоны данных с других листов и добавить их в поле списка «Диапазон данных».
- Наконец, нажмите кнопку «OK».
Результат: возвращены все совпадающие записи — см. снимки экрана:
| | | |  |
Нажмите, чтобы скачать Kutools для 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. Чтобы лучше понять эту формулу, стоит отметить, что она состоит из нескольких функций ВПР, объединённых с помощью ЕСЛИОШИБКА. Если у вас больше рабочих листов, просто добавьте ещё одну пару ВПР и ЕСЛИОШИБКА после основной формулы.
3. Если искомое конкретное значение не существует, будет отображаться ошибка #Н/Д.
Заключение:
В этом руководстве представлены три различных метода выполнения поиска по функции ВПР по нескольким рабочим листам, учитывающих различные потребности и уровни подготовки пользователей:
- Формула массива — мощный инструмент для опытных пользователей. Формулы массива обеспечивают динамичный и гибкий многолистовой поиск без необходимости подключать дополнительные инструменты. Хотя этот метод требует определённых знаний формул, он оказывается эффективным и результативным при работе со сложными наборами данных.
- Kutools для Excel — отличный выбор для пользователей, которым нужен удобный и автоматизированный способ решения задач. Благодаря встроенным инструментам Kutools упрощает поиск по нескольким листам, экономя время и усилия, особенно для тех, кто не знаком с продвинутыми функциями Excel.
- Обычная формула: использование вложенных функций ЕСЛИОШИБКА или ЕСЛИ — практичный и простой способ выполнить поиск по нескольким листам. Этот подход идеально подходит для небольших наборов данных или когда нужно быстро найти решение без привлечения дополнительных инструментов и сложных методов.
Выбор подходящего метода зависит от того, насколько вы знакомы с Excel, от сложности задачи и доступных инструментов. Если вы хотите узнать ещё больше полезных советов и приёмов работы в Excel, на нашем сайте вас ждут тысячи обучающих материалов, которые помогут вам освоить Excel.
Другие связанные статьи:
- Поиск по функции ВПР снизу вверх в Excel
- Обычно функция ВПР ищет данные сверху вниз и возвращает первое совпадающее значение из списка. Однако иногда нужно выполнить поиск снизу вверх, чтобы получить последнее соответствующее значение. Как вы решаете такую задачу в Excel?
- ВПР и возврат всей строки / Вся строка найденного значения в Excel
- Обычно с помощью функции ВПР можно найти и вернуть совпадающее значение из диапазона данных, но пробовали ли вы когда-нибудь находить и возвращать всю строку данных на основе определённых критериев, как показано на следующем снимке экрана?
- ВПР и объединение нескольких соответствующих значений в Excel
- Как всем известно, функция ВПР в Excel помогает искать значение и возвращать соответствующие данные из другого столбца, однако обычно она может получить только первое соответствующее значение, если существует несколько совпадений. В этой статье рассказывается, как выполнять поиск по функции ВПР и объединять несколько соответствующих значений в одной ячейке или в вертикальном списке.
- ВПР по нескольким листам с суммированием результатов в Excel
- Допустим, у меня есть четыре рабочих листа с одинаковым форматированием, и теперь я хочу найти телевизор в столбце «Продукт» каждого листа и получить общее количество заказов по всем этим листам, как показано на следующем снимке экрана. Каким простым и быстрым способом можно решить эту задачу в Excel?
- ВПР и возврат совпадающего значения в отфильтрованном списке
- Функция ВПР по умолчанию позволяет находить и возвращать первое совпадающее значение независимо от того, является ли диапазон обычным или отфильтрованным. Иногда же требуется выполнять поиск и возвращать только видимое значение, если список отфильтрован. Как решить эту задачу в 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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек