學不完．教不停．用不盡

被問到，在 Excel 裡，可否使用公式執行資料剖析的動作。利用公式可以在輸入儲存格內容後，即可自動將儲存格內容剖析至各個儲存格中。

像是：a/b/c/d/e/f、甲,乙,丙,丁,戊、1-2-3-4-5-6-7 等之類的內容要剖析至多個儲存格中。（參考下圖）

本篇打算要使用 FILTERXML 函數來完成，可以做一個通用的公式。

【設計與解析】

儲存格B6：=IFERROR(FILTERXML("<X><A>"&SUBSTITUTE($A6,"/","</A><A>")&"</A></X>","X/A["&COLUMN(A6)&"]"),"")

複製儲存格B6，貼至儲存格B6:H6。

(1) 因為 FILTERXML 的語法為：FILTERXML(xml, xpath)，其中的語法結構是以<文字>開頭，</文字>結尾。

(2 利用 SUBSTITUTE 函數將「/」置換成「</A></X>」，再配合頭尾的<X>和</X>，組成完整的 XML 語法。

(3 "X/A" 為第一層：X，第二層：A。

(4) "X/A["&COLUMN(A6)&"]")：X/A[數字]，其中的數字n，表示第n筆資料。

(5) COLMUMN(A6)會傳回「1」。A欄傳回1，B欄傳回2，C欄傳回3…。向右複製公式時，COLMUMN(A6)=1→COLMUMN(B6)=2→COLMUMN(C6)=3→COLMUMN(A4)=4→…。

如果分隔字元更改，例如：「/」換成「,」，則公式調整為：

=IFERROR(FILTERXML("<X><A>"&SUBSTITUTE($A10,",","</A><A>")&"</A></X>","X/A["&COLUMN(A10)&"]"),"")

其餘依此類推。

【延伸閱讀】

 Excel-查詢地點的經度和緯度

 Excel-利用經度和緯度查詢地圵

【參考資料】

  FILTERXML 函數參考微軟提供的說明網頁：

https://support.microsoft.com/zh-tw/office/filterxml-函數-4df72efc-11ec-4951-86f5-c1374812f5b7

 SUBSTITUTE 函數參考微軟提供的說明網頁：

 COLUMN 函數參考微軟提供的說明網頁：