贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

網友問到:在 Excel 中有一個數值清單,如何針對這些數值,以固定數量為間隔來計算平圴?

以下圖為例,如何計算每 4 個一組或是 6 個一組來計算平均?

Excel-取固定數量分組的平均(OFFSET,ROW)


【公式設計與解析】

儲存格D2:=AVERAGE(OFFSET($A$1,(ROW(1:1)-1)*4+1,0,4,1))

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

(1) (ROW(1:1)-1)*4+1

當公式向下複製時,會產生 1, 5, 9, 13, 17, 21, 25, …。

ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。

(2) OFFSET($A$1,(ROW(1:1)-1)*4+1,0,4,1)

當公式向下複製時,會產生儲存格A2:A5、儲存格A6:A9、儲存格A10:A13、…。

最後透過 AVERAGE 函數取得各個儲存格範圍的平均值。


同理:

儲存格F2:=AVERAGE(OFFSET($A$1,(ROW(1:1)-1)*6+1,0,6,1))

複製儲存格F2,往下各列貼上。

當公式向下複製時,會產生儲存格A2:A7、儲存格A8:A13、儲存格A14:A19、…。

最後透過 AVERAGE 函數取得平均值。


參考下圖:

Excel-取固定數量分組的平均(OFFSET,ROW)

文章標籤
創作者介紹
創作者 vincent 的頭像
vincent

學不完.教不停.用不盡

vincent 發表在 痞客邦 留言(1) 人氣()


留言列表 (1)

發表留言
  • ian
  • 您好,有個vlookup使用上問題想請教,因無老師您的信箱,故將有問題的excel上傳onedrive網路空間,若方便的話,煩請協助解惑,謝謝。
    https://1drv.ms/x/s!AviPqv3FS4gNkXPYtgzIA96zXu3R
    (問題簡述如下:
    用vlookup函數 C行 要抓D行資料, 但因為A行數字 和D行數字格式不一樣,(已使用複製格式仍無效) 讓A行和D行格式一樣, 使C行公式vlookup可以正常運作)
    謝謝老師( 我的email:ian77705HOTMAIL .com )
  • 儲存格C3:=VLOOKUP(LEFT(A3,4)*1,D3:F1683,3,FALSE)
    您的問題在於A欄中的每個儲存格有一個隱藏字元,公式利用每個代號都是4碼,所以利用LEFT取出前4個字元,即可順利完成公式。

    vincent 於 2018/09/12 20:35 回覆

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼