Excel教學Office軟件超實用教學函數公式數學及統計函數查閱與參照函數

每5行計算平均值

如果我們要計算某一範圍的平均值,我們會用AVERAGE。不過如果要每5行計算平均值,那AVERAGE公式內的範圍不就是要人手逐一去輸入?因為=AVERAGE(A1:A5)向下拉一格只會是=AVERAGE(A2:A6),不會是=AVERAGE(A6:A10)。那如何才可以在公式解決這個花時間人手操作的問題呢?

每5行計算平均值

F2的公式是:
=AVERAGE(OFFSET($C$2,(ROW()-ROW($F$2))*5,,5,))
然後向下拉便可。
=AVERAGE(OFFSET($C$2,(ROW()-ROW($F$2))*5,,5,))

  • AVERAGE是計算平均值
  • 當中的內容是一個範圍或一組數字
  • 所以只要我們成功地在AVERAGE內包含了第1批5格內容,即C2至C6,向下拉時,又成功地在AVERAGE內包含了第2批5格內容,即C7至C11,如此類推就會計到答案了

  • OFFSET是找出某一格或某一範圍的內容
  • OFFSET的語法是:
    • OFFSET(reference, rows, cols, [height], [width])
    • Reference 必要。 這是用以計算位移的起始參照。 Reference 必須參照一個儲存格或相鄰的儲存格範圍,否則 OFFSET 會傳回 #VALUE! 錯誤值。
    • Rows 必要。 這是要左上角儲存格往上或往下參照的列數。 使用 5 做為 rows 引數,指出參照的左上角儲存格是 reference 下方的第五列。 Rows 可以是正數 (表示在起始參照下方) 或負數 (表示在起始參照上方)。
    • Cols 必要。 這是要結果的左上角儲存格向左或向右參照的欄數。 使用 5 作為 cols 引數,指出參照位址的左上角儲存格是 reference 右方的第五欄。 Cols 可以是正數 (表示在起始參照右方) 或負數 (表示在起始參照左方)。
    • [高度] 選擇性。 這是要傳回參照的列數高度。 Height 必須是正數。
    • [寬度] 選擇性。 這是要傳回參照的欄數寬度。 Width 必須是正數。

  • 可理解成:
    • Reference 起點
    • Rows 向下走y格
    • Cols 必要。 向下走y格
    • [高度] 從起點向下及向右走y,x格後的起點起向下走n格的範圍
    • [寬度] 從起點向下及向右走y,x格後的起點起向右走m格的範圍

  • OFFSET($C$2,(ROW()-ROW($F$2))*5,,5,)就是:
    • 以C2作為起點
    • 向下走(ROW()-ROW($F$2))*5的格數
      • ROW()是指公式身處的列數,由於身處在F2,即第2列
      • ROW($F$2)是指F2身處的列數,即第2列
      • 所以是(2-2)*5,即是0
    • 第3個參數漏空,即是0,維持在同一欄上
    • 現在新的起點是C2向下行0格,向右行0格的位置,亦即是原來的F2
    • 第4及第5參數是5及漏空(0),即是以F2起,得出下面4格(F2本身為第1格)及右面0格的範圍,即是A2:A6,再以AVERAGE計算平均數
  • 我們再看F3的公式:=AVERAGE(OFFSET($C$2,(ROW()-ROW($F$2))*5,,5,))
    • OFFSET($C$2,(ROW()-ROW($F$2))*5,,5,)
    • 以C2為起點
    • 向下行(ROW()-ROW($F$2))*5的格數,即(3-2)*5
    • 向右行0格
    • C7是新起點
    • 向下取4格(加上本身C7也包括在內)
    • C7至C11便是要計算平均數的範圍了
=AVERAGE(OFFSET($C$2,(ROW()-ROW($F$2))*5,,5,))
  • C2是資料開首的第一格
  • F2是公式身處的位置
  • 5是每隔n行的n