7 cách áp dụng hàm FILTERXML để phân tách chuỗi trong Excel

Khi nói đến việc trích xuất dữ liệu từ mã XML trong Excel, FILTERXML là công cụ không thể thiếu. Hỗ trợ của nó hiện có trong Excel bản 2013, 2016, 2019, 2021 và Excel 365 dành cho hệ điều hành Windows, không áp dụng cho Excel trực tuyến hoặc phiên bản Mac. Bản chất, FILTERXML phối hợp với ENCODEURL và WEBSERVICE để xử lý dữ liệu XML; tuy nhiên, nó còn mở rộng khả năng áp dụng cho các nhu cầu tách chuỗi phức tạp thông qua Xpath. Bài viết sau đây sẽ trình bày 7 ví dụ áp dụng hàm FILTERXML cho việc tách chuỗi trong Excel.

FILTERXML Function

Cú pháp sử dụng hàm FILTERXML như sau:

=FILTERXML(xml, xpath)

Tại đây, “xml” là nội dung XML cần xử lý và “xpath” là biểu thức chuẩn của Xpath. Phiên bản Xpath hoạt động ở đây là 1.0.

Một ví dụ minh họa về đoạn XML:

Mẫu XML

Các phần tử trong XML được bao quanh bởi cặp dấu <> và được gọi là elements. Còn các khái niệm như attributes, namespaces sẽ không được đi sâu trong bài viết này. Siêu Marketing khuyến nghị bạn đọc tham khảo thêm thông tin về xml và xpath từ w3school để cải thiện kiến thức cơ bản:

XML: https://www.w3schools.com/xml/xml_whatis.asp

Xpath: https://www.w3schools.com/xml/xpath_intro.asp

Với FILTERXML, chúng ta có thể biến đổi một chuỗi thông thường thành dạng XML bằng cách dùng hàm SUBSTITUTE. Chuỗi mẫu cho bài viết như sau:

Chuỗi được chuyển đối thành dạng XML nhờ vào công thức dưới đây:

="<a><b>"&SUBSTITUTE(chuỗi,", ","</b><b>")&"</b></a>"

Kết quả thu được:

Với chuỗi đã được chuyển đối, FILTERXML bắt đầu phát huy khả năng mạnh mẽ của mình. Ví dụ dưới đây giả định rằng “xml” là chuỗi đã được biến đổi như trên.

1. Phân tách chuỗi sử dụng hàm FILTERXML

=FILTERXML(xml,"//b")

Tính năng của FILTERXML cho phép phân tách chuỗi và trả về kết quả theo cột. Nếu bạn không sử dụng Office 365, cần phải chọn ô trống cho kết quả rồi nhấn Ctrl + Shift + Enter sau khi nhập công thức.

Ngược lại, nếu Office 365 đang là công cụ của bạn, chỉ cần nhấn Enter là các phần tử sẽ tự động hiển thị thành một hàng dọc. Hay là bạn đã biết rằng Xpath có khả năng tự động cắt bỏ khoảng trắng ở đầu của chuỗi?

Ứng dụng cao cấp: Kết hợp cột dùng TEXTJOIN

TEXTJOIN đã xuất hiện trong Office 2019, nhưng hàm mới VSTACK thì chưa xuất hiện. Vì vậy nếu bạn có một bảng với nhiều cột hoặc mảng hai chiều, bạn có thể kết hợp chúng lại dùng TEXTJOIN, sau đó dùng FILTERXML để phân cách, tạo ra hiệu ứng kết hợp cột. Còn nếu bạn muốn kết hợp thành một hàng ngang thì TRANSPOSE sẽ giải quyết vấn đề này cho bạn.

FILTERXML kết hợp TEXTJOIN

2. Trích xuất một phần tử cụ thể trong danh sách sử dụng FILTERXML

Chuỗi

=FILTERXML(xml,"//b[số thứ tự]")

Chúng ta có thể chỉ định mục tiêu để truy xuất bằng cách đưa chỉ số của mục tiêu vào dấu ngoặc vuông. Ví dụ, để tìm phần tử thứ ba, bạn nhập //b[3] và hàm sẽ chỉ trả về một phần tử đó.

Ứng dụng cao cấp: Gắn kết với MATCH để tuyển chọn dữ liệu cần thiết.

Nếu ta có chuỗi csv có dạng “A, 1/1/2020, 3” và ba tiêu đề “Tên, DOB, Tuổi” tương ứng. Để tìm tuổi, ta cần lồng ghép MATCH vào FILTERXML như hình dưới đây:

Sử dụng MATCH với hàng tiêu đề để tìm giá trị tuổi tương ứng

3. Trích xuất thông tin theo phạm vi chỉ định

=FILTERXML(xml,"//b[position()<=số cần nhập]")

Bên cạnh phương pháp sử dụng //b[số cần nhập], ta cũng có thể sử dụng hàm position(). Ví dụ muốn lấy ra bốn giá trị đầu tiên, ta có thể nhập //b[position()<=4], và chuỗi xml sẽ cho kết quả như sau:

4. Lựa chọn các ô có chứa ký tự chỉ định

=FILTERXML(xml,"//b[contains(.,'ký tự được chọn, lưu ý dùng dấu nháy đơn')]")

Chức năng contains() cho phép ta xác định các phần tử có chứa kí tự nào đó. Ví dụ, để tìm các phần tử chứa ký tự “H” (chú ý sử dụng dấu nháy đơn cho contains chứ không phải nháy kép):

5. Ánh xạ các giá trị trong Xpath

=FILTERXML(xml,"//b[translate(.,'dãy kí tự cần ánh xạ','dãy kí tự ánh xạ')=.]")

Trong Xpath, hàm translate() giúp chuyển đổi kí tự thành kí tự khác. Tham khảo thêm về hàm này thông qua đường link này: https://developer.mozilla.org/en-US/docs/Web/XPath/Functions/translate.

translate('abcabc','ab','xy') => xycxyc

Ứng dụng của formula trên làm ta có thể so sánh giá trị sau khi được ánh xạ với giá trị gốc để rút ra các phần tử mong muốn. Dưới đây là một ví dụ cho việc tìm tất cả giá trị không bao gồm chữ số:

=FILTERXML(xml,”//b[translate(.,’1234567890′,”)=.]”)

Lưu ý: Chúng ta đang tìm các giá trị không chứa chữ số chứ không phải là giá trị không phải số. Do đó, một phần tử như “Th12uc” cũng bị loại bỏ vì có chứa chữ số bên trong.

6. Chọn các phần tử dạng số

=FILTERXML(xml,"//b[number()=.]")

Khi dùng hàm number() kết hợp, ta sẽ có khả năng lọc ra các giá trị dạng số từ chuỗi xml, như trong hình minh họa:

Một biểu thức khác cũng mang lại kết quả tương tự là:

=FILTERXML(xml,"//b[boolean(number())]")

7.

Xác định các giá trị duy nhất

=FILTERXML(xml,"//b[not(. = following-sibling::*)]")

Bằng cách sử dụng trục following-sibling trong Xpath, chúng ta có khả năng tuyển chọn các nút “không trùng lặp”, tức là các nút này xuất hiện một lần duy nhất trong danh sách kết quả. Đối với những thiết bị chưa được cài đặt hàm UNIQUE, phương pháp này làm một giải pháp thay thế rất hữu ích.


Nhìn chung trong bài viết, hàm FILTERXML được nhắc đến như một công cụ cực kỳ có ích. Tuy nhiên, có một số lý do khiến hàm này chưa được áp dụng rộng rãi trong thực tiễn, chẳng hạn như sự thiếu phổ biến trong các phiên bản Excel khác nhau hay độ phức tạp của Xpath đối với người dùng không chuyên.

Trương Thành Tài
0
    0
    Đơn hàng
    Đơn hàng trốngQuay lại Shop