Hàm SUBTOTAL: tính tổng khi lọc dữ liệu trong Excel
Dùng SUBTOTAL 9 và 109 để tính tổng chỉ các dòng đang hiển thị khi lọc, đánh số thứ tự tự động khi lọc và dùng AGGREGATE để bỏ qua dòng ẩn lẫn ô lỗi.
Khi lọc bảng bán hàng chỉ còn chi nhánh Hà Nội, hàm SUM vẫn cộng cả những dòng đã bị ẩn — con số báo cáo bị sai. Giải pháp là =SUBTOTAL(9,F2:F500): hàm này chỉ tính các dòng đang hiển thị sau khi lọc. Bài viết hướng dẫn SUBTOTAL, sự khác nhau giữa mã 9 và 109, cách đánh số thứ tự không bị nhảy khi lọc, và hàm AGGREGATE mạnh hơn.
=SUBTOTAL(9;F2:F500).1. Cú pháp SUBTOTAL
=SUBTOTAL(mã_hàm, vùng_1, [vùng_2], ...)| Phép tính | Mã bỏ qua dòng bị lọc | Mã bỏ qua cả dòng ẩn thủ công |
|---|---|---|
| AVERAGE (trung bình) | 1 | 101 |
| COUNT (đếm ô số) | 2 | 102 |
| COUNTA (đếm ô không trống) | 3 | 103 |
| MAX | 4 | 104 |
| MIN | 5 | 105 |
| SUM (tổng) | 9 | 109 |
Ngoài ra còn mã 6 (PRODUCT), 7–8 (độ lệch chuẩn), 10–11 (phương sai) và bản 1xx tương ứng.
2. SUBTOTAL 9 hay 109?
- Mã 9: bỏ qua dòng bị ẩn do lọc (Filter), nhưng vẫn cộng dòng bạn tự ẩn bằng chuột phải → Hide.
- Mã 109: bỏ qua mọi dòng không hiển thị, dù ẩn do lọc hay ẩn tay.
Với báo cáo, nên dùng 109 để “thấy gì cộng nấy”. Lưu ý SUBTOTAL chỉ xét dòng ẩn, không xét cột ẩn.
Một ưu điểm ít người biết: SUBTOTAL bỏ qua các ô chứa SUBTOTAL khác trong vùng. Bạn có thể đặt tổng phụ từng nhóm và tổng cuối cùng bằng SUBTOTAL mà không bị cộng trùng.
3. Ví dụ thực tế
Tổng doanh thu theo bộ lọc
Bảng bán hàng có cột F là thành tiền. Đặt ô tổng phía trên tiêu đề (ví dụ F1) để không bị bộ lọc che:
=SUBTOTAL(109,F3:F500)Lọc theo nhân viên, sản phẩm hay tháng — ô F1 thay đổi theo ngay. Bật bộ lọc nhanh bằng Ctrl + Shift + L; xem thêm cách lọc dữ liệu trong Excel.
Đếm số dòng đang hiển thị
=SUBTOTAL(103,B3:B500)Đếm số nhân viên còn lại sau khi lọc theo phòng ban (cột B là họ tên, không có ô trống).
Điểm trung bình của lớp đang lọc
=SUBTOTAL(101,E3:E60)4. Đánh số thứ tự tự động khi lọc
Cột STT gõ tay 1, 2, 3… sẽ bị nhảy cóc sau khi lọc. Tại A3 gõ công thức rồi kéo xuống:
=SUBTOTAL(103,$B$3:B3)Vùng $B$3:B3 mở rộng dần theo từng dòng (một đầu cố định bằng dấu $, xem địa chỉ tuyệt đối F4), nên công thức đếm số dòng đang hiển thị từ đầu đến dòng hiện tại — lọc xong STT vẫn liền mạch 1, 2, 3.
=AGGREGATE(3,5,$B$3:B3) cho kết quả tương tự.5. AGGREGATE — bỏ qua cả dòng ẩn lẫn ô lỗi
=AGGREGATE(mã_hàm, tuỳ_chọn, vùng)Mã hàm giống SUBTOTAL (9 = SUM, 1 = AVERAGE, 4 = MAX…), thêm 14 = LARGE, 15 = SMALL… Tuỳ chọn hay dùng:
| Tuỳ chọn | Bỏ qua |
|---|---|
| 5 | Dòng ẩn |
| 6 | Giá trị lỗi (#N/A, #DIV/0!…) |
| 7 | Cả dòng ẩn và giá trị lỗi |
Ví dụ cột G có vài ô #N/A do tra cứu không thấy, SUM và SUBTOTAL đều báo lỗi, còn =AGGREGATE(9,7,G3:G500) vẫn cộng được phần hợp lệ của các dòng đang hiển thị.
6. Trên Google Sheets
Google Sheets có SUBTOTAL với cùng bảng mã 1–11 và 101–111, hoạt động với bộ lọc (Filter) và chế độ xem lọc (Filter view). Hàm AGGREGATE là hàm của Excel; trên Sheets, muốn bỏ qua ô lỗi có thể dùng =SUMIF(G3:G500,"<>#N/A") hoặc bọc nguồn bằng IFERROR. Muốn tính tổng theo điều kiện cố định (không phụ thuộc bộ lọc), dùng SUMIFS.