錦子老師

如果要在統計工作表中B2儲存格計算最近20日內的數值平均值，每個月分別有自己的工作表，為了取代每天手動抓取範圍，要如何寫出自動抓取從今天往前算最近20日的平均值?如果今天是本月的第一天，要往前從上一個工作表最後十九天到今天，依此類推又要如何寫公式?

首先我們要先將工作表的名稱統一化，例如：

1月、2、3月...12月。

JAN、FEB、MAR...DEC

使用MID、FIND函數

步驟1：點取B2儲存格，輸入公式「=IF(DAY(A2)>=20,SUM(OFFSET(INDIRECT(TEXT(A2,"m")&"月!a1"), DAY(A2),1,-20,1)),SUM(OFFSET(INDIRECT(TEXT(A2,"m")&"月!a1"),1,1,DAY(A2),1))+SUM(OFFSET(INDIRECT(TEXT(A2,"m") -1&"月!a1"),DAY(EOMONTH(A2,-1)),1,-(20-DAY(A2)),1)))/20」。

公式說明

=IF(DAY(A2)>=20,SUM(OFFSET(INDIRECT(TEXT(A2,"m")&"月!a1"),DAY(EOMONTH(A2,0)),1,-20,1)),SUM(OFFSET(INDIRECT(TEXT(A2,"m")&"月!a1"),1,1,DAY(A2),1))+SUM (OFFSET(INDIRECT(TEXT(A2,"m")-1&"月!a1"),DAY(EOMONTH(A2,-1)),1,-(20-DAY(A2)),1)))/20

公式一：TEXT(A2,"m") 傳回A2儲存格的月份數字。

公式二：INDIRECT(公式一&"月!a1") 傳回公式一結果傳回數字與月字元所代表的工作表A1儲存格。

公式三：EOMONTH(A2,0) 傳回A2儲存格當月最後一天的數值。

公式四：DAY(公式三) 傳回公式三傳回結果的天數值。

公式五：OFFSET(公式二,公式四,1,-20,1) 傳回從公式二傳回的工作表A1儲存格，移動公式四傳回結果數值的列數，移動1欄後，框選向上19列(含目前列共20列)1欄。

公式六：DAY(A2) 傳回A2儲存格的天數值。

公式七：SUM(公式五) 計算公式五傳回範圍中數值合計。

公式八：SUM(OFFSET(INDIRECT(TEXT(A2,"m")&"月!a1"),1,1,DAY(A2),1))

公式九：EOMONTH(A2,-1) 傳回A2儲存格上個月最後一天的數值。

公式十：DAY(公式九) 傳回公式九傳回結果的天數值。

公式十一：OFFSET(公式二,公式十,1,-(20-公式六),1)) 傳回從公式二傳回的工作表A1儲存格，移動公式十傳回結果數值的列數，移動1欄後，框選向上列數(20減公式六傳回結果數字)1欄。

公式十二：SUM(公式十一) 計算公式十一傳回範圍中數值合計。

公式十三：=IF(DAY(A2)>=20,公式七,公式八+公式十二)/20 如果A2儲存格的天數值等於或大於20，則執行公式七後除以20，否則執行公式八與公式十二並將其值加總後除以20。