{"id":10670,"date":"2025-01-14T22:56:06","date_gmt":"2025-01-14T22:56:06","guid":{"rendered":"https:\/\/blog.ezworkapp.com\/index.php\/2025\/01\/14\/danh-so-thu-tu-ket-qua-loc-autofilter-voi-ham-subtotal\/"},"modified":"2025-01-14T22:56:06","modified_gmt":"2025-01-14T22:56:06","slug":"danh-so-thu-tu-ket-qua-loc-autofilter-voi-ham-subtotal","status":"publish","type":"post","link":"https:\/\/blog.ezworkapp.com\/index.php\/2025\/01\/14\/danh-so-thu-tu-ket-qua-loc-autofilter-voi-ham-subtotal\/","title":{"rendered":"\u0110\u00e1nh s\u1ed1 th\u1ee9 t\u1ef1 k\u1ebft qu\u1ea3 l\u1ecdc Autofilter v\u1edbi h\u00e0m Subtotal"},"content":{"rendered":"<div class=\"uk-panel uk-margin\" id=\"template-nJy84WMn#1\">\n<p>Th\u00f4ng th\u01b0\u1eddng trong khi l\u00e0m vi\u1ec7c tr\u00ean Excel ch\u00fang ta th\u01b0\u1eddng s\u1eed d\u1ee5ng c\u1ed9t S\u1ed1 th\u1ee9 t\u1ef1\u00a0\u0111\u1ec3\u00a0\u0111\u1ebfm th\u1ee9 t\u1ef1 c\u00e1c d\u00f2ng n\u1ed9i dung trong b\u1ea3ng t\u00ednh. Tuy nhi\u00ean vi\u1ec7c\u00a0\u0111\u00e1nh s\u1ed1 th\u1ee9 t\u1ef1 theo c\u00e1ch th\u00f4ng th\u01b0\u1eddng s\u1ebd c\u00f3 h\u1ea1n ch\u1ebf khi ch\u00fang ta s\u1eed d\u1ee5ng t\u00ednh n\u0103ng l\u1ecdc d\u1eef li\u1ec7u b\u1eb1ng Autofilter.\u00a0\u0110\u1ec3 minh h\u1ecda r\u00f5 h\u01a1n\u00a0\u0111i\u1ec1u n\u00e0y ch\u00fang ta c\u00f9ng xem v\u00ed d\u1ee5 b\u00ean d\u01b0\u1edbi:<\/p>\n<p>Cho b\u1ea3ng d\u1eef li\u1ec7u nh\u01b0 sau, trong\u00a0\u0111\u00f3 c\u1ed9t s\u1ed1 th\u1ee9 t\u1ef1\u00a0\u0111\u01b0\u1ee3c\u00a0\u0111\u00e1nh s\u1ed1 tr\u1ef1c ti\u1ebfp:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-14503\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/heo-subtotal-autofilter-01.png\" width=\"543\" height=\"675\" title=\"heo-subtotal-autofilter-01\"><\/p>\n<p>Khi l\u1ecdc n\u1ed9i dung trong b\u1ea3ng d\u1eef li\u1ec7u tr\u00ean theo nh\u00e2n vi\u00ean Long (S\u1eed d\u1ee5ng AutoFilter v\u00e0 l\u1ecdc \u1edf c\u1ed9t D theo nh\u00e2n vi\u00ean Long)\u00a0th\u00ec k\u1ebft qu\u1ea3 nh\u01b0 sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-14504\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/heo-subtotal-autofilter-02.png\" width=\"559\" height=\"328\" title=\"heo-subtotal-autofilter-02\"><\/p>\n<p>Nh\u00ecn v\u00e0o c\u1ed9t STT ch\u00fang ta th\u1ea5y STT kh\u00f4ng thay\u00a0\u0111\u1ed5i khi ta th\u1ef1c\u00a0 hi\u1ec7n vi\u1ec7c\u00a0 l\u1ecdc. V\u00e0 v\u1edbi STT nh\u01b0 v\u1eady th\u00ec vi\u1ec7c theo d\u00f5i k\u1ebft qu\u1ea3 l\u1ecdc c\u1ee7a ch\u00fang ta s\u1ebd g\u1eb7p nhi\u1ec1u kh\u00f3 kh\u0103n, do c\u1ed9t STT kh\u00f4ng c\u00f2n th\u1ec3 hi\u1ec7n\u00a0\u0111\u00fang vai tr\u00f2 l\u00e0 th\u1ee9 t\u1ef1\u00a0c\u00e1c d\u00f2ng n\u1ed9i dung ph\u00e1t sinh.<\/p>\n<p>\u0110\u1ec3 c\u00f3 th\u1ec3 gi\u1eef\u00a0\u0111\u00fang b\u1ea3n ch\u1ea5t c\u1ee7a c\u1ed9t STT khi s\u1eed d\u1ee5ng AutoFilter, ch\u00fang ta s\u1ebd\u00a0\u00e1p d\u1ee5ng h\u00e0m SUBTOTAL trong c\u1ed9t s\u1ed1 th\u1ee9 t\u1ef1.<\/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<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\/2025\/01\/vba101_92019-nho-8.gif\" title=\"vba101_92019-nho-8\"><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\/2025\/01\/ex101_92019-nho-208.gif\" title=\"ex101_92019-nho-208\"><br \/>\n<\/a>\n<\/div>\n<\/div>\n<p><label for=\"ez-toc-cssicon-toggle-item-6786eb8590446\" 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-3\"><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/blog.hocexcel.online\/danh-thu-tu-ket-qua-loc-autofilter-voi-ham-subtotal.html#1_Dac_diem_cua_ham_SUBTOTAL\" title=\"1.\u00a0\u0110\u1eb7c\u00a0\u0111i\u1ec3m c\u1ee7a h\u00e0m SUBTOTAL\">1.\u00a0\u0110\u1eb7c\u00a0\u0111i\u1ec3m c\u1ee7a h\u00e0m SUBTOTAL<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/blog.hocexcel.online\/danh-thu-tu-ket-qua-loc-autofilter-voi-ham-subtotal.html#2_Cong_thuc_danh_so_thu_tu\" title=\"2. C\u00f4ng th\u1ee9c\u00a0\u0111\u00e1nh s\u1ed1 th\u1ee9 t\u1ef1\">2. C\u00f4ng th\u1ee9c\u00a0\u0111\u00e1nh s\u1ed1 th\u1ee9 t\u1ef1<\/a><\/li>\n<li class=\"ez-toc-page-1 ez-toc-heading-level-3\"><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/blog.hocexcel.online\/danh-thu-tu-ket-qua-loc-autofilter-voi-ham-subtotal.html#3_Danh_gia_ket_qua\" title=\"3.\u00a0\u0110\u00e1nh gi\u00e1 k\u1ebft qu\u1ea3\">3.\u00a0\u0110\u00e1nh gi\u00e1 k\u1ebft qu\u1ea3<\/a><\/li>\n<\/ul>\n<\/nav>\n<\/div>\n<h3><span class=\"ez-toc-section\" id=\"1_Dac_diem_cua_ham_SUBTOTAL\"><\/span><strong>1.\u00a0\u0110\u1eb7c\u00a0\u0111i\u1ec3m c\u1ee7a h\u00e0m SUBTOTAL<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><strong>H\u00e0m SUBTOTAL<\/strong> l\u00e0 h\u00e0m c\u00f3 th\u1ec3 s\u1eed d\u1ee5ng cho nhi\u1ec1u lo\u1ea1i c\u00f4ng th\u1ee9c: c\u1ed9ng, trung b\u00ecnh,\u00a0\u0111\u1ebfm\u2026 <strong>m\u00e0 ch\u1ec9 t\u00ednh v\u1edbi c\u00e1c gi\u00e1 tr\u1ecb kh\u00f4ng b\u1ecb\u00a0\u1ea9n<\/strong> (b\u1edfi vi\u1ec7c\u00a0 Hide Row, Hide Column, L\u1ecdc b\u1ecf b\u1edfi kh\u00f4ng ph\u00f9 h\u1ee3p k\u1ebft qu\u1ea3 trong Filter\u2026)<\/p>\n<p>Do\u00a0\u0111\u00f3 ch\u00fang ta c\u00f3 th\u1ec3 t\u1eadn d\u1ee5ng t\u00ednh n\u0103ng c\u1ee7a h\u00e0m Subtotal\u00a0\u0111\u1ec3 x\u00e2y d\u1ef1ng c\u00f4ng th\u1ee9c\u00a0\u0111\u00e1nh s\u1ed1 th\u1ee9 t\u1ef1.<\/p>\n<p>C\u1ea5u tr\u00fac h\u00e0m SUBTOTAL(Function_num, ref1, [ref2]\u2026)<\/p>\n<p>Trong\u00a0\u00a0\u0111\u00f3<\/p>\n<ul>\n<li>Function_num l\u00e0 s\u1ed1 th\u1ec3 hi\u1ec7n thu\u1eadt to\u00e1n (t\u00ednh t\u1ed5ng,\u00a0\u0111\u1ebfm, t\u00ednh trung\u00a0 b\u00ecnh\u2026) (b\u1eaft bu\u1ed9c)<\/li>\n<li>ref1 l\u00e0 v\u00f9ng tham chi\u1ebfu th\u1ee9 1 (b\u1eaft bu\u1ed9c)<\/li>\n<li>ref2 l\u00e0 v\u00f9ng tham chi\u1ebfu th\u1ee9 2 (n\u1ebfu c\u00f3, kh\u00f4ng b\u1eaft bu\u1ed9c) v\u00e0 c\u00f3 th\u1ec3 c\u00f3 nhi\u1ec1u v\u00f9ng tham chi\u1ebfu h\u01a1n n\u1eefa.<\/li>\n<\/ul>\n<h3><span class=\"ez-toc-section\" id=\"2_Cong_thuc_danh_so_thu_tu\"><\/span><strong>2. C\u00f4ng th\u1ee9c\u00a0\u0111\u00e1nh s\u1ed1 th\u1ee9 t\u1ef1<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p><strong>Nguy\u00ean t\u1eafc c\u1ee7a vi\u1ec7c\u00a0\u0111\u00e1nh s\u1ed1 th\u1ee9 t\u1ef1<\/strong> l\u00e0\u00a0<strong>\u0111\u1ebfm<\/strong> nh\u1eefng d\u00f2ng c\u00f3 n\u1ed9i dung. V\u1edbi m\u1ed7i d\u00f2ng c\u00f3 n\u1ed9i dung ph\u00e1t sinh th\u00ec s\u1ebd t\u00ednh l\u00e0 1\u00a0\u0111\u01a1n\u00a0 v\u1ecb, d\u00f2ng ti\u1ebfp theo c\u00f3 ch\u1ee9a n\u1ed9i dung th\u00ec s\u1ebd b\u1eb1ng s\u1ed1 th\u1ee9 t\u1ef1\u00a0\u1edf d\u00f2ng tr\u01b0\u1edbc c\u1ed9ng th\u00eam 1.<\/p>\n<p>Khi nh\u1eafc t\u1edbi h\u00e0m\u00a0\u0111\u1ebfm trong\u00a0excel, ch\u00fang ta ngh\u0129 t\u1edbi COUNT<\/p>\n<p>Trong h\u00e0m Subtotal c\u0169ng h\u1ed7 tr\u1ee3 vi\u1ec7c\u00a0\u0111\u1ebfm: s\u1eed d\u1ee5ng <strong>function_num l\u00e0 3<\/strong><\/p>\n<p>V\u00f9ng tham chi\u1ebfu c\u00f3 th\u1ec3 ch\u1ecdn l\u00e0 v\u00f9ng lu\u00f4n ch\u1eafc ch\u1eafn c\u00f3 n\u1ed9i dung (\u0111\u1ec3 tr\u00e1nh vi\u1ec7c tr\u00ean c\u00f9ng 1 d\u00f2ng m\u00e0\u00a0\u0111\u1ebfm\u00a0\u1edf v\u00f9ng kh\u00f4ng c\u00f3 n\u1ed9i dung th\u00ec d\u00f2ng\u00a0\u0111\u00f3 s\u1ebd kh\u00f4ng\u00a0\u0111\u01b0\u1ee3c t\u00ednh). V\u00ed d\u1ee5\u00a0\u1edf\u00a0\u0111\u00e2y ch\u00fang ta ch\u1ecdn v\u1edbi c\u1ed9t D \u2013 c\u1ed9t Nh\u00e2n vi\u00ean.\u00a0\u0110\u1ebfm b\u1eaft\u00a0\u0111\u1ea7u t\u1eeb d\u00f2ng th\u1ee9 2 n\u00ean s\u1ebd b\u1eaft\u00a0\u0111\u1ea7u v\u1edbi\u00a0\u00f4 D2. Khi\u00a0\u0111\u1ebfm c\u00e1c d\u00f2ng ti\u1ebfp theo th\u00ec s\u1ebd\u00a0\u0111\u1ebfm c\u1ea3 nh\u1eefng\u00a0\u00f4 tr\u01b0\u1edbc\u00a0\u0111\u00f3:<\/p>\n<ul>\n<li>S\u1ed1 th\u1ee9 t\u1ef1 t\u1ea1i d\u00f2ng 2 = COUNT(D2:D2)<\/li>\n<li>S\u1ed1 th\u1ee9 t\u1ef1 t\u1ea1i d\u00f2ng 3 = COUNT(D2:D3)<\/li>\n<li>S\u1ed1 th\u1ee9 t\u1ef1 t\u1ea1i d\u00f2ng\u00a04 = COUNT(D2:D4)<\/li>\n<\/ul>\n<p>Do\u00a0\u0111\u00f3 D2 lu\u00f4n xu\u1ea5t hi\u1ec7n trong c\u00e1c c\u00f4ng th\u1ee9c =&gt; C\u1ed1\u00a0\u0111\u1ecbnh\u00a0\u0111i\u1ec3m D2<\/p>\n<p>Ch\u00fang ta c\u00f3 c\u00f4ng th\u1ee9c\u00a0\u0111\u1ebfm s\u1ed1 th\u1ee9 t\u1ef1 t\u1ea1i\u00a0\u00f4 A2 nh\u01b0 sau:<\/p>\n<blockquote>\n<p>A2=SUBTOTALL(3, $D$2:D2)<\/p>\n<\/blockquote>\n<p>=&gt; Copy (filldown) c\u00f4ng th\u1ee9c t\u1ea1i A2 xu\u1ed1ng c\u00e1c d\u00f2ng ti\u1ebfp theo\u00a0\u1edf c\u1ed9t A<\/p>\n<h3><span class=\"ez-toc-section\" id=\"3_Danh_gia_ket_qua\"><\/span><strong>3.\u00a0\u0110\u00e1nh gi\u00e1 k\u1ebft qu\u1ea3<\/strong><span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>K\u1ebft qu\u1ea3 sau khi s\u1eed d\u1ee5ng c\u00f4ng th\u1ee9c Subtotal\u00a0\u1edf c\u1ed9t STT v\u00e0 s\u1eed d\u1ee5ng Autofilter v\u1edbi Nh\u00e2n vi\u00ean Long nh\u01b0 sau:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-14505\" src=\"http:\/\/blog.ezworkapp.com\/wp-content\/uploads\/2025\/01\/heo-subtotal-autofilter-04.png\" width=\"552\" height=\"352\" title=\"heo-subtotal-autofilter-04\"><\/p>\n<p>Nh\u01b0 v\u1eady s\u1ed1 th\u1ee9 t\u1ef1\u00a0\u0111\u00e3\u00a0\u0111\u01b0\u1ee3c thay\u00a0\u0111\u1ed5i 1 c\u00e1ch t\u1ef1\u00a0\u0111\u1ed9ng d\u1ef1a theo k\u1ebft qu\u1ea3 l\u1ecdc.<\/p>\n<p>R\u1ea5t nhi\u1ec1u ki\u1ebfn th\u1ee9c ph\u1ea3i kh\u00f4ng n\u00e0o? To\u00e0n b\u1ed9 nh\u1eefng ki\u1ebfn th\u1ee9c n\u00e0y c\u00e1c b\u1ea1n \u0111\u1ec1u c\u00f3 th\u1ec3 h\u1ecdc \u0111\u01b0\u1ee3c trong kh\u00f3a h\u1ecdc\u00a0<a href=\"http:\/\/blog.ezworkapp.com\/khoa-hoc\/excel-tu-co-ban-den-chuyen-gia-danh-cho-nguoi-di-lam\">EX101 \u2013 Excel t\u1eeb c\u01a1 b\u1ea3n t\u1edbi chuy\u00ean gia<\/a> c\u1ee7a H\u1ecdc Excel Online. \u0110\u00e2y l\u00e0 kh\u00f3a h\u1ecdc gi\u00fap b\u1ea1n h\u1ec7 th\u1ed1ng ki\u1ebfn th\u1ee9c m\u1ed9t c\u00e1ch \u0111\u1ea7y \u0111\u1ee7, chi ti\u1ebft. H\u01a1n n\u1eefa kh\u00f4ng h\u1ec1 c\u00f3 gi\u1edbi h\u1ea1n v\u1ec1 th\u1eddi gian h\u1ecdc t\u1eadp n\u00ean b\u1ea1n c\u00f3 th\u1ec3 tho\u1ea3i m\u00e1i h\u1ecdc b\u1ea5t c\u1ee9 l\u00fac n\u00e0o, d\u1ec5 d\u00e0ng tra c\u1ee9u l\u1ea1i ki\u1ebfn th\u1ee9c khi c\u1ea7n. Hi\u1ec7n nay h\u1ec7 th\u1ed1ng \u0111ang c\u00f3 \u01b0u \u0111\u00e3i r\u1ea5t l\u1edbn cho b\u1ea1n khi \u0111\u0103ng k\u00fd tham gia kh\u00f3a h\u1ecdc\u00a0n\u00e0y.<\/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\/2025\/01\/vba101_92019-nho-8.gif\"><br \/>\n<\/a>\n<\/div>\n","protected":false},"author":1,"featured_media":10671,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"class_list":["post-10670","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\/10670","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=10670"}],"version-history":[{"count":0,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/posts\/10670\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media\/10671"}],"wp:attachment":[{"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/media?parent=10670"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/categories?post=10670"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.ezworkapp.com\/index.php\/wp-json\/wp\/v2\/tags?post=10670"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}