一個公式搞定多表條件求和,不看後悔!

  我在《高手專用函數Indirect,你值得擁有!》一文中給大家介紹了怎樣使用Sum Indirect快速地匯總多個表格的數據。這個只是匯總了總和,但是有時候我們想要明細數據的匯總信息。方法有很多,比如合併計算,數據透視表的多重合併計算區域,甚至用SQL聯合查詢等,其實我們用一個公式就可以輕鬆搞定。

假設我們有Data 1、Data 2、Data 3這三個數據表,我們需要將所有表的數據匯總到一起。

一個公式搞定多表條件求和,不看後悔!

在Summary表中輸入以下公式就可以輕鬆匯總數據了。

=SUM(SUMIF(INDIRECT(””Data ”&{1,2,3}&””!B:B”),A2,INDIRECT(””Data ”&{1,2,3}&””!C:C”)))

或者

=SUM(SUMIF(INDIRECT({””Data 1””,””Data 2””,””Data 3””}&”!B:B”),A3,INDIRECT({””Data 1””,””Data 2””,””Data 3””}&”!C:C”)))

如果工作表較多,還可以用Row來生成序列數,從而生成工作表名稱。

如下,這個需要使用數組形式,按Ctrl Shift Enter結束。

=SUM(SUMIF(INDIRECT(””Data ”&ROW(1:3)&””!B:B”),A4,INDIRECT(””Data ”&ROW(1:3)&””!C:C”)))

也可以用宏表函數來獲取工作表名稱數組,具體方法可以參考之前推送的文章《在Excel中設置目錄頁》中的介紹的獲取工作表名稱的方法。需要注意:匯總工作表中公式所在的列不要跟明細工作表中的數據放在相同的列,否則會產生循環引用。

比如我們新建名稱ws,公式定義如下,來獲取工作表名稱的集合。

=MID(GET.WORKBOOK(1)&T(NOW()),FIND(”]”,GET.WORKBOOK(1)&T(NOW())) 1,100)

在D5單元格輸入以下公式,按Ctrl Shift Enter結束。

=SUM(SUMIF(INDIRECT(”””&ws&””!B:B”),A5,INDIRECT(”””&ws&””!C:C”)))

一個公式搞定多表條件求和,不看後悔!

公式解釋

使用Indirect結合數組,生成多表的區域引用;

Sumif生成結果是一個數組,所以在最外層套上Sum進行求和。

拓展

上面是對於工作表用了數組的形式,其實,對於Sumif的條件,我們也可以用數組的形式。

還是上面的數據,我們求Lily和Cherry兩個人的銷量總和。

公式如下,注意這裡條件參數{”Lily”;”Cherry”}中間是分號。

=SUM(SUMIF(INDIRECT(””Data ”&{1,2,3}&””!B:B”),{”Lily”;”Cherry”},INDIRECT(””Data ”&{1,2,3}&””!C:C”)))

Sumif公式的結果是一個兩行三列的數組,再用Sum求和就可以了。

Sumif公式結果={522,537,313;288,562,641}

— End —