有群友在群裡問如何在多個工作表中查詢數據,當時我太忙,沒仔細考慮,只是建議用VLOOKUP+INDIRECT應該能解決。等忙完後自己仔細考慮了一下,這兩個函數結合其它函數是可以解決這個問題的,現在將我解決的思路寫出來供大家思考。
=VLOOKUP(A2,LOOKUP(1,0/COUNTIF(INDIRECT({"河北區";"北京區";"天津區"}&"!A:A"),A2),INDIRECT({"河北區";"北京區";"天津區"}&"!A:B")),2,0)
模擬了一些數據,查詢表中的姓名在三個工作表中的任意一個,需要得到每位員工對應的銷售額。
VLOOKUP函數的用法不再解釋,它由四個參數組成:
VLOOKUP(要查找的數據,在哪個區域查找,返回區域第幾列,精確匹配或模糊匹配)
這四個參數中,我們可以解決三個,公式是在查詢表的B2單元格中輸入的。
VLOOKUP(A2,在哪個區域查找,2,0)
在哪個區域查找,這個我們是不確定的,有可能是在“=河北區!A:B”,也有可能是在“=北京區!A:B”,還有可能是在“=天津區!A:B”。因為是在三個工作表中,我們不確定這名員工到底在哪個工作表。所以我們需要來確定他在哪個工作表中。
因為姓名都在各工作表中第一列,所以要查找的姓名在三個區域中,分別為“=河北區!A:A”、 “=北京區!A:A”、 “=天津區!A:A”。
如果查找員工有沒有在某一個工作表中,我們可以用COUNTIF來查找,如果結果等於1,代表這個工作表中有這名員工,如果等於0則代表這個工作表中沒有這名員工。但現在我們需要在三個工作表中查找,可以用INDIRECT函數來引用。
所以可以編輯公式:COUNTIF(INDIRECT({"河北區";"北京區";"天津區"}&"!A:A"),A2),它返回由1和0組成的數組,如{1;0;0},這個結果代表員工在河北區。
我們知道1在什麼位置就是哪個工作表,但是EXCEL不知道,所以我們得讓它知道1所以位置對應的工作表。
此時我們用到LOOKUP(1,0/數組或公式結果為數組,返回結果的區域或數組)這種經典的用法,公式如下:
LOOKUP(1,0/COUNTIF(INDIRECT({"河北區";"北京區";"天津區"}&"!A:A"),A2),INDIRECT({"河北區";"北京區";"天津區"}&"!A:B"))
這樣就能得到姓名所在的區域,例如 “河北區!A:B”。
這樣的話,就是VLOOKUP的基礎用法了:VLOOKUP(A2, 河北區!A:B,2,0)
我們要注意的是公式是數組公式,要用CTRL+SHIFT+ENTER鍵結束輸入。
本例中公式可以作為多表查詢的一個通用公式,其實我們通過姓名來返回數值型數據,還可以用SUMIF+INDIRECT,本例還可以用數組公式,公式不再解釋。
=SUM(SUMIF(INDIRECT({"河北區";"北京區";"天津區"}&"!A:A"),A2,INDIRECT({"河北區";"北京區";"天津區"}&"!B:B")))