{"id":5859,"date":"2024-12-21T08:21:30","date_gmt":"2024-12-21T08:21:30","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/21\/excel-ke-toan-ung-dung-ham-sumproduct-phan-1\/"},"modified":"2024-12-21T08:21:30","modified_gmt":"2024-12-21T08:21:30","slug":"excel-ke-toan-ung-dung-ham-sumproduct-phan-1","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/21\/excel-ke-toan-ung-dung-ham-sumproduct-phan-1\/","title":{"rendered":"Excel k\u1ebf to\u00e1n: \u1ee8ng d\u1ee5ng h\u00e0m SUMPRODUCT \u2013 Ph\u1ea7n 1"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p>Ch\u00e0o c\u00e1c b\u1ea1n,<\/p>\n<p>H\u00f4m nay blog.hocexcel.online g\u1eedi t\u1edbi c\u00e1c b\u1ea1n seri b\u00e0i vi\u1ebft v\u1ec1 ch\u1ee7 \u0111\u1ec1 Excel v\u1edbi k\u1ebf to\u00e1n: \u201c<strong>\u1ee8ng d\u1ee5ng h\u00e0m SUMPRODUCT<\/strong>\u201d. \u0110\u00e2y l\u00e0 m\u1ed9t h\u00e0m c\u00f3 s\u1ee9c m\u1ea1nh r\u1ea5t l\u1edbn, c\u00f3 th\u1ec3 thay th\u1ebf h\u1ea7u h\u1ebft m\u1ecdi h\u00e0m khi l\u00e0m k\u1ebf to\u00e1n tr\u00ean excel, hay n\u00f3i c\u00e1ch kh\u00e1c khi ngh\u0129 t\u1edbi l\u00e0m k\u1ebf to\u00e1n tr\u00ean excel l\u00e0 ch\u00fang ta ph\u1ea3i ngh\u0129 ngay t\u1edbi h\u00e0m SUMPRODUCT. T\u1ea1i sao n\u00f3 l\u1ea1i quan tr\u1ecdng nh\u01b0 v\u1eady? Sau \u0111\u00e2y ch\u00fang ta c\u00f9ng t\u00ecm hi\u1ec3u c\u00e1ch \u1ee9ng d\u1ee5ng h\u00e0m SUMPRODUCT s\u1ebd r\u00f5.<\/p>\n<p>N\u1ed9i dung Seri b\u00e0i vi\u1ebft:<\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/excel-ke-toan-ung-dung-ham-sumproduct-phan-1.html\">Ph\u1ea7n 1: C\u1ea5u tr\u00fac h\u00e0m SUMPRODUCT,\u00a0\u1ee9ng d\u1ee5ng v\u00e0o vi\u1ec7c t\u00ednh t\u1ed5ng theo nhi\u1ec1u\u00a0\u0111i\u1ec1u ki\u1ec7n, so s\u00e1nh v\u1edbi h\u00e0m SUMIFS<\/a><\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/excel-ke-toan-ung-dung-ham-sumproduct-phan-2.html\">Ph\u1ea7n 2:\u00a0T\u00ednh t\u1ed5ng ph\u00e1t sinh c\u1ee7a t\u00e0i kho\u1ea3n t\u1ed5ng h\u1ee3p d\u1ef1a tr\u00ean ghi s\u1ed5 c\u1ee7a t\u00e0i kho\u1ea3n chi ti\u1ebft<\/a><\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/excel-ke-toan-ung-dung-ham-sumproduct-phan-3.html\">Ph\u1ea7n 3:\u00a0\u1ee8ng d\u1ee5ng h\u00e0m SUMPRODUCT trong k\u1ebf to\u00e1n kho<\/a><\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/excel-ke-toan-ung-dung-ham-sumproduct-phan-4.html\">Ph\u1ea7n 4:\u00a0\u1ee8ng d\u1ee5ng h\u00e0m SUMPRODUCT trong k\u1ebf to\u00e1n ti\u1ec1n l\u01b0\u01a1ng: x\u1eed l\u00fd d\u1eef li\u1ec7u m\u00e1y ch\u1ea5m c\u00f4ng<\/a><\/p>\n<p>\u2026<\/p>\n<div class=\"code-block code-block-3\" style=\"margin: 8px 0;clear: both\">\n<div class=\"ad-inserterpro\">\n<p>D\u00f9 \u0111\u00e3 c\u00f3 ph\u1ea7n m\u1ec1m, nh\u01b0ng k\u1ef9 n\u0103ng Excel v\u1eabn c\u1ef1c k\u1ef3 quan tr\u1ecdng v\u1edbi k\u1ebf to\u00e1n, b\u1ea1n \u0111\u00e3 v\u1eefng Excel ch\u01b0a? H\u00e3y \u0111\u1ec3 t\u00f4i gi\u00fap b\u1ea1n, \u0111\u0103ng k\u00fd kho\u00e1 h\u1ecdc Excel:<\/p>\n<p><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-517.gif\" title=\"ex101_92019-nho-517\"><br \/>\n<\/a>\n<\/div>\n<\/div>\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-219.gif\" title=\"vba101_92019-nho-219\"><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-517.gif\" title=\"ex101_92019-nho-517\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p><strong>C\u1ea5u tr\u00fac h\u00e0m SUMPRODUCT(array1, [array2], [array3], \u2026)<\/strong><\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/ham-sumproduct-trong-excel-va-vi-du-cong-thuc.html\">H\u00e0m Sumproduct<\/a> l\u00e0 h\u00e0m x\u1eed l\u00fd v\u1edbi \u0111\u1ed1i t\u01b0\u1ee3ng l\u00e0 c\u00e1c m\u1ea3ng. \u1ede \u0111\u00e2y ch\u00fang ta c\u1ea7n l\u01b0u \u00fd ph\u00e2n bi\u1ec7t kh\u00e1i ni\u1ec7m \u201cM\u1ea3ng = Array\u201d v\u1edbi kh\u00e1i ni\u1ec7m \u201cV\u00f9ng = Range\u201d (h\u00e0m SUMIFS l\u00e0 x\u00e9t tr\u00ean \u0111\u1ed1i t\u01b0\u1ee3ng Range). Array c\u00f3 s\u1ee9c m\u1ea1nh h\u01a1n h\u1eb3n Range khi ch\u00fang ta c\u00f3 th\u1ec3 tham chi\u1ebfu t\u1edbi m\u1ed9t ph\u1ea7n n\u1ed9i dung nh\u1ea5t \u0111\u1ecbnh trong m\u1ed7i \u00f4 trong m\u1ea3ng (c\u00f3 th\u1ec3 kh\u00f4ng ph\u1ea3i to\u00e0n b\u1ed9 n\u1ed9i dung trong m\u1ed7i \u00f4 \u0111\u00f3), c\u00f2n Range kh\u00f4ng l\u00e0m \u0111\u01b0\u1ee3c \u0111i\u1ec1u \u0111\u00f3. \u0110\u1ed5i l\u1ea1i vi\u1ec7c s\u1eed d\u1ee5ng Array l\u1ea1i kh\u00f3 h\u01a1n d\u00f9ng Range.<\/p>\n<p>Sumproduct l\u00e0 gh\u00e9p gi\u1eefa SUM v\u1edbi PRODUCT, l\u00e0 T\u1ed5ng v\u1edbi T\u00edch, \u0111\u01b0\u1ee3c hi\u1ec3u l\u00e0 th\u1ecfa m\u00e3n \u0111\u1ed3ng th\u1eddi nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n th\u00ec s\u1ebd t\u00ednh t\u1ed5ng c\u00e1c gi\u00e1 tr\u1ecb th\u1ecfa m\u00e3n \u0111i\u1ec1u ki\u1ec7n.<\/p>\n<p><strong>\u1ee8ng d\u1ee5ng 1: T\u00ednh t\u1ed5ng theo nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n, so s\u00e1nh v\u1edbi h\u00e0m SUMIFS<\/strong><\/p>\n<p><strong><em>\u0110\u1ec1 b\u00e0i:<\/em><\/strong> Cho b\u1ea3ng s\u1ed1 li\u1ec7u t\u1eeb v\u00f9ng A1:E9, y\u00eau c\u1ea7u t\u00ednh t\u1ed5ng s\u1ed1 N\u1ee3 cho t\u00e0i kho\u1ea3n 1111 (G6) theo th\u1eddi gian t\u00f9y ch\u1ecdn T\u1eeb ng\u00e0y, \u0110\u1ebfn ng\u00e0y (H3 v\u00e0 H4) theo 2 c\u00e1ch:<\/p>\n<p>C\u00e1ch 1: \u00f4 H6 s\u1eed d\u1ee5ng h\u00e0m SUMPRODUCT<\/p>\n<p>C\u00e1ch 2: \u00f4 H7 s\u1eed d\u1ee5ng h\u00e0m SUMIFS<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-11677 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/ud1-pic0.png\" width=\"733\" height=\"262\" title=\"ud1-pic0\"><\/p>\n<p><em><strong>C\u00e1ch l\u00e0m:<\/strong><\/em><\/p>\n<p><strong>v\u1edbi h\u00e0m SUMIFS ch\u00fang ta ph\u00e2n t\u00edch n\u1ed9i dung h\u00e0m nh\u01b0 sau:<\/strong><\/p>\n<p>Sum_range l\u00e0 v\u00f9ng c\u1ea7n t\u00ednh t\u1ed5ng, l\u00e0 E2:E9<\/p>\n<ul>\n<li>Criteria_range1 l\u00e0 v\u00f9ng \u0111i\u1ec1u ki\u1ec7n th\u1ee9 1, c\u1ed9t TK N\u1ee3 (C2:C9)<\/li>\n<li>Criteria1 l\u00e0 \u0111i\u1ec1u ki\u1ec7n th\u1ee9 1, n\u1eb1m trong v\u00f9ng \u0111i\u1ec1u ki\u1ec7n th\u1ee9 1, \u00f4 G6<\/li>\n<li>Criteria_range2 l\u00e0 v\u00f9ng \u0111i\u1ec1u ki\u1ec7n th\u1ee9 2, c\u1ed9t Ng\u00e0y (A2:A9)<\/li>\n<li>Criteria2 l\u00e0 \u0111i\u1ec1u ki\u1ec7n th\u1ee9 2, n\u1eb1m trong v\u00f9ng \u0111i\u1ec1u ki\u1ec7n th\u1ee9 2, \u00f4 H3, \u1edf \u0111\u00e2y l\u00e0 &gt;=H3<\/li>\n<li>Criteria_range3 l\u00e0 v\u00f9ng \u0111i\u1ec1u ki\u1ec7n th\u1ee9 3, c\u1ed9t Ng\u00e0y (A2:A9)<\/li>\n<li>Criteria3 l\u00e0 \u0111i\u1ec1u ki\u1ec7n th\u1ee9 3, n\u1eb1m trong v\u00f9ng \u0111i\u1ec1u ki\u1ec7n th\u1ee9 2, \u00f4 H4, \u1edf \u0111\u00e2y l\u00e0 &lt;=H4\u00a0 (v\u00ec gi\u00e1 tr\u1ecb ng\u00e0y bao g\u1ed3m 2 gi\u00e1 tr\u1ecb n\u00ean ph\u1ea3i t\u00e1ch ra 2 \u0111i\u1ec1u ki\u1ec7n)<\/li>\n<\/ul>\n<blockquote>\n<p>H7 = SUMIFS($E$2:$E$9,$C$2:$C$9,$G$7,$A$2:$A$9,\u201d&gt;=\u201d&amp;$H$3,$A$2:$A$9,\u201d&lt;=\u201d&amp;$H$4)<\/p>\n<\/blockquote>\n<p><strong>V\u1edbi h\u00e0m SUMPRODUCT ch\u00fang ta ph\u00e2n t\u00edch n\u1ed9i dung h\u00e0m nh\u01b0 sau:<\/strong><\/p>\n<ul>\n<li>Array1 l\u00e0 v\u00f9ng c\u1ea7n t\u00ednh t\u1ed5ng E2:E9<\/li>\n<li>Array2 l\u00e0 v\u00f9ng ch\u1ee9a TK N\u1ee3, v\u1edbi c\u00e1c \u00f4 c\u00f3 gi\u00e1 tr\u1ecb l\u00e0 1111<\/li>\n<li>Array3 l\u00e0 v\u00f9ng ch\u1ee9a Ng\u00e0y, v\u1edbi c\u00e1c \u00f4 c\u00f3 ng\u00e0y &gt;=H3<\/li>\n<li>Array4 l\u00e0 v\u00f9ng ch\u1ee9a Ng\u00e0y, v\u1edbi c\u00e1c \u00f4 c\u00f3 ng\u00e0y &lt;=H4<\/li>\n<\/ul>\n<blockquote>\n<p>H6 = SUMPRODUCT($E$2:$E$9*($C$2:$C$9=G6)*($A$2:$A$9&gt;=$H$3)*($A$2:$A$9&lt;=$H$4))<\/p>\n<\/blockquote>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-11678 aligncenter\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/ud1-pic1.png\" width=\"1299\" height=\"263\" title=\"ud1-pic1\"><\/p>\n<p><strong>*** Nh\u1eadn x\u00e9t:<\/strong><\/p>\n<ul>\n<li>K\u1ebft qu\u1ea3 c\u1ee7a 2 h\u00e0m l\u00e0 nh\u01b0 nhau =&gt; C\u00f3 t\u00e1c d\u1ee5ng gi\u1ed1ng nhau trong tr\u01b0\u1eddng h\u1ee3p n\u00e0y (\u0111i\u1ec1u ki\u1ec7n \u0111\u01b0\u1ee3c x\u00e1c \u0111\u1ecbnh c\u1ee5 th\u1ec3 v\u00e0 \u0111\u1ed1i t\u01b0\u1ee3ng tham chi\u1ebfu trong Array l\u00e0 \u0111\u1ed1i t\u01b0\u1ee3ng c\u00f3 th\u1ec3 x\u00e1c \u0111\u1ecbnh tr\u1ef1c ti\u1ebfp).<\/li>\n<li>\u0110i\u1ec1u ki\u1ec7n h\u00e0m SUMIFS bi\u1ec3u di\u1ec5n kh\u00e1c h\u00e0m SUMPRODUCT, Criteria_range v\u00e0 Criteria \u0111\u01b0\u1ee3c ph\u00e2n bi\u1ec7t r\u00f5 t\u1eebng th\u00e0nh ph\u1ea7n trong h\u00e0m, c\u00f2n Array \u0111\u01b0\u1ee3c g\u1ed9p c\u1ea3 Range v\u00e0 Criteria v\u00e0o m\u1ed9t<\/li>\n<li>L\u01b0u \u00fd khi x\u00e1c \u0111\u1ecbnh \u0111i\u1ec1u ki\u1ec7n d\u1ea1ng &gt;=, &lt;= th\u00ec v\u1edbi h\u00e0m SUMIFS ph\u1ea3i \u0111\u1eb7t d\u1ea5u \u0111\u00f3 trong d\u1ea5u nh\u00e1y k\u00e9p, n\u1ed1i v\u1edbi Criteria b\u1edfi d\u1ea5u &amp;, c\u00f2n h\u00e0m SUMPRODUCT c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng tr\u1ef1c ti\u1ebfp.<\/li>\n<li>Tr\u01b0\u1eddng h\u1ee3p n\u00e0y c\u00f3 th\u1ec3 th\u1ea5y SUMPRODUCT gi\u1ed1ng v\u1edbi SUMIFS v\u1ec1 \u0111\u1ed9 d\u00e0i v\u00e0 c\u00e1ch bi\u1ec7n lu\u1eadn, th\u1ee9 t\u1ef1 logic c\u0169ng gi\u1ed1ng nhau. H\u00e0m SUMIFS l\u00e0 m\u1ed9t h\u00e0m c\u00f3 t\u00ednh\u00a0\u1ee9ng d\u1ee5ng r\u1ea5t cao v\u00e0 d\u1ec5 vi\u1ebft, v\u00e0 ch\u00fang ta c\u00f3 th\u1ec3 th\u1ea5y h\u00e0m SUMPRODUCT c\u0169ng c\u00f3 th\u1ec3 thay th\u1ebf ho\u00e0n to\u00e0n cho h\u00e0m SUMIFS<\/li>\n<\/ul>\n<p>Trong c\u00e1c b\u00e0i h\u1ecdc sau ch\u00fang ta s\u1ebd t\u00ecm hi\u1ec3u c\u00e1c \u1ee9ng d\u1ee5ng kh\u00e1c c\u1ee7a h\u00e0m SUMPRODUCT \u0111\u1ec3 kh\u00e1m ph\u00e1 th\u00eam s\u1ee9c m\u1ea1nh c\u1ee7a h\u00e0m n\u00e0y.<\/p>\n<p>C\u1ea3m\u00a0\u01a1n c\u00e1c b\u1ea1n\u00a0\u0111\u00e3 ch\u00fa\u00a0\u00fd theo d\u00f5i.<\/p>\n<blockquote>\n<p>\u0110\u1eebng b\u1ecf l\u1ee1: Kh\u00f3a\u00a0<a href=\"http:\/\/blog.ezworkapp.com\/khoa-hoc-excel-ke-toan.html\">ho\u0323c Excel k\u1ebf to\u00e1n b\u00e1n h\u00e0ng<\/a><\/p>\n<\/blockquote>\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<p>D\u00f9 \u0111\u00e3 c\u00f3 ph\u1ea7n m\u1ec1m, nh\u01b0ng k\u1ef9 n\u0103ng Excel v\u1eabn c\u1ef1c k\u1ef3 quan tr\u1ecdng v\u1edbi k\u1ebf to\u00e1n, b\u1ea1n \u0111\u00e3 v\u1eefng Excel ch\u01b0a? H\u00e3y \u0111\u1ec3 t\u00f4i gi\u00fap b\u1ea1n, \u0111\u0103ng k\u00fd kho\u00e1 h\u1ecdc Excel:<\/p>\n<p><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-517.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":5860,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-5859","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\/5859","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=5859"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/5859\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/5860"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=5859"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=5859"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=5859"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}