如何跨多表?xiàng)l件求和之sumif的高級(jí)用法

一、工作表名規(guī)則(序號(hào)+字符)

【例】如下圖所示，有1日~5日5個(gè)列相同、行數(shù)不同的明細(xì)表，要求匯總出每個(gè)產(chǎn)品的銷量之和。

分表：

匯總表

分析：

如果只有一個(gè)表，我們只需要用sumif函數(shù)直接求和：

=SUMIF('1日'!B:B,合計(jì)!A2,'1日'!C:C)

對(duì)于多個(gè)表，除了用sumif()+sumif+sumif()...外，Sumif函數(shù)支持多表同時(shí)求和，但必須用indirect函數(shù)生成對(duì)多個(gè)表的引用，即：

INDIRECT(ROW($1:$5)&"日!B:B")

和

INDIRECT(ROW($1:$5)&"日!C:C")

(回復(fù) Indirect 可以查看該函數(shù)的用法)

用sumif組合起來(lái)，即：

=SUMIF(INDIRECT(ROW($1:$5)&"日!b:b"),A2,INDIRECT(ROW($1:$5)&"日!c:c"))

而上述的公式返回的每個(gè)表的求和結(jié)果，是一組數(shù)，最后還需要用sumrpoduct函數(shù)進(jìn)行求和，即：

=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())

公式說(shuō)明：

GET.WORKBOOK(1)是宏表函數(shù)，當(dāng)參數(shù)是1時(shí)，可以獲取當(dāng)前工作簿中所有工作表名稱，由于名稱中帶有工作簿名稱，所以用FIND+MID截取只含工作表名稱的字符串。&T(now())的作用是讓公式自動(dòng)更新。

然后，我們就可以在公式中用 sh&"! 替換原來(lái)的ROW($1:$5)&"日!,

最終公式為：

=SUMPRODUCT((SUMIF(INDIRECT(sh&"!b:b"),D2,INDIRECT(sh&"!c:c"))))

由于sh取得的名稱中包括“合計(jì)”，所以匯總表在制作時(shí)，要避開(kāi)B列和C列，以免造成循環(huán)引用。