網友對前一篇文章產生了興趣: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))

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

如果是計算儲存格左側連續的『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,往下各列貼上。

arrow
arrow
    全站熱搜

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