2017年2月2日 星期四

Excel-取出字串中的數字(ROW,COLUMN,陣列公式)

常有網友提到的 Excel 中的問題:取出儲存格中字串中包含的數字,該如何處理?
如下圖,一個儲存格中的內容,數字可能出現的位置:左邊、中間、右邊,如何取出其中的數字。而該公式也適用空字串、全無數字、全是數字的儲存格。
Excel-取出字串中的數字(ROW,COLUMN,陣列公式)

【公式設計與解析】
儲存格B1:{=MAX(IFERROR(MID(A2,ROW($1:$10),COLUMN($A:$J))*1,0))}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。
複製儲存格B1,貼至儲存格B1:B9。
(1) ROW($1:$10)
在陣列公式中產生 1 ~ 10的數字。ROW(1:1)=1、ROW(2:2)=2、…、ROW(10:10)=10。
(2) COLUMN($A:$J)
在陣列公式中產生 1 ~ 10的數字。COLUMN(A:A)=1、COLUMN(B:B)=2、…、COLUMN(J:J)=10。
(3) MID(A2,ROW($1:$10),COLUMN($A:$J))
在陣列公式中,利用 MID 函數取出所有可能的字串。例如:『AK6』 會取出 A、K、6、AK、K6、AK6。(MID 函數的作用乃是由第 n 個字取出 m 個字)
(4) MID(A2,ROW($1:$10),COLUMN($A:$J))*1
公式中「*1」的用意,是將取出的數字字串轉換為數值,但是非數字的字串會傳回錯誤訊息。
(5) IFERROR(MID(A2,ROW($1:$10),COLUMN($A:$J))*1,0)
利用 IFERROR 函數將第(4)式的傳回值的錯誤訊息以『0』取代。
(6) MAX(IFERROR(MID(A2,ROW($1:$10),COLUMN($A:$J))*1,0))
利用 MAX 函數,以取出第(5)式傳回的所有數值的最大值。該最大值即為字串中的數字。

沒有留言:

張貼留言

好康東東