學不完．教不停．用不盡

在 Excel 取得一個資料表(如下圖左),現在要摘要出項目的小計(如下圖中)和組別的小計(如下圖右),該如何處理?

(一) 計算「項目」的小計

由於項目是每三列一個項目,若是儲存格F2中使用例如:SUM(C2:C4)的公式,將會無法向下複製得到每間隔三列的和。所以改用 INDIRECT 和 ADDRESS 函數:

儲存格F2:=SUM(INDIRECT(ADDRESS(ROW(1:1)*3-1,3)&":"&ADDRESS(ROW(1:1)*3+1,3)))

複製儲存格F2,在儲存格F2:F11貼上。

其中ROW(1:1)每往下複製一列會變成ROW(2:2)、ROW(3:3) ...

而ROW(1:1)=1、ROW(2:2)=2、ROW(3:3)=3 ...

所以ADDRESS(ROW(1:1)*3-1,3)&":"&ADDRESS(ROW(1:1)*3+1,3))=C2:C4 (這是一串字)

經過INDIRECT函數轉換成位址。

複製公式後,即可得SUM(C2:C4)、SUM(C5:C7)、SUM(C8:C10) ...

 

(二) 計算「組別」的小計

儲存格I2:=SUMPRODUCT((B2:B31=H2)*(C2:C31))

複製儲存格I2,在儲存格I2:I4貼上。

透過 SUMPRODUCT 函數,將B欄中合於H欄中的項目者,再乘以C欄的數量。其運算過程中會將TRUE/FALSE 轉換成 1/0。