{"id":776,"date":"2024-12-02T02:48:25","date_gmt":"2024-12-02T02:48:25","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/indirect-lam-viec-voi-tham-so-mang-tat-nhien-roi\/"},"modified":"2024-12-02T02:48:25","modified_gmt":"2024-12-02T02:48:25","slug":"indirect-lam-viec-voi-tham-so-mang-tat-nhien-roi","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/indirect-lam-viec-voi-tham-so-mang-tat-nhien-roi\/","title":{"rendered":"INDIRECT l\u00e0m vi\u1ec7c v\u1edbi tham s\u1ed1 m\u1ea3ng? T\u1ea5t nhi\u00ean r\u1ed3i"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p>Th\u00f4ng th\u01b0\u1eddng khi ta vi\u1ebft h\u00e0m INDIRECT, ch\u1eb3ng h\u1ea1n nh\u01b0 =INDIRECT(\u201cA1\u201d), h\u00e0m s\u1ebd tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 l\u00e0 gi\u00e1 tr\u1ecb t\u1ea1i \u00f4 A1. \u0110i\u1ec1u n\u00e0y r\u1ea5t d\u1ec5 hi\u1ec3u ph\u1ea3i kh\u00f4ng n\u00e0o? Tuy nhi\u00ean, khi s\u1eed d\u1ee5ng INDIRECT l\u00e0m vi\u1ec7c v\u1edbi tham s\u1ed1 m\u1ea3ng v\u1edbi v\u00ed d\u1ee5 =INDIRECT({\u201cA1\u2033;\u201dA2\u201d}), k\u1ebft qu\u1ea3 tr\u1ea3 v\u1ec1 s\u1ebd l\u00e0 l\u1ed7i #VALUE! R\u1ea5t nhi\u1ec1u ng\u01b0\u1eddi \u0111\u00e3 m\u1eb7c \u0111\u1ecbnh r\u1eb1ng, <em>INDIRECT kh\u00f4ng l\u00e0m vi\u1ec7c \u0111\u01b0\u1ee3c v\u1edbi tham s\u1ed1 m\u1ea3ng<\/em>. Nh\u01b0ng \u0111i\u1ec1u \u0111\u00f3 c\u00f3 ch\u00ednh x\u00e1c kh\u00f4ng, h\u00e3y c\u00f9ng H\u1ecdc Excel Online t\u00ecm hi\u1ec3u trong b\u00e0i vi\u1ebft n\u00e0y 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-674d1ff8af3ae\" 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\/indirect-voi-mang.html#INDIRECT_voi_mang\" title=\"INDIRECT v\u1edbi m\u1ea3ng\">INDIRECT v\u1edbi m\u1ea3ng<\/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\/indirect-voi-mang.html#Mang_doi_tuong\" title=\"M\u1ea3ng \u0111\u1ed1i t\u01b0\u1ee3ng\">M\u1ea3ng \u0111\u1ed1i t\u01b0\u1ee3ng<\/a>\n<ul class=\"ez-toc-list-level-4\">\n<li class=\"ez-toc-heading-level-4\">\n<ul class=\"ez-toc-list-level-4\">\n<li class=\"ez-toc-heading-level-4\"><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/blog.hocexcel.online\/indirect-voi-mang.html#Tim_hieu_them_nhung_bai_viet_lien_quan\" title=\"T\u00ecm hi\u1ec3u th\u00eam nh\u1eefng b\u00e0i vi\u1ebft li\u00ean quan:\">T\u00ecm hi\u1ec3u th\u00eam nh\u1eefng b\u00e0i vi\u1ebft li\u00ean quan:<\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"INDIRECT_voi_mang\"><\/span>INDIRECT v\u1edbi m\u1ea3ng<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>H\u00e3y nh\u00ecn h\u00ecnh d\u01b0\u1edbi \u0111\u1ec3 c\u00f3 m\u1ed9t h\u00ecnh dung c\u1ee5 th\u1ec3 h\u01a1n:<\/p>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"477\" height=\"148\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/image-12.png\" class=\"wp-image-41251\" title=\"image-12\"><figcaption class=\"wp-element-caption\">Indirect tr\u1ea3 v\u1ec1 #VALUE! khi l\u00e0m vi\u1ec7c v\u1edbi tham s\u1ed1 m\u1ea3ng<\/figcaption><\/figure>\n<\/div>\n<p>Tuy nhi\u00ean, n\u1ebfu ta \u0111\u1eb7t th\u00eam m\u1ed9t s\u1ed1 h\u00e0m, ch\u1eb3ng h\u1ea1n nh\u01b0 h\u00e0m CELL v\u1edbi c\u00f4ng th\u1ee9c =CELL(\u201caddress\u201d,INDIRECT({\u201cA1\u2033;\u201dA2\u201d})), ta s\u1ebd nh\u1eadn \u0111\u01b0\u1ee3c k\u1ebft qu\u1ea3 \u0111\u00fang.<\/p>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"588\" height=\"145\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/image-1-4.png\" class=\"wp-image-41252\" title=\"image-1-4\"><figcaption class=\"wp-element-caption\"><em>H\u00e0m CELL s\u1ebd tr\u1ea3 v\u1ec1 th\u00f4ng tin li\u00ean quan t\u1edbi tham chi\u1ebfu nh\u01b0 \u0111\u1ecba ch\u1ec9 \u00f4, n\u1ed9i dung trong \u00f4, \u0111\u1ed9 r\u1ed9ng c\u1ed9t, v\u1ecb tr\u00ed h\u00e0ng\u2026<\/em><\/figcaption><\/figure>\n<\/div>\n<p>T\u1edbi \u0111\u00e2y ch\u1eafc h\u1eb3n nhi\u1ec1u b\u1ea1n s\u1ebd th\u1eafc m\u1eafc t\u1ea1i sao k\u1ebft qu\u1ea3 c\u1ee7a CELL v\u1edbi #VALUE! l\u1ea1i ra \u0111\u01b0\u1ee3c gi\u00e1 tr\u1ecb \u0111\u00fang. \u0110\u1ec3 l\u00e0m r\u00f5 h\u01a1n, ta s\u1ebd \u0111i v\u00e0o t\u1eebng b\u01b0\u1edbc th\u1ef1c thi h\u00e0m INDIRECT.<\/p>\n<p>\u0110\u1ea7u ti\u00ean, khi t\u00ednh to\u00e1n v\u1edbi 1 m\u1ea3ng {\u201cA1\u2033;\u201dA2\u201d}, Excel s\u1ebd truy\u1ec1n v\u00e0 t\u00ednh to\u00e1n t\u1eebng gi\u00e1 tr\u1ecb trong m\u1ea3ng v\u00e0o trong tham s\u1ed1 h\u00e0m. Trong phi\u00ean b\u1ea3n Office 365, v\u1edbi Dynamic array, ta c\u00f3 th\u1ec3 d\u1ec5 d\u00e0ng nh\u00ecn th\u1ea5y k\u1ebft qu\u1ea3 \u0111\u00e3 \u0111\u01b0\u1ee3c t\u1ef1 m\u1edf r\u1ed9ng sang \u00f4 b\u00ean d\u01b0\u1edbi \u2013 n\u00f3i c\u00e1ch kh\u00e1c, h\u00e0m INDIRECT \u0111\u00e3 \u0111\u01b0\u1ee3c th\u1ef1c thi 2 l\u1ea7n bao g\u1ed3m =INDIRECT(\u201cA1\u201d) v\u00e0 =INDIRECT(\u201cA2\u201d).<\/p>\n<p>Tuy nhi\u00ean, k\u1ebft qu\u1ea3 m\u00e0 h\u00e0m INDIRECT tr\u1ea3 v\u1ec1 <strong>kh\u00f4ng<\/strong> ph\u1ea3i l\u00e0 gi\u00e1 tr\u1ecb. Hay n\u00f3i c\u00e1ch kh\u00e1c, vi\u1ec7c tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb l\u00e0 c\u00f4ng vi\u1ec7c c\u1ee7a Excel, c\u00f2n h\u00e0m INDIRECT th\u1ef1c thi c\u00f4ng vi\u1ec7c tr\u1ea3 v\u1ec1 <strong>tham chi\u1ebfu<\/strong> <em>(t\u01b0\u01a1ng t\u1ef1 v\u1edbi h\u00e0m OFFSET s\u1ebd \u0111\u01b0\u1ee3c n\u00f3i \u1edf b\u00ean d\u01b0\u1edbi). <\/em><\/p>\n<p>V\u1eady, m\u1ea3ng m\u00e0 INDIRECT tr\u1ea3 v\u1ec1 tr\u00f4ng ra sao?<\/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-43.gif\" title=\"ex101_92019-nho-43\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Mang_doi_tuong\"><\/span>M\u1ea3ng \u0111\u1ed1i t\u01b0\u1ee3ng<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>B\u1edfi Office kh\u00f4ng ph\u1ea3i l\u00e0 ph\u1ea7n m\u1ec1m m\u00e3 ngu\u1ed3n m\u1edf, n\u00ean kh\u00f4ng c\u00f3 c\u00e1ch n\u00e0o \u0111\u1ec3 bi\u1ebft ch\u00ednh x\u00e1c. Tuy nhi\u00ean, n\u1ebfu bi\u1ebft m\u1ed9t ch\u00fat v\u1ec1 l\u1eadp tr\u00ecnh, \u00edt nhi\u1ec1u ta s\u1ebd nghe t\u1edbi <strong>array <\/strong>v\u00e0 <strong>object<\/strong>. N\u1ebfu ta <strong>coi m\u1ed7i \u00f4 l\u00e0 m\u1ed9t object<\/strong>, th\u00ec k\u1ebft qu\u1ea3 tr\u1ea3 v\u1ec1 c\u1ee7a h\u00e0m =INDIRECT({\u201cA1\u2033;\u201dA2\u201d}) c\u00f3 th\u1ec3 \u0111\u01b0\u1ee3c bi\u1ec3u di\u1ec5n nh\u01b0 sau:<\/p>\n<pre class=\"wp-block-code\"><code>{[object_1];[object_2]}<\/code><\/pre>\n<p>Trong \u0111\u00f3, m\u1ed7i object s\u1ebd bi\u1ec3u th\u1ecb 1 \u00f4 v\u00e0 g\u1ed3m m\u1ed9t v\u00e0i c\u1eb7p thu\u1ed9c t\u00ednh \u2013 gi\u00e1 tr\u1ecb ch\u1eb3ng h\u1ea1n nh\u01b0:<\/p>\n<pre class=\"wp-block-code\"><code>{\n\"address\":$A$1,\n\"contents\":1,\n\"row_num\":1,\n\"row_height\":3,\n...\n}<\/code><\/pre>\n<p>N\u1ebfu ta hi\u1ec3u theo c\u00e1ch n\u00e0y, c\u00f3 th\u1ec3 gi\u1ea3 \u0111\u1ecbnh r\u1eb1ng khi duy\u1ec7t tr\u1ea3 gi\u00e1 tr\u1ecb trong m\u1ea3ng, Excel ch\u1ec9 tr\u1ea3 v\u1ec1 \u0111\u01b0\u1ee3c gi\u00e1 tr\u1ecb n\u1ebfu \u0111\u00f3 l\u00e0 m\u1ed9t m\u1ea3ng gi\u00e1 tr\u1ecb 2 chi\u1ec1u ch\u1ee9 kh\u00f4ng th\u1ec3 tr\u1ea3 v\u1ec1 \u0111\u1ed1i t\u01b0\u1ee3ng (v\u00ec kh\u00f4ng bi\u1ebft nh\u00ecn v\u00e0o thu\u1ed9c t\u00ednh n\u00e0o ch\u0103ng?). V\u00e0 \u0111\u1ed3ng th\u1eddi, trong Excel ta c\u00f3 nh\u1eefng h\u00e0m s\u1ebd \u0111\u1ecdc thu\u1ed9c t\u00ednh \u0111\u1ed1i t\u01b0\u1ee3ng nh\u01b0 h\u00e0m CELL. T\u1eeb suy lu\u1eadn n\u00e0y, ta c\u00f3 th\u00eam m\u1ed9t s\u1ed1 h\u00e0m kh\u00e1c nh\u01b0 h\u00e0m ROW hay COLUMN.<\/p>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"505\" height=\"131\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/image-2-5.png\" class=\"wp-image-41255\" title=\"image-2-5\"><figcaption class=\"wp-element-caption\"><em>C\u00f3 bao gi\u1edd b\u1ea1n th\u1eafc m\u1eafc khi h\u00e0m ROW ho\u1ea1t \u0111\u1ed9ng, l\u00e0m th\u1ebf n\u00e0o \u0111\u1ec3 h\u00e0m n\u00e0y bi\u1ebft \u0111\u01b0\u1ee3c d\u00f2ng bao nhi\u00eau ch\u01b0a?<\/em><\/figcaption><\/figure>\n<\/div>\n<p>M\u1edf r\u1ed9ng b\u00e0i to\u00e1n, ta \u0111\u1ebfn v\u1edbi tr\u01b0\u1eddng h\u1ee3p OFFSET. Theo t\u00e0i li\u1ec7u Microsoft, OFFSET s\u1ebd tr\u1ea3 v\u1ec1 <strong>tham chi\u1ebfu<\/strong> t\u1edbi v\u00f9ng, n\u00ean ta ho\u00e0n to\u00e0n c\u00f3 th\u1ec3 t\u1ea1o ra m\u1ed9t m\u1ea3ng g\u1ed3m nhi\u1ec1u v\u00f9ng v\u00e0 hi\u1ec3n th\u1ecb th\u00f4ng tin b\u1eb1ng vi\u1ec7c \u0111\u1eb7t v\u00e0o trong h\u00e0m CELL.<\/p>\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"477\" height=\"135\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/image-3-5.png\" class=\"wp-image-41256\" title=\"image-3-5\"><\/figure>\n<\/div>\n<p>V\u1eady k\u1ebft lu\u1eadn l\u1ea1i, ta c\u00f3 th\u1ec3 n\u00f3i r\u1eb1ng: <em><strong>INDIRECT c\u00f3 l\u00e0m vi\u1ec7c v\u1edbi tham s\u1ed1 m\u1ea3ng, ch\u1ec9 l\u00e0 Excel kh\u00f4ng hi\u1ec3n th\u1ecb \u0111\u01b0\u1ee3c k\u1ebft qu\u1ea3 nh\u01b0 mong mu\u1ed1n m\u00e0 th\u00f4i<\/strong>.<\/em><\/p>\n<hr class=\"wp-block-separator has-alpha-channel-opacity is-style-wide\">\n<p><em>N\u1ebfu b\u1ea1n mu\u1ed1n h\u1ecdc th\u00eam nh\u1eefng ki\u1ebfn th\u1ee9c v\u1ec1 tin h\u1ecdc v\u0103n ph\u00f2ng c\u0169ng nh\u01b0 l\u1eadp tr\u00ecnh ph\u1ee5c v\u1ee5 c\u00f4ng vi\u1ec7c, h\u00e3y tham kh\u1ea3o \u0111\u01b0\u1eddng link sau: <\/em><a href=\"http:\/\/blog.hocexcel.online\/khoa-hoc?r=2\">C\u00e1c kho\u00e1 h\u1ecdc<\/a><\/p>\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Tim_hieu_them_nhung_bai_viet_lien_quan\"><\/span>T\u00ecm hi\u1ec3u th\u00eam nh\u1eefng b\u00e0i vi\u1ebft li\u00ean quan:<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p>H\u00e0m INDIRECT trong Excel k\u00e8m v\u00ed d\u1ee5: <a href=\"http:\/\/blog.ezworkapp.com\/ham-indirect-trong-excel.html\">https:\/\/blog.hocexcel.online\/ham-indirect-trong-excel.html<\/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-43.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":777,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-776","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\/776","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=776"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/776\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/777"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=776"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=776"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=776"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}