{"id":5127,"date":"2024-12-09T08:33:18","date_gmt":"2024-12-09T08:33:18","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/09\/cach-ket-hop-ham-vlookup-voi-ham-sum-ham-sumif-trong-excel\/"},"modified":"2024-12-09T08:33:18","modified_gmt":"2024-12-09T08:33:18","slug":"cach-ket-hop-ham-vlookup-voi-ham-sum-ham-sumif-trong-excel","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/09\/cach-ket-hop-ham-vlookup-voi-ham-sum-ham-sumif-trong-excel\/","title":{"rendered":"C\u00e1ch k\u1ebft h\u1ee3p h\u00e0m VLOOKUP v\u1edbi h\u00e0m SUM\/ h\u00e0m SUMIF trong Excel"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p><em><span style=\"font-weight: 400\">Trong b\u00e0i vi\u1ebft n\u00e0y, <a href=\"http:\/\/blog.ezworkapp.com\/\"><strong>H\u1ecdc Excel Online<\/strong> <\/a>s\u1ebd <\/span><span style=\"font-weight: 400\">chia s\u1ebb v\u1edbi c\u00e1c b\u1ea1n m\u1ed9t v\u00e0i v\u00ed d\u1ee5 v\u1ec1<strong> c\u00e1ch k\u1ebft h\u1ee3p h\u00e0m VLOOKUP<\/strong> v\u00e0 h\u00e0m SUM ho\u1eb7c SUMIF trong Excel \u0111\u1ec3 tra c\u1ee9u v\u00e0 t\u00ednh t\u1ed5ng d\u1ef1a tr\u00ean m\u1ed9t ho\u1eb7c nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n<\/span><span style=\"font-weight: 400\">.<\/span><\/em><\/p>\n<p><span style=\"font-weight: 400\">C\u00f3 ph\u1ea3i b\u1ea1n \u0111ang mu\u1ed1n t\u1ea1o ra m\u1ed9t file b\u00e1o c\u00e1o trong Excel? Trong b\u00e1o c\u00e1o n\u00e0y, b\u1ea1n c\u1ea7n t\u00ecm t\u1ea5t c\u1ea3 nh\u1eefng gi\u00e1 tr\u1ecb c\u1ee5 th\u1ec3 nh\u1ea5t \u0111\u1ecbnh sau \u0111\u00f3 t\u00ednh t\u1ed5ng c\u00e1c gi\u00e1 tr\u1ecb li\u00ean quan t\u1edbi gi\u00e1 tr\u1ecb c\u1ee5 th\u1ec3 \u0111\u00f3 (n\u1eb1m tr\u00ean c\u00f9ng 1 d\u00f2ng ch\u1eb3ng h\u1ea1n)?\u00a0 Ho\u1eb7c c\u00f3 th\u1ec3 b\u1ea1n c\u1ea7n t\u00ednh t\u1ed5ng gi\u00e1 tr\u1ecb to\u00e0n b\u1ed9 ho\u00e1 \u0111\u01a1n c\u1ee7a m\u1ed9t nh\u00e0 cung c\u1ea5p c\u1ee5 th\u1ec3?<\/span><\/p>\n<p><span style=\"font-weight: 400\">C\u00e1c nhi\u1ec7m v\u1ee5 c\u00f3 th\u1ec3 kh\u00e1c nhau, nh\u01b0ng b\u1ea3n ch\u1ea5t l\u00e0 gi\u1ed1ng nhau \u2013 b\u1ea1n mu\u1ed1n tra c\u1ee9u v\u00e0 t\u00ednh t\u1ed5ng c\u00e1c gi\u00e1 tr\u1ecb v\u1edbi m\u1ed9t ho\u1eb7c m\u1ed9t s\u1ed1 ti\u00eau ch\u00ed trong Excel. <\/span><span style=\"font-weight: 400\">Ki\u1ec3u d\u1eef li\u1ec7u n\u00e0o? B\u1ea5t c\u1ee9 d\u1eef li\u1ec7u ki\u1ec3u s\u1ed1 n\u00e0o. Lo\u1ea1i ti\u00eau ch\u00ed n\u00e0o? B\u1ea5t k\u1ef3 lo\u1ea1i n\u00e0o <\/span><span style=\"font-weight: 400\">\ud83d\ude42 B\u1eaft \u0111\u1ea7u t\u1eeb m\u1ed9t s\u1ed1 ho\u1eb7c tham chi\u1ebfu \u0111\u1ebfn m\u1ed9t \u00f4 c\u00f3 ch\u1ee9a gi\u00e1 tr\u1ecb ph\u00f9 h\u1ee3p v\u00e0 k\u1ebft th\u00fac b\u1eb1ng c\u00e1c ph\u00e9p to\u00e1n logic v\u00e0 k\u1ebft qu\u1ea3 \u0111\u01b0\u1ee3c tr\u1ea3 v\u1ec1 b\u1edfi c\u00e1c c\u00f4ng th\u1ee9c Excel.<\/span><\/p>\n<p><span style=\"font-weight: 400\">V\u00ec v\u1eady, Microsoft Excel c\u00f3 b\u1ea5t k\u1ef3 ch\u1ee9c n\u0103ng n\u00e0o c\u00f3 th\u1ec3 gi\u00fap th\u1ef1c hi\u1ec7n c\u00e1c c\u00f4ng vi\u1ec7c tr\u00ean kh\u00f4ng? T\u1ea5t nhi\u00ean l\u00e0 c\u00f3! B\u1ea1n c\u00f3 th\u1ec3 t\u00ecm ra gi\u1ea3i ph\u00e1p b\u1eb1ng <strong>c\u00e1ch k\u1ebft h\u1ee3p h\u00e0m VLOOKUP ho\u1eb7c LOOKUP<\/strong> c\u1ee7a Excel v\u1edbi c\u00e1c <em><a href=\"http:\/\/blog.ezworkapp.com\/cac-vi-du-ve-ham-sum-trong-excel-cach-chi-tinh-tong-cac-cot-hang-hoac-o-duoc-hien-thi.html\"><strong>h\u00e0m SUM<\/strong><\/a> <\/em>ho\u1eb7c <a href=\"http:\/\/blog.ezworkapp.com\/cach-dung-ham-sumif-trong-excel-vi-du-cong-thuc.html\"><em><strong>h\u00e0m\u00a0SUMIF<\/strong><\/em><\/a>. C\u00e1c v\u00ed d\u1ee5 v\u1ec1 c\u00f4ng th\u1ee9c sau \u0111\u00e2y s\u1ebd gi\u00fap b\u1ea1n hi\u1ec3u c\u00e1ch ho\u1ea1t \u0111\u1ed9ng c\u1ee7a c\u00e1c h\u00e0m Excel n\u00e0y v\u00e0 c\u00e1ch \u00e1p d\u1ee5ng ch\u00fang v\u00e0o d\u1eef li\u1ec7u th\u1ef1c.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Xin l\u01b0u \u00fd, \u0111\u00e2y l\u00e0 nh\u1eefng v\u00ed d\u1ee5 n\u00e2ng cao m\u00e0 b\u1ea1n \u0111\u00e3 quen thu\u1ed9c v\u1edbi c\u00e1c nguy\u00ean t\u1eafc v\u00e0 c\u00fa ph\u00e1p c\u1ee7a <em><a href=\"http:\/\/blog.ezworkapp.com\/ham-vlookup-trong-excel-huong-dan-su-dung-chi-tiet-va-co-vi-du-cu-the.html\"><strong>h\u00e0m VLOOKUP<\/strong><\/a><\/em>. N\u1ebfu kh\u00f4ng, ph\u1ea7n \u0111\u1ea7u ti\u00ean c\u1ee7a h\u01b0\u1edbng d\u1eabn VLOOKUP d\u00e0nh cho ng\u01b0\u1eddi m\u1edbi b\u1eaft \u0111\u1ea7u ch\u1eafc ch\u1eafn \u0111\u00e1ng \u0111\u1ec3 b\u1ea1n ch\u00fa \u00fd \u2013 c\u00fa ph\u00e1p h\u00e0m VLOOKUP trong Excel v\u00e0 c\u00e1ch s\u1eed d\u1ee5ng.<\/span><\/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-6756ab4d46c33\" 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\/cach-ket-hop-ham-vlookup-voi-ham-sum-ham-sumif-trong-excel.html#Excel_VLOOKUP_va_SUM_%E2%80%93_tim_tong_cac_gia_tri_phu_hop\" title=\"Excel VLOOKUP v\u00e0 SUM \u2013 t\u00ecm t\u1ed5ng c\u00e1c gi\u00e1 tr\u1ecb ph\u00f9 h\u1ee3p\">Excel VLOOKUP v\u00e0 SUM \u2013 t\u00ecm t\u1ed5ng c\u00e1c gi\u00e1 tr\u1ecb ph\u00f9 h\u1ee3p<\/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\/cach-ket-hop-ham-vlookup-voi-ham-sum-ham-sumif-trong-excel.html#Cach_thuc_hien_cac_phep_tinh_khac_voi_ham_VLOOKUP_trong_Excel\" title=\"C\u00e1ch th\u1ef1c hi\u1ec7n c\u00e1c ph\u00e9p t\u00ednh kh\u00e1c v\u1edbi h\u00e0m VLOOKUP trong Excel\">C\u00e1ch th\u1ef1c hi\u1ec7n c\u00e1c ph\u00e9p t\u00ednh kh\u00e1c v\u1edbi h\u00e0m VLOOKUP trong 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\/cach-ket-hop-ham-vlookup-voi-ham-sum-ham-sumif-trong-excel.html#LOOKUP_VA_SUM_%E2%80%93_tra_cuu_trong_mot_mang_va_tinh_tong_cac_gia_tri_phu_hop\" title=\"LOOKUP V\u00c0 SUM \u2013 tra c\u1ee9u trong m\u1ed9t m\u1ea3ng v\u00e0 t\u00ednh t\u1ed5ng c\u00e1c gi\u00e1 tr\u1ecb ph\u00f9 h\u1ee3p\">LOOKUP V\u00c0 SUM \u2013 tra c\u1ee9u trong m\u1ed9t m\u1ea3ng v\u00e0 t\u00ednh t\u1ed5ng c\u00e1c gi\u00e1 tr\u1ecb ph\u00f9 h\u1ee3p<\/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\/cach-ket-hop-ham-vlookup-voi-ham-sum-ham-sumif-trong-excel.html#VLOOKUP_va_SUMIF_%E2%80%93_tra_cuu_va_tinh_tong_cac_gia_tri_voi_tieu_chi\" title=\"VLOOKUP v\u00e0 SUMIF \u2013 tra c\u1ee9u v\u00e0 t\u00ednh t\u1ed5ng c\u00e1c gi\u00e1 tr\u1ecb v\u1edbi ti\u00eau ch\u00ed\">VLOOKUP v\u00e0 SUMIF \u2013 tra c\u1ee9u v\u00e0 t\u00ednh t\u1ed5ng c\u00e1c gi\u00e1 tr\u1ecb v\u1edbi ti\u00eau ch\u00ed<\/a><\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2><span class=\"ez-toc-section\" id=\"Excel_VLOOKUP_va_SUM_%E2%80%93_tim_tong_cac_gia_tri_phu_hop\"><\/span><b>Excel VLOOKUP v\u00e0 SUM \u2013 t\u00ecm t\u1ed5ng c\u00e1c gi\u00e1 tr\u1ecb ph\u00f9 h\u1ee3p<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">N\u1ebfu b\u1ea1n l\u00e0m vi\u1ec7c v\u1edbi d\u1eef li\u1ec7u s\u1ed1 trong Excel, b\u1ea1n th\u01b0\u1eddng kh\u00f4ng ch\u1ec9 tr\u00edch xu\u1ea5t c\u00e1c gi\u00e1 tr\u1ecb li\u00ean quan t\u1eeb m\u1ed9t b\u1ea3ng kh\u00e1c m\u00e0 c\u00f2n t\u00ednh t\u1ed5ng c\u00e1c s\u1ed1 trong m\u1ed9t s\u1ed1 c\u1ed9t ho\u1eb7c h\u00e0ng. \u0110\u1ec3 l\u00e0m \u0111i\u1ec1u n\u00e0y, b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng k\u1ebft h\u1ee3p c\u00e1c h\u00e0m SUM v\u00e0 VLOOaKUP nh\u01b0 minh h\u1ecda b\u00ean d\u01b0\u1edbi.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Ngu\u1ed3n d\u1eef li\u1ec7u:<\/span><\/p>\n<div class=\"code-block code-block-1\" style=\"margin: 8px 0;clear: both\">\n<div class=\"ad-inserterpro\">\n<a href=\"https:\/\/hocexcel.online\/course\/preview\/ex101-excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/ex101_92019-nho-434.gif\" title=\"ex101_92019-nho-434\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p><span style=\"font-weight: 400\">Gi\u1ea3 s\u1eed, b\u1ea1n c\u00f3 m\u1ed9t danh s\u00e1ch s\u1ea3n ph\u1ea9m v\u1edbi s\u1ed1 li\u1ec7u b\u00e1n h\u00e0ng trong v\u00e0i th\u00e1ng, m\u1ed9t c\u1ed9t l\u00e0 m\u1ed9t\u00a0 th\u00e1ng. D\u1eef li\u1ec7u ngu\u1ed3n n\u1eb1m tr\u00ean sheet c\u00f3 t\u00ean <\/span><span style=\"font-weight: 400\">Monthly sales<\/span><span style=\"font-weight: 400\">:<\/span><\/p>\n<figure id=\"attachment_33323\" aria-describedby=\"caption-attachment-33323\" style=\"width: 615px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-33323 size-full\" title=\"C\u00e1ch k\u1ebft h\u1ee3p h\u00e0m VLOOKUP v\u1edbi h\u00e0m SUM 1\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/excel-vlookup-vc3a0-sum-tc3acm-te1bb95ng-cc3a1c-gic3a1-tre1bb8b-phc3b9-he1bba3p-1.png\" alt=\"C\u00e1ch k\u1ebft h\u1ee3p h\u00e0m VLOOKUP v\u1edbi h\u00e0m SUM 1\" width=\"615\" height=\"202\"><figcaption id=\"caption-attachment-33323\" class=\"wp-caption-text\">C\u00e1ch k\u1ebft h\u1ee3p h\u00e0m VLOOKUP v\u1edbi h\u00e0m SUM 1<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\">B\u00e2y gi\u1edd, b\u1ea1n mu\u1ed1n l\u1eadp m\u1ed9t b\u1ea3ng t\u1ed5ng h\u1ee3p v\u1edbi t\u1ed5ng doanh thu cho t\u1eebng s\u1ea3n ph\u1ea9m.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Gi\u1ea3i ph\u00e1p l\u00e0 s\u1eed d\u1ee5ng m\u1ed9t m\u1ea3ng trong tham s\u1ed1 th\u1ee9 3 (col_index_num) c\u1ee7a h\u00e0m VLOOKUP trong Excel. \u0110\u00e2y l\u00e0 m\u1ed9t c\u00f4ng th\u1ee9c chung:<\/span><\/p>\n<p><strong>SUM(VLOOKUP(lookup value, lookup range, {2,3,\u2026,n}, FALSE))<\/strong><\/p>\n<p><span style=\"font-weight: 400\">Nh\u01b0 b\u1ea1n \u0111\u00e3 th\u1ea5y, ch\u00fang ta s\u1eed d\u1ee5ng m\u1ed9t h\u1eb1ng s\u1ed1 m\u1ea3ng b\u1eb1ng tham s\u1ed1 th\u1ee9 3 \u0111\u1ec3 th\u1ef1c hi\u1ec7n tra c\u1ee9u trong c\u00f9ng m\u1ed9t c\u00f4ng th\u1ee9c VLOOKUP \u0111\u1ec3 l\u1ea5y t\u1ed5ng c\u00e1c gi\u00e1 tr\u1ecb trong c\u1ed9t 2, 3 v\u00e0 4.<\/span><\/p>\n<p><span style=\"font-weight: 400\">V\u00e0 b\u00e2y gi\u1edd, h\u00e3y \u0111i\u1ec1u ch\u1ec9nh s\u1ef1 k\u1ebft h\u1ee3p c\u1ee7a h\u00e0m VLOOKUP v\u00e0 h\u00e0m SUM cho d\u1eef li\u1ec7u \u0111\u1ec3 t\u00ecm t\u1ed5ng doanh thu trong c\u00e1c c\u1ed9t B \u2013 M trong b\u1ea3ng tr\u00ean:<\/span><\/p>\n<p><strong>=SUM(VLOOKUP(B2, \u2018Monthly sales\u2019! $A$2:$M$9, {2,3,4,5,6,7,8,9,10,11,12,13}, FALSE))<\/strong><\/p>\n<p><span style=\"font-weight: 400\">Quan tr\u1ecdng! V\u00ec b\u1ea1n \u0111ang x\u00e2y d\u1ef1ng m\u1ed9t c\u00f4ng th\u1ee9c m\u1ea3ng, h\u00e3y nh\u1edb nh\u1ea5n t\u1ed5 h\u1ee3p ph\u00edm Ctrl + Shift + Enter thay v\u00ec nh\u1ea5n ph\u00edm Enter \u0111\u01a1n gi\u1ea3n khi b\u1ea1n nh\u1eadp xong. Khi b\u1ea1n l\u00e0m \u0111i\u1ec1u n\u00e0y, Microsoft Excel s\u1ebd \u0111\u1eb7t c\u00f4ng th\u1ee9c c\u1ee7a b\u1ea1n trong d\u1ea5u ngo\u1eb7c nh\u1ecdn nh\u01b0 sau:<\/span><\/p>\n<p><strong>{=SUM(VLOOKUP(B2, \u2018Monthly sales\u2019!$A$2:$M$9, {2,3,4,5,6,7,8,9,10,11,12,13}, FALSE))}<\/strong><\/p>\n<p><span style=\"font-weight: 400\">N\u1ebfu b\u1ea1n nh\u1ea5n ph\u00edm Enter nh\u01b0 b\u00ecnh th\u01b0\u1eddng, ch\u1ec9 gi\u00e1 tr\u1ecb \u0111\u1ea7u ti\u00ean trong m\u1ea3ng m\u1edbi \u0111\u01b0\u1ee3c x\u1eed l\u00fd, \u0111i\u1ec1u n\u00e0y s\u1ebd t\u1ea1o ra k\u1ebft qu\u1ea3 kh\u00f4ng ch\u00ednh x\u00e1c.<\/span><\/p>\n<figure id=\"attachment_33324\" aria-describedby=\"caption-attachment-33324\" style=\"width: 564px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-33324 size-full\" title=\"Excel VLOOKUP v\u00e0 SUM - t\u00ecm t\u1ed5ng c\u00e1c gi\u00e1 tr\u1ecb ph\u00f9 h\u1ee3p\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/cc3a1ch-ke1babft-he1bba3p-hc3a0m-vlookup-ve1bb9bi-hc3a0m-sum-2.png\" alt=\"Excel VLOOKUP v\u00e0 SUM - t\u00ecm t\u1ed5ng c\u00e1c gi\u00e1 tr\u1ecb ph\u00f9 h\u1ee3p\" width=\"564\" height=\"241\"><figcaption id=\"caption-attachment-33324\" class=\"wp-caption-text\">Excel VLOOKUP v\u00e0 SUM \u2013 t\u00ecm t\u1ed5ng c\u00e1c gi\u00e1 tr\u1ecb ph\u00f9 h\u1ee3p<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\"><em><strong>M\u1eb9o:<\/strong><\/em>\u00a0B\u1ea1n c\u00f3 th\u1ec3 t\u00f2 m\u00f2 v\u00ec sao c\u00f4ng th\u1ee9c hi\u1ec3n th\u1ecb [@Product] l\u00e0m gi\u00e1 tr\u1ecb tra c\u1ee9u trong h\u00ecnh tr\u00ean. \u0110i\u1ec1u n\u00e0y l\u00e0 do v\u00f9ng d\u1eef li\u1ec7u tr\u00ean \u0111\u00e3 \u0111\u01b0\u1ee3c chuy\u1ec3n \u0111\u1ed5i d\u1eef li\u1ec7u th\u00e0nh b\u1ea3ng (Th\u1ebb Insert tr\u00ean thanh c\u00f4ng c\u1ee5 Ribbon &gt; Table). \u0110i\u1ec1u n\u00e0y thu\u1eadn ti\u1ec7n khi l\u00e0m vi\u1ec7c v\u1edbi b\u1ea3ng Excel c\u00f3 \u0111\u1ea7y \u0111\u1ee7 ch\u1ee9c n\u0103ng v\u00e0 tham chi\u1ebfu c\u00f3 c\u1ea5u tr\u00fac c\u1ee7a ch\u00fang. V\u00ed d\u1ee5: khi b\u1ea1n nh\u1eadp c\u00f4ng th\u1ee9c v\u00e0o m\u1ed9t \u00f4, Excel s\u1ebd t\u1ef1 \u0111\u1ed9ng sao ch\u00e9p c\u00f4ng th\u1ee9c tr\u00ean to\u00e0n b\u1ed9 c\u1ed9t v\u00e0 theo c\u00e1ch n\u00e0y, b\u1ea1n s\u1ebd ti\u1ebft ki\u1ec7m \u0111\u01b0\u1ee3c th\u1eddi gian.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Nh\u01b0 b\u1ea1n th\u1ea5y, vi\u1ec7c k\u1ebft h\u1ee3p h\u00e0m VLOOKUP v\u00e0 SUM trong Excel r\u1ea5t d\u1ec5 d\u00e0ng. Tuy nhi\u00ean, \u0111\u00e2y kh\u00f4ng ph\u1ea3i l\u00e0 gi\u1ea3i ph\u00e1p l\u00fd t\u01b0\u1edfng, \u0111\u1eb7c bi\u1ec7t n\u1ebfu b\u1ea1n \u0111ang l\u00e0m vi\u1ec7c v\u1edbi nh\u1eefng c\u00e1i b\u1ea3ng l\u1edbn. V\u1ea5n \u0111\u1ec1 l\u00e0 vi\u1ec7c s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c m\u1ea3ng c\u00f3 th\u1ec3 \u1ea3nh h\u01b0\u1edfng x\u1ea5u \u0111\u1ebfn hi\u1ec7u su\u1ea5t c\u1ee7a workbook v\u00ec m\u1ed7i gi\u00e1 tr\u1ecb trong m\u1ea3ng th\u1ef1c hi\u1ec7n m\u1ed9t l\u1ec7nh g\u1ecdi ri\u00eang c\u1ee7a h\u00e0m VLOOKUP. V\u00ec v\u1eady, b\u1ea1n c\u00e0ng c\u00f3 nhi\u1ec1u gi\u00e1 tr\u1ecb m\u1ea3ng v\u00e0 c\u00e0ng c\u00f3 nhi\u1ec1u c\u00f4ng th\u1ee9c m\u1ea3ng trong s\u1ed5 l\u00e0m vi\u1ec7c c\u1ee7a m\u00ecnh th\u00ec Excel c\u00e0ng ho\u1ea1t \u0111\u1ed9ng ch\u1eadm h\u01a1n.<\/span><\/p>\n<p><span style=\"font-weight: 400\">B\u1ea1n c\u00f3 th\u1ec3 b\u1ecf qua v\u1ea5n \u0111\u1ec1 n\u00e0y b\u1eb1ng c\u00e1ch s\u1eed d\u1ee5ng k\u1ebft h\u1ee3p c\u00e1c h\u00e0m INDEX v\u00e0 MATCH thay v\u00ec h\u00e0m SUM v\u00e0 VLOOKUP, v\u00e0 t\u00f4i s\u1ebd ch\u1ec9 cho b\u1ea1n m\u1ed9t v\u00e0i v\u00ed d\u1ee5 v\u1ec1 c\u00f4ng th\u1ee9c trong b\u00e0i vi\u1ebft ti\u1ebfp theo.<\/span><\/p>\n<h2><span class=\"ez-toc-section\" id=\"Cach_thuc_hien_cac_phep_tinh_khac_voi_ham_VLOOKUP_trong_Excel\"><\/span><b>C\u00e1ch th\u1ef1c hi\u1ec7n c\u00e1c ph\u00e9p t\u00ednh kh\u00e1c v\u1edbi h\u00e0m VLOOKUP trong Excel<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">C\u00e1ch \u0111\u00e2y \u00edt l\u00e2u, ch\u00fang ta \u0111\u00e3 th\u1ea3o lu\u1eadn v\u1ec1 m\u1ed9t v\u00ed d\u1ee5 v\u1ec1 c\u00e1ch b\u1ea1n c\u00f3 th\u1ec3 tr\u00edch xu\u1ea5t gi\u00e1 tr\u1ecb t\u1eeb m\u1ed9t s\u1ed1 c\u1ed9t trong b\u1ea3ng tra c\u1ee9u v\u00e0 t\u00ednh t\u1ed5ng c\u00e1c gi\u00e1 tr\u1ecb \u0111\u00f3. Theo c\u00e1ch t\u01b0\u01a1ng t\u1ef1, b\u1ea1n c\u00f3 th\u1ec3 th\u1ef1c hi\u1ec7n c\u00e1c ph\u00e9p t\u00ednh to\u00e1n h\u1ecdc kh\u00e1c v\u1edbi k\u1ebft qu\u1ea3 \u0111\u01b0\u1ee3c tr\u1ea3 v\u1ec1 b\u1edfi h\u00e0m VLOOKUP. D\u01b0\u1edbi \u0111\u00e2y l\u00e0 m\u1ed9t s\u1ed1 v\u00ed d\u1ee5 v\u1ec1 c\u00f4ng th\u1ee9c:<\/span><\/p>\n<table>\n<tbody>\n<tr>\n<td><span style=\"font-weight: 400\">Ph\u00e9p to\u00e1n<\/span><\/td>\n<td><span style=\"font-weight: 400\">C\u00f4ng th\u1ee9c<\/span><\/td>\n<td><span style=\"font-weight: 400\">M\u00f4 t\u1ea3<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">T\u00ednh trung b\u00ecnh<\/span><\/td>\n<td><span style=\"font-weight: 400\">{=AVERAGE(VLOOKUP(A2, \u2018Lookup Table\u2019$A$2:$D$10, {2,3,4}, FALSE))}<\/span><\/td>\n<td><span style=\"font-weight: 400\">C\u00f4ng th\u1ee9c t\u00ecm ki\u1ebfm gi\u00e1 tr\u1ecb c\u1ee7a \u00f4 A2 trong \u201cLookup Table\u201d v\u00e0 t\u00ednh gi\u00e1 tr\u1ecb trung b\u00ecnh c\u1ee7a c\u1ed9t B, C v\u00e0 D trong c\u00f9ng m\u1ed9t h\u00e0ng.<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">T\u00ecm gi\u00e1 tr\u1ecb l\u1edbn nh\u1ea5t<\/span><\/td>\n<td><span style=\"font-weight: 400\">{=MAX(VLOOKUP(A2, \u2018Lookup Table\u2019$A$2:$D$10, {2,3,4}, FALSE))}<\/span><\/td>\n<td><span style=\"font-weight: 400\">C\u00f4ng th\u1ee9c t\u00ecm ki\u1ebfm gi\u00e1 tr\u1ecb \u00f4 A2 trong \u201cLookup Table\u201d v\u00e0 t\u00ecm gi\u00e1 tr\u1ecb l\u1edbn nh\u1ea5t trong c\u00e1c c\u1ed9t B, C v\u00e0 D trong c\u00f9ng m\u1ed9t h\u00e0ng.<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">T\u00ecm gi\u00e1 tr\u1ecb nh\u1ecf nh\u1ea5t<\/span><\/td>\n<td><span style=\"font-weight: 400\">{=MIN(VLOOKUP(A2, \u2018Lookup Table\u2019$A$2:$D$10, {2,3,4}, FALSE))}<\/span><\/td>\n<td><span style=\"font-weight: 400\">C\u00f4ng th\u1ee9c t\u00ecm ki\u1ebfm gi\u00e1 tr\u1ecb \u00f4 A2 trong \u201cLookup Table\u201d v\u00e0 t\u00ecm gi\u00e1 tr\u1ecb nh\u1ecf nh\u1ea5t trong c\u00e1c c\u1ed9t B, C v\u00e0 D trong c\u00f9ng m\u1ed9t h\u00e0ng.<\/span><\/td>\n<\/tr>\n<tr>\n<td><span style=\"font-weight: 400\">T\u00ecnh ph\u1ea7n tr\u0103m t\u1ed5ng<\/span><\/td>\n<td><span style=\"font-weight: 400\">{=0.3*SUM(VLOOKUP(A2, \u2018Lookup Table\u2019$A$2:$D$10, {2,3,4}, FALSE))}<\/span><\/td>\n<td><span style=\"font-weight: 400\">C\u00f4ng th\u1ee9c t\u00ecm ki\u1ebfm gi\u00e1 tr\u1ecb \u00f4 A2 trong \u201cLookup Table\u201d v\u00e0 t\u00ednh t\u1ed5ng c\u00e1c gi\u00e1 tr\u1ecb trong c\u00e1c c\u1ed9t B, C v\u00e0 D trong c\u00f9ng m\u1ed9t h\u00e0ng, sau \u0111\u00f3 t\u00ednh 30% t\u1ed5ng.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><span style=\"font-weight: 400\">L\u01b0u \u00fd: V\u00ec t\u1ea5t c\u1ea3 c\u00e1c c\u00f4ng th\u1ee9c tr\u00ean \u0111\u1ec1u l\u00e0 c\u00f4ng th\u1ee9c m\u1ea3ng, h\u00e3y nh\u1edb nh\u1ea5n <strong>Ctrl + Shift + Enter<\/strong> \u0111\u1ec3 nh\u1eadp ch\u00ednh x\u00e1c v\u00e0o m\u1ed9t \u00f4.<\/span><\/p>\n<p><span style=\"font-weight: 400\">N\u1ebfu ch\u00fang ta th\u00eam c\u00e1c c\u00f4ng th\u1ee9c tr\u00ean v\u00e0o b\u1ea3ng \u2018Summary Sales\u2019 t\u1eeb v\u00ed d\u1ee5 tr\u01b0\u1edbc, k\u1ebft qu\u1ea3 s\u1ebd gi\u1ed1ng nh\u01b0 sau:<\/span><\/p>\n<figure id=\"attachment_33325\" aria-describedby=\"caption-attachment-33325\" style=\"width: 628px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-33325 size-full\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/cc3a1ch-ke1babft-he1bba3p-hc3a0m-vlookup-ve1bb9bi-hc3a0m-sum-3.png\" alt=\"C\u00e1ch k\u1ebft h\u1ee3p h\u00e0m VLOOKUP v\u1edbi h\u00e0m SUM 3\" width=\"628\" height=\"244\" title=\"cc3a1ch-ke1babft-he1bba3p-hc3a0m-vlookup-ve1bb9bi-hc3a0m-sum-3\"><figcaption id=\"caption-attachment-33325\" class=\"wp-caption-text\">C\u00e1ch k\u1ebft h\u1ee3p h\u00e0m VLOOKUP v\u1edbi h\u00e0m SUM 3<\/figcaption><\/figure>\n<h2><span class=\"ez-toc-section\" id=\"LOOKUP_VA_SUM_%E2%80%93_tra_cuu_trong_mot_mang_va_tinh_tong_cac_gia_tri_phu_hop\"><\/span><b>LOOKUP V\u00c0 SUM \u2013 tra c\u1ee9u trong m\u1ed9t m\u1ea3ng v\u00e0 t\u00ednh t\u1ed5ng c\u00e1c gi\u00e1 tr\u1ecb ph\u00f9 h\u1ee3p<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">Trong tr\u01b0\u1eddng h\u1ee3p tham s\u1ed1 tra c\u1ee9u c\u1ee7a b\u1ea1n l\u00e0 m\u1ed9t m\u1ea3ng ch\u1ee9 kh\u00f4ng ph\u1ea3i m\u1ed9t gi\u00e1 tr\u1ecb \u0111\u01a1n l\u1ebb, th\u00ec h\u00e0m Vlookup kh\u00f4ng c\u00f3 \u00edch v\u00ec n\u00f3 kh\u00f4ng th\u1ec3 tra c\u1ee9u trong m\u1ea3ng d\u1eef li\u1ec7u. Trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y, b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng h\u00e0m LOOKUP c\u1ee7a Excel t\u01b0\u01a1ng t\u1ef1 v\u1edbi h\u00e0m VLOOKUP nh\u01b0ng ho\u1ea1t \u0111\u1ed9ng v\u1edbi c\u00e1c m\u1ea3ng c\u0169ng nh\u01b0 v\u1edbi c\u00e1c gi\u00e1 tr\u1ecb ri\u00eang l\u1ebb.<\/span><\/p>\n<p><span style=\"font-weight: 400\">H\u00e3y xem x\u00e9t v\u00ed d\u1ee5 sau \u0111\u1ec3 b\u1ea1n c\u00f3 th\u1ec3 hi\u1ec3u r\u00f5 h\u01a1n nh\u1eefng g\u00ec t\u00f4i \u0111ang n\u00f3i \u0111\u1ebfn. Gi\u1ea3 s\u1eed, b\u1ea1n c\u00f3 m\u1ed9t b\u1ea3ng li\u1ec7t k\u00ea t\u00ean kh\u00e1ch h\u00e0ng, s\u1ea3n ph\u1ea9m \u0111\u00e3 mua v\u00e0 s\u1ed1 l\u01b0\u1ee3ng (Main Table). B\u1ea1n c\u0169ng c\u00f3 m\u1ed9t b\u1ea3ng th\u1ee9 2 ch\u1ee9a gi\u00e1 s\u1ea3n ph\u1ea9m (Lookup Table). Nhi\u1ec7m v\u1ee5 c\u1ee7a b\u1ea1n l\u00e0 l\u1eadp m\u1ed9t c\u00f4ng th\u1ee9c t\u00ecm t\u1ed5ng s\u1ed1 \u0111\u01a1n h\u00e0ng c\u1ee7a kh\u00e1ch h\u00e0ng nh\u1ea5t \u0111\u1ecbnh.<\/span><\/p>\n<figure id=\"attachment_33326\" aria-describedby=\"caption-attachment-33326\" style=\"width: 604px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-33326 size-full\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/cc3a1ch-ke1babft-he1bba3p-hc3a0m-vlookup-ve1bb9bi-hc3a0m-sum-4.png\" alt=\"C\u00e1ch k\u1ebft h\u1ee3p h\u00e0m VLOOKUP v\u1edbi h\u00e0m SUM 4\" width=\"604\" height=\"244\" title=\"cc3a1ch-ke1babft-he1bba3p-hc3a0m-vlookup-ve1bb9bi-hc3a0m-sum-4\"><figcaption id=\"caption-attachment-33326\" class=\"wp-caption-text\">C\u00e1ch k\u1ebft h\u1ee3p h\u00e0m VLOOKUP v\u1edbi h\u00e0m SUM 4<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\">Nh\u01b0 b\u1ea1n \u0111\u00e3 bi\u1ebft, b\u1ea1n kh\u00f4ng th\u1ec3 s\u1eed d\u1ee5ng <strong>h\u00e0m Vlookup trong Excel<\/strong> v\u00ec b\u1ea1n c\u00f3 nhi\u1ec1u tr\u01b0\u1eddng h\u1ee3p c\u1ee7a gi\u00e1 tr\u1ecb tra c\u1ee9u (m\u1ea3ng d\u1eef li\u1ec7u). Thay v\u00e0o \u0111\u00f3, b\u1ea1n s\u1eed d\u1ee5ng k\u1ebft h\u1ee3p c\u00e1c h\u00e0m SUM v\u00e0 LOOKUP nh\u01b0 sau:<\/span><\/p>\n<p><strong>=SUM(LOOKUP($C$2:$C$10,\u2019Lookup table\u2019!$A$2:$A$16,\u2019Lookup table\u2019!$B$2:$B$16)*$D$2:$D$10*($B$2:$B$10=$G$1))<\/strong><\/p>\n<p><span style=\"font-weight: 400\">V\u00ec \u0111\u00e2y l\u00e0 c\u00f4ng th\u1ee9c m\u1ea3ng n\u00ean h\u00e3y nh\u1edb nh\u1ea5n Ctrl + Shift + Enter \u0111\u1ec3 ho\u00e0n th\u00e0nh.<\/span><\/p>\n<figure id=\"attachment_33327\" aria-describedby=\"caption-attachment-33327\" style=\"width: 544px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-33327 size-full\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/cc3a1ch-ke1babft-he1bba3p-hc3a0m-vlookup-ve1bb9bi-hc3a0m-sum-5.png\" alt=\"C\u00e1ch k\u1ebft h\u1ee3p h\u00e0m VLOOKUP v\u1edbi h\u00e0m SUM 5\" width=\"544\" height=\"284\" title=\"cc3a1ch-ke1babft-he1bba3p-hc3a0m-vlookup-ve1bb9bi-hc3a0m-sum-5\"><figcaption id=\"caption-attachment-33327\" class=\"wp-caption-text\">C\u00e1ch k\u1ebft h\u1ee3p h\u00e0m VLOOKUP v\u1edbi h\u00e0m SUM 5<\/figcaption><\/figure>\n<p><span style=\"font-weight: 400\">V\u00e0 b\u00e2y gi\u1edd, h\u00e3y ph\u00e2n t\u00edch c\u00e1c th\u00e0nh ph\u1ea7n c\u1ee7a c\u00f4ng th\u1ee9c \u0111\u1ec3 b\u1ea1n hi\u1ec3u c\u00e1ch ho\u1ea1t \u0111\u1ed9ng c\u1ee7a t\u1eebng ch\u1ee9c n\u0103ng v\u00e0 c\u00f3 th\u1ec3 \u0111i\u1ec1u ch\u1ec9nh n\u00f3 cho d\u1eef li\u1ec7u c\u1ee7a ri\u00eang b\u1ea1n.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Ch\u00fang ta s\u1ebd t\u1ea1m g\u00e1c h\u00e0m SUM sang m\u1ed9t b\u00ean v\u00ec m\u1ee5c \u0111\u00edch l\u00e0 l\u00e0m r\u00f5 v\u00e0 t\u1eadp trung v\u00e0o 3 th\u00e0nh ph\u1ea7n \u0111\u01b0\u1ee3c nh\u00e2n:<\/span><\/p>\n<ol>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">LOOKUP($C$2:$C$10,\u2019Lookup table\u2019!$A$2:$A$16,\u2019Lookup table\u2019!$B$2:$B$16)<\/span><\/li>\n<\/ol>\n<p><span style=\"font-weight: 400\">H\u00e0m LOOKUP n\u00e0y tra c\u1ee9u h\u00e0ng h\u00f3a \u0111\u01b0\u1ee3c li\u1ec7t k\u00ea \u1edf c\u1ed9t C trong b\u1ea3ng ch\u00ednh v\u00e0 tr\u1ea3 v\u1ec1 gi\u00e1 t\u01b0\u01a1ng \u1ee9ng t\u1eeb c\u1ed9t B trong b\u1ea3ng tra c\u1ee9u.<\/span><\/p>\n<ol>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">$D$2:$D$10<\/span><\/li>\n<\/ol>\n<p><span style=\"font-weight: 400\">Th\u00e0nh ph\u1ea7n n\u00e0y tr\u1ea3 v\u1ec1 s\u1ed1 l\u01b0\u1ee3ng c\u1ee7a m\u1ed7i s\u1ea3n ph\u1ea9m \u0111\u01b0\u1ee3c mua b\u1edfi m\u1ed7i kh\u00e1ch h\u00e0ng, \u0111\u01b0\u1ee3c li\u1ec7t k\u00ea trong c\u1ed9t D trong b\u1ea3ng ch\u00ednh. Nh\u00e2n v\u1edbi gi\u00e1, \u0111\u01b0\u1ee3c tr\u1ea3 v\u1ec1 b\u1edfi h\u00e0m LOOKUP \u1edf tr\u00ean, n\u00f3 s\u1ebd cung c\u1ea5p cho b\u1ea1n chi ph\u00ed c\u1ee7a m\u1ed7i s\u1ea3n ph\u1ea9m \u0111\u00e3 mua.<\/span><\/p>\n<ol>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">$B$2:$B$10=$G$1<\/span><\/li>\n<\/ol>\n<p><span style=\"font-weight: 400\">C\u00f4ng th\u1ee9c n\u00e0y so s\u00e1nh t\u00ean c\u1ee7a kh\u00e1ch h\u00e0ng trong c\u1ed9t B v\u1edbi t\u00ean trong \u00f4 G1. N\u1ebfu t\u00ecm th\u1ea5y m\u1ed9t k\u1ebft qu\u1ea3 ph\u00f9 h\u1ee3p, n\u00f3 s\u1ebd tr\u1ea3 v\u1ec1 \u201c1\u201d, ng\u01b0\u1ee3c l\u1ea1i l\u00e0 \u201c0\u201d. B\u1ea1n s\u1eed d\u1ee5ng n\u00f3 \u0111\u01a1n gi\u1ea3n \u0111\u1ec3 \u201cc\u1eaft b\u1ecf\u201d t\u00ean c\u1ee7a kh\u00e1ch h\u00e0ng kh\u00e1c v\u1edbi t\u00ean trong \u00f4 G1, v\u00ec t\u1ea5t c\u1ea3 ch\u00fang ta \u0111\u1ec1u bi\u1ebft r\u1eb1ng b\u1ea5t k\u1ef3 s\u1ed1 n\u00e0o nh\u00e2n v\u1edbi 0 c\u0169ng \u0111\u1ec1u b\u1eb1ng kh\u00f4ng.<\/span><\/p>\n<p><span style=\"font-weight: 400\">B\u1edfi v\u00ec \u0111\u00e2y l\u00e0 c\u00f4ng th\u1ee9c m\u1ea3ng, n\u00f3 l\u1eb7p l\u1ea1i qu\u00e1 tr\u00ecnh \u0111\u01b0\u1ee3c m\u00f4 t\u1ea3 \u1edf tr\u00ean cho m\u1ed7i gi\u00e1 tr\u1ecb trong m\u1ea3ng tra c\u1ee9u. V\u00e0 cu\u1ed1i c\u00f9ng, h\u00e0m SUM t\u00ednh t\u1ed5ng c\u00e1c t\u00edch c\u1ee7a t\u1ea5t c\u1ea3 c\u00e1c ph\u00e9p nh\u00e2n.<\/span><\/p>\n<p><span style=\"font-weight: 400\"><em><strong>Ghi ch\u00fa<\/strong><\/em>: \u0110\u1ec3 c\u00f4ng th\u1ee9c LOOKUP ho\u1ea1t \u0111\u1ed9ng ch\u00ednh x\u00e1c, b\u1ea1n c\u1ea7n s\u1eafp x\u1ebfp c\u1ed9t tra c\u1ee9u trong b\u1ea3ng Tra c\u1ee9u c\u1ee7a m\u00ecnh theo th\u1ee9 t\u1ef1 t\u0103ng d\u1ea7n (t\u1eeb A \u0111\u1ebfn Z). N\u1ebfu vi\u1ec7c s\u1eafp x\u1ebfp kh\u00f4ng \u0111\u01b0\u1ee3c ch\u1ea5p nh\u1eadn tr\u00ean d\u1eef li\u1ec7u c\u1ee7a b\u1ea1n, h\u00e3y xem qua c\u00f4ng th\u1ee9c SUM v\u00e0 TRANSPOSE.<\/span><\/p>\n<h2><span class=\"ez-toc-section\" id=\"VLOOKUP_va_SUMIF_%E2%80%93_tra_cuu_va_tinh_tong_cac_gia_tri_voi_tieu_chi\"><\/span><b>VLOOKUP v\u00e0 SUMIF \u2013 tra c\u1ee9u v\u00e0 t\u00ednh t\u1ed5ng c\u00e1c gi\u00e1 tr\u1ecb v\u1edbi ti\u00eau ch\u00ed<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\"><a href=\"http:\/\/blog.ezworkapp.com\/cach-dung-ham-sumif-trong-excel-vi-du-cong-thuc.html\"><strong>C\u00e1ch d\u00f9ng h\u00e0m SUMIF trong Excel<\/strong><\/a> t\u01b0\u01a1ng t\u1ef1 nh\u01b0 h\u00e0m SUM m\u00e0 ch\u00fang ta \u0111\u00e3 th\u1ea3o lu\u1eadn c\u00e1ch m\u00e0 n\u00f3 t\u00ednh t\u1ed5ng gi\u00e1 tr\u1ecb. S\u1ef1 kh\u00e1c bi\u1ec7t l\u00e0 h\u00e0m SUMIF ch\u1ec9 t\u00ednh t\u1ed5ng nh\u1eefng gi\u00e1 tr\u1ecb \u0111\u00e1p \u1ee9ng ti\u00eau ch\u00ed b\u1ea1n ch\u1ec9 \u0111\u1ecbnh. V\u00ed d\u1ee5: c\u00f4ng th\u1ee9c SUMIF \u0111\u01a1n gi\u1ea3n nh\u1ea5t = SUMIF (A2: A10, \u201c&gt; 10\u201d) th\u00eam c\u00e1c gi\u00e1 tr\u1ecb trong \u00f4 A2 \u0111\u1ebfn A10 l\u1edbn h\u01a1n 10.<\/span><\/p>\n<p><span style=\"font-weight: 400\">V\u00e0 b\u00e2y gi\u1edd ch\u00fang ta h\u00e3y xem x\u00e9t m\u1ed9t tr\u01b0\u1eddng h\u1ee3p ph\u1ee9c t\u1ea1p h\u01a1n. Gi\u1ea3 s\u1eed b\u1ea1n c\u00f3 m\u1ed9t b\u1ea3ng li\u1ec7t k\u00ea t\u00ean v\u00e0 s\u1ed1 ID c\u1ee7a nh\u00e2n vi\u00ean b\u00e1n h\u00e0ng (Lookup_table). B\u1ea1n c\u00f3 m\u1ed9t b\u1ea3ng kh\u00e1c c\u00f3 c\u00f9ng ID v\u00e0 s\u1ed1 li\u1ec7u b\u00e1n h\u00e0ng \u0111\u01b0\u1ee3c li\u00ean k\u1ebft (Main_table). Nhi\u1ec7m v\u1ee5 c\u1ee7a b\u1ea1n l\u00e0 t\u00ecm t\u1ed5ng doanh s\u1ed1 b\u00e1n h\u00e0ng c\u1ee7a m\u1ed9t ng\u01b0\u1eddi nh\u1ea5t \u0111\u1ecbnh theo ID c\u1ee7a h\u1ecd. V\u00e0 b\u00e2y gi\u1edd, c\u00f3 2 v\u1ea5n \u0111\u1ec1:<\/span><\/p>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">B\u1ea3ng th\u01b0 ch\u1ee9a nhi\u1ec1u m\u1ee5c nh\u1eadp cho c\u00f9ng m\u1ed9t ID theo th\u1ee9 t\u1ef1 ng\u1eabu nhi\u00ean.<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\">B\u1ea1n kh\u00f4ng th\u1ec3 th\u00eam c\u1ed9t \u201cT\u00ean ng\u01b0\u1eddi b\u00e1n h\u00e0ng\u201d v\u00e0o b\u1ea3ng ch\u00ednh.<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\">V\u00e0 b\u00e2y gi\u1edd, ch\u00fang ta h\u00e3y t\u1ea1o m\u1ed9t c\u00f4ng th\u1ee9c, th\u1ee9 nahats, t\u00ecm t\u1ea5t c\u1ea3 doanh s\u1ed1 b\u00e1n h\u00e0ng c\u1ee7a m\u1ed9t ng\u01b0\u1eddi nh\u1ea5t \u0111\u1ecbnh v\u00e0 th\u1ee9 2, t\u00ednh t\u1ed5ng c\u00e1c gi\u00e1 tr\u1ecb t\u00ecm \u0111\u01b0\u1ee3c.<\/span><\/p>\n<p><span style=\"font-weight: 400\">Tr\u01b0\u1edbc khi ch\u00fang ta b\u1eaft \u0111\u1ea7u c\u00f4ng th\u1ee9c, h\u00e3y \u0111\u1ec3 t\u00f4i nh\u1eafc b\u1ea1n c\u00fa ph\u00e1p c\u1ee7a h\u00e0m SUMIF:<\/span><\/p>\n<p><strong>SUMIF(range, criteria, [sum_range])<\/strong><\/p>\n<ul>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\"><strong>Range<\/strong> \u2013 th\u00f4ng s\u1ed1 n\u00e0y c\u00f3 th\u1ec3 hi\u1ec3u \u0111\u01b0\u1ee3c, ch\u1ec9 \u0111\u01a1n gi\u1ea3n l\u00e0 m\u1ed9t d\u1ea3i \u00f4 m\u00e0 b\u1ea1n mu\u1ed1n \u0111\u00e1nh gi\u00e1 theo c\u00e1c ti\u00eau ch\u00ed \u0111\u00e3 ch\u1ec9 \u0111\u1ecbnh.<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\"><strong>Criteria<\/strong> \u2013 \u0111i\u1ec1u ki\u1ec7n cho c\u00f4ng th\u1ee9c bi\u1ebft gi\u00e1 tr\u1ecb n\u00e0o c\u1ea7n t\u00ednh t\u1ed5ng. N\u00f3 c\u00f3 th\u1ec3 \u0111\u01b0\u1ee3c cung c\u1ea5p d\u01b0\u1edbi d\u1ea1ng m\u1ed9t s\u1ed1, tham chi\u1ebfu \u00f4, bi\u1ec3u th\u1ee9c ho\u1eb7c m\u1ed9t h\u00e0m Excel kh\u00e1c.<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\"><strong>Sum_range<\/strong> \u2013 tham s\u1ed1 n\u00e0y t\u00f9y ch\u1ecdn, nh\u01b0ng r\u1ea5t quan tr\u1ecdng. N\u00f3 x\u00e1c \u0111\u1ecbnh ph\u1ea1m vi n\u01a1i c\u00e1c gi\u00e1 tr\u1ecb c\u1ee7a \u00f4 t\u01b0\u01a1ng \u1ee9ng s\u1ebd \u0111\u01b0\u1ee3c th\u00eam v\u00e0o. N\u1ebfu b\u1ecb b\u1ecf qua, Excel s\u1ebd t\u00ednh t\u1ed5ng c\u00e1c gi\u00e1 tr\u1ecb c\u1ee7a \u00f4 \u0111\u01b0\u1ee3c ch\u1ec9 \u0111\u1ecbnh trong tham s\u1ed1 ph\u1ea1m vi (tham s\u1ed1 th\u1ee9 nh\u1ea5t).<\/span><\/li>\n<\/ul>\n<p><span style=\"font-weight: 400\"><em><strong>L\u01b0u \u00fd:<\/strong> <\/em>h\u00e3y x\u00e1c \u0111\u1ecbnh 3 tham s\u1ed1 cho h\u00e0m SUMIF. Nh\u01b0 b\u1ea1n bi\u1ebft, ch\u00fang t\u00f4i mu\u1ed1n t\u00ednh t\u1ed5ng t\u1ea5t c\u1ea3 doanh s\u1ed1 b\u00e1n h\u00e0ng \u0111\u01b0\u1ee3c th\u1ef1c hi\u1ec7n b\u1edfi m\u1ed9t ng\u01b0\u1eddi nh\u1ea5t \u0111\u1ecbnh c\u00f3 t\u00ean \u0111\u01b0\u1ee3c nh\u1eadp v\u00e0o \u00f4 F2 trong b\u1ea3ng ch\u00ednh (vui l\u00f2ng xem h\u00ecnh tr\u00ean).<\/span><\/p>\n<ol>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\"><strong>Range<\/strong> \u2013 v\u00ec \u0111ang t\u00ecm ki\u1ebfm ID nh\u00e2n vi\u00ean b\u00e1n h\u00e0ng, tham s\u1ed1 ph\u1ea1m vi c\u1ee7a h\u00e0m SUMIF l\u00e0 c\u1ed9t B trong b\u1ea3ng ch\u00ednh. V\u00ec v\u1eady, b\u1ea1n c\u00f3 th\u1ec3 nh\u1eadp ph\u1ea1m vi B:B ho\u1eb7c n\u1ebfu b\u1ea1n chuy\u1ec3n \u0111\u1ed5i d\u1eef li\u1ec7u c\u1ee7a m\u00ecnh th\u00e0nh m\u1ed9t b\u1ea3ng, b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng t\u00ean c\u1ee7a c\u1ed9t \u0111\u1ec3 thay th\u1ebf: Main_table[ID]<\/span><\/li>\n<li style=\"font-weight: 400\"><span style=\"font-weight: 400\"><strong>Criteria<\/strong> \u2013 b\u1edfi v\u00ec ch\u00fang ta c\u00f3 t\u00ean c\u1ee7a nh\u00e2n vi\u00ean b\u00e1n h\u00e0ng trong m\u1ed9t b\u1ea3ng kh\u00e1c (b\u1ea3ng tra c\u1ee9u), ch\u00fang ta ph\u1ea3i s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c VLOOKUP \u0111\u1ec3 t\u00ecm ID t\u01b0\u01a1ng \u1ee9ng v\u1edbi m\u1ed9t ng\u01b0\u1eddi nh\u1ea5t \u0111\u1ecbnh. T\u00ean c\u1ee7a ng\u01b0\u1eddi \u0111\u00f3 \u0111\u01b0\u1ee3c vi\u1ebft trong \u00f4 F2 trong b\u1ea3ng ch\u00ednh, v\u00ec v\u1eady tra c\u1ee9u b\u1eb1ng c\u00f4ng th\u1ee9c sau: VLOOKUP($F$2,Lookup_table,2,FALSE)<\/span><\/li>\n<\/ol>\n<p><span style=\"font-weight: 400\">T\u1ea5t nhi\u00ean, b\u1ea1n c\u00f3 th\u1ec3 nh\u1eadp t\u00ean v\u00e0o ti\u00eau ch\u00ed tra c\u1ee9u c\u1ee7a h\u00e0m VLOOKUP, nh\u01b0ng s\u1eed d\u1ee5ng tham chi\u1ebfu \u00f4 tuy\u1ec7t \u0111\u1ed1i l\u00e0 c\u00e1ch t\u1ed1t h\u01a1n v\u00ec \u0111i\u1ec1u n\u00e0y t\u1ea1o ra m\u1ed9t c\u00f4ng th\u1ee9c chung m\u00e0 ho\u1ea1t \u0111\u1ed9ng cho b\u1ea5t k\u1ef3 t\u00ean n\u00e0o \u0111\u01b0\u1ee3c th\u00eam v\u00e0o \u00f4.<\/span><\/p>\n<p><span style=\"font-weight: 400\">\u00a0 \u00a0 \u00a0 3. <strong>Sum_range<\/strong> \u2013 \u0111\u00e2y l\u00e0 ph\u1ea7n d\u1ec5 nh\u1ea5t. V\u00ec s\u1ed1 b\u00e1n h\u00e0ng n\u1eb1m trong c\u1ed9t C c\u00f3 t\u00ean l\u00e0 \u201cSales\u201d, ch\u00fang ta ch\u1ec9 c\u1ea7n \u0111\u1eb7t Main_table[Sales].<\/span><\/p>\n<p><span style=\"font-weight: 400\">B\u00e2y gi\u1edd, t\u1ea5t c\u1ea3 nh\u1eefng g\u00ec b\u1ea1n c\u1ea7n l\u00e0 t\u1eadp h\u1ee3p c\u00e1c ph\u1ea7n c\u1ee7a c\u00f4ng th\u1ee9c v\u00e0 c\u00f4ng th\u1ee9c SUMIF + VLOOKUP c\u1ee7a b\u1ea1n \u0111\u00e3 s\u1eb5n s\u00e0ng:<\/span><\/p>\n<p><strong>=SUMIF(Main_table[ID], VLOOKUP($F$2, Lookup_table, 2, FALSE), Main_table[Sales])<\/strong><\/p>\n<p><strong><a href=\"http:\/\/blog.ezworkapp.com\/cach-ket-hop-ham-vlookup-voi-ham-sum-ham-sumif-trong-excel.html\">K\u1ebft h\u1ee3p h\u00e0m h\u00e0m VLOOKUP v\u1edbi h\u00e0m SUM\/ h\u00e0m SUMIF<\/a> <\/strong>trong Excel l\u00e0 m\u1ed9t k\u1ef9 thu\u1eadt <a href=\"http:\/\/blog.ezworkapp.com\/khoa-hoc-excel-tu-co-ban-toi-nang-cao-danh-cho-dan-van-phong-2010-20132016\"><strong>Excel c\u01a1 b\u1ea3n<\/strong><\/a> nh\u01b0ng kh\u00e1 d\u1ec5 nh\u1ea7m. H\u1ecdc Excel Online hy v\u1ecdng c\u00e1c b\u1ea1n \u0111\u00e3 n\u1eafm r\u00f5 c\u00e1ch k\u1ebft h\u1ee3p qua b\u00e0i vi\u1ebft n\u00e0y. Tr\u00e2n tr\u1ecdng!<\/p>\n<\/div>\n<div class=\"uk-panel uk-margin uk-width-xlarge\">\n<p>Click \u0111\u0103ng k\u00fd h\u1ecdc t\u1ea1i:<\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/khoa-hoc\" target=\"_blank\" rel=\"noopener\">T\u1ea5t c\u1ea3 kho\u00e1 h\u1ecdc\u00a0<span>\ud83d\udcd6<\/span><\/a><\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-kich-hoat-khoa-hoc-tai-hoc-excel-online.html\" target=\"_blank\" rel=\"noopener\">H\u01b0\u1edbng d\u1eabn k\u00edch ho\u1ea1t<\/a><\/p>\n<p>HKD H\u1ecdc Excel Online<br \/>S\u1ed1 \u0110K: 17A80048102<\/p>\n<\/div>\n<div class=\"uk-panel uk-text-small uk-margin uk-margin-remove-bottom uk-text-center\">\n<p>\u00a9  document.currentScript.insertAdjacentHTML(&#8216;afterend&#8217;, &#8221; + new Intl.DateTimeFormat(document.documentElement.lang, {year: &#8216;numeric&#8217;}).format() + &#8221;);  H\u1ecdc Excel Online. All rights reserved.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<div class=\"ad-inserterpro\">\n<a href=\"https:\/\/hocexcel.online\/course\/preview\/ex101-excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/ex101_92019-nho-434.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":5128,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-5127","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-co-ban"],"_links":{"self":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/5127","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=5127"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/5127\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/5128"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=5127"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=5127"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=5127"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}