前幾天蘭色推過一期跨表公式合集,其中有一個是利用sum進行多表求和
【例】如下圖所示,需要在匯總表中統計1~30日的各個商品銷量合計(日報表和匯總表格式、位置完全一樣)
在匯總表B2中輸入公式:
=sum('*'!b2)
輸入後會自動替換為多表引用方式
=SUM('1日:30日 '!B2)
有同學提問:如果各個表中商品的位置(所在行數)不一樣,該怎麼求和?蘭色今天要分享一個更強大的支持行數不同的求和公式。
分析及公式設置過程:
如果對單個表(比如1日)進行對A商品進行求和,可以直接用sumif函數搞定:
1日表
在匯總表中設置求和公式:
=SUMIF('1日'!A:A,A2,'1日'!B:B)
依此類推,如果對30天求和,公式應為:
=SUMIF('1日'!A:A,A2,'1日'!B:B)+SUMIF('2日'!A:A,A2,'2日'!B:B)
+..+SUMIF('30日'!A:A,A2,'30日'!B:B)
這公式也太長了吧.
細心的同學會發現,公式雖然,但還是有規律的:對各個表的求和除了表名外,其他公式部分都相同。
利用這個特點,我們可以用row函數自動生成對1~30天的引用。
=Row(1:30) 的結果為
{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30}
為證明這一點,可以在單元格中輸入公式後,選中row(1:30)按F9鍵
連接成對各個表A列和B列的引用
=ROW(1:30)&"日!A:A"
=ROW(1:30)&"日!B:B"
連接成的只是字符串,並不能代表1:30日的A列和B列。把字符串地址轉換成真正的引用,這是indirect函數的特長:
=Inidrect(ROW(1:30)&"日!A:A")
=Indirect(ROW(1:30)&"日!B:B")
有地址了,把它套進sumif函數中會怎麼樣?
=SUMIF(Inidrect(ROW(1:30)&"日!A:A"),A2,Indirect(ROW(1:30)&"日!B:B"))
結果是會把各個表中的A產品銷量分別進行求和,查看結果按F9。
最後用sumproduct函數進行求和(這裡不用sum的原因是:sum無法直接支持數組運算,本公式中同時對多數組進行運算屬數組運算)
最終的公式為:
=SUMPRODUCT(SUMIF(INDIRECT(ROW($1:$30)&"日!a:a"),A2,INDIRECT(ROW($1:$30)&"日!b:b")))
由於公式複製後row(1:30)中的行數會發生變化,所以這裡必須要添加絕對引用符號$
註:如果是多表多條件求和,可以用sumifs函數,原理相同。
蘭色說:這是蘭色第1次對多表求和進行這麼詳細的解釋,這種解釋公式的形式如果同學們覺得好就點右下角在看支持,以後蘭色會繼續用這種形式剖析更多excel公式。
如果你是新同學,長按下面二維碼 - 識別圖中二維碼 - 關注,就可以每天和蘭色一起學Excel了。