Hàm SUBTOTAL trong Excel: tính tổng, đếm, trung bình đúng khi có bộ lọc dữ liệu

Vì sao dùng SUM sẽ cho kết quả sai khi đã lọc dữ liệu?

Đây là tình huống rất nhiều người gặp: bạn có một bảng dữ liệu vài trăm dòng, dùng Filter để chỉ xem đơn hàng của một khu vực hoặc một tháng, rồi nhìn xuống ô tính tổng bằng công thức =SUM(...) — nhưng con số đó không hề đổi, vẫn là tổng của toàn bộ dữ liệu gốc chứ không phải tổng của các dòng đang hiển thị.

Lý do: Filter chỉ ẩn các dòng không thỏa điều kiện, chứ không xóa chúng. Hàm SUM vẫn cộng tất cả các ô trong vùng tham chiếu, kể cả những dòng đang bị ẩn. Nếu bạn báo cáo con số này cho sếp hoặc khách hàng trong khi tưởng đó là tổng đã lọc, kết quả sẽ sai lệch hoàn toàn.

Hàm SUBTOTAL được sinh ra để giải quyết đúng vấn đề này: nó chỉ tính toán trên các dòng đang hiển thị, tự động bỏ qua các dòng bị ẩn bởi Filter.

Dữ liệu ví dụ dùng xuyên suốt bài — bảng đơn hàng đơn giản:

	A	B	C
1	Khu vực	Sản phẩm	Doanh thu
2	Vinh	Bàn ghế	12,000,000
3	Hà Nội	Tủ gỗ	8,500,000
4	Vinh	Tủ gỗ	15,000,000
5	Hà Nội	Bàn ghế	9,000,000
6	Vinh	Bàn ghế	6,000,000
7	Hà Nội	Tủ gỗ	7,500,000

Cú pháp hàm SUBTOTAL

=SUBTOTAL(function_num, ref1, [ref2], ...)

Trong đó:

• function_num: một con số từ 1–11 hoặc 101–111, quy định phép tính cần thực hiện (tổng, đếm, trung bình...).
• ref1, ref2...: vùng dữ liệu cần tính toán, có thể tham chiếu nhiều vùng cùng lúc.

Khác với các hàm quen thuộc như SUM hay AVERAGE, SUBTOTAL không cố định một phép tính — bạn chọn phép tính bằng cách đổi function_num.

Bảng các function_num thường dùng

function_num (tính cả dòng ẩn thủ công)	function_num (bỏ qua cả dòng ẩn thủ công)	Ý nghĩa
1	101	AVERAGE — trung bình cộng
2	102	COUNT — đếm ô chứa số
3	103	COUNTA — đếm ô không rỗng
4	104	MAX — giá trị lớn nhất
5	105	MIN — giá trị nhỏ nhất
9	109	SUM — tính tổng

Trong thực tế, 9 (SUM) và 109 là hai giá trị dùng nhiều nhất.

Ví dụ 1: Tính tổng doanh thu các dòng đang hiển thị

Giả sử bạn bật Filter ở hàng tiêu đề (Data → Filter), sau đó lọc cột Khu vực chỉ để lại "Vinh". Lúc này các dòng 3, 5, 7 (Hà Nội) bị ẩn đi.

Tại ô tính tổng, gõ:

=SUBTOTAL(9, C2:C7)

Excel chỉ cộng các dòng đang hiển thị (2, 4, 6 — đều là Vinh): 12,000,000 + 15,000,000 + 6,000,000 = 33,000,000, đúng bằng tổng của phần dữ liệu bạn đang xem, dù công thức vẫn tham chiếu tới toàn bộ vùng C2:C7.

Nếu dùng =SUM(C2:C7) cho cùng vùng này, kết quả vẫn là tổng của cả 6 dòng (58,000,000) — sai với những gì đang hiển thị trên màn hình.

Ví dụ 2: Đếm số đơn hàng đang hiển thị

Muốn biết sau khi lọc còn bao nhiêu đơn hàng, dùng COUNTA (đếm ô không rỗng) trên cột Sản phẩm:

=SUBTOTAL(3, B2:B7)

Với ví dụ lọc "Vinh" ở trên, kết quả trả về 3 — đúng bằng số dòng đang hiển thị.

Phân biệt nhóm 1–11 và nhóm 101–111

Cả hai nhóm đều tự động bỏ qua các dòng bị ẩn do Filter. Điểm khác nhau duy nhất nằm ở cách xử lý dòng bị ẩn thủ công (chuột phải → Hide Rows, không qua Filter):

• 1–11: vẫn tính cả những dòng bị ẩn thủ công (chỉ bỏ qua dòng bị ẩn do Filter).
• 101–111: bỏ qua luôn cả dòng bị ẩn thủ công lẫn dòng bị ẩn do Filter.

Nếu bảng của bạn chỉ dùng Filter (không tự ẩn dòng bằng tay), dùng 9 hay 109 cho ra kết quả giống hệt nhau. Chỉ khi có dòng bị ẩn thủ công thì hai nhóm này mới cho kết quả khác nhau — khi đó nên ưu tiên 109 để chắc chắn chỉ tính đúng phần dữ liệu đang thật sự nhìn thấy.

Dùng SUBTOTAL tự động qua Total Row của Excel Table

Nếu dữ liệu được chuyển thành Table (chọn vùng dữ liệu → Ctrl + T), bạn không cần tự gõ công thức SUBTOTAL:

1. Bấm vào bất kỳ ô nào trong Table.
2. Vào tab Table Design → tick chọn Total Row.
3. Excel tự thêm một dòng tổng ở cuối bảng. Bấm vào ô tổng ở cột cần tính, một danh sách xổ xuống hiện ra (Sum, Average, Count, Max, Min...).
4. Chọn phép tính mong muốn — Excel tự chèn đúng công thức SUBTOTAL với function_num phù hợp (nhóm 109) phía sau.

Cách này tiện vì bạn không cần nhớ số hiệu function_num, đồng thời dòng tổng luôn nằm cố định ở cuối Table dù bạn lọc hay sắp xếp dữ liệu.

Lỗi thường gặp

Vẫn ra kết quả sai như SUM dù đã dùng SUBTOTAL Kiểm tra lại xem dữ liệu có thật sự đang được lọc (Filter) không, hay chỉ đang ẩn dòng thủ công trong khi bạn dùng function_num nhóm 1–11 (nhóm này vẫn tính dòng ẩn thủ công). Nếu vậy, đổi sang nhóm 101–111.

SUBTOTAL lồng nhau bị tính hai lần Nếu trong vùng tham chiếu của một công thức SUBTOTAL có chứa một công thức SUBTOTAL khác, Excel tự động bỏ qua công thức SUBTOTAL bên trong để tránh cộng trùng — đây là hành vi mặc định, không phải lỗi, nhưng cần biết để không hoang mang khi thấy kết quả "thiếu".

Nhầm SUBTOTAL với hàm AGGREGATE AGGREGATE là hàm mới hơn, làm được nhiều việc hơn SUBTOTAL (bỏ qua cả lỗi #DIV/0!, #N/A...), nhưng cú pháp phức tạp hơn. Với nhu cầu tính toán thông thường khi có Filter, SUBTOTAL đã đủ dùng và dễ nhớ hơn.

Copy công thức SUBTOTAL sang ô khác nhưng vùng tham chiếu bị lệch Giống các hàm khác, nếu kéo công thức sang cột bên cạnh mà không cố định vùng bằng dấu $, vùng tham chiếu sẽ dịch chuyển theo. Dùng phím F4 để khóa vùng nếu cần copy công thức mà giữ nguyên tham chiếu.

Khi nào nên dùng SUBTOTAL trong công việc?

• Báo cáo doanh thu, chi phí theo từng khu vực/phòng ban khi thường xuyên lọc qua lại giữa các nhóm dữ liệu
• Bảng chấm công, bảng theo dõi công việc cần đếm số dòng đang lọc ra
• Các Table có dòng tổng cần tự cập nhật đúng theo phần dữ liệu đang xem, không phải toàn bộ dữ liệu gốc
• Thay thế SUM ở bất kỳ bảng dữ liệu nào có bật Filter, để tránh báo cáo sai số

Học Excel thực chiến tại TP Vinh

SUBTOTAL là một trong những hàm giúp báo cáo Excel luôn chính xác dù dữ liệu thay đổi liên tục qua các lần lọc. Nếu bạn muốn học Excel bài bản từ các hàm cơ bản đến xử lý dữ liệu, báo cáo thực tế trong công việc, tham khảo khóa học Excel tại Điền Nguyễn AZ.

📞 Hotline / Zalo: 0362 465 361

👉 Xem chi tiết: Khóa học Excel thực tế tại TP Vinh