{"id":9955,"date":"2025-01-06T04:20:05","date_gmt":"2025-01-06T04:20:05","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2025\/01\/06\/ham-mang-dong-trong-excel-dynamic-array-formulas-ham-co-kha-nang-mo-rong-vung-ket-qua\/"},"modified":"2025-01-06T04:20:05","modified_gmt":"2025-01-06T04:20:05","slug":"ham-mang-dong-trong-excel-dynamic-array-formulas-ham-co-kha-nang-mo-rong-vung-ket-qua","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2025\/01\/06\/ham-mang-dong-trong-excel-dynamic-array-formulas-ham-co-kha-nang-mo-rong-vung-ket-qua\/","title":{"rendered":"H\u00e0m m\u1ea3ng \u0111\u1ed9ng trong Excel \u2013 Dynamic Array Formulas | H\u00e0m c\u00f3 kh\u1ea3 n\u0103ng m\u1edf r\u1ed9ng v\u00f9ng k\u1ebft qu\u1ea3"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p><strong>Dynamic Array Formulas<\/strong> hay t\u00ean ti\u1ebfng Vi\u1ec7t l\u00e0 <strong>h\u00e0m m\u1ea3ng \u0111\u1ed9ng<\/strong> \u0111\u01b0\u1ee3c gi\u1edbi thi\u1ec7u trong b\u1ea3n Public Preview c\u1ee7a Excel v\u00e0o ng\u00e0y 24 th\u00e1ng 9 n\u0103m 2018 m\u1edbi \u0111\u00e2y s\u1ebd c\u00f3 m\u1eb7t trong b\u1ea3n Office 365 ti\u1ebfp theo. B\u1ea1n \u0111ang t\u00f2 m\u00f2 h\u00e0m m\u1ea3ng \u0111\u1ed9ng l\u00e0 h\u00e0m g\u00ec, th\u00ec trong b\u00e0i vi\u1ebft n\u00e0y H\u1ecdc Excel Online s\u1ebd b\u1eadt m\u00ed cho b\u1ea1n \u0111i\u1ec1u n\u00e0y. H\u00e0m m\u1ea3ng \u0111\u1ed9ng bao g\u1ed3m nh\u1eefng h\u00e0m Excel nh\u01b0 SORT, SORTBY, FILTER, UNIQUE, SEQUENCE v\u00e0 RANDARRAY. B\u00ecnh th\u01b0\u1eddng, khi b\u1ea1n \u0111i\u1ec1n c\u00f4ng th\u1ee9c v\u00e0o 1 \u00f4 trong b\u1ea3ng t\u00ednh Excel, th\u00ec c\u00f4ng th\u1ee9c ch\u1ec9 c\u00f3 t\u00e1c d\u1ee5ng \u0111i\u1ec1n d\u1eef li\u1ec7u v\u00e0o ch\u00ednh \u00f4 \u0111\u00f3 m\u00e0 th\u00f4i tr\u1eeb khi b\u1ea1n s\u1eed d\u1ee5ng 1 addin kh\u00e1c nh\u01b0\u00a0<a href=\"http:\/\/blog.ezworkapp.com\/ham-arrayformula-trong-excel-cua-hoc-excel-online.html\" target=\"_blank\" rel=\"noopener noreferrer\">H\u00e0m HEO_ARRAYFORMULA t\u1ef1 resize k\u1ebft qu\u1ea3<\/a>. V\u1edbi s\u1ef1 n\u00e2ng c\u1ea5p \u0111\u00e1ng k\u1ec3 n\u00e0y th\u00ec Microsoft \u0111\u00e3 t\u0103ng s\u1ee9c m\u1ea1nh c\u1ee7a Excel l\u00ean nhi\u1ec1u l\u1ea7n v\u00e0 m\u1edf r\u1ed9ng kh\u1ea3 n\u0103ng \u1ee9ng d\u1ee5ng c\u1ee7a h\u00e0m Excel v\u00e0o nh\u1eefng v\u1ea5n \u0111\u1ec1 m\u00e0 tr\u01b0\u1edbc kia ch\u1ec9 c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng VBA \u0111\u1ec3 can thi\u1ec7p. Gi\u1edd \u0111\u00e2y, ngay trong Excel, b\u1ea1n c\u00f3 th\u1ec3 s\u1eafp x\u1ebfp, l\u1ecdc, t\u00ecm gi\u00e1 tr\u1ecb duy nh\u1ea5t ho\u00e0n to\u00e0n b\u1eb1ng c\u00f4ng th\u1ee9c Excel.<\/p>\n<p>OK, nghe v\u1eady l\u00e0 th\u1ea5y th\u00edch r\u1ed3i. B\u1ea1n s\u1ebd c\u00f3 c\u00e2u h\u1ecfi r\u1eb1ng, t\u00f4i \u0111ang d\u00f9ng Excel 2016, ho\u1eb7c th\u1eadm ch\u00ed Excel 2019 th\u00ec li\u1ec7u b\u1ea1n c\u00f3 th\u1ec3 d\u00f9ng nh\u1eefng h\u00e0m n\u00e0y hay kh\u00f4ng? C\u00e2u tr\u1ea3 l\u1eddi l\u00e0, hi\u1ec7n nay, tr\u1eeb khi b\u1ea1n s\u1eed d\u1ee5ng Excel c\u1ee7a Office 365 th\u00ec b\u1ea1n m\u1edbi c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng nh\u1eefng t\u00ednh n\u0103ng n\u00e0y. N\u1ebfu c\u01a1 quan c\u1ee7a b\u1ea1n s\u1eed d\u1ee5ng b\u1ed9 Office 365, th\u00ec may m\u1eafn cho b\u1ea1n. N\u1ebfu b\u1ea1n mua Office 2016 b\u1ea3n standalone, th\u00ec b\u1ea1n kh\u00f4ng th\u1ec3 s\u1eed d\u1ee5ng c\u00e1c t\u00ednh n\u0103ng m\u1edbi n\u00e0y.<\/p>\n<p>Serie video v\u1ec1 h\u00e0m m\u1ea3ng \u0111\u1ed9ng trong Excel:<\/p>\n<iframe loading=\"lazy\" src=\"https:\/\/www.youtube.com\/embed\/GH_KMTowo6U\" width=\"560\" height=\"314\" allowfullscreen=\"allowfullscreen\"><\/iframe>\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-677b59f4578cd\" 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-mang-dong-trong-excel-dynamic-array-formulas-phan-1-ham-co-kha-nang-mo-rong-vung-du-lieu.html#Kha_nang_tu_mo_rong_resize_mang_ket_qua_cua_Excel\" title=\"Kh\u1ea3 n\u0103ng t\u1ef1 m\u1edf r\u1ed9ng \/ resize m\u1ea3ng k\u1ebft qu\u1ea3 c\u1ee7a Excel\">Kh\u1ea3 n\u0103ng t\u1ef1 m\u1edf r\u1ed9ng \/ resize m\u1ea3ng k\u1ebft qu\u1ea3 c\u1ee7a 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-mang-dong-trong-excel-dynamic-array-formulas-phan-1-ham-co-kha-nang-mo-rong-vung-du-lieu.html#Loi_SPILL_khi_co_du_lieu_nam_trong_vung_mo_rong_cua_cong_thuc\" title=\"L\u1ed7i #SPILL! khi c\u00f3 d\u1eef li\u1ec7u n\u1eb1m trong v\u00f9ng m\u1edf r\u1ed9ng c\u1ee7a c\u00f4ng th\u1ee9c\">L\u1ed7i #SPILL! khi c\u00f3 d\u1eef li\u1ec7u n\u1eb1m trong v\u00f9ng m\u1edf r\u1ed9ng c\u1ee7a c\u00f4ng th\u1ee9c<\/a><\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h3><span class=\"ez-toc-section\" id=\"Kha_nang_tu_mo_rong_resize_mang_ket_qua_cua_Excel\"><\/span>Kh\u1ea3 n\u0103ng t\u1ef1 m\u1edf r\u1ed9ng \/ resize m\u1ea3ng k\u1ebft qu\u1ea3 c\u1ee7a Excel<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Gi\u1ea3 s\u1eed b\u1ea1n \u0111i\u1ec1n c\u00f4ng th\u1ee9c <strong>=A2:C10\u00a0<\/strong>trong \u00f4\u00a0<strong>E3.\u00a0<\/strong>Tr\u01b0\u1edbc kia, k\u1ebft qu\u1ea3 b\u1ea1n nh\u1eadn \u0111\u01b0\u1ee3c s\u1ebd l\u00e0 l\u1ed7i <strong>#VALUE!\u00a0<\/strong>n\u1ebfu b\u1ea1n nh\u1eadp c\u00f4ng th\u1ee9c 1 c\u00e1ch b\u00ecnh th\u01b0\u1eddng b\u1eb1ng c\u00e1ch b\u1ea5m n\u00fat Enter tr\u00ean b\u00e0n ph\u00edm. Trong tr\u01b0\u1eddng h\u1ee3p, b\u1ea1n nh\u1eadp c\u00f4ng th\u1ee9c \u0111\u00f3 v\u1edbi t\u1ed5 h\u1ee3p ph\u00edm CTRL + SHIFT + ENTER \u0111\u1ec3 nh\u1eadp v\u00e0o\u00a0<a href=\"http:\/\/blog.ezworkapp.com\/ung-dung-excel-tim-hieu-ve-cong-thuc-mang-trong-excel.html\" target=\"_blank\" rel=\"noopener noreferrer\">m\u1ed9t c\u00f4ng th\u1ee9c m\u1ea3ng<\/a>\u00a0th\u00ec k\u1ebft qu\u1ea3 nh\u1eadn \u0111\u01b0\u1ee3c s\u1ebd l\u00e0 East v\u00e0 c\u00f4ng th\u1ee9c \u1edf E3 s\u1ebd c\u00f3 d\u1ea1ng <strong>{=A2:C10}<\/strong>. C\u00f4ng th\u1ee9c \u0111\u00f3 s\u1ebd ch\u1ec9 c\u00f3 t\u00e1c \u0111\u1ed9ng t\u1edbi \u00f4 E3,<\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2018\/10\/01-implicit-intersection.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-22643\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/01-implicit-intersection.png\" alt=\"01-implicit-intersection\" width=\"746\" height=\"429\" title=\"01-implicit-intersection\"><\/a><\/p>\n<p>Trong phi\u00ean b\u1ea3n ti\u1ebfp theo c\u1ee7a Excel 365, khi b\u1ea1n nh\u1eadp c\u00f4ng th\u1ee9c <strong>=A2:C10\u00a0<\/strong>v\u00e0o E3 v\u00e0 ch\u1ec9 b\u1ea5m Enter, th\u00ec \u0111\u00e2y l\u00e0 k\u1ebft qu\u1ea3 b\u1ea1n s\u1ebd nh\u1eadn \u0111\u01b0\u1ee3c<\/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-114.gif\" title=\"ex101_92019-nho-114\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2018\/10\/02-implicit-intersection.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-22645\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/02-implicit-intersection.png\" alt=\"02-implicit-intersection\" width=\"573\" height=\"474\" title=\"02-implicit-intersection\"><\/a><\/p>\n<p>Excel trong phi\u00ean b\u1ea3n n\u00e0y \u0111\u00e3 kh\u00e1c tr\u01b0\u1edbc: k\u1ebft qu\u1ea3 \u0111\u01b0\u1ee3c ghi sang c\u1ea3 nh\u1eefng \u00f4 l\u00e2n c\u1eadn v\u1edbi \u00f4 m\u00e0 b\u1ea1n nh\u1eadp c\u00f4ng th\u1ee9c v\u00e0o. C\u00f4ng th\u1ee9c \u0111\u01b0\u1ee3c nh\u1eadp v\u00e0o s\u1ebd kh\u00f4ng ph\u1ea3i l\u00e0 1 c\u00f4ng th\u1ee9c m\u1ea3ng. N\u1ebfu b\u1ea1n ch\u1ecdn b\u1ea5t c\u1ee9 \u00f4 n\u00e0o kh\u00e1c trong v\u00f9ng k\u1ebft qu\u1ea3, th\u00ec b\u1ea1n c\u0169ng s\u1ebd th\u1ea5y \u0111\u01b0\u1ee3c r\u1eb1ng k\u1ebft qu\u1ea3 n\u00e0y \u0111\u1ec1u th\u1ed1ng nh\u1ea5t l\u00e0 <strong>A2:C10\u00a0<\/strong>\u0111\u01b0\u1ee3c in m\u00e0u ghi nh\u1ea1t v\u00e0 kh\u00f4ng c\u00f3 ngo\u1eb7c nh\u1ecdn <strong>{}\u00a0<\/strong><\/p>\n<p>K\u1ebft qu\u1ea3 c\u1ee5 th\u1ec3 tr\u00f4ng s\u1ebd nh\u01b0 sau:<\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2018\/10\/Dynamic-Arrays-trong-Excel.gif\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-23179\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/dynamic-arrays-trong-excel.gif\" alt=\"Dynamic Arrays trong Excel\" width=\"664\" height=\"354\" title=\"dynamic-arrays-trong-excel\"><\/a><\/p>\n<p>C\u00e1c ch\u1ee9c n\u0103ng nh\u01b0 s\u1eafp x\u1ebfp, l\u1ea5y k\u1ebft qu\u1ea3 duy nh\u1ea5t ho\u1eb7c l\u1ecdc d\u1eef li\u1ec7u gi\u1edd ch\u00fang ta c\u00f3 th\u1ec3 th\u1ef1c hi\u1ec7n b\u1eb1ng h\u00e0m Excel th\u1eadt \u0111\u01a1n gi\u1ea3n v\u00e0 m\u1edf ra kh\u1ea3 n\u0103ng s\u1eed d\u1ee5ng Excel theo c\u00e1ch ho\u00e0n to\u00e0n kh\u00e1c.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Loi_SPILL_khi_co_du_lieu_nam_trong_vung_mo_rong_cua_cong_thuc\"><\/span>L\u1ed7i #SPILL! khi c\u00f3 d\u1eef li\u1ec7u n\u1eb1m trong v\u00f9ng m\u1edf r\u1ed9ng c\u1ee7a c\u00f4ng th\u1ee9c<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Trong phi\u00ean b\u1ea3n Excel v\u1edbi ki\u1ec3u c\u00f4ng th\u1ee9c m\u1ea3ng m\u1edbi n\u00e0y, 1 l\u1ed7i m\u1edbi \u0111\u01b0\u1ee3c gi\u1edbi thi\u1ec7u t\u1edbi ng\u01b0\u1eddi s\u1eed d\u1ee5ng, \u0111\u00f3 l\u00e0 l\u1ed7i <strong>#SPILL!.\u00a0<\/strong>L\u1ed7i n\u00e0y x\u1ea3y ra trong tr\u01b0\u1eddng h\u1ee3p c\u00f3 d\u1eef li\u1ec7u trong ph\u1ea1m vi m\u1edf r\u1ed9ng (Spill Range) c\u1ee7a c\u00f4ng th\u1ee9c m\u1ea3ng ki\u1ec3u m\u1edbi. C\u1ee5 th\u1ec3 nh\u01b0 trong h\u00ecnh minh h\u1ecda<\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2018\/10\/03-loi-spill-trong-excel.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-22646\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/03-loi-spill-trong-excel.png\" alt=\"03-loi-spill-trong-excel\" width=\"755\" height=\"487\" title=\"03-loi-spill-trong-excel\"><\/a><\/p>\n<p>C\u00e1ch kh\u1eafc ph\u1ee5c l\u1ed7i n\u00e0y: x\u00f3a d\u1eef li\u1ec7u n\u1eb1m trong v\u00f9ng m\u1edf r\u1ed9ng (Spill Range) c\u1ee7a c\u00f4ng th\u1ee9c m\u1ea3ng \u0111\u1ed9ng.<\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2018\/10\/xu-ly-loi-spill-trong-excel.gif\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-23180\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/xu-ly-loi-spill-trong-excel.gif\" alt=\"xu ly loi spill trong excel\" width=\"796\" height=\"422\" title=\"xu-ly-loi-spill-trong-excel\"><\/a><\/p>\n<p>Nh\u01b0 v\u1eady l\u00e0 trong b\u00e0i n\u00e0y, b\u1ea1n c\u00f3 th\u1ec3 c\u00f3 c\u00e1i nh\u00ecn \u0111\u1ea7u ti\u00ean v\u00e0o nh\u1eefng g\u00ec phi\u00ean b\u1ea3n Office 365 t\u1edbi s\u1ebd c\u00f3 th\u1ec3 l\u00e0m \u0111\u01b0\u1ee3c cho b\u1ea1n. N\u1ebfu b\u1ea1n c\u00f3 ki\u1ebfn th\u1ee9c Google Sheet, th\u00ec nh\u1eefng h\u00e0m n\u00e0y \u0111\u00e3 t\u1ed3n t\u1ea1i trong Google Sheet t\u1eeb l\u00e2u. Trong n\u0103m 2019, ch\u00fang ta s\u1ebd ch\u1edd \u0111\u00f3n nh\u1eefng h\u00e0m n\u00e0y \u0111\u1ebfn v\u1edbi Excel 365. Ch\u00fac th\u00e0nh c\u00f4ng.<\/p>\n<p>Ngu\u1ed3n<br \/>\n[1] Excel Dynamics Array Straight To The Point \u2013 Bill Jelen.<\/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-114.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":9956,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-9955","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\/9955","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=9955"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/9955\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/9956"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=9955"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=9955"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=9955"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}