在 Excel 裡有一個資料表如下圖中的A欄,在 D 欄中有一個資格不符者的清單,如何在 B欄中產生刪除不符者的清單?

Excel-刪除儲存格內容中不符合者

儲存格B3公式:=TEXTJOIN(",",,IFERROR(MID(A3,IF(ISERROR(MATCH(TEXTSPLIT(A3,","),$D$3:$D$9,0)),COLUMN(A:D),"")*2-1,1),""))

複製儲存格B3,貼至儲存格B3:B16。

公式解析:

(1) TEXTSPLIT(A3,",")

將儲存格A3內容以「,」分隔符號,取出成為陣列。傳回:{"丙","壬","寅","未"}。

(2) MATCH(TEXTSPLIT(A3,","),$D$3:$D$9,0)

將第(1)式傳回的陣列裡的每一個內容和儲存格D3:D9比對,傳回位置(數字),若沒有相符,則傳回錯誤訊息。

傳回:{#N/A,4,#N/A,#N/A}

如果傳回數值,表示該位置為不符合者,若傳回錯誤訊息,則該位置為必須保留者。

(3) IF(ISERROR(第(2)式),COLUMN(A:D),"")

若第(2)式的傳回值是錯誤訊息,則傳回 1~4 的其中一個數值(位置),否則傳回空字串。

傳回:{1,"",3,4}

(4) MID(A3,第(3)式*2-1,1)

將第(3)式中的第 1,3,5,7位置的文字取出。

傳回:{"丙",#VALUE!,"寅","未"}

(5) IFERROR(MID(A3,第(3)式*2-1,1),"")

如果有錯誤訊息,則傳回空字串(空白)。

傳回:{"丙","","寅","未"}

(6) TEXTJOIN(",",,IFERROR(MID(A3,第3式*2-1,1),""))

將第(5)式的傳回值以「,」做為分隔符號串接在一起。

傳回:"丙,寅,未"

學不完.教不停.用不盡文章列表

arrow
arrow
    文章標籤
    Excel
    全站熱搜
    創作者介紹
    創作者 vincent 的頭像
    vincent

    學不完.教不停.用不盡

    vincent 發表在 痞客邦 留言(0) 人氣()