{"id":4455,"date":"2024-12-06T06:24:58","date_gmt":"2024-12-06T06:24:58","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/06\/huong-dan-cach-tao-danh-sach-dong-trong-excel-voi-ham-offset\/"},"modified":"2024-12-06T06:24:58","modified_gmt":"2024-12-06T06:24:58","slug":"huong-dan-cach-tao-danh-sach-dong-trong-excel-voi-ham-offset","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/06\/huong-dan-cach-tao-danh-sach-dong-trong-excel-voi-ham-offset\/","title":{"rendered":"H\u01b0\u1edbng d\u1eabn c\u00e1ch t\u1ea1o danh s\u00e1ch \u0111\u1ed9ng trong Excel v\u1edbi h\u00e0m OFFSET"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p><a href=\"http:\/\/blog.ezworkapp.com\/ham-offset-va-cac-ket-hop-ham-cua-no-trong-excel-cong-thuc-vi-du.html\">H\u00e0m\u00a0OFFSET<\/a> l\u00e0 m\u1ed9t trong nh\u1eefng h\u00e0m c\u00f3 nhi\u1ec1u t\u00ednh n\u0103ng r\u1ea5t th\u00fa v\u1ecb. M\u1ed9t trong nh\u1eefng t\u00ednh n\u0103ng\u00a0\u0111\u00f3 l\u00e0 ch\u00fang ta c\u00f3 th\u1ec3 d\u00f9ng h\u00e0m\u00a0OFFSET t\u1ea1o ra danh s\u00e1ch\u00a0\u0111\u1ed9ng trong\u00a0Excel. Trong b\u00e0i vi\u1ebft n\u00e0y, b\u1ea1n h\u00e3y c\u00f9ng H\u1ecdc\u00a0Excel Online t\u00ecm hi\u1ec3u c\u00e1ch\u00a0t\u1ea1o danh s\u00e1ch \u0111\u1ed9ng trong excel v\u1edbi h\u00e0m offset nh\u00e9.<\/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-675298b98d080\" 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\/huong-dan-cach-tao-danh-sach-dong-trong-excel-voi-ham-offset.html#Tao_danh_sach_khong_co_dong_trong_thua_o_cuoi_danh_sach\" title=\"T\u1ea1o danh s\u00e1ch kh\u00f4ng c\u00f3 d\u00f2ng tr\u1ed1ng th\u1eeba\u00a0\u1edf cu\u1ed1i danh s\u00e1ch\">T\u1ea1o danh s\u00e1ch kh\u00f4ng c\u00f3 d\u00f2ng tr\u1ed1ng th\u1eeba\u00a0\u1edf cu\u1ed1i danh s\u00e1ch<\/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\/huong-dan-cach-tao-danh-sach-dong-trong-excel-voi-ham-offset.html#Tao_danh_sach_phu_thuoc_nhau_danh_sach_theo_nhom_voi_ham_OFFSET\" title=\"T\u1ea1o danh s\u00e1ch ph\u1ee5 thu\u1ed9c nhau, danh s\u00e1ch theo nh\u00f3m v\u1edbi h\u00e0m\u00a0OFFSET\">T\u1ea1o danh s\u00e1ch ph\u1ee5 thu\u1ed9c nhau, danh s\u00e1ch theo nh\u00f3m v\u1edbi h\u00e0m\u00a0OFFSET<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/blog.hocexcel.online\/huong-dan-cach-tao-danh-sach-dong-trong-excel-voi-ham-offset.html#Ket_luan\" title=\"K\u1ebft lu\u1eadn\">K\u1ebft lu\u1eadn<\/a><\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2><span class=\"ez-toc-section\" id=\"Tao_danh_sach_khong_co_dong_trong_thua_o_cuoi_danh_sach\"><\/span>T\u1ea1o danh s\u00e1ch kh\u00f4ng c\u00f3 d\u00f2ng tr\u1ed1ng th\u1eeba\u00a0\u1edf cu\u1ed1i danh s\u00e1ch<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>V\u00ed d\u1ee5 b\u1ea1n c\u00f3 1 danh s\u00e1ch nh\u00e2n vi\u00ean, b\u1ea1n d\u1ef1 t\u00ednh danh s\u00e1ch\u00a0\u0111\u00f3 s\u1ebd g\u1ed3m t\u1ed1i\u00a0\u0111a l\u00e0 20 ng\u01b0\u1eddi. Th\u1ebf nh\u01b0ng t\u1ea1i th\u1eddi\u00a0\u0111i\u1ec3m th\u00e1ng 1, b\u1ea1n m\u1edbi ch\u1ec9 c\u00f3\u00a05 ng\u01b0\u1eddi trong danh s\u00e1ch, c\u00f2n l\u1ea1i 15 d\u00f2ng tr\u1ed1ng. V\u00e0 trong nh\u1eefng th\u00e1ng ti\u1ebfp theo th\u00ec s\u1ed1 ng\u01b0\u1eddi c\u00f3 th\u1ec3 t\u0103ng th\u00eam, gi\u1ea3m\u00a0\u0111i.<\/p>\n<p>V\u1eady l\u00e0m c\u00e1ch n\u00e0o\u00a0\u0111\u1ec3 danh s\u00e1ch nh\u00e2n vi\u00ean\u00a0\u0111\u00f3 t\u1ef1\u00a0\u0111\u1ed9ng thay\u00a0\u0111\u1ed5i theo s\u1ed1 nh\u00e2n vi\u00ean hi\u1ec7n c\u00f3, v\u00e0 nh\u1eefng d\u00f2ng tr\u1ed1ng s\u1ebd b\u1ecb lo\u1ea1i ra kh\u1ecfi danh s\u00e1ch?<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-25211\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/danh-sach-nhan-vien-gom-5-nguoi.png\" width=\"500\" height=\"241\" title=\"danh-sach-nhan-vien-gom-5-nguoi\"><\/p>\n<p style=\"text-align: center\"><em>H\u00ecnh 1: danh s\u00e1ch nh\u00e2n vi\u00ean khi c\u00f3 5 ng\u01b0\u1eddi<\/em><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-25212\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/danh-sach-nhan-vien-gom-7-nguoi.png\" width=\"500\" height=\"240\" title=\"danh-sach-nhan-vien-gom-7-nguoi\"><\/p>\n<p style=\"text-align: center\"><em>H\u00ecnh 2: danh s\u00e1ch t\u1ef1\u00a0\u0111\u1ed9ng m\u1edf r\u1ed9ng khi c\u00f3 th\u00eam 2 ng\u01b0\u1eddi<\/em><\/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-362.gif\" title=\"ex101_92019-nho-362\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p>\u0110\u1ec3 danh s\u00e1ch c\u00f3 th\u1ec3 t\u1ef1 thay\u00a0\u0111\u1ed5i\u00a0\u0111\u01b0\u1ee3c, ch\u00fang ta ph\u1ea3i s\u1eed d\u1ee5ng h\u00e0m\u00a0OFFSET nh\u01b0 sau:<\/p>\n<p><strong>T\u00ecm \u0111i\u1ec3m b\u1eaft \u0111\u1ea7u<\/strong><\/p>\n<p>Danh s\u00e1ch b\u1eaft\u00a0\u0111\u1ea7u t\u1eeb\u00a0\u00f4 B3, v\u00ec v\u1eady trong h\u00e0m\u00a0Offset ch\u00fang ta c\u00f3 th\u1ec3:<\/p>\n<ul>\n<li>B\u1eaft\u00a0\u0111\u1ea7u v\u1edbi tham chi\u1ebfu B3, kh\u00f4ng thay\u00a0\u0111\u1ed5i v\u1ec1 d\u00f2ng, c\u1ed9t<\/li>\n<li>B\u1eaft\u00a0\u0111\u1ea7u v\u1edbi tham chi\u1ebfu B2, xu\u1ed1ng 1 d\u00f2ng, kh\u00f4ng\u00a0\u0111\u1ed5i v\u1ec1 c\u1ed9t<\/li>\n<li>B\u1eaft\u00a0\u0111\u1ea7u v\u1edbi tham chi\u1ebfu A2, xu\u1ed1ng 1 d\u00f2ng, sang ph\u1ea3i 1 c\u1ed9t<\/li>\n<\/ul>\n<p>Vi\u1ec7c ch\u1ecdn v\u1ecb tr\u00ed b\u1eaft\u00a0\u0111\u1ea7u ch\u1ec9\u00a0\u0111\u01a1n gi\u1ea3n l\u00e0 c\u0103n c\u1ee9\u00a0\u0111\u1ec3 ch\u00fang ta t\u00ecm\u00a0\u0111\u01b0\u1ee3c\u00a0\u0111i\u1ec3m\u00a0\u0111\u1ea7u c\u1ee7a danh s\u00e1ch. T\u00f9y theo v\u1ecb tr\u00ed tham chi\u1ebfu m\u00e0 ch\u00fang ta c\u00f3 th\u1ef1c hi\u1ec7n di chuy\u1ec3n s\u1ed1 d\u00f2ng, s\u1ed1 c\u1ed9t kh\u00f4ng. N\u1ebfu c\u00f3 th\u00ec di chuy\u1ec3n bao nhi\u00eau d\u00f2ng, bao nhi\u00eau c\u1ed9t.<\/p>\n<p>V\u00ed d\u1ee5 c\u00f4ng th\u1ee9c b\u1eaft\u00a0\u0111\u1ea7u v\u1edbi\u00a0\u00f4 B3<\/p>\n<p style=\"padding-left: 30px\">=OFFSET(B3,0,0,\u0111\u1ed9 r\u1ed9ng theo s\u1ed1d\u00f2ng,\u00a0\u0111\u1ed9 r\u1ed9ng theo s\u1ed1 c\u1ed9t)<\/p>\n<p><strong>X\u00e1c\u00a0\u0111\u1ecbnh\u00a0\u0111\u1ed9 r\u1ed9ng c\u1ee7a danh s\u00e1ch<\/strong><\/p>\n<p>Ti\u1ebfp\u00a0\u0111\u1ebfn l\u00e0 x\u00e1c\u00a0\u0111\u1ecbnh danh s\u00e1ch\u00a0\u0111\u00f3 c\u00f3 bao nhi\u00eau\u00a0\u00f4. \u0110\u1ec3 bi\u1ebft\u00a0\u0111i\u1ec1u n\u00e0y, ch\u00fang ta d\u00f9ng <a href=\"http:\/\/blog.ezworkapp.com\/cach-dung-ham-count-va-counta-de-dem-o-trong-excel.html\">h\u00e0m COUNTA<\/a>. H\u00e0m COUNTA c\u00f3 t\u00e1c d\u1ee5ng\u00a0\u0111\u1ebfm nh\u1eefng\u00a0\u00f4 c\u00f3 ch\u1ee9a n\u1ed9i dung (kh\u00f4ng ph\u1ea3i\u00a0\u00f4 tr\u1ed1ng)<\/p>\n<p>Ch\u00fang ta\u00a0\u0111\u1ebfm trong c\u1ed9t B, t\u1eeb d\u00f2ng 3 t\u1edbi d\u00f2ng 22 (t\u01b0\u01a1ng\u00a0\u1ee9ng v\u1edbi t\u1ed1i \u0111a 20 nh\u00e2n vi\u00ean trong danh s\u00e1ch):<\/p>\n<p>COUNTA(B3:B20)<\/p>\n<ul>\n<li>V\u1edbi 5 nh\u00e2n vi\u00ean, k\u1ebft qu\u1ea3 h\u00e0m COUNTA = 5<\/li>\n<li>V\u1edbi 7 nh\u00e2n vi\u00ean, k\u1ebft qu\u1ea3 h\u00e0m COUNTA=7<\/li>\n<\/ul>\n<p>Khi\u00a0\u0111\u1eb7t gi\u00e1 tr\u1ecb n\u00e0y v\u00e0o tham s\u1ed1\u00a0Weight c\u1ee7a h\u00e0m\u00a0OFFSET, ch\u00fang ta s\u1ebd c\u00f3\u00a0\u0111\u1ed9 r\u1ed9ng c\u1ee7a danh s\u00e1ch l\u00e0 5 hay 7\u00a0\u00f4, t\u00ednh t\u1eeb\u00a0\u00f4 B3.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-25213\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/mo-ta-cach-dung-ham-offset.png\" width=\"500\" height=\"466\" title=\"mo-ta-cach-dung-ham-offset\"><\/p>\n<p>=OFFST(B3,0,0,COUNTA(B3:B22))<\/p>\n<p>Kh\u00f4ng c\u1ea7n nh\u1eadp tham s\u1ed1\u00a0\u0111\u1ed9 r\u1ed9ng theo s\u1ed1 c\u1ed9t, v\u00ec\u00a0\u1edf\u00a0\u0111\u00e2y ch\u00fang ta ch\u1ec9\u00a0\u00e1p d\u1ee5ng tr\u00ean 1 c\u1ed9t B<\/p>\n<p><strong>T\u1ea1o danh s\u00e1ch ch\u1ecdn v\u1edbi Data Validation<\/strong><\/p>\n<p>Sau khi\u00a0\u0111\u00e3 vi\u1ebft\u00a0\u0111\u00fang\u00a0\u0111\u01b0\u1ee3c h\u00e0m\u00a0OFFSET th\u00ec ch\u00fang ta ch\u1ec9 vi\u1ec7c copy c\u00f4ng th\u1ee9c\u00a0\u0111\u00f3\u00a0\u0111\u1ec3\u00a0\u0111\u01b0a v\u00e0o trong m\u1ee5c <a href=\"http:\/\/blog.ezworkapp.com\/tao-mot-danh-sach-tuy-chon-trong-excel-tinh-dong-tu-mot-bang-tinh-khac.html\">t\u1ea1o danh s\u00e1ch ch\u1ecdn trong Data Validation<\/a>:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-25214\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/tao-danh-sach-chon-data-validation.png\" width=\"718\" height=\"448\" title=\"tao-danh-sach-chon-data-validation\"><\/p>\n<h2><span class=\"ez-toc-section\" id=\"Tao_danh_sach_phu_thuoc_nhau_danh_sach_theo_nhom_voi_ham_OFFSET\"><\/span>T\u1ea1o danh s\u00e1ch ph\u1ee5 thu\u1ed9c nhau, danh s\u00e1ch theo nh\u00f3m v\u1edbi h\u00e0m\u00a0OFFSET<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>V\u00ed d\u1ee5 v\u1ec1 danh s\u00e1ch ph\u1ee5 thu\u1ed9c nhau, danh s\u00e1ch theo nh\u00f3m nh\u01b0 sau:<\/p>\n<p>Trong danh s\u00e1ch nh\u00e2n vi\u00ean\u00a0\u1edf tr\u00ean, ch\u00fang ta c\u00f3 th\u00eam c\u1ed9t B\u1ed9 ph\u1eadn. T\u1ea1i\u00a0\u0111\u00f3 chia l\u00e0m 2 b\u1ed9 ph\u1eadn l\u00e0 Kinh doanh v\u00e0 K\u1ebf to\u00e1n. Khi ch\u1ecdn b\u1ed9 ph\u1eadn Kinh doanh, ch\u00fang ta s\u1ebd c\u00f3 1 danh s\u00e1ch ch\u1ec9 c\u00f3 nh\u1eefng nh\u00e2n vi\u00ean thu\u1ed9c b\u1ed9 ph\u1eadn kinh doanh:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-25215\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/danh-sach-nhan-vien-bo-phan-kinh-doanh.png\" width=\"693\" height=\"542\" title=\"danh-sach-nhan-vien-bo-phan-kinh-doanh\"><\/p>\n<p>C\u00f2n khi ch\u1ecdn b\u1ed9 ph\u1eadn\u00a0K\u1ebf to\u00e1n, danh s\u00e1ch nh\u00e2n vi\u00ean s\u1ebd thay\u00a0\u0111\u1ed5i ch\u1ec9 c\u00f3 nh\u00e2n vi\u00ean thu\u1ed9c b\u1ed9 ph\u1eadn k\u1ebf to\u00e1n:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-25216\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/danh-sach-nhan-vien-bo-phan-ke-toan.png\" width=\"692\" height=\"543\" title=\"danh-sach-nhan-vien-bo-phan-ke-toan\"><\/p>\n<p>\u0110\u1ec3 l\u00e0m\u00a0\u0111\u01b0\u1ee3c vi\u1ec7c n\u00e0y, ch\u00fang ta s\u1ebd s\u1eed d\u1ee5ng h\u00e0m\u00a0OFFSET nh\u01b0 sau:<\/p>\n<p><strong>X\u00e1c\u00a0\u0111\u1ecbnh\u00a0\u0111i\u1ec3m b\u1eaft\u00a0\u0111\u1ea7u l\u00e0m tham chi\u1ebfu g\u1ed1c<\/strong><\/p>\n<p>B\u1edfi v\u00ec\u00a0b\u1ed9 ph\u1eadn c\u00f3 th\u1ec3 thay\u00a0\u0111\u1ed5i, n\u00ean ch\u00fang ta s\u1ebd x\u00e1c\u00a0\u0111\u1ecbnh\u00a0\u0111i\u1ec3m b\u1eaft\u00a0\u0111\u1ea7u l\u00e0 1 v\u1ecb tr\u00ed c\u1ed1\u00a0\u0111\u1ecbnh. V\u00ed d\u1ee5 nh\u01b0\u00a0\u00f4 B2<\/p>\n<p style=\"padding-left: 30px\">=OFFSET(B2, \u2026.)<\/p>\n<p><strong>X\u00e1c\u00a0\u0111\u1ecbnh\u00a0h\u01b0\u1edbng thay\u00a0\u0111\u1ed5i\u00a0\u0111i\u1ec3m b\u1eaft\u00a0\u0111\u1ea7u theo d\u00f2ng<\/strong><\/p>\n<p>T\u00f9y v\u00e0o b\u1ed9 ph\u1eadn\u00a0\u0111\u01b0\u1ee3c ch\u1ecdn\u00a0\u1edf\u00a0\u00f4 F2 m\u00e0 ch\u00fang ta s\u1ebd x\u00e1c\u00a0\u0111\u1ecbnh t\u1eeb v\u1ecb tr\u00ed B2 s\u1ebd di chuy\u1ec3n bao nhi\u00eau d\u00f2ng. Vi\u1ec7c n\u00e0y x\u00e1c\u00a0\u0111\u1ecbnh m\u1ed9t c\u00e1ch kh\u00e1\u00a0\u0111\u01a1n gi\u1ea3n v\u1edbi <a href=\"http:\/\/blog.ezworkapp.com\/cach-su-dung-ham-match-trong-excel.html\">h\u00e0m MATCH <\/a>nh\u01b0 sau:<\/p>\n<p style=\"padding-left: 30px\">=MATCH(F2,B3:B22,0)<\/p>\n<p>D\u00f2 t\u00ecm gi\u00e1 tr\u1ecb F2\u00a0\u1edf trong v\u00f9ng B3:B22. K\u1ebft qu\u1ea3 s\u1ebd l\u00e0 v\u1ecb tr\u00ed d\u00f2ng th\u1ee9 m\u1ea5y trong v\u00f9ng B3:B22\u00a0\u1ee9ng v\u1edbi gi\u00e1 tr\u1ecb t\u1ea1i F2, n\u1ebfu c\u00f3 nhi\u1ec1u h\u01a1n 1 gi\u00e1 tr\u1ecb\u00a0\u0111\u00fang th\u00ec s\u1ebd tr\u1ea3 v\u1ec1 v\u1ecb tr\u00ed c\u1ee7a gi\u00e1 tr\u1ecb\u00a0\u0111\u1ea7u ti\u00ean.<\/p>\n<ul>\n<li>N\u1ebfu ch\u1ecdn Kinh doanh, ta s\u1ebd c\u00f3 v\u1ecb tr\u00ed\u00a0\u0111\u1ea7u ti\u00ean t\u00ecm\u00a0\u0111\u01b0\u1ee3c l\u00e0\u00a0\u00f4 B3,\u00a0\u1ee9ng v\u1edbi d\u00f2ng 1 =&gt; k\u1ebft qu\u1ea3 l\u00e0 1<\/li>\n<li>N\u1ebfu ch\u1ecdn K\u1ebf to\u00e1n, ta s\u1ebd c\u00f3 v\u1ecb tr\u00ed\u00a0\u0111\u1ea7u ti\u00ean t\u00ecm\u00a0\u0111\u01b0\u1ee3c l\u00e0\u00a0\u00f4 B6,\u00a0\u1ee9ng v\u1edbi d\u00f2ng\u00a04 =&gt; k\u1ebft qu\u1ea3 l\u00e0\u00a04<\/li>\n<\/ul>\n<p style=\"padding-left: 30px\">=OFFSET(B2,MATCH(F2,B3:B22,0), \u2026.)<\/p>\n<p><strong>X\u00e1c\u00a0\u0111\u1ecbnh h\u01b0\u1edbng thay\u00a0\u0111\u1ed5i\u00a0\u0111i\u1ec3m b\u1eaft\u00a0\u0111\u1ea7u theo c\u1ed9t<\/strong><\/p>\n<p>Do danh s\u00e1ch c\u1ea7n t\u00ecm l\u00e0 H\u1ecd t\u00ean nh\u00e2n vi\u00ean, c\u00e1ch v\u1ecb tr\u00ed\u00a0\u00f4 B2 l\u00e0 1 c\u1ed9t, n\u00ean s\u1ebd thay\u00a0\u0111\u1ed5i 1 c\u1ed9t. Ch\u00fang ta ch\u1ecdn s\u1ed1 1<\/p>\n<p style=\"padding-left: 30px\">=OFFSET(B2,MATCH(F2,B3:B22,0),1, \u2026)<\/p>\n<p><strong>X\u00e1c\u00a0\u0111\u1ecbnh\u00a0\u0111\u1ed9 r\u1ed9ng theo d\u00f2ng<\/strong><\/p>\n<p>\u0110\u1ec3 x\u00e1c\u00a0\u0111\u1ecbnh\u00a0\u0111\u1ed9 r\u1ed9ng theo d\u00f2ng, ch\u00fang ta c\u1ea7n\u00a0\u0111\u1ebfm xem c\u00f3 bao nhi\u00eau d\u00f2ng trong c\u1ed9t B\u1ed9 ph\u1eadn c\u00f3 gi\u00e1 tr\u1ecb gi\u1ed1ng v\u1edbi B\u1ed9 ph\u1eadn\u00a0\u0111\u01b0\u1ee3c ch\u1ecdn\u00a0\u1edf\u00a0\u00f4 F2. \u0110\u00e2y l\u00e0 vi\u1ec7c \u0111\u1ebfm theo \u0111i\u1ec1u ki\u1ec7n, do\u00a0\u0111\u00f3 ch\u00fang ta d\u00f9ng <a href=\"http:\/\/blog.ezworkapp.com\/dem-co-dieu-kien-ham-countif-co-ban.html\">h\u00e0m COUNTIF<\/a> nh\u01b0 sau:<\/p>\n<p style=\"padding-left: 30px\">=COUNTIF(B3:B22,F2)<\/p>\n<ul>\n<li>N\u1ebfu ch\u1ecdn Kinh doanh,\u00a0h\u00e0m COUNTIF\u00a0\u0111\u1ebfm ra 3 k\u1ebft qu\u1ea3 =&gt; T\u01b0\u01a1ng\u00a0\u1ee9ng\u00a0\u0111\u1ed9 r\u1ed9ng c\u1ee7a v\u00f9ng t\u1ea1o b\u1edfi h\u00e0m\u00a0OFFSET c\u00f3 3 d\u00f2ng, t\u00ednh t\u1eeb d\u00f2ng 3\u00a0\u0111\u1ebfn d\u00f2ng 5<\/li>\n<li>N\u1ebfu ch\u1ecdn K\u1ebf to\u00e1n,\u00a0h\u00e0m COUNTIF\u00a0\u0111\u1ebfm ra 4 k\u1ebft qu\u1ea3\u00a0=&gt; T\u01b0\u01a1ng\u00a0\u1ee9ng\u00a0\u0111\u1ed9 r\u1ed9ng c\u1ee7a v\u00f9ng t\u1ea1o b\u1edfi h\u00e0m\u00a0OFFSET c\u00f3\u00a04 d\u00f2ng, t\u00ednh t\u1eeb d\u00f2ng 6\u00a0\u0111\u1ebfn d\u00f2ng 9<\/li>\n<\/ul>\n<p style=\"padding-left: 30px\">=OFFSET(B2,MATCH(F2,B3:B22,0),1,COUNTIF(B3:B22,F2), \u2026)<\/p>\n<p><strong>X\u00e1c\u00a0\u0111\u1ecbnh\u00a0\u0111\u1ed9 r\u1ed9ng theo\u00a0c\u1ed9t<\/strong><\/p>\n<p>V\u00ec danh s\u00e1ch n\u00e0y ch\u00fang ta ch\u1ec9 l\u1ea5y tr\u00ean 1 c\u1ed9t, do\u00a0\u0111\u00f3 kh\u00f4ng c\u1ea7n t\u1edbi\u00a0\u0111i\u1ec1u ch\u1ec9nh v\u1ec1\u00a0\u0111\u1ed9 r\u1ed9ng s\u1ed1 c\u1ed9t. C\u00f3 th\u1ec3 kh\u00f4ng c\u1ea7n nh\u1eadp tham s\u1ed1 n\u00e0y. Ch\u00fang ta k\u1ebft th\u00fac h\u00e0m\u00a0OFFSET v\u1edbi n\u1ed9i dung<\/p>\n<p style=\"padding-left: 30px\">=OFFSET(B2,MATCH(F2,B3:B22,0),1,COUNTIF(B3:B22,F2))<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-25217\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/ket-qua-ham-offset-danh-sach-phu-thuoc.png\" width=\"800\" height=\"347\" title=\"ket-qua-ham-offset-danh-sach-phu-thuoc\"><\/p>\n<p><strong>T\u1ea1o danh s\u00e1ch ch\u1ecdn v\u1edbi Data Validation<\/strong><\/p>\n<p>Cu\u1ed1i c\u00f9ng ch\u00fang ta ch\u1ec9 vi\u1ec7c copy c\u00f4ng th\u1ee9c tr\u00ean v\u00e0 d\u00e1n v\u00e0o danh s\u00e1ch ch\u1ecdn Data Validation cho\u00a0\u00f4 F3<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-25218\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/tao-danh-sach-chon-data-validation-ds-phu-thuoc.png\" width=\"800\" height=\"454\" title=\"tao-danh-sach-chon-data-validation-ds-phu-thuoc\"><\/p>\n<p>\u0110\u1ec3 cho ch\u1eafc ch\u1eafn d\u1eef li\u1ec7u kh\u00f4ng b\u1ecb thay\u00a0\u0111\u1ed5i, khi\u00a0\u0111\u01b0a c\u00f4ng th\u1ee9c v\u00e0o Data Validation th\u00ec ch\u00fang ta n\u00ean c\u1ed1\u00a0\u0111\u1ecbnh l\u1ea1i t\u1ecda \u0111\u1ed9 d\u1eef li\u1ec7u nh\u01b0 sau:<\/p>\n<p style=\"padding-left: 30px\">=OFFSET($B$2,MATCH($F$2,$B$3:$B$22,0),1,COUNTIF($B$3:B$22,$F$2))<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Ket_luan\"><\/span>K\u1ebft lu\u1eadn<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Nh\u01b0 v\u1eady l\u00e0 ch\u00fang ta\u00a0\u0111\u00e3 c\u00f3 th\u1ec3 n\u1eafm\u00a0\u0111\u01b0\u1ee3c c\u00e1c\u00a0\u00e1p d\u1ee5ng h\u00e0m\u00a0OFFSET v\u00e0o vi\u1ec7c t\u1ea1o danh s\u00e1ch\u00a0\u0111\u1ed9ng trong\u00a0Excel r\u1ed3i ph\u1ea3i kh\u00f4ng n\u00e0o. Ch\u1ec9 v\u1edbi m\u1ed9t s\u1ed1 h\u00e0m c\u01a1 b\u1ea3n trong\u00a0Excel nh\u01b0 h\u00e0m MATCH, COUNTA, COUNTIF khi k\u1ebft h\u1ee3p v\u1edbi h\u00e0m\u00a0OFFSET\u00a0\u0111\u00e3 t\u1ea1o ra nh\u1eefng hi\u1ec7u qu\u1ea3 r\u1ea5t\u00a0\u1ea5n t\u01b0\u1ee3ng. C\u00e1c b\u1ea1n h\u00e3y t\u00ecm hi\u1ec3u k\u1ef9 h\u01a1n v\u1ec1 c\u00e1c h\u00e0m trong\u00a0Excel\u00a0\u0111\u1ec3 c\u00f3 th\u00eam nhi\u1ec1u ki\u1ebfn th\u1ee9c b\u1ed5\u00a0\u00edch h\u01a1n n\u1eefa nh\u00e9.<\/p>\n<p style=\"padding-left: 30px\"><a href=\"http:\/\/blog.ezworkapp.com\/ham-offset-va-cac-ket-hop-ham-cua-no-trong-excel-cong-thuc-vi-du.html\">H\u00e0m OFFSET v\u00e0 c\u00e1c k\u1ebft h\u1ee3p h\u00e0m c\u1ee7a n\u00f3 trong Excel, c\u00f4ng th\u1ee9c v\u00ed d\u1ee5<\/a><\/p>\n<p style=\"padding-left: 30px\"><a href=\"http:\/\/blog.ezworkapp.com\/danh-sach-data-validation-phu-thuoc-nhau-trong-excel.html\">T\u1ea1o Drop-down list c\u00f3 gi\u00e1 tr\u1ecb ph\u1ee5 thu\u1ed9c m\u1ed9t list kh\u00e1c<\/a><\/p>\n<p style=\"padding-left: 30px\"><a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-cach-tao-danh-sach-nhan-vien-phu-thuoc-theo-chi-nhanh.html\">H\u01b0\u1edbng d\u1eabn c\u00e1ch t\u1ea1o danh s\u00e1ch nh\u00e2n vi\u00ean ph\u1ee5 thu\u1ed9c theo chi nh\u00e1nh<\/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-362.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":4456,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-4455","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\/4455","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=4455"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/4455\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/4456"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=4455"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=4455"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=4455"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}