Cách tách và sắp xếp dữ liệu từ file XML trong Excel sử dụng hàm FILTERXML

Microsoft Excel

Trong bài viết này, mình xin chia sẻ với các bạn một cách dễ hiểu XML là gì, tiếp sau đó, chúng ta sẽ đi tìm hiểu cách sử dụng hàm FILTER XML đơn giản qua một số ví dụ xử lý dữ liệu trong Excel.

XML là một định dạng dữ liệu thường dùng để trao đổi dữ liệu giữa các hệ thống thông tin. Đặc biệt, nếu các bạn sử dụng các phần mềm hóa đơn ở Việt Nam, thì chắc chắn bạn đã nghe đến các tệp hay file XML rồi. Trong Excel phiên bản 2013 trở lên, có hàm FILTERXML. Nếu đứng một mình, thì hàm này không có nhiều ứng dụng ngoài việc sử dụng để đọc ra giá trị trong một chuỗi nội dung XML. Tuy nhiên, với sự ra mắt của những hàm mảng động trong Excel, thì sự kết hợp giữa hàm FILTERXML và các hàm mảng động sẽ tạo ra những ứng dụng rất có ích trong việc xử lý dữ liệu như tách dữ liệu, sắp xếp dữ liệu, chọn lọc dữ liệu trong Excel.

XML là gì

Để trả lời cho câu hỏi XML là gì, không có cách gì dễ hơn là chúng ta sẽ đi vào một ví dụ:

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

Trong đoạn XML bên trên, mình đã chủ động xuống dòng và lùi vào đầu dòng ở nơi cần thiết để bạn có thể dễ dàng nhận ra được cấu trúc của đoạn XML này. Trong đoạn XML phía trên:

  • <parent>, <item>, </parent>, </item> được gọi là các thẻ tag;
  • <parent>, <item> là các thẻ mở (Tiếng Anh: opening tag);
  • </parent>, </item> là các thẻ đóng (Tiếng Anh: closing tag);
  • Các giá trị nằm giữa thẻ mở và thẻ đóng tương ứng như id, first_name, last_name, gender, email được gọi là giá trị;
  • Các tên thẻ như parent, hay item là do bạn tự đặt, miễn là các thẻ ở đúng vị trí, đúng cấu trúc là được.

Sau khi đã hiểu được cấu trúc cơ bản của một đoạn XML, chúng ta hãy hẹn nhau ở phần tiếp theo để chúng ta có thể từng bước áp dụng hàm FILTERXML để xử lý dữ liệu.

Cách dùng hàm FILTERXML để xử lý dữ liệu

Cú pháp của hàm FILTERXML

=FILTERXML( chuỗi_XML, chuỗi_XPATH )

Trong đó:

  • chuỗi_XML là tham số bắt buộc, là một chuỗi dữ liệu có định dạng như chúng ta nói tới ở phần trên
  • chuỗi_XPATH là tham số bắt buộc, là một chuỗi với cú pháp đặc biệt để hàm FILTERXML có thể hiểu và trả về cho chúng ta dữ liệu cần tìm kiếm ở trong chuỗi_XML

Chú ý:

  • Vì hàm FILTERXML sử dụng chức năng đặc trưng của hệ điều hành Windows, nên hàm này sẽ không hoạt động trên phiên bản Excel Web và phiên bản Excel trên máy MacOS
  • Hàm FILTERXML có mặt trên Excel phiên bản 2013 trở lên
  • Nếu chuỗi_XML có định dạng không hợp lệ, thì kết quả của hàm FILTERXML sẽ là #VALUE!

Cách sử dụng hàm FILTERXML để tách dữ liệu thành nhiều cột

Với những kiến thức cơ bản về hàm FILTERXML ở trên, thì chúng ta sẽ bắt đầu với một ví dụ đầu tiên để ứng dụng hàm FILTERXML trong việc tách dữ liệu bị ngăn cách bởi dấu phẩy (hoặc dấu bất kỳ) thành nhiều cột trong Excel.

Giả sử bạn có dữ liệu trong cột A như sau, và bạn muốn tách dữ liệu đó ra thành nhiều cột như bên tay phải của hình minh họa:

Ví dụ ứng dụng hàm FILTERXML để tách dữ liệu ra nhiều cột trong Excel

Với vấn đề này, bạn có thể hoàn toàn sử dụng chức năng Text To Columns ở trong Excel để giải quyết hết sức nhanh chóng. Tuy nhiên, chúng ta sẽ sử dụng hàm FILTERXML trong trường hợp này để có được sự linh hoạt của hàm.

Đầu tiên, để chuyển được dữ liệu đang bị ngăn cách bởi các dấu “,” sang dạng XML, thì chúng ta sẽ sử dụng hàm SUBSTITUTE như sau trong ô C1:

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

Kết quả của hàm này là:

"id</i><i>first_name</i><i>last_name</i><i>email</i><i>gender"

Như vậy, kết quả của chúng ta còn thiếu <p><i> ở đầu chuỗi và thiếu </i></p> ở cuối chuỗi để có được một chuỗi XML hợp lệ. Điều này có thể được giải quyết đơn giản bằng cách ghép nối các chuỗi này lần lượt vào vị trí đầu và cuối của kết quả hàm Substitute

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

Sau đó, chúng ta sẽ có một chuỗi XML hợp lệ:

"<p><i>id</i><i>first_name</i><i>last_name</i><i>email</i><i>gender</i></p>"

Với chuỗi XML hợp lệ này, chúng ta có thể sử dụng chuỗi_XPATH là "//i" để có thể lấy ra giá trị giữa những thẻ tag <i> và </i>

(Nếu bạn đặt thẻ tag là <item> thì chuỗi_XPATH trong trường hợp này bạn cần sử dụng là "//item")

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

Kết quả của hàm FILTERXML này là:

Kết quả khi sử dụng hàm FILTERXML

Để chuyển kết quả này từ dạng cột dọc thành hàng ngang, chúng ta sẽ lồng ghép thêm hàm Transpose với hàm FILTERXML như sau:

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

Sau đó, chúng ta có thể kéo công thức này xuống phía dưới cho các dòng còn lại để nhận được kết quả tách dữ liệu bị ngăn cách bởi dấu phẩy thành nhiều cột.

Ví dụ ứng dụng hàm FILTERXML để tách dữ liệu ra nhiều cột trong Excel

Cách sử dụng hàm FILTERXML để sắp xếp dữ liệu trong một ô

Trong một số trường hợp đặc biệt, dữ liệu được xuất ra từ hệ thống hay bạn nhận được dữ liệu từ một đồng nghiệp mà lại phát sinh nhu cầu sắp xếp dữ liệu được lưu trong một ô chứ không sắp xếp dữ liệu được lưu trong một cột, thì đây chính xác là kỹ thuật bạn cần nhớ tới và nắm vững. Để có thể sắp xếp được dữ liệu và có kết quả như trong cột C ở trong hình minh họa:

Sử dụng hàm FILTERXML để sắp xếp dữ liệu trong một ô

Để giải quyết vấn đề này, chúng ta lại bắt đầu từ hàm FILTERXML như sau trong ô C1:

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

Với công thức này, chúng ta sẽ đạt được kết quả như ở phần trước như sau:

Kết quả khi sử dụng hàm FILTERXML

Sau đó, để sắp xếp, thì chúng ta sẽ lồng ghép hàm SORT ra ngoài hàm FILTERXML như sau

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

Kết quả của việc này là cột dữ liệu sẽ được sắp xếp lại:

Cách sử dụng hàm FILTERXML để sắp xếp dữ liệu trong ô

Sau đó, chúng ta sẽ áp dụng hàm TEXTJOIN để có thể nối lại cột dữ liệu này sau khi đã sắp xếp:

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

Và kết quả có thể áp dụng được cho cả những dòng sau đó:

Sử dụng hàm FILTERXML để sắp xếp dữ liệu trong một ô

Đọc số thành chữ khi kết hợp với hàm Webservice

Trong một số trường hợp, khi bạn sử dụng dịch vụ, API đọc số thành chữ mà những API này trả về dữ liệu là XML, thì bạn hoàn toàn có thể sử dụng kết hợp hàm Webservice và hàm FILTERXML để có thể đọc ra kết quả 1 cách nhanh chóng. Điều này không chỉ áp dụng cho việc đọc số thành chữ, mà còn có thể áp dụng cho bất cứ nguồn dữ liệu nào trả kết quả về là XML như một số sàn chứng khoán, một số trang Web dữ liệu về thời tiết.

Như vậy, qua bài này, mình đã hướng dẫn cách bạn một số ví dụ ứng dụng hàm FILTERXML để xử lý dữ liệu cũng như khai thác dữ liệu trong Excel.

CÓ THỂ BẠN QUAN TÂM