輕鬆搞定！運用 FILTERXML 函數從 Excel 文字中擷取多個數量

在處理 Excel 資料時，您是否曾遇過需要從一段文字中擷取多個數量的困擾？本文將介紹如何巧妙運用 FILTERXML 函數，搭配 SUBSTITUTE 函數，輕鬆解決這個問題。

理解核心函數：FILTERXML 與 SUBSTITUTE

• SUBSTITUTE 函數： 這個函數的功能是將文字字串中的特定字元或文字，用新的字元或文字取代。[1][2][3] 在本次的應用中，我們將利用它來轉換我們的資料，使其符合 FILTERXML 函數所需的格式。

• FILTERXML 函數： 此函數可以從 XML 格式的文字中，根據指定的 XPath 路徑傳回特定的資料。[4][5] 要注意的是，FILTERXML 函數是在 Excel 2013 之後的版本才有提供，且在網頁版或 Mac 版的 Excel 中無法使用。

關鍵的 XPath 語法解析

在 FILTERXML 函數中，XPath 是用來指定要擷取哪個部分資料的路徑。其中，有兩個重要的符號需要特別了解：

•  (雙斜線): 這個符號代表搜尋整個 XML 結構中符合條件的節點，無論它在哪個層級。

•  (at 符號): 這個符號用來選取元素的屬性。[6][7] 在我們的例子中，它將用來抓取特定標籤的屬性值。

實際操作步驟

假設我們的資料如下圖所示，A1儲存格中的文字包含了多個產品名稱及對應的數量，格式為 (qty=數量)。

目標： 從 A1 儲存格中，將所有的數量擷取出來。

公式：

Generated excel

=FILTERXML("<a><b>"&SUBSTITUTE(SUBSTITUTE(A1,"(qty=","<q>"),")","</q><b>")&"</b></a>","//q")

公式解析：

1. 內層 SUBSTITUTE：

   • SUBSTITUTE(A1,"(qty=","<q>")： 這個部分會將 A1 儲存格中的所有 (qty= 取代為 <q>。

   • SUBSTITUTE(...,")","</q><b>")： 接著，再將上一步結果中的 ) 取代為 </q><b>。經過這兩層的取代，原本的 (qty=1080) 就會變成 <b><q>1080</q></b>。

2. 串接成完整的 XML 格式：

   • "<a><b>"& ... &"</b></a>"： 我們在經過 SUBSTITUTE 處理後的字串前後，分別加上了 <a><b> 和 </b></a>，使其成為一個完整的 XML 結構。

3. FILTERXML 擷取資料：

   • FILTERXML(...,"//q")： 最後，FILTERXML 函數會根據 XPath //q，去尋找整個 XML 結構中所有名為 q 的節點，並將其內容（也就是我們需要的數量）傳回。

重要提醒：

• 在使用 FILTERXML 函數時，如果公式展開後會覆蓋到有資料的儲存格，將會出現 #SPILL! 的錯誤。請確保公式下方有足夠的空白儲存格。

• 如果 XML 格式不正確，FILTERXML 函數會傳回 #VALUE! 錯誤。[4][5]

透過以上步驟，您就可以輕鬆地從文字中擷取出所有需要的數量。這個方法不僅實用，也能讓您對 Excel 的資料處理能力有更深入的了解。

Sourceshelp

1. microsoft.com
2. steachs.com
3. gtwang.org
4. microsoft.com
5. microsoft.com
6. realnewbie.com
7. cnblogs.com