2015年2月2日 星期一

Excel-取出字串中的數字計算總和(陣列公式,MID,ROW,IFERROR)

有網友想要在 Excel 的資料表中的一個文數字清單,將每個文數字中的數字取出並計算總和,該如何處理?(參考下圖)

例如下圖中的儲存格A2:OK231PP2L98,其中數字為2,3,1,2,9,8,其和為25。

image

 

【設計公式】

儲存格B2:{=SUM(IFERROR(MID(A2,ROW($1:$30),1)+0,0))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加入「{}」。

複製儲存格B2,貼至儲存格B2:B13。

ROW($1:$30):在陣列公式會傳回 1, 2, 3, ..., 29, 30。

MID(A2,ROW($1:$30),1):取出儲存格A2內容的第 1, 2, 3, ..., 30 個字。如果有抓到字,則會傳回該字元(是一個文字),如果取不到,則會傳回空字串。

MID(A2,ROW($1:$30),1)+0:將取到的字元加 0 運算,如果該字元是英文字加 0,則傳回#VALUE錯誤訊息,如果該字元是數字(是一個文字),則傳回轉換為數字的字元,如果取不到文字(空字串),再加 0,則會傳回#VALUE錯誤訊息。

最後透過 IFFERROR 函數,將錯誤訊息#VALUE轉換為0。再由 SUM 函數計算總和。

沒有留言:

張貼留言

好康東東