vlookup函數最高階的應用：多表多檔案查詢

關於vlookup函數的教學本站已發過入門+初級+進階+高階的。在網上也可以搜到很多關於vlookup的教學，具體詳見：vlookup函數 - vlookup函數的使用方法_vlookup函數的操作範例。但這些教學中都缺了vlookup的一個關鍵應用：跨多表多檔案查詢。今天本文將講述了vlookup函數最高階的應用：多表多檔案查詢。

一、跨多工作表查詢

【例】工資表模板中，每個部門一個表。

在查詢表中，要求根據提供的姓名，從銷售~綜合5個工作表中查詢該員工的基本工資。

分析：

如果，我們知道A1是銷售部的，那麼公式可以寫為：

=VLOOKUP(A2,銷售!A:G,7,0)

如果，我們知道A1可能在銷售或財務表這2個表中，公式可以寫為：

=IFERROR(VLOOKUP(A2,銷售!A:G,7,0),VLOOKUP(A2,財務!A:G,7,0))

意思是，如果在銷售表中查詢不到(用iferror函數判斷)，則去財務表中再查詢。

如果，我們知道A1可能在銷售、財務或服務表中，公式可以再次改為：

=IFERROR(VLOOKUP(A2,銷售!A:G,7,0),IFERROR(VLOOKUP(A2,財務!A:G,7,0),VLOOKUP(A2,!A:G,7,0)))

意思是從銷售表開始查詢，前面的查詢不到就到後面的表中查詢。

如果，有更多的表，如本例中5個表，那就一層層的套用下去。這也是我們今天提供的VLOOKUP多表查詢

方法1：

=IFERROR(VLOOKUP(A2,服務!A:G,7,0),IFERROR(VLOOKUP(A2,人事!A:G,7,0),IFERROR(VLOOKUP(A2,綜合!A:G,7,0),IFERROR(VLOOKUP(A2,財務!A:G,7,0),IFERROR(VLOOKUP(A2,銷售!A:G,7,0),"無此人資訊")))))

------------------------------------------

如果你想簡化一下公式，以適合在更多的表中查謁，再提供一個思路，只是公式簡單了，理解起來卻難了。這裡你只需要學會怎麼修改公式套用就可以了。

方法2：

=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT({"銷售";"服務";"人事";"綜合";"財務"}&"!a:a"),A2),{"銷售";"服務";"人事";"綜合";"財務"})&"!a:g"),7,0)

你只需要修改以下部分，就可以直接套用

A2：查詢的內容

{""}：大括號內是要查詢的多個工作表名稱，用逗號分隔

a:a :本例是姓名在各個表中的A列，如果在B列則為b:b

a:g ：vlookup查詢的區域

7：是vlookup第3個引數，相對應的列數。你懂的。

公式思路說明：

1、確定員工是在哪個表中。這裡利用countif函數可以多表統計來分雖計算各個表中該員工存在的個數。

2、利用lookup(1,0/(陣列),陣列) 結構取得工作表的名稱

3、利用indirec函數把字串轉換成單元格參照。

4、利用vlookup查詢。

二、跨多檔案查詢

跨多個檔案查詢，估計你搜遍網路也找不到，這也是首次編寫跨多檔案查詢公式。其實原理和跨多表查詢一樣，也是借助lookup等函數實現。

資料夾中有N個倉庫產品表格，需要在“查詢”檔案完成查詢

倉庫表樣式

在查詢表中設定公式，根據產品名稱從指定的檔案中sheet1工作表查詢入庫單價

=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT("["&{"倉庫1";"倉庫2";"倉庫3"}&".xlsx]sheet1!a:a"),A2),"["&{"倉庫1";"倉庫2";"倉庫3"}&".xlsx]sheet1")&"!a:b"),2,0)。

補充：

vlookup函數的多檔案查詢，同樣可以用iferror+vlookup的模式，公式雖然長，但容易理解且公式不容易出錯。如果你有一定基礎，倒可以試試第2種方法。

另外，如果工作表或excel檔案有幾十個或更多，就需要使用宏表函數Get.workbook來獲取所有工作表的名稱和用Files獲取所有excel檔名稱，然後應用到公式中。