sumif的高級用法:跨多表條件求和
一、工作表名規(guī)則(序號+字符)
【例】如下圖所示,有1日~5日5個列相同、行數(shù)不同的明細表,要求匯總出每個產(chǎn)品的銷量之和。
分表:
匯總表
分析:
如果只有一個表,我們只需要用sumif函數(shù)直接求和:
=SUMIF('1日'!B:B,合計!A2,'1日'!C:C)
對于多個表,除了用sumif()+sumif+sumif()...外,Sumif函數(shù)支持多表同時求和,但必須用indirect函數(shù)生成對多個表的引用,即:
INDIRECT(ROW($1:$5)&"日!B:B")
和
INDIRECT(ROW($1:$5)&"日!C:C")
(回復(fù) Indirect 可以查看該函數(shù)的用法)
用sumif組合起來,即:
=SUMIF(INDIRECT(ROW($1:$5)&"日!b:b"),A2,INDIRECT(ROW($1:$5)&"日!c:c"))
而上述的公式返回的每個表的求和結(jié)果,是一組數(shù),最后還需要用sumrpoduct函數(shù)進行求和,即:
=SUMPRODUCT(SUMIF(INDIRECT(ROW($1:$5)&"日!b:b"),A2,INDIRECT(ROW($1:$5)&"日!c:c")))
二、工作表名稱不規(guī)則
工作表名稱常常是不規(guī)則的,如下圖所示
分析:
首先我們需要用宏表函數(shù)取得表名
公式 - 名稱管理器 - 新建名稱 - 在新建名稱中輸入名稱“sh”,然后“引用位置”框中輸入公式:
=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,99)&T(now())
公式說明:
GET.WORKBOOK(1)是宏表函數(shù),當參數(shù)是1時,可以獲取當前工作簿中所有工作表名稱,由于名稱中帶有工作簿名稱,所以用FIND+MID截取只含工作表名稱的字符串。&T(now())的作用是讓公式自動更新。
然后,我們就可以在公式中用 sh&"! 替換原來的ROW($1:$5)&"日!,
最終公式為:
=SUMPRODUCT((SUMIF(INDIRECT(sh&"!b:b"),D2,INDIRECT(sh&"!c:c"))))
由于sh取得的名稱中包括“合計”,所以匯總表在制作時,要避開B列和C列,以免造成循環(huán)引用。
繼續(xù)查找其他問題的答案?
-
Excel 2019如何使用復(fù)制、粘貼命令插入Word數(shù)據(jù)?(視頻講解)
2021-01-257次播放
-
Excel 2019如何制作數(shù)據(jù)透視表?(視頻講解)
2021-01-2227次播放
-
Excel 2019如何設(shè)置相對引用?(視頻講解)
2021-01-2210次播放
-
2021-01-2212次播放
-
Excel 2019如何使用命令復(fù)制公式?(視頻講解)
2021-01-226次播放
-
2021-01-223次播放
-
2021-01-222次播放
-
Excel 2019如何利用開始選項卡進入篩選模式?(視頻講解)
2021-01-223次播放
-
Excel 2019如何設(shè)置指定數(shù)據(jù)的篩選?(視頻講解)
2021-01-223次播放
-
Excel 2019如何設(shè)置自定義排序?(視頻講解)
2021-01-222次播放
點擊加載更多評論>>