2018年7月4日 星期三

Excel-增加清單項目不需改變加總計算的公式(SUMPRODUCT,OFFSET,COUNT)

在 Excel 中,如下圖左的資料清單,如依據日期和項目計算加總?
Excel-增加清單項目不需改變加總計算的公式(SUMPRODUCT,OFFSET,COUNT)
【公式設計與解析】
儲存格F2:
=SUMPRODUCT(($A$2:$A$23=$E2)*($B$2:$B$23=F$1)*$C$2:$C$23)
複製儲存格F2,貼至儲存皮F2:H8。
利用 SUMPRODUCT 函數,只要一個式子即完成所有計算。

但是,如果清單項目會持續的增加,要如何能不改變加總計算的公式,也可自動得到正確結果?(參考下圖)
Excel-增加清單項目不需改變加總計算的公式(SUMPRODUCT,OFFSET,COUNT)
因為清單項目會持續的增加,所以可以利用名稱定義來決定儲存格範圍。
定義名稱:
日期:=OFFSET(工作表2!$A$1,1,0,COUNT(工作表2!$A:$A),1)
項目:=OFFSET(工作表2!$B$1,1,0,COUNT(工作表2!$A:$A),1)
數量:=OFFSET(工作表2!$C$1,1,0,COUNT(工作表2!$A:$A),1)
COUNT(工作表2!$A:$A)可以決定共有幾筆資料,再利用 OFFSET 函數決定資料的儲存格範圍。
image
修改公式:
儲存格F2:=SUMPRODUCT((日期=$E2)*(項目=F$1)*數量)

沒有留言:

張貼留言

檢視其他文章

好康東東