Перейти до основного матеріалу

Як переглядати значення на декількох робочих аркушах?

У Excel ми можемо легко застосувати функцію vlookup для повернення відповідних значень в одній таблиці робочого аркуша. Але чи замислювались ви коли-небудь про те, як здійснити пошук значення на кількох робочих аркушах? Припустимо, у мене є такі три робочі аркуші з діапазоном даних, і тепер я хочу отримати частину відповідних значень на основі критеріїв з цих трьох аркушів, див. Скріншоти:

Значення перегляду з декількох аркушів із формулою масиву

Значення перегляду з кількох аркушів із нормальною формулою

Значення Vlookup з декількох аркушів із чудовою функцією


Значення перегляду з декількох аркушів із формулою масиву

Щоб використовувати цю формулу масиву, ви повинні вказати цим трьом робочим аркушам назву діапазону. Будь ласка, перелічіть імена своїх аркушів на новому аркуші, наприклад, наведеному нижче скріншоті:

1. Дайте цим робочим аркушам назву діапазону, виберіть імена аркушів і введіть ім’я в полі Ім'я Box який поруч із рядком формул, у цьому випадку, я введу Sheetlist як назву діапазону, а потім натисніть Що натомість? Створіть віртуальну версію себе у ключ

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: - посилання на клітинку, якому потрібно повернути його відносне значення;
  • Список аркушів: це назва діапазону імен робочого аркуша, які я створив на кроці 1;
  • A2: B6: - діапазон даних робочих аркушів, які вам потрібні для пошуку;
  • 2: вказує номер стовпця, що повертається відповідне значення.

2. Якщо конкретного значення, яке ви шукаєте, не існує, відобразиться значення # N / A.


Демонстрація: значення Vlookup із декількох аркушів із формулою масиву


Перегляд відповідних записів із декількох аркушів у Excel

Вам може бути клопітно шукати відповідні значення з декількох аркушів, але, з Kutools для Excel's ПЕРЕГЛЯД за кількома аркушами Утиліта, ви можете швидко вирішити це завдання без будь-яких складних формул.           Клацніть, щоб завантажити Kutools для Excel!

Kutools для Excel: із понад 300 зручними надбудовами Excel, які можна безкоштовно спробувати без обмежень протягом 30 днів. Завантажте та безкоштовно пробну версію зараз!


Значення перегляду з кількох аркушів із нормальною формулою

Якщо ви не хочете вводити назву діапазону і не знайомі з формулою масиву, тут також є звичайна формула, яка вам допоможе.

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 спільно з IFERROE після формули.

3. Якщо конкретного значення, яке ви шукаєте, не існує, відобразиться значення # N / A.


Значення Vlookup з декількох аркушів із чудовою функцією

Можливо, наведені вище дві формули занадто важкі для використання, тут я представив потужну функцію, Kutools для Excel's ПЕРЕГЛЯД за кількома аркушами, за допомогою цієї функції ви можете швидко впоратися з цією роботою, якщо є десятки або сотні аркушів.

Порада:Щоб застосувати це ПЕРЕГЛЯД за кількома аркушами По-перше, вам слід завантажити Kutools для Excel, а потім швидко та легко застосувати функцію.

після установки Kutools для Excel, будь ласка, зробіть так:

1. Клацання Кутулс > Супер пошук > ПЕРЕГЛЯД за кількома аркушами, див. скріншот:

2, в ПЕРЕГЛЯД за кількома аркушами діалоговому вікні, виконайте такі дії:

  • Виберіть комірки значень підстановки та вихідні комірки з Значення пошуку та діапазон виводу розділ;
  • Потім виберіть і додайте діапазон даних з інших аркушів у Діапазон даних вікно списку.

примітки: Якщо ви хочете замінити значення помилки # N / A іншим текстовим значенням, вам просто потрібно перевірити Замініть значення помилки # N / A на вказане значення , а потім введіть потрібний текст.

3. Потім натисніть кнопку OK , всі відповідні записи були повернуті на декількох робочих аркушах, див. скріншоти:

Натисніть, щоб завантажити Kutools для Excel та безкоштовну пробну версію зараз!


Більше відносних статей:

  • Значення відповідності Vlookup знизу до верху в Excel
  • Зазвичай функція Vlookup може допомогти вам знайти дані зверху вниз, щоб отримати перше відповідне значення зі списку. Але, іноді, вам потрібно переглядати знизу вгору, щоб отримати останнє відповідне значення. Чи є у вас якісь ідеї для вирішення цього завдання в Excel?
  • Перегляд і об'єднання декількох відповідних значень в Excel
  • Як ми всі знаємо, функція Vlookup в Excel може допомогти нам шукати значення і повернути відповідні дані в інший стовпець, але загалом, воно може отримати перше відносне значення лише за наявності декількох відповідних даних. У цій статті я розповім про те, як переглядати та об’єднувати кілька відповідних значень лише в одній комірці або вертикальному списку.
  • Перегляд кількох аркушів та підсумок результатів у Excel
  • Припустимо, у мене є чотири аркуші, які мають однакове форматування, і тепер я хочу знайти телевізор у стовпці Product на кожному аркуші та отримати загальну кількість замовлень на цих аркушах, як показано на наступному знімку екрана. Як я міг вирішити цю проблему простим і швидким методом у Excel?
  • Перегляд та повернення відповідного значення у відфільтрованому списку
  • Функція VLOOKUP може допомогти вам знайти та повернути перше відповідне значення за замовчуванням, будь то нормальний діапазон або відфільтрований список. Іноді ви просто хочете здійснити пошук і повернути лише видиме значення, якщо є відфільтрований список. Як ви могли вирішити це завдання в Excel?

Найкращі інструменти продуктивності офісу

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

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

Опис


Вкладка Office Передає інтерфейс із вкладками в Office і значно полегшує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
Comments (17)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
can you convert the formula into plain text
This comment was minimized by the moderator on the site
hi, in multiple use sheet , iwant to value increase by serial. =VLOOKUP($C10,'[apri.xlsx]ahm'!$C$10:$L$10,6,FALSE) =VLOOKUP($C10,'[april.xlsx]ahm'!$C$10:$L$10,7,FALSE) autometically value can change in formula
This comment was minimized by the moderator on the site
Hi, I am also having problem using this formula to compile the values from multiple sheet.
This comment was minimized by the moderator on the site
when i try this foirmula its not valid
This comment was minimized by the moderator on the site
this formula is not valid when i am triying in my excel
This comment was minimized by the moderator on the site
try using Iferror funtion icluding vlookup..
This comment was minimized by the moderator on the site
Here is an example of what that would look like. =IFERROR(VLOOKUP(A1,Sheet1!A:B,2,FALSE),IFERROR(VLOOKUP(A1,Sheet2!A:B,2,FALSE),IFERROR(VLOOKUP(A1,Sheet3!A:B,2,FALSE),"Item Not Found!")))


Essetially look in Sheet1 for this value, if you can't find it, look in Sheet2. If it isn't there look in Sheet3, and if after all of that it can't be found, tell me that the value could not be found.
This comment was minimized by the moderator on the site
Hi Sir, Please help me I have open a excel book in which more than 50 on sheet data available but summary available at sheet one but I want pick value from every sheet. So please help out how can i pick value please sir do urgently. I am waiting for your response definietly I will appreciate your response. regard's Mohd Shehzaad Khan
This comment was minimized by the moderator on the site
I want to bring mutiple sheets informatiom into one sheets lke pivot table and i want them to be connect..same structures..i did by consolidation but the column department (one field)numbers are not spreading out colums wise (other fields are spread over the columns)..can anyone help plz..
This comment was minimized by the moderator on the site
=IFERROR(VLOOKUP($A2,Sheet1!$A$2:$B$5,2,FALSE),IFERROR(VLOOKUP($A2,Sheet2!$A$2:$B$5,2,FALSE),VLOOKUP($A2,Sheet3!$A$2:$B$5,2,FALSE))) in above formula, instead 2(column number) i want match criteria with main sheet. please help me
This comment was minimized by the moderator on the site
Hi, I am trying to look up multiple sheets to another sheets... can you help me?
This comment was minimized by the moderator on the site
sorry guys wrongly comment on wrong site
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations