2017年8月2日 星期三

Excel-指定數值清單中累計至第幾個(SUBTOTAL,OFFSET,INDEX)

在 Excel 中有一個數值清單(如下圖B欄),如何計算累計至指定第幾個?
在下圖中,C欄為計算每一個累計的結果,如何在沒有C欄輔助時計算指定個數的累計?
Excel-指定數值清單中累計至第幾個(SUBTOTAL,OFFSET,INDEX)
【公式設計與解析】
方法一:儲存格C2:=SUM($B$2:B2)
方法二:儲存格C2:=SUM(OFFSET($B$2,0,0,ROW(1:1),1))
方法三:儲存格C2:=SUBTOTAL(9,OFFSET($B$2,0,0,ROW(1:1),1))
複製儲存格C2,貼至儲存格C21。
以上三種方式都可以得到每個數值的累計結果。
現在,根據儲存格E2的指定個數,要計算累計結果。
儲存格E4:=INDEX(SUBTOTAL(9,OFFSET($B$2,0,0,ROW(1:20),1)),E2,1)
(1) OFFSET($B$2,0,0,ROW(1:20),1))
利用 OFFSET 函數取得B欄中要計算累計的儲存格區間。
(2) SUBTOTAL(9,OFFSET($B$2,0,0,ROW(1:20),1))
透過 SUBTOTAL 函數指定參數 9,用以指定執行 SUM 功能。
Excel-指定數值清單中累計至第幾個(SUBTOTAL,OFFSET,INDEX)
使用 SUBTOTAL 函數的用意,是可以利用 ROW(1:20) 產生 1~20  的陣列。
image
在執行 SUM 運算時得到累計的結果。
image
(3) INDEX(SUBTOTAL(9,OFFSET($B$2,0,0,ROW(1:20),1)),E2,1)
最後,再利用 INDEX  函數取出指定(儲存格E2)的累計結果。

沒有留言:

張貼留言

好康東東