2017年12月16日 星期六

Excel-根據數值清單計算累計和排名前幾個的總和(SUMPRODUCT,OFFSET)

(網友提問)在 Excel 的工作表中有個數值清單(參考下圖左),如何根據數值清單計算累計前幾個的總和,及排名前幾個的總和?
image

【公式設計與解析】
選取儲存格A1:D24,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:序號、數值、累計、排名。
1. 累計前n個總和
儲存格G2:=SUM(OFFSET($B$2,0,0,G1,1))
複製儲存格G2,貼至儲存格G2:I2。
OFFSET($B$2,0,0,G1,1):根據儲存格G1的內容,傳回從儲存格B2起始的儲存格範圍。
再利用 SUM 函數計算總和。

2. 排名前n個總和(*使用D欄)
儲存格G3:=SUMPRODUCT(數值*(排名<=G1))
複製儲存格G3,貼至儲存格G3:I3。
在 SUMPRODUCT 函數中先判斷每個數值的名次是否小於儲存格G1,若是傳回 TRUE,若否,則傳回 FALSE。再於 SUMPRODUCT 函數中計算對應的數值總和。

3. 排名前n個總和(*不使用D欄)
儲存格G3:=SUMPRODUCT(數值*(RANK(數值,數值)<=G1))
複製儲存格G3,貼至儲存格G3:I3。
RANK(數值,數值)<=G1:在 SUMPRODUCT 函數中可以算出數值陣列中每個儲存格的排序。再判斷是否小於或等於儲存格G1。再於 SUMPRODUCT 函數中計算對應的數值總和。

沒有留言:

張貼留言

檢視其他文章

好康東東