{"id":6259,"date":"2024-12-21T14:16:26","date_gmt":"2024-12-21T14:16:26","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/21\/huong-dan-ham-textjoin-ghep-chuoi-thoa-man-dieu-kien-trong-excel\/"},"modified":"2024-12-21T14:16:26","modified_gmt":"2024-12-21T14:16:26","slug":"huong-dan-ham-textjoin-ghep-chuoi-thoa-man-dieu-kien-trong-excel","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/21\/huong-dan-ham-textjoin-ghep-chuoi-thoa-man-dieu-kien-trong-excel\/","title":{"rendered":"H\u01b0\u1edbng d\u1eabn h\u00e0m TEXTJOIN gh\u00e9p chu\u1ed7i th\u1ecfa m\u00e3n \u0111i\u1ec1u ki\u1ec7n trong Excel"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p>Ta \u0111\u1ec1u \u0111\u00e3 bi\u1ebft t\u1edbi k\u0129 thu\u1eadt <a href=\"http:\/\/blog.ezworkapp.com\/substitute-va-40-ten-cuop.html\"><strong>t\u00e1ch chu\u1ed7i, t\u00e1ch \u0111\u1ecba ch\u1ec9, h\u1ecd t\u00ean\u2026\u00a0s\u1eed d\u1ee5ng SUBSTITUTE<\/strong><\/a>. Ng\u01b0\u1ee3c l\u1ea1i, ta c\u0169ng c\u00f3 k\u0129 thu\u1eadt gh\u00e9p chu\u1ed7i b\u1eb1ng c\u00e1c h\u00e0m nh\u01b0\u00a0<strong>CONCAT<\/strong>,\u00a0<strong>TEXTJOIN<\/strong> hay c\u00e1c h\u00e0m\u00a0<strong>UDF (User-defined function)<\/strong>. Trong b\u00e0i vi\u1ebft n\u00e0y, ta s\u1ebd \u0111i v\u00e0o c\u1ee5 th\u1ec3 m\u1ed9t v\u00ed d\u1ee5:\u00a0<strong>h\u00e0m TEXTJOIN gh\u00e9p chu\u1ed7i th\u1ecfa m\u00e3n \u0111i\u1ec1u ki\u1ec7n<\/strong>. \u0110\u00e2y l\u00e0 h\u00e0m ch\u1ec9 c\u00f3 trong phi\u00ean b\u1ea3n Office 365 v\u00e0 2019, b\u1edfi v\u1eady v\u1edbi c\u00e1c phi\u00ean b\u1ea3n c\u0169 h\u01a1n, c\u00e1c b\u1ea1n c\u00f3 th\u1ec3 tham kh\u1ea3o c\u00e1c h\u00e0m kh\u00e1c trong link cu\u1ed1i b\u00e0i vi\u1ebft.<\/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-6766cdb933e9a\" 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-3\"><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/blog.hocexcel.online\/ham-textjoin-ghep-chuoi-thoa-man-dieu-kien.html#Ham_TEXTJOIN_ghep_chuoi_thoa_man_dieu_kien_trong_Excel\" title=\"H\u00e0m TEXTJOIN gh\u00e9p chu\u1ed7i th\u1ecfa m\u00e3n \u0111i\u1ec1u ki\u1ec7n trong Excel\">H\u00e0m TEXTJOIN gh\u00e9p chu\u1ed7i th\u1ecfa m\u00e3n \u0111i\u1ec1u ki\u1ec7n trong Excel<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/blog.hocexcel.online\/ham-textjoin-ghep-chuoi-thoa-man-dieu-kien.html#Giai_thich\" title=\"Gi\u1ea3i th\u00edch\">Gi\u1ea3i th\u00edch<\/a><\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h3><span class=\"ez-toc-section\" id=\"Ham_TEXTJOIN_ghep_chuoi_thoa_man_dieu_kien_trong_Excel\"><\/span>H\u00e0m TEXTJOIN gh\u00e9p chu\u1ed7i th\u1ecfa m\u00e3n \u0111i\u1ec1u ki\u1ec7n trong Excel<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Ta c\u00f3 v\u00ed d\u1ee5 sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-29169\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/ham-textjoin-1.png\" width=\"987\" height=\"342\" title=\"ham-textjoin-1\"><\/p>\n<p><strong>Y\u00eau c\u1ea7u:<\/strong> Li\u1ec7t k\u00ea c\u00e1c th\u00e0nh ph\u1ed1 theo t\u1eebng qu\u1ed1c gia, ph\u00e2n c\u00e1ch b\u1edfi d\u1ea5u ph\u1ea9y.<\/p>\n<p><strong>Gi\u1ea3i:<\/strong><\/p>\n<p><strong>H\u00e0m TEXTJOIN k\u1ebft h\u1ee3p c\u00f9ng h\u00e0m IF\u00a0<\/strong>s\u1ebd gi\u00fap ta l\u00e0m \u0111i\u1ec1u n\u00e0y. C\u00f4ng th\u1ee9c \u1edf \u0111\u00e2y l\u00e0:<\/p>\n<blockquote>\n<p>\n<strong>{=TEXTJOIN(\u201c, \u201c,TRUE,IF($A$2:$A$16=$E2,$B$2:$B$16,\u201d\u201d))}<\/strong><\/p>\n<\/blockquote>\n<p>Sau \u0111\u00f3 k\u00e9o c\u00f4ng th\u1ee9c cho nh\u1eefng \u00f4 c\u00f2n l\u1ea1i. Ta c\u00f3 k\u1ebft qu\u1ea3:<\/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-563.gif\" title=\"ex101_92019-nho-563\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<figure id=\"attachment_29170\" aria-describedby=\"caption-attachment-29170\" style=\"width: 992px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-29170 size-full\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/ham-textjoin-2.png\" width=\"992\" height=\"389\" title=\"ham-textjoin-2\"><figcaption id=\"caption-attachment-29170\" class=\"wp-caption-text\">Trong Office 365 ta kh\u00f4ng b\u1eaft bu\u1ed9c s\u1eed d\u1ee5ng Ctrl + Shift + Enter \u0111\u1ec3 tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 h\u00e0m m\u1ea3ng<\/figcaption><\/figure>\n<h3><span class=\"ez-toc-section\" id=\"Giai_thich\"><\/span><strong>Gi\u1ea3i th\u00edch<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Ta s\u1ebd c\u00f9ng di\u1ec5n gi\u1ea3i c\u00f4ng th\u1ee9c n\u00e0y nh\u01b0 sau:<\/p>\n<p>H\u00e0m\u00a0<strong>TEXTJOIN<\/strong> bao g\u1ed3m c\u00e1c \u0111\u1ed1i s\u1ed1:<\/p>\n<p>=<em>TEXTJOIN(<strong>delimiter<\/strong>,\u00a0<strong>ignore_text<\/strong>,\u00a0<strong>text1, text2\u2026<\/strong>)<\/em><\/p>\n<p><strong>delimiter:\u00a0<\/strong>k\u00ed t\u1ef1 ph\u00e2n c\u00e1ch c\u00e1c chu\u1ed7i \u1edf d\u1ea1ng TEXT, \u0111\u01b0\u1ee3c ghi trong d\u1ea5u nh\u00e1y k\u00e9p.<\/p>\n<p><strong>ignore_text:<\/strong> gi\u00e1 tr\u1ecb\u00a0<strong>TRUE<\/strong> s\u1ebd b\u1ecf qua nh\u1eefng \u00f4 tr\u1ed1ng,\u00a0<strong>FALSE\u00a0<\/strong>bao g\u1ed3m nh\u1eefng \u00f4 tr\u1ed1ng.<\/p>\n<p><strong>text1, text2:<\/strong> c\u00e1c chu\u1ed7i c\u1ea7n gh\u00e9p b\u1eb1ng TEXTJOIN<\/p>\n<p>\u00a0<\/p>\n<p>Trong \u0111\u1ed1i s\u1ed1 th\u1ee9 3\u00a0<strong>text1<\/strong> ta l\u1ed3ng v\u00e0o h\u00e0m\u00a0<strong>IF<\/strong> v\u1edbi \u00fd ngh\u0129a nh\u01b0 sau:<\/p>\n<p><em>=IF(v\u00f9ng \u0111i\u1ec1u ki\u1ec7n=\u0111i\u1ec1u ki\u1ec7n,v\u00f9ng ch\u1ee9a chu\u1ed7i c\u1ea7n gh\u00e9p,gi\u00e1 tr\u1ecb r\u1ed7ng)<\/em><\/p>\n<p>T\u1eeb \u0111\u00f3 Excel s\u1ebd x\u00e9t t\u1eebng \u00f4 trong v\u00f9ng \u0111i\u1ec1u ki\u1ec7n c\u00f3 th\u1ecfa m\u00e3n \u0111i\u1ec1u ki\u1ec7n kh\u00f4ng v\u00e0 tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb t\u01b0\u01a1ng \u1ee9ng. K\u1ebft qu\u1ea3 t\u1eeb ch\u1ee9c n\u0103ng\u00a0<strong>Evaluate Formula<\/strong> c\u1ee7a ta nh\u01b0 sau:<\/p>\n<figure id=\"attachment_29175\" aria-describedby=\"caption-attachment-29175\" style=\"width: 527px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-29175\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/ham-textjoin-3.png\" width=\"527\" height=\"281\" title=\"ham-textjoin-3\"><figcaption id=\"caption-attachment-29175\" class=\"wp-caption-text\">X\u00e9t t\u1eebng gi\u00e1 tr\u1ecb trong m\u1ea3ng \u0111i\u1ec1u ki\u1ec7n v\u1edbi \u0111i\u1ec1u ki\u1ec7n \u0111\u1ea7u ti\u00ean l\u00e0 Indonesia<\/figcaption><\/figure>\n<p>\u00a0<\/p>\n<figure id=\"attachment_29176\" aria-describedby=\"caption-attachment-29176\" style=\"width: 530px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-29176\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/ham-textjoin-4.png\" width=\"530\" height=\"282\" title=\"ham-textjoin-4\"><figcaption id=\"caption-attachment-29176\" class=\"wp-caption-text\">\u0110\u01b0a v\u1ec1 TRUE\/FALSE<\/figcaption><\/figure>\n<p>\u00a0<\/p>\n<figure id=\"attachment_29177\" aria-describedby=\"caption-attachment-29177\" style=\"width: 529px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-29177\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/ham-textjoin-5.png\" width=\"529\" height=\"281\" title=\"ham-textjoin-5\"><figcaption id=\"caption-attachment-29177\" class=\"wp-caption-text\">Tr\u1ea3 v\u1ec1 m\u1ea3ng c\u00e1c gi\u00e1 tr\u1ecb \u0111\u00fang v\u00e0 sai<\/figcaption><\/figure>\n<p>\u00a0<\/p>\n<figure id=\"attachment_29178\" aria-describedby=\"caption-attachment-29178\" style=\"width: 529px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-29178\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/ham-textjoin-6.png\" width=\"529\" height=\"282\" title=\"ham-textjoin-6\"><figcaption id=\"caption-attachment-29178\" class=\"wp-caption-text\">K\u1ebft qu\u1ea3 cu\u1ed1i<\/figcaption><\/figure>\n<p>\u00a0<\/p>\n<p>Trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y, h\u00e0m IF v\u1eabn tr\u1ea3 v\u1ec1 c\u1ea3 k\u1ebft qu\u1ea3 c\u1ee7a 2 tr\u01b0\u1eddng h\u1ee3p \u0111\u00fang v\u00e0 sai. B\u1edfi v\u1eady, n\u1ebfu ta \u0111\u1eb7t\u00a0<strong>ignore_text<\/strong> b\u1eb1ng\u00a0<strong>FALSE<\/strong>, k\u1ebft qu\u1ea3 ta nh\u1eadn \u0111\u01b0\u1ee3c s\u1ebd bao g\u1ed3m c\u1ea3 nh\u1eefng gi\u00e1 tr\u1ecb r\u1ed7ng nh\u01b0 sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-29179\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/ham-textjoin-7.png\" width=\"769\" height=\"229\" title=\"ham-textjoin-7\"><\/p>\n<hr>\n<p>Xem th\u00eam c\u00e1c b\u00e0i vi\u1ebft t\u01b0\u01a1ng t\u1ef1 sau:<\/p>\n<p><strong><a href=\"http:\/\/blog.ezworkapp.com\/cach-ham-ghep-chuoi-chu-theo-o-vung-hoac-theo-nhieu-dieu-kien-voi-jointext-joinif-joinifs.html\">C\u00e1ch d\u00f9ng h\u00e0m gh\u00e9p chu\u1ed7i, n\u1ed1i chu\u1ed7i theo \u00f4, v\u00f9ng ho\u1eb7c theo \u0111i\u1ec1u ki\u1ec7n<\/a><\/strong><\/p>\n<p><strong><a href=\"http:\/\/blog.ezworkapp.com\/ghep-noi-ky-tu-voi-ham-concat-va-textjoin-trong-excel.html\">Gh\u00e9p n\u1ed1i k\u00ed t\u1ef1 v\u1edbi h\u00e0m CONCAT v\u00e0 TEXTJOIN<\/a><\/strong><\/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-563.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":6260,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-6259","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\/6259","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=6259"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/6259\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/6260"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=6259"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=6259"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=6259"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}