excel 小技巧 第八十六集 excel多表格求和方法
多Excel表相同項求和方法來了,以後不要再問我!
最近不斷的有同學提問多個表相同項求和怎麼做。其實蘭色已推送多次。今天我們再學習sumif的跨多表條件求和公式。
一、工作表名規則(序號+字元)
【例】如下圖所示,有1日~5日5個列相同、行數不同的明細表,要求匯總出每個產品的銷量之和。
分表:
匯總表
分析:
如果只有一個表,
=SUMIF('1日'!B:B,合計!A2,'1日'!C:C)
對於多個表,除了用sumif()+sumif+sumif()...外,Sumif函數支援多表同時求和,但必須用indirect函數生成對多個表的引用,即:
INDIRECT(ROW($1:$5)&"日!B:B")
和
INDIRECT(ROW($1:$5)&"日!C:C")
(回復Indirect可以查看該函數的用法)
用sumif組合起來,即:
=SUMIF(INDIRECT(ROW($1:$5)&"日!b:b"),A2,INDIRECT(ROW($1:$5)&"日!c:c"))
而上述的公式返回的每個表的求和結果,是一組數,最後還需要用sumrpoduct函數進行求和,即:
=SUMPRODUCT(SUMIF(INDIRECT(ROW($1:$5)&"日!b:b"),A2,INDIRECT(ROW($1:$5)&"日!c:c")))
二、工作表名稱不規則
工作表名稱常常是不規則的,如下圖所示
分析:
首先我們需要用巨集表函數取得表名
公式 - 名稱管理器 - 新建名稱 - 在新建名稱中輸入名稱“sh”,然後“引用位置”框中輸入公式:
=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,99)&T(now())
公式說明:
GET.WORKBOOK(1)是巨集表函數,當參數是1時,可以獲取當前工作簿中所有工作表名稱,由於名稱中帶有工作簿名稱,所以用FIND+MID截取只含工作表名稱的字串。&T(now())的作用是讓公式自動更新。
然後,我們就可以在公式中用sh&"! 替換原來的ROW($1:$5)&"日!,
最終公式為:
=SUMPRODUCT((SUMIF(INDIRECT(sh&"!b:b"),D2,INDIRECT(sh&"!c:c"))))
由於sh取得的名稱中包括“合計”,所以匯總表在製作時,要避開B列和C列,以免造成迴圈引用。
如果你是新同學,