提示:其他語言是Google翻譯的。 你可以訪問 English 版本。
登入
x
or
x
x
註冊
x

or

如何在多個工作表中查看值?

在Excel中,我們可以輕鬆應用vlookup函數來將匹配值返回到工作表的單個表中。 但是,你有沒有考慮過如何在多個工作表上查看價值? 假設我有以下三個數據范圍的工作表,現在我想根據這三個工作表中的條件獲取相應值的一部分,請參閱截圖:

DOC-VLOOKUP-多片-1 DOC-VLOOKUP-多片-2 DOC-VLOOKUP-多片-2 -2 DOC-VLOOKUP-多片-2

使用數組公式從多個工作表中查找值

使用普通公式從多個工作表中查找值


將多個工作表或csv文件合併/導入到一個工作表或工作簿中:

在您的日常工作中,將多個工作表,工作簿和csv文件合併到一個工作表或工作簿中可能是一項巨大而頭痛的工作。 但是,如果你有 Kutools for Excel,其強大的功能 - 結合,您可以將多個工作表,工作簿或csv文件快速合併到一個工作表或工作簿中。

doc結合了多個工作表-1

Kutools for Excel:比200方便的Excel加載項,可以在60天免費試用。 下載並免費試用現在!


使用數組公式從多個工作表中查找值


要使用這個數組公式,您應該給這三個工作表一個範圍名稱,請在新的工作表中列出您的工作表名稱,如下面的截圖所示:

DOC-VLOOKUP-多片-2

注意:如果有多個工作表名稱要列入單元格, 創建圖紙名稱列表 的特點 Kutools for Excel 可以幫助您列出工作簿中的所有工作表名稱,如下面的屏幕截圖所示:

DOC-VLOOKUP-多片-10

Kutools for Excel:比200方便的Excel加載項,可以在60天免費試用。 下載並免費試用現在!

1。 為這些工作表提供一個範圍名稱,選擇表單名稱,然後在中輸入一個名稱 名稱框 在編輯欄旁邊,在這種情況下,我將輸入Sheetlist作為範圍名稱,然後按 輸入 鍵。

DOC-VLOOKUP-多片-2

2. 然後你可以在你的特定單元格中輸入以下長公式: =VLOOKUP(A2,INDIRECT("'"&INDEX(Sheetlist,MATCH(1,--(COUNTIF(INDIRECT("'"&Sheetlist&"'!$A$2:$B$5"),A2)>0),0))&"'!$A$2:$B$5"),2,FALSE),然後按 Ctrl + Shift + Enter 鍵一起得到相應的值,看截圖:

DOC-VLOOKUP-多片-2

3。 然後將填充手柄向下拖動到要應用此公式的單元格,每行的所有相對值已按如下所示返回:

DOC-VLOOKUP-多片-2

注意:

在上面的公式中:

A2:是要返回其相對值的單元格引用;

Sheetlist:是我在step1中創建的工作表名稱的範圍名稱;

A2:B5:是您需要搜索的工作表的數據范圍;

2:表示您匹配的值返回的列號。


演示:使用數組公式從多個工作表中查找值

Kutools for Excel:比200方便的Excel加載項,可以在60天免費試用。 下載並免費試用現在!


使用普通公式從多個工作表中查找值

如果你不想使范圍名稱和數組公式不熟悉,這裡也有一個正常的公式來幫助你。

請在您需要的單元格中輸入以下公式: =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))) 並按下 輸入 鍵返回你想要的值,看截圖:

DOC-VLOOKUP-多片-2

然後將填充手柄向下拖動到要包含此公式的單元格區域。

筆記:

1。 在上面的公式中:

A2:是要返回其相對值的單元格引用;

Sheet1,Sheet2,Sheet3:是包含您要使用的數據的表單名稱;

A2:B5:是您需要搜索的工作表的數據范圍;

2:表示您的匹配值返回的列號

2。 為了更容易理解這個公式,實際上,長公式由幾個vlookup函數組成,並且與IFERROR函數連接。 如果你有更多的工作表,你只需要在公式之後添加vlookup函數和IFERROE。


Kutools for Excel解決了您的大多數問題,並使您的生產率提高了80%

  • 重用: 快速插入 複雜的公式,圖表 以及你以前用過的任何東西; 加密單元格 密碼; 創建郵件列表 並發送電子郵件...
  • 超級方程式酒吧 (輕鬆編輯多行文字和公式); 閱讀佈局 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
  • 合併單元格/行/列 不丟失數據; 分裂細胞含量; 組合重複的行/列...防止重複的細胞; 比較範圍...
  • 選擇複製或唯一 行; 選擇空行 (所有細胞都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇......
  • 精確複製 多個單元格而不更改公式參考; 自動創建參考 多張表; 插入項目符號,複選框等等......
  • 提取文本,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
  • 超級過濾器 (將過濾方案保存並應用到其他工作表); 高級排序 按月/週/日,頻率等; 特殊過濾器 用粗體,斜體......
  • 結合工作簿和工作表; 根據鍵列合併表; 將數據拆分為多個表格; 批量轉換xls,xlsx和PDF...
  • 超過300強大的功能。 支持Office / Excel 2007-2019和365。 支持所有語言。 在您的企業或組織中輕鬆部署。 全功能30天免費試用。
kte tab 201905

Office選項卡為Office提供選項卡式界面,使您的工作更輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,Publisher,Access,Visio和Project。
  • 在同一窗口的新選項卡中打開並創建多個文檔,而不是在新窗口中。
  • 通過50%提高您的工作效率,每天為您減少數百次鼠標點擊!
官方底部
Say something here...
symbols left.
You are guest ( Sign Up? )
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 · 10 months ago
    can you convert the formula into plain text
  • To post as a guest, your comment is unpublished.
    Kiran · 2 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 · 2 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 · 2 years ago
    when i try this foirmula its not valid
  • To post as a guest, your comment is unpublished.
    usha · 2 years ago
    this formula is not valid when i am triying in my excel
    • To post as a guest, your comment is unpublished.
      malik · 2 years ago
      try using Iferror funtion icluding vlookup..
      • To post as a guest, your comment is unpublished.
        Jon · 2 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 · 2 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 · 3 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 · 3 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 · 3 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 · 3 years ago
    sorry guys wrongly comment on wrong site
  • To post as a guest, your comment is unpublished.
    Prakash Siddhu Gaikw · 3 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. · 4 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 · 3 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 · 3 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 · 3 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