{"id":9294,"date":"2025-01-02T00:29:20","date_gmt":"2025-01-02T00:29:20","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2025\/01\/02\/huong-dan-cach-chuyen-mot-cot-thanh-nhieu-cot-bang-cong-thuc-trong-excel-phan-2\/"},"modified":"2025-01-02T00:29:20","modified_gmt":"2025-01-02T00:29:20","slug":"huong-dan-cach-chuyen-mot-cot-thanh-nhieu-cot-bang-cong-thuc-trong-excel-phan-2","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2025\/01\/02\/huong-dan-cach-chuyen-mot-cot-thanh-nhieu-cot-bang-cong-thuc-trong-excel-phan-2\/","title":{"rendered":"H\u01b0\u1edbng d\u1eabn c\u00e1ch chuy\u1ec3n m\u1ed9t c\u1ed9t th\u00e0nh nhi\u1ec1u c\u1ed9t b\u1eb1ng c\u00f4ng th\u1ee9c trong Excel (Ph\u1ea7n 2)"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p>Trong<a href=\"http:\/\/blog.ezworkapp.com\/chuyen-mot-cot-thanh-nhieu-cot-bang-cong-thuc.html\"> b\u00e0i vi\u1ebft tr\u01b0\u1edbc<\/a>, H\u1ecdc Excel Online \u0111\u00e3 h\u01b0\u1edbng d\u1eabn b\u1ea1n c\u00e1ch chuy\u1ec3n m\u1ed9t c\u1ed9t th\u00e0nh nhi\u1ec1u c\u1ed9t b\u1eb1ng c\u00f4ng th\u1ee9c v\u1edbi\u00a0s\u1ef1 nh\u1ea3y \u0111\u01a1n v\u1ecb di\u1ec5n ra \u1edf c\u00e1c h\u00e0ng. \u1ede b\u00e0i vi\u1ebft n\u00e0y, ch\u00fang ta s\u1ebd c\u00f9ng t\u00ecm hi\u1ec3u c\u00e1ch chuy\u1ec3n \u0111\u1ed5i\u00a0v\u1edbi s\u1ef1 nh\u1ea3y \u0111\u01a1n v\u1ecb t\u1ea1i \u0111\u1ea7u m\u1ed7i c\u1ed9t. C\u1ee5 th\u1ec3, h\u00e3y xem v\u00ed d\u1ee5 d\u01b0\u1edbi.<\/p>\n<p>L\u1ea7n n\u00e0y, ta s\u1ebd s\u1eed d\u1ee5ng v\u00ed d\u1ee5 \u0111\u01b0\u1ee3c \u1ee9ng d\u1ee5ng nhi\u1ec1u h\u01a1n trong th\u1ef1c t\u1ebf: Tr\u00ecnh b\u00e0y l\u1ea1i m\u1ed9t b\u1ea3ng d\u1eef li\u1ec7u copy v\u1ec1 t\u1eeb tr\u00ean m\u1ea1ng.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-25003\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/chuyen-1-cot-thanh-nhieu-cot-8.png\" width=\"513\" height=\"348\" title=\"chuyen-1-cot-thanh-nhieu-cot-8\"><\/p>\n<p>\u1ee8ng d\u1ee5ng \u0111\u00e2y r\u1ed3i! L\u00e0m th\u1ebf n\u00e0o \u0111\u1ec3 nh\u00e9t d\u1eef li\u1ec7u t\u1eeb c\u1ed9t B v\u00e0o trong b\u1ea3ng \u0111\u00e2y?<\/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-6775dddf570cc\" 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\/chuyen-mot-cot-thanh-nhieu-cot-bang-cong-thuc-phan-2.html#Chuyen_mot_cot_thanh_nhieu_cot_bang_cong_thuc_OFFSET_trong_bai_hoc_truoc\" title=\"Chuy\u1ec3n m\u1ed9t c\u1ed9t th\u00e0nh nhi\u1ec1u c\u1ed9t b\u1eb1ng c\u00f4ng th\u1ee9c OFFSET trong b\u00e0i h\u1ecdc tr\u01b0\u1edbc\">Chuy\u1ec3n m\u1ed9t c\u1ed9t th\u00e0nh nhi\u1ec1u c\u1ed9t b\u1eb1ng c\u00f4ng th\u1ee9c OFFSET trong b\u00e0i h\u1ecdc tr\u01b0\u1edbc<\/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\/chuyen-mot-cot-thanh-nhieu-cot-bang-cong-thuc-phan-2.html#Chuyen_mot_cot_thanh_nhieu_cot_bang_cong_thuc_INDEX\" title=\"Chuy\u1ec3n m\u1ed9t c\u1ed9t th\u00e0nh nhi\u1ec1u c\u1ed9t b\u1eb1ng c\u00f4ng th\u1ee9c INDEX\">Chuy\u1ec3n m\u1ed9t c\u1ed9t th\u00e0nh nhi\u1ec1u c\u1ed9t b\u1eb1ng c\u00f4ng th\u1ee9c INDEX<\/a><\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h3><span class=\"ez-toc-section\" id=\"Chuyen_mot_cot_thanh_nhieu_cot_bang_cong_thuc_OFFSET_trong_bai_hoc_truoc\"><\/span>Chuy\u1ec3n m\u1ed9t c\u1ed9t th\u00e0nh nhi\u1ec1u c\u1ed9t b\u1eb1ng c\u00f4ng th\u1ee9c OFFSET trong b\u00e0i h\u1ecdc tr\u01b0\u1edbc<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>V\u1edbi ph\u1ea7n 1, c\u00e1c b\u1ea1n \u0111\u00e3 \u0111\u01b0\u1ee3c h\u1ecdc c\u00e1ch\u00a0<strong><a href=\"http:\/\/blog.ezworkapp.com\/chuyen-mot-cot-thanh-nhieu-cot-bang-cong-thuc.html\">s\u1eed d\u1ee5ng OFFSET<\/a><\/strong> nh\u01b0 th\u1ebf n\u00e0o. \u0110\u1ed1i v\u1edbi d\u1eef li\u1ec7u l\u1ea7n n\u00e0y, thay v\u00ec \u0111i t\u1eeb tr\u00e1i sang ph\u1ea3i t\u0103ng 1 \u0111\u01a1n v\u1ecb, \u0111i t\u1eeb tr\u00ean xu\u1ed1ng nh\u1ea3y \u0111\u01a1n v\u1ecb th\u00ec ta l\u00e0m ng\u01b0\u1ee3c l\u1ea1i:\u00a0<em>\u0111i t\u1eeb tr\u00e1i sang ph\u1ea3i nh\u1ea3y \u0111\u01a1n v\u1ecb, \u0111i t\u1eeb tr\u00ean xu\u1ed1ng t\u0103ng 1 \u0111\u01a1n v\u1ecb<\/em>.<\/p>\n<p>N\u1ebfu c\u00e1c b\u1ea1n ch\u01b0a nh\u1edb th\u00ec <a href=\"http:\/\/blog.ezworkapp.com\/33-%E2%97%8F-cach-su-dung-ham-offset-trong-excel.html\">c\u00f4ng th\u1ee9c\u00a0<strong>OFFSET<\/strong><\/a>\u00a0trong b\u00e0i tr\u01b0\u1edbc nh\u01b0 sau:<\/p>\n<p><strong><em>=IF(COLUMNS($C$1:C1)&gt;VALUE(LEFT($K$1,1)),\u201d\u201d,(OFFSET($A$1,COLUMNS($A1:A1)-1+(ROWS(A$1:A1)-1)*5,0)))<\/em><\/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-34.gif\" title=\"ex101_92019-nho-34\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p>T\u1ea1m th\u1eddi b\u1ecf ph\u1ea7n r\u01b0\u1eddm r\u00e0 \u0111i, ta quay l\u1ea1i c\u01a1 b\u1ea3n:<\/p>\n<p><strong>=OFFSET($A$1,COLUMNS($A1:A1)-1+(ROWS(A$1:A1)-1)*5,0)<\/strong><\/p>\n<p>\u0110\u1ec3 \u0111\u1ea3o l\u1ea1i, ta\u00a0<strong>kh\u00f4ng<\/strong> \u0111\u1ed5i v\u1ecb tr\u00ed ROWS v\u00e0 COLUMNS, m\u00e0 ta thay \u0111\u1ed5i ch\u1ec9 s\u1ed1 h\u1ec7 s\u1ed1, chuy\u1ec3n\u00a0<strong>*5<\/strong> sang COLUMNS<strong>.\u00a0<\/strong>Khi \u0111\u00f3, c\u00f4ng th\u1ee9c s\u1ebd th\u00e0nh:<\/p>\n<p><strong>=OFFSET($A$1,(COLUMNS($A1:A1)-1)*5+(ROWS(A$1:A1)-1),0)<\/strong><\/p>\n<p>\u00c1p d\u1ee5ng v\u00e0o b\u00e0i, thay \u0111\u1ed5i t\u1ecda \u0111\u1ed9, thay \u0111\u1ed5i h\u1ec7 s\u1ed1, ta c\u00f3 k\u1ebft qu\u1ea3:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-25004\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/chuyen-1-cot-thanh-nhieu-cot-9.png\" width=\"666\" height=\"419\" title=\"chuyen-1-cot-thanh-nhieu-cot-9\"><\/p>\n<h3><span class=\"ez-toc-section\" id=\"Chuyen_mot_cot_thanh_nhieu_cot_bang_cong_thuc_INDEX\"><\/span>Chuy\u1ec3n m\u1ed9t c\u1ed9t th\u00e0nh nhi\u1ec1u c\u1ed9t b\u1eb1ng c\u00f4ng th\u1ee9c INDEX<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Ngo\u00e0i c\u00e1ch l\u00e0m v\u1edbi OFFSET, H\u1ecdc Excel Online gi\u1edbi thi\u1ec7u t\u1edbi c\u00e1c b\u1ea1n \u1ee9ng d\u1ee5ng c\u1ee7a <a href=\"http:\/\/blog.ezworkapp.com\/6-tinh-nang-hieu-qua-nhat-cua-ham-index-trong-excel.html\">h\u00e0m INDEX<\/a> trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y. Ta h\u00e3y tr\u1edf l\u1ea1i v\u1edbi d\u1eef li\u1ec7u c\u0169:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-25005\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/chuyen-1-cot-thanh-nhieu-cot-1-1.png\" width=\"449\" height=\"312\" title=\"chuyen-1-cot-thanh-nhieu-cot-1-1\"><\/p>\n<p>\u0110\u1ec3 chuy\u1ec3n m\u1ed9t c\u1ed9t th\u00e0nh nhi\u1ec1u c\u1ed9t m\u00e0 s\u1ef1 nh\u1ea3y \u0111\u01a1n v\u1ecb di\u1ec5n ra theo h\u00e0ng ngang, t\u1ea1i \u00f4 C1 ta c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng INDEX k\u1ebft h\u1ee3p ROWS v\u00e0 COLUMNS nh\u01b0 sau:<\/p>\n<p><strong>=INDEX($A$1:$A$15,ROW(C1)+(5*(COLUMNS($C$1:C$1)-1)))<\/strong><\/p>\n<p>V\u1edbi\u00a0<strong>5<\/strong> l\u00e0\u00a0<strong>s\u1ed1 d\u00f2ng<\/strong>.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-25006\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/chuyen-1-cot-thanh-nhieu-cot-10.png\" width=\"638\" height=\"373\" title=\"chuyen-1-cot-thanh-nhieu-cot-10\"><\/p>\n<p>V\u1eabn l\u00e0 s\u1ef1 k\u1ebft h\u1ee3p quen thu\u1ed9c \u0111\u00f3 th\u00f4i. B\u00e2y gi\u1edd, H\u1ecdc Excel Online s\u1ebd \u0111\u1ec3 l\u1ea1i ph\u1ea7n gi\u1ea3i th\u00edch cho c\u00e1c b\u1ea1n. H\u00e3y v\u1eadn d\u1ee5ng nh\u1eefng g\u00ec \u0111\u00e3 \u0111\u1ecdc \u0111\u1ec3 t\u1ef1 tr\u1ea3 l\u1eddi c\u00e2u h\u1ecfi: c\u00e1ch s\u1eed d\u1ee5ng \u1edf \u0111\u00e2y l\u00e0 g\u00ec?<\/p>\n<p>Ch\u00fac c\u00e1c b\u1ea1n h\u1ecdc t\u1ed1t!<\/p>\n<p>Tham kh\u1ea3o th\u00eam c\u00e1c b\u00e0i vi\u1ebft c\u00f9ng ch\u1ee7 \u0111\u1ec1 t\u1ea1i:<\/p>\n<p><strong><a href=\"http:\/\/blog.ezworkapp.com\/chuyen-mot-cot-thanh-nhieu-cot-bang-cong-thuc.html\">H\u01b0\u1edbng d\u1eabn c\u00e1ch chuy\u1ec3n m\u1ed9t c\u1ed9t th\u00e0nh nhi\u1ec1u c\u1ed9t b\u1eb1ng c\u00f4ng th\u1ee9c trong Excel (Ph\u1ea7n 1)<\/a><\/strong><\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/33-%E2%97%8F-cach-su-dung-ham-offset-trong-excel.html\"><strong>T\u00ecm hi\u1ec3u h\u00e0m OFFSET th\u00f4ng qua video<\/strong><\/a><\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/su-khac-nhau-giua-ham-columns-va-ham-column-trong-excel.html\"><strong>Ph\u00e2n bi\u1ec7t h\u00e0m COLUMN v\u00e0 COLUMNS<\/strong><\/a><\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/4-tuyet-chieu-tra-cuu-voi-vlookup-index-match-sumproduct-va-names-range-trong-excel.html\"><strong>Tuy\u1ec7t chi\u00eau tra c\u1ee9u v\u1edbi INDEX<\/strong><\/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\/2025\/01\/ex101_92019-nho-34.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":9295,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-9294","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\/9294","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=9294"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/9294\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/9295"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=9294"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=9294"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=9294"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}