2017年7月29日 星期六

Excel-找出連續0之後的2個數予以加總(SUMPRODUCT,OFFSET)

網友問到:在 Excel 中的一個資料表,想要求得每一欄中,不為 0 的第 1, 2 個並且予以加總。該如何處理?
參考下圖,每一欄都有數個連續內容為 0 的儲存格,如何求得不為 0 的第 1, 2 個並且予以加總?
Excel-找出連續0之後的2個數予以加總(SUMPRODUCT,OFFSET)

【公式設計與解析】
儲存格A19:
=SUM(OFFSET(A1,SUMPRODUCT(MAX((A1:A18=0)*ROW(A1:A18))),0,2,1))
複製儲存格A19,貼至儲存格A19:G19。
(1) (A1:A18=0)*ROW(A1:A18)
在 SUMPRODUCT 函數中傳回符合條件 A1:A18=0 者的列號。ROW 函數可以傳回儲存格列號。
(2) MAX((A1:A18=0)*ROW(A1:A18))
利用 MAX 函數取得不為 0 者儲存格列號中的最大值。
(3) SUMPRODUCT(MAX((A1:A18=0)*ROW(A1:A18)))
利用 SUMPRODUCT 函數可以使用陣列運算。
(4) OFFSET(A1,SUMPRODUCT(MAX((A1:A18=0)*ROW(A1:A18))),0,2,1)
透過 OFFSET 函數,以儲存格A1為起點,位移至不為 0 的第 1 個儲存格,再取高度為 2、寬度為 1 的儲存格範圍。本例傳回儲存格A15:A16。
(5) 最後再透過 SUM 函數予以加總,即為所求。
Excel-找出連續0之後的2個數予以加總(SUMPRODUCT,OFFSET)

沒有留言:

張貼留言

檢視其他文章

好康東東