Note: The other languages of the website are Google-translated. Back to English
Увійти  \/ 
x
or
x
Реєстрація  \/ 
x

or

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

У 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 для Excel вирішує більшість ваших проблем і збільшує продуктивність на 80%

  • Повторне використання: Швидко вставте складні формули, діаграми і все, що ви використовували раніше; Шифрувати комірки з паролем; Створити список розсилки та надсилати електронні листи ...
  • Супер формула бар (легко редагувати кілька рядків тексту та формули); Макет читання (легко читати та редагувати велику кількість комірок); Вставте у відфільтрований діапазон...
  • Об’єднати клітинки / рядки / стовпці без втрати даних; Вміст розділених комірок; Об'єднати повторювані рядки / стовпці... Запобігання дублюючим клітинам; Порівняйте діапазони...
  • Виберіть Повторюваний або Унікальний Рядки; Виберіть Пусті рядки (усі клітинки порожні); Супер знахідка та нечітка знахідка у багатьох робочих зошитах; Випадковий вибір ...
  • Точна копія Кілька клітинок без зміни посилання на формулу; Автоматичне створення посилань на кілька аркушів; Вставте кулі, Прапорці та інше ...
  • Витяг тексту, Додати текст, Видалити за позицією, Видаліть пробіл; Створення та друк проміжних підсумків підкачки; Перетворення вмісту комірок та коментарів...
  • Супер фільтр (зберегти та застосувати схеми фільтрів до інших аркушів); Розширене сортування за місяцем / тижнем / днем, частотою та іншим; Спеціальний фільтр жирним, курсивом ...
  • Поєднайте робочі зошити та робочі аркуші; Об’єднати таблиці на основі ключових стовпців; Розділіть дані на кілька аркушів; Пакетне перетворення xls, xlsx та PDF...
  • Понад 300 потужних функцій. Підтримує Office / Excel 2007-2019 та 365. Підтримує всі мови. Простота розгортання на вашому підприємстві чи в організації. Повна функція 30-денної безкоштовної пробної версії. 60-денна гарантія повернення грошей.
вкладка kte 201905

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

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
дно офісної таблиці
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    kathy · 3 years ago
    can you convert the formula into plain text
  • To post as a guest, your comment is unpublished.
    Kiran · 4 years ago
    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
  • To post as a guest, your comment is unpublished.
    Sunil Gyawali · 4 years ago
    Hi, I am also having problem using this formula to compile the values from multiple sheet.
  • To post as a guest, your comment is unpublished.
    usha · 4 years ago
    when i try this foirmula its not valid
  • To post as a guest, your comment is unpublished.
    usha · 4 years ago
    this formula is not valid when i am triying in my excel
    • To post as a guest, your comment is unpublished.
      malik · 4 years ago
      try using Iferror funtion icluding vlookup..
      • To post as a guest, your comment is unpublished.
        Jon · 4 years ago
        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.
  • To post as a guest, your comment is unpublished.
    mohd shehzaad khan · 4 years ago
    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
  • To post as a guest, your comment is unpublished.
    Sam · 5 years ago
    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..
  • To post as a guest, your comment is unpublished.
    Aruana R · 5 years ago
    =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
  • To post as a guest, your comment is unpublished.
    Grace · 5 years ago
    Hi,

    I am trying to look up multiple sheets to another sheets... can you help me?
  • To post as a guest, your comment is unpublished.
    Prakash Siddhu Gaikw · 5 years ago
    sorry guys wrongly comment on wrong site
  • To post as a guest, your comment is unpublished.
    Prakash Siddhu Gaikw · 5 years ago
    very 3rd class services of this site do not purchase any kind of product.if your a son of your father than repay may money back to my a/c.already mail sent to sajid.
  • To post as a guest, your comment is unpublished.
    Achyutanand. · 6 years ago
    Hiii Dude,

    I am trying to loookup with new worksheets to multiple sheets but i have geeting eroor with this formula please get me solutions.

    my formula is = =VLOOKUP(B17,INDIRECT("'"&INDEX(List,MATCH(1,--(COUNTIF(INDIRECT("'"&List&"[Daily Collection Report-2014-15.xlsx]Modification Pmt'!$D$2B2:AZ2000"),B17)>0),0))&"[Daily Collection Report-2014-15.xlsx]Modification Pmt'!$D$2B2:AZ2000"),2,FALSE)
    • To post as a guest, your comment is unpublished.
      Gerhard · 5 years ago
      Hi,

      I have multiple spreadsheets, I want to lookup a cell value based on matching the name of the Tab.

      can you help please
    • To post as a guest, your comment is unpublished.
      achyutanand · 5 years ago
      [quote name="Achyutanand."]Hiii Dude,

      I am trying to loookup with new worksheets to multiple sheets but i have geeting eroor with this formula please get me solutions.

      my formula is = =VLOOKUP(B17,INDIRECT("'"&INDEX(List,MATCH(1,--(COUNTIF(INDIRECT("'"&List&"[Daily Collection Report-2014-15.xlsx]Modification Pmt'!$D$2B2:AZ2000"),B17)>0),0))&"[Daily Collection Report-2014-15.xlsx]Modification Pmt'!$D$2B2:AZ2000"),2,FALSE)[/quote]
      a

      hi please give me excel sheet
      • To post as a guest, your comment is unpublished.
        kedar · 5 years ago
        =IFERROR(VLOOKUP(A2,Sheet1!$G$2:H5,2,0),IFERROR(VLOOKUP(Summary!A2,Sheet2!$G$2:H5,2,0),IFERROR(VLOOKUP(Summary!A2,Sheet3!$G$2:H5,2,0),"NOT")))
        Lookup Value Result
        A 100 100
        B 200 200
        C 300 300
        D 400 400
        D 400 400
        G 325 325
        H 425 425
        I 150 150
        A 100 100
        K 350 350
        L 450 450
        sheet1 Data
        A 100
        B 200
        C 300
        D 400
        Data 2
        E 125
        F 225
        G 325
        H 425
        Data 3
        I 150
        J 250
        K 350
        L 450