Nội Dung Bài Viết

XML là định dạng dữ liệu phổ biến, thường được sử dụng để trao đổi thông tin giữa các hệ thống, đặc biệt là trong các phần mềm hóa đơn tại Việt Nam. Excel 2013 trở lên cung cấp hàm FILTERXML, cho phép đọc giá trị từ chuỗi XML. Khi kết hợp với các hàm mảng động, FILTERXML trở thành công cụ mạnh mẽ để tách, sắp xếp và lọc dữ liệu trong Excel. Bài viết này sẽ giải thích XML là gì và hướng dẫn cách sử dụng hàm FILTERXML qua các ví dụ thực tế trong Excel 365.

Phần mở đầu: XML là gì?

Để hiểu XML, hãy xem ví dụ sau:

<parent>
    <item>id</item>
    <item>first_name</item>
    <item>last_name</item>
    <item>gender</item>
    <item>email</item>
</parent>

Trong đoạn XML trên:

<parent></parent>, <item></item> là các thẻ (tag).
<parent></parent>, <item></item> là thẻ mở (opening tag).
, là thẻ đóng (closing tag).
id, first_name, last_name, gender, email nằm giữa thẻ mở và thẻ đóng là giá trị.
Tên thẻ như parent, item do bạn tự định nghĩa, miễn là đúng cấu trúc và vị trí.

Tiếp theo, chúng ta sẽ tìm hiểu cách sử dụng hàm FILTERXML để xử lý dữ liệu.

Hướng dẫn sử dụng hàm FILTERXML

Cú pháp

=FILTERXML(chuỗi_XML, chuỗi_XPATH)

Trong đó:

Cú pháp hàm FILTERXML

chuỗi_XML: (bắt buộc) Chuỗi dữ liệu định dạng XML.
chuỗi_XPATH: (bắt buộc) Chuỗi cú pháp đặc biệt giúp FILTERXML tìm kiếm dữ liệu trong chuỗi_XML.

Xem thêm: Thủ Thuật Ẩn/Hiện Nhiều Sheet Cùng Lúc Trong Excel

Lưu ý:

FILTERXML không hoạt động trên Excel Web và Excel trên macOS.
Hàm này có sẵn từ Excel 2013.
Nếu chuỗi_XML không hợp lệ, kết quả sẽ là #VALUE!.

Tách dữ liệu thành nhiều cột

Ví dụ, bạn có dữ liệu trong cột A và muốn tách thành nhiều cột như hình minh họa:

Tách dữ liệu với FILTERXML

Thay vì dùng Text To Columns, chúng ta sẽ sử dụng FILTERXML để linh hoạt hơn.

Đầu tiên, chuyển dữ liệu ngăn cách bởi dấu phẩy sang dạng XML bằng hàm SUBSTITUTE trong ô C1:

=SUBSTITUTE(A1,",","<i>")</i>

Kết quả: "idfirst_namelast_nameemailgender"

Bổ sung  và  để tạo chuỗi XML hợp lệ:

="<p><i>" & SUBSTITUTE(A1,",","</i><i>") & "</i></p>"

Kết quả: "idfirst_namelast_nameemailgender"

Sử dụng chuỗi_XPATH "//i" để lấy giá trị giữa các thẻ :

=FILTERXML("<p><i>" & SUBSTITUTE(A1,",","</i><i>") & "</i></p>","//i")

Kết quả:

Kết quả FILTERXML

Dùng hàm TRANSPOSE để chuyển kết quả thành hàng ngang:

=TRANSPOSE(FILTERXML("<p><i>" & SUBSTITUTE(A1,",","</i><i>") & "</i></p>","//i"))

Kéo công thức xuống để áp dụng cho các dòng còn lại.

Sắp xếp dữ liệu trong một ô

Đôi khi, bạn cần sắp xếp dữ liệu trong một ô. Ví dụ:

Sắp xếp dữ liệu với FILTERXML

Sử dụng FILTERXML như phần trước:

=FILTERXML("<p><i>" & SUBSTITUTE(A1,",","</i><i>") & "</i></p>","//i")

Sử dụng hàm SORT để sắp xếp:

=SORT(FILTERXML("<p><i>" & SUBSTITUTE(A1,",","</i><i>") & "</i></p>","//i"))

Kết quả:

Kết quả sắp xếp

Sử dụng TEXTJOIN để nối lại dữ liệu sau khi sắp xếp:

=TEXTJOIN(",",FALSE,SORT(FILTERXML("<p><i>" & SUBSTITUTE(A1,",","</i><i>") & "</i></p>","//i")))

Áp dụng cho các dòng còn lại.

Kết luận

Bài viết đã hướng dẫn cách sử dụng hàm FILTERXML để xử lý và khai thác dữ liệu trong Excel. FILTERXML kết hợp với các hàm khác mang lại hiệu quả cao trong việc tách, sắp xếp và xử lý dữ liệu.

Đánh Giá Bài Viết