多表查詢數據

與EXCEL打交道也好幾年了，你是不是還停留在經常性簡單而重複的工作呢？每月忙的時候天天加班，空閑的時候盯著手機刷刷刷，??，既然如此，輕鬆的時候就應該琢磨下怎樣提升效率，比如規範好自己的數據源，設計一套模板，直接更換數據源就能出來你想要的很多報表……

這樣你可能就不會加那麼多班了。好了，看看下面幾位群友的問題，希望能對各位有所啟發！

先給大家展示下效果，具體界面自己去美化，我們只講怎麼實現這個功能：

為了演示方便，我對數據進行了簡化，群友的表是12個月的工資都有。

如果每個月的工資格式都相同，那就很好辦，只要根據名字和月份在對應的表中查詢就可以了。

D4中公式：

=VLOOKUP($A$4,"1月"!$B:$J,3,0)

以上公式在往後拉的時候，第三個參數返回的列序號，你是一個個手工改的嗎？肯定有人是，那有沒有什麼辦法可以不用手工改呢？

D4至J4公式中第三個參數對應的是3-9，所以我們用返回列號的函數column（C1）—column（j1）直接表達

所以公式可以修改成：

=VLOOKUP($A$4,"1月"!$B:$J,column(C1),0)，這樣直接往後拉就不需要手工去修改第三個參數了。

但是往下拉時第二個參數都是"1月"!$B:$J（大家注意引用其他表中數據時的表達方式）我們需要往下拉時X月也能跟著改變，怎麼辦呢？其實只需要讓1變成2，3，4，……就可以了，那我們可以用返回行數的函數row來表達：

=row（A1）&"月！B:J"

函數返回來{"1月!B:J"}，這是個字元串，我們需要把他轉換成引用，所以用indirect函數

=indirect（row（A1）&"月！B:J"）

終於OK了，整合公式得到：

=VLOOKUP($A$4,INDIRECT(ROW(A1)&"月!B:J"),COLUMN(C1),0)

直接向右向下就可以了！

上面的兩步套路在別的嵌套中經常使用，所以要徹底領悟。

下拉列表效果的實現非常簡單，利用數據驗證（以前版本就數據有效性）

但這位群友跟我說二月份的工資里多了一列扣款，所以總表就按照2月的格式，1、3月份沒這項可以空著，如果我們繼續用VL公式，往後拉公式就會出現對應列序號的錯亂。

比如一月的數據實際工資應返回第9列，而2月里因為多了一列所以應該返回第10列，這時我們該怎麼辦呢？

公式（由群友咫尺天涯提供，謝謝！）：=iferror(VLOOKUP($A$4,INDIRECT(ROW(A1)&"月!B:k"),MATCH(D$3,INDIRECT(ROW(A1)&"月!B2:k2"),0)),"")

用match函數返回統計表中的列欄位和每個月明細表列欄位去匹配後的相對位置來表達應該返回的列序號，這樣比如2月份對應的實際工資相對姓名在第10列，那就返回第10列。思路挺好，大家仔細體會！強調一點，統計表中的列欄位一定要囊括工資表的所有列欄位，這樣數據才能全部查找過來。

上面例子中的查詢說白了還不叫多表查詢，畢竟1月的數據對應在1月的工資表中，而下面這個，姓名有可能存在與一辦、二辦、三辦任意一個辦事處中，這種情況該如何查詢呢？

如果我們知道姓名存在於哪個辦事處的話，那直接用VLookup公式就可以了。

思路一：

在不知道姓名存在哪個辦事處的情況下，我們就用iferror函數：

如果在一辦，那就到一般去查，否則在嵌套iferror，如果在二辦就到二辦查，否則就在三辦查。

C4中公式如下：

=IFERROR(VLOOKUP($A$4,一辦!$A:$H,2,0),IFERROR(VLOOKUP($A$4,二辦!$A:$H,2,0),VLOOKUP($A$4,三辦!$A:$H,2,0)))

規規矩矩的公式，只要邏輯清楚，在嵌套幾個也沒問題，就是長了點,??！

思路二：

如果我們能把姓名找出來是在哪個表中，就直接用vlookup就可以了。

統計總表中的姓名在分表中出現的次數就知道姓名在哪個辦事處了。如果統計結果是0，說明該姓名不在此表中，否則就是在。

=COUNTIF(INDIRECT({"一辦";"二辦";"三辦"}&"!A:A"),總表!A4)

（與第一個例子中引用各月的工作表時的方法有異曲同工之處，這就是為什麼要大家必須掌握的原因）

然後用lookup找出姓名到底在哪個辦事處

=LOOKUP(1,0/COUNTIF(INDIRECT({"一辦";"二辦";"三辦"}&"!A:A"),總表!A4),{"一辦";"二辦";"三辦"}&"!A:h")

經典的LOOKUP的用法

最後用vlookup直接查找

=VLOOKUP($A$4,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT({"一辦";"二辦";"三辦"}&"!A:A"),$A$4),{"一辦";"二辦";"三辦"}&"!A:h")),COLUMN(G1),0)

老套路又出現了吧！

今天的內容理解起來有一定難度，現在已經是深夜12：04了，此篇文章無論是row、column還是indirect、lookup以及數組的應用我認為都是精髓，不想一有問題就求人的話建議你用心學學！

分享是一種美德，共同學習共同成長！

推薦閱讀：