網友問到:在 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)

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

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