Hàm SUBTOTAL Excel: Công Thức, Ví Dụ Và So Sánh Với SUM

Hàm SUBTOTAL trong Excel giải quyết trực tiếp vấn đề mà SUM truyền thống bó tay: tính tổng trên dữ liệu đã lọc. Microsoft hỗ trợ 22 chức năng thống kê từ AVERAGE đến VARP, trong đó mã 9 và 109 đều là SUM nhưng xử lý hàng ẩn thủ công theo hai cách khác nhau — đây là điểm then chốt quyết định kết quả báo cáo của bạn.

Số chức năng hỗ trợ: 22 (1-11 và 101-111) ·
Phạm vi áp dụng: Dữ liệu dọc, không hỗ trợ ngang ·
Ưu điểm chính: Tự động bỏ qua hàng ẩn khi lọc ·
Mã phổ biến: 9 (SUM) và 109 (SUM bỏ ẩn thủ công) ·
Nguồn chính thức: Microsoft Support

Hàm SUBTOTAL là gì?

Theo định nghĩa từ tài liệu hỗ trợ chính thức của Microsoft, hàm SUBTOTAL trả về tổng phụ trong danh sách hoặc cơ sở dữ liệu. Khác với hàm SUM thông thường, SUBTOTAL có khả năng tự động bỏ qua các giá trị bị ẩn do lọc — đây là tính năng then chốt khi bạn cần thống kê trên dữ liệu đã được Filter.

Định nghĩa cơ bản

Cú pháp hàm SUBTOTAL là =SUBTOTAL(function_num, ref1,,…), trong đó function_num xác định chức năng thống kê và ref là phạm vi dữ liệu. Bạn có thể sử dụng tối đa 254 đối số tham chiếu theo tài liệu từ Vietda. Ref1 là bắt buộc, các ref tiếp theo là tùy chọn.

Các chức năng chính

Bảng function_num từ Microsoft cho thấy có hai nhóm mã: 1-11 bao gồm hàng ẩn thủ công, còn 101-111 loại trừ chúng. Cụ thể, theo Microsoft Support:

Điểm mấu chốt: cả mã 9 và 109 đều thực hiện phép tính tổng, nhưng 9 bao gồm hàng ẩn thủ công (Ctrl+9), còn 109 loại trừ chúng. Cả hai đều bỏ qua hàng bị lọc ra theo thông tin từ Blog Hocexcel.

Subtotal khác gì với SUM?

Sự khác biệt cốt lõi nằm ở cách hai hàm xử lý dữ liệu đã được lọc hoặc ẩn. Hàm SUM tính tất cả giá trị trong phạm vi, bất kể hàng đó có đang hiển thị hay không. Ngược lại, SUBTOTAL(9, …) chỉ tính tổng các hàng nhìn thấy sau khi lọc — đây là lý do chính khiến SUBTOTAL được ưa chuộng trong báo cáo thống kê.

So sánh xử lý hàng ẩn

Theo video hướng dẫn từ kênh MrExcel trên YouTube, SUBTOTAL 9 dùng cho dữ liệu lọc và không loại trừ hàng ẩn thủ công. Trong khi đó, SUBTOTAL 109 dùng cho cả dữ liệu lọc lẫn hàng ẩn bằng Ctrl+9 — nó chính xác hơn khi bạn cần loại trừ mọi hàng không hiển thị.

Khi nào dùng từng hàm

Sử dụng SUM khi bạn cần tính tổng toàn bộ dữ liệu mà không quan tâm đến trạng thái hiển thị. Dùng SUBTOTAL(9) khi làm việc với AutoFilter và muốn kết quả tự cập nhật theo bộ lọc. Chọn SUBTOTAL(109) khi báo cáo cần loại trừ cả dữ liệu lọc lẫn hàng ẩn thủ công — đây là lựa chọn an toàn nhất theo khuyến nghị từ Dailymotion Excel Tips.

“1-11 bao gồm những hàng ẩn bằng cách thủ công, còn 101-111 loại trừ chúng ra.” — Microsoft Support

“SUBTOTAL 9 tính tổng hàng nhìn thấy sau lọc, nhưng bao gồm hàng ẩn thủ công.” — Dailymotion Excel Tips

Hàm subtotal 9 và 109 khác nhau như thế nào?

Đây là câu hỏi được thảo luận nhiều nhất trên các diễn đàn Excel, và sự khác biệt thực sự rất đơn giản nhưng có ý nghĩa quan trọng trong thực tế. Theo thảo luận trên diễn đàn Giaiphapexcel, SUBTOTAL 9 tính tổng cả dòng ẩn thủ công, trong khi 109 chỉ tính các dòng thỏa điều kiện lọc và hiển thị.

Mã 9: SUM bao gồm ẩn thủ công

SUBTOTAL(9, C2:C8) tính tổng phạm vi C2:C8 chỉ với dữ liệu lọc, nhưng nếu bạn ẩn một hàng thủ công bằng cách bôi đen hàng đó và nhấn Ctrl+9, hàng đó vẫn được tính vào tổng. Điều này có thể gây bất ngờ nếu bạn không nắm rõ cơ chế hoạt động.

Mã 109: SUM bỏ qua ẩn thủ công

SUBTOTAL(109, C2:C8) bỏ qua mọi hàng không hiển thị — dù hàng đó bị ẩn do lọc hay do thao tác thủ công. Vietda xác nhận rằng SUBTOTAL(109, A1:E1) không bị ảnh hưởng bởi cột ẩn — hàm chỉ loại trừ hàng ẩn, không phải cột ẩn.

Khi nào dùng hàm SUBTOTAL?

Hàm SUBTOTAL phát huy sức mạnh trong các tình huống cụ thể mà SUM truyền thống không thể xử lý tốt. Theo Blog Hocexcel, đây là công cụ lý tưởng cho danh sách hoặc cơ sở dữ liệu cần thống kê linh hoạt.

Với dữ liệu lọc

Khi bạn sử dụng AutoFilter hoặc bộ lọc nâng cao trên bảng dữ liệu, SUBTOTAL(9, D3:D22) sẽ tự động cập nhật tổng theo kết quả lọc — bạn không cần thay đổi công thức khi thay đổi bộ lọc. Vietda minh họa ví dụ này với dữ liệu thực tế về quản lý bán hàng.

Thống kê danh sách

SUBTOTAL hỗ trợ tính đánh số thứ tự trong dữ liệu lọc — khi bạn lọc bảng, số thứ tự tự động cập nhật và chỉ đánh số các hàng đang hiển thị. Ngoài ra, bạn có thể kết hợp IF với SUBTOTAL để tạo báo cáo động hiển thị Total, Average, Max, Min tùy theo nhu cầu theo hướng dẫn từ Vietda.

Công thức và ví dụ hàm SUBTOTAL?

Cú pháp đầy đủ của hàm SUBTOTAL theo Blog Hocexcel là =SUBTOTAL(function_num, ref1,,…). Dưới đây là hướng dẫn từng bước để bạn áp dụng ngay. Để hiểu rõ hơn về cách sử dụng hàm SUBTOTAL, bạn có thể tham khảo $Đánh giá G304 Logitech.

Cú pháp chi tiết

• function_num: Số từ 1-11 hoặc 101-111, xác định chức năng thống kê (xem bảng trên)
• ref1: Phạm vi hoặc tham chiếu đầu tiên — bắt buộc
• ref2,…: Các phạm vi bổ sung, tối đa 254 đối số — tùy chọn

Bảng sau tổng hợp đầy đủ 11 chức năng chính theo Microsoft Support:

Ví dụ thực tế

Dưới đây là các ví dụ cụ thể giúp bạn hình dung cách áp dụng SUBTOTAL:

• =SUBTOTAL(9, C2:C8) — Tính tổng C2:C8, bỏ qua hàng lọc nhưng bao gồm hàng ẩn thủ công
• =SUBTOTAL(109, C2:C8) — Tính tổng C2:C8, bỏ qua cả hàng lọc lẫn hàng ẩn thủ công
• =SUBTOTAL(2, D3:D22) — Đếm số ô có dữ liệu trong phạm vi đã lọc
• =SUBTOTAL(4, A1:A100) — Tìm giá trị lớn nhất, tự động cập nhật khi lọc

Lưu ý quan trọng: SUBTOTAL bỏ qua các SUBTOTAL lồng nhau để tránh tính trùng. Nếu bạn có SUBTOTAL trong phạm vi tham chiếu, nó sẽ không bị tính lại — đây là cơ chế thiết kế thông minh theo Blog Hocexcel.

So sánh chi tiết: SUBTOTAL 9 vs 109

Bảng so sánh trực tiếp dưới đây giúp bạn nắm rõ sự khác biệt giữa hai mã phổ biến nhất, dựa trên tài liệu từ Excel Tips Video và Microsoft Support:

Điểm mấu chốt: cả hai mã đều bỏ qua hàng lọc, nhưng 109 kiểm soát tốt hơn khi bạn kết hợp cả lọc lẫn ẩn thủ công. Nếu chỉ dùng AutoFilter đơn thuần, 9 và 109 cho kết quả giống nhau.

Điều này có nghĩa là kế toán viên và chuyên viên thống kê nên ưu tiên mã 109 để đảm bảo báo cáo luôn phản ánh đúng dữ liệu đang hiển thị, tránh sai số do quên ẩn hàng thủ công.

Related reading: Cách sử dụng hàm SUBTOTAL trong Excel · Hàm SUBTOTAL