2015年6月27日 星期六

Excel-計算文數字組成的儲存格中數字個數和文字個數(MID,COUNT,陣列公式)

有網友問到在 Excel 的資料表中,如果要分別計算由文字和數字組成的儲存格中,其數字的個數和文字的個數,要如何處理?(參考下圖)

(一) 儲存格中的字數固定
參考上圖,其中儲存格內的字數固定,本例假設每個儲存格中有10個字元。
(1) 儲存格B2:{=COUNT(MID(A2,ROW($1:$10),1)*1)}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。
ROW($1:$10):表示{1,2,3,4,5,6,7,8,9,10}陣列。
MID(A2,ROW($1:$10),1):分別取出儲存格A2中的第1,第2, ..., 第10個字元,其傳回字元視為文字。
MID(A2,ROW($1:$10),1)*1:將上式傳回的 10 個「字元」乘以 1,如果是數字的字元,會轉換為數字;如果是文字的字元,會傳回錯誤訊息。
最後再以 COUNT 函數計算上式中有幾個數字,即為所求。
(2) 儲存格C2:{=LEN(A2)-COUNT(MID(A2,ROW($1:$10),1)*1)}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。
LEN(A2):透過 LEN 函數計算儲存格內容的字元數,將該值減掉(1)的傳回值,即為所求。
複製儲存格B2:C2,貼至儲存格B2:C21。

(二) 儲存格中的字數不固定
參考以下圖,其中儲存格內的文數字長度並不統一,所以設計的公式也要跟著調整。

(1) 儲存格B2:{=COUNT(MID(A2,ROW(INDIRECT("$1:$" & LEN(A2))),1)*1)}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。
INDIRECT("$1:$" & LEN(A2)):透過 LEN 函數計算儲存格內容的字元數,並且使用INDIRECT 函數來將字串轉換為儲存格位址。
(2) 儲存格C2:{=LEN(A2)-COUNT(MID(A2,ROW($1:$10),1)*1)}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。
複製儲存格B2:C2,貼至儲存格B2:C21。

沒有留言:

張貼留言

好康東東