Nội Dung Bài Viết

FILTERXML là một hàm mạnh mẽ trong Excel, cho phép bạn trích xuất thông tin từ dữ liệu XML. Hàm này có sẵn trong Excel 2013, 2016, 2019, 2021 và Excel 365 trên Windows (chưa hỗ trợ trên Excel Web và Excel cho Mac). Mặc dù thường được dùng với ENCODEURL và WEBSERVICE để lấy và xử lý dữ liệu XML từ web, FILTERXML còn có thể kết hợp với XPath để giải quyết các bài toán tách chuỗi hiệu quả. Bài viết này sẽ giới thiệu 7 ứng dụng tách chuỗi bằng FILTERXML trong Excel.

Cú pháp hàm FILTERXML:

=FILTERXML(xml, xpath)

Trong đó:

xml: Đoạn văn bản XML cần xử lý.
xpath: Biểu thức XPath để xác định dữ liệu cần trích xuất (phiên bản XPath 1.0).

Cú pháp hàm FILTERXML

Để tìm hiểu thêm về XML và XPath, bạn có thể tham khảo các tài liệu sau trên W3Schools:

Chúng ta có thể chuyển đổi một chuỗi bất kỳ sang định dạng XML bằng hàm SUBSTITUTE. Ví dụ với chuỗi sau:

Hieu, Hieu, An, Nam, An, 100, 1234, Thuc, Tuan Anh, 567, An, Tuan, Long

Sử dụng công thức sau để chuyển đổi:

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

Kết quả:

<a><b>Hieu</b><b>Hieu</b><b>An</b><b>Nam</b><b>An</b><b>100</b><b>1234</b><b>Thuc</b><b>Tuan Anh</b><b>567</b><b>An</b><b>Tuan</b><b>Long</b></a>

Trong các ví dụ dưới đây, “xml” đại diện cho chuỗi đã được chuyển đổi.

Xem thêm: Sử dụng Hàm SUMIF trong Excel: Hướng dẫn Chi Tiết Cho Người Việt

1. Tách Chuỗi Thành Cột

=FILTERXML(xml,"//b")

FILTERXML tách chuỗi và trả về kết quả theo cột. Với Office 365, kết quả sẽ tự động hiển thị. Với các phiên bản cũ hơn, cần chọn vùng kết quả trước, nhập công thức và nhấn Ctrl + Shift + Enter. XPath sẽ tự động loại bỏ khoảng trắng thừa ở đầu chuỗi.

Kết quả tách chuỗi

Ứng dụng nâng cao: Kết hợp với TEXTJOIN để gộp nhiều cột thành một cột trước khi tách bằng FILTERXML. Sử dụng TRANSPOSE nếu muốn gộp thành một hàng.

FILTERXML kết hợp TEXTJOIN

2. Trích Xuất Phần Tử Cụ Thể

=FILTERXML(xml,"//b[n]")

Thay n bằng vị trí của phần tử cần lấy (ví dụ: //b[3] để lấy phần tử thứ 3).

Ứng dụng nâng cao: Kết hợp với MATCH để tìm vị trí của phần tử dựa trên tiêu chí cụ thể. Ví dụ, trích xuất “Tuổi” từ chuỗi “Tên, Ngày Sinh, Tuổi”.

FILTERXML kết hợp MATCH

3. Trích Xuất Theo Khoảng Vị Trí

=FILTERXML(xml,"//b[position()>=n and position()<=m]")

Thay n và m bằng vị trí bắt đầu và kết thúc. Ví dụ, //b[position()>=1 and position()<=4] để lấy 4 phần tử đầu tiên.

Trích xuất theo khoảng vị trí

4. Lọc Theo Ký Tự Cụ Thể

=FILTERXML(xml,"//b[contains(.,'ký_tự')]")

Thay ký_tự bằng ký tự cần tìm (sử dụng dấu nháy đơn). Ví dụ, //b[contains(.,'H')] để lấy các phần tử chứa chữ “H”.

Lọc theo ký tự

5. Ánh Xạ Ký Tự

=FILTERXML(xml,"//b[translate(.,'ký_tự_gốc','ký_tự_mới')=.]")

Hàm translate() ánh xạ các ký tự. Ví dụ, translate('abcabc','ab','xy') sẽ trả về xycxyc. Ứng dụng để lọc các phần tử dựa trên kết quả ánh xạ. Ví dụ, //b[translate(.,'1234567890','')=.] để lấy các phần tử không chứa số.

Xem thêm: Lọc Dữ Liệu Chính Xác với Advanced Filter trong Excel

Ánh xạ ký tự

Lưu ý: Ví dụ trên lọc các phần tử không chứa số, chứ không phải các phần tử chỉ chứa ký tự.

6. Trích Xuất Giá Trị Số

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

Hoặc:

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

Cả hai công thức đều trích xuất các phần tử là số.

Trích xuất giá trị số

7. Loại Bỏ Giá Trị Trùng Lặp

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

Sử dụng XPath axes following-sibling để trả về danh sách các phần tử duy nhất, không trùng lặp.

Loại bỏ giá trị trùng lặp

FILTERXML là một hàm mạnh mẽ nhưng chưa được sử dụng rộng rãi do tính phức tạp của XPath. Hy vọng bài viết này giúp bạn hiểu rõ hơn về cách sử dụng FILTERXML để tách chuỗi trong Excel. Bạn có thể tìm hiểu thêm về FILTERXML tại các liên kết sau: