1. 首頁 > 生活百科 > >

瞬間搞定一月數據匯總!這個Excel求和公式太牛了

小知識生活百科

我推過一期跨表公式合集 ， 其中有一個是利用sum進行多表求和
【例】如下圖所示 ， 需要在匯總表中統計1~30日的各個商品銷量合計(日報表和匯總表格式、位置完全一樣)

瞬間搞定一月數據匯總!這個Excel求和公式太牛了


 
在匯總表B2中輸入公式：
=sum(‘*’!b2)
輸入后會自動替換為多表引用方式
=SUM(‘1日:30日 ‘!B2)
有同學提問：如果各個表中商品的位置(所在行數)不一樣 ， 該怎么求和?我今天要分享一個更強大的支持行數不同的求和公式 。
分析及公式設置過程：
如果對單個表(比如1日)進行對A商品進行求和 ， 可以直接用sumif函數搞定：
1日表
瞬間搞定一月數據匯總!這個Excel求和公式太牛了


 
在匯總表中設置求和公式：
=SUMIF(‘1日’!A:A,A2,’1日’!B:B)
瞬間搞定一月數據匯總!這個Excel求和公式太牛了


 
依此類推 ， 如果對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鍵
瞬間搞定一月數據匯總!這個Excel求和公式太牛了


 
連接成對各個表A列和B列的引用
=ROW(1:30)&”日!A:A”
=ROW(1:30)&”日!B:B”
瞬間搞定一月數據匯總!這個Excel求和公式太牛了


 
連接成的只是字符串 ， 并不能代表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 。
【瞬間搞定一月數據匯總!這個Excel求和公式太牛了】
瞬間搞定一月數據匯總!這個Excel求和公式太牛了


 
最后用sumproduct函數進行求和(這里不用sum的原因是：sum無法直接支持數組運算 ， 本公式中同時對多數組進行運算屬數組運算)
最終的公式為：
=SUMPRODUCT(SUMIF(INDIRECT(ROW($1:$30)&”日!a:a”),A2,INDIRECT(ROW($1:$30)&”日!b:b”)))
由于公式復制后row(1：30)中的行數會發生變化 ， 所以這里必須要添加絕對引用符號$
瞬間搞定一月數據匯總!這個Excel求和公式太牛了


 
注：如果是多表多條件求和 ， 可以用sumifs函數 ， 原理相同 。

    猜你喜歡