{"id":2279,"date":"2024-12-02T07:20:54","date_gmt":"2024-12-02T07:20:54","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/cach-tinh-tong-danh-sach-da-loc-trong-excel\/"},"modified":"2024-12-02T07:20:54","modified_gmt":"2024-12-02T07:20:54","slug":"cach-tinh-tong-danh-sach-da-loc-trong-excel","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2024\/12\/02\/cach-tinh-tong-danh-sach-da-loc-trong-excel\/","title":{"rendered":"C\u00e1ch t\u00ednh t\u1ed5ng danh s\u00e1ch \u0111\u00e3 l\u1ecdc trong Excel"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p><em>Trong b\u00e0i vi\u1ebft n\u00e0y, <a href=\"http:\/\/blog.ezworkapp.com\/\"><strong>H\u1ecdc Excel Online<\/strong> <\/a>s\u1ebd h\u01b0\u1edbng d\u1eabn b\u1ea1n c\u00e1c <a href=\"http:\/\/blog.ezworkapp.com\/cach-tinh-tong-danh-sach-da-loc-trong-excel.html\"><strong>c\u00e1ch t\u00ednh t\u1ed5ng danh s\u00e1ch \u0111\u00e3 l\u1ecdc<\/strong><\/a> c\u1ee7a m\u1ed9t c\u1ed9t trong Excel\u00a0\u0111\u1ec3 c\u1ed9ng t\u1ea5t c\u1ea3 ho\u1eb7c ch\u1ec9 nh\u1eefng \u00f4 \u0111\u00e3 ch\u1ecdn, d\u00f9ng h\u00e0m Subtotal ngay sau \u0111\u00e2y.<\/em><\/p>\n<figure id=\"attachment_33504\" aria-describedby=\"caption-attachment-33504\" style=\"width: 700px\" class=\"wp-caption aligncenter\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-33504\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2024\/12\/cach-tinh-tong-danh-sach-da-loc-trong-excel-1.png\" alt=\"C\u00e1ch t\u00ednh t\u1ed5ng danh s\u00e1ch \u0111\u00e3 l\u1ecdc trong Excel 1\" width=\"700\" height=\"400\" title=\"cach-tinh-tong-danh-sach-da-loc-trong-excel-1\"><figcaption id=\"caption-attachment-33504\" class=\"wp-caption-text\">C\u00e1ch t\u00ednh t\u1ed5ng danh s\u00e1ch \u0111\u00e3 l\u1ecdc trong Excel 1<\/figcaption><\/figure>\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-674d5fd56dcee\" 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-tinh-tong-danh-sach-da-loc-trong-excel.html#Cong_thuc_chung\" title=\"C\u00f4ng th\u1ee9c chung\">C\u00f4ng th\u1ee9c chung<\/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-tinh-tong-danh-sach-da-loc-trong-excel.html#Cach_su_dung\" title=\"C\u00e1ch s\u1eed d\u1ee5ng\">C\u00e1ch s\u1eed d\u1ee5ng<\/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-tinh-tong-danh-sach-da-loc-trong-excel.html#Giai_thich\" title=\"Gi\u1ea3i th\u00edch\">Gi\u1ea3i th\u00edch<\/a><\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h2><span class=\"ez-toc-section\" id=\"Cong_thuc_chung\"><\/span><b>C\u00f4ng th\u1ee9c chung<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><strong><i>= SUBTOTAL(9,range)<\/i><\/strong><\/p>\n<h2><span class=\"ez-toc-section\" id=\"Cach_su_dung\"><\/span><b>C\u00e1ch s\u1eed d\u1ee5ng<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">\u0110\u1ec3 t\u00ednh t\u1ed5ng c\u00e1c h\u00e0ng gi\u00e1 tr\u1ecb \u0111ang hi\u1ec3n th\u1ecb trong danh s\u00e1ch \u0111\u00e3 l\u1ecdc (t\u1ee9c l\u00e0 lo\u1ea1i tr\u1eeb c\u00e1c h\u00e0ng \u0111\u00e3 b\u1ecb \u201clo\u1ea1i ra\u201d), b\u1ea1n s\u1eed d\u1ee5ng h\u00e0m SUBTOTAL v\u1edbi h\u00e0m s\u1ed1 9 ho\u1eb7c 109. Trong v\u00ed d\u1ee5 d\u01b0\u1edbi \u0111\u00e2y, \u0111\u1ec3 t\u00ednh t\u1ed5ng c\u00e1c gi\u00e1 ti\u1ec1n \u0111\u00e3 \u0111\u01b0\u1ee3c l\u1ecdc, c\u00f4ng th\u1ee9c trong F2 l\u00e0:<\/span><\/p>\n<p><b>= SUBTOTAL(9,F5:F14)<\/b><\/p>\n<p><span style=\"font-weight: 400\">Th\u1eadt \u0111\u01a1n gi\u1ea3n ph\u1ea3i kh\u00f4ng n\u00e0o?<\/span><\/p>\n<p>Xem th\u00eam:<strong> H\u01b0\u1edbng d\u1eabn s\u1eed d\u1ee5ng h\u00e0m <a href=\"http:\/\/blog.ezworkapp.com\/huong-dan-cach-su-dung-ham-subtotal-de-tinh-tong-thay-cho-ham-sum.html\">subtotal trong Excel<\/a> \u0111\u1ec3 t\u00ednh t\u1ed5ng thay cho h\u00e0m Sum<\/strong><\/p>\n<h2><span class=\"ez-toc-section\" id=\"Giai_thich\"><\/span><b>Gi\u1ea3i th\u00edch<\/b><span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p><span style=\"font-weight: 400\">Nhi\u1ec1u b\u1ea1n s\u1ebd th\u1eafc m\u1eafc r\u1eb1ng t\u1ea1i sao kh\u00f4ng d\u00f9ng h\u00e0m SUM cho \u0111\u01a1n gi\u1ea3n? Th\u00ec nh\u01b0 \u0111\u00e3 nh\u1ea5n m\u1ea1nh \u1edf \u0111\u1ea7u b\u00e0i, \u0111i\u1ec3m \u0111\u1eb7c bi\u1ec7t c\u1ee7a h\u00e0m Subtotal l\u00e0 n\u00f3 t\u1ef1 \u0111\u1ed9ng b\u1ecf qua c\u00e1c h\u00e0ng b\u1ecb \u1ea9n trong danh s\u00e1ch ho\u1eb7c b\u1ea3ng \u0111\u00e3 l\u1ecdc, \u0111i\u1ec1u m\u00e0 h\u00e0m SUM kh\u00f4ng l\u00e0m \u0111\u01b0\u1ee3c. B\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng t\u00ednh n\u0103ng n\u00e0y khi c\u1ea7n \u0111\u1ebfm, t\u00ednh t\u1ed5ng, t\u00ednh trung b\u00ecnh, v.v. c\u00e1c gi\u00e1 tr\u1ecb trong danh s\u00e1ch \u0111\u00e3 l\u1ecdc. N\u00f3i c\u00e1ch kh\u00e1c, b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng SUBTOTAL \u0111\u1ec3 th\u1ef1c hi\u1ec7n c\u00e1c ph\u00e9p t\u00ednh m\u00e0 lo\u1ea1i tr\u1eeb c\u00e1c gi\u00e1 tr\u1ecb \u201c\u0111\u00e3 l\u1ecdc ra\u201d.<\/span><\/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-174.gif\" title=\"ex101_92019-nho-174\"><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-73.gif\" title=\"vba101_92019-nho-73\"><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-174.gif\" title=\"ex101_92019-nho-174\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p><span style=\"font-weight: 400\">Ngo\u00e0i ra h\u00e0m SUBTOTAL c\u00f2n cung c\u1ea5p nhi\u1ec1u t\u00f9y ch\u1ecdn v\u1edbi nhi\u1ec1u t\u00ednh n\u0103ng kh\u00e1c \u0111\u1ec3 t\u00ednh to\u00e1n, \u0111\u01b0\u1ee3c x\u00e1c \u0111\u1ecbnh b\u1edfi m\u1ed9t s\u1ed1 h\u00e0m \u0111\u01b0\u1ee3c cung c\u1ea5p l\u00e0m \u0111\u1ed1i s\u1ed1 \u0111\u1ea7u ti\u00ean(thay cho s\u1ed1 9).<\/span><\/p>\n<p><span style=\"font-weight: 400\">Nh\u01b0 \u0111\u00e3 n\u00f3i \u1edf tr\u00ean, \u0111\u1ec3 ch\u1ec9 t\u00ednh t\u1ed5ng c\u00e1c h\u00e0ng hi\u1ec3n th\u1ecb, b\u1ea1n c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng h\u00e0m SUBTOTAL v\u1edbi h\u00e0m s\u1ed1 9 ho\u1eb7c 109. Trong v\u00ed d\u1ee5 tr\u00ean m\u00ecnh \u0111\u00e3 s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c:<\/span><\/p>\n<p><strong>= SUBTOTAL(9,F5:F14)<\/strong><\/p>\n<p><span style=\"font-weight: 400\">K\u1ebft qu\u1ea3 tr\u1ea3 v\u1ec1 l\u00e0 $9,54, l\u00e0 t\u1ed5ng gi\u00e1 tr\u1ecb c\u1ee7a 7 h\u00e0ng \u0111ang hi\u1ec3n th\u1ecb(trong khi t\u1eeb F5 \u0111\u1ebfn F14 c\u00f3 t\u1ed5ng c\u1ed9ng 10 h\u00e0ng)<\/span><\/p>\n<p><span style=\"font-weight: 400\">N\u1ebfu b\u1ea1n \u0111ang \u1ea9n c\u00e1c h\u00e0ng theo c\u00e1ch th\u1ee7 c\u00f4ng (t\u1ee9c l\u00e0 nh\u1ea5p chu\u1ed9t ph\u1ea3i, ch\u1ecdn \u1ea8n), h\u00e3y s\u1eed d\u1ee5ng phi\u00ean b\u1ea3n n\u00e0y \u0111\u1ec3 thay th\u1ebf:<\/span><\/p>\n<p><b>= SUBTOTAL(109,F5:F14)<\/b><\/p>\n<p><span style=\"font-weight: 400\">Ngh\u0129a l\u00e0 s\u1eed d\u1ee5ng s\u1ed1 h\u00e0m 109 cho SUBTOTAL khi c\u00e1c h\u00e0ng b\u1ecb \u1ea9n m\u1ed9t c\u00e1ch th\u1ee7 c\u00f4ng.<\/span><\/p>\n<p><span style=\"font-weight: 400\">B\u1eb1ng c\u00e1ch thay \u0111\u1ed5i s\u1ed1 h\u00e0m, h\u00e0m SUBTOTAL c\u00f3 th\u1ec3 th\u1ef1c hi\u1ec7n nhi\u1ec1u ph\u00e9p t\u00ednh kh\u00e1c (v\u00ed d\u1ee5: COUNT, SUM, MAX, MIN, v.v.). B\u1ea1n c\u00f3 th\u1ec3 xem danh s\u00e1ch \u0111\u1ea7y \u0111\u1ee7 c\u00e1c s\u1ed1 ch\u1ee9c n\u0103ng tr\u00ean trang n\u00e0y nh\u00e9).<\/span><\/p>\n<p><strong><a href=\"http:\/\/blog.ezworkapp.com\/ham-subtotal-cac-ung-dung-cua-subtotal-trong-excel.html\">H\u01b0\u1edbng d\u1eabn c\u00e1ch s\u1eed d\u1ee5ng h\u00e0m Subtotal<\/a> <\/strong>\u0111\u00e3 kh\u00e9p l\u1ea1i b\u00e0i vi\u1ebft r\u1ed3i. H\u00e3y theo d\u00f5i v\u00e0 tham gia c\u00e1c <a href=\"http:\/\/blog.ezworkapp.com\/khoa-hoc-excel-tu-co-ban-toi-nang-cao-danh-cho-dan-van-phong-2010-20132016\"><strong>kh\u00f3a h\u1ecdc Excel<\/strong> <\/a>\u0111\u1ec3 n\u00e2ng cao, b\u1ed5 sung nh\u1eefng ki\u1ebfn th\u1ee9c h\u1eefu \u00edch. B\u1ea1n s\u1ebd c\u1ea3m th\u1ea5y c\u00f4ng vi\u1ec7c d\u1ec5 d\u00e0ng v\u00e0 x\u1eed l\u00fd ch\u00fang nhanh g\u1ecdn c\u00f9ng c\u00e1c m\u1eb9o c\u00f4ng th\u1ee9c Excel.<\/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<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-174.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":2280,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-2279","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\/2279","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=2279"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/2279\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/2280"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=2279"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=2279"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=2279"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}