2012年1月4日 星期三

Excel-分組小計(間隔列加總)(OFFSET+SUMPRODUCT)

在 Excel 中有一個資料表(如下圖左),其資料排列是依 A,B,C,D 順序,並各有 6 筆資料,如果要計算 A~D 各組和 1~6 各組的小計(如下圖右),該如何處理?

【解法】

(1) 計算 A~D 各組的小計

儲存格E2:=SUMPRODUCT(((MOD(ROW($B$2:$B$25)-1,4)=MOD(ROW(E2)-1,4))*$B$2:$B$25))

複製儲存格E2,貼至儲存格E2:E5。

MOD(ROW($B$2:$B$25)-1,4):因為每個 A(B,C,D) 的資料都間隔 4 列,所以使用 MOD 函數來求取列數除以 4 的餘數。公式中的「-1」是因為資料由第 2 列開始。向下複製後的結果為「1,2,3,0,1,2,3,0…」。

MOD(ROW(E2)-1,4)):儲存格E2的結果為1(儲存格E3結果為2,儲存格E4結果為3,儲存格E5結果為0)。

MOD(ROW($B$2:$B$25)-1,4)=MOD(ROW(E2)-1,4):產生True, False, False, False, True, False, False, False, True, …數列。

透過 SUMPRODUCT 函數將上式為 True 所對應的儲存格取出加總。

 

(2) 計算 1~6 各組的小計

儲存格E8:=SUM(OFFSET($B$2,(ROW(B2)-2)*4,,4,))

複製儲存格E8,貼至儲存格E8:E13。

(ROW(B2)-2)*4:向下複製後,產生 4, 8, 12, …的數列。

OFFSET($B$2,(ROW(B2)-2)*4,,4,):向下複製後,產生$B$2:$B$5、$B$6:$B$9、$B$10:$B$13、…。

1 則留言:

  1. 是否也可以用ADDRESS做組別小計?
    若可以用ADDRESS,A(1~6)的小計要如何列示?

    回覆刪除

好康東東