2013年1月15日 星期二

Excel-指定組距計算小計(OFFSET)

在 Excel 中有一個資料表(如下圖左),根據不同組距值,要自動顯示範圍、最大值、最小值、平均值和總和(如下圖右),該如何處理?

【準備工作】

選取資料範圍(例如:儲存格A1:B52),按一下 Ctrl+Shift+F3 鍵,選取「頂端列」,定義名稱:序號、數值。

選取儲存格E1,定義名稱:組距。

 

【輸入公式】

儲存格D2:=(ROW(1:1)-1)*組距+1&"-"&ROW(1:1)*組距

儲存格E2:=MAX(OFFSET(數值,(ROW(1:1)-1)*組距,,組距,))

儲存格F2:=MIN(OFFSET(數值,(ROW(1:1)-1)*組距,,組距,))

儲存格G2:=AVERAGE(OFFSET(數值,(ROW(1:1)-1)*組距,,組距,))

儲存格H2:=SUM(OFFSET(數值,(ROW(1:1)-1)*組距,,組距,))

複製儲存格D2:H2,往下各列貼上。

如果要避免公式出現0或是錯誤訊息,則可以稍加修改公式。

儲存格D2:

=IF((ROW(1:1)-1)*組距<MAX(序號),(ROW(1:1)-1)*組距+1&"-"&ROW(1:1)*組距,"")

儲存格E2:

=IF((ROW(1:1)-1)*組距<MAX(序號),MAX(OFFSET(數值,(ROW(1:1)-1)*組距,,組距,)),"")

儲存格F2、儲存格G2、儲存格H2和儲存格E2的處理方式相同。

本例使用微調按鈕來控制儲存格E1的值,形成一個可以動態顯示的報表。

 

【補充資料】

相關之詳細函數說明,請參閱微軟網站:

OFFSEThttp://office.microsoft.com/zh-tw/excel-help/HP010342739.aspx

OFFSET 函數:傳回根據所指定的儲存格位址、列距及欄距而算出的參照位址。

語法:OFFSET(reference, rows, cols, [height], [width])

Reference:用以計算位移的起始參照位址。

Rows:左上角儲存格要往上或往下參照的列數。Rows可以是正數(表示在起始參照位址下方)或負數(表示在起始參照位址上方)

Cols:左上角儲存格要往左或往右參照的欄數。Cols 可以是正數(表示在起始參照位址右方)或負數(表示在起始參照位址左方)

Height:所傳回參照位址的高度 (以列數為單位)Height 必須是正數。

Width:所傳回參照位址的寬度 (以欄數為單位)Width 必須是正數。

沒有留言:

張貼留言

檢視其他文章

好康東東