(網友提問)在 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 函數中計算對應的數值總和。

arrow
arrow
    文章標籤
    Excel SUMPRODUCT OFFSET
    全站熱搜

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