在 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的值，形成一個可以動態顯示的報表。
【補充資料】
相關之詳細函數說明，請參閱微軟網站：
OFFSET：http://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 必須是正數。 |
留言列表