2015年11月13日 星期五

Excel-計算儲存格內左、右方連續數字的個數(SUMPRODUCT,SUBSTITUTE)

網友對前一篇文章產生了興趣:Excel-計算儲存格內左、右方連續0的個數(SUMPRODUCT,LEFT,RIGHT,ROW),想要了解如何計算儲存格內左方、右方連續n個相同數字個數的做法。
以下圖為例,來計算左方連續個1的個數,和右方連續個1的個數。
Excel-計算儲存格內左、右方連續任意數字的個數(SUMPRODUCT,SUBSTITUTE,ROW)

【公式設計與解析】
1. 計算儲存格左側連續的 1 個數
假設儲存格內的數字不超過20個。
參考如果是要計算儲存格左側連續的『0』個數,公式:
儲存格B2:=SUMPRODUCT(--(--LEFT(A2,ROW($1:$20))=0))
如果是計算儲存格左側連續的『1』個數,公式:
儲存格B2:
=SUMPRODUCT(--(LEN(SUBSTITUTE(LEFT(A2,ROW($1:$20)),"1",""))=0))
其中的參數『"1"』,即指定要計算連續個 1 的個數,若改為 2,則表示要計算連續 2 的個數。
SUBSTITUTE(LEFT(A2,ROW($1:$20)),"1",""):將儲存格A2中,取出儲存格A2的左邊起算的 1, 2, 3, …, 20 個字,本例為:{"1","11","111","1115","11154",...}。然後利用SUBSTITUTE 函數將其中所有的『1』置換為空白。
--(LEN(SUBSTITUTE(LEFT(A2,ROW($1:$20)),"1",""))=0):判斷已將其中所有的『1』置換為空白後的字串,其長度如果為0,表示取得的字串為連續的 1。本例傳回{TRUE, TRUE, TRUE, FALSE, FALSE, ... },再藉由『--』將 TRUE/FASLE 陣列轉換為1/0 陣列,本例傳回{1,1,1,0,0, … ,0}。
最後再透過 SUMPRODUCT 函數執行『乘積和』運算,1+1+1+0+0+ ... +0=3。
複製儲存格B2,往下各列貼上。

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

沒有留言:

張貼留言

檢視其他文章

好康東東