{"id":9581,"date":"2025-01-02T10:20:43","date_gmt":"2025-01-02T10:20:43","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2025\/01\/02\/huong-dan-thuc-hien-phep-tru-trong-excel-tat-tan-tat-nhung-dieu-co-ban-nhat-phan-2\/"},"modified":"2025-01-02T10:20:43","modified_gmt":"2025-01-02T10:20:43","slug":"huong-dan-thuc-hien-phep-tru-trong-excel-tat-tan-tat-nhung-dieu-co-ban-nhat-phan-2","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2025\/01\/02\/huong-dan-thuc-hien-phep-tru-trong-excel-tat-tan-tat-nhung-dieu-co-ban-nhat-phan-2\/","title":{"rendered":"H\u01b0\u1edbng d\u1eabn th\u1ef1c hi\u1ec7n ph\u00e9p tr\u1eeb trong Excel: t\u1ea5t t\u1ea7n t\u1eadt nh\u1eefng \u0111i\u1ec1u c\u01a1 b\u1ea3n nh\u1ea5t (Ph\u1ea7n 2)"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p>Ti\u1ebfp n\u1ed1i b\u00e0i vi\u1ebft v\u1ec1 <a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-thuc-hien-phep-tru-trong-excel-co-ban.html\">th\u1ef1c hi\u1ec7n ph\u00e9p tr\u1eeb trong Excel<\/a>, trong ph\u1ea7n 2 n\u00e0y H\u1ecdc Excel Online s\u1ebd h\u01b0\u1edbng d\u1eabn b\u1ea1n ph\u00e9p tr\u1eeb k\u00ed t\u1ef1.<\/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-6776687a420ad\" 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\/phep-tru-ki-tu-trong-excel-co-ban-phan-2.html#Phep_tru_ki_tu_trong_Excel\" title=\"Ph\u00e9p tr\u1eeb k\u00ed t\u1ef1 trong Excel\">Ph\u00e9p tr\u1eeb k\u00ed t\u1ef1 trong Excel<\/a>\n<ul class=\"ez-toc-list-level-4\">\n<li class=\"ez-toc-heading-level-4\"><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/blog.hocexcel.online\/phep-tru-ki-tu-trong-excel-co-ban-phan-2.html#Phep_tru_su_dung_ham_phan_biet_ki_tu\" title=\"Ph\u00e9p tr\u1eeb s\u1eed d\u1ee5ng h\u00e0m ph\u00e2n bi\u1ec7t k\u00ed t\u1ef1\">Ph\u00e9p tr\u1eeb s\u1eed d\u1ee5ng h\u00e0m ph\u00e2n bi\u1ec7t k\u00ed t\u1ef1<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-4\"><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/blog.hocexcel.online\/phep-tru-ki-tu-trong-excel-co-ban-phan-2.html#Phep_tru_su_dung_ham_khong_biet_ki_tu\" title=\"Ph\u00e9p tr\u1eeb s\u1eed d\u1ee5ng h\u00e0m kh\u00f4ng bi\u1ec7t k\u00ed t\u1ef1\">Ph\u00e9p tr\u1eeb s\u1eed d\u1ee5ng h\u00e0m kh\u00f4ng bi\u1ec7t k\u00ed t\u1ef1<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-4\"><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/blog.hocexcel.online\/phep-tru-ki-tu-trong-excel-co-ban-phan-2.html#Su_dung_COUNTIF_tru_hai_danh_sach_cho_nhau\" title=\"S\u1eed d\u1ee5ng COUNTIF tr\u1eeb hai danh s\u00e1ch cho nhau\">S\u1eed d\u1ee5ng COUNTIF tr\u1eeb hai danh s\u00e1ch cho nhau<\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h3><span class=\"ez-toc-section\" id=\"Phep_tru_ki_tu_trong_Excel\"><\/span>Ph\u00e9p tr\u1eeb k\u00ed t\u1ef1 trong Excel<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<h4><span class=\"ez-toc-section\" id=\"Phep_tru_su_dung_ham_phan_biet_ki_tu\"><\/span>Ph\u00e9p tr\u1eeb s\u1eed d\u1ee5ng h\u00e0m ph\u00e2n bi\u1ec7t k\u00ed t\u1ef1<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>Nh\u1eefng c\u00f4ng th\u1ee9c n\u00e0y s\u1ebd ph\u00e2n bi\u1ec7t r\u00f5 k\u00ed t\u1ef1 hoa, k\u00ed t\u1ef1 th\u01b0\u1eddng trong \u00f4.<\/p>\n<p>\u0110\u1ec3 ti\u1ebfn h\u00e0nh tr\u1eeb m\u1ed9t chu\u1ed7i k\u00ed t\u1ef1, ta s\u1eed d\u1ee5ng h\u00e0m <strong>SUBSTITUTE<\/strong> \u0111\u1ec3 thay th\u1ebf chu\u1ed7i b\u1eb1ng kho\u1ea3ng tr\u1ed1ng, sau \u0111\u00f3 s\u1eed d\u1ee5ng h\u00e0m <strong>TRIM<\/strong> \u0111\u1ec3 lo\u1ea1i b\u1ecf kho\u1ea3ng c\u00e1ch c\u00f2n th\u1eeba. Nh\u01b0 v\u1eady, ta \u0111\u00e3 \u201ctr\u1eeb\u201d r\u1ed3i \u0111\u00f3.<\/p>\n<p>C\u1ee5 th\u1ec3 nh\u01b0 h\u00ecnh minh h\u1ecda sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-23961\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/phep-tru-1.png\" width=\"439\" height=\"106\" title=\"phep-tru-1\"><\/p>\n<p>\u0110\u1ec3 c\u00f4ng th\u1ee9c ho\u1ea1t \u0111\u1ed9ng, h\u00e3y nh\u1edb r\u1eb1ng chu\u1ed7i k\u00ed t\u1ef1 c\u1ea7n ph\u1ea3i ch\u00ednh x\u00e1c!<\/p>\n<h4><span class=\"ez-toc-section\" id=\"Phep_tru_su_dung_ham_khong_biet_ki_tu\"><\/span>Ph\u00e9p tr\u1eeb s\u1eed d\u1ee5ng h\u00e0m kh\u00f4ng bi\u1ec7t k\u00ed t\u1ef1<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>C\u00f4ng th\u1ee9c n\u00e0y c\u0169ng s\u1ebd s\u1eed d\u1ee5ng ph\u01b0\u01a1ng ph\u00e1p nh\u01b0 tr\u00ean: thay th\u1ebf \u0111o\u1ea1n k\u00ed t\u1ef1 mu\u1ed1n tr\u1eeb \u0111i b\u1eb1ng k\u00ed t\u1ef1 r\u1ed7ng. Tuy nhi\u00ean, l\u1ea7n n\u00e0y ta s\u1eed d\u1ee5ng h\u00e0m\u00a0<strong>REPLACE k\u1ebft h\u1ee3p SEARCH v\u00e0 LEN:<\/strong><\/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\/2025\/01\/ex101_92019-nho-68.gif\" title=\"ex101_92019-nho-68\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<ul>\n<li>SEARCH\u00a0tr\u1ea3 v\u1ec1 s\u1ed1 k\u00fd t\u1ef1 t\u1ea1i v\u1ecb tr\u00ed xu\u1ea5t hi\u1ec7n \u0111\u1ea7u ti\u00ean c\u1ee7a k\u00fd t\u1ef1 ho\u1eb7c x\u00e2u v\u0103n b\u1ea3n cho tr\u01b0\u1edbc, t\u00ednh t\u1eeb tr\u00e1i qua ph\u1ea3i v\u00e0 kh\u00f4ng ph\u00e2n bi\u1ec7t ch\u1eef hoa th\u01b0\u1eddng.<\/li>\n<li>REPLACE\u00a0thay th\u1ebf m\u1ed9t ph\u1ea7n c\u1ee7a chu\u1ed7i v\u0103n b\u1ea3n b\u1eb1ng chu\u1ed7i v\u0103n b\u1ea3n kh\u00e1c.<\/li>\n<li>LEN\u00a0tr\u1ea3 v\u1ec1 s\u1ed1 l\u01b0\u1ee3ng k\u00ed t\u1ef1 trong chu\u1ed7i v\u0103n b\u1ea3n.<\/li>\n<\/ul>\n<p>Ta s\u1ebd s\u1eed d\u1ee5ng h\u00e0m SEARCH tr\u1ea3 v\u1ec1 v\u1ecb tr\u00ed c\u1ee7a ch\u1eef c\u00e1i \u0111\u1ea7u ti\u00ean ta mu\u1ed1n tr\u1eeb trong chu\u1ed7i g\u1ed1c, n\u1eb1m trong\u00a0<em>start_num<\/em> c\u1ee7a h\u00e0m REPLACE. Ti\u1ebfp theo, h\u00e0m LEN t\u00ecm s\u1ed1 l\u01b0\u1ee3ng k\u00ed t\u1ef1 mu\u1ed1n x\u00f3a, thu\u1ed9c ph\u1ea7n\u00a0<em>num_chars<\/em> trong REPLACE.<\/p>\n<p>C\u00f4ng th\u1ee9c s\u1ebd nh\u01b0 sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-23962\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/phep-tru-2.png\" width=\"496\" height=\"127\" title=\"phep-tru-2\"><\/p>\n<h2 id=\"Subtract-list-from-another\"><\/h2>\n<h4><span class=\"ez-toc-section\" id=\"Su_dung_COUNTIF_tru_hai_danh_sach_cho_nhau\"><\/span>S\u1eed d\u1ee5ng COUNTIF tr\u1eeb hai danh s\u00e1ch cho nhau<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>Trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y, ta s\u1ebd tr\u1eeb hai danh s\u00e1ch cho nhau b\u1eb1ng\u00a0<strong>COUNTIF.<\/strong><\/p>\n<p>Hay n\u00f3i c\u00e1ch kh\u00e1c, ta s\u1ebd \u0111\u1ebfm nh\u1eefng gi\u00e1 tr\u1ecb \u0111\u1ed9c nh\u1ea5t.<\/p>\n<p>V\u00ed d\u1ee5:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-23963\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/phep-tru-3.png\" width=\"301\" height=\"144\" title=\"phep-tru-3\"><\/p>\n<p>\u00a0<\/p>\n<p>L\u1eddi gi\u1ea3i:<br \/>\nTa s\u1ebd s\u1eed d\u1ee5ng h\u00e0m\u00a0<strong>COUNTIF<\/strong> \u0111\u1ec3 \u0111\u1ebfm l\u1ea7n l\u01b0\u1ee3t s\u1ed1 l\u1ea7n xu\u1ea5t hi\u1ec7n c\u1ee7a t\u1eebng \u00f4 thu\u1ed9c danh s\u00e1ch l\u1edbn h\u01a1n trong danh s\u00e1ch nh\u1ecf h\u01a1n.<\/p>\n<p><em>=COUNITF($B$1:$B$3,A1)<\/em><\/p>\n<p>Sau khi \u0111\u1ebfm, nh\u1eefng gi\u00e1 tr\u1ecb \u0111\u1ed9c nh\u1ea5t c\u00f3 k\u1ebft qu\u1ea3 b\u1eb1ng 0. \u0110\u1ec3 hi\u1ec3n th\u1ecb, ta s\u1eed d\u1ee5ng l\u1ed3ng th\u00eam h\u00e0m\u00a0<strong>IF:<\/strong><\/p>\n<p><em>=IF(COUNTIF($B$1:$B$3,A1)=0,A1,\u201d\u201d)<\/em><\/p>\n<p>V\u00e0 ta c\u00f3 k\u1ebft qu\u1ea3:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-23964\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/phep-tru-4.png\" width=\"494\" height=\"211\" title=\"phep-tru-4\"><\/p>\n<p>V\u1eady l\u00e0 ta \u0111\u00e3 kh\u00e1i qu\u00e1t \u0111\u01b0\u1ee3c c\u00e1c tr\u01b0\u1eddng h\u1ee3p c\u01a1 b\u1ea3n v\u1ec1 ph\u00e9p tr\u1eeb trong Excel r\u1ed3i. Kh\u00f4ng kh\u00f3 ph\u1ea3i kh\u00f4ng n\u00e0o?<\/p>\n<p>Ch\u00fac c\u00e1c b\u1ea1n h\u1ecdc t\u1ed1t.<\/p>\n<p>\u00a0<\/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\/2025\/01\/ex101_92019-nho-68.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":9582,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-9581","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\/9581","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=9581"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/9581\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/9582"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=9581"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=9581"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=9581"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}