{"id":1982,"date":"2024-12-02T06:40:45","date_gmt":"2024-12-02T06:40:45","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/6-tinh-nang-hieu-qua-nhat-cua-ham-index-trong-excel\/"},"modified":"2024-12-02T06:40:45","modified_gmt":"2024-12-02T06:40:45","slug":"6-tinh-nang-hieu-qua-nhat-cua-ham-index-trong-excel","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/6-tinh-nang-hieu-qua-nhat-cua-ham-index-trong-excel\/","title":{"rendered":"6 t\u00ednh n\u0103ng hi\u1ec7u qu\u1ea3 nh\u1ea5t c\u1ee7a h\u00e0m INDEX trong Excel"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p style=\"text-align: justify\"><em>Trong b\u00e0i vi\u1ebft n\u00e0y, <strong><a href=\"http:\/\/blog.ezworkapp.com\" target=\"_blank\" rel=\"noopener noreferrer\">H\u1ecdc Excel Online<\/a><\/strong> s\u1ebd l\u1ea5y v\u00ed d\u1ee5 v\u1ec1 <a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-cach-dung-ham-index-va-match-qua-bai-tap-co-loi-giai.html\"><strong>h\u00e0m INDEX<\/strong><\/a> trong Excel, qua \u0111\u00f3 m\u00f4 t\u1ea3 cho b\u1ea1n nh\u1eefng c\u00e1ch s\u1eed d\u1ee5ng hi\u1ec7u qu\u1ea3 c\u00f4ng c\u1ee5 n\u00e0y.<\/em><\/p>\n<p style=\"text-align: justify\">Trong t\u1ea5t c\u1ea3 c\u00e1c ch\u1ee9c n\u0103ng c\u1ee7a Excel m\u00e0 s\u1ee9c m\u1ea1nh c\u1ee7a ch\u00fang th\u01b0\u1eddng b\u1ecb \u0111\u00e1nh gi\u00e1 th\u1ea5p v\u00e0 kh\u00f4ng \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng \u0111\u00fang c\u00e1ch, th\u00ec <strong><em>INDEX<\/em><\/strong> ch\u1eafc ch\u1eafn s\u1ebd x\u1ebfp h\u1ea1ng \u1edf \u0111\u00e2u \u0111\u00f3 trong top 10. B\u1edfi h\u00e0m n\u00e0y r\u1ea5t nh\u1ea1y v\u00e0 c\u00f3 th\u1ec3 k\u1ebft h\u1ee3p v\u1edbi c\u00e1c h\u00e0m kh\u00e1c n\u1eefa \u0111\u1ec3 gi\u1ea3i quy\u1ebft \u0111\u01b0\u1ee3c nhi\u1ec1u v\u1ea5n \u0111\u1ec1 \u0111\u1eb7t ra.<\/p>\n<p style=\"text-align: justify\">V\u1eady h\u00e0m <strong><em>INDEX<\/em><\/strong> trong Excel l\u00e0 g\u00ec? V\u1ec1 c\u01a1 b\u1ea3n, m\u1ed9t h\u00e0m <strong><em>INDEX<\/em><\/strong> s\u1ebd tr\u1ea3 v\u1ec1 m\u1ed9t \u00f4 tham chi\u1ebfu trong m\u1ed9t m\u1ea3ng ho\u1eb7c m\u1ed9t d\u1ea3i nh\u1ea5t \u0111\u1ecbnh. N\u00f3i c\u00e1ch kh\u00e1c, b\u1ea1n s\u1eed d\u1ee5ng <strong><em>INDEX<\/em><\/strong> khi b\u1ea1n bi\u1ebft (ho\u1eb7c c\u00f3 th\u1ec3 t\u00ednh to\u00e1n) v\u1ecb tr\u00ed c\u1ee7a m\u1ed9t ph\u1ea7n t\u1eed trong d\u1ea3i v\u00e0 b\u1ea1n mu\u1ed1n nh\u1eadn gi\u00e1 tr\u1ecb th\u1ef1c c\u1ee7a ph\u1ea7n t\u1eed \u0111\u00f3.<\/p>\n<p style=\"text-align: justify\">\u0110i\u1ec1u n\u00e0y nghe c\u00f3 v\u1ebb t\u1ea7m th\u01b0\u1eddng, nh\u01b0ng khi b\u1ea1n nh\u1eadn ra ti\u1ec1m n\u0103ng th\u1ef1c s\u1ef1 c\u1ee7a h\u00e0m INDEX, th\u00ec n\u00f3 c\u00f3 th\u1ec3 t\u1ea1o n\u00ean nh\u1eefng thay \u0111\u1ed5i quan tr\u1ecdng \u0111\u1ed1i v\u1edbi c\u00e1ch m\u00e0 b\u1ea1n t\u00ednh to\u00e1n, ph\u00e2n t\u00edch v\u00e0 tr\u00ecnh b\u00e0y d\u1eef li\u1ec7u trong c\u00e1c b\u1ea3ng t\u00ednh c\u1ee7a b\u1ea1n.<\/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-674d566c05b7b\" 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\/6-tinh-nang-hieu-qua-nhat-cua-ham-index-trong-excel.html#Ham_INDEX_%E2%80%93_cu_phap_va_su_dung_co_ban\" title=\"H\u00e0m INDEX \u2013 c\u00fa ph\u00e1p v\u00e0 s\u1eed d\u1ee5ng c\u01a1 b\u1ea3n\">H\u00e0m INDEX \u2013 c\u00fa ph\u00e1p v\u00e0 s\u1eed d\u1ee5ng c\u01a1 b\u1ea3n<\/a>\n<ul class=\"ez-toc-list-level-3\">\n<li class=\"ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/blog.hocexcel.online\/6-tinh-nang-hieu-qua-nhat-cua-ham-index-trong-excel.html#INDEX_lam_viec_voi_mang\" title=\"INDEX \u00a0l\u00e0m vi\u1ec7c v\u1edbi m\u1ea3ng\">INDEX \u00a0l\u00e0m vi\u1ec7c v\u1edbi m\u1ea3ng<\/a>\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\/6-tinh-nang-hieu-qua-nhat-cua-ham-index-trong-excel.html#Cau_truc_ham\" title=\"C\u1ea5u\u00a0tr\u00fac h\u00e0m\">C\u1ea5u\u00a0tr\u00fac h\u00e0m<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-4\"><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/blog.hocexcel.online\/6-tinh-nang-hieu-qua-nhat-cua-ham-index-trong-excel.html#Dang_mang_va_INDEX_%E2%80%93_dieu_can_nho\" title=\"D\u1ea1ng m\u1ea3ng v\u00e0 INDEX \u2013 \u0111i\u1ec1u c\u1ea7n nh\u1edb\">D\u1ea1ng m\u1ea3ng v\u00e0 INDEX \u2013 \u0111i\u1ec1u c\u1ea7n nh\u1edb<\/a><\/li>\n<\/ul>\n<\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/blog.hocexcel.online\/6-tinh-nang-hieu-qua-nhat-cua-ham-index-trong-excel.html#Dinh_dang_tham_chieu_INDEX\" title=\"\u0110\u1ecbnh d\u1ea1ng tham chi\u1ebfu INDEX\">\u0110\u1ecbnh d\u1ea1ng tham chi\u1ebfu INDEX<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/blog.hocexcel.online\/6-tinh-nang-hieu-qua-nhat-cua-ham-index-trong-excel.html#Cau_truc_ham-2\" title=\"C\u1ea5u\u00a0tr\u00fac h\u00e0m\">C\u1ea5u\u00a0tr\u00fac h\u00e0m<\/a>\n<ul class=\"ez-toc-list-level-4\">\n<li class=\"ez-toc-heading-level-4\"><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/blog.hocexcel.online\/6-tinh-nang-hieu-qua-nhat-cua-ham-index-trong-excel.html#Dinh_dang_tham_chieu_INDEX_%E2%80%93_nhung_dieu_can_nho\" title=\"\u0110\u1ecbnh d\u1ea1ng tham chi\u1ebfu INDEX \u2013 nh\u1eefng \u0111i\u1ec1u c\u1ea7n nh\u1edb\">\u0110\u1ecbnh d\u1ea1ng tham chi\u1ebfu INDEX \u2013 nh\u1eefng \u0111i\u1ec1u c\u1ea7n nh\u1edb<\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/blog.hocexcel.online\/6-tinh-nang-hieu-qua-nhat-cua-ham-index-trong-excel.html#Cach_dung_ham_INDEX_trong_Excel-_vi_du_cong_thuc\" title=\"C\u00e1ch d\u00f9ng h\u00e0m INDEX trong Excel- v\u00ed d\u1ee5, c\u00f4ng th\u1ee9c\">C\u00e1ch d\u00f9ng h\u00e0m INDEX trong Excel- v\u00ed d\u1ee5, c\u00f4ng th\u1ee9c<\/a><\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"Ham_INDEX_%E2%80%93_cu_phap_va_su_dung_co_ban\"><\/span><strong>H\u00e0m <em>INDEX<\/em> \u2013 c\u00fa ph\u00e1p v\u00e0 s\u1eed d\u1ee5ng c\u01a1 b\u1ea3n<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p style=\"text-align: justify\">C\u00f3 hai phi\u00ean b\u1ea3n c\u1ee7a h\u00e0m INDEX trong Excel \u2013 \u0111\u1ecbnh d\u1ea1ng d\u1ea3i v\u00e0 \u0111\u1ecbnh d\u1ea1ng tham chi\u1ebfu. V\u00e0 c\u1ea3 hai m\u1eabu \u0111\u1ec1u c\u00f3 th\u1ec3 \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng trong t\u1ea5t c\u1ea3 c\u00e1c phi\u00ean b\u1ea3n c\u1ee7a Microsoft Excel 2013, Excel 2010, Excel 2007 v\u00e0 2003.<\/p>\n<blockquote>\n<p style=\"text-align: justify\">Xem ngay: T\u00e0i li\u1ec7u\u00a0<a href=\"http:\/\/blog.ezworkapp.com\/tag\/hoc-excel-2003\">h\u01b0\u1edbng d\u1eabn s\u1eed d\u1ee5ng excel 2003<\/a><\/p>\n<\/blockquote>\n<h3 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"INDEX_lam_viec_voi_mang\"><\/span><strong><em>INDEX<\/em> \u00a0l\u00e0m vi\u1ec7c v\u1edbi m\u1ea3ng<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p style=\"text-align: justify\">D\u1ea1ng m\u1ea3ng INDEX tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb c\u1ee7a m\u1ed9t ph\u1ea7n t\u1eed trong m\u1ed9t b\u1ea3ng ho\u1eb7c m\u1ed9t m\u1ea3ng d\u1ef1a tr\u00ean s\u1ed1 h\u00e0ng v\u00e0 c\u1ed9t m\u00e0 b\u1ea1n ch\u1ec9 \u0111\u1ecbnh.<\/p>\n<h4 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"Cau_truc_ham\"><\/span>C\u1ea5u\u00a0tr\u00fac h\u00e0m<span class=\"ez-toc-section-end\"><\/span><\/h4>\n<p style=\"text-align: justify\"><strong><em>INDEX (array, row_num, [column_num])<\/em><\/strong><\/p>\n<div class=\"code-block code-block-2\" style=\"margin: 8px 0;clear: both\">\n<div class=\"ad-inserterpro\">\n<a href=\"https:\/\/hocexcel.online\/course\/preview\/vba101-tu-dong-hoa-excel-voi-lap-trinh-vba-cho-nguoi-moi-bat-dau\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vba101_92019-nho-61.gif\" title=\"vba101_92019-nho-61\"><br \/>\n<\/a>\n<\/div>\n<\/div>\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-147.gif\" title=\"ex101_92019-nho-147\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<ul style=\"text-align: justify\">\n<li><strong><em> array<\/em><\/strong> \u2013 l\u00e0 m\u1ed9t d\u00e3i c\u00e1c \u00f4, c\u00f3 t\u00ean d\u1ea3i ho\u1eb7c b\u1ea3ng.<\/li>\n<li><strong><em> row_num<\/em><\/strong> \u2013 l\u00e0 h\u00e0ng th\u1ee9 m\u1ea5y trong m\u1ea3ng m\u00e0 b\u1ea1n mu\u1ed1n \u0111\u01b0\u1ee3c tr\u1ea3 v\u1ec1 m\u1ed9t gi\u00e1 tr\u1ecb. N\u1ebfu row_num b\u1ecb b\u1ecf qua, column_num l\u00e0 b\u1eaft bu\u1ed9c.<\/li>\n<li><strong><em> column_num<\/em><\/strong> \u2013 l\u00e0 c\u1ed9t th\u1ee9 m\u1ea5y \u0111\u1ec3 tr\u1ea3 v\u1ec1 m\u1ed9t gi\u00e1 tr\u1ecb. N\u1ebfu column_num b\u1ecb b\u1ecf qua, row_num l\u00e0 b\u1eaft bu\u1ed9c.<\/li>\n<\/ul>\n<p style=\"text-align: justify\">V\u00ed d\u1ee5, c\u00f4ng th\u1ee9c <strong><em>\u201c= INDEX (A1: D6, 4, 3)\u201d<\/em><\/strong> s\u1ebd tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb t\u1ea1i giao \u0111i\u1ec3m c\u1ee7a h\u00e0ng th\u1ee9 4 v\u00e0 c\u1ed9t th\u1ee9 3 trong m\u1ea3ng A1: D6, l\u00e0 gi\u00e1 tr\u1ecb trong \u00f4 C4.<\/p>\n<p style=\"text-align: justify\">\u0110\u1ec3 c\u00f3 \u00fd t\u01b0\u1edfng v\u1ec1 c\u00e1ch <strong><em>h\u00e0m INDEX<\/em><\/strong> ho\u1ea1t \u0111\u1ed9ng tr\u00ean d\u1eef li\u1ec7u th\u1ef1c, h\u00e3y xem v\u00ed d\u1ee5 sau:<\/p>\n<p style=\"text-align: justify\"><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-index-array-form.png\" alt=\"An example of the INDEX array form\" title=\"excel-index-array-form\"><\/p>\n<p style=\"text-align: justify\">Thay v\u00ec nh\u1eadp s\u1ed1 h\u00e0ng v\u00e0 c\u1ed9t trong c\u00f4ng th\u1ee9c, b\u1ea1n c\u00f3 th\u1ec3 cung c\u1ea5p tham chi\u1ebfu \u00f4 \u0111\u1ec3 c\u00f3 \u0111\u01b0\u1ee3c c\u00f4ng th\u1ee9c: \u201c= <strong><em>INDEX ($B$2:$D$6,G2,G1)\u201d<\/em><\/strong><\/p>\n<p style=\"text-align: justify\">L\u01b0u \u00fd: Vi\u1ec7c s\u1eed d\u1ee5ng t\u00e0i li\u1ec7u tham kh\u1ea3o tuy\u1ec7t \u0111\u1ed1i ($B$2:$D$6) thay v\u00ec tham chi\u1ebfu t\u01b0\u01a1ng \u0111\u1ed1i (B2:D6) trong \u0111\u1ed1i s\u1ed1 m\u1ea3ng gi\u00fap b\u1ea1n d\u1ec5 d\u00e0ng sao ch\u00e9p c\u00f4ng th\u1ee9c l\u00ean c\u00e1c \u00f4 kh\u00e1c. Ngo\u00e0i ra, b\u1ea1n c\u00f3 th\u1ec3 chuy\u1ec3n \u0111\u1ed5i m\u1ed9t d\u1ea3i th\u00e0nh m\u1ed9t b\u1ea3ng <strong>(Ctrl + T)<\/strong> v\u00e0 tham kh\u1ea3o n\u00f3 b\u1eb1ng t\u00ean b\u1ea3ng.<\/p>\n<h4 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"Dang_mang_va_INDEX_%E2%80%93_dieu_can_nho\"><\/span><strong>D\u1ea1ng m\u1ea3ng v\u00e0 <em>INDEX<\/em> \u2013 \u0111i\u1ec1u c\u1ea7n nh\u1edb<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h4>\n<ol style=\"text-align: justify\">\n<li>N\u1ebfu \u0111\u1ed1i s\u1ed1 m\u1ea3ng ch\u1ec9 bao g\u1ed3m m\u1ed9t h\u00e0ng ho\u1eb7c c\u1ed9t, th\u00ec b\u1ea1n c\u00f3 th\u1ec3 x\u00e1c \u0111\u1ecbnh ho\u1eb7c kh\u00f4ng x\u00e1c \u0111\u1ecbnh \u0111\u1ed1i s\u1ed1 <strong>row_num<\/strong> ho\u1eb7c <strong><em>column_num<\/em><\/strong> t\u01b0\u01a1ng \u1ee9ng.<\/li>\n<li>N\u1ebfu \u0111\u1ed1i s\u1ed1 m\u1ea3ng bao g\u1ed3m nhi\u1ec1u h\u01a1n m\u1ed9t d\u00f2ng v\u00e0 <strong><em>row_num<\/em><\/strong> b\u1ecb b\u1ecf qua ho\u1eb7c b\u1eb1ng 0, th\u00ec h\u00e0m INDEX tr\u1ea3 v\u1ec1 m\u1ed9t m\u1ea3ng c\u1ee7a to\u00e0n b\u1ed9 c\u1ed9t. T\u01b0\u01a1ng t\u1ef1, n\u1ebfu m\u1ea3ng bao g\u1ed3m nhi\u1ec1u c\u1ed9t v\u00e0 \u0111\u1ed1i s\u1ed1 <strong><em>column_num<\/em><\/strong> b\u1ecb b\u1ecf qua ho\u1eb7c b\u1eb1ng 0, c\u00f4ng th\u1ee9c <strong><em>INDEX<\/em><\/strong> s\u1ebd tr\u1ea3 v\u1ec1 to\u00e0n b\u1ed9 h\u00e0ng. D\u01b0\u1edbi \u0111\u00e2y l\u00e0 m\u1ed9t v\u00ed d\u1ee5 v\u1ec1 c\u00f4ng th\u1ee9c minh h\u1ecda.<\/li>\n<li>C\u00e1c \u0111\u1ed1i s\u1ed1 row_num v\u00e0 column_num ph\u1ea3i tham chi\u1ebfu \u0111\u1ebfn m\u1ed9t \u00f4 trong m\u1ea3ng; N\u1ebfu kh\u00f4ng, c\u00f4ng th\u1ee9c INDEX s\u1ebd tr\u1ea3 v\u1ec1 <strong><em>#REF!<\/em><\/strong> l\u1ed7i.<\/li>\n<\/ol>\n<h3 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"Dinh_dang_tham_chieu_INDEX\"><\/span><strong>\u0110\u1ecbnh d\u1ea1ng tham chi\u1ebfu <em>INDEX<\/em><\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p style=\"text-align: justify\">\u0110\u1ecbnh d\u1ea1ng tham chi\u1ebfu c\u1ee7a <strong><em>h\u00e0m INDEX<\/em><\/strong> tr\u1ea3 v\u1ec1 \u00f4 tham chi\u1ebfu t\u1ea1i giao \u0111i\u1ec3m c\u1ee7a h\u00e0ng v\u00e0 c\u1ed9t \u0111\u00e3 x\u00e1c \u0111\u1ecbnh.<\/p>\n<h3 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"Cau_truc_ham-2\"><\/span>C\u1ea5u\u00a0tr\u00fac h\u00e0m<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p style=\"text-align: justify\"><strong><em>INDEX (reference, row_num, [column_num], [area_num])<\/em><\/strong><\/p>\n<ul style=\"text-align: justify\">\n<li><strong><em> reference<\/em><\/strong> \u2013 l\u00e0 m\u1ed9t ho\u1eb7c nhi\u1ec1u d\u1ea3i.<\/li>\n<\/ul>\n<p style=\"text-align: justify\">N\u1ebfu b\u1ea1n nh\u1eadp nhi\u1ec1u h\u01a1n m\u1ed9t ph\u1ea1m vi, h\u00e3y t\u00e1ch c\u00e1c kho\u1ea3ng b\u1eb1ng d\u1ea5u ph\u1ea9y v\u00e0 k\u00e8m theo \u0111\u1ed1i s\u1ed1 tham chi\u1ebfu trong ngo\u1eb7c \u0111\u01a1n, v\u00ed d\u1ee5 (A1:B5,D1:F5).<\/p>\n<p style=\"text-align: justify\">N\u1ebfu m\u1ed7i d\u00e3y trong t\u00e0i li\u1ec7u tham kh\u1ea3o ch\u1ec9 ch\u1ee9a m\u1ed9t h\u00e0ng ho\u1eb7c c\u1ed9t, \u0111\u1ed1i s\u1ed1 <strong><em>row_num<\/em><\/strong> ho\u1eb7c <strong><em>column_num<\/em><\/strong> t\u01b0\u01a1ng \u1ee9ng l\u00e0 t\u00f9y ch\u1ecdn.<\/p>\n<ul style=\"text-align: justify\">\n<li><strong><em> row_num \u2013<\/em><\/strong> s\u1ed1 th\u1ee9 t\u1ef1 c\u1ee7a h\u00e0ng n\u1eb1m trong kho\u1ea3ng m\u00e0 t\u1eeb \u0111\u00f3 tr\u1ea3 v\u1ec1 m\u1ed9t \u00f4 tham chi\u1ebfu, n\u00f3 t\u01b0\u01a1ng t\u1ef1 nh\u01b0 d\u1ea1ng m\u1ea3ng.<\/li>\n<li><strong><em> column_num \u2013<\/em><\/strong> s\u1ed1 th\u1ee9 t\u1ef1 c\u1ed9t \u0111\u1ec3 tr\u1ea3 v\u1ec1 m\u1ed9t \u00f4 tham chi\u1ebfu, c\u0169ng ho\u1ea1t \u0111\u1ed9ng t\u01b0\u01a1ng t\u1ef1 nh\u01b0 d\u1ea1ng m\u1ea3ng.<\/li>\n<li><strong><em> area_num \u2013<\/em><\/strong> m\u1ed9t tham s\u1ed1 t\u00f9y ch\u1ecdn ch\u1ec9 ra m\u1ea3ng t\u1eeb \u0111\u1ed1i s\u1ed1 tham chi\u1ebfu \u0111\u1ec3 s\u1eed d\u1ee5ng. N\u1ebfu b\u1ecf qua, c\u00f4ng th\u1ee9c INDEX s\u1ebd tr\u1ea3 l\u1ea1i k\u1ebft qu\u1ea3 cho m\u1ea3ng \u0111\u1ea7u ti\u00ean \u0111\u01b0\u1ee3c li\u1ec7t k\u00ea trong danh s\u00e1ch tham kh\u1ea3o.<\/li>\n<\/ul>\n<p style=\"text-align: justify\">V\u00ed d\u1ee5, c\u00f4ng th\u1ee9c <strong><em>= INDEX ((A2: D3, A5: D7), 3, 4, 2) <\/em><\/strong>tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb c\u1ee7a \u00f4 D7, t\u1ea1i giao \u0111i\u1ec3m c\u1ee7a h\u00e0ng th\u1ee9 3 v\u00e0 c\u1ed9t th\u1ee9 4 trong khu v\u1ef1c th\u1ee9 hai (A5: D7).<\/p>\n<p style=\"text-align: justify\"><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-index-reference-form.png\" alt=\"An example of the INDEX reference form\" title=\"excel-index-reference-form\"><\/p>\n<h4 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"Dinh_dang_tham_chieu_INDEX_%E2%80%93_nhung_dieu_can_nho\"><\/span><strong>\u0110\u1ecbnh d\u1ea1ng tham chi\u1ebfu <em>INDEX <\/em>\u2013 nh\u1eefng \u0111i\u1ec1u c\u1ea7n nh\u1edb<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h4>\n<ol style=\"text-align: justify\">\n<li>N\u1ebfu \u0111\u1ed1i s\u1ed1 <strong><em>row_num <\/em><\/strong>ho\u1eb7c <strong><em>column_num <\/em><\/strong>\u0111\u01b0\u1ee3c \u0111\u1eb7t th\u00e0nh 0, th\u00ec m\u1ed9t c\u00f4ng th\u1ee9c Excel INDEX tr\u1ea3 v\u1ec1 tham chi\u1ebfu cho to\u00e0n b\u1ed9 c\u1ed9t ho\u1eb7c h\u00e0ng t\u01b0\u01a1ng \u1ee9ng.<\/li>\n<li>N\u1ebfu c\u1ea3 hai <strong><em>row_num <\/em><\/strong>v\u00e0 <strong><em>column_num <\/em><\/strong>b\u1ecb b\u1ecf qua, h\u00e0m INDEX tr\u1ea3 v\u1ec1 v\u00f9ng \u0111\u01b0\u1ee3c ch\u1ec9 \u0111\u1ecbnh trong \u0111\u1ed1i s\u1ed1 area_num.<\/li>\n<li>T\u1ea5t c\u1ea3 c\u00e1c _<strong><em>num arguments (row_num, column_num v\u00e0 area_num) <\/em><\/strong>ph\u1ea3i tham kh\u1ea3o m\u1ed9t \u00f4 trong tham chi\u1ebfu; N\u1ebfu kh\u00f4ng, c\u00f4ng th\u1ee9c INDEX s\u1ebd tr\u1ea3 v\u1ec1 #REF! l\u1ed7i.<\/li>\n<\/ol>\n<p style=\"text-align: justify\">C\u1ea3 hai c\u00f4ng th\u1ee9c <strong><em>INDEX <\/em><\/strong>ch\u00fang ta \u0111\u00e3 th\u1ea3o lu\u1eadn cho \u0111\u1ebfn nay \u0111\u1ec1u r\u1ea5t \u0111\u01a1n gi\u1ea3n v\u00e0 ch\u1ec9 minh ho\u1ea1 cho kh\u00e1i ni\u1ec7m. C\u00f4ng th\u1ee9c th\u1ef1c s\u1ef1 c\u1ee7a b\u1ea1n c\u00f3 th\u1ec3 ph\u1ee9c t\u1ea1p h\u01a1n nhi\u1ec1u so v\u1edbi \u0111\u00f3, v\u00ec v\u1eady, h\u00e3y c\u00f9ng kh\u00e1m ph\u00e1 m\u1ed9t v\u00e0i s\u1eed d\u1ee5ng hi\u1ec7u qu\u1ea3 nh\u1ea5t c\u1ee7a INDEX trong Excel.<\/p>\n<blockquote>\n<p style=\"text-align: justify\"><a href=\"http:\/\/blog.ezworkapp.com\/tag\/cac-ham-co-ban-trong-excel\">C\u00e1c h\u00e0m th\u01b0\u1eddng d\u00f9ng trong excel <\/a><\/p>\n<\/blockquote>\n<h2 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"Cach_dung_ham_INDEX_trong_Excel-_vi_du_cong_thuc\"><\/span><strong>C\u00e1ch d\u00f9ng h\u00e0m <em>INDEX<\/em> trong Excel- v\u00ed d\u1ee5, c\u00f4ng th\u1ee9c<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p style=\"text-align: justify\">C\u00f3 th\u1ec3 n\u00f3 kh\u00f4ng c\u00f3 nhi\u1ec1u \u1ee9ng d\u1ee5ng th\u1ef1c t\u1ebf, nh\u01b0ng n\u1ebfu k\u1ebft h\u1ee3p v\u1edbi c\u00e1c h\u00e0m kh\u00e1c nh\u01b0 MATCH hay COUNTA, n\u00f3 c\u00f3 th\u1ec3 t\u1ea1o c\u00e1c c\u00f4ng th\u1ee9c r\u1ea5t m\u1ea1nh.<\/p>\n<p style=\"text-align: justify\"><strong>Ngu\u1ed3n d\u1eef li\u1ec7u (SourceData)<\/strong><\/p>\n<p style=\"text-align: justify\">T\u1ea5t c\u1ea3 c\u00e1c c\u00f4ng th\u1ee9c Excel <strong><em>INDEX<\/em><\/strong> c\u1ee7a ch\u00fang t\u00f4i (ngo\u1ea1i tr\u1eeb c\u00f4ng th\u1ee9c cu\u1ed1i c\u00f9ng), ch\u00fang t\u00f4i s\u1ebd s\u1eed d\u1ee5ng d\u1eef li\u1ec7u d\u01b0\u1edbi \u0111\u00e2y. \u0110\u1ec3 thu\u1eadn ti\u1ec7n, n\u00f3 \u0111\u01b0\u1ee3c t\u1ed5 ch\u1ee9c trong m\u1ed9t b\u1ea3ng c\u00f3 t\u00ean <strong><em>SourceData<\/em><\/strong>.<\/p>\n<p style=\"text-align: justify\"><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-index-source-data.png\" alt=\"The source table to be used in formulas\" title=\"excel-index-source-data\"><\/p>\n<p style=\"text-align: justify\">Vi\u1ec7c s\u1eed d\u1ee5ng c\u00e1c b\u1ea3ng ho\u1eb7c c\u00e1c m\u1ea3ng \u0111\u01b0\u1ee3c \u0111\u1eb7t t\u00ean c\u00f3 th\u1ec3 l\u00e0m cho c\u00f4ng th\u1ee9c d\u00e0i h\u01a1n m\u1ed9t t\u00ed, nh\u01b0ng n\u00f3 c\u0169ng l\u00e0m cho ch\u00fang linh ho\u1ea1t v\u00e0 d\u1ec5 \u0111\u1ecdc h\u01a1n. \u0110\u1ec3 \u0111i\u1ec1u ch\u1ec9nh b\u1ea5t k\u1ef3 c\u00f4ng th\u1ee9c INDEX n\u00e0o cho c\u00e1c b\u1ea3ng t\u00ednh c\u1ee7a b\u1ea1n, b\u1ea1n ch\u1ec9 c\u1ea7n ch\u1ec9nh s\u1eeda m\u1ed9t c\u00e1i t\u00ean duy nh\u1ea5t.<\/p>\n<p style=\"text-align: justify\">T\u1ea5t nhi\u00ean, kh\u00f4ng c\u00f3 g\u00ec ng\u0103n c\u1ea3n b\u1ea1n s\u1eed d\u1ee5ng m\u1ea3ng th\u00f4ng th\u01b0\u1eddng n\u1ebfu b\u1ea1n mu\u1ed1n. Trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y, b\u1ea1n ch\u1ec9 c\u1ea7n thay th\u1ebf t\u00ean b\u1ea3ng SourceData b\u1eb1ng tham chi\u1ebfu m\u1ea3ng th\u00edch h\u1ee3p.<\/p>\n<ol style=\"text-align: justify\">\n<li><strong>L\u1ea5y d\u1eef li\u1ec7u \u1edf v\u1ecb tr\u00ed th\u1ee9 N t\u1eeb danh s\u00e1ch<\/strong><\/li>\n<\/ol>\n<p style=\"text-align: justify\">\u0110\u00e2y l\u00e0 c\u00e1ch s\u1eed d\u1ee5ng c\u01a1 b\u1ea3n v\u00e0 l\u00e0 v\u00ed d\u1ee5 v\u1ec1 h\u00e0m INDEX d\u1ec5 hi\u1ec3u nh\u1ea5t. \u0110\u1ec3 l\u1ea5y m\u1ed9t d\u1eef li\u1ec7u nh\u1ea5t \u0111\u1ecbnh trong danh s\u00e1ch, b\u1ea1n ch\u1ec9 c\u1ea7n vi\u1ebft <strong><em>= INDEX (range, n),<\/em><\/strong> trong \u0111\u00f3 <strong><em>range<\/em><\/strong> l\u00e0 m\u1ed9t d\u1ea3i \u00f4 ho\u1eb7c d\u1ea3i \u00f4 \u0111\u01b0\u1ee3c \u0111\u1eb7t t\u00ean, v\u00e0 <strong><em>n <\/em><\/strong>l\u00e0 v\u1ecb tr\u00ed c\u1ee7a d\u1eef li\u1ec7u m\u00e0 b\u1ea1n mu\u1ed1n nh\u1eadn.<\/p>\n<p style=\"text-align: justify\">Khi l\u00e0m vi\u1ec7c v\u1edbi b\u1ea3ng Excel, b\u1ea1n c\u00f3 th\u1ec3 d\u00f9ng tr\u1ecf chu\u1ed9t ch\u1ecdn c\u1ed9t v\u00e0 Excel s\u1ebd \u0111\u01b0a t\u00ean c\u1ee7a c\u1ed9t c\u00f9ng v\u1edbi t\u00ean c\u1ee7a b\u1ea3ng v\u00f4 c\u00f4ng th\u1ee9c:<\/p>\n<p style=\"text-align: justify\"><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-index-item-list.png\" alt=\"An INDEX formula to get the N&lt;sup&gt;th&lt;\/sup&gt; item from the list\" title=\"excel-index-item-list\"><\/p>\n<p style=\"text-align: justify\">\u0110\u1ec3 nh\u1eadn gi\u00e1 tr\u1ecb c\u1ee7a \u00f4 \u1edf giao \u0111i\u1ec3m c\u1ee7a m\u1ed9t h\u00e0ng v\u00e0 c\u1ed9t \u0111\u00e3 cho, b\u1ea1n s\u1eed d\u1ee5ng c\u00e1ch ti\u1ebfp c\u1eadn t\u01b0\u01a1ng t\u1ef1 v\u1edbi s\u1ef1 kh\u00e1c bi\u1ec7t duy nh\u1ea5t l\u00e0 b\u1ea1n s\u1eed d\u1ee5ng c\u1ea3 hai \u2013 s\u1ed1 h\u00e0ng v\u00e0 s\u1ed1 c\u1ed9t.<\/p>\n<p style=\"text-align: justify\">V\u00e0 \u0111\u00e2y l\u00e0 m\u1ed9t v\u00ed d\u1ee5 kh\u00e1c: Trong b\u1ea3ng t\u00ednh m\u00e0 ch\u00fang t\u00f4i l\u1ea5y l\u00e0m v\u00ed d\u1ee5, \u0111\u1ec3 t\u00ecm ra h\u00e0nh tinh l\u1edbn th\u1ee9 2 trong h\u1ec7 m\u1eb7t tr\u1eddi, b\u1ea1n s\u1eafp x\u1ebfp b\u1ea3ng theo c\u1ed9t \u0110\u01b0\u1eddng k\u00ednh (<strong><em>Diameter<\/em><\/strong>) v\u00e0 s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c <strong><em>INDEX <\/em><\/strong>nh\u01b0 sau:<\/p>\n<p style=\"text-align: justify\"><strong><em>= INDEX (SourceData, 2, 3)<\/em><\/strong><\/p>\n<ul style=\"text-align: justify\">\n<li><strong><em> Array l\u00e0 <\/em><\/strong>t\u00ean b\u1ea3ng, ho\u1eb7c m\u1ed9t tham chi\u1ebfu d\u1ea3i, t\u01b0\u01a1ng \u1ee9ng v\u1edbi SourceData trong v\u00ed d\u1ee5 n\u00e0y.<\/li>\n<li><strong><em> Row_num<\/em><\/strong> l\u00e0 2 v\u00ec b\u1ea1n \u0111ang t\u00ecm ki\u1ebfm m\u1ee5c th\u1ee9 hai trong danh s\u00e1ch<\/li>\n<li><strong><em> Column_num <\/em><\/strong>l\u00e0 3 v\u00ec Diameter \u1edf v\u1ecb tr\u00ed c\u1ed9t th\u1ee9 3 trong b\u1ea3ng.<\/li>\n<\/ul>\n<p style=\"text-align: justify\">N\u1ebfu b\u1ea1n mu\u1ed1n tr\u1ea3 l\u1ea1i t\u00ean c\u1ee7a h\u00e0nh tinh thay v\u00ec \u0111\u01b0\u1eddng k\u00ednh, th\u00ec b\u1ea1n ph\u1ea3i thay \u0111\u1ed5i column_num th\u00e0nh 1. V\u00e0 t\u1ef1 nhi\u00ean, b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng m\u1ed9t tham chi\u1ebfu \u00f4 trong \u0111\u1ed1i s\u1ed1 row_num v\u00e0\/ho\u1eb7c column_num \u0111\u1ec3 l\u00e0m cho c\u00f4ng th\u1ee9c INDEX c\u1ee7a b\u1ea1n linh ho\u1ea1t h\u01a1n, nh\u01b0 \u0111\u01b0\u1ee3c th\u1ec3 hi\u1ec7n trong h\u00ecnh b\u00ean d\u01b0\u1edbi:<\/p>\n<p style=\"text-align: justify\"><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-index-formula.png\" alt=\"An INDEX formula to get the value at the intersection of a given row and column\" title=\"excel-index-formula\"><\/p>\n<ol style=\"text-align: justify\" start=\"2\">\n<li><strong> Nh\u1eadn \u0111\u01b0\u1ee3c t\u1ea5t c\u1ea3 c\u00e1c gi\u00e1 tr\u1ecb trong m\u1ed9t h\u00e0ng ho\u1eb7c c\u1ed9t<\/strong><\/li>\n<\/ol>\n<p style=\"text-align: justify\">Ngo\u00e0i vi\u1ec7c l\u1ea5y ra m\u1ed9t \u00f4 duy nh\u1ea5t, h\u00e0m <strong><em>INDEX<\/em><\/strong> c\u00f3 th\u1ec3 tr\u1ea3 v\u1ec1 m\u1ed9t d\u1ea3i c\u00e1c gi\u00e1 tr\u1ecb t\u1eeb to\u00e0n b\u1ed9 c\u00e1c h\u00e0ng ho\u1eb7c c\u00e1c c\u1ed9t. \u0110\u1ec3 l\u1ea5y t\u1ea5t c\u1ea3 c\u00e1c gi\u00e1 tr\u1ecb t\u1eeb m\u1ed9t c\u1ed9t nh\u1ea5t \u0111\u1ecbnh, b\u1ea1n ph\u1ea3i b\u1ecf qua \u0111\u1ed1i s\u1ed1 <strong><em>row_num <\/em><\/strong>ho\u1eb7c \u0111\u1eb7t n\u00f3 l\u00e0 0. T\u01b0\u01a1ng t\u1ef1, \u0111\u1ec3 c\u00f3 \u0111\u01b0\u1ee3c to\u00e0n b\u1ed9 h\u00e0ng, b\u1ea1n b\u1ecf qua ho\u1eb7c 0 trong <strong><em>column_num<\/em><\/strong>.<\/p>\n<p style=\"text-align: justify\">C\u00e1c c\u00f4ng th\u1ee9c <strong><em>INDEX<\/em><\/strong> nh\u01b0 v\u1eady kh\u00f3 c\u00f3 th\u1ec3 \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng b\u1edfi v\u00ec Excel kh\u00f4ng th\u1ec3 ph\u00f9 h\u1ee3p v\u1edbi d\u1ea3i c\u00e1c gi\u00e1 tr\u1ecb \u0111\u01b0\u1ee3c tr\u1ea3 v\u1ec1 b\u1edfi c\u00f4ng th\u1ee9c trong m\u1ed9t \u00f4 duy nh\u1ea5t, m\u00e0 thay v\u00e0o \u0111\u00f3 th\u00ec b\u1ea1n s\u1ebd nh\u1eadn \u0111\u01b0\u1ee3c <strong><em>#VALUE! <\/em><\/strong>l\u1ed7i. Tuy nhi\u00ean, n\u1ebfu b\u1ea1n s\u1eed d\u1ee5ng INDEX k\u1ebft h\u1ee3p v\u1edbi c\u00e1c h\u00e0m trong Excel kh\u00e1c, ch\u1eb3ng h\u1ea1n nh\u01b0 <strong><em>SUM ho\u1eb7c AVERAGE<\/em><\/strong>, b\u1ea1n s\u1ebd thu \u0111\u01b0\u1ee3c k\u1ebft qu\u1ea3 tuy\u1ec7t v\u1eddi.<\/p>\n<p style=\"text-align: justify\">V\u00ed d\u1ee5, b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c Index Excel sau \u0111\u1ec3 t\u00ednh nhi\u1ec7t \u0111\u1ed9 trung b\u00ecnh c\u1ee7a h\u00e0nh tinh trong h\u1ec7 m\u1eb7t tr\u1eddi:<\/p>\n<p style=\"text-align: justify\"><strong><em>= AVERAGE (INDEX (SourceData,, 4))<\/em><\/strong><\/p>\n<p style=\"text-align: justify\">Trong c\u00f4ng th\u1ee9c tr\u00ean, \u0111\u1ed1i s\u1ed1 column_num l\u00e0 4 v\u00ec Temperature l\u00e0 c\u1ed9t th\u1ee9 4 trong b\u1ea3ng c\u1ee7a ch\u00fang ta. Th\u00f4ng s\u1ed1 row_num b\u1ecb b\u1ecf qua.<\/p>\n<p style=\"text-align: justify\"><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-index-average.png\" alt=\"A formula to calculate the average of values in a list\" title=\"excel-index-average\"><\/p>\n<p style=\"text-align: justify\">T\u01b0\u01a1ng t\u1ef1 nh\u01b0 v\u1eady, b\u1ea1n c\u00f3 th\u1ec3 t\u00ecm \u0111\u01b0\u1ee3c nhi\u1ec7t \u0111\u1ed9 t\u1ed1i thi\u1ec3u v\u00e0 t\u1ed1i \u0111a:<\/p>\n<p style=\"text-align: justify\"><strong><em>= MAX (INDEX (SourceData,, 4))<\/em><\/strong><\/p>\n<p style=\"text-align: justify\"><strong><em>= MIN (INDEX (SourceData,, 4))<\/em><\/strong><\/p>\n<p style=\"text-align: justify\">V\u00e0 t\u00ednh t\u1ed5ng kh\u1ed1i l\u01b0\u1ee3ng h\u00e0nh tinh (Mass l\u00e0 c\u1ed9t th\u1ee9 2 trong b\u1ea3ng):<\/p>\n<p style=\"text-align: justify\"><strong><em>= SUM (INDEX (SourceData,, 2))<\/em><\/strong><\/p>\n<p style=\"text-align: justify\">T\u1eeb quan \u0111i\u1ec3m th\u1ef1c t\u1ebf, h\u00e0m <strong><em>INDEX<\/em><\/strong> trong c\u00f4ng th\u1ee9c tr\u00ean l\u00e0 kh\u00f4ng c\u1ea7n thi\u1ebft. B\u1ea1n ch\u1ec9 c\u1ea7n vi\u1ebft <strong><em>= AVERAGE (d\u1ea3i) ho\u1eb7c = SUM (d\u1ea3i)<\/em><\/strong> v\u00e0 c\u0169ng nh\u1eadn \u0111\u01b0\u1ee3c k\u1ebft qu\u1ea3 t\u01b0\u01a1ng t\u1ef1.<\/p>\n<p style=\"text-align: justify\">Khi l\u00e0m vi\u1ec7c v\u1edbi d\u1eef li\u1ec7u th\u1ef1c, t\u00ednh n\u0103ng n\u00e0y c\u00f3 th\u1ec3 h\u1eefu \u00edch nh\u01b0 l\u00e0 m\u1ed9t ph\u1ea7n c\u1ee7a c\u00e1c c\u00f4ng th\u1ee9c ph\u1ee9c t\u1ea1p h\u01a1n m\u00e0 b\u1ea1n s\u1eed d\u1ee5ng \u0111\u1ec3 ph\u00e2n t\u00edch d\u1eef li\u1ec7u.<\/p>\n<ol style=\"text-align: justify\" start=\"3\">\n<li><strong> S\u1eed d\u1ee5ng <em>INDEX<\/em> v\u1edbi c\u00e1c h\u00e0m Excel kh\u00e1c (<em>SUM, AVERAGE, MAX, MIN<\/em>) <\/strong><\/li>\n<\/ol>\n<p style=\"text-align: justify\">T\u1eeb c\u00e1c v\u00ed d\u1ee5 tr\u01b0\u1edbc, b\u1ea1n c\u00f3 th\u1ec3 ngh\u0129 r\u1eb1ng c\u00f4ng th\u1ee9c INDEX trong Excel tr\u1ea3 v\u1ec1 c\u00e1c gi\u00e1 tr\u1ecb, nh\u01b0ng th\u1ef1c t\u1ebf l\u00e0 n\u00f3 tr\u1ea3 v\u1ec1 m\u1ed9t tham chi\u1ebfu \u0111\u1ebfn \u00f4 c\u00f3 ch\u1ee9a gi\u00e1 tr\u1ecb. V\u00e0 v\u00ed d\u1ee5 n\u00e0y th\u1ec3 hi\u1ec7n b\u1ea3n ch\u1ea5t th\u1ef1c s\u1ef1 c\u1ee7a h\u00e0m INDEX.<\/p>\n<p style=\"text-align: justify\">V\u1edbi k\u1ebft qu\u1ea3 c\u1ee7a m\u1ed9t c\u00f4ng th\u1ee9c INDEX mang t\u00ednh tham chi\u1ebfu, ch\u00fang ta c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng n\u00f3 trong c\u00e1c h\u00e0m kh\u00e1c \u0111\u1ec3 t\u1ea1o ra m\u1ed9t d\u1ea3i \u0111\u1ed9ng. Kh\u00f3 hi\u1ec3u \u01b0? C\u00f4ng th\u1ee9c sau \u0111\u00e2y s\u1ebd l\u00e0m r\u00f5 m\u1ecdi th\u1ee9:<\/p>\n<p style=\"text-align: justify\">Gi\u1ea3 s\u1eed b\u1ea1n c\u00f3 m\u1ed9t c\u00f4ng th\u1ee9c <strong><em>= AVERAGE (A1: A10)<\/em><\/strong> tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb trung b\u00ecnh c\u1ee7a c\u00e1c \u00f4 A1: A10. Thay v\u00ec vi\u1ebft d\u1ea3i tr\u1ef1c ti\u1ebfp nh\u01b0 trong c\u00f4ng th\u1ee9c, th\u00ec b\u1ea1n c\u00f3 th\u1ec3 thay th\u1ebf c\u1ea3 A1 ho\u1eb7c A10 ho\u1eb7c c\u1ea3 hai b\u1eb1ng c\u00e1c h\u00e0m INDEX nh\u01b0 sau:<\/p>\n<p style=\"text-align: justify\"><strong><em>= AVERAGE(A1: INDEX (A1: A20,10))<\/em><\/strong><\/p>\n<p style=\"text-align: justify\">C\u1ea3 hai c\u00f4ng th\u1ee9c tr\u00ean s\u1ebd mang l\u1ea1i k\u1ebft qu\u1ea3 t\u01b0\u01a1ng t\u1ef1 b\u1edfi v\u00ec h\u00e0m INDEX c\u0169ng tr\u1ea3 v\u1ec1 m\u1ed9t tham chi\u1ebfu \u0111\u1ebfn \u00f4 A10 (row_num \u0111\u01b0\u1ee3c \u0111\u1eb7t l\u00e0 10, col_num b\u1ecf qua). S\u1ef1 kh\u00e1c bi\u1ec7t l\u00e0 c\u00f4ng th\u1ee9c <strong><em>AVERAGE \/ INDEX <\/em><\/strong>s\u1eed d\u1ee5ng cho d\u1ea3i \u0111\u1ed9ng v\u00e0 khi b\u1ea1n thay \u0111\u1ed5i \u0111\u1ed1i s\u1ed1 row_num trong INDEX, th\u00ec d\u1ea3i m\u00e0 \u0111\u01b0\u1ee3c x\u1eed l\u00fd b\u1edfi <a href=\"http:\/\/blog.ezworkapp.com\/tag\/ham-average\">h\u00e0m <strong><em>AVERAGE<\/em><\/strong><\/a> s\u1ebd thay \u0111\u1ed5i v\u00e0 c\u00f4ng th\u1ee9c s\u1ebd tr\u1ea3 v\u1ec1 m\u1ed9t k\u1ebft qu\u1ea3 kh\u00e1c.<\/p>\n<p style=\"text-align: justify\">R\u00f5 r\u00e0ng, tuy c\u00e1ch th\u1ee9c c\u00f4ng th\u1ee9c INDEX v\u1eadn h\u00e0nh t\u1ecf ra qu\u00e1 ph\u1ee9c t\u1ea1p, nh\u01b0ng n\u00f3 c\u00f3 c\u00e1c \u1ee9ng d\u1ee5ng th\u1ef1c t\u1ebf, nh\u01b0 trong c\u00e1c v\u00ed d\u1ee5 sau \u0111\u00e2y:<\/p>\n<p style=\"text-align: justify\"><strong>V\u00ed d\u1ee5 1. T\u00ednh trung b\u00ecnh c\u1ee7a c\u00e1c m\u1ee5c trong danh s\u00e1ch n m\u1ee5c \u0111\u1ee9ng \u0111\u1ea7u:<\/strong><\/p>\n<p style=\"text-align: justify\">Gi\u1ea3 s\u1eed b\u1ea1n mu\u1ed1n bi\u1ebft \u0111\u01b0\u1eddng k\u00ednh trung b\u00ecnh c\u1ee7a n h\u00e0nh tinh l\u1edbn nh\u1ea5t trong h\u1ec7 th\u1ed1ng c\u1ee7a ch\u00fang t\u00f4i. V\u00ec v\u1eady, b\u1ea1n s\u1eafp x\u1ebfp b\u1ea3ng theo c\u1ed9t <strong><em>Diameter<\/em><\/strong> t\u1eeb l\u1edbn nh\u1ea5t \u0111\u1ebfn nh\u1ecf nh\u1ea5t v\u00e0 s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c Average \/ Index sau:<\/p>\n<p style=\"text-align: justify\"><strong><em>= AVERAGE(C5: INDEX (SouceData [Diameter], B1)) <\/em><\/strong><\/p>\n<p style=\"text-align: justify\"><strong><em>\u00a0<img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-index-dynamic-range.png\" alt=\"Calculating the average of the top N items in the list\" title=\"excel-index-dynamic-range\"><\/em><\/strong><\/p>\n<p style=\"text-align: justify\"><strong>V\u00ed d\u1ee5 2.\u00a0 T\u00ednh t\u1ed5ng c\u00e1c gi\u00e1 tr\u1ecb gi\u1eefa 2 m\u1ee5c c\u1ee5 th\u1ec3:<\/strong><\/p>\n<p style=\"text-align: justify\">Trong tr\u01b0\u1eddng h\u1ee3p b\u1ea1n mu\u1ed1n x\u00e1c \u0111\u1ecbnh gi\u1edbi h\u1ea1n tr\u00ean v\u00e0 gi\u1edbi h\u1ea1n d\u01b0\u1edbi trong c\u00f4ng th\u1ee9c c\u1ee7a m\u00ecnh, ch\u1ec9 c\u1ea7n s\u1eed d\u1ee5ng hai h\u00e0m INDEX \u0111\u1ec3 tr\u1ea3 l\u1ea1i m\u1ee5c \u0111\u1ea7u ti\u00ean v\u00e0 m\u1ee5c cu\u1ed1i c\u00f9ng b\u1ea1n mu\u1ed1n.<\/p>\n<p style=\"text-align: justify\">V\u00ed d\u1ee5: c\u00f4ng th\u1ee9c sau tr\u1ea3 v\u1ec1 t\u1ed5ng c\u00e1c gi\u00e1 tr\u1ecb trong c\u1ed9t Diameter (\u0111\u01b0\u1eddng k\u00ednh) gi\u1eefa hai d\u1eef li\u1ec7u \u0111\u01b0\u1ee3c ch\u1ec9 \u0111\u1ecbnh trong \u00f4 B1 v\u00e0 \u200b\u200bB2:<\/p>\n<p style=\"text-align: justify\"><strong><em>= SUM (INDEX (SourceData [Diameter], B1): INDEX (SourceData [Diameter], B2))\u00a0<\/em><\/strong><\/p>\n<p style=\"text-align: justify\"><strong><em>\u00a0<img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-index-sum.png\" alt=\"Sum values between the first and the last item you specify\" title=\"excel-index-sum\"><\/em><\/strong><\/p>\n<ol style=\"text-align: justify\" start=\"4\">\n<li><strong> C\u00f4ng th\u1ee9c INDEX \u0111\u1ec3 t\u1ea1o c\u00e1c d\u1ea3i \u0111\u1ed9ng v\u00e0 danh s\u00e1ch theo m\u1ee5c<\/strong><\/li>\n<\/ol>\n<p style=\"text-align: justify\">\u0110i\u1ec1u n\u00e0y r\u1ea5t th\u01b0\u1eddng x\u1ea3y ra. Khi b\u1ea1n b\u1eaft \u0111\u1ea7u t\u1ed5 ch\u1ee9c d\u1eef li\u1ec7u trong m\u1ed9t b\u1ea3ng t\u00ednh, b\u1ea1n c\u00f3 th\u1ec3 kh\u00f4ng bi\u1ebft c\u00f3 bao nhi\u00eau m\u1ee5c b\u1ea1n s\u1ebd c\u00f3.<\/p>\n<p style=\"text-align: justify\">D\u00f9 sao th\u00ec, n\u1ebfu b\u1ea1n c\u00f3 s\u1ed1 c\u00e1c m\u1ee5c trong m\u1ed9t c\u1ed9t \u0111\u00e3 cho thay \u0111\u1ed5i, t\u1eeb A1 \u0111\u1ebfn A<em>n<\/em>, v\u00e0 b\u1ea1n c\u00f3 th\u1ec3 mu\u1ed1n t\u1ea1o ra m\u1ed9t d\u1ea3i \u0111\u01b0\u1ee3c \u0111\u1eb7t t\u00ean \u2013 bao g\u1ed3m t\u1ea5t c\u1ea3 c\u00e1c \u00f4 c\u00f3 d\u1eef li\u1ec7u. T\u1ea1i \u0111\u00f3, b\u1ea1n mu\u1ed1n d\u1ea3i \u0111\u01b0\u1ee3c \u0111i\u1ec1u ch\u1ec9nh t\u1ef1 \u0111\u1ed9ng khi b\u1ea1n th\u00eam m\u1ee5c m\u1edbi ho\u1eb7c x\u00f3a m\u1ed9t s\u1ed1 m\u1ee5c hi\u1ec7n c\u00f3. V\u00ed d\u1ee5: n\u1ebfu b\u1ea1n hi\u1ec7n c\u00f3 10 m\u1ee5c, d\u1ea3i \u0111\u01b0\u1ee3c \u0111\u1eb7t t\u00ean c\u1ee7a b\u1ea1n s\u1ebd l\u00e0 A1: A10. N\u1ebfu b\u1ea1n th\u00eam m\u1ed9t m\u1ee5c nh\u1eadp m\u1edbi, ph\u1ea1m vi t\u00ean s\u1ebd t\u1ef1 \u0111\u1ed9ng m\u1edf r\u1ed9ng l\u00ean A1: A11 v\u00e0 n\u1ebfu b\u1ea1n thay \u0111\u1ed5i \u00fd \u0111\u1ecbnh v\u00e0 x\u00f3a d\u1eef li\u1ec7u m\u1edbi \u0111\u01b0\u1ee3c th\u00eam v\u00e0o, ph\u1ea1m vi s\u1ebd t\u1ef1 \u0111\u1ed9ng quay tr\u1edf l\u1ea1i A1: A10.<\/p>\n<p style=\"text-align: justify\">L\u1ee3i \u00edch ch\u00ednh c\u1ee7a c\u00e1ch ti\u1ebfp c\u1eadn n\u00e0y l\u00e0 b\u1ea1n kh\u00f4ng ph\u1ea3i li\u00ean t\u1ee5c c\u1eadp nh\u1eadt t\u1ea5t c\u1ea3 c\u00e1c c\u00f4ng th\u1ee9c trong b\u1ea3ng t\u00ednh c\u1ee7a b\u1ea1n \u0111\u1ec3 \u0111\u1ea3m b\u1ea3o ch\u00fang tham chi\u1ebfu \u0111\u1ebfn c\u00e1c d\u1ea3i m\u1ed9t c\u00e1ch ch\u00ednh x\u00e1c.<\/p>\n<p style=\"text-align: justify\">M\u1ed9t c\u00e1ch \u0111\u1ec3 x\u00e1c \u0111\u1ecbnh m\u1ed9t d\u1ea3i \u0111\u1ed9ng \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng h\u00e0m OFFSET:<\/p>\n<p style=\"text-align: justify\"><strong><em>= OFFSET (Sheet_Name! $ A $ 1, 0, 0, COUNTA (Sheet_Name! $A :$A ), 1)<\/em><\/strong><\/p>\n<p style=\"text-align: justify\">M\u1ed9t gi\u1ea3i ph\u00e1p c\u00f3 th\u1ec3 kh\u00e1c l\u00e0 s\u1eed d\u1ee5ng Excel INDEX c\u00f9ng v\u1edbi COUNTA:<\/p>\n<p style=\"text-align: justify\"><strong><em>= Sheet_Name! $ A $ 1: INDEX (Sheet_Name! $ A: $ A, COUNTA (Sheet_Name! $A :$A ))<\/em><\/strong><\/p>\n<p style=\"text-align: justify\">Trong c\u1ea3 hai c\u00f4ng th\u1ee9c, A1 l\u00e0 \u00f4 c\u00f3 ch\u1ee9a m\u1ee5c \u0111\u1ea7u ti\u00ean c\u1ee7a danh s\u00e1ch v\u00e0 d\u1ea3i \u0111\u1ed9ng \u0111\u01b0\u1ee3c t\u1ea1o ra b\u1edfi c\u1ea3 hai c\u00f4ng th\u1ee9c s\u1ebd gi\u1ed1ng h\u1ec7t nhau.<\/p>\n<p style=\"text-align: justify\">S\u1ef1 kh\u00e1c bi\u1ec7t \u1edf \u0111\u00e2y ch\u00ednh l\u00e0 c\u00e1ch ti\u1ebfp c\u1eadn. H\u00e0m INDEX t\u00ecm th\u1ea5y m\u1ed9t \u00f4 \u1edf giao \u0111i\u1ec3m c\u1ee7a m\u1ed9t h\u00e0ng v\u00e0 c\u1ed9t c\u1ee5 th\u1ec3, trong khi h\u00e0m <strong><em>OFFSET<\/em><\/strong> di chuy\u1ec3n t\u1eeb \u0111i\u1ec3m l\u00e0m m\u1ed1c b\u1edfi m\u1ed9t s\u1ed1 h\u00e0ng v\u00e0 \/ ho\u1eb7c c\u00e1c c\u1ed9t nh\u1ea5t \u0111\u1ecbnh. H\u00e0m <strong><em>COUNTA<\/em><\/strong>, \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng trong c\u1ea3 hai c\u00f4ng th\u1ee9c, t\u00ednh s\u1ed1 l\u01b0\u1ee3ng c\u00e1c \u00f4 kh\u00f4ng r\u1ed7ng trong c\u1ed9t v\u00e0 \u0111\u1ea3m b\u1ea3o r\u1eb1ng ch\u1ec9 nh\u1eefng \u00f4 c\u00f3 d\u1eef li\u1ec7u \u0111\u01b0\u1ee3c bao g\u1ed3m trong d\u1ea3i \u0111\u00e3 \u0111\u1eb7t t\u00ean.<\/p>\n<p style=\"text-align: justify\">H\u00ecnh sau \u0111\u00e2y s\u1ebd cho b\u1ea1n th\u1ea5y c\u00e1ch b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c Index \u0111\u1ec3 t\u1ea1o m\u1ed9t danh s\u00e1ch t\u00f9y ch\u1ecdn.<\/p>\n<p style=\"text-align: justify\"><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-index-named-range.png\" alt=\"INDEX formula to create a dynamic named range\" title=\"excel-index-named-range\"><\/p>\n<p style=\"text-align: justify\"><strong>M\u1eb9o<\/strong>: C\u00e1ch d\u1ec5 nh\u1ea5t \u0111\u1ec3 t\u1ea1o m\u1ed9t danh s\u00e1ch t\u00f9y ch\u1ecdn \u0111\u01b0\u1ee3c c\u1eadp nh\u1eadt t\u1ef1 \u0111\u1ed9ng trong Excel l\u00e0 t\u1ea1o m\u1ed9t danh s\u00e1ch (\u0111\u00e3 \u0111\u1eb7t t\u00ean) v\u00e0 \u0111\u01b0\u1ee3c li\u1ec7t k\u00ea d\u1ef1a tr\u00ean m\u1ed9t b\u1ea3ng. Trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y, b\u1ea1n s\u1ebd kh\u00f4ng c\u1ea7n b\u1ea5t k\u1ef3 c\u00f4ng th\u1ee9c ph\u1ee9c t\u1ea1p n\u00e0o v\u00ec c\u00e1c b\u1ea3ng Excel l\u00e0 d\u1ea3i \u0111\u1ed9ng.<\/p>\n<ol style=\"text-align: justify\" start=\"5\">\n<li><strong> S\u1ee9c m\u1ea1nh c\u1ee7a c\u00e1c h\u00e0m Vlookup khi k\u1ebft h\u1ee3p v\u1edbi INDEX \/ MATCH:<\/strong><\/li>\n<\/ol>\n<p style=\"text-align: justify\">Th\u1ef1c hi\u1ec7n c\u00e1c h\u00e0m <strong><em>Vlookup<\/em><\/strong> theo chi\u1ec1u d\u1ecdc trong Excel \u2013 \u0111\u00e2y l\u00e0 n\u01a1i m\u00e0 h\u00e0m INDEX th\u1ef1c s\u1ef1 t\u1ecfa s\u00e1ng. N\u1ebfu b\u1ea1n \u0111\u00e3 t\u1eebng th\u1eed s\u1eed d\u1ee5ng h\u00e0m <strong><em>VLOOKUP<\/em><\/strong>, b\u1ea1n s\u1ebd nh\u1eadn th\u1ee9c \u0111\u01b0\u1ee3c nhi\u1ec1u h\u1ea1n ch\u1ebf c\u1ee7a n\u00f3, ch\u1eb3ng h\u1ea1n nh\u01b0 kh\u00f4ng c\u00f3 kh\u1ea3 n\u0103ng k\u00e9o gi\u00e1 tr\u1ecb t\u1eeb c\u00e1c c\u1ed9t qua b\u00ean tr\u00e1i c\u1ee7a c\u1ed9t tra c\u1ee9u ho\u1eb7c gi\u1edbi h\u1ea1n 255 k\u00fd t\u1ef1 cho m\u1ed9t gi\u00e1 tr\u1ecb tra c\u1ee9u. V\u00e0 v\u00ec th\u1ebf cho n\u00ean <strong>k\u1ebft h\u1ee3p index v\u00e0 match<\/strong> l\u00e0 gi\u1ea3i ph\u00e1p m\u00e0 nhi\u1ec1u ng\u01b0\u1eddi l\u1ef1a ch\u1ecdn \u0111\u1ec3 thay th\u00ea<\/p>\n<p>Xem th\u00eam: <strong>S\u1ef1 <a href=\"http:\/\/blog.ezworkapp.com\/ham-index-va-ham-match-trong-excel-su-thay-tot-hon-ca-ham-vlookup.html\">k\u1ebft h\u1ee3p h\u00e0m INDEX v\u00e0 MATCH<\/a> trong Excel \u2013 S\u1ef1 thay th\u1ebf t\u1ed1t h\u01a1n c\u1ea3 h\u00e0m VLOOKUP<\/strong><\/p>\n<p style=\"text-align: justify\">H\u00e0m <strong><em>INDEX \/ MATCH<\/em><\/strong> t\u1ed1t h\u01a1n <strong><em>VLOOKUP<\/em><\/strong> \u1edf nhi\u1ec1u kh\u00eda c\u1ea1nh:<\/p>\n<ul style=\"text-align: justify\">\n<li>Kh\u00f4ng c\u00f3 v\u1ea5n \u0111\u1ec1 v\u1edbi d\u00f2 t\u00ecm b\u00ean tr\u00e1i.<\/li>\n<li>Kh\u00f4ng gi\u1edbi h\u1ea1n k\u00edch th\u01b0\u1edbc gi\u00e1 tr\u1ecb tra c\u1ee9u.<\/li>\n<li>Kh\u00f4ng y\u00eau c\u1ea7u ph\u00e2n lo\u1ea1i (VLOOKUP v\u1edbi k\u1ebft qu\u1ea3 t\u01b0\u01a1ng \u0111\u1ed1i khi y\u00eau c\u1ea7u s\u1eafp x\u1ebfp c\u1ed9t tra c\u1ee9u theo th\u1ee9 t\u1ef1 t\u0103ng d\u1ea7n).<\/li>\n<li>B\u1ea1n \u0111\u01b0\u1ee3c t\u1ef1 do ch\u00e8n v\u00e0 lo\u1ea1i b\u1ecf c\u00e1c c\u1ed9t trong m\u1ed9t b\u1ea3ng m\u00e0 kh\u00f4ng c\u1ea7n c\u1eadp nh\u1eadt m\u1ed7i c\u00f4ng th\u1ee9c li\u00ean quan.<\/li>\n<\/ul>\n<p style=\"text-align: justify\">V\u00e0 cu\u1ed1i c\u00f9ng nh\u01b0ng kh\u00f4ng k\u00e9m ph\u1ea7n quan tr\u1ecdng, <strong><em>INDEX \/ MATCH<\/em><\/strong> kh\u00f4ng l\u00e0m ch\u1eadm Excel c\u1ee7a b\u1ea1n nh\u01b0 nhi\u1ec1u h\u00e0m Vlookup \u0111\u00e3 l\u00e0m.<\/p>\n<p style=\"text-align: justify\">B\u1ea1n s\u1eed d\u1ee5ng <strong><em>INDEX \/ MATCH<\/em><\/strong> \u2013 h\u00e0m index k\u1ebft h\u1ee3p match theo c\u00e1ch sau:<\/p>\n<p style=\"text-align: justify\"><strong><em>= INDEX (column to return a value from, (MATCH (lookup value, column to lookup against, 0))<\/em><\/strong><\/p>\n<p style=\"text-align: justify\">V\u00ed d\u1ee5: n\u1ebfu ch\u00fang ta m\u1edf b\u1ea3ng d\u1eef li\u1ec7u g\u1ed1c c\u1ee7a ch\u00fang ta ra v\u1edbi t\u00ean h\u00e0nh tinh (planet name) tr\u1edf th\u00e0nh c\u1ed9t \u0111\u1ea7u ti\u00ean b\u00ean ph\u1ea3i, th\u00ec h\u00e0m c\u00f4ng th\u1ee9c INDEX \/ MATCH v\u1eabn l\u1ea5y gi\u00e1 tr\u1ecb ph\u00f9 h\u1ee3p t\u1eeb c\u1ed9t b\u00ean tr\u00e1i m\u00e0 kh\u00f4ng g\u1eb7p b\u1ea5t k\u00ec tr\u1edf ng\u1ea1i n\u00e0o.<\/p>\n<p style=\"text-align: justify\"><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-index-match.png\" alt=\"The INDEX \/ MATCH formula for a left Vlookup\" title=\"excel-index-match\"><\/p>\n<blockquote>\n<p style=\"text-align: justify\"><a href=\"http:\/\/blog.ezworkapp.com\/khoa-hoc-excel-ke-toan.html\">Kho\u0301a h\u1ecdc excel k\u1ebf to\u00e1n online<\/a><\/p>\n<\/blockquote>\n<ol style=\"text-align: justify\" start=\"6\">\n<li><strong> S\u1eed d\u1ee5ng h\u00e0m <em>INDEX<\/em> \u0111\u1ec3 l\u1ea5y 1 d\u1ea3i t\u1eeb danh s\u00e1ch c\u00e1c d\u1ea3i<\/strong>:<\/li>\n<\/ol>\n<p style=\"text-align: justify\">M\u1ed9t c\u00e1ch s\u1eed d\u1ee5ng th\u00f4ng minh v\u00e0 h\u1eefu hi\u1ec7u h\u00e0m INDEX trong Excel l\u00e0 b\u1ea1n c\u00f3 th\u1ec3 c\u00f3 \u0111\u01b0\u1ee3c m\u1ed9t d\u1ea3i t\u1eeb m\u1ed9t danh s\u00e1ch c\u00e1c d\u1ea3i.<\/p>\n<p style=\"text-align: justify\">Gi\u1ea3 s\u1eed, b\u1ea1n c\u00f3 nhi\u1ec1u danh s\u00e1ch v\u1edbi s\u1ed1 l\u01b0\u1ee3ng c\u00e1c m\u1ee5c kh\u00e1c nhau.<\/p>\n<p style=\"text-align: justify\">Tr\u01b0\u1edbc h\u1ebft, b\u1ea1n \u00a0\u0111\u1eb7t t\u00ean cho m\u1ed7i d\u1ea3i trong m\u1ed7i danh s\u00e1ch; V\u00ed nh\u01b0 c\u00f3 th\u1ec3 \u0111\u1eb7t l\u00e0 <strong><em>PlanetsD<\/em><\/strong> v\u00e0 <strong><em>MoonsD<\/em><\/strong> trong v\u00ed d\u1ee5 n\u00e0y:<\/p>\n<p style=\"text-align: justify\"><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/named-ranges.png\" alt=\"The named ranges to be use in the INDEX formula\" title=\"named-ranges\"><\/p>\n<p style=\"text-align: justify\">Tuy nhi\u00ean, b\u1ea3ng <strong><em>Moons<\/em><\/strong> c\u00f2n ch\u01b0a ho\u00e0n ch\u1ec9nh, c\u00f3 176 m\u1eb7t tr\u0103ng t\u1ef1 nhi\u00ean \u0111\u01b0\u1ee3c bi\u1ebft \u0111\u1ebfn trong H\u1ec7 M\u1eb7t tr\u1eddi c\u1ee7a ch\u00fang ta, Jupiter c\u00f3 63 v\u00e0 v\u1eabn c\u00f2n nhi\u1ec1u h\u01a1n n\u1eefa. \u0110\u1ed1i v\u1edbi v\u00ed d\u1ee5 n\u00e0y, t\u00f4i ch\u1ecdn 11 c\u00e1i ng\u1eabu nhi\u00ean.<\/p>\n<p style=\"text-align: justify\">Gi\u1ea3 s\u1eed r\u1eb1ng <strong><em>PlanetsD<\/em><\/strong> l\u00e0 d\u1ea3i 1 c\u1ee7a b\u1ea1n <strong><em>v\u00e0 MoonsD<\/em><\/strong> n\u1eb1m trong d\u1ea3i 2 v\u00e0 \u00f4 B1 l\u00e0 n\u01a1i b\u1ea1n \u0111\u1eb7t s\u1ed1 d\u00e3y, b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c Index sau \u0111\u1ec3 t\u00ednh gi\u00e1 tr\u1ecb trung b\u00ecnh c\u1ee7a c\u00e1c gi\u00e1 tr\u1ecb trong d\u1ea3i \u00f4 \u0111\u01b0\u1ee3c ch\u1ecdn:<\/p>\n<p style=\"text-align: justify\"><strong><em>= AVERAGE (INDEX ((PlanetsD, MoonsD),,, B1))<\/em><\/strong><\/p>\n<p style=\"text-align: justify\">H\u00e3y l\u01b0u \u00fd r\u1eb1ng b\u00e2y gi\u1edd ch\u00fang ta \u0111ang s\u1eed d\u1ee5ng \u0111\u1ecbnh d\u1ea1ng tham chi\u1ebfu c\u1ee7a h\u00e0m INDEX, v\u00e0 s\u1ed1 trong \u0111\u1ed1i s\u1ed1 cu\u1ed1i c\u00f9ng (area_num) cho c\u00f4ng th\u1ee9c bi\u1ebft ph\u1ea1m vi d\u1ea3i n\u00e0o \u0111\u01b0\u1ee3c l\u1ef1a ch\u1ecdn.<\/p>\n<p style=\"text-align: justify\">Trong h\u00ecnh b\u00ean d\u01b0\u1edbi, area_num (cell B1) \u0111\u01b0\u1ee3c \u0111\u1eb7t th\u00e0nh 2, trong c\u00f4ng th\u1ee9c t\u00ednh \u0111\u01b0\u1eddng k\u00ednh trung b\u00ecnh c\u1ee7a Moons v\u00ec <strong><em>d\u1ea3i MoonsD<\/em><\/strong> \u0111\u1ee9ng th\u1ee9 2 trong \u0111\u1ed1i s\u1ed1 tham chi\u1ebfu.<\/p>\n<p style=\"text-align: justify\"><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-index-ranges.png\" alt=\"The INDEX formula to get one range from a list of ranges\" title=\"excel-index-ranges\"><\/p>\n<p style=\"text-align: justify\">N\u1ebfu b\u1ea1n l\u00e0m vi\u1ec7c v\u1edbi nhi\u1ec1u danh s\u00e1ch v\u00e0 kh\u00f4ng mu\u1ed1n nh\u1edb c\u00e1c s\u1ed1 li\u00ean quan, b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng th\u00eam h\u00e0m IF \u0111\u1ec3 l\u00e0m \u0111i\u1ec1u n\u00e0y cho b\u1ea1n:<\/p>\n<p style=\"text-align: justify\"><strong><em>= AVERAGE (INDEX ((PlanetsD, MoonsD),,, IF (B1 = \u201cplanets\u201d, 1, IF (B1 = \u201cmoon\u201d, 2))))<\/em><\/strong><\/p>\n<p style=\"text-align: justify\">Trong h\u00e0m <strong><em>IF<\/em><\/strong>, b\u1ea1n s\u1eed d\u1ee5ng m\u1ed9t s\u1ed1 t\u00ean danh s\u00e1ch \u0111\u01a1n gi\u1ea3n v\u00e0 d\u1ec5 nh\u1edb m\u00e0 b\u1ea1n mu\u1ed1n ng\u01b0\u1eddi d\u00f9ng nh\u1eadp v\u00e0o \u00f4 B1 thay v\u00ec s\u1ed1. Xin l\u01b0u \u00fd r\u1eb1ng, \u0111\u1ec3 c\u00f4ng th\u1ee9c l\u00e0m vi\u1ec7c ch\u00ednh x\u00e1c, v\u0103n b\u1ea3n trong B1 ph\u1ea3i ch\u00ednh x\u00e1c nh\u01b0 nhau (kh\u00f4ng ph\u00e2n bi\u1ec7t ch\u1eef hoa ch\u1eef th\u01b0\u1eddng) nh\u01b0 trong c\u00e1c th\u00f4ng s\u1ed1 c\u1ee7a <strong><em>IF<\/em><\/strong>, n\u1ebfu kh\u00f4ng c\u00f4ng th\u1ee9c Index c\u1ee7a b\u1ea1n s\u1ebd xu\u1ea5t ra <strong><em>#VALUE <\/em><\/strong>l\u1ed7i.<\/p>\n<p style=\"text-align: justify\">Th\u1eadm ch\u00ed, \u0111\u1ec3 c\u00f4ng th\u1ee9c th\u00e2n thi\u1ec7n h\u01a1n, b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng <strong><em>Excel Data Validation <\/em><\/strong>\u0111\u1ec3 t\u1ea1o danh s\u00e1ch t\u00f9y ch\u1ecdn \u0111\u1ec3 tr\u00e1nh l\u1ed7i ch\u00ednh t\u1ea3 v\u00e0 l\u1ed7i trang in:<\/p>\n<p style=\"text-align: justify\"><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-index-ranges-if.png\" alt=\"A user-friendly INDEX \/ IF formula to get 1 range from a list of ranges\" title=\"excel-index-ranges-if\"><\/p>\n<p style=\"text-align: justify\">Cu\u1ed1i c\u00f9ng, \u0111\u1ec3 l\u00e0m cho c\u00f4ng th\u1ee9c <strong><em>INDEX <\/em><\/strong>c\u1ee7a b\u1ea1n ho\u00e0n to\u00e0n ho\u00e0n h\u1ea3o, b\u1ea1n c\u00f3 th\u1ec3 \u0111\u1eb7t n\u00f3 trong h\u00e0m IFERROR s\u1ebd nh\u1eafc ng\u01b0\u1eddi d\u00f9ng ch\u1ecdn m\u1ed9t m\u1ee5c t\u1eeb danh s\u00e1ch theo m\u1ee5c, n\u1ebfu kh\u00f4ng c\u00f3 l\u1ef1a ch\u1ecdn n\u00e0o \u0111\u01b0\u1ee3c \u0111\u01b0a ra:<\/p>\n<p style=\"text-align: justify\"><strong><em>= IFERROR (AVERAGE (INDEX ((PlanetsD, MoonsD),,, IF (B1 = \u201cplanet\u201d, 1, IF (B1 = \u201cmoon\u201d, 2)))), \u201cPlease select the list!\u201d)<\/em><\/strong><\/p>\n<p style=\"text-align: justify\">Tr\u00ean \u0111\u00e2y l\u00e0 h\u01b0\u1edbng d\u1eabn c\u00e1ch b\u1ea1n s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c <strong><em>INDEX<\/em><\/strong> trong Excel. T\u00f4i hy v\u1ecdng nh\u1eefng v\u00ed d\u1ee5 n\u00e0y cho b\u1ea1n v\u00e0i c\u00e1ch \u0111\u1ec3 khai th\u00e1c ti\u1ec1m n\u0103ng c\u1ee7a h\u00e0m <strong><em>INDEX<\/em><\/strong> trong Excel trong b\u1ea3ng t\u00ednh c\u1ee7a b\u1ea1n. C\u1ea3m \u01a1n b\u1ea1n \u0111\u00e3 \u0111\u1ecdc!<\/p>\n<p>\u0110\u1ec3 c\u00f3 th\u1ec3 \u1ee9ng d\u1ee5ng t\u1ed1t Excel v\u00e0o trong c\u00f4ng vi\u1ec7c, ch\u00fang ta kh\u00f4ng ch\u1ec9 n\u1eafm v\u1eefng \u0111\u01b0\u1ee3c c\u00e1c h\u00e0m m\u00e0 c\u00f2n ph\u1ea3i s\u1eed d\u1ee5ng t\u1ed1t c\u1ea3 c\u00e1c c\u00f4ng c\u1ee5 c\u1ee7a Excel. Nh\u1eefng h\u00e0m n\u00e2ng cao gi\u00fap \u00e1p d\u1ee5ng t\u1ed1t v\u00e0o c\u00f4ng vi\u1ec7c nh\u01b0 SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH\u2026 Nh\u1eefng c\u00f4ng c\u1ee5 th\u01b0\u1eddng s\u1eed d\u1ee5ng l\u00e0 Data validation, Conditional formatting, Pivot table\u2026<\/p>\n<p>To\u00e0n b\u1ed9 nh\u1eefng ki\u1ebfn th\u1ee9c n\u00e0y c\u00e1c b\u1ea1n \u0111\u1ec1u c\u00f3 th\u1ec3 h\u1ecdc \u0111\u01b0\u1ee3c trong kh\u00f3a h\u1ecdc:<\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/khoa-hoc\/excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\" target=\"_blank\" rel=\"noopener\">EX101 \u2013 Excel t\u1eeb c\u01a1 b\u1ea3n \u0111\u1ebfn n\u00e2ng cao d\u00e0nh cho ng\u01b0\u1eddi \u0111i l\u00e0m<\/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\/vba101-tu-dong-hoa-excel-voi-lap-trinh-vba-cho-nguoi-moi-bat-dau\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/vba101_92019-nho-61.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":1983,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-1982","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\/1982","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=1982"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/1982\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/1983"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=1982"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=1982"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=1982"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}