2016年8月18日 星期四

Excel-在一個數值區間中計算含有某個數字的個數(SUBSTITUTE,ROW)

在日常生活中,有時會用到要在一個數值區間中計算含有某個數字的個數,該如何處理?
以下圖的 Excel 例子,要計算 1~500 的數值區間中,共有幾個數含有『4』?例如:4, 140, 403, ...,這些數都含有 4。
Excel-在一個數值區間中計算含有某個數字的個數(SUBSTITUTE,ROW)

【公式設計與解析】
儲存格C2:{=SUM(1*(SUBSTITUTE(ROW(1:500),"4","")<>ROW(1:500)&""))}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。
(1) SUBSTITUTE(ROW(1:500),"4","")
在陣列公式中對 1~500 的數值利用 SUBSTITUTE 函數將數值中的 4 以空白取代,其傳回 500 個新的數字組成的字串陣列。
(2) ROW(1:500)&""
在陣列公式中將 1~500 的數值轉換為字串。
(3) SUBSTITUTE(ROW(1:500),"4","")<>ROW(1:500)&"")
判斷第(1)式和第(2)是否不相等,若是,代表該數含有 4;若不是,代表該數含 4。其結果傳回 TRUE/FALSE 陣列。
(4) 1*(SUBSTITUTE(ROW(1:500),"4","")<>ROW(1:500)&"")
利用『1*』運算,將第(3)式傳回的 TRUE/FALSE 陣列轉換為 1/0 陣列。
(5) SUM(1*(SUBSTITUTE(ROW(1:500),"4","")<>ROW(1:500)&""))
將第(4)式傳回的 1/0 陣列加總,其總和即為含有 4 的數值個數。

沒有留言:

張貼留言

好康東東