2011年1月3日 星期一

Excel-INDIRECT+ADDRESS函數應用

在 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))=$C$2:$C$4 (這是一串字)

經過INDIRECT函數轉換成位址。

複製公式後,即可得SUM($C$2:$C$4)、SUM($C$5:$C$7)、SUM($C$8:$C$10) …

 

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

儲存格I2:=SUMPRODUCT(($B$2:$B$31=H2)*($C$2:$C$31))

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

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

沒有留言:

張貼留言

好康東東