學不完．教不停．用不盡

網友問到：在 Excel 中的一個資料表，想要求得每一欄中，不為 0 的第 1, 2 個並且予以加總。該如何處理？

參考下圖，每一欄都有數個連續內容為 0 的儲存格，如何求得不為 0 的第 1, 2 個並且予以加總？

【公式設計與解析】

儲存格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 函數予以加總，即為所求。