Bạn có một bảng doanh số, lọc ra vài dòng nhưng ô tổng vẫn cộng cả những dòng đã ẩn? Đây là lỗi rất nhiều người mới dùng Excel gặp phải, vì hàm SUM quen thuộc không biết dòng nào đang bị lọc. Hàm SUBTOTAL sinh ra để giải quyết đúng vấn đề này. Bài viết dưới đây TechCare sẽ hướng dẫn từng bước bằng ngôn ngữ dễ hiểu, kèm ví dụ thực tế, để bạn làm theo được ngay cả khi mới làm quen với Excel.
TechCare là cửa hàng bán và sửa chữa laptop tại Đà Nẵng, thường xuyên hỗ trợ khách hàng cài đặt, tối ưu máy và hướng dẫn dùng Office cho công việc hằng ngày. Nội dung được đội ngũ kỹ thuật viên soạn và kiểm tra lại các công thức trên Excel trước khi đăng.
Hàm SUBTOTAL trong Excel là gì?
Hàm SUBTOTAL là hàm tính tổng phụ của một vùng dữ liệu. Tùy mã bạn chọn, nó có thể cộng, tính trung bình, đếm ô, tìm số lớn nhất, số nhỏ nhất… Điểm đặc biệt: hàm này tự bỏ qua các dòng đã bị lọc (Filter) và có thể bỏ qua cả dòng bạn ẩn thủ công.
Hàm SUBTOTAL thường dùng để:
- Tính tổng doanh thu, chi phí, hàng tồn kho theo từng điều kiện lọc.
- Tìm doanh số cao nhất hoặc thấp nhất theo nhân viên, khu vực, ngày bán.
- Đếm số đơn hàng, số sản phẩm sau khi lọc.
Cú pháp hàm SUBTOTAL
=SUBTOTAL(function_num, ref1, [ref2], ...)
- function_num: con số cho Excel biết bạn muốn làm phép tính nào (tổng, trung bình, đếm…). Có hai nhóm: 1–11 và 101–111.
- ref1: vùng dữ liệu cần tính, ví dụ C2:C8.
- ref2, …: các vùng khác (không bắt buộc).
Bảng mã function_num dễ nhớ
| Mã (tính cả dòng ẩn thủ công) | Mã (bỏ qua dòng ẩn thủ công) | Phép tính |
| 1 | 101 | AVERAGE – trung bình |
| 2 | 102 | COUNT – đếm ô chứa số |
| 3 | 103 | COUNTA – đếm ô không trống |
| 4 | 104 | MAX – lớn nhất |
| 5 | 105 | MIN – nhỏ nhất |
| 6 | 106 | PRODUCT – tích |
| 7 | 107 | STDEV – độ lệch chuẩn mẫu |
| 8 | 108 | STDEVP – độ lệch chuẩn toàn bộ |
| 9 | 109 | SUM – tổng |
| 10 | 110 | VAR – phương sai mẫu |
| 11 | 111 | VARP – phương sai toàn bộ |
Điểm quan trọng cần nhớ:
- Cả hai nhóm mã đều bỏ qua dòng bị lọc bằng Filter.
- Khác biệt chỉ nằm ở dòng bạn ẩn thủ công (chuột phải > Hide): nhóm 1–11 vẫn tính, nhóm 101–111 thì bỏ qua.
- Người mới chỉ cần nhớ ba mã hay dùng nhất: 9 hoặc 109 (tổng), 1 hoặc 101 (trung bình), 3 hoặc 103 (đếm).
- Hàm chỉ xử lý dòng bị ẩn; ẩn cột không làm thay đổi kết quả.
Cách dùng hàm SUBTOTAL từng bước
Trong các ví dụ dưới, giả sử bạn có bảng doanh số 7 ngày: cột B là sản phẩm, cột C là doanh số (nghìn đồng) ở các ô C2 đến C8, gồm: 300, 500, 400, 50, 600, 250, 100. Dòng 5 là sản phẩm "Nón".
Bước 1: Tính tổng, trung bình, đếm khi chưa ẩn dòng nào
Bấm vào ô trống muốn hiện kết quả.
Gõ =SUBTOTAL(9,F2:F8) rồi nhấn Enter. Kết quả: 2.200 (nghìn đồng).
Muốn tính trung bình, gõ =SUBTOTAL(1,F2:F8). Kết quả: khoảng 314,3.
Muốn đếm số ngày có doanh thu, gõ =SUBTOTAL(3,F2:F8). Kết quả: 7.
Bước 2: Bỏ qua dòng bị ẩn
Giả sử bạn ẩn dòng 5 (Nón, 50 nghìn): chuột phải vào số dòng > Hide.
Gõ =SUBTOTAL(109,F2:F8) > Enter. Kết quả giảm còn 2.150.
Gõ =SUBTOTAL(101,F2:F8) > Enter. Trung bình mới là khoảng 358,3.
Gõ =SUBTOTAL(103,F2:F8) > Enter. Số ngày còn 6.
Nếu bạn dùng mã 9, 1, 3 trong trường hợp ẩn thủ công này, kết quả vẫn tính luôn dòng đã ẩn. Đây là lý do nhiều người thấy số "không khớp".
Bước 3: Dùng SUBTOTAL với bộ lọc (Filter)
Bôi đen bảng dữ liệu, vào thẻ Home > Sort & Filter > Filter.
Bấm mũi tên ở cột Sản phẩm, chỉ tích chọn một loại (ví dụ "Áo thun") rồi bấm OK.
Ô công thức =SUBTOTAL(9,F2:F8) sẽ tự cập nhật, chỉ cộng các dòng đang hiển thị.
Để ô tổng không bị ẩn theo bộ lọc, hãy đặt công thức bên ngoài vùng dữ liệu (ví dụ dưới bảng một dòng hoặc ở dòng phía trên tiêu đề).
Bước 4: Kết hợp SUBTOTAL với Table (Ctrl + T)
Bôi đen vùng dữ liệu, nhấn Ctrl + T > OK để biến thành bảng (Table).
Bấm mũi tên ở từng cột để đặt điều kiện lọc.
Nhập =SUBTOTAL(109,F2:F8) như trên. Khi thêm dòng mới vào bảng, công thức sẽ được cập nhật theo.
Mẹo: trong Table, bạn có thể bật Total Row (thẻ Table Design) để Excel tự chèn sẵn công thức SUBTOTAL ở dòng cuối.
Kết hợp SUBTOTAL với các hàm khác
Với IF – đánh số thứ tự tự bỏ qua dòng trống:
=IF(B2="","",SUBTOTAL(3,$B$2:B2))
Kéo công thức xuống các dòng bên dưới. Dòng nào cột B trống sẽ không được đánh số, và khi lọc dữ liệu, số thứ tự vẫn liền mạch.
Với SUMPRODUCT – tính thành tiền (số lượng × đơn giá) chỉ cho dòng đang hiển thị:
=SUMPRODUCT(SUBTOTAL(103,OFFSET(C2,ROW(C2:C8)-ROW(C2),0)),C2:C8*E2:E8)
Công thức này hơi dài, bạn chỉ cần chép lại và sửa địa chỉ ô cho đúng bảng của mình.
Với AGGREGATE – khi bảng có cả dòng ẩn và ô lỗi:
SUBTOTAL không bỏ qua ô lỗi (như #N/A, #DIV/0!). Khi đó hãy dùng =AGGREGATE(9,7,E2:E8). Trong đó 9 là phép tính tổng, 7 nghĩa là bỏ qua cả dòng ẩn và ô lỗi.
SUBTOTAL khác SUM như thế nào?
| Tiêu chí | SUBTOTAL | SUM |
| Số phép tính | 11 phép (chọn bằng mã) | Chỉ cộng |
| Bỏ qua dòng bị lọc | Có | Không |
| Bỏ qua dòng ẩn thủ công | Có (nhóm 101–111) | Không |
| Nên dùng khi | Bảng có lọc, cần tổng thay đổi theo bộ lọc | Bảng đơn giản, cộng toàn bộ |
Một lưu ý hay: nếu trong vùng tính có sẵn công thức SUBTOTAL khác, Excel sẽ tự bỏ qua để không cộng trùng. Nhờ vậy, SUBTOTAL rất hợp để làm báo cáo có nhiều tổng nhỏ và một tổng lớn.
Lỗi thường gặp khi dùng SUBTOTAL
- #VALUE!: nhập mã function_num sai (ngoài 1–11 hoặc 101–111), hoặc dùng tham chiếu 3D (nhiều trang tính cùng lúc). Cách sửa: kiểm tra lại mã và chỉ chọn vùng trong một trang tính.
- #DIV/0!: thường xảy ra khi tính trung bình hoặc độ lệch chuẩn mà vùng chọn không có ô số nào (hoặc toàn bộ đã bị lọc hết). Cách sửa: kiểm tra bộ lọc và định dạng dữ liệu, số lưu dạng chữ cần chuyển lại thành số.
- #NAME?: gõ sai tên hàm (ví dụ SUBTOTALL). Cách sửa: gõ lại đúng SUBTOTAL.
- Kết quả không đổi khi ẩn dòng: bạn đang dùng mã 1–11 để ẩn thủ công. Hãy đổi sang mã 101–111.
Kinh nghiệm từ TechCare
Khi hỗ trợ khách hàng làm bảng kế toán, bán hàng nhỏ, lỗi "tổng không khớp" thường do dùng nhầm mã 9 thay vì 109 sau khi ẩn dòng. Thói quen an toàn là luôn dùng nhóm mã 101–111: kết quả đúng cả khi lọc lẫn khi ẩn thủ công. Ngoài ra, hãy đặt ô tổng ở ngoài vùng dữ liệu và lưu file thường xuyên (Ctrl + S) để tránh mất công sức.
Khi nào nên đem máy đến kỹ thuật viên?
SUBTOTAL là công thức nên lỗi thường nằm ở cách nhập, không phải do máy. Tuy nhiên, bạn nên mang laptop đến TechCare nếu:
- Excel bị treo, đứng hình hoặc tự đóng khi mở file bảng tính lớn.
- Máy chạy rất chậm, nóng, quạt kêu to khi làm việc với Office.
- Không cài được hoặc không kích hoạt được Excel, hoặc file thường xuyên báo lỗi hỏng.
- Bạn cần máy mới hoặc nâng cấp RAM, SSD để xử lý bảng tính nặng mượt hơn.
Câu hỏi thường gặp
Cần hỗ trợ thêm? Liên hệ TechCare
Nếu bạn làm theo mà vẫn chưa ra kết quả, hoặc laptop gặp trục trặc khi dùng Excel, hãy mang máy đến TechCare hoặc liên hệ hotline 02363.663.333. Kỹ thuật viên sẽ kiểm tra, tư vấn và hướng dẫn trực tiếp, đơn giản và rõ ràng.
Nguồn tham khảo chính thức
- Microsoft Support – Hàm SUBTOTAL (support.microsoft.com)
- Microsoft Support – Hàm AGGREGATE (support.microsoft.com)
- Microsoft Support – Lọc dữ liệu trong phạm vi hoặc bảng (support.microsoft.com)
- Google Docs Editors Help – SUBTOTAL (support.google.com)
Bài viết được biên soạn bởi đội ngũ chuyên gia TechCare | Cập nhật: Tháng 10/2026
