Dữ liệu dạng "Tai nghe,Chuột,Màn hình" nằm trong một ô rất khó sắp xếp hoặc lấy riêng từng phần. Hàm FILTERXML trong Excel vốn dùng để đọc dữ liệu XML, nhưng bạn có thể tận dụng nó để tách chuỗi bằng một công thức.

Bài viết này đi qua 5 ví dụ trên một ô dữ liệu. Bài không kèm file vì bạn chỉ cần tự gõ 2 ô. Ảnh trong bài được chụp trên Excel 365 giao diện tiếng Anh, và màu tiêu đề trong ảnh chỉ để dễ nhìn.

Hàm FILTERXML không có trên Excel cho web và Excel trên Mac. Nếu file của bạn được mở trên các nền tảng đó, bạn nên dùng hàm TEXTSPLIT ở cuối bài. Các ví dụ có kết quả tràn và hàm SORT được chụp trên Excel 365, nên phiên bản cũ có thể không hiện giống ảnh.

Bạn cần gõ dữ liệu này trước khi làm theo

Bạn gõ "Danh sách trong một ô" vào ô A1, rồi gõ nội dung sau vào ô A2. Giữa các mục chỉ có dấu phẩy, không có dấu cách.

Tai nghe,Chuột,Màn hình,Bàn phím

Ô A2 chứa 4 sản phẩm ngăn cách bằng dấu phẩy
Ô A2 chứa cả 4 sản phẩm, và các công thức trong bài đặt ở ô C2.

Khi sang ví dụ mới, bạn xóa kết quả cũ từ cột C trở đi rồi gõ công thức mới vào C2. Ô C1 trong ảnh chỉ là tiêu đề cho dễ đọc.

Nếu Excel báo lỗi khi bạn gõ dấu phẩy ngăn cách tham số, máy của bạn đang dùng dấu chấm phẩy. Khi đó, bạn đổi các dấu phẩy ngăn cách tham số thành dấu chấm phẩy. Riêng dấu phẩy nằm trong dấu ngoặc kép là ký tự cần tìm, nên bạn giữ nguyên.

Vì sao hàm FILTERXML tách được chuỗi?

Hàm có cú pháp =FILTERXML(xml, xpath). Tham số xml là chuỗi XML, còn xpath cho biết cần lấy phần nào. Ô A2 chưa phải XML, nên bạn cần đổi nó trước. Bạn gõ công thức sau vào C2 để xem kết quả:

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

Mỗi dấu phẩy được thay bằng cặp thẻ, nên mỗi sản phẩm nằm trong một thẻ <i> riêng. Tên thẻ p và i là do bạn tự đặt.

Công thức SUBSTITUTE tạo chuỗi XML từ ô A2
Chuỗi kết quả có 4 thẻ i nằm trong một thẻ p.

Ví dụ 1: Hàm FILTERXML tách một ô thành nhiều dòng

Bạn đưa chuỗi XML vào hàm và dùng //i để lấy mọi thẻ i. Bạn gõ công thức sau vào C2:

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

Kết quả tràn xuống 4 dòng là Tai nghe, Chuột, Màn hình và Bàn phím.

Công thức FILTERXML tách ô A2 thành 4 dòng
Mỗi thẻ i trở thành một dòng kết quả.

Ví dụ 2: Hàm TRANSPOSE đổi kết quả thành nhiều cột

Bạn bọc thêm hàm TRANSPOSE để chuyển dòng thành cột. Bạn gõ công thức sau vào C2:

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

Kết quả nằm ngang trong các ô C2:F2.

Công thức TRANSPOSE đưa 4 sản phẩm ra 4 cột
Hàm TRANSPOSE xoay kết quả từ chiều dọc sang chiều ngang.

Ví dụ 3: Hàm SORT sắp xếp các giá trị

Bạn gõ công thức sau vào C2:

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

Kết quả lần lượt là Bàn phím, Chuột, Màn hình và Tai nghe.

Công thức SORT sắp xếp 4 sản phẩm theo bảng chữ cái
Thứ tự chữ có dấu có thể khác nhau tùy cài đặt vùng của máy.

Ví dụ 4: Hàm TEXTJOIN nối kết quả đã sắp xếp vào một ô

Bạn bọc thêm hàm TEXTJOIN để đưa kết quả về một ô. Bạn gõ công thức sau vào C2:

=TEXTJOIN(",",TRUE,SORT(FILTERXML("<p><i>"&SUBSTITUTE(A2,",","</i><i>")&"</i></p>","//i")))

Ô C2 trả về Bàn phím,Chuột,Màn hình,Tai nghe. Cách này giúp bạn sắp xếp nội dung ngay trong một ô.

Công thức TEXTJOIN nối 4 sản phẩm đã sắp xếp vào một ô
Kết quả quay lại dạng một ô nhưng đã đúng thứ tự.

Ví dụ 5: Hàm FILTERXML lấy đúng phần tử thứ n

Bạn thêm số thứ tự trong dấu ngoặc vuông để lấy một phần tử. Bạn gõ công thức sau vào C2:

=FILTERXML("<p><i>"&SUBSTITUTE(A2,",","</i><i>")&"</i></p>","//i[2]")

Ô C2 trả về Chuột, là mục thứ 2 trong danh sách.

Công thức FILTERXML với //i[2] lấy sản phẩm thứ 2
Phần [2] trong xpath chọn đúng thẻ i thứ 2.

Phần xpath còn có vài cách viết hữu ích khác. Ví dụ, //i[last()] lấy phần tử cuối. Với dữ liệu là số, //i[.>6] chỉ lấy các số lớn hơn 6, còn với ô A2 toàn chữ thì công thức báo #VALUE!.

Bạn thường gặp những lỗi nào với hàm FILTERXML?

  • Dữ liệu có dấu & gây lỗi #VALUE!: Công thức =FILTERXML("<p><i>Tôm & Cá</i></p>","//i") báo lỗi vì dấu & có ý nghĩa riêng trong XML. Bạn cần thay dấu đó trước khi tạo XML, như công thức =FILTERXML("<p><i>"&SUBSTITUTE(SUBSTITUTE(A2,"&","&amp;"),",","</i><i>")&"</i></p>","//i"). Dấu < cũng gây lỗi tương tự, và bạn thay nó bằng &lt;.

  • Thẻ bị thiếu gây lỗi #VALUE!: Chuỗi XML phải có đủ thẻ mở và thẻ đóng. Ví dụ, =FILTERXML("<p><i>a</p>","//i") báo lỗi. Bạn nên kiểm tra bằng công thức ở phần đầu bài.

  • Mục rỗng gây lỗi #VALUE!: Chuỗi có 2 dấu phẩy liền nhau sẽ tạo một thẻ rỗng, và dòng đó báo lỗi.

  • Xpath không khớp gây lỗi #VALUE!: Công thức =FILTERXML("<p><i>a</i></p>","//b") báo lỗi vì không có thẻ b.

  • Kết quả báo lỗi #SPILL!: Các ô bên dưới hoặc bên phải đang có dữ liệu. Bạn cần xóa các ô đó.

Người đọc thường hỏi gì về hàm FILTERXML trong Excel?

Hàm TEXTSPLIT có thay được FILTERXML không?

Với việc tách chuỗi, hàm TEXTSPLIT đơn giản hơn trên Excel 365. Công thức =TEXTSPLIT(A2,",") tách ra nhiều cột, còn =TEXTSPLIT(A2,,",") tách ra nhiều dòng. Hàm FILTERXML vẫn hữu ích khi bạn dùng phiên bản chưa có TEXTSPLIT, hoặc khi cần lọc bằng xpath.

Kết quả của FILTERXML có phải là số không?

Hàm tự đổi nội dung là chữ số thành số. Ví dụ, =SUM(FILTERXML("<p><i>5</i><i>12</i></p>","//i")) trả về 17. Vì vậy, mã có số 0 ở đầu sẽ bị mất số 0, như 007 trở thành 7.

Bạn muốn dùng Excel thành thạo cho công việc văn phòng?

Hàm FILTERXML cho thấy một hàm có thể được dùng sáng tạo hơn mục đích ban đầu, và đây là kỹ năng rất hữu ích khi xử lý dữ liệu.

Nếu muốn học bài bản cùng người hướng dẫn, bạn có thể tham khảo khóa 30 ngày học và thực hành Excel của Nghề Excel, với 30 buổi luyện tập, hơn 500 bài tập thực tế lấy từ doanh nghiệp và coach đồng hành cùng bạn trong từng buổi.

Ảnh chụp màn hình sản phẩm Microsoft: Used with permission from Microsoft.