{"id":5684,"date":"2024-12-18T23:59:22","date_gmt":"2024-12-18T23:59:22","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/18\/huong-dan-cach-gop-du-lieu-tu-nhieu-bang-vao-mot-bang-voi-ham-sumproduct\/"},"modified":"2024-12-18T23:59:22","modified_gmt":"2024-12-18T23:59:22","slug":"huong-dan-cach-gop-du-lieu-tu-nhieu-bang-vao-mot-bang-voi-ham-sumproduct","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/18\/huong-dan-cach-gop-du-lieu-tu-nhieu-bang-vao-mot-bang-voi-ham-sumproduct\/","title":{"rendered":"H\u01b0\u1edbng d\u1eabn c\u00e1ch g\u1ed9p d\u1eef li\u1ec7u t\u1eeb nhi\u1ec1u b\u1ea3ng v\u00e0o m\u1ed9t b\u1ea3ng v\u1edbi h\u00e0m sumproduct"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p>Trong c\u00f4ng vi\u1ec7c h\u00e0ng ng\u00e0y ch\u00fang ta c\u00f3 th\u1ec3 hay g\u1eb7p ph\u1ea3i y\u00eau c\u1ea7u g\u1ed9p d\u1eef li\u1ec7u t\u1eeb nhi\u1ec1u b\u1ea3ng v\u00e0o trong m\u1ed9t b\u1ea3ng. V\u00e0 ch\u1eafc h\u1eb3n b\u1ea1n r\u1ea5t mu\u1ed1n bi\u1ebft c\u00f3 h\u00e0m n\u00e0o gi\u00fap b\u1ea1n l\u00e0m vi\u1ec7c n\u00e0y m\u1ed9t c\u00e1ch nhanh ch\u00f3ng. V\u1eady th\u00ec h\u00e3y c\u00f9ng H\u1ecdc\u00a0Excel Online t\u00ecm hi\u1ec3u\u00a0c\u00e1ch g\u1ed9p d\u1eef li\u1ec7u t\u1eeb nhi\u1ec1u b\u1ea3ng v\u00e0o m\u1ed9t b\u1ea3ng v\u1edbi h\u00e0m SUMPRODUCT.<\/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-676361d9b2f81\" 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\/huong-dan-cach-gop-du-lieu-tu-nhieu-bang-vao-mot-bang-voi-ham-sumproduct.html#Yeu_cau_gop_du_lieu_tu_nhieu_bang_vao_mot_bang\" title=\"Y\u00eau c\u1ea7u g\u1ed9p d\u1eef li\u1ec7u t\u1eeb nhi\u1ec1u b\u1ea3ng v\u00e0o m\u1ed9t b\u1ea3ng\">Y\u00eau c\u1ea7u g\u1ed9p d\u1eef li\u1ec7u t\u1eeb nhi\u1ec1u b\u1ea3ng v\u00e0o m\u1ed9t b\u1ea3ng<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-2\"><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/blog.hocexcel.online\/huong-dan-cach-gop-du-lieu-tu-nhieu-bang-vao-mot-bang-voi-ham-sumproduct.html#Cach_gop_du_lieu_tu_nhieu_bang_vao_mot_bang_voi_ham_Sumproduct\" title=\"C\u00e1ch g\u1ed9p d\u1eef li\u1ec7u t\u1eeb nhi\u1ec1u b\u1ea3ng v\u00e0o m\u1ed9t b\u1ea3ng v\u1edbi h\u00e0m Sumproduct\">C\u00e1ch g\u1ed9p d\u1eef li\u1ec7u t\u1eeb nhi\u1ec1u b\u1ea3ng v\u00e0o m\u1ed9t b\u1ea3ng v\u1edbi h\u00e0m Sumproduct<\/a><\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2><span class=\"ez-toc-section\" id=\"Yeu_cau_gop_du_lieu_tu_nhieu_bang_vao_mot_bang\"><\/span>Y\u00eau c\u1ea7u g\u1ed9p d\u1eef li\u1ec7u t\u1eeb nhi\u1ec1u b\u1ea3ng v\u00e0o m\u1ed9t b\u1ea3ng<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>H\u00e3y xem v\u00ed d\u1ee5 d\u01b0\u1edbi\u00a0\u0111\u00e2y:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-23800\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/gop-du-lieu-voi-sumproduct-de-bai.png\" width=\"993\" height=\"315\" title=\"gop-du-lieu-voi-sumproduct-de-bai\"><\/p>\n<p>Trong h\u00ecnh tr\u00ean, ta th\u1ea5y c\u00f3 2 b\u1ea3ng d\u1eef li\u1ec7u nh\u1ecf l\u00e0 B\u1ea3ng nh\u00f3m 1 v\u00e0 B\u1ea3ng nh\u00f3m 2. Hai b\u1ea3ng n\u00e0y c\u00f3 c\u1ea5u tr\u00fac gi\u1ed1ng nhau nh\u01b0ng n\u1ed9i dung l\u1ea1i kh\u00f4ng gi\u1ed1ng nhau. Y\u00eau c\u1ea7u l\u00e0 ch\u00fang ta ph\u1ea3i g\u1ed9p d\u1eef li\u1ec7u t\u1eeb 2 b\u1ea3ng Nh\u00f3m 1 v\u00e0 Nh\u00f3m 2 v\u00e0o chung 1 b\u1ea3ng.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Cach_gop_du_lieu_tu_nhieu_bang_vao_mot_bang_voi_ham_Sumproduct\"><\/span>C\u00e1ch g\u1ed9p d\u1eef li\u1ec7u t\u1eeb nhi\u1ec1u b\u1ea3ng v\u00e0o m\u1ed9t b\u1ea3ng v\u1edbi h\u00e0m Sumproduct<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><strong>B\u01b0\u1edbc 1:\u00a0\u0111\u1eb7t t\u00ean cho c\u00e1c v\u00f9ng d\u1eef li\u1ec7u<\/strong><\/p>\n<p>\u0110\u1ec3 gi\u00fap vi\u1ec7c x\u00e2y d\u1ef1ng c\u00f4ng th\u1ee9c\u00a0\u0111\u01a1n gi\u1ea3n h\u01a1n, ch\u00fang ta s\u1ebd\u00a0\u0111\u1eb7t t\u00ean cho c\u00e1c b\u1ea3ng d\u1eef li\u1ec7u nh\u01b0 sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-23804\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/dat-ten-vung-du-lieu.png\" width=\"1070\" height=\"319\" title=\"dat-ten-vung-du-lieu\"><\/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-497.gif\" title=\"ex101_92019-nho-497\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<ul>\n<li>V\u00f9ng A3:A6:\u00a0\u0111\u1eb7t t\u00ean l\u00e0 B1_SanPham (b\u1ea3ng nh\u00f3m 1, c\u1ed9t S\u1ea3n ph\u1ea9m)<\/li>\n<li>V\u00f9ng B2:D2\u00a0\u0111\u1eb7t t\u00ean l\u00e0 B1_NhanVien (b\u1ea3ng nh\u00f3m 1, d\u00f2ng Nh\u00e2n vi\u00ean)<\/li>\n<li>V\u00f9ng B3:D6\u00a0\u0111\u1eb7t t\u00ean l\u00e0 B1_KetQua (b\u1ea3ng nh\u00f3m 1, v\u00f9ng k\u1ebft qu\u1ea3 c\u1ee7a t\u1eebng s\u1ea3n ph\u1ea9m theo t\u1eebng nh\u00e2n vi\u00ean)<\/li>\n<\/ul>\n<p>T\u01b0\u01a1ng t\u1ef1 nh\u01b0 v\u1eady\u00a0\u0111\u1eb7t cho b\u1ea3ng Nh\u00f3m 2<\/p>\n<p><strong>B\u01b0\u1edbc 2: X\u00e1c\u00a0\u0111\u1ecbnh tr\u00ecnh t\u1ef1 th\u1ef1c hi\u1ec7n<\/strong><\/p>\n<p>\u0110\u1ec3 c\u00f3 th\u1ec3 g\u1ed9p d\u1eef li\u1ec7u, ch\u00fang ta c\u00f3 th\u1ec3 th\u1ea5y:<\/p>\n<ul>\n<li>\u0110i\u1ec1u ki\u1ec7n g\u1ed9p ch\u00ednh l\u00e0 2 ph\u1ea7n: T\u00ean s\u1ea3n ph\u1ea9m (theo c\u1ed9t F) v\u00e0 t\u00ean nh\u00e2n vi\u00ean (d\u00f2ng 2)<\/li>\n<li>K\u1ebft qu\u1ea3 ch\u00ednh l\u00e0 ph\u1ea7n k\u1ebft qu\u1ea3 t\u01b0\u01a1ng\u00a0\u1ee9ng theo t\u00ean s\u1ea3n ph\u1ea9m v\u00e0 t\u00ean nh\u00e2n vi\u00ean c\u1ee7a c\u00e1c b\u1ea3ng nh\u00f3m.<\/li>\n<\/ul>\n<p>Do\u00a0\u0111\u00f3 ch\u00fang ta c\u00f3 th\u1ec3 x\u00e2y d\u1ef1ng c\u00f4ng th\u1ee9c t\u00ednh t\u1ed5ng trong b\u1ea3ng 1 v\u00e0 c\u1ed9ng v\u1edbi b\u1ea3ng 2 (v\u00ec d\u1eef li\u1ec7u c\u00e1c b\u1ea3ng kh\u00f4ng tr\u00f9ng nhau\u00a0\u0111\u1ed3ng th\u1eddi c\u1ea3 2\u00a0\u0111i\u1ec1u ki\u1ec7n)<\/p>\n<p><strong>B\u01b0\u1edbc 3: x\u00e2y d\u1ef1ng c\u00f4ng th\u1ee9c t\u00ednh<\/strong><\/p>\n<p>C\u00f4ng th\u1ee9c t\u00ednh k\u1ebft qu\u1ea3 theo b\u1ea3ng nh\u00f3m 1:<\/p>\n<p style=\"padding-left: 30px\">=SUMPRODUCT((B1_NhanVien=G$2)*(B1_SanPham=$F3)*B1_KetQua)<\/p>\n<ul>\n<li>X\u00e9t t\u1eebng gi\u00e1 tr\u1ecb t\u00ean s\u1ea3n ph\u1ea9m (t\u1eeb F3) trong v\u00f9ng b\u1ea3ng nh\u00f3m 1, c\u1ed9t s\u1ea3n ph\u1ea9m<\/li>\n<li>X\u00e9t t\u1eebng gi\u00e1 tr\u1ecb t\u00ean nh\u00e2n vi\u00ean (t\u1eeb G2) trong v\u00f9ng b\u1ea3ng nh\u00f3m 1, d\u00f2ng nh\u00e2n vi\u00ean<\/li>\n<li>K\u1ebft qu\u1ea3 thu\u00a0\u0111\u01b0\u1ee3c s\u1ebd l\u1ea5y\u00a0\u1edf v\u1ecb tr\u00ed t\u01b0\u01a1ng\u00a0\u1ee9ng trong b\u1ea3ng nh\u00f3m 1, v\u00f9ng k\u1ebft qu\u1ea3<\/li>\n<\/ul>\n<p>C\u00f4ng th\u1ee9c t\u00ednh k\u1ebf qu\u1ea3 theo b\u1ea3ng nh\u00f3m 2:<\/p>\n<p style=\"padding-left: 30px\">=SUMPRODUCT((B2_NhanVien=G$2)*(B2_SanPham=$F3)*B2_KetQua)<\/p>\n<p>C\u00e1ch vi\u1ebft c\u00f4ng th\u1ee9c n\u00e0y r\u1ea5t\u00a0\u0111\u01a1n gi\u1ea3n, ch\u1ec9 c\u1ea7n copy l\u1ea1i c\u00f4ng th\u1ee9c c\u1ee7a b\u1ea3ng nh\u00f3m 1 r\u1ed3i s\u1eeda s\u1ed1 1 th\u00e0nh s\u1ed1 2 trong c\u00e1c v\u00f9ng t\u00ean.<\/p>\n<p>T\u1ea1i v\u1ecb tr\u00ed\u00a0\u00f4 G3 ch\u00fang ta c\u1ed9ng 2 c\u00f4ng th\u1ee9c l\u1ea1i v\u00e0 thu\u00a0\u0111\u01b0\u1ee3c k\u1ebft qu\u1ea3:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-23807\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/ket-qua-cong-thuc-sumproduct-gop-du-lieu.png\" width=\"1442\" height=\"352\" title=\"ket-qua-cong-thuc-sumproduct-gop-du-lieu\"><\/p>\n<p>C\u00e1c v\u1ecb tr\u00ed c\u00f2n l\u1ea1i trong b\u1ea3ng g\u1ed9p d\u1eef li\u1ec7u\u00a0 ch\u00fang ta s\u1ebd sao ch\u00e9p c\u00f4ng th\u1ee9c t\u1eeb\u00a0\u00f4 G3.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-23808\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/ket-qua-cong-thuc-sumproduct-gop-du-lieu-fill.png\" width=\"569\" height=\"177\" title=\"ket-qua-cong-thuc-sumproduct-gop-du-lieu-fill\"><\/p>\n<p>Nh\u01b0 v\u1eady l\u00e0 ch\u00fang ta\u00a0\u0111\u00e3 g\u1ed9p\u00a0\u0111\u01b0\u1ee3c k\u1ebft qu\u1ea3 t\u1eeb 2 b\u1ea3ng v\u00e0o chung 1 b\u1ea3ng\u00a0\u0111\u01b0\u1ee3c r\u1ed3i.<\/p>\n<p>C\u00e1c b\u1ea1n c\u00f3 th\u1ec3 tham kh\u1ea3o m\u1ed9t s\u1ed1 b\u00e0i vi\u1ebft kh\u00e1c v\u1ec1 t\u00e1c d\u1ee5ng c\u1ee7a h\u00e0m SUMPRODUCT:<\/p>\n<p style=\"padding-left: 30px\"><a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-cach-dung-ham-sumproduct-de-dem-theo-nhieu-dieu-kien.html\">H\u01b0\u1edbng d\u1eabn c\u00e1ch d\u00f9ng h\u00e0m SUMPRODUCT \u0111\u1ec3 \u0111\u1ebfm theo nhi\u1ec1u \u0111i\u1ec1u ki\u1ec7n<\/a><\/p>\n<p style=\"padding-left: 30px\"><a href=\"http:\/\/blog.ezworkapp.com\/47-%E2%97%8F-ham-sumproduct-va-su-khac-nhau-voi-sumifs-countifs-trong-excel.html\">H\u00e0m SUMPRODUCT v\u00e0 s\u1ef1 kh\u00e1c nhau so v\u1edbi SUMIFS, COUNTIFS trong excel<\/a><\/p>\n<\/div>\n<div class=\"uk-panel uk-margin uk-width-xlarge\">\n<p>Click \u0111\u0103ng k\u00fd h\u1ecdc t\u1ea1i:<\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/khoa-hoc\" target=\"_blank\" rel=\"noopener\">T\u1ea5t c\u1ea3 kho\u00e1 h\u1ecdc\u00a0<span>\ud83d\udcd6<\/span><\/a><\/p>\n<p><a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-kich-hoat-khoa-hoc-tai-hoc-excel-online.html\" target=\"_blank\" rel=\"noopener\">H\u01b0\u1edbng d\u1eabn k\u00edch ho\u1ea1t<\/a><\/p>\n<p>HKD H\u1ecdc Excel Online<br \/>S\u1ed1 \u0110K: 17A80048102<\/p>\n<\/div>\n<div class=\"uk-panel uk-text-small uk-margin uk-margin-remove-bottom uk-text-center\">\n<p>\u00a9  document.currentScript.insertAdjacentHTML(&#8216;afterend&#8217;, &#8221; + new Intl.DateTimeFormat(document.documentElement.lang, {year: &#8216;numeric&#8217;}).format() + &#8221;);  H\u1ecdc Excel Online. All rights reserved.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<div class=\"ad-inserterpro\">\n<a href=\"https:\/\/hocexcel.online\/course\/preview\/ex101-excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\"><br \/>\n<img decoding=\"async\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/ex101_92019-nho-497.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":5685,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-5684","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\/5684","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=5684"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/5684\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/5685"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=5684"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=5684"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=5684"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}