{"id":9019,"date":"2024-12-31T12:15:38","date_gmt":"2024-12-31T12:15:38","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/31\/cach-them-dong-thoi-mot-cum-tu-vao-vi-tri-bat-ki-cua-nhieu-o-trong-excel-them-tu-hang-loat\/"},"modified":"2024-12-31T12:15:38","modified_gmt":"2024-12-31T12:15:38","slug":"cach-them-dong-thoi-mot-cum-tu-vao-vi-tri-bat-ki-cua-nhieu-o-trong-excel-them-tu-hang-loat","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/31\/cach-them-dong-thoi-mot-cum-tu-vao-vi-tri-bat-ki-cua-nhieu-o-trong-excel-them-tu-hang-loat\/","title":{"rendered":"C\u00e1ch th\u00eam \u0111\u1ed3ng th\u1eddi m\u1ed9t c\u1ee5m t\u1eeb v\u00e0o v\u1ecb tr\u00ed b\u1ea5t k\u00ec c\u1ee7a nhi\u1ec1u \u00f4 trong Excel (th\u00eam t\u1eeb h\u00e0ng lo\u1ea1t)"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p>L\u00e0m th\u1ebf n\u00e0o \u0111\u1ec3 th\u00eam \u0111\u1ed3ng th\u1eddi m\u1ed9t c\u1ee5m t\u1eeb\u00a0v\u00e0o v\u1ecb tr\u00ed b\u1ea5t k\u00ec c\u1ee7a nhi\u1ec1u\u00a0\u00f4 trong Excel? B\u00e0i vi\u1ebft d\u01b0\u1edbi \u0111\u00e2y s\u1ebd ch\u1ec9 cho b\u1ea1n c\u00e1ch \u0111\u01a1n gi\u1ea3n \u0111\u1ec3 l\u00e0m c\u00f4ng vi\u1ec7c th\u00eam t\u1eeb h\u00e0ng lo\u1ea1t \u0111\u00f3:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-25984\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/them-tu-hang-loat-1.png\" width=\"292\" height=\"258\" title=\"them-tu-hang-loat-1\"><\/p>\n<p>H\u00e3y \u0111\u1ebfn v\u1edbi v\u00ed d\u1ee5:<\/p>\n<p><em>Th\u00eam c\u1ee5m t\u1eeb \u201cL\u1edbp 10A10 \u201d v\u00e0o tr\u01b0\u1edbc t\u00ean h\u1ecdc sinh.<\/em><\/p>\n<p><em>Th\u00eam c\u1ee5m t\u1eeb \u201d THPT Kim Li\u00ean\u201d v\u00e0o sau t\u00ean h\u1ecdc sinh.<\/em><\/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-6773e0696b395\" 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\/them-tu-hang-loat-trong-excel.html#Su_dung_dau_%E2%80%9C_%E2%80%9D_de_them_tu_hang_loat\" title=\"S\u1eed d\u1ee5ng d\u1ea5u \u201c&amp;\u201d \u0111\u1ec3 th\u00eam t\u1eeb h\u00e0ng lo\u1ea1t\">S\u1eed d\u1ee5ng d\u1ea5u \u201c&amp;\u201d \u0111\u1ec3 th\u00eam t\u1eeb h\u00e0ng lo\u1ea1t<\/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\/them-tu-hang-loat-trong-excel.html#Su_dung_cong_thuc_va_vi_du_ve_dien_tu_vao_giua\" title=\"S\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c v\u00e0 v\u00ed d\u1ee5 v\u1ec1 \u0111i\u1ec1n t\u1eeb v\u00e0o gi\u1eefa\">S\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c v\u00e0 v\u00ed d\u1ee5 v\u1ec1 \u0111i\u1ec1n t\u1eeb v\u00e0o gi\u1eefa<\/a><\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h3><span class=\"ez-toc-section\" id=\"Su_dung_dau_%E2%80%9C_%E2%80%9D_de_them_tu_hang_loat\"><\/span>S\u1eed d\u1ee5ng d\u1ea5u \u201c&amp;\u201d \u0111\u1ec3 th\u00eam t\u1eeb h\u00e0ng lo\u1ea1t<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>V\u1edbi c\u00e1ch th\u1ee9 nh\u1ea5t, t\u1ea1i \u00f4 B1 ta g\u00f5 v\u00e0o c\u00f4ng th\u1ee9c sau:<\/p>\n<p><strong>=\u201dL\u1edbp 10A10 \u201d &amp; A1<\/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\/2024\/12\/ex101_92019-nho-849.gif\" title=\"ex101_92019-nho-849\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p>Nh\u1edb l\u00e0 sau c\u1ee5m\u00a0<strong>L\u1edbp 10A10<\/strong> ph\u1ea3i c\u00f3 m\u1ed9t d\u1ea5u c\u00e1ch \u0111\u1ec3 tr\u00ecnh b\u00e0y \u0111\u01b0\u1ee3c \u0111\u1eb9p h\u01a1n nh\u00e9. T\u01b0\u01a1ng t\u1ef1 \u0111\u1ec3 \u0111i\u1ec1n v\u00e0o cu\u1ed1i, ta th\u00eam m\u1ed9t d\u1ea5u\u00a0<strong>&amp;<\/strong> v\u00e0 nh\u1eadp c\u1ee5m \u201d\u00a0<strong>THPT Kim Li\u00ean\u201d<\/strong>, nh\u1edb l\u00e0 c\u00f3 d\u1ea5u c\u00e1ch \u1edf \u0111\u1ea7u \u0111\u1ea5y.<\/p>\n<p>C\u00f4ng th\u1ee9c c\u1ee7a ta l\u00e0:<\/p>\n<p><strong>=\u201dL\u1edbp 10A10 \u201d &amp; A1 &amp; \u201d THPT Kim Li\u00ean\u201d<\/strong><\/p>\n<p>Sau \u0111\u00f3 <strong><a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-su-dung-autofill-trong-excel-2010-2013-2016.html\">s\u1eed d\u1ee5ng Autofill<\/a><\/strong> \u0111\u1ec3 \u0111i\u1ec1n k\u1ebft qu\u1ea3 v\u00e0o nh\u1eefng \u00f4 c\u00f2n l\u1ea1i.<\/p>\n<p>K\u1ebft qu\u1ea3:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-25986\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/them-tu-hang-loat-2.png\" width=\"565\" height=\"307\" title=\"them-tu-hang-loat-2\"><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Su_dung_cong_thuc_va_vi_du_ve_dien_tu_vao_giua\"><\/span>S\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c v\u00e0 v\u00ed d\u1ee5 v\u1ec1 \u0111i\u1ec1n t\u1eeb v\u00e0o gi\u1eefa<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Sau khi \u0111\u00e3 \u0111i\u1ec1n \u0111\u01b0\u1ee3c v\u00e0o \u00f4 B1, m\u1ed9t y\u00eau c\u1ea7u m\u1edbi l\u1ea1i \u0111\u01b0\u1ee3c \u0111\u01b0a ra:<\/p>\n<p><em>\u0110i\u1ec1n c\u1ee5m t\u1eeb \u201cBan D \u201d v\u00e0o sau \u201cL\u1edbp 10A10 \u201c.<\/em><\/p>\n<p>\u0110\u1ed1i v\u1edbi y\u00eau c\u1ea7u n\u00e0y, ta c\u00f3 th\u1ec3 ti\u1ebfp t\u1ee5c s\u1eed d\u1ee5ng d\u1ea5u \u201c&amp;\u201d, ho\u1eb7c <strong>s\u1eed d\u1ee5ng h\u00e0m.\u00a0<\/strong>V\u1ec1 h\u00e0m ta c\u00f3\u00a0<strong>CONCAT, TEXTJOIN\u2026\u00a0<\/strong>B\u1ea1n c\u00f3 th\u1ec3 \u0111\u1ecdc chi ti\u1ebft\u00a0<strong><a href=\"http:\/\/blog.ezworkapp.com\/ghep-noi-ky-tu-voi-ham-concat-va-textjoin-trong-excel.html\">t\u1ea1i \u0111\u00e2y<\/a>.<\/strong><\/p>\n<p>B\u1ecf qua ph\u01b0\u01a1ng th\u1ee9c, ta h\u00e3y \u0111i v\u00e0o vi\u1ec7c x\u1eed l\u00fd chu\u1ed7i. T\u1ea1i \u00f4 C1, c\u00f4ng th\u1ee9c kh\u1edfi \u0111\u1ea7u c\u1ee7a ta c\u00f3 th\u1ec3 nh\u01b0 sau:<\/p>\n<p><strong>=CONCAT(\u201cL\u1edbp 10A10 \u201c,\u201dBan D\u201d)<\/strong><\/p>\n<p>Ho\u1eb7c t\u1ed5ng qu\u00e1t cho b\u00e0i to\u00e1n \u0111i\u1ec1n c\u1ee5m t\u1eeb\u00a0<strong>X<\/strong> v\u00e0o sau\u00a0<strong>Y<\/strong> ch\u1eef c\u00e1i, ta c\u00f3 th\u1ec3 \u0111\u01b0a ra c\u00f4ng th\u1ee9c:<\/p>\n<p>=<strong>CONCAT(LEFT(B1,Y),\u201dBan D\u201d)<\/strong><\/p>\n<p>C\u1ee5 th\u1ec3 trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y:<\/p>\n<p><strong>=CONCAT(LEFT(B1,LEN(\u201cL\u1edbp 10A10 \u201c),\u201dBan D \u201c))<\/strong><\/p>\n<p>Sau khi x\u1eed l\u00fd k\u1ebft qu\u1ea3 s\u1ebd l\u00e0:<\/p>\n<p><strong>L\u1edbp 10A10 Ban D<\/strong><\/p>\n<p>\u0110\u1ec3 gh\u00e9p n\u1ed1t ph\u1ea7n sau, ta c\u1ea7n t\u00ecm \u0111i\u1ec3m b\u1eaft \u0111\u1ea7u b\u1eb1ng c\u00e1ch k\u1ebft h\u1ee3p\u00a0<strong>h\u00e0m RIGHT<\/strong> v\u00e0\u00a0<strong>h\u00e0m LEN.<\/strong><\/p>\n<p>\u0110\u1ec3 t\u00ecm v\u00ed tr\u00ed b\u1eaft \u0111\u1ea7u sau \u201c<strong>L\u1edbp 10A10 \u201c<\/strong> ta \u0111i\u1ec1n c\u00f4ng th\u1ee9c:<\/p>\n<p><strong>=LEN(B1)-LEN(\u201cL\u1edbp 10A10 \u201c))<\/strong><\/p>\n<p>Sau \u0111\u00f3 d\u00f9ng h\u00e0m RIGHT tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3:<\/p>\n<p><strong>=RIGHT(B1,LEN(B1)-LEN(\u201cL\u1edbp 10A10 \u201c))<\/strong><\/p>\n<p>Cu\u1ed1i c\u00f9ng \u0111em v\u00e0o h\u00e0m CONCAT:<\/p>\n<blockquote>\n<p><strong>=CONCAT(LEFT(B1,LEN(\u201cL\u1edbp 10A10 \u201c)),\u201dBan D \u201c,RIGHT(B1,LEN(B1)-LEN(\u201cL\u1edbp 10A10 \u201c)))<\/strong><\/p>\n<\/blockquote>\n<p>S\u1eed d\u1ee5ng autofill:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-25991\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/them-tu-hang-loat-3.png\" width=\"789\" height=\"194\" title=\"them-tu-hang-loat-3\"><\/p>\n<p>\u00a0<\/p>\n<p>Kh\u00f4ng h\u1ec1 kh\u00f3 ph\u1ea3i kh\u00f4ng n\u00e0o? Ngo\u00e0i ra b\u1ea1n c\u00f3 th\u1ec3 tham kh\u1ea3o nh\u1eefng b\u00e0i vi\u1ebft t\u01b0\u01a1ng t\u1ef1 sau:<\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/ghep-noi-ky-tu-voi-ham-concat-va-textjoin-trong-excel.html\"><strong>Gh\u00e9p n\u1ed1i k\u00ed t\u1ef1 v\u1edbi CONCAT ho\u1eb7c TEXTJOIN<\/strong><\/a><\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-tra-cuu-ki-tu-voi-ham-char-va-code-trong-excel.html\"><strong>Tra c\u1ee9u k\u00ed t\u1ef1 v\u1edbi h\u00e0m CHAR v\u00e0 CODE<\/strong><\/a><\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/cach-dem-ki-tu-trong-o-bang-ham-len.html\"><strong>\u0110\u1ebfm k\u00ed t\u1ef1 trong \u00f4 t\u00ednh b\u1eb1ng h\u00e0m LEN<\/strong><\/a><\/p>\n<p>\u00a0<\/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\/2024\/12\/ex101_92019-nho-849.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":9020,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-9019","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\/9019","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=9019"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/9019\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/9020"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=9019"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=9019"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=9019"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}