2016年7月12日 星期二

Excel-找出儲存格中第一個非數字的位置(ISNUMBER,MATCH,陣列公式)

在 Excel 中的儲存格內容常會是數字和非數字混合,如何能找出某個儲存格中第一個非數字的位置呢?(參考下圖)
Excel-找出儲存格中第一個非數字的位置(ISNUMBER,MATCH,陣列公式)

【公式設計與解析】
先假設儲存格內容不會超過 20 個字元組成。
儲存格B2:{=MATCH(FALSE,ISNUMBER(VALUE(MID(A2,ROW($1:$20),1))),0)}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。
(1) MID(A2,ROW($1:$20),1))
在陣列公式中,利用 ROW(1:20) 來獲得 {1,2,3,4,5,..,20},再使用 MID 函數取出儲存格中的每一個字元。
(2) VALUE(MID(A2,ROW($1:$20),1))
將第(1)式傳回的每一個字元利用 VALUE 函數轉換為數字,但若是字元非數字,則會產生錯誤訊息。本例傳回:{1,2,3,#VALUE!,#VALUE!,#VALUE!, … ,#VALUE!}。
(3) ISNUMBER(VALUE(MID(A2,ROW($1:$20),1)))
利用 ISNUMBER 函數判斷第(2)式的結果是否為數字,本例傳回:{TRUE,TRUE,TRUE,FALSE,FALSE,FALSE, … ,FALSE}。
最後利用 MATCH 函數找出第一個 FALSE 的位置,即為所求,本例傳回「4」。
你也可以使用以下的公式得到相同結果:
儲存格B2:{=MATCH(TRUE,ISERROR(VALUE(MID(A2,ROW($1:$20),1))),0)}

而如果你的儲存格內容,其字元長度不確定是否會低於20個字,則改用以下公式。
儲存格B2:{=MATCH(TRUE,ISERROR(VALUE(MID(A2,ROW(INDIRECT("1:"&
LEN(A2))),1))),0)}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。
ROW(INDIRECT("1:"&LEN(A2))):利用 LEN(A2) 取得儲存格的字元數,再透過 INDIRECT 函數將「"1:"&LEN(A2)」的結果轉換為實際位址。
本例傳回:ROW(INDIRECT("1:6"))→ROW(1:6),指第1列至第6列。

沒有留言:

張貼留言

好康東東