{"id":550,"date":"2024-12-02T02:26:27","date_gmt":"2024-12-02T02:26:27","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/cach-dung-ham-filterxml-de-xu-ly-tach-va-sap-xep-du-lieu\/"},"modified":"2024-12-02T02:26:27","modified_gmt":"2024-12-02T02:26:27","slug":"cach-dung-ham-filterxml-de-xu-ly-tach-va-sap-xep-du-lieu","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/cach-dung-ham-filterxml-de-xu-ly-tach-va-sap-xep-du-lieu\/","title":{"rendered":"C\u00e1ch d\u00f9ng h\u00e0m FILTERXML \u0111\u1ec3 x\u1eed l\u00fd t\u00e1ch v\u00e0 s\u1eafp x\u1ebfp d\u1eef li\u1ec7u"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p>XML l\u00e0 m\u1ed9t \u0111\u1ecbnh d\u1ea1ng d\u1eef li\u1ec7u th\u01b0\u1eddng d\u00f9ng \u0111\u1ec3 trao \u0111\u1ed5i d\u1eef li\u1ec7u gi\u1eefa c\u00e1c h\u1ec7 th\u1ed1ng th\u00f4ng tin. \u0110\u1eb7c bi\u1ec7t, n\u1ebfu c\u00e1c b\u1ea1n s\u1eed d\u1ee5ng c\u00e1c ph\u1ea7n m\u1ec1m h\u00f3a \u0111\u01a1n \u1edf Vi\u1ec7t Nam, th\u00ec ch\u1eafc ch\u1eafn b\u1ea1n \u0111\u00e3 nghe \u0111\u1ebfn c\u00e1c t\u1ec7p hay file XML r\u1ed3i. Trong Excel phi\u00ean b\u1ea3n 2013 tr\u1edf l\u00ean, c\u00f3 h\u00e0m FILTERXML. N\u1ebfu \u0111\u1ee9ng m\u1ed9t m\u00ecnh, th\u00ec h\u00e0m n\u00e0y kh\u00f4ng c\u00f3 nhi\u1ec1u \u1ee9ng d\u1ee5ng ngo\u00e0i vi\u1ec7c s\u1eed d\u1ee5ng \u0111\u1ec3 \u0111\u1ecdc ra gi\u00e1 tr\u1ecb trong m\u1ed9t chu\u1ed7i n\u1ed9i dung XML. Tuy nhi\u00ean, v\u1edbi s\u1ef1 ra m\u1eaft c\u1ee7a nh\u1eefng h\u00e0m m\u1ea3ng \u0111\u1ed9ng trong Excel, th\u00ec s\u1ef1 k\u1ebft h\u1ee3p gi\u1eefa h\u00e0m FILTERXML v\u00e0 c\u00e1c h\u00e0m m\u1ea3ng \u0111\u1ed9ng s\u1ebd t\u1ea1o ra nh\u1eefng \u1ee9ng d\u1ee5ng r\u1ea5t c\u00f3 \u00edch trong vi\u1ec7c x\u1eed l\u00fd d\u1eef li\u1ec7u nh\u01b0 t\u00e1ch d\u1eef li\u1ec7u, s\u1eafp x\u1ebfp d\u1eef li\u1ec7u, ch\u1ecdn <a href=\"http:\/\/blog.ezworkapp.com\/cach-loc-du-lieu-trong-excel.html\" target=\"_blank\" rel=\"noopener\">l\u1ecdc d\u1eef li\u1ec7u trong Excel<\/a>.<\/p>\n<p>Trong b\u00e0i vi\u1ebft n\u00e0y, m\u00ecnh xin chia s\u1ebb v\u1edbi c\u00e1c b\u1ea1n m\u1ed9t c\u00e1ch d\u1ec5 hi\u1ec3u XML l\u00e0 g\u00ec, ti\u1ebfp sau \u0111\u00f3, ch\u00fang ta s\u1ebd \u0111i t\u00ecm hi\u1ec3u c\u00e1ch s\u1eed d\u1ee5ng h\u00e0m FILTER XML \u0111\u01a1n gi\u1ea3n qua m\u1ed9t s\u1ed1 v\u00ed d\u1ee5 x\u1eed l\u00fd d\u1eef li\u1ec7u trong Excel 365. B\u00e0i vi\u1ebft n\u00e0y c\u00f3 m\u1ed9t video n\u1ed9i dung k\u00e8m theo, n\u1ebfu b\u1ea1n th\u00edch xem video tr\u01b0\u1edbc khi \u0111\u1ecdc b\u00e0i vi\u1ebft, th\u00ec b\u1ea1n c\u00f3 th\u1ec3 theo d\u00f5i video d\u01b0\u1edbi \u0111\u00e2y:<\/p>\n<iframe loading=\"lazy\" src=\"https:\/\/www.youtube.com\/embed\/ODwMGuTUpjk\" width=\"560\" height=\"314\" allowfullscreen=\"allowfullscreen\"><\/iframe>\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-674d1ad2167aa\" 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-2\"><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/blog.hocexcel.online\/cach-dung-ham-filterxml-de-xu-ly-tach-va-sap-xep-du-lieu.html#XML_la_gi\" title=\"XML l\u00e0 g\u00ec\">XML l\u00e0 g\u00ec<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/blog.hocexcel.online\/cach-dung-ham-filterxml-de-xu-ly-tach-va-sap-xep-du-lieu.html#Cach_dung_ham_FILTERXML_de_xu_ly_du_lieu\" title=\"C\u00e1ch d\u00f9ng h\u00e0m FILTERXML \u0111\u1ec3 x\u1eed l\u00fd d\u1eef li\u1ec7u\">C\u00e1ch d\u00f9ng h\u00e0m FILTERXML \u0111\u1ec3 x\u1eed l\u00fd d\u1eef li\u1ec7u<\/a>\n<ul class=\"ez-toc-list-level-3\">\n<li class=\"ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/blog.hocexcel.online\/cach-dung-ham-filterxml-de-xu-ly-tach-va-sap-xep-du-lieu.html#Cu_phap_cua_ham_FILTERXML\" title=\"C\u00fa ph\u00e1p c\u1ee7a h\u00e0m FILTERXML\">C\u00fa ph\u00e1p c\u1ee7a h\u00e0m FILTERXML<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/blog.hocexcel.online\/cach-dung-ham-filterxml-de-xu-ly-tach-va-sap-xep-du-lieu.html#Cach_su_dung_ham_FILTERXML_de_tach_du_lieu_thanh_nhieu_cot\" title=\"C\u00e1ch s\u1eed d\u1ee5ng h\u00e0m FILTERXML \u0111\u1ec3 t\u00e1ch d\u1eef li\u1ec7u th\u00e0nh nhi\u1ec1u c\u1ed9t\">C\u00e1ch s\u1eed d\u1ee5ng h\u00e0m FILTERXML \u0111\u1ec3 t\u00e1ch d\u1eef li\u1ec7u th\u00e0nh nhi\u1ec1u c\u1ed9t<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/blog.hocexcel.online\/cach-dung-ham-filterxml-de-xu-ly-tach-va-sap-xep-du-lieu.html#Cach_su_dung_ham_FILTERXML_de_sap_xep_du_lieu_trong_mot_o\" title=\"C\u00e1ch s\u1eed d\u1ee5ng h\u00e0m FILTERXML \u0111\u1ec3 s\u1eafp x\u1ebfp d\u1eef li\u1ec7u trong m\u1ed9t \u00f4\">C\u00e1ch s\u1eed d\u1ee5ng h\u00e0m FILTERXML \u0111\u1ec3 s\u1eafp x\u1ebfp d\u1eef li\u1ec7u trong m\u1ed9t \u00f4<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/blog.hocexcel.online\/cach-dung-ham-filterxml-de-xu-ly-tach-va-sap-xep-du-lieu.html#Doc_so_thanh_chu_khi_ket_hop_voi_ham_Webservice\" title=\"\u0110\u1ecdc s\u1ed1 th\u00e0nh ch\u1eef khi k\u1ebft h\u1ee3p v\u1edbi h\u00e0m Webservice\">\u0110\u1ecdc s\u1ed1 th\u00e0nh ch\u1eef khi k\u1ebft h\u1ee3p v\u1edbi h\u00e0m Webservice<\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2><span class=\"ez-toc-section\" id=\"XML_la_gi\"><\/span>XML l\u00e0 g\u00ec<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>\u0110\u1ec3 tr\u1ea3 l\u1eddi cho c\u00e2u h\u1ecfi XML l\u00e0 g\u00ec, kh\u00f4ng c\u00f3 c\u00e1ch g\u00ec d\u1ec5 h\u01a1n l\u00e0 ch\u00fang ta s\u1ebd \u0111i v\u00e0o m\u1ed9t v\u00ed d\u1ee5:<\/p>\n<pre><code class=\"language-xml\">&lt;parent&gt;\r\n    &lt;item&gt;id&lt;\/item&gt;\r\n    &lt;item&gt;first_name&lt;\/item&gt;\r\n    &lt;item&gt;last_name&lt;\/item&gt;\r\n    &lt;item&gt;gender&lt;\/item&gt;\r\n    &lt;item&gt;email&lt;\/item&gt;\r\n&lt;\/parent&gt;<\/code><\/pre>\n<p>Trong \u0111o\u1ea1n XML b\u00ean tr\u00ean, m\u00ecnh \u0111\u00e3 ch\u1ee7 \u0111\u1ed9ng xu\u1ed1ng d\u00f2ng v\u00e0 l\u00f9i v\u00e0o \u0111\u1ea7u d\u00f2ng \u1edf n\u01a1i c\u1ea7n thi\u1ebft \u0111\u1ec3 b\u1ea1n c\u00f3 th\u1ec3 d\u1ec5 d\u00e0ng nh\u1eadn ra \u0111\u01b0\u1ee3c c\u1ea5u tr\u00fac c\u1ee7a \u0111o\u1ea1n XML n\u00e0y. Trong \u0111o\u1ea1n XML ph\u00eda tr\u00ean:<\/p>\n<ul>\n<li><code>&lt;parent&gt;<\/code>, <code>&lt;item&gt;<\/code>, <code>&lt;\/parent&gt;<\/code>, <code>&lt;\/item&gt;<\/code> \u0111\u01b0\u1ee3c g\u1ecdi l\u00e0 c\u00e1c th\u1ebb tag<\/li>\n<li><code>&lt;parent&gt;<\/code>, <code>&lt;item&gt;<\/code> l\u00e0 c\u00e1c th\u1ebb m\u1edf (Ti\u1ebfng Anh: opening tag)<\/li>\n<li><code>&lt;\/parent&gt;<\/code>, <code>&lt;\/item&gt;<\/code> l\u00e0 c\u00e1c th\u1ebb \u0111\u00f3ng (Ti\u1ebfng Anh: closing tag)<\/li>\n<li>C\u00e1c gi\u00e1 tr\u1ecb n\u1eb1m gi\u1eefa th\u1ebb m\u1edf v\u00e0 th\u1ebb \u0111\u00f3ng t\u01b0\u01a1ng \u1ee9ng nh\u01b0 <code>id<\/code>, <code>first_name<\/code>, <code>last_name<\/code>, <code>gender<\/code>, <code>email<\/code> \u0111\u01b0\u1ee3c g\u1ecdi l\u00e0 gi\u00e1 tr\u1ecb<\/li>\n<li>C\u00e1c t\u00ean th\u1ebb nh\u01b0 <code>parent<\/code>, hay <code>item<\/code> l\u00e0 do b\u1ea1n t\u1ef1 \u0111\u1eb7t, mi\u1ec5n l\u00e0 c\u00e1c th\u1ebb \u1edf \u0111\u00fang v\u1ecb tr\u00ed, \u0111\u00fang c\u1ea5u tr\u00fac l\u00e0 \u0111\u01b0\u1ee3c<\/li>\n<\/ul>\n<p>Sau khi \u0111\u00e3 hi\u1ec3u \u0111\u01b0\u1ee3c c\u1ea5u tr\u00fac c\u01a1 b\u1ea3n c\u1ee7a m\u1ed9t \u0111o\u1ea1n XML, ch\u00fang ta h\u00e3y h\u1eb9n nhau \u1edf ph\u1ea7n ti\u1ebfp theo \u0111\u1ec3 ch\u00fang ta c\u00f3 th\u1ec3 t\u1eebng b\u01b0\u1edbc \u00e1p d\u1ee5ng h\u00e0m FILTERXML \u0111\u1ec3 x\u1eed l\u00fd d\u1eef li\u1ec7u.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Cach_dung_ham_FILTERXML_de_xu_ly_du_lieu\"><\/span>C\u00e1ch d\u00f9ng h\u00e0m FILTERXML \u0111\u1ec3 x\u1eed l\u00fd d\u1eef li\u1ec7u<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<h3><span class=\"ez-toc-section\" id=\"Cu_phap_cua_ham_FILTERXML\"><\/span>C\u00fa ph\u00e1p c\u1ee7a h\u00e0m FILTERXML<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<pre><code class=\"language-xls\">=FILTERXML( chu\u1ed7i_XML, chu\u1ed7i_XPATH )<\/code><\/pre>\n<p>Trong \u0111\u00f3:<\/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-26.gif\" title=\"ex101_92019-nho-26\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<ul>\n<li><strong>chu\u1ed7i_XML<\/strong> l\u00e0 tham s\u1ed1 b\u1eaft bu\u1ed9c, l\u00e0 m\u1ed9t chu\u1ed7i d\u1eef li\u1ec7u c\u00f3 \u0111\u1ecbnh d\u1ea1ng nh\u01b0 ch\u00fang ta n\u00f3i t\u1edbi \u1edf ph\u1ea7n tr\u00ean<\/li>\n<li><strong>chu\u1ed7i_XPATH<\/strong> l\u00e0 tham s\u1ed1 b\u1eaft bu\u1ed9c, l\u00e0 m\u1ed9t chu\u1ed7i v\u1edbi c\u00fa ph\u00e1p \u0111\u1eb7c bi\u1ec7t \u0111\u1ec3 h\u00e0m FILTERXML c\u00f3 th\u1ec3 hi\u1ec3u v\u00e0 tr\u1ea3 v\u1ec1 cho ch\u00fang ta d\u1eef li\u1ec7u c\u1ea7n t\u00ecm ki\u1ebfm \u1edf trong chu\u1ed7i_XML<\/li>\n<\/ul>\n<p><strong>Ch\u00fa \u00fd:<\/strong><\/p>\n<ul>\n<li>V\u00ec h\u00e0m FILTERXML s\u1eed d\u1ee5ng ch\u1ee9c n\u0103ng \u0111\u1eb7c tr\u01b0ng c\u1ee7a h\u1ec7 \u0111i\u1ec1u h\u00e0nh Windows, n\u00ean h\u00e0m n\u00e0y s\u1ebd kh\u00f4ng ho\u1ea1t \u0111\u1ed9ng tr\u00ean phi\u00ean b\u1ea3n Excel Web v\u00e0 phi\u00ean b\u1ea3n Excel tr\u00ean m\u00e1y MacOS<\/li>\n<li>H\u00e0m FILTERXML c\u00f3 m\u1eb7t tr\u00ean Excel phi\u00ean b\u1ea3n 2013 tr\u1edf l\u00ean<\/li>\n<li>N\u1ebfu chu\u1ed7i_XML c\u00f3 \u0111\u1ecbnh d\u1ea1ng kh\u00f4ng h\u1ee3p l\u1ec7, th\u00ec k\u1ebft qu\u1ea3 c\u1ee7a h\u00e0m FILTERXML s\u1ebd l\u00e0 #VALUE!<\/li>\n<\/ul>\n<h3><span class=\"ez-toc-section\" id=\"Cach_su_dung_ham_FILTERXML_de_tach_du_lieu_thanh_nhieu_cot\"><\/span>C\u00e1ch s\u1eed d\u1ee5ng h\u00e0m FILTERXML \u0111\u1ec3 t\u00e1ch d\u1eef li\u1ec7u th\u00e0nh nhi\u1ec1u c\u1ed9t<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>V\u1edbi nh\u1eefng ki\u1ebfn th\u1ee9c c\u01a1 b\u1ea3n v\u1ec1 h\u00e0m FILTERXML \u1edf tr\u00ean, th\u00ec ch\u00fang ta s\u1ebd b\u1eaft \u0111\u1ea7u v\u1edbi m\u1ed9t v\u00ed d\u1ee5 \u0111\u1ea7u ti\u00ean \u0111\u1ec3 \u1ee9ng d\u1ee5ng h\u00e0m FILTERXML trong vi\u1ec7c t\u00e1ch d\u1eef li\u1ec7u b\u1ecb ng\u0103n c\u00e1ch b\u1edfi d\u1ea5u ph\u1ea9y (ho\u1eb7c d\u1ea5u b\u1ea5t k\u1ef3) th\u00e0nh nhi\u1ec1u c\u1ed9t trong Excel.<\/p>\n<p>Gi\u1ea3 s\u1eed b\u1ea1n c\u00f3 d\u1eef li\u1ec7u trong c\u1ed9t A nh\u01b0 sau, v\u00e0 b\u1ea1n mu\u1ed1n t\u00e1ch d\u1eef li\u1ec7u \u0111\u00f3 ra th\u00e0nh nhi\u1ec1u c\u1ed9t nh\u01b0 b\u00ean tay ph\u1ea3i c\u1ee7a h\u00ecnh minh h\u1ecda:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-36016\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/cach-su-dung-ham-filterxml-trong-excel-01-1024x318-1.png\" alt=\"V\u00ed d\u1ee5 \u1ee9ng d\u1ee5ng h\u00e0m FILTERXML \u0111\u1ec3 t\u00e1ch d\u1eef li\u1ec7u ra nhi\u1ec1u c\u1ed9t trong Excel\" width=\"1024\" height=\"318\" title=\"cach-su-dung-ham-filterxml-trong-excel-01-1024x318-1\"><\/p>\n<p>V\u1edbi v\u1ea5n \u0111\u1ec1 n\u00e0y, b\u1ea1n c\u00f3 th\u1ec3 ho\u00e0n to\u00e0n s\u1eed d\u1ee5ng <a href=\"http:\/\/blog.ezworkapp.com\/chuyen-doi-tu-text-sang-date-voi-text-columns-fixed-width.html\" target=\"_blank\" rel=\"noopener\">ch\u1ee9c n\u0103ng Text To Columns \u1edf trong Excel<\/a> \u0111\u1ec3 gi\u1ea3i quy\u1ebft h\u1ebft s\u1ee9c nhanh ch\u00f3ng. Tuy nhi\u00ean, ch\u00fang ta s\u1ebd s\u1eed d\u1ee5ng h\u00e0m FILTERXML trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y \u0111\u1ec3 c\u00f3 \u0111\u01b0\u1ee3c s\u1ef1 linh ho\u1ea1t c\u1ee7a h\u00e0m.<\/p>\n<p>\u0110\u1ea7u ti\u00ean, \u0111\u1ec3 chuy\u1ec3n \u0111\u01b0\u1ee3c d\u1eef li\u1ec7u \u0111ang b\u1ecb ng\u0103n c\u00e1ch b\u1edfi c\u00e1c d\u1ea5u \u201c,\u201d sang d\u1ea1ng XML, th\u00ec ch\u00fang ta s\u1ebd s\u1eed d\u1ee5ng <a href=\"http:\/\/blog.ezworkapp.com\/substitute-va-40-ten-cuop.html\" target=\"_blank\" rel=\"noopener\">h\u00e0m SUBSTITUTE<\/a> nh\u01b0 sau trong \u00f4 C1:<\/p>\n<pre><code class=\"language-xls\">=SUBSTITUTE(A1,\",\",\"&lt;\/i&gt;&lt;i&gt;\")<\/code><\/pre>\n<p>k\u1ebft qu\u1ea3 c\u1ee7a h\u00e0m n\u00e0y l\u00e0:<\/p>\n<p class=\"p1\"><code>\"id&lt;\/i&gt;&lt;i&gt;first_name&lt;\/i&gt;&lt;i&gt;last_name&lt;\/i&gt;&lt;i&gt;email&lt;\/i&gt;&lt;i&gt;gender\"<\/code><\/p>\n<p>Nh\u01b0 v\u1eady, k\u1ebft qu\u1ea3 c\u1ee7a ch\u00fang ta c\u00f2n thi\u1ebfu <code>&lt;p&gt;&lt;i&gt;<\/code> \u1edf \u0111\u1ea7u chu\u1ed7i v\u00e0 thi\u1ebfu <code>&lt;\/i&gt;&lt;\/p&gt;<\/code> \u1edf cu\u1ed1i chu\u1ed7i \u0111\u1ec3 c\u00f3 \u0111\u01b0\u1ee3c m\u1ed9t chu\u1ed7i XML h\u1ee3p l\u1ec7. \u0110i\u1ec1u n\u00e0y c\u00f3 th\u1ec3 \u0111\u01b0\u1ee3c gi\u1ea3i quy\u1ebft \u0111\u01a1n gi\u1ea3n b\u1eb1ng c\u00e1ch gh\u00e9p n\u1ed1i c\u00e1c chu\u1ed7i n\u00e0y l\u1ea7n l\u01b0\u1ee3t v\u00e0o v\u1ecb tr\u00ed \u0111\u1ea7u v\u00e0 cu\u1ed1i c\u1ee7a k\u1ebft qu\u1ea3 h\u00e0m Substitute<\/p>\n<pre><code class=\"language-xls\">=\"&lt;p&gt;&lt;i&gt;\" &amp; SUBSTITUTE(A1,\",\",\"&lt;\/i&gt;&lt;i&gt;\") &amp; \"&lt;\/i&gt;&lt;\/p&gt;\"<\/code><\/pre>\n<p>Sau \u0111\u00f3, ch\u00fang ta s\u1ebd c\u00f3 m\u1ed9t chu\u1ed7i XML h\u1ee3p l\u1ec7:<\/p>\n<pre><code class=\"language-xls\">\"&lt;p&gt;&lt;i&gt;id&lt;\/i&gt;&lt;i&gt;first_name&lt;\/i&gt;&lt;i&gt;last_name&lt;\/i&gt;&lt;i&gt;email&lt;\/i&gt;&lt;i&gt;gender&lt;\/i&gt;&lt;\/p&gt;\"<\/code><\/pre>\n<p>V\u1edbi chu\u1ed7i XML h\u1ee3p l\u1ec7 n\u00e0y, ch\u00fang ta c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng chu\u1ed7i_XPATH l\u00e0 <code>\"\/\/i\"<\/code> \u0111\u1ec3 c\u00f3 th\u1ec3 l\u1ea5y ra gi\u00e1 tr\u1ecb gi\u1eefa nh\u1eefng th\u1ebb tag <code>&lt;i&gt;<\/code> v\u00e0 <code>&lt;\/i&gt;<\/code><\/p>\n<p>(N\u1ebfu b\u1ea1n \u0111\u1eb7t th\u1ebb tag l\u00e0 <code>&lt;item&gt;<\/code> th\u00ec chu\u1ed7i_XPATH trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y b\u1ea1n c\u1ea7n s\u1eed d\u1ee5ng l\u00e0 <code>\"\/\/item\"<\/code>)<\/p>\n<pre><code class=\"language-xls\">=FILTERXML(\"&lt;p&gt;&lt;i&gt;\" &amp; SUBSTITUTE(A1,\",\",\"&lt;\/i&gt;&lt;i&gt;\") &amp; \"&lt;\/i&gt;&lt;\/p&gt;\",\"\/\/i\")<\/code><\/pre>\n<p>K\u1ebft qu\u1ea3 c\u1ee7a h\u00e0m FILTERXML n\u00e0y l\u00e0:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-36017\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/cach-su-dung-ham-filterxml-trong-excel-02.png\" alt=\"K\u1ebft qu\u1ea3 khi s\u1eed d\u1ee5ng h\u00e0m FILTERXML\" width=\"125\" height=\"181\" title=\"cach-su-dung-ham-filterxml-trong-excel-02\"><\/p>\n<p>V\u00ec m\u00ecnh s\u1eed d\u1ee5ng Excel 365, n\u00ean k\u1ebft qu\u1ea3 c\u1ee7a h\u00e0m n\u00e0y s\u1ebd tr\u00e0n ra c\u00e1c \u00f4 kh\u00e1c \u0111\u00fang nh\u01b0 c\u00e1ch ho\u1ea1t \u0111\u1ed9ng c\u1ee7a <a href=\"http:\/\/blog.ezworkapp.com\/ham-mang-dong-trong-excel-dynamic-array-formulas-phan-1-ham-co-kha-nang-mo-rong-vung-du-lieu.html\" target=\"_blank\" rel=\"noopener\">c\u00e1c h\u00e0m m\u1ea3ng \u0111\u1ed9ng trong Excel<\/a><\/p>\n<p>\u0110\u1ec3 chuy\u1ec3n k\u1ebft qu\u1ea3 n\u00e0y t\u1eeb d\u1ea1ng c\u1ed9t d\u1ecdc th\u00e0nh h\u00e0ng ngang, ch\u00fang ta s\u1ebd l\u1ed3ng gh\u00e9p th\u00eam <a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-su-dung-ham-transpose-chuyen-doi-hang-thanh-cot-cot-thanh-hang-trong-excel.html\" target=\"_blank\" rel=\"noopener\">h\u00e0m Transpose<\/a> v\u1edbi h\u00e0m FILTERXML nh\u01b0 sau:<\/p>\n<pre><code class=\"language-xls\">=TRANSPOSE(FILTERXML(\"&lt;p&gt;&lt;i&gt;\" &amp; SUBSTITUTE(A1,\",\",\"&lt;\/i&gt;&lt;i&gt;\") &amp; \"&lt;\/i&gt;&lt;\/p&gt;\",\"\/\/i\"))<\/code><\/pre>\n<p>Sau \u0111\u00f3, ch\u00fang ta c\u00f3 th\u1ec3 k\u00e9o c\u00f4ng th\u1ee9c n\u00e0y xu\u1ed1ng ph\u00eda d\u01b0\u1edbi cho c\u00e1c d\u00f2ng c\u00f2n l\u1ea1i \u0111\u1ec3 nh\u1eadn \u0111\u01b0\u1ee3c k\u1ebft qu\u1ea3 t\u00e1ch d\u1eef li\u1ec7u b\u1ecb ng\u0103n c\u00e1ch b\u1edfi d\u1ea5u ph\u1ea9y th\u00e0nh nhi\u1ec1u c\u1ed9t.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-36016\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/cach-su-dung-ham-filterxml-trong-excel-01-1024x318-1.png\" alt=\"V\u00ed d\u1ee5 \u1ee9ng d\u1ee5ng h\u00e0m FILTERXML \u0111\u1ec3 t\u00e1ch d\u1eef li\u1ec7u ra nhi\u1ec1u c\u1ed9t trong Excel\" width=\"1024\" height=\"318\" title=\"cach-su-dung-ham-filterxml-trong-excel-01-1024x318-1\"><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Cach_su_dung_ham_FILTERXML_de_sap_xep_du_lieu_trong_mot_o\"><\/span>C\u00e1ch s\u1eed d\u1ee5ng h\u00e0m FILTERXML \u0111\u1ec3 s\u1eafp x\u1ebfp d\u1eef li\u1ec7u trong m\u1ed9t \u00f4<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Trong m\u1ed9t s\u1ed1 tr\u01b0\u1eddng h\u1ee3p \u0111\u1eb7c bi\u1ec7t, d\u1eef li\u1ec7u \u0111\u01b0\u1ee3c xu\u1ea5t ra t\u1eeb h\u1ec7 th\u1ed1ng hay b\u1ea1n nh\u1eadn \u0111\u01b0\u1ee3c d\u1eef li\u1ec7u t\u1eeb m\u1ed9t \u0111\u1ed3ng nghi\u1ec7p m\u00e0 l\u1ea1i ph\u00e1t sinh nhu c\u1ea7u s\u1eafp x\u1ebfp d\u1eef li\u1ec7u \u0111\u01b0\u1ee3c l\u01b0u trong m\u1ed9t \u00f4 ch\u1ee9 kh\u00f4ng s\u1eafp x\u1ebfp d\u1eef li\u1ec7u \u0111\u01b0\u1ee3c l\u01b0u trong m\u1ed9t c\u1ed9t, th\u00ec \u0111\u00e2y ch\u00ednh x\u00e1c l\u00e0 k\u1ef9 thu\u1eadt b\u1ea1n c\u1ea7n nh\u1edb t\u1edbi v\u00e0 n\u1eafm v\u1eefng. \u0110\u1ec3 c\u00f3 th\u1ec3 s\u1eafp x\u1ebfp \u0111\u01b0\u1ee3c d\u1eef li\u1ec7u v\u00e0 c\u00f3 k\u1ebft qu\u1ea3 nh\u01b0 trong c\u1ed9t C \u1edf trong h\u00ecnh minh h\u1ecda:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-36018\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/cach-su-dung-ham-filterxml-trong-excel-03-1024x266-1.png\" alt=\"S\u1eed d\u1ee5ng h\u00e0m FILTERXML \u0111\u1ec3 s\u1eafp x\u1ebfp d\u1eef li\u1ec7u trong m\u1ed9t \u00f4\" width=\"1024\" height=\"266\" title=\"cach-su-dung-ham-filterxml-trong-excel-03-1024x266-1\"><\/p>\n<p>\u0110\u1ec3 gi\u1ea3i quy\u1ebft v\u1ea5n \u0111\u1ec1 n\u00e0y, ch\u00fang ta l\u1ea1i b\u1eaft \u0111\u1ea7u t\u1eeb h\u00e0m FILTERXML nh\u01b0 sau trong \u00f4 C1:<\/p>\n<pre><code class=\"language-xls\">=FILTERXML(\"&lt;p&gt;&lt;i&gt;\" &amp; SUBSTITUTE(A1,\",\",\"&lt;\/i&gt;&lt;i&gt;\") &amp; \"&lt;\/i&gt;&lt;\/p&gt;\",\"\/\/i\")<\/code><\/pre>\n<p>V\u1edbi c\u00f4ng th\u1ee9c n\u00e0y, ch\u00fang ta s\u1ebd \u0111\u1ea1t \u0111\u01b0\u1ee3c k\u1ebft qu\u1ea3 nh\u01b0 \u1edf ph\u1ea7n tr\u01b0\u1edbc nh\u01b0 sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-36017\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/cach-su-dung-ham-filterxml-trong-excel-02.png\" alt=\"K\u1ebft qu\u1ea3 khi s\u1eed d\u1ee5ng h\u00e0m FILTERXML\" width=\"132\" height=\"191\" title=\"cach-su-dung-ham-filterxml-trong-excel-02\"><\/p>\n<p>Sau \u0111\u00f3, \u0111\u1ec3 s\u1eafp x\u1ebfp, th\u00ec ch\u00fang ta s\u1ebd l\u1ed3ng gh\u00e9p <a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-cach-tu-dong-sap-xep-du-lieu-bang-ham-sort.html\" target=\"_blank\" rel=\"noopener\">h\u00e0m SORT<\/a> ra ngo\u00e0i h\u00e0m FILTERXML nh\u01b0 sau<\/p>\n<pre><code class=\"language-xls\">=SORT(FILTERXML(\"&lt;p&gt;&lt;i&gt;\" &amp; SUBSTITUTE(A1,\",\",\"&lt;\/i&gt;&lt;i&gt;\") &amp; \"&lt;\/i&gt;&lt;\/p&gt;\",\"\/\/i\"))<\/code><\/pre>\n<p>K\u1ebft qu\u1ea3 c\u1ee7a vi\u1ec7c n\u00e0y l\u00e0 c\u1ed9t d\u1eef li\u1ec7u s\u1ebd \u0111\u01b0\u1ee3c s\u1eafp x\u1ebfp l\u1ea1i:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-36019\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/cach-su-dung-ham-filterxml-trong-excel-04.png\" alt=\"C\u00e1ch s\u1eed d\u1ee5ng h\u00e0m FILTERXML \u0111\u1ec3 s\u1eafp x\u1ebfp d\u1eef li\u1ec7u trong \u00f4\" width=\"125\" height=\"162\" title=\"cach-su-dung-ham-filterxml-trong-excel-04\"><\/p>\n<p>Sau \u0111\u00f3, ch\u00fang ta s\u1ebd \u00e1p d\u1ee5ng h\u00e0m TEXTJOIN \u0111\u1ec3 c\u00f3 th\u1ec3 n\u1ed1i l\u1ea1i c\u1ed9t d\u1eef li\u1ec7u n\u00e0y sau khi \u0111\u00e3 s\u1eafp x\u1ebfp:<\/p>\n<pre><code class=\"language-xls\">=TEXTJOIN(\",\",FALSE,SORT(FILTERXML(\"&lt;p&gt;&lt;i&gt;\" &amp; SUBSTITUTE(A1,\",\",\"&lt;\/i&gt;&lt;i&gt;\") &amp; \"&lt;\/i&gt;&lt;\/p&gt;\",\"\/\/i\")))<\/code><\/pre>\n<p>V\u00e0 k\u1ebft qu\u1ea3 c\u00f3 th\u1ec3 \u00e1p d\u1ee5ng \u0111\u01b0\u1ee3c cho c\u1ea3 nh\u1eefng d\u00f2ng sau \u0111\u00f3:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-large wp-image-36018\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/cach-su-dung-ham-filterxml-trong-excel-03-1024x266-1.png\" alt=\"S\u1eed d\u1ee5ng h\u00e0m FILTERXML \u0111\u1ec3 s\u1eafp x\u1ebfp d\u1eef li\u1ec7u trong m\u1ed9t \u00f4\" width=\"1024\" height=\"266\" title=\"cach-su-dung-ham-filterxml-trong-excel-03-1024x266-1\"><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Doc_so_thanh_chu_khi_ket_hop_voi_ham_Webservice\"><\/span>\u0110\u1ecdc s\u1ed1 th\u00e0nh ch\u1eef khi k\u1ebft h\u1ee3p v\u1edbi h\u00e0m Webservice<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Trong m\u1ed9t s\u1ed1 tr\u01b0\u1eddng h\u1ee3p, khi b\u1ea1n s\u1eed d\u1ee5ng d\u1ecbch v\u1ee5, API <a href=\"http:\/\/blog.ezworkapp.com\/cach-doc-so-thanh-chu-don-gian-nhat-trong-excel-va-google-sheets.html\" target=\"_blank\" rel=\"noopener\">\u0111\u1ecdc s\u1ed1 th\u00e0nh ch\u1eef<\/a> m\u00e0 nh\u1eefng API n\u00e0y tr\u1ea3 v\u1ec1 d\u1eef li\u1ec7u l\u00e0 XML, th\u00ec b\u1ea1n ho\u00e0n to\u00e0n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng k\u1ebft h\u1ee3p h\u00e0m Webservice v\u00e0 h\u00e0m FILTERXML \u0111\u1ec3 c\u00f3 th\u1ec3 \u0111\u1ecdc ra k\u1ebft qu\u1ea3 1 c\u00e1ch nhanh ch\u00f3ng. \u0110i\u1ec1u n\u00e0y kh\u00f4ng ch\u1ec9 \u00e1p d\u1ee5ng cho vi\u1ec7c \u0111\u1ecdc s\u1ed1 th\u00e0nh ch\u1eef, m\u00e0 c\u00f2n c\u00f3 th\u1ec3 \u00e1p d\u1ee5ng cho b\u1ea5t c\u1ee9 ngu\u1ed3n d\u1eef li\u1ec7u n\u00e0o tr\u1ea3 k\u1ebft qu\u1ea3 v\u1ec1 l\u00e0 XML nh\u01b0 m\u1ed9t s\u1ed1 s\u00e0n ch\u1ee9ng kho\u00e1n, m\u1ed9t s\u1ed1 trang Web d\u1eef li\u1ec7u v\u1ec1 th\u1eddi ti\u1ebft.<\/p>\n<p>Nh\u01b0 v\u1eady, qua b\u00e0i n\u00e0y, Thanh \u0111\u00e3 h\u01b0\u1edbng d\u1eabn c\u00e1ch b\u1ea1n m\u1ed9t s\u1ed1 v\u00ed d\u1ee5 \u1ee9ng d\u1ee5ng h\u00e0m FILTERXML \u0111\u1ec3 x\u1eed l\u00fd d\u1eef li\u1ec7u c\u0169ng nh\u01b0 khai th\u00e1c d\u1eef li\u1ec7u trong Excel. B\u1ea1n c\u00f3 th\u1ec3 quan t\u00e2m t\u1edbi kh\u00f3a h\u1ecdc Excel tuy\u1ec7t v\u1eddi c\u1ee7a Thanh: <a href=\"https:\/\/hocexcel.online\/course\/preview\/ex101-excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\" target=\"_blank\" rel=\"noopener\">EX101 \u2013 Excel t\u1eeb c\u01a1 b\u1ea3n \u0111\u1ebfn chuy\u00ean gia d\u00e0nh cho ng\u01b0\u1eddi \u0111i l\u00e0m<\/a><\/p>\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-26.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":551,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-550","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-nang-cao"],"_links":{"self":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/550","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=550"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/550\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/551"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=550"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=550"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=550"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}