{"id":7374,"date":"2024-12-21T17:27:18","date_gmt":"2024-12-21T17:27:18","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/21\/cac-vi-du-cong-thu-mang-cho-excel-danh-cho-nguoi-moi-bat-dau-va-nguoi-dung-muon-tim-hieu-sau\/"},"modified":"2024-12-21T17:27:18","modified_gmt":"2024-12-21T17:27:18","slug":"cac-vi-du-cong-thu-mang-cho-excel-danh-cho-nguoi-moi-bat-dau-va-nguoi-dung-muon-tim-hieu-sau","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/21\/cac-vi-du-cong-thu-mang-cho-excel-danh-cho-nguoi-moi-bat-dau-va-nguoi-dung-muon-tim-hieu-sau\/","title":{"rendered":"C\u00e1c v\u00ed d\u1ee5 c\u00f4ng th\u1eed m\u1ea3ng cho Excel d\u00e0nh cho ng\u01b0\u1eddi m\u1edbi b\u1eaft \u0111\u1ea7u v\u00e0 ng\u01b0\u1eddi d\u00f9ng mu\u1ed1n t\u00ecm hi\u1ec3u s\u00e2u"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-dQf5b79p#1\">\n<p style=\"text-align: justify\"><a href=\"http:\/\/blog.ezworkapp.com\/cac-ham-cong-thuc-va-nhung-hang-mang-trong-excel-vi-du-va-huong-dan-su-dung.html\"><strong>C\u00f4ng th\u1ee9c m\u1ea3ng Excel<\/strong><\/a> l\u00e0 m\u1ed9t trong nh\u1eefng t\u00ednh n\u0103ng kh\u00f3 hi\u1ec3u nh\u1ea5t trong Excel, nh\u01b0ng d\u01b0\u1eddng nh\u01b0 l\u1ea1i l\u00e0 m\u1ed9t trong nh\u1eefng \u0111i\u1ec1u th\u00fa v\u1ecb v\u00e0 h\u1ea5p d\u1eabn nh\u1ea5t.<\/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-6766fa74f1daa\" 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\/cac-vi-du-cong-thu-mang-cho-excel-danh-cho-nguoi-moi-bat-dau-va-nguoi-dung-muon-tim-hieu-sau.html#Vi_du_1_Dem_cac_o_thoa_man_cac_dieu_kien_nhat_dinh\" title=\"V\u00ed d\u1ee5 1. \u0110\u1ebfm c\u00e1c \u00f4 th\u1ecfa m\u00e3n c\u00e1c \u0111i\u1ec1u ki\u1ec7n nh\u1ea5t \u0111\u1ecbnh\">V\u00ed d\u1ee5 1. \u0110\u1ebfm c\u00e1c \u00f4 th\u1ecfa m\u00e3n c\u00e1c \u0111i\u1ec1u ki\u1ec7n nh\u1ea5t \u0111\u1ecbnh<\/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\/cac-vi-du-cong-thu-mang-cho-excel-danh-cho-nguoi-moi-bat-dau-va-nguoi-dung-muon-tim-hieu-sau.html#Vi_du_2_Su_dung_mot_so_ham_trong_cong_thuc_mang_Excel\" title=\"V\u00ed d\u1ee5 2. S\u1eed d\u1ee5ng m\u1ed9t s\u1ed1 h\u00e0m trong c\u00f4ng th\u1ee9c m\u1ea3ng Excel\">V\u00ed d\u1ee5 2. S\u1eed d\u1ee5ng m\u1ed9t s\u1ed1 h\u00e0m trong c\u00f4ng th\u1ee9c m\u1ea3ng Excel<\/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\/cac-vi-du-cong-thu-mang-cho-excel-danh-cho-nguoi-moi-bat-dau-va-nguoi-dung-muon-tim-hieu-sau.html#Vi_du_3_Cong_thuc_mang_%E2%80%93_dem_tat_ca_cac_ky_tu_trong_mot_dai_nhat_dinh\" title=\"V\u00ed d\u1ee5 3. C\u00f4ng th\u1ee9c m\u1ea3ng \u2013 \u0111\u1ebfm t\u1ea5t c\u1ea3 c\u00e1c k\u00fd t\u1ef1 trong m\u1ed9t d\u1ea3i nh\u1ea5t \u0111\u1ecbnh\">V\u00ed d\u1ee5 3. C\u00f4ng th\u1ee9c m\u1ea3ng \u2013 \u0111\u1ebfm t\u1ea5t c\u1ea3 c\u00e1c k\u00fd t\u1ef1 trong m\u1ed9t d\u1ea3i nh\u1ea5t \u0111\u1ecbnh<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/blog.hocexcel.online\/cac-vi-du-cong-thu-mang-cho-excel-danh-cho-nguoi-moi-bat-dau-va-nguoi-dung-muon-tim-hieu-sau.html#Vi_du_4_Cong_thuc_mang_de_tinh_bat_ky_cac_ky_tu_nao_trong_dai\" title=\"V\u00ed d\u1ee5 4. C\u00f4ng th\u1ee9c m\u1ea3ng \u0111\u1ec3 t\u00ednh b\u1ea5t k\u1ef3 (c\u00e1c) k\u00fd t\u1ef1 n\u00e0o trong d\u1ea3i\">V\u00ed d\u1ee5 4. C\u00f4ng th\u1ee9c m\u1ea3ng \u0111\u1ec3 t\u00ednh b\u1ea5t k\u1ef3 (c\u00e1c) k\u00fd t\u1ef1 n\u00e0o trong d\u1ea3i<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/blog.hocexcel.online\/cac-vi-du-cong-thu-mang-cho-excel-danh-cho-nguoi-moi-bat-dau-va-nguoi-dung-muon-tim-hieu-sau.html#Vi_du_5_Cac_gia_tri_tong_hop_trong_moi_hang_hoac_cho_hang_thu_N\" title=\"V\u00ed d\u1ee5 5. C\u00e1c gi\u00e1 tr\u1ecb t\u1ed5ng h\u1ee3p trong m\u1ed7i h\u00e0ng ho\u1eb7c cho h\u00e0ng th\u1ee9 N\">V\u00ed d\u1ee5 5. C\u00e1c gi\u00e1 tr\u1ecb t\u1ed5ng h\u1ee3p trong m\u1ed7i h\u00e0ng ho\u1eb7c cho h\u00e0ng th\u1ee9 N<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/blog.hocexcel.online\/cac-vi-du-cong-thu-mang-cho-excel-danh-cho-nguoi-moi-bat-dau-va-nguoi-dung-muon-tim-hieu-sau.html#Vi_du_6_Thuc_hien_tinh_toan_khac_nhau_tren_cac_con_so_trong_dai_khac_nhau\" title=\"V\u00ed d\u1ee5 6. Th\u1ef1c hi\u1ec7n t\u00ednh to\u00e1n kh\u00e1c nhau tr\u00ean c\u00e1c con s\u1ed1 trong d\u1ea3i kh\u00e1c nhau\">V\u00ed d\u1ee5 6. Th\u1ef1c hi\u1ec7n t\u00ednh to\u00e1n kh\u00e1c nhau tr\u00ean c\u00e1c con s\u1ed1 trong d\u1ea3i kh\u00e1c nhau<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/blog.hocexcel.online\/cac-vi-du-cong-thu-mang-cho-excel-danh-cho-nguoi-moi-bat-dau-va-nguoi-dung-muon-tim-hieu-sau.html#Vi_du_7_Cac_ham_do_nguoi_dung_dinh_nghia_trong_cong_thuc_mang_Excel\" title=\"V\u00ed d\u1ee5 7. C\u00e1c h\u00e0m do ng\u01b0\u1eddi d\u00f9ng \u0111\u1ecbnh ngh\u0129a trong c\u00f4ng th\u1ee9c m\u1ea3ng Excel\">V\u00ed d\u1ee5 7. C\u00e1c h\u00e0m do ng\u01b0\u1eddi d\u00f9ng \u0111\u1ecbnh ngh\u0129a trong c\u00f4ng th\u1ee9c m\u1ea3ng Excel<\/a><\/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\/cac-vi-du-cong-thu-mang-cho-excel-danh-cho-nguoi-moi-bat-dau-va-nguoi-dung-muon-tim-hieu-sau.html#Cong_thuc_mang_Excel_%E2%80%93_nhung_han_che_va_cac_phuong_an_thay_the\" title=\"C\u00f4ng th\u1ee9c m\u1ea3ng Excel \u2013 nh\u1eefng h\u1ea1n ch\u1ebf v\u00e0 c\u00e1c ph\u01b0\u01a1ng \u00e1n thay th\u1ebf\">C\u00f4ng th\u1ee9c m\u1ea3ng Excel \u2013 nh\u1eefng h\u1ea1n ch\u1ebf v\u00e0 c\u00e1c ph\u01b0\u01a1ng \u00e1n thay th\u1ebf<\/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-9\" href=\"https:\/\/blog.hocexcel.online\/cac-vi-du-cong-thu-mang-cho-excel-danh-cho-nguoi-moi-bat-dau-va-nguoi-dung-muon-tim-hieu-sau.html#1_Nhung_mang_lon_co_the_lam_cham_Excel\" title=\"1. Nh\u1eefng m\u1ea3ng l\u1edbn c\u00f3 th\u1ec3 l\u00e0m ch\u1eadm Excel:\">1. Nh\u1eefng m\u1ea3ng l\u1edbn c\u00f3 th\u1ec3 l\u00e0m ch\u1eadm Excel:<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/blog.hocexcel.online\/cac-vi-du-cong-thu-mang-cho-excel-danh-cho-nguoi-moi-bat-dau-va-nguoi-dung-muon-tim-hieu-sau.html#2_Khong_duoc_phep_chon_cac_mang_hoan_toan_chi_co_cot\" title=\"2. Kh\u00f4ng \u0111\u01b0\u1ee3c ph\u00e9p ch\u1ecdn c\u00e1c m\u1ea3ng ho\u00e0n to\u00e0n ch\u1ec9 c\u00f3 c\u1ed9t.\">2. Kh\u00f4ng \u0111\u01b0\u1ee3c ph\u00e9p ch\u1ecdn c\u00e1c m\u1ea3ng ho\u00e0n to\u00e0n ch\u1ec9 c\u00f3 c\u1ed9t.<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-11\" href=\"https:\/\/blog.hocexcel.online\/cac-vi-du-cong-thu-mang-cho-excel-danh-cho-nguoi-moi-bat-dau-va-nguoi-dung-muon-tim-hieu-sau.html#3_Gioi_han_cua_cac_cong_thuc_mang_khi_tham_chieu_den_1_trang_tinh_khac\" title=\"3. Gi\u1edbi h\u1ea1n c\u1ee7a c\u00e1c c\u00f4ng th\u1ee9c m\u1ea3ng khi tham chi\u1ebfu \u0111\u1ebfn 1 trang t\u00ednh kh\u00e1c:\">3. Gi\u1edbi h\u1ea1n c\u1ee7a c\u00e1c c\u00f4ng th\u1ee9c m\u1ea3ng khi tham chi\u1ebfu \u0111\u1ebfn 1 trang t\u00ednh kh\u00e1c:<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-12\" href=\"https:\/\/blog.hocexcel.online\/cac-vi-du-cong-thu-mang-cho-excel-danh-cho-nguoi-moi-bat-dau-va-nguoi-dung-muon-tim-hieu-sau.html#4_Go_loi_cho_cac_cong_thuc_mang\" title=\"4. G\u1ee1 l\u1ed7i cho c\u00e1c c\u00f4ng th\u1ee9c m\u1ea3ng\">4. G\u1ee1 l\u1ed7i cho c\u00e1c c\u00f4ng th\u1ee9c m\u1ea3ng<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-13\" href=\"https:\/\/blog.hocexcel.online\/cac-vi-du-cong-thu-mang-cho-excel-danh-cho-nguoi-moi-bat-dau-va-nguoi-dung-muon-tim-hieu-sau.html#5_Cac_lua_chon_thay_the_cho_cac_cong_thuc_mang\" title=\"5. C\u00e1c l\u1ef1a ch\u1ecdn thay th\u1ebf cho c\u00e1c c\u00f4ng th\u1ee9c m\u1ea3ng.\">5. C\u00e1c l\u1ef1a ch\u1ecdn thay th\u1ebf cho c\u00e1c c\u00f4ng th\u1ee9c m\u1ea3ng.<\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"Vi_du_1_Dem_cac_o_thoa_man_cac_dieu_kien_nhat_dinh\"><\/span>V\u00ed d\u1ee5 1. \u0110\u1ebfm c\u00e1c \u00f4 th\u1ecfa m\u00e3n c\u00e1c \u0111i\u1ec1u ki\u1ec7n nh\u1ea5t \u0111\u1ecbnh<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p style=\"text-align: justify\">Vi\u1ec7c gi\u1edbi thi\u1ec7u c\u00e1c <a href=\"http:\/\/blog.ezworkapp.com\/ham-sumif-trong-excel-ham-tinh-tong-theo-mot-dieu-kien-cho-truoc.html\" target=\"_blank\" rel=\"noopener\">h\u00e0m SUMIF<\/a> v\u00e0 <a href=\"http:\/\/blog.ezworkapp.com\/cach-dung-ham-countifs-va-countif-voi-nhieu-dieu-kien.html\" target=\"_blank\" rel=\"noopener\">COUNTIF<\/a> c\u0169ng gi\u1ed1ng nh\u01b0 \u0111a ph\u1ea7n c\u00e1c \u0111\u1ed1i t\u01b0\u1ee3ng kh\u00e1c trong Excel 2007, \u0111\u1ec1u kh\u00f4ng th\u1ec3 lu\u00f4n ph\u00f9 h\u1ee3p v\u1edbi m\u1ecdi tr\u01b0\u1eddng h\u1ee3p. Tuy nhi\u00ean, v\u1eabn c\u00f3 nh\u1eefng nhi\u1ec7m v\u1ee5 m\u00e0 c\u00f4ng th\u1ee9c m\u1ea3ng l\u00e0 gi\u1ea3i ph\u00e1p c\u00f3 hi\u1ec7u qu\u1ea3 nh\u1ea5t\u00a0 v\u1eabn c\u00f3 c\u00e1c l\u1ef1a ch\u1ecdn thay th\u1ebf kh\u00e1c.<\/p>\n<p style=\"text-align: justify\">Gi\u1ea3 s\u1eed b\u1ea1n c\u00f3 2 c\u1ed9t s\u1ed1, c\u1ed9t A (\u0111\u00e3 l\u00ean k\u1ebf ho\u1ea1ch \u2013 planned) v\u00e0 c\u1ed9t B (th\u1ef1c t\u1ebf). V\u00e0 b\u1ea1n mu\u1ed1n bi\u1ebft c\u1ed9t B l\u1edbn h\u01a1n g\u1ea5p bao nhi\u00eau l\u1ea7n so v\u1edbi c\u1ed9t A, khi m\u1ed9t gi\u00e1 tr\u1ecb trong c\u1ed9t B l\u1edbn h\u01a1n 0.<\/p>\n<p style=\"text-align: justify\">Nhi\u1ec7m v\u1ee5 n\u00e0y \u0111\u00f2i h\u1ecfi ph\u1ea3i so s\u00e1nh hai d\u00e3y v\u00e0 r\u1ea5t c\u1ea7n c\u00f4ng th\u1ee9c m\u1ea3ng Excel \u1edf \u0111\u00e2y.<\/p>\n<p style=\"text-align: justify\">V\u00ec v\u1eady, b\u1ea1n th\u1ec3 hi\u1ec7n c\u00e1c \u0111i\u1ec1u ki\u1ec7n nh\u01b0 (B2: B10&gt; = A2: A10) v\u00e0 (B2: B10&gt; 0), n\u1ed1i ch\u00fang b\u1eb1ng d\u1ea5u hoa th\u1ecb (*) ho\u1ea1t \u0111\u1ed9ng nh\u01b0 h\u00e0m AND trong c\u00e1c c\u00f4ng th\u1ee9c m\u1ea3ng, v\u00e0 \u0111\u01b0a ch\u00fang v\u00e0o trong \u0111\u1ed1i s\u1ed1 c\u1ee7a h\u00e0m SUM:<\/p>\n<p style=\"text-align: justify\"><strong>= SUM ((B2: B10&gt; = A2: A10) * (B2: B10&gt; 0))<\/strong><\/p>\n<p style=\"text-align: justify\"><strong>\u00a0<img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/array-formula-compare-ranges.png\" alt=\"An Excel array formula to count cells that meet two conditions\" title=\"array-formula-compare-ranges\"><\/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-294.gif\" title=\"vba101_92019-nho-294\"><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-663.gif\" title=\"ex101_92019-nho-663\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p style=\"text-align: justify\">V\u00e0 h\u00e3y nh\u1edb nh\u1ea5n Ctrl + Shift + Enter \u0111\u1ec3 nh\u1eadp ch\u00ednh x\u00e1c m\u1ea3ng trong Excel!<\/p>\n<p style=\"text-align: justify\">Hai \u0111o\u1ea1n ti\u1ebfp theo d\u00e0nh cho nh\u1eefng ai t\u00ecm hi\u1ec3u s\u00e2u h\u01a1n v\u1ec1 c\u00f4ng ngh\u1ec7. N\u1ebfu b\u1ea1n kh\u00f4ng quan t\u00e2m, th\u00ec b\u1ea1n c\u00f3 th\u1ec3 b\u1ecf qua v\u00e0 \u0111i t\u1edbi v\u00ed d\u1ee5 c\u00f4ng th\u1ee9c ti\u1ebfp theo.<\/p>\n<p style=\"text-align: justify\">\u0110\u1ec3 hi\u1ec3u r\u00f5 h\u01a1n v\u1ec1 c\u00f4ng th\u1ee9c n\u00e0y n\u00f3i ri\u00eang v\u00e0 c\u00e1c c\u00f4ng th\u1ee9c m\u1ea3ng Excel n\u00f3i chung, h\u00e3y ch\u1ecdn hai bi\u1ec3u th\u1ee9c b\u00ean trong ngo\u1eb7c \u0111\u01a1n c\u1ee7a h\u00e0m SUM trong thanh c\u00f4ng th\u1ee9c, r\u1ed3i nh\u1ea5n F9 \u0111\u1ec3 xem c\u00e1c m\u1ea3ng trong th\u00e0nh ph\u1ea7n c\u1ee7a c\u00f4ng th\u1ee9c.<\/p>\n<p style=\"text-align: justify\">V\u00ec v\u1eady, nh\u1eefng g\u00ec ch\u00fang ta c\u00f3 \u1edf \u0111\u00e2y l\u00e0 hai m\u1ea3ng c\u00e1c gi\u00e1 tr\u1ecb Boolean, trong \u0111\u00f3 TRUE t\u01b0\u01a1ng \u0111\u01b0\u01a1ng v\u1edbi 1 v\u00e0 FALSE t\u01b0\u01a1ng \u0111\u01b0\u01a1ng v\u1edbi 0. Do ch\u00fang ta \u0111ang s\u1eed d\u1ee5ng to\u00e1n t\u1eed AND () trong c\u00f4ng th\u1ee9c, n\u00ean h\u00e0m SUM s\u1ebd ch\u1ec9 th\u00eam c\u00e1c h\u00e0ng c\u00f3 gi\u00e1 tr\u1ecb TRUE (1) trong c\u1ea3 hai m\u1ea3ng, nh\u01b0 trong \u1ea3nh ch\u1ee5p m\u00e0n h\u00ecnh d\u01b0\u1edbi \u0111\u00e2y:<\/p>\n<p style=\"text-align: justify\"><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/evaluating-array-formula.png\" alt=\"Select portions of the formula and press F9 to view the arrays behind the formula parts.\" title=\"evaluating-array-formula\"><\/p>\n<p style=\"text-align: justify\">Ch\u00fa \u00fd: Kh\u00f4ng ph\u1ea3i t\u1ea5t c\u1ea3 c\u00e1c h\u00e0m Excel h\u1ed7 tr\u1ee3 c\u00e1c m\u1ea3ng c\u00f3 th\u1ec3 chuy\u1ec3n \u0111\u1ed5i TRUE v\u00e0 FALSE th\u00e0nh 1 v\u00e0 0. Trong c\u00e1c c\u00f4ng th\u1ee9c m\u1ea3ng ph\u1ee9c t\u1ea1p h\u01a1n, th\u00ec b\u1ea1n c\u00f3 th\u1ec3 c\u1ea7n ph\u1ea3i s\u1eed d\u1ee5ng d\u1ea5u g\u1ea1ch \u0111\u00f4i (\u2013), \u0111\u01b0\u1ee3c g\u1ecdi l\u00e0 to\u00e1n t\u1eed \u0111\u00f4i, \u0111\u1ec3 chuy\u1ec3n \u0111\u1ed5i c\u00e1c s\u1ed1 kh\u00f4ng ph\u1ea3i l\u00e0 gi\u00e1 tr\u1ecb Boolean v\u1edbi s\u1ed1.<\/p>\n<p style=\"text-align: justify\">Bao g\u1ed3m 2 d\u1ea5u g\u1ea1ch ngang trong c\u00f4ng th\u1ee9c tr\u00ean s\u1ebd kh\u00f4ng l\u00e0m \u1ea3nh h\u01b0\u1edfng g\u00ec c\u1ea3, m\u00e0 n\u00f3 s\u1ebd ch\u1ec9 gi\u1eef c\u00f4ng th\u1ee9c b\u1ea1n an to\u00e0n h\u01a1n: = SUM (- (B2: B10&gt; = A2: A10) * (B2: B10&gt; 0))<\/p>\n<p style=\"text-align: justify\">V\u00e0 \u0111\u00e2y l\u00e0 m\u1ed9t v\u00ed d\u1ee5 c\u00f4ng th\u1ee9c m\u1ea3ng Excel ph\u1ee9c t\u1ea1p h\u01a1n m\u00e0 ho\u00e0n to\u00e0n y\u00eau c\u1ea7u s\u1eed d\u1ee5ng to\u00e1n t\u1eed \u0111\u01a1n v\u1ecb.<\/p>\n<blockquote>\n<p style=\"text-align: justify\">\u0110\u1eebng v\u1ed9i b\u1ecf qua:\u00a0<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=\"Vi_du_2_Su_dung_mot_so_ham_trong_cong_thuc_mang_Excel\"><\/span>V\u00ed d\u1ee5 2. S\u1eed d\u1ee5ng m\u1ed9t s\u1ed1 h\u00e0m trong c\u00f4ng th\u1ee9c m\u1ea3ng Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p style=\"text-align: justify\">C\u00e1c c\u00f4ng th\u1ee9c m\u1ea3ng c\u00f3 th\u1ec3 l\u00e0m vi\u1ec7c v\u1edbi m\u1ed9t s\u1ed1 h\u00e0m Excel t\u1ea1i m\u1ed9t th\u1eddi \u0111i\u1ec3m v\u00e0 th\u1ef1c hi\u1ec7n nhi\u1ec1u ph\u00e9p t\u00ednh trong m\u1ed9t c\u00f4ng th\u1ee9c.<\/p>\n<p style=\"text-align: justify\">V\u00ed d\u1ee5: n\u1ebfu b\u1ea1n c\u00f3 m\u1ed9t b\u1ea3ng li\u1ec7t k\u00ea nhi\u1ec1u s\u1ea3n ph\u1ea9m b\u00e1n h\u00e0ng c\u1ee7a m\u1ed9t s\u1ed1 nh\u00e2n vi\u00ean b\u00e1n h\u00e0ng v\u00e0 b\u1ea1n mu\u1ed1n bi\u1ebft doanh s\u1ed1 b\u00e1n h\u00e0ng t\u1ed1i \u0111a c\u1ee7a m\u1ed9t ng\u01b0\u1eddi c\u1ee5 th\u1ec3 cho m\u1ed9t s\u1ea3n ph\u1ea9m nh\u1ea5t \u0111\u1ecbnh, b\u1ea1n c\u00f3 th\u1ec3 vi\u1ebft m\u1ed9t c\u00f4ng th\u1ee9c m\u1ea3ng d\u1ef1a tr\u00ean m\u1eabu sau:<\/p>\n<p style=\"text-align: justify\"><strong>= MAX (IF ((salesmen_range = \u201cname\u201d) * (product_range = \u201cname\u201d), sales_range, \u201c\u201d))<\/strong><\/p>\n<p style=\"text-align: justify\">Gi\u1ea3 s\u1eed r\u1eb1ng t\u00ean c\u1ee7a nh\u00e2n vi\u00ean b\u00e1n h\u00e0ng n\u1eb1m trong c\u1ed9t A, t\u00ean s\u1ea3n ph\u1ea9m n\u1eb1m trong c\u1ed9t B v\u00e0 doanh s\u1ed1 b\u00e1n h\u00e0ng n\u1eb1m trong c\u1ed9t C, th\u00ec \u0111\u1ec3 t\u00ednh doanh s\u1ed1 b\u00e1n t\u00e1o (apples) nhi\u1ec1u nh\u1ea5t m\u00e0 Mike c\u00f3 th\u1ec3 b\u00e1n, s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c:<\/p>\n<p style=\"text-align: justify\"><strong>= MAX (IF (($ A $ 2: $ A $ 9 = \u201cmike\u201d) * ($ B $ 2: $ B $ 9 = \u201capples\u201d), $ C $ 2: $ C $ 9, \u201c\u201d))<\/strong><\/p>\n<p style=\"text-align: justify\">\u0110\u01b0\u01a1ng nhi\u00ean, b\u1ea1n c\u00f3 th\u1ec3 thay th\u1ebf c\u00e1c t\u00ean trong c\u00f4ng th\u1ee9c b\u1eb1ng c\u00e1c tham chi\u1ebfu \u00f4 \u0111\u1ec3 ng\u01b0\u1eddi d\u00f9ng c\u1ee7a b\u1ea1n ch\u1ec9 c\u1ea7n g\u00f5 t\u00ean v\u00e0o c\u00e1c \u00f4 nh\u1ea5t \u0111\u1ecbnh m\u00e0 kh\u00f4ng s\u1eeda \u0111\u1ed5i c\u00f4ng th\u1ee9c m\u1ea3ng c\u1ee7a b\u1ea1n:<\/p>\n<p style=\"text-align: justify\"><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/array-formula-several-functions.png\" alt=\"Using several functions in Excel array formulas\" title=\"array-formula-several-functions\"><\/p>\n<p style=\"text-align: justify\">Trong h\u00ecnh \u1edf tr\u00ean c\u00f3 s\u1eed d\u1ee5ng c\u00e1c c\u00f4ng th\u1ee9c m\u1ea3ng trong Excel (v\u00e0 \u0111\u1eebng qu\u00ean nh\u1ea5n Ctrl + Shift + Enter \u0111\u1ec3 nh\u1eadp ch\u00ednh x\u00e1c nh\u00e9):<\/p>\n<p style=\"text-align: justify\"><strong><em>Gi\u00e1 tr\u1ecb l\u1edbn nh\u1ea5t:\u00a0<\/em><\/strong><strong><em>=MAX(IF(($A$2:$A$9=$F$1) * ($B$2:$B$9=$F$2), $C$2:$C$9,\u201d\u201d))<\/em><\/strong><\/p>\n<p style=\"text-align: justify\"><strong><em>Gi\u00e1 tr\u1ecb nh\u1ecf nh\u1ea5t:\u00a0<\/em><\/strong><strong><em>=MIN(IF(($A$2:$A$9=$F$1) * ($B$2:$B$9=$F$2), $C$2:$C$9,\u201d\u201d))<\/em><\/strong><\/p>\n<p style=\"text-align: justify\"><strong><em>Gi\u00e1 tr\u1ecb trung b\u00ecnh:\u00a0<\/em><\/strong><strong><em>=AVERAGE(IF(($A$2:$A$9=$F$1) * ($B$2:$B$9=$F$2), $C$2:$C$9,\u201d\u201d))<\/em><\/strong><\/p>\n<p style=\"text-align: justify\"><strong><em>T\u1ed5ng c\u1ed9ng:\u00a0<\/em><\/strong><strong><em>=SUM(IF(($A$2:$A$9=$F$1) * ($B$2:$B$9=$F$2), $C$2:$C$9,\u201d\u201d))<\/em><\/strong><\/p>\n<h2 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"Vi_du_3_Cong_thuc_mang_%E2%80%93_dem_tat_ca_cac_ky_tu_trong_mot_dai_nhat_dinh\"><\/span>V\u00ed d\u1ee5 3. C\u00f4ng th\u1ee9c m\u1ea3ng \u2013 \u0111\u1ebfm t\u1ea5t c\u1ea3 c\u00e1c k\u00fd t\u1ef1 trong m\u1ed9t d\u1ea3i nh\u1ea5t \u0111\u1ecbnh<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p style=\"text-align: justify\">V\u00ed d\u1ee5 c\u00f4ng th\u1ee9c m\u1ea3ng n\u00e0y s\u1ebd ch\u1ec9 cho b\u1ea1n c\u00e1ch \u0111\u1ebfm s\u1ed1 k\u00fd t\u1ef1, bao g\u1ed3m kho\u1ea3ng tr\u1eafng, trong m\u1ed9t d\u1ea3i \u00f4. C\u00f4ng th\u1ee9c r\u1ea5t \u0111\u01a1n gi\u1ea3n, nh\u01b0 sau:<\/p>\n<p style=\"text-align: justify\"><strong><em>= SUM (LEN (range))<\/em><\/strong><\/p>\n<p style=\"text-align: justify\">B\u1ea1n s\u1eed d\u1ee5ng h\u00e0m LEN \u0111\u1ec3 tr\u1ea3 l\u1ea1i chi\u1ec1u d\u00e0i c\u1ee7a chu\u1ed7i v\u0103n b\u1ea3n trong m\u1ed7i \u00f4 ri\u00eang l\u1ebb, v\u00e0 sau \u0111\u00f3 s\u1eed d\u1ee5ng h\u00e0m SUM \u0111\u1ec3 th\u00eam c\u00e1c con s\u1ed1 \u0111\u00f3 v\u00e0o \u0111\u1ec3 t\u00ednh t\u1ed5ng.<\/p>\n<p style=\"text-align: justify\">V\u00ed d\u1ee5, c\u00f4ng th\u1ee9c m\u1ea3ng = <strong><em>SUM (LEN (A1: A10))<\/em><\/strong> t\u00ednh t\u1ed5ng s\u1ed1 t\u1ea5t c\u1ea3 c\u00e1c k\u00fd t\u1ef1 (c\u00f3 t\u00ednh k\u00ed t\u1ef1 kho\u1ea3ng c\u00e1ch) trong d\u1ea3i A1: A10.<\/p>\n<h2 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"Vi_du_4_Cong_thuc_mang_de_tinh_bat_ky_cac_ky_tu_nao_trong_dai\"><\/span>V\u00ed d\u1ee5 4. C\u00f4ng th\u1ee9c m\u1ea3ng \u0111\u1ec3 t\u00ednh b\u1ea5t k\u1ef3 (c\u00e1c) k\u00fd t\u1ef1 n\u00e0o trong d\u1ea3i<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p style=\"text-align: justify\">N\u1ebfu b\u1ea1n mu\u1ed1n bi\u1ebft c\u00f3 bao nhi\u00eau l\u1ea7n m\u1ed9t nh\u00e2n v\u1eadt nh\u1ea5t \u0111\u1ecbnh ho\u1eb7c m\u1ed9t nh\u00f3m nh\u00e2n v\u1eadt xu\u1ea5t hi\u1ec7n trong m\u1ed9t d\u1ea3i \u00f4 \u0111\u01b0\u1ee3c ch\u1ec9 \u0111\u1ecbnh, m\u1ed9t c\u00f4ng th\u1ee9c m\u1ea3ng v\u1edbi h\u00e0m LEN c\u00f3 th\u1ec3 gi\u00fap m\u1ed9t l\u1ea7n n\u1eefa. Trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y, c\u00f4ng th\u1ee9c ph\u1ee9c t\u1ea1p h\u01a1n m\u1ed9t ch\u00fat:<\/p>\n<p style=\"text-align: justify\"><strong><em>= SUM ((LEN (range) \u2013 LEN (SUBSTITUTE (range,character, \u201c\u201d))) \/ LEN (character))<\/em><\/strong><\/p>\n<p style=\"text-align: justify\">V\u00e0 \u0111\u00e2y l\u00e0 m\u1ed9t v\u00ed d\u1ee5 th\u1ef1c t\u1ebf. Gi\u1ea3 s\u1eed b\u1ea1n c\u00f3 m\u1ed9t danh s\u00e1ch c\u00e1c \u0111\u01a1n \u0111\u1eb7t h\u00e0ng m\u00e0 m\u1ed9t \u00f4 c\u00f3 th\u1ec3 ch\u1ee9a m\u1ed9t s\u1ed1 th\u1ee9 t\u1ef1 \u0111\u01b0\u1ee3c ph\u00e2n c\u00e1ch b\u1edfi d\u1ea5u ph\u1ea9y ho\u1eb7c b\u1ea5t k\u1ef3 d\u1ea5u ph\u00e2n c\u00e1ch n\u00e0o kh\u00e1c. C\u00f3 m\u1ed9t s\u1ed1 lo\u1ea1i l\u1ec7nh v\u00e0 m\u1ed7i lo\u1ea1i c\u00f3 m\u1ed9t s\u1ed1 nh\u1eadn d\u1ea1ng duy nh\u1ea5t c\u1ee7a ri\u00eang n\u00f3 \u2013 k\u00fd t\u1ef1 \u0111\u1ea7u ti\u00ean trong m\u1ed9t s\u1ed1 \u0111\u01a1n \u0111\u1eb7t h\u00e0ng.<\/p>\n<p style=\"text-align: justify\">Gi\u1ea3 s\u1eed c\u00e1c \u0111\u01a1n \u0111\u1eb7t h\u00e0ng n\u1eb1m trong c\u00e1c \u00f4 B2: B5 v\u00e0 m\u00e3 nh\u1eadn bi\u1ebft \u0111\u01a1n h\u00e0ng \u0111\u1eb7t trong \u00f4 E1, c\u00f4ng th\u1ee9c nh\u01b0 sau:<\/p>\n<p style=\"text-align: justify\"><strong><em>= SUM ((LEN (B2: B5) \u2013 LEN (SUBSTITUTE (B2: B5, E1, \u201c\u201d))) \/ LEN (E1))<\/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\/array-formula-count-char.png\" alt=\"Array formula to count any given character(s) in a ranges\" title=\"array-formula-count-char\"><\/em><\/strong><\/p>\n<h2 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"Vi_du_5_Cac_gia_tri_tong_hop_trong_moi_hang_hoac_cho_hang_thu_N\"><\/span>V\u00ed d\u1ee5 5. C\u00e1c gi\u00e1 tr\u1ecb t\u1ed5ng h\u1ee3p trong m\u1ed7i h\u00e0ng ho\u1eb7c cho h\u00e0ng th\u1ee9 N<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p style=\"text-align: justify\">N\u1ebfu b\u1ea1n mu\u1ed1n t\u1ed5ng h\u1ee3p m\u1ed7i h\u00e0ng kh\u00e1c ho\u1eb7c h\u00e0ng th\u1ee9 N trong m\u1ed9t b\u1ea3ng, b\u1ea1n s\u1ebd c\u1ea7n k\u1ebft h\u1ee3p c\u00e1c h\u00e0m SUM v\u00e0 MOD trong m\u1ed9t c\u00f4ng th\u1ee9c m\u1ea3ng:<\/p>\n<p style=\"text-align: justify\"><strong>= SUM ((\u2013 (MOD (ROW (range), Nth row) = 0)) * (range))<\/strong><\/p>\n<p style=\"text-align: justify\">H\u00e0m MOD tr\u1ea3 v\u1ec1 s\u1ed1 d\u01b0 sau khi l\u00e0m tr\u00f2n s\u1ed1 kia th\u00e0nh s\u1ed1 nguy\u00ean g\u1ea7n nh\u1ea5t v\u00e0 chia cho s\u1ed1 chia. Ch\u00fang t\u00f4i nh\u00fang h\u00e0m ROW \u0111\u1ec3 l\u1ea5y ra s\u1ed1 th\u1ee9 t\u1ef1 c\u1ee7a h\u00e0ng, v\u00e0 sau \u0111\u00f3 chia n\u00f3 cho h\u00e0ng th\u1ee9 N (v\u00ed d\u1ee5 b\u1eb1ng 2 \u0111\u1ec3 t\u1ed5ng h\u1ee3p m\u1ed7i \u00f4 th\u1ee9 hai) v\u00e0 ki\u1ec3m tra n\u1ebfu ph\u1ea7n c\u00f2n l\u1ea1i l\u00e0 s\u1ed1 kh\u00f4ng. N\u1ebfu c\u00f3, th\u00ec \u00f4 \u0111\u01b0\u1ee3c t\u00ednh t\u1ed5ng.<\/p>\n<p style=\"text-align: justify\">To\u00e1n t\u1eed \u0111\u01a1n v\u1ecb (\u2013) \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng \u0111\u1ec3 chuy\u1ec3n th\u00e0nh c\u00e1c gi\u00e1 tr\u1ecb Boolean kh\u00f4ng-ph\u1ea3i-gi\u00e1-tr\u1ecb-s\u1ed1 l\u00e0 <strong><em>TRUE v\u00e0 FALSE<\/em><\/strong>, v\u00e0 sau khi s\u1eed d\u1ee5ng h\u00e0m <strong><em>MOD<\/em><\/strong> th\u00ec tr\u1edf th\u00e0nh 1 v\u00e0 0, \u0111\u1ec3 cu\u1ed1i c\u00f9ng th\u00ec h\u00e0m SUM \u0111\u1ec3 c\u00f3 th\u1ec3 th\u00eam c\u00e1c s\u1ed1 \u0111\u00f3.<\/p>\n<p style=\"text-align: justify\">V\u00ed d\u1ee5, \u0111\u1ec3 \u0111\u1ebfm m\u1ed7i \u00f4 kh\u00e1c trong kho\u1ea3ng B2: B10, b\u1ea1n s\u1eed d\u1ee5ng m\u1ed9t trong c\u00e1c c\u00f4ng th\u1ee9c sau:<\/p>\n<p style=\"text-align: justify\">\u0110\u1ebfm c\u1ea3 h\u00e0ng (h\u00e0ng th\u1ee9 2, th\u1ee9 4, v.v.):<\/p>\n<p style=\"text-align: justify\"><strong><em>= SUM ((\u2013 (MOD (ROW ($ B2: B10), 2) = 0)) * (B2: B10))<\/em><\/strong><\/p>\n<p style=\"text-align: justify\">\u0110\u1ebfm c\u00e1c h\u00e0ng l\u1ebb (h\u00e0ng th\u1ee9 nh\u1ea5t, th\u1ee9 ba, etc.):<\/p>\n<p style=\"text-align: justify\"><strong><em>= SUM ((\u2013 (MOD (ROW ($ B2: B10), 2) = 1)) * (B2: B10))<\/em><\/strong><\/p>\n<p style=\"text-align: justify\">\u0110\u1ec3 c\u00f3 \u0111\u01b0\u1ee3c m\u1ed9t c\u00f4ng th\u1ee9c ph\u1ed5 qu\u00e1t c\u00f3 th\u1ec3 t\u1ed5ng h\u1ee3p c\u00e1c gi\u00e1 tr\u1ecb trong b\u1ea5t k\u1ef3 d\u00f2ng th\u1ee9 N n\u00e0o m\u00e0 b\u1ea1n ch\u1ec9 \u0111\u1ecbnh v\u00e0 ho\u1ea1t \u0111\u1ed9ng ch\u00ednh x\u00e1c v\u1edbi b\u1ea5t k\u1ef3 d\u1ea3i n\u00e0o b\u1ea5t k\u1ec3 v\u1ecb tr\u00ed c\u1ee7a ch\u00fang trong m\u1ed9t b\u1ea3ng t\u00ednh, th\u00ec c\u00f4ng th\u1ee9c tr\u00ean v\u1eabn c\u1ea7n ph\u1ea3i \u0111\u01b0\u1ee3c c\u1ea3i thi\u1ec7n th\u00eam m\u1ed9t ch\u00fat n\u1eefa, nh\u01b0 sau:<\/p>\n<p style=\"text-align: justify\"><strong><em>= SUM ((\u2013 (MOD ((ROW ($ B $ 2: $ B $ 7) -ROW ($ B $ 2)), E1) = E1-1)) * ($ B $ 2: $ B $ 7))<\/em><\/strong><\/p>\n<p style=\"text-align: justify\">V\u1edbi \u00f4 E1 l\u1ea7n l\u01b0\u1ee3t l\u00e0 h\u00e0ng N m\u00e0 b\u1ea1n mu\u1ed1n t\u00ednh t\u1ed5ng<\/p>\n<p style=\"text-align: justify\"><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/sum-every-n-row.png\" alt=\"An array formula to sum values in every other or N&lt;sup&gt;th&lt;\/sup&gt; row\" title=\"sum-every-n-row\"><\/p>\n<blockquote>\n<p style=\"text-align: justify\">Kh\u00f3a h\u1ecdc\u00a0<a href=\"http:\/\/blog.ezworkapp.com\/khoa-hoc-excel-ke-toan.html\">excel k\u1ebf to\u00e1n<\/a>\u00a0khuy\u1ebfn m\u00e3i c\u1ef1c l\u1edbn<\/p>\n<\/blockquote>\n<h2 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"Vi_du_6_Thuc_hien_tinh_toan_khac_nhau_tren_cac_con_so_trong_dai_khac_nhau\"><\/span>V\u00ed d\u1ee5 6. Th\u1ef1c hi\u1ec7n t\u00ednh to\u00e1n kh\u00e1c nhau tr\u00ean c\u00e1c con s\u1ed1 trong d\u1ea3i kh\u00e1c nhau<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p style=\"text-align: justify\">D\u01b0\u1edbi \u0111\u00e2y l\u00e0 m\u1ed9t t\u00ecnh hu\u1ed1ng \u0111i\u1ec3n h\u00ecnh cho nhi\u1ec1u nh\u00e0 cung c\u1ea5p \u2013 ch\u00ednh l\u00e0 \u0111\u01a1n gi\u00e1 thay \u0111\u1ed5i t\u00f9y thu\u1ed9c v\u00e0o s\u1ed1 l\u01b0\u1ee3ng mua, v\u00e0 v\u1ea5n \u0111\u1ec1 \u0111\u1eb7t ra cho b\u1ea1n l\u00e0 vi\u1ebft m\u1ed9t c\u00f4ng th\u1ee9c t\u00ednh t\u1ed5ng s\u1ed1 ti\u1ec1n cho b\u1ea5t k\u1ef3 gi\u00e1 \u0111\u1ea7u v\u00e0o n\u00e0o \u1edf m\u1ed9t 1 \u00f4 c\u1ee5 th\u1ec3.<\/p>\n<p style=\"text-align: justify\"><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/source-data-3.png\" alt=\"Source data\" title=\"source-data-3\"><\/p>\n<p style=\"text-align: justify\">V\u1ea5n \u0111\u1ec1 n\u00e0y c\u00f3 th\u1ec3 d\u1ec5 d\u00e0ng gi\u1ea3i quy\u1ebft b\u1eb1ng c\u00e1ch s\u1eed d\u1ee5ng c\u00e1c c\u00f4ng th\u1ee9c IF l\u1ed3ng nhau nh\u01b0 sau:<\/p>\n<p style=\"text-align: justify\"><strong><em>= B8 * IF (B8&gt; = 101, B6, IF (B8&gt; = 50, B5, IF (B8&gt; = 20, B4, IF (B8&gt; = 11, B3, IF (B8&gt; = 1, B2, )))))<\/em><\/strong><\/p>\n<p style=\"text-align: justify\">Tuy nhi\u00ean, c\u00e1ch ti\u1ebfp c\u1eadn n\u00e0y c\u00f3 m\u1ed9t h\u1ea1n ch\u1ebf \u0111\u00e1ng k\u1ec3. B\u1edfi v\u00ec c\u00f4ng th\u1ee9c tham chi\u1ebfu \u0111\u1ebfn m\u1ed7i gi\u00e1 trong c\u00e1c \u00f4 B2:B6 l\u00e0 \u0111\u1ed9c l\u1eadp, n\u00ean b\u1ea1n s\u1ebd ph\u1ea3i c\u1eadp nh\u1eadt c\u00f4ng th\u1ee9c ngay khi ng\u01b0\u1eddi d\u00f9ng thay \u0111\u1ed5i b\u1ea5t k\u1ef3 d\u1ea3i \u00f4 n\u00e0o ho\u1eb7c th\u00eam v\u00e0o m\u1ed9t d\u1ea3i s\u1ed1 m\u1edbi.<\/p>\n<p style=\"text-align: justify\">\u0110\u1ec3 l\u00e0m cho c\u00f4ng th\u1ee9c linh ho\u1ea1t h\u01a1n, n\u00ean \u0111\u1ec3 thao t\u00e1c tr\u00ean m\u1ea3ng h\u01a1n l\u00e0 tr\u00ean c\u00e1c \u00f4 ri\u00eang l\u1ebb. Trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y, b\u1ea5t k\u1ec3 c\u00f3 bao nhi\u00eau gi\u00e1 tr\u1ecb \u0111\u01b0\u1ee3c thay \u0111\u1ed5i, th\u00eam ho\u1eb7c x\u00f3a, b\u1ea1n s\u1ebd ch\u1ec9 ph\u1ea3i c\u1eadp nh\u1eadt m\u1ed9t tham chi\u1ebfu d\u1ea3i \u00f4 trong c\u00f4ng th\u1ee9c.<\/p>\n<p style=\"text-align: justify\"><strong><em>= SUM (B8 * (B2: B6) * (\u2013 (B8&gt; = VALUE (LEFT (A2: A6, FIND (\u201c\u201d, A2: A6))))) * (\u2013 (B8 &lt;= VALUE (RIGHT (A2: A6, LEN (A2: A6) \u2013 FIND (\u201cto\u201d, A2: A6) -LEN (\u201cto\u201d))))))<\/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\/array-formula-multiple-conditions.png\" alt=\"Array formula to perform different calculations on numbers within different ranges\" title=\"array-formula-multiple-conditions\"><\/em><\/strong><\/p>\n<p style=\"text-align: justify\">Vi\u1ec7c chia nh\u1ecf c\u00f4ng th\u1ee9c n\u00e0y\u0111\u1ec3 ph\u00e2n t\u00edch chi ti\u1ebft c\u00f3 th\u1ec3 s\u1ebd y\u00eau c\u1ea7u m\u1ed9t b\u00e0i b\u00e1o ri\u00eang, do \u0111\u00f3 t\u00f4i s\u1ebd ch\u1ec9 \u0111\u01b0a ra m\u1ed9t c\u00e1i nh\u00ecn kh\u00e1i qu\u00e1t v\u1ec1 m\u1eb7t logic c\u1ee7a c\u00f4ng th\u1ee9c. N\u1ebfu b\u1ea1n ch\u1ecdn t\u1eebng ph\u1ea7n c\u1ee7a c\u00f4ng th\u1ee9c trong thanh c\u00f4ng th\u1ee9c v\u00e0 nh\u1ea5n F9, b\u1ea1n s\u1ebd th\u1ea5y n\u00f3 \u0111\u00e1nh gi\u00e1 3 m\u1ea3ng sau \u0111\u00e2y (v\u1edbi s\u1ed1 l\u01b0\u1ee3ng l\u00e0 100 (\u00f4 B8) nh\u01b0 trong \u1ea3nh ch\u1ee5p m\u00e0n h\u00ecnh \u1edf tr\u00ean):<\/p>\n<p style=\"text-align: justify\"><strong><em>= SUM (B8 * {20; 18; 16; 13; 12} * {1; 1; 1; 1; 0} * {0; 0; 0; 1; 1}<\/em><\/strong><\/p>\n<p style=\"text-align: justify\">N\u0103m ph\u1ea7n t\u1eed \u0111\u1ea7u ti\u00ean c\u1ee7a m\u1ea3ng ch\u00ednh l\u00e0 gi\u00e1 c\u1ea3 trong c\u00e1c \u00f4 B2: B6. V\u00e0 2 m\u1ea3ng cu\u1ed1i c\u00f9ng c\u1ee7a 0 v\u00e0 1 x\u00e1c \u0111\u1ecbnh ch\u00ednh x\u00e1c gi\u00e1 n\u00e0o s\u1ebd \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng \u0111\u1ec3 t\u00ednh to\u00e1n. V\u00ec v\u1eady, c\u00e2u h\u1ecfi ch\u00ednh l\u00e0 \u2013 l\u1ea5y \u0111\u01b0\u1ee3c 2 m\u1ea3ng n\u00e0y nh\u01b0 th\u1ebf n\u00e0o v\u00e0 \u00fd ngh\u0129a c\u1ee7a ch\u00fang?<\/p>\n<p style=\"text-align: justify\">C\u00f4ng th\u1ee9c bao g\u1ed3m 2 h\u00e0m VALUE, nh\u01b0 sau: <strong><em>(B8&gt; = VALUE ()) * (B8 &lt;= VALUE ())<\/em><\/strong><\/p>\n<p style=\"text-align: justify\">H\u00e0m th\u1ee9 nh\u1ea5t ki\u1ec3m tra xem gi\u00e1 tr\u1ecb c\u1ee7a B8 c\u00f3 l\u1edbn h\u01a1n hay b\u1eb1ng gi\u1edbi h\u1ea1n d\u01b0\u1edbi c\u1ee7a m\u1ed7i d\u1ea3i \u201cs\u1ed1 l\u01b0\u1ee3ng \u0111\u01a1n v\u1ecb\u201d v\u00e0 h\u00e0m th\u1ee9 2 \u0111\u1ec3 ki\u1ec3m tra n\u1ebfu B8 nh\u1ecf h\u01a1n ho\u1eb7c b\u1eb1ng v\u1edbi gi\u1edbi h\u1ea1n tr\u00ean c\u1ee7a m\u1ed7i d\u00e3y (k\u1ebft h\u1ee3p c\u00e1c h\u00e0m LEFT , RIGHT, FIND v\u00e0 LEN \u0111\u1ec3 l\u1ea5y ra c\u00e1c gi\u00e1 tr\u1ecb gi\u1edbi h\u1ea1n tr\u00ean v\u00e0 d\u01b0\u1edbi). Th\u00ec sau \u0111\u00f3, b\u1ea1n s\u1ebd nh\u1eadn \u0111\u01b0\u1ee3c 0 n\u1ebfu \u0111i\u1ec1u ki\u1ec7n kh\u00f4ng \u0111\u01b0\u1ee3c th\u1ecfa, v\u00e0 1 n\u1ebfu th\u1ecfa \u0111i\u1ec1u ki\u1ec7n.<\/p>\n<p style=\"text-align: justify\">Cu\u1ed1i c\u00f9ng, h\u00e0m SUM s\u1ebd nh\u00e2n s\u1ed1 l\u01b0\u1ee3ng trong B8 b\u1edfi v\u1edbi gi\u00e1 t\u01b0\u01a1ng \u1ee9ng trong m\u1ea3ng \u0111\u01a1n gi\u00e1 (B2: B6) v\u00e0 m\u1ed7i ph\u1ea7n t\u1eed c\u1ee7a m\u1ea3ng 0 v\u00e0 1. V\u00ec nh\u00e2n v\u1edbi 0 lu\u00f4n cho 0, n\u00ean lu\u00f4n ch\u1ec9 c\u00f3 m\u1ed9t gi\u00e1 \u0111\u01b0\u1ee3c s\u1eed d\u1ee5ng cho m\u1ed7i s\u1ea3n ph\u00e2m.<\/p>\n<p style=\"text-align: justify\">Trong v\u00ed d\u1ee5 n\u00e0y, s\u1ed1 l\u01b0\u1ee3ng \u0111\u01b0\u1ee3c nh\u00e2n v\u1edbi 13 \u0111\u00f4 la n\u1eb1m trong kho\u1ea3ng gi\u00e1 tr\u1ecb \u201c50 \u0111\u1ebfn 100\u201d. \u0110\u00e2y l\u00e0 m\u1ee5c th\u1ee9 4 c\u1ee7a m\u1ea3ng gi\u00e1 (\u00f4 B5 trong ph\u1ea1m vi B2: B6), v\u00e0 n\u00f3 l\u00e0 th\u00e0nh ph\u1ea7n duy nh\u1ea5t c\u00f3 m\u1eb7t trong hai m\u1ea3ng cu\u1ed1i c\u00f9ng.<\/p>\n<p style=\"text-align: justify\">\u0110\u1ec3 c\u00f4ng th\u1ee9c l\u00e0m vi\u1ec7c ch\u00ednh x\u00e1c, h\u00e3y ch\u1eafc ch\u1eafn ki\u1ec3m tra l\u1ea1i hai \u0111i\u1ec1u sau:<\/p>\n<ul style=\"text-align: justify\">\n<li>S\u1ed1 l\u01b0\u1ee3ng trong c\u00e1c \u00f4 A2: A6 n\u00ean t\u1ea1o th\u00e0nh m\u1ed9t kho\u1ea3ng gi\u00e1 tr\u1ecb li\u00ean ti\u1ebfp \u0111\u1ec3 kh\u00f4ng c\u00f3 gi\u00e1 tr\u1ecb n\u00e0o b\u1ecb b\u1ecf l\u1ea1i.<\/li>\n<li>T\u1ea5t c\u1ea3 c\u00e1c kh\u1ed1i l\u01b0\u1ee3ng trong c\u00e1c \u00f4 A2: A6 ph\u1ea3i \u0111\u01b0\u1ee3c nh\u1eadp v\u00e0o trong m\u1eabu \u201cX \u0111\u1ebfn Y\u201d n\u00e0y v\u00ec n\u00f3 \u0111\u01b0\u1ee3c m\u00e3 ho\u00e1 trong c\u00f4ng th\u1ee9c. N\u1ebfu s\u1ed1 l\u01b0\u1ee3ng c\u1ee7a b\u1ea1n \u0111\u01b0\u1ee3c nh\u1eadp b\u1eb1ng m\u1ed9t c\u00e1ch kh\u00e1c, h\u00e3y n\u00f3i \u201c1 \u2013 10\u201d, sau \u0111\u00f3 thay \u201c\u0111\u1ebfn\u201d b\u1eb1ng \u201c-\u201d trong c\u00f4ng th\u1ee9c.<\/li>\n<\/ul>\n<p style=\"text-align: justify\">N\u1ebfu b\u1ea1n mu\u1ed1n hi\u1ec3n th\u1ecb m\u1ed9t th\u00f4ng b\u00e1o \u201cN\u1eb1m ngo\u00e0i d\u1ea3i\u201d khi s\u1ed1 l\u01b0\u1ee3ng \u0111\u1ea7u v\u00e0o trong B8 n\u1eb1m ngo\u00e0i d\u1ea3i s\u1ed1 ti\u1ec1n, th\u00ec h\u00e3y tham kh\u1ea3o c\u00e2u l\u1ec7nh IF sau \u0111\u00e2y:<\/p>\n<p style=\"text-align: justify\"><strong><em>= IF (AND (B8&gt; = VALUE (LEFT (A2, FIND (\u201c\u201d, A2))), B8 &lt;= VALUE (RIGHT (A6, LEN (A6) \u2013 FIND(\u201cto\u201d, A6) -LEN ( \u201cto\u201d)))), SUM (\u2026))<\/em><\/strong><\/p>\n<p style=\"text-align: justify\">C\u00f4ng th\u1ee9c h\u00e0m If n\u00e0y d\u00f9 nh\u00ecn c\u00f3 v\u1ebb ph\u1ee9c t\u1ea1p nh\u01b0ng th\u1ef1c s\u1ef1 l\u1ea1i r\u1ea5t \u0111\u01a1n gi\u1ea3n \u2013 n\u00f3 ki\u1ec3m tra r\u1eb1ng n\u1ebfu gi\u00e1 tr\u1ecb trong B8 l\u1edbn h\u01a1n ho\u1eb7c b\u1eb1ng v\u1edbi r\u00e0ng bu\u1ed9c d\u01b0\u1edbi trong A2 v\u00e0 \u00edt h\u01a1n ho\u1eb7c b\u1eb1ng v\u1edbi c\u00e1c r\u00e0ng bu\u1ed9c tr\u00ean trong \u00f4 A6. N\u00f3i c\u00e1ch kh\u00e1c, n\u00f3 ki\u1ec3m tra \u0111i\u1ec1u ki\u1ec7n n\u00e0y: AND (B8&gt; = 1, B8 &lt;= 200).<\/p>\n<p style=\"text-align: justify\">C\u00f4ng th\u1ee9c \u0111\u1ea7y \u0111\u1ee7 m\u00e0 b\u1ea1n c\u1ea7n nh\u01b0 sau:<\/p>\n<p style=\"text-align: justify\"><strong><em>= IF (AND (B8&gt; = VALUE (LEFT (A2, FIND (\u201c\u201d, A2))), B8 &lt;= VALUE (RIGHT (A6, LEN (A6) -FIND (\u201cto\u201d, A6) -LEN (\u201d \u201c), SUM (B8 * (B2: B6) * (- (B8&gt; = VALUE\u1eca (LEFT (A2: A6, FIND (\u201d \u201c, A2: A6))))) * (- ( B8 &lt;= VALUE (RIGHT (A2: A6, LEN (A2: A6) -FIND (\u201cto\u201d, A2: A6) -LEN (\u201cto\u201d)))))), \u201cOut of range\u201d)<\/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\/array-formula-multiple-conditions2.png\" alt=\"Array formula to perform different calculations on numbers within limited ranges\" title=\"array-formula-multiple-conditions2\"><\/em><\/strong><\/p>\n<h2 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"Vi_du_7_Cac_ham_do_nguoi_dung_dinh_nghia_trong_cong_thuc_mang_Excel\"><\/span>V\u00ed d\u1ee5 7. C\u00e1c h\u00e0m do ng\u01b0\u1eddi d\u00f9ng \u0111\u1ecbnh ngh\u0129a trong c\u00f4ng th\u1ee9c m\u1ea3ng Excel<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p style=\"text-align: justify\">V\u00ed d\u1ee5 n\u00e0y nh\u1eb1m d\u00e0nh cho nh\u1eefng ng\u01b0\u1eddi bi\u1ebft v\u00e0 hi\u1ec3u v\u1ec1 c\u00e1c macro VBA trong Excel v\u00e0 c\u00e1c h\u00e0m do ng\u01b0\u1eddi d\u00f9ng t\u1ef1 \u0111\u1ecbnh ngh\u0129a.<\/p>\n<p style=\"text-align: justify\">B\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng h\u00e0m \u0111\u00e3 \u0111\u01b0\u1ee3c ng\u01b0\u1eddi d\u00f9ng (b\u1ea1n) \u0111\u1ecbnh ngh\u0129a t\u1eeb tr\u01b0\u1edbc trong c\u00e1c c\u00f4ng th\u1ee9c m\u1ea3ng trong Excel, cho ph\u00e9p m\u1ed9t c\u00f4ng th\u1ee9c x\u00e1c \u0111\u1ecbnh h\u1ed7 tr\u1ee3 c\u00e1c ph\u00e9p t\u00ednh trong c\u00e1c m\u1ea3ng.<\/p>\n<p style=\"text-align: justify\">V\u00ed d\u1ee5, m\u1ed9t trong nh\u1eefng chuy\u00ean gia Excel c\u1ee7a ch\u00fang t\u00f4i \u0111\u00e3 vi\u1ebft m\u1ed9t h\u00e0m g\u1ecdi l\u00e0 GetCellColor c\u00f3 th\u1ec3 nh\u1eadn bi\u1ebft \u0111\u01b0\u1ee3c m\u1ed9t m\u00e0u c\u1ee7a t\u1ea5t c\u1ea3 c\u00e1c \u00f4 trong m\u1ed9t d\u1ea3i, y nh\u01b0 t\u00ean c\u1ee7a n\u00f3. B\u1ea1n c\u00f3 th\u1ec3 l\u1ea5y m\u00e3 c\u1ee7a h\u00e0m c\u00f4ng th\u1ee9c tr\u00ean t\u1eeb b\u00e0i vi\u1ebft n\u00e0y \u2013 L\u00e0m th\u1ebf n\u00e0o \u0111\u1ec3 \u0111\u1ebfm v\u00e0 t\u00ednh t\u1ed5ng c\u00e1c \u00f4 d\u1ef1a tr\u00ean m\u00e0u s\u1eafc trong Excel.<\/p>\n<p style=\"text-align: justify\">V\u00e0 b\u00e2y gi\u1edd, ch\u00fang ta h\u00e3y xem l\u00e0m th\u1ebf n\u00e0o m\u00e0 b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng h\u00e0m GetCellColor trong m\u1ed9t c\u00f4ng th\u1ee9c m\u1ea3ng. Gi\u1ea3 s\u1eed b\u1ea1n c\u00f3 m\u1ed9t b\u1ea3ng v\u1edbi m\u1ed9t c\u1ed9t m\u00e3 m\u00e0u v\u00e0 b\u1ea1n mu\u1ed1n t\u1ed5ng h\u1ee3p c\u00e1c gi\u00e1 tr\u1ecb \u0111\u00e1p \u1ee9ng m\u1ed9t s\u1ed1 \u0111i\u1ec1u ki\u1ec7n, bao g\u1ed3m m\u00e0u c\u1ee7a \u00f4. V\u00ed d\u1ee5: h\u00e3y t\u00ednh t\u1ed5ng doanh s\u1ed1 c\u00e1c c\u1ed9t m\u00e0u \u201cxanh\u201d v\u00e0 \u201cv\u00e0ng\u201d c\u1ee7a Neal:<\/p>\n<p style=\"text-align: justify\"><img decoding=\"async\" class=\"aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/array-formula-custom-function.png\" alt=\"Using user-defined functions in Excel array formulas\" title=\"array-formula-custom-function\"><\/p>\n<p style=\"text-align: justify\">Nh\u01b0 \u0111\u00e3 tr\u00ecnh b\u00e0y trong h\u00ecnh tr\u00ean, ch\u00fang ta s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c m\u1ea3ng Excel nh\u01b0 sau:<\/p>\n<p style=\"text-align: justify\"><strong>=<em>SUM(\u2013($A$2:$A$10=$F$1) * ($C$2:$C$10) * (\u2013(GetCellColor($C$2:$C$10)=GetCellColor($E$2))))<\/em><\/strong><\/p>\n<p style=\"text-align: justify\">Trong \u0111\u00f3, \u00f4 F1 l\u00e0 t\u00ean ng\u01b0\u1eddi b\u00e1n h\u00e0ng v\u00e0 E2 l\u00e0 m\u1eabu m\u00e0u.<\/p>\n<p style=\"text-align: justify\">C\u00f4ng th\u1ee9c t\u00ednh c\u00e1c \u00f4 trong kho\u1ea3ng C2: C10 n\u1ebfu th\u1ecfa m\u00e3n hai \u0111i\u1ec1u ki\u1ec7n sau:<\/p>\n<ul style=\"text-align: justify\">\n<li>$A$2:$A$10 = $F$1 \u2013 ki\u1ec3m tra n\u1ebfu m\u1ed9t \u00f4 trong c\u1ed9t A kh\u1edbp v\u1edbi t\u00ean ng\u01b0\u1eddi b\u00e1n trong F1, t\u1ee9c l\u00e0 Neal nh\u01b0 trong v\u00ed d\u1ee5 n\u00e0y.<\/li>\n<li>GetCellColor ($C$2:$C$10) = GetCellColor ($ E $ 2) \u2013 s\u1eed d\u1ee5ng h\u00e0m \u0111\u01b0\u1ee3c ng\u01b0\u1eddi d\u00f9ng \u0111\u1ecbnh ngh\u0129a t\u00f9y ch\u1ec9nh \u0111\u1ec3 l\u1ea5y m\u00e0u c\u1ee7a c\u00e1c \u00f4 C2 \u0111\u1ebfn C10 v\u00e0 ki\u1ec3m tra xem n\u00f3 c\u00f3 ph\u00f9 h\u1ee3p v\u1edbi m\u1eabu m\u00e0u trong E2 hay kh\u00f4ng, trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y.<\/li>\n<\/ul>\n<p style=\"text-align: justify\">H\u00e3y ch\u00fa \u00fd r\u1eb1ng ch\u00fang ta s\u1eed d\u1ee5ng to\u00e1n t\u1eed \u0111\u01a1n v\u1ecb v\u1edbi c\u1ea3 hai bi\u1ec3u th\u1ee9c tr\u00ean \u0111\u1ec3 chuy\u1ec3n \u0111\u1ed5i c\u00e1c gi\u00e1 tr\u1ecb Boolean TRUE v\u00e0 FALSE tr\u1edf v\u1ec1 1 v\u00e0 0 \u0111\u1ec3 h\u00e0m SUM c\u00f3 th\u1ec3 ho\u1ea1t \u0111\u1ed9ng tr\u00ean d\u1eef li\u1ec7u n\u00e0y. N\u1ebfu c\u1ea3 hai \u0111i\u1ec1u ki\u1ec7n \u0111\u01b0\u1ee3c \u0111\u00e1p \u1ee9ng, ngh\u0129a l\u00e0 hai gi\u00e1 tr\u1ecb \u0111\u01b0\u1ee3c tr\u1ea3 l\u1ea1i, SUM s\u1ebd c\u1ed9ng th\u00eam s\u1ed1 ti\u1ec1n b\u00e1n h\u00e0ng t\u1eeb m\u1ed9t \u00f4 t\u01b0\u01a1ng \u1ee9ng trong c\u1ed9t C.<\/p>\n<blockquote>\n<p style=\"text-align: justify\"><a href=\"http:\/\/blog.ezworkapp.com\">\u0110\u1ecba ch\u1ec9 h\u1ecdc Excel t\u1ea1i H\u00e0 N\u1ed9i<\/a><\/p>\n<\/blockquote>\n<h2 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"Cong_thuc_mang_Excel_%E2%80%93_nhung_han_che_va_cac_phuong_an_thay_the\"><\/span>C\u00f4ng th\u1ee9c m\u1ea3ng Excel \u2013 nh\u1eefng h\u1ea1n ch\u1ebf v\u00e0 c\u00e1c ph\u01b0\u01a1ng \u00e1n thay th\u1ebf<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p style=\"text-align: justify\">C\u00e1c c\u00f4ng th\u1ee9c m\u1ea3ng l\u00e0 m\u1ed9t trong nh\u1eefng t\u00ednh n\u0103ng m\u1ea1nh m\u1ebd nh\u1ea5t c\u1ee7a Excel, nh\u01b0ng kh\u00f4ng ph\u1ea3i l\u00e0 \u1edf m\u1ecdi ph\u01b0\u01a1ng di\u1ec7n. \u0110\u00e2y l\u00e0 nh\u1eefng h\u1ea1n ch\u1ebf quan tr\u1ecdng nh\u1ea5t c\u1ee7a m\u1ea3ng trong Excel.<\/p>\n<h3 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"1_Nhung_mang_lon_co_the_lam_cham_Excel\"><\/span>1. Nh\u1eefng m\u1ea3ng l\u1edbn c\u00f3 th\u1ec3 l\u00e0m ch\u1eadm Excel:<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p style=\"text-align: justify\">M\u1eb7c d\u00f9 Microsoft Excel kh\u00f4ng \u00e1p \u0111\u1eb7t b\u1ea5t k\u1ef3 gi\u1edbi h\u1ea1n n\u00e0o v\u1ec1 k\u00edch th\u01b0\u1edbc c\u1ee7a m\u1ea3ng b\u1ea1n s\u1eed d\u1ee5ng trong c\u00e1c b\u1ea3ng t\u00ednh, nh\u01b0ng b\u1ea1n s\u1ebd b\u1ecb gi\u1edbi h\u1ea1n b\u1edfi b\u1ed9 nh\u1edb s\u1eb5n c\u00f3 tr\u00ean m\u00e1y t\u00ednh c\u1ee7a b\u1ea1n, v\u00ec vi\u1ec7c t\u00ednh to\u00e1n l\u1ea1i c\u00e1c c\u00f4ng th\u1ee9c v\u1edbi c\u00e1c m\u1ea3ng l\u1edbn t\u1ed1n kha kh\u00e1 th\u1eddi gian. M\u1eb7c d\u00f9 tr\u00ean l\u00fd thuy\u1ebft, b\u1ea1n c\u00f3 th\u1ec3 t\u1ea1o c\u00e1c m\u1ea3ng kh\u1ed5ng l\u1ed3 bao g\u1ed3m h\u00e0ng tr\u0103m ho\u1eb7c h\u00e0ng ng\u00e0n th\u00e0nh ph\u1ea7n, trong th\u1ef1c t\u1ebf \u0111i\u1ec1u n\u00e0y kh\u00f4ng \u0111\u01b0\u1ee3c khuy\u1ebfn c\u00e1o b\u1edfi v\u00ec ch\u00fang c\u00f3 th\u1ec3 l\u00e0m ch\u1eadm \u0111\u00e1ng k\u1ec3 b\u1ea3ng t\u00ednh c\u1ee7a b\u1ea1n.<\/p>\n<h3 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"2_Khong_duoc_phep_chon_cac_mang_hoan_toan_chi_co_cot\"><\/span>2. Kh\u00f4ng \u0111\u01b0\u1ee3c ph\u00e9p ch\u1ecdn c\u00e1c m\u1ea3ng ho\u00e0n to\u00e0n ch\u1ec9 c\u00f3 c\u1ed9t.<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p style=\"text-align: justify\">B\u1ea1n kh\u00f4ng \u0111\u01b0\u1ee3c ph\u00e9p t\u1ea1o m\u1ed9t m\u1ea3ng bao g\u1ed3m to\u00e0n b\u1ed9 c\u1ed9t ho\u1eb7c v\u00e0i c\u1ed9t v\u00ec l\u00fd do r\u00f5 r\u00e0ng \u0111\u01b0\u1ee3c gi\u1ea3i th\u00edch \u1edf tr\u00ean. C\u00e1c c\u00f4ng th\u1ee9c m\u1ea3ng trong Excel r\u1ea5t thi\u1ebfu t\u00e0i nguy\u00ean v\u00e0 Microsoft \u0111ang \u00e1p d\u1ee5ng c\u00e1c bi\u1ec7n ph\u00e1p ng\u0103n ch\u1eb7n vi\u1ec7c \u0111\u00f3ng b\u0103ng c\u1ee7a Excel.<\/p>\n<h3 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"3_Gioi_han_cua_cac_cong_thuc_mang_khi_tham_chieu_den_1_trang_tinh_khac\"><\/span>3. Gi\u1edbi h\u1ea1n c\u1ee7a c\u00e1c c\u00f4ng th\u1ee9c m\u1ea3ng khi tham chi\u1ebfu \u0111\u1ebfn 1 trang t\u00ednh kh\u00e1c:<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p style=\"text-align: justify\">Trong Excel 2003 v\u00e0 c\u00e1c phi\u00ean b\u1ea3n tr\u01b0\u1edbc, m\u1ed9t b\u1ea3ng t\u00ednh nh\u1ea5t \u0111\u1ecbnh c\u00f3 th\u1ec3 ch\u1ee9a t\u1ed1i \u0111a 65.472 c\u00f4ng th\u1ee9c m\u1ea3ng \u0111\u1ec1 c\u1eadp \u0111\u1ebfn m\u1ed9t trang t\u00ednh kh\u00e1c. Trong c\u00e1c phi\u00ean b\u1ea3n hi\u1ec7n t\u1ea1i c\u1ee7a Excel 2013, 2010 v\u00e0 2007, c\u00f4ng th\u1ee9c m\u1ea3ng c\u00e1c b\u1ea3ng t\u00ednh ch\u00e9o \u0111\u01b0\u1ee3c gi\u1edbi h\u1ea1n b\u1edfi b\u1ed9 nh\u1edb s\u1eb5n c\u00f3.<\/p>\n<h3 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"4_Go_loi_cho_cac_cong_thuc_mang\"><\/span>4. G\u1ee1 l\u1ed7i cho c\u00e1c c\u00f4ng th\u1ee9c m\u1ea3ng<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p style=\"text-align: justify\">N\u1ebfu c\u00f4ng th\u1ee9c m\u1ea3ng tr\u1ea3 v\u1ec1 k\u1ebft qu\u1ea3 kh\u00f4ng ch\u00ednh x\u00e1c, h\u00e3y ch\u1eafc ch\u1eafn l\u00e0 b\u1ea1n \u0111\u00e3 nh\u1ea5n Ctrl + Shift + Enter khi nh\u1eadp n\u00f3. N\u1ebfu b\u1ea1n \u0111\u00e3 l\u00e0m, h\u00e3y ch\u1ecdn c\u00e1c ph\u1ea7n c\u1ee7a c\u00f4ng th\u1ee9c v\u00e0 nh\u1ea5n F9 \u0111\u1ec3 \u0111\u00e1nh gi\u00e1 v\u00e0 g\u1ee1 l\u1ed7i ch\u00fang.<\/p>\n<h3 style=\"text-align: justify\"><span class=\"ez-toc-section\" id=\"5_Cac_lua_chon_thay_the_cho_cac_cong_thuc_mang\"><\/span>5. C\u00e1c l\u1ef1a ch\u1ecdn thay th\u1ebf cho c\u00e1c c\u00f4ng th\u1ee9c m\u1ea3ng.<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p style=\"text-align: justify\">N\u1ebfu b\u1ea1n th\u1ea5y c\u00f4ng th\u1ee9c m\u1ea3ng qu\u00e1 ph\u1ee9c t\u1ea1p v\u00e0 kh\u1ecf hi\u1ec3u, th\u00ec b\u1ea1n v\u1eabn c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng m\u1ed9t trong nh\u1eefng h\u00e0m c\u00f4ng th\u1ee9c Excel kh\u00e1c \u0111\u1ec3 x\u1eed l\u00fd c\u00e1c d\u1eef li\u1ec7u m\u1ea3ng (m\u00e0 kh\u00f4ng c\u1ea7n \u0111\u1ebfn t\u1ed5 h\u1ee3p ph\u00edm Ctrl + Shift + Enter). V\u00ed d\u1ee5 nh\u01b0 <a href=\"http:\/\/blog.ezworkapp.com\/47-%e2%97%8f-ham-sumproduct-va-su-khac-nhau-voi-sumifs-countifs-trong-excel.html\" target=\"_blank\" rel=\"noopener\">h\u00e0m SUMPRODUCT<\/a> \u2013 nh\u00e2n c\u00e1c gi\u00e1 tr\u1ecb c\u1ee7a c\u00e1c m\u1ea3ng nh\u1ea5t \u0111\u1ecbnh v\u1edbi nhau v\u00e0 cho ra k\u1ebft qu\u1ea3 l\u00e0 t\u1ed5ng t\u1ea5t c\u1ea3 s\u1ea3n ph\u1ea9m. M\u1ed9t v\u00ed\u00a0 d\u1ee5 kh\u00e1c l\u00e0 h\u00e0m INDEX trong Excel khi c\u00f3 c\u00e1c gi\u00e1 tr\u1ecb tr\u1ed1ng ho\u1eb7c b\u1eb1ng 0 trong \u0111\u1ed1i s\u1ed1 row_num hay col_num th\u00ec s\u1ebd tr\u1ea3 v\u1ec1 gi\u00e1 tr\u1ecb m\u1ea3ng c\u1ee7a to\u00e0n b\u1ed9 c\u00e1c c\u1ed9t v\u00e0 h\u00e0ng t\u01b0\u01a1ng \u1ee9ng.<\/p>\n<p style=\"text-align: justify\">N\u1ebfu b\u1ea1n mu\u1ed1n t\u1ea3i xu\u1ed1ng c\u00e1c v\u00ed d\u1ee5 v\u1ec1 h\u00e0m c\u00f4ng th\u1ee9c m\u1ea3ng (s\u1eed d\u1ee5ng ph\u01b0\u01a1ng ph\u00e1p l\u1eadt ng\u01b0\u1ee3c v\u1ea5n \u0111\u1ec1 hi\u1ec3u s\u00e2u h\u01a1n), th\u00ec b\u1ea1n s\u1ebd d\u1ec5 d\u00e0ng t\u1ea3i xu\u1ed1ng c\u00e1c v\u00ed d\u1ee5 v\u1ec1 c\u00f4ng th\u1ee9c. \u0110\u00e2y l\u00e0 t\u1ec7p .xlsm k\u1ec3 t\u1eeb v\u00ed d\u1ee5 6 ch\u1ee9a ch\u1ee9c n\u0103ng VBA tu\u1ef3 ch\u1ec9nh, do \u0111\u00f3 b\u1ea1n s\u1ebd ph\u1ea3i nh\u1ea5p v\u00e0o n\u00fat Enable Content sau khi t\u1ea3i xu\u1ed1ng, \u0111\u1ec3 cho ph\u00e9p macro ch\u1ea1y.<\/p>\n<p style=\"text-align: justify\">V\u00e0 \u0111\u00f3 l\u00e0 t\u1ea5t c\u1ea3 b\u00e0i h\u1ecdc m\u00e0 ch\u00fang t\u00f4i mu\u1ed1n d\u00e0nh cho b\u1ea1n cho ng\u00e0y h\u00f4m nay, c\u1ea3m \u01a1n c\u00e1c b\u1ea1n \u0111\u00e3 \u0111\u1ecdc!<\/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-294.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":7375,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-7374","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\/7374","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=7374"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/7374\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/7375"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=7374"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=7374"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=7374"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}