{"id":809,"date":"2024-12-02T02:52:50","date_gmt":"2024-12-02T02:52:50","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/7-ung-dung-tach-chuoi-voi-ham-filterxml-trong-excel\/"},"modified":"2024-12-02T02:52:50","modified_gmt":"2024-12-02T02:52:50","slug":"7-ung-dung-tach-chuoi-voi-ham-filterxml-trong-excel","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/7-ung-dung-tach-chuoi-voi-ham-filterxml-trong-excel\/","title":{"rendered":"7 \u1ee9ng d\u1ee5ng t\u00e1ch chu\u1ed7i v\u1edbi h\u00e0m FILTERXML trong Excel"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p>FILTERXML l\u00e0 m\u1ed9t h\u00e0m \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng trong Excel \u0111\u1ec3 l\u1ea5y th\u00f4ng tin t\u1eeb nh\u1eefng \u0111o\u1ea1n XML. Hi\u1ec7n h\u00e0m n\u00e0y kh\u1ea3 d\u1ee5ng trong c\u00e1c phi\u00ean b\u1ea3n Excel 2013, 2016, 2019, 2021 v\u00e0 Excel 365 cho Windows (kh\u00f4ng kh\u1ea3 d\u1ee5ng v\u1edbi Excel tr\u00ean Web v\u00e0 Excel cho Mac). M\u1ee5c \u0111\u00edch \u0111\u1ea7u ti\u00ean c\u1ee7a h\u00e0m l\u00e0 k\u1ebft h\u1ee3p v\u1edbi 2 h\u00e0m kh\u00e1c bao g\u1ed3m ENCODEURL v\u00e0 WEBSERVICE \u0111\u1ec3 l\u1ea5y d\u1eef li\u1ec7u d\u01b0\u1edbi d\u1ea1ng xml v\u1ec1 v\u00e0 x\u1eed l\u00fd ch\u00fang. Tuy v\u1eady, c\u00f3 r\u1ea5t nhi\u1ec1u c\u00e1ch \u0111\u1ec3 \u1ee9ng d\u1ee5ng Xpath trong FILTERXML \u0111\u1ec3 x\u1eed l\u00fd c\u00e1c b\u00e0i to\u00e1n t\u00e1ch chu\u1ed7i. D\u01b0\u1edbi \u0111\u00e2y s\u1ebd l\u00e0 7 \u1ee9ng d\u1ee5ng t\u00e1ch chu\u1ed7i v\u1edbi h\u00e0m FILTERXML trong Excel<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_61 counter-hierarchy ez-toc-counter ez-toc-transparent ez-toc-container-direction\">\n<p class=\"ez-toc-title\">Xem nhanh<\/p>\n<p><label for=\"ez-toc-cssicon-toggle-item-674d2101785c7\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"\"><span class=\"eztoc-hide\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><\/span><\/span><\/label><\/p>\n<nav>\n<ul class=\"ez-toc-list ez-toc-list-level-1 \">\n<li class=\"ez-toc-page-1 ez-toc-heading-level-1\"><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/blog.hocexcel.online\/7-ung-dung-voi-filterxml.html#Ham_FILTERXML\" title=\"H\u00e0m FILTERXML\">H\u00e0m FILTERXML<\/a>\n<ul class=\"ez-toc-list-level-2\">\n<li class=\"ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/blog.hocexcel.online\/7-ung-dung-voi-filterxml.html#1_Tach_chuoi_voi_ham_FILTERXML\" title=\"1. T\u00e1ch chu\u1ed7i v\u1edbi h\u00e0m FILTERXML\">1. T\u00e1ch chu\u1ed7i v\u1edbi h\u00e0m FILTERXML<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/blog.hocexcel.online\/7-ung-dung-voi-filterxml.html#2_Lay_mot_phan_tu_xac_dinh_trong_chuoi\" title=\"2. L\u1ea5y m\u1ed9t ph\u1ea7n t\u1eed x\u00e1c \u0111\u1ecbnh trong chu\u1ed7i\">2. L\u1ea5y m\u1ed9t ph\u1ea7n t\u1eed x\u00e1c \u0111\u1ecbnh trong chu\u1ed7i<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/blog.hocexcel.online\/7-ung-dung-voi-filterxml.html#3_Lay_gia_tri_theo_khoang_vi_tri\" title=\"3. L\u1ea5y gi\u00e1 tr\u1ecb theo kho\u1ea3ng v\u1ecb tr\u00ed\">3. L\u1ea5y gi\u00e1 tr\u1ecb theo kho\u1ea3ng v\u1ecb tr\u00ed<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/blog.hocexcel.online\/7-ung-dung-voi-filterxml.html#4_Lay_nhung_o_chua_ki_tu_duoc_xac_dinh\" title=\"4. L\u1ea5y nh\u1eefng \u00f4 ch\u1ee9a k\u00ed t\u1ef1 \u0111\u01b0\u1ee3c x\u00e1c \u0111\u1ecbnh\">4. L\u1ea5y nh\u1eefng \u00f4 ch\u1ee9a k\u00ed t\u1ef1 \u0111\u01b0\u1ee3c x\u00e1c \u0111\u1ecbnh<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/blog.hocexcel.online\/7-ung-dung-voi-filterxml.html#5_Map_gia_tri_trong_Xpath\" title=\"5. Map gi\u00e1 tr\u1ecb trong Xpath\">5. Map gi\u00e1 tr\u1ecb trong Xpath<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/blog.hocexcel.online\/7-ung-dung-voi-filterxml.html#6_Lay_nhung_phan_tu_la_so\" title=\"6. L\u1ea5y nh\u1eefng ph\u1ea7n t\u1eed l\u00e0 s\u1ed1\">6. L\u1ea5y nh\u1eefng ph\u1ea7n t\u1eed l\u00e0 s\u1ed1<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/blog.hocexcel.online\/7-ung-dung-voi-filterxml.html#7_Tach_cac_gia_tri_khong_trung_lap\" title=\"7. T\u00e1ch c\u00e1c gi\u00e1 tr\u1ecb kh\u00f4ng tr\u00f9ng l\u1eb7p\">7. T\u00e1ch c\u00e1c gi\u00e1 tr\u1ecb kh\u00f4ng tr\u00f9ng l\u1eb7p<\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h1 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Ham_FILTERXML\"><\/span>H\u00e0m FILTERXML<span class=\"ez-toc-section-end\"><\/span><\/h1>\n<p>H\u00e0m FILTERXML \u0111\u01b0\u1ee3c vi\u1ebft nh\u01b0 sau:<\/p>\n<pre class=\"wp-block-code\"><code>=FILTERXML(xml,xpath)<\/code><\/pre>\n<p>Trong \u0111\u00f3, xml l\u00e0 \u0111o\u1ea1n \u0111\u1ecbnh d\u1ea1ng xml c\u1ee7a ch\u00fang ta, c\u00f2n xpath l\u00e0 chu\u1ed7i \u1edf d\u1ea1ng standard xpath. Phi\u00ean b\u1ea3n xpath \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng l\u00e0 1.0.<\/p>\n<p>D\u01b0\u1edbi \u0111\u00e2y l\u00e0 m\u1ed9t v\u00ed d\u1ee5 v\u1ec1 \u0111o\u1ea1n xml:<\/p>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"342\" height=\"263\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/image-13.png\" class=\"wp-image-40889\" title=\"image-13\"><figcaption class=\"wp-element-caption\">XML m\u1eabu<\/figcaption><\/figure>\n<\/div>\n<p>Nh\u1eefng th\u00e0nh ph\u1ea7n \u0111\u01b0\u1ee3c ghi trong c\u1eb7p d\u1ea5u &lt;&gt; g\u1ecdi l\u00e0 c\u00e1c element. Ngo\u00e0i ra ch\u00fang ta c\u00f2n c\u00f3 1 s\u1ed1 \u0111\u1ed1i t\u01b0\u1ee3ng kh\u00e1c nh\u01b0 attributes, namespaces nh\u01b0ng trong ph\u1ea1m vi b\u00e0i vi\u1ebft n\u00e0y ta s\u1ebd kh\u00f4ng \u0111i qu\u00e1 s\u00e2u. \u0110\u1ec3 t\u00ecm hi\u1ec3u th\u00eam, H\u1ecdc Excel Online g\u1ee3i \u00fd b\u1ea1n \u0111\u1ecdc 2 n\u1ed9i dung v\u1ec1 xml v\u00e0 xpath tr\u00ean w3school \u0111\u1ec3 c\u00f3 c\u00e1i nh\u00ecn c\u01a1 b\u1ea3n:<\/p>\n<p>XML: <a href=\"https:\/\/www.w3schools.com\/xml\/xml_whatis.asp\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/www.w3schools.com\/xml\/xml_whatis.asp<\/a><\/p>\n<p>Xpath: <a href=\"https:\/\/www.w3schools.com\/xml\/xpath_intro.asp\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/www.w3schools.com\/xml\/xpath_intro.asp<\/a><\/p>\n<div class=\"code-block code-block-1\" style=\"margin: 8px 0;clear: both\">\n<div class=\"ad-inserterpro\">\n<a href=\"https:\/\/hocexcel.online\/course\/preview\/ex101-excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/ex101_92019-nho-47.gif\" title=\"ex101_92019-nho-47\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p>Quay tr\u1edf l\u1ea1i v\u1edbi b\u00e0i vi\u1ebft, ta c\u00f3 th\u1ec3 bi\u1ebfn m\u1ed9t chu\u1ed7i b\u1ea5t k\u1ef3 th\u00e0nh \u0111\u1ecbnh d\u1ea1ng xml b\u1eb1ng h\u00e0m SUBSTITUTE \u0111\u01a1n gi\u1ea3n. Trong b\u00e0i vi\u1ebft n\u00e0y, ta s\u1ebd c\u00f3 v\u00ed d\u1ee5 l\u00e0 1 chu\u1ed7i nh\u01b0 sau:<\/p>\n<blockquote class=\"wp-block-quote\">\n<p>Hieu, Hieu, An, Nam, An, 100, 1234, Thuc, Tuan Anh, 567, An, Tuan, Long<\/p>\n<\/blockquote>\n<p>\u0110\u1ec3 thu\u1eadn ti\u1ec7n s\u1eed d\u1ee5ng FILTERXML, chu\u1ed7i \u0111\u01b0\u1ee3c bi\u1ebfn \u0111\u1ed5i v\u1edbi c\u00f4ng th\u1ee9c:<\/p>\n<pre class=\"wp-block-code\"><code>=\"&lt;a&gt;&lt;b&gt;\"&amp;SUBSTITUTE(chu\u1ed7i,\", \",\"&lt;\/b&gt;&lt;b&gt;\")&amp;\"&lt;\/b&gt;&lt;\/a&gt;\"<\/code><\/pre>\n<p>K\u1ebft qu\u1ea3:<\/p>\n<blockquote class=\"wp-block-quote\">\n<p>&lt;a&gt;&lt;b&gt;Hieu&lt;\/b&gt;&lt;b&gt;Hieu&lt;\/b&gt;&lt;b&gt;An&lt;\/b&gt;&lt;b&gt;Nam&lt;\/b&gt;&lt;b&gt;An&lt;\/b&gt;&lt;b&gt;100&lt;\/b&gt;&lt;b&gt;1234&lt;\/b&gt;&lt;b&gt;Thuc&lt;\/b&gt;&lt;b&gt;Tuan Anh&lt;\/b&gt;&lt;b&gt;567&lt;\/b&gt;&lt;b&gt;An&lt;\/b&gt;&lt;b&gt;Tuan&lt;\/b&gt;&lt;b&gt;Long&lt;\/b&gt;&lt;\/a&gt;<\/p>\n<\/blockquote>\n<p>T\u1eeb \u0111\u00e2y, c\u00f3 th\u1ec3 ngh\u0129 \u0111\u1ebfn nh\u1eefng \u1ee9ng d\u1ee5ng v\u00f4 c\u00f9ng m\u1ea1nh m\u1ebd v\u1edbi FILTERXML. Trong nh\u1eefng c\u00f4ng th\u1ee9c d\u01b0\u1edbi, m\u1eb7c \u0111\u1ecbnh \u201c<strong>xml<\/strong>\u201d l\u00e0 chu\u1ed7i \u0111\u00e3 \u0111\u01b0\u1ee3c bi\u1ebfn \u0111\u1ed5i b\u00ean tr\u00ean<\/p>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"1_Tach_chuoi_voi_ham_FILTERXML\"><\/span>1. T\u00e1ch chu\u1ed7i v\u1edbi h\u00e0m FILTERXML<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<pre class=\"wp-block-code\"><code>=FILTERXML(xml,\"\/\/b\")<\/code><\/pre>\n<p>FILTERXML c\u00f3 kh\u1ea3 n\u0103ng t\u00e1ch chu\u1ed7i v\u00e0 tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 theo chi\u1ec1u d\u1ecdc. N\u1ebfu phi\u00ean b\u1ea3n b\u1ea1n \u0111ang s\u1eed d\u1ee5ng kh\u00f4ng ph\u1ea3i l\u00e0 Office 365, c\u1ea7n ch\u1ecdn v\u00f9ng tr\u1ea3 k\u1ebft qu\u1ea3 (theo chi\u1ec1u d\u1ecdc) tr\u01b0\u1edbc, nh\u1eadp c\u00f4ng th\u1ee9c v\u00e0 \u1ea5n Ctrl + Shift + Enter. <em>N\u1ebfu b\u1ea1n ch\u01b0a hi\u1ec3u, vui l\u00f2ng \u0111\u1ecdc b\u00e0i vi\u1ebft v\u1ec1 c\u00e1ch vi\u1ebft c\u00f4ng th\u1ee9c m\u1ea3ng tr\u01b0\u1edbc: <\/em><a href=\"http:\/\/blog.ezworkapp.com\/ung-dung-excel-tim-hieu-ve-cong-thuc-mang-trong-excel.html\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/blog.hocexcel.online\/ung-dung-excel-tim-hieu-ve-cong-thuc-mang-trong-excel.html<\/a><\/p>\n<p>C\u00f2n n\u1ebfu b\u1ea1n \u0111ang s\u1eed d\u1ee5ng Office 365, ch\u1ec9 c\u1ea7n \u1ea5n Enter th\u00ec t\u1ea5t c\u1ea3 c\u00e1c ph\u1ea7n t\u1eed \u0111\u01b0\u1ee3c t\u00e1ch s\u1ebd hi\u1ec3n th\u1ecb th\u00e0nh m\u1ed9t c\u1ed9t. <em>V\u00e0 b\u1ea1n c\u00f3 bi\u1ebft Xpath s\u1ebd t\u1ef1 \u0111\u1ed9ng Trim nh\u1eefng kho\u1ea3ng tr\u1eafng \u1edf \u0111\u1ea7u kh\u00f4ng?<\/em> <\/p>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"630\" height=\"570\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/image-1-5.png\" class=\"wp-image-40890\" title=\"image-1-5\"><\/figure>\n<\/div>\n<p>\u1ee8ng d\u1ee5ng n\u00e2ng cao: <strong>G\u1ed9p c\u1ed9t v\u1edbi TEXTJOIN<\/strong><\/p>\n<p>Trong Office 2019 ch\u00fang ta \u0111\u00e3 c\u00f3 TEXTJOIN, tuy nhi\u00ean ch\u01b0a c\u00f3 h\u00e0m ch\u1ed3ng c\u1ed9t m\u1edbi l\u00e0 VSTACK. V\u1eady n\u00ean n\u1ebfu b\u1ea1n c\u00f3 1 b\u1ea3ng nhi\u1ec1u c\u1ed9t\/m\u1ea3ng 2 chi\u1ec1u, c\u00e1c b\u1ea1n c\u00f3 th\u1ec3 g\u1ed9p c\u00e1c gi\u00e1 tr\u1ecb l\u1ea1i v\u1edbi TEXTJOIN, r\u1ed3i t\u00e1ch b\u1eb1ng FILTERXML \u0111\u1ec3 \u0111\u01b0\u1ee3c hi\u1ec7u \u1ee9ng g\u1ed9p c\u1ed9t. C\u00f2n n\u1ebfu mu\u1ed1n g\u1ed9p th\u00e0nh 1 h\u00e0ng? Th\u00eam 1 h\u00e0m TRANSPOSE l\u00e0 c\u00e2u tr\u1ea3 l\u1eddi cho b\u1ea1n.<\/p>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"832\" height=\"219\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/image-2-6.png\" class=\"wp-image-40891\" title=\"image-2-6\"><figcaption class=\"wp-element-caption\">FILTERXML + TEXTJOIN<\/figcaption><\/figure>\n<\/div>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"2_Lay_mot_phan_tu_xac_dinh_trong_chuoi\"><\/span>2. L\u1ea5y m\u1ed9t ph\u1ea7n t\u1eed x\u00e1c \u0111\u1ecbnh trong chu\u1ed7i<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<pre class=\"wp-block-code\"><code>=FILTERXML(xml,\"\/\/b[nh\u1eadp s\u1ed1 v\u00e0o \u0111\u00e2y]\")<\/code><\/pre>\n<p>Ta c\u00f3 th\u1ec3 x\u00e1c \u0111\u1ecbnh ph\u1ea7n t\u1eed c\u1ea7n l\u1ea5y b\u1eb1ng c\u00e1ch nh\u1eadp v\u1ecb tr\u00ed c\u1ee7a ph\u1ea7n t\u1eed v\u00e0o trong d\u1ea5u ngo\u1eb7c vu\u00f4ng. Ch\u1eb3ng h\u1ea1n, \u0111\u1ec3 l\u1ea5y ph\u1ea7n t\u1eed th\u1ee9 3, ta s\u1ebd \u0111\u1eb7t l\u00e0 \/\/b[3] v\u00e0 khi \u0111\u00f3 h\u00e0m s\u1ebd tr\u1ea3 v\u1ec1 duy nh\u1ea5t 1 ph\u1ea7n t\u1eed.<\/p>\n<p>\u1ee8ng d\u1ee5ng n\u00e2ng cao: <strong>K\u1ebft h\u1ee3p v\u1edbi MATCH \u0111\u1ec3 l\u1ecdc ra nh\u1eefng th\u00f4ng tin c\u1ea7n l\u1ea5y<\/strong>.<\/p>\n<p>Gi\u1ea3 s\u1eed ch\u00fang ta c\u00f3 1 chu\u1ed7i csv theo d\u1ea1ng \u201cA, 1\/1\/2020, 3\u201d v\u00e0 3 ti\u00eau \u0111\u1ec1 c\u1ed9t t\u01b0\u01a1ng \u1ee9ng l\u00e0 \u201cT\u00ean, DOB, Tu\u1ed5i\u201d. \u0110\u1ec3 l\u1ea5y tu\u1ed5i ta s\u1ebd l\u1ed3ng h\u00e0m MATCH v\u00e0o trong FILTERXML nh\u01b0 \u1ea3nh:<\/p>\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"765\" height=\"178\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/image-3-6.png\" class=\"wp-image-40892\" title=\"image-3-6\"><figcaption class=\"wp-element-caption\">\u0110em age v\u00e0o trong MATCH v\u1edbi d\u00f2ng ti\u00eau \u0111\u1ec1 \u0111\u1ec3 l\u1ea5y gi\u00e1 tr\u1ecb t\u01b0\u01a1ng \u1ee9ng<\/figcaption><\/figure>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"3_Lay_gia_tri_theo_khoang_vi_tri\"><\/span>3. L\u1ea5y gi\u00e1 tr\u1ecb theo kho\u1ea3ng v\u1ecb tr\u00ed<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<pre class=\"wp-block-code\"><code>=FILTERXML(xml,\"\/\/b[position()&lt;=nh\u1eadp s\u1ed1 v\u00e0o \u0111\u00e2y]\")<\/code><\/pre>\n<p>Ngo\u00e0i c\u00e1ch vi\u1ebft ng\u1eafn g\u1ecdn \/\/b[nh\u1eadp s\u1ed1] nh\u01b0 tr\u00ean, ta c\u00f3 th\u1ec3 vi\u1ebft kh\u00e1i qu\u00e1t b\u1eb1ng h\u00e0m position(). Ch\u1eb3ng h\u1ea1n, n\u1ebfu mu\u1ed1n l\u1ea5y 4 gi\u00e1 tr\u1ecb \u0111\u1ea7u, ta vi\u1ebft \/\/b[position()&lt;=4]. Khi \u0111\u00f3, chu\u1ed7i xml s\u1ebd tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 nh\u01b0 h\u00ecnh:<\/p>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"601\" height=\"366\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/image-4-4.png\" class=\"wp-image-40893\" title=\"image-4-4\"><\/figure>\n<\/div>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"4_Lay_nhung_o_chua_ki_tu_duoc_xac_dinh\"><\/span>4. L\u1ea5y nh\u1eefng \u00f4 ch\u1ee9a k\u00ed t\u1ef1 \u0111\u01b0\u1ee3c x\u00e1c \u0111\u1ecbnh<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<pre class=\"wp-block-code\"><code>=FILTERXML(xml,\"\/\/b[contains(.,'nh\u1eadp k\u00ed t\u1ef1 v\u00e0o \u0111\u00e2y, l\u01b0u \u00fd \u0111\u00e2y l\u00e0 d\u1ea5u nh\u00e1y \u0111\u01a1n')]\")<\/code><\/pre>\n<p>Ta c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng h\u00e0m contains() \u0111\u1ec3 x\u00e1c \u0111\u1ecbnh xem nh\u1eefng ph\u1ea7n t\u1eed \u0111\u00f3 s\u1ebd ch\u1ee9a k\u00ed t\u1ef1 n\u00e0o. Ch\u1eb3ng h\u1ea1n, \u0111\u1ec3 l\u1ea5y nh\u1eefng ph\u1ea7n t\u1eed c\u00f3 ch\u1ee9a ch\u1eef \u201cH\u201d (l\u01b0u \u00fd, khi \u0111\u01b0a k\u00ed t\u1ef1 v\u00e0o contains, ta k\u1eb9p v\u00e0o trong d\u1ea5u nh\u00e1y \u0111\u01a1n ch\u1ee9 kh\u00f4ng ph\u1ea3i nh\u00e1y k\u00e9p:<\/p>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"588\" height=\"319\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/image-5-4.png\" class=\"wp-image-40894\" title=\"image-5-4\"><\/figure>\n<\/div>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"5_Map_gia_tri_trong_Xpath\"><\/span>5. Map gi\u00e1 tr\u1ecb trong Xpath<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<pre class=\"wp-block-code\"><code>=FILTERXML(xml,\"\/\/b[translate(.,'b\u1ed9 k\u00ed t\u1ef1 c\u1ea7n map','b\u1ed9 k\u00ed t\u1ef1 map')=.]\")<\/code><\/pre>\n<p>Trong Xpath, ta c\u00f3 h\u00e0m translate() c\u00f3 t\u00e1c d\u1ee5ng map c\u00e1c k\u00ed t\u1ef1 thay cho nhau. \u0110\u1ec3 hi\u1ec3u h\u01a1n v\u1ec1 h\u00e0m n\u00e0y, c\u00e1c  b\u1ea1n \u0111\u1ecdc th\u00eam t\u1ea1i link sau: <a href=\"https:\/\/developer.mozilla.org\/en-US\/docs\/Web\/XPath\/Functions\/translate\">https:\/\/developer.mozilla.org\/en-US\/docs\/Web\/XPath\/Functions\/translate<\/a>.<\/p>\n<pre class=\"wp-block-code\"><code>translate('abcabc','ab','xy') =&gt; xycxyc<\/code><\/pre>\n<p>\u1ee8ng d\u1ee5ng c\u1ee7a c\u00f4ng th\u1ee9c tr\u00ean l\u00e0 ta c\u00f3 th\u1ec3 so s\u00e1nh c\u00e1c gi\u00e1 tr\u1ecb sau khi map v\u1edbi gi\u00e1 tr\u1ecb ban \u0111\u1ea7u, t\u1eeb \u0111\u00f3 l\u1ecdc ra c\u00e1c ph\u1ea7n t\u1eed c\u1ea7n l\u1ea5y. V\u00ed d\u1ee5 d\u01b0\u1edbi \u0111\u00e2y s\u1ebd l\u1ea5y t\u1ea5t c\u1ea3 c\u00e1c gi\u00e1 tr\u1ecb kh\u00f4ng ch\u1ee9a s\u1ed1:<\/p>\n<p>=FILTERXML(xml,\u201d\/\/b[translate(.,\u20191234567890\u2032,\u201d)=.]\u201d)<\/p>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"558\" height=\"421\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/image-6-4.png\" class=\"wp-image-40898\" title=\"image-6-4\"><\/figure>\n<\/div>\n<p>L\u01b0u \u00fd: \u1ede \u0111\u00e2y s\u1ebd l\u1ea5y ra c\u00e1c gi\u00e1 tr\u1ecb <strong>kh\u00f4ng ch\u1ee9a s\u1ed1<\/strong>, ch\u1ee9 kh\u00f4ng ph\u1ea3i c\u00e1c gi\u00e1 tr\u1ecb <strong>kh\u00f4ng ph\u1ea3i l\u00e0 s\u1ed1<\/strong>. B\u1edfi v\u1eady, n\u1ebfu b\u1ea1n c\u00f3 1 ph\u1ea7n t\u1eed ki\u1ec3u \u201cTh12uc\u201d, ph\u1ea7n t\u1eed n\u00e0y c\u0169ng s\u1ebd b\u1ecb lo\u1ea1i tr\u1eeb b\u1edfi c\u00f3 ch\u1ee9a s\u1ed1 b\u00ean trong.<\/p>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"6_Lay_nhung_phan_tu_la_so\"><\/span>6. L\u1ea5y nh\u1eefng ph\u1ea7n t\u1eed l\u00e0 s\u1ed1<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<pre class=\"wp-block-code\"><code>=FILTERXML(xml,\"\/\/b[number()=.]\")<\/code><\/pre>\n<p>V\u1edbi s\u1ef1 k\u1ebft h\u1ee3p c\u00f9ng h\u00e0m number(), ta c\u00f3 th\u1ec3 l\u1ea5y ra c\u00e1c gi\u00e1 tr\u1ecb s\u1ed1 trong chu\u1ed7i xml nh\u01b0 \u1ea3nh:<\/p>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"489\" height=\"282\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/image-7-4.png\" class=\"wp-image-40899\" title=\"image-7-4\"><\/figure>\n<\/div>\n<p>M\u1ed9t c\u00e1ch vi\u1ebft kh\u00e1c c\u0169ng cho k\u1ebft qu\u1ea3 t\u01b0\u01a1ng t\u1ef1: <\/p>\n<pre class=\"wp-block-code\"><code>=FILTERXML(xml,\"\/\/b[boolean(number())]\")<\/code><\/pre>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"7_Tach_cac_gia_tri_khong_trung_lap\"><\/span>7. T\u00e1ch c\u00e1c gi\u00e1 tr\u1ecb kh\u00f4ng tr\u00f9ng l\u1eb7p<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<pre class=\"wp-block-code\"><code>=FILTERXML(xml,\"\/\/b[not(. = following-sibling::*)]\")<\/code><\/pre>\n<p>S\u1eed d\u1ee5ng Xpath axes <strong>following-sibling<\/strong>, ta c\u00f3 th\u1ec3 l\u1ea5y ra \u0111\u01b0\u1ee3c c\u00e1c ph\u1ea7n t\u1eed \u201cdistinct\u201d, c\u00f3 ngh\u0129a l\u00e0 m\u1ed7i ph\u1ea7n t\u1eed s\u1ebd kh\u00f4ng l\u1eb7p l\u1ea1i trong m\u1ea3ng k\u1ebft qu\u1ea3 tr\u1ea3 v\u1ec1. V\u1edbi c\u00e1c m\u00e1y ch\u01b0a c\u00f3 h\u00e0m UNIQUE, \u0111\u00e2y l\u00e0 m\u1ed9t s\u1ef1 thay th\u1ebf r\u1ea5t h\u1eefu d\u1ee5ng.<\/p>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"544\" height=\"413\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/image-8-3.png\" class=\"wp-image-40900\" title=\"image-8-3\"><\/figure>\n<\/div>\n<hr class=\"wp-block-separator has-alpha-channel-opacity\">\n<p>Qua b\u00e0i vi\u1ebft ta c\u00f3 th\u1ec3 th\u1ea5y FILTERXML l\u00e0 m\u1ed9t h\u00e0m v\u00f4 c\u00f9ng h\u1eefu d\u1ee5ng, tuy nhi\u00ean t\u1eeb tr\u01b0\u1edbc t\u1edbi nay c\u00f3 m\u1ed9t s\u1ed1 nguy\u00ean nh\u00e2n khi\u1ebfn ta ch\u01b0a \u1ee9ng d\u1ee5ng \u0111\u01b0\u1ee3c v\u00e0o trong nhi\u1ec1u nh\u1eefng t\u00ecnh hu\u1ed1ng th\u1ef1c t\u1ebf nh\u01b0 h\u00e0m ch\u01b0a \u0111\u01b0\u1ee3c ph\u1ed5 bi\u1ebfn r\u1ed9ng \u1edf nhi\u1ec1u phi\u00ean b\u1ea3n, c\u0169ng nh\u01b0 Xpath kh\u00f4ng d\u1ec5 hi\u1ec3u v\u1edbi ng\u01b0\u1eddi d\u00f9ng ph\u1ed5 th\u00f4ng\u2026  <\/p>\n<p>C\u00e1c b\u1ea1n c\u00f3 th\u1ec3 \u0111\u1ecdc th\u00eam nh\u1eefng t\u00ednh n\u0103ng kh\u00e1c c\u1ee7a h\u00e0m \u1edf \u0111\u00e2y:<\/p>\n<ol>\n<li>D\u00f9ng h\u00e0m FILTERXML \u0111\u1ec3 t\u00e1ch v\u00e0 x\u1eed l\u00fd d\u1eef li\u1ec7u: <a href=\"http:\/\/blog.ezworkapp.com\/cach-dung-ham-filterxml-de-xu-ly-tach-va-sap-xep-du-lieu.html\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/blog.hocexcel.online\/cach-dung-ham-filterxml-de-xu-ly-tach-va-sap-xep-du-lieu.html<\/a><\/li>\n<li>L\u1ea5y t\u1ef7 gi\u00e1 ngo\u1ea1i t\u1ec7 c\u1ee7a Vietcombank v\u00e0o Excel: <a href=\"http:\/\/blog.ezworkapp.com\/tong-hop-cac-cach-lay-ty-gia-ngoai-te-tu-vcb-vao-excel-google-sheets.html\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/blog.hocexcel.online\/tong-hop-cac-cach-lay-ty-gia-ngoai-te-tu-vcb-vao-excel-google-sheets.html<\/a><\/li>\n<\/ol>\n<\/div>\n<div class=\"uk-panel uk-margin uk-width-xlarge\">\n<p>Click \u0111\u0103ng k\u00fd h\u1ecdc t\u1ea1i:<\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/khoa-hoc\" target=\"_blank\" rel=\"noopener\">T\u1ea5t c\u1ea3 kho\u00e1 h\u1ecdc\u00a0<span>\ud83d\udcd6<\/span><\/a><\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-kich-hoat-khoa-hoc-tai-hoc-excel-online.html\" target=\"_blank\" rel=\"noopener\">H\u01b0\u1edbng d\u1eabn k\u00edch ho\u1ea1t<\/a><\/p>\n<p>HKD H\u1ecdc Excel Online<br \/>S\u1ed1 \u0110K: 17A80048102<\/p>\n<\/div>\n<div class=\"uk-panel uk-text-small uk-margin uk-margin-remove-bottom uk-text-center\">\n<p>\u00a9  document.currentScript.insertAdjacentHTML(&#8216;afterend&#8217;, &#8221; + new Intl.DateTimeFormat(document.documentElement.lang, {year: &#8216;numeric&#8217;}).format() + &#8221;);  H\u1ecdc Excel Online. All rights reserved.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<div class=\"ad-inserterpro\">\n<a href=\"https:\/\/hocexcel.online\/course\/preview\/ex101-excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/ex101_92019-nho-47.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":810,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-809","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-co-ban"],"_links":{"self":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/809","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/comments?post=809"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/809\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/810"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=809"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=809"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=809"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}