Excel185|巧用OFFSET函式,始終統計分析近三個月的銷量
2021-10-12由 韓老師講office 發表于 農業
銷售量的公式是什麼
問題來源
某單位,要時時統計分析各產品最近三個月的銷量情況。
但:資料表中,會不斷插入行或者列,來記錄新的一個月銷售數量。
如何保證統計結果永遠是最近三個月的呢?
OFFSET函式,可以幫忙。
公式實現
月份列分佈:
如下工作表:
在E2中輸入公式:
=SUM(OFFSET(E2,0,-3,1,3)),可實現即使有新列插入,總能計算結果列之前三列的加和。
如下動圖:
其中:
OFFSET(E2,0,-3,1,3)的含義是:
由E2單元格偏移0行,向左偏移3列,到B2單元格,由B2單元格開始的1行3列的區域,即B2:D2區域。
如下圖:
在E列前插入列,基準點E2都會自動變為當前統計結果所在列。
因此,這個公式永遠統計前三列,即最近三個月的和。
統計最近三個月銷量的其他情況,可把SUM函式改為相應的函式。
如:
近三個月平均值:
=AVERAGE(OFFSET(E2,0,-3,1,3))
近三個月最大值:
=MAX(OFFSET(E2,0,-3,1,3))
月份行分佈:
如下工作表:
在B5中輸入公式:
=SUM(OFFSET(B5,-1,0,-3,1)),可實現即使有新行插入,總能計算結果行之前三行的加和。
如下動圖:
其中:
OFFSET(B5,-1,0,-3,1)的含義是:
由B5單元格向上偏移1行,偏移0列,到B4單元格,由B4單元格開始的向上2行1列的區域,即B2:B4區域。
OFFSET函式說明
語法:OFFSET(reference,rows,cols,height,width)
引用 必需。 要以其為偏移量的底數的引用。 引用必須是對單元格或相鄰的單元格區域的引用;否則OFFSET 返回 錯誤值 #VALUE!。
Rows 必需。 需要左上角單元格引用的向上或向下行數。 使用 5 作為 rows 引數,可指定引用中的左上角單元格為引用下方的 5 行。 Rows 可為正數(這意味著在起始引用的下方)或負數(這意味著在起始引用的上方)。
Cols 必需。 需要結果的左上角單元格引用的從左到右的列數。 使用 5 作為 cols 引數,可指定引用中的左上角單元格為引用右方的 5 列。 Cols 可為正數(這意味著在起始引用的右側)或負數(這意味著在起始引用的左側)。
高度 可選。 需要返回的引用的行高。 Height 必須為正數。
寬度 可選。 需要返回的引用的列寬。 Width 必須為正數
圖示說明:
藍色區域由A1偏移形成,那麼函式就是:OFFSET(A1,5,2,12,3)