2015年11月13日 星期五

Excel-計算儲存格內左、右方連續0的個數(SUMPRODUCT,LEFT,RIGHT,ROW)

在 Excel 中的一個資料表,其中儲存格可能在最左或是最右側有連續的0(參考下圖),如何得知這些連續的 0 分別有幾個?
Excel-計算儲存格內左、右方連續0的個數(SUMPRODUCT,LEFT,RIGHT,ROW)

【公式設計與解析】
1. 計算儲存格左側連續的 0 個數
假設儲存格內的數字不超過20個。
儲存格B2:=SUMPRODUCT(--(--LEFT(A2,ROW($1:$20))=0))
ROW($1:$20):相當於 1, 2, 3, …, 20 的陣列。
LEFT(A2,ROW($1:$20)):取出儲存格A2的左邊起算的 1, 2, 3, …, 20 個字。
--LEFT(A2,ROW($1:$20)):本例取得『--{"0","00","000","0003","00039", … } 』陣列,因為 LEFT 函數取得的結果為「文字」型態,所以公式中『--』可以將陣列轉換為{0,0,0,3,39, … }。
--(--LEFT(A2,ROW($1:$20))=0):公式中第一個『--』,可以將判斷是否為 0 的傳回值 TRUE/FALSE 陣列,轉換為 1/0 陣列。
(將『--』置換成『1*』或『0+』等其他運算都是可以的)
最後,再讓 SUMPRODUCT 函數執行『乘積和』運算。

2. 計算儲存格右側連續的 0 個數
儲存格E2:=SUMPRODUCT(--(--RIGHT(D2,ROW($1:$20))=0))
原理同上,將 LEFT 函數用 RIGHT 函數取代。

沒有留言:

張貼留言

好康東東