2017年10月10日 星期二

Excel-查詢指定字串所在的儲存格位置(FIND,SEARCH,SUSTITUTE)

根據前一篇文章:
延伸的公式設計(使用FIND,SEARCH,SUSTITUTE),其公式執行概念大同小異。(參考下圖)
Excel-查詢指定字串所在的儲存格位置(FIND,SEARCH,SUSTITUTE)

【公式設計與解析】
選取儲存格B1:B21,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:字串。

《FIND函數》
儲存格C2:{=IFERROR(SMALL(IFERROR((FIND($F$2,字串)>0)*ROW(字串),""),
ROW(1:1))-1,"")}
這是陣列公式,輸入完成按 Ctrl+Shift+Enter 鍵,Excel 會自動加「{}」。
複製儲存格C2,貼至儲存格C2:C21。

《SEARCH函數》
儲存格D2:{=IFERROR(SMALL(IFERROR((SEARCH($F$2,字串)>0)*ROW(字串),""),
ROW(1:1))-1,"")}
使用 SEARCH 函數的關念和 FIND 函數完全相同。

《SUBSTITUTE函數》
儲存格E2:{=IFERROR(SMALL(IF(SUBSTITUTE(字串,$F$2,"")<>字串,
ROW(字串),""),ROW(1:1))-1,"")}
使用 SUBSTITUTE 函數並不是以尋找的關念來操作,而是將儲存格中的內容置換掉所指定字元,若結果不相同者,則表示該字串有包含指定字元。

相同公式,適用於不管指定幾個字元,但不適用於空白。若是沒有符合的字串,則會顯示空白。
Excel-查詢指定字串所在的儲存格位置(FIND,SEARCH,SUSTITUTE)

沒有留言:

張貼留言

檢視其他文章

好康東東