大多數情況下, 我們對表格統計都會在同一張工作簿進行, 這樣操作起來非常方便, 不過有時也會進行跨工作簿統計。
跨工作簿條件求和最常見的兩個問題:
01跨工作簿區域應該如何寫?
02關閉工作簿後, 統計出來的結果變成錯誤值怎麼回事?
條件求和, 首先想到的是用SUMIF函數進行統計。
函數語法:
=SUMIF(條件區域,條件,求和區域)
其實跨工作簿的情況下, 區域的選取跟在同一個工作簿一樣, 都是用滑鼠選取, 而不是手寫。 有一點必須要記住:兩個工作簿必須同時打開。
滑鼠選取區域詳見動畫
最終公式為:
=SUMIF([跨工作簿統計1.xlsx]Sheet1!$B:$B,A2,[跨工作簿統計1.xlsx]Sheet1!$D:$D)
一旦將跨工作簿統計1.xlsx關閉, 修改統計月份, 金額就變成錯誤值。
在Excel中, 並不是所有函數都支援跨工作簿, 如SUMIF、COUNTIF函數就不支援, 而VLOOKUP、SUMPRODUCT函數就支援。 這裡可以借助SUMPRODUCT函數實現跨工作簿統計。
函數語法:
=SUMPRODUCT((條件區域=條件)*求和區域)
我們重新看一下出錯的儲存格, 公式變成:
=SUMIF('C:UserschenxiluDesktop[跨工作簿統計1.xlsx]Sheet1'!$B:$B,A2,'C:UserschenxiluDesktop[跨工作簿統計1.xlsx]Sheet1'!$D:$D)
C:UserschenxiluDesktop這個是路徑, 意思就是說這個表格存在盧子電腦的桌面。
[跨工作簿統計1.xlsx]Sheet1這個是工作簿名稱跟工作表名稱。
不要看公式很長, 其實拆分開真的沒什麼, 都是很簡單的東西。
SUMPRODUCT函數不支援引用整列,
=SUMPRODUCT(('C:UserschenxiluDesktop[跨工作簿統計1.xlsx]Sheet1'!$B$2:$B$100=A2)*'C:UserschenxiluDesktop[跨工作簿統計1.xlsx]Sheet1'!$D$2:$D$100)
這樣即使工作簿不打開的情況下, 也能正確統計。
一句話總結:可以用SUMPRODUCT函數實現跨工作簿統計, 直接打開兩個工作簿, 用滑鼠選取區域, 路徑是原來的表格關閉後自動生成的。