Download.com.vn

Cách dùng SUBTOTAL trong Microsoft Excel

1 Đánh giá

SUBTOTAL là hàm tính tổng trong Excel được các chuyên gia khuyên dùng. Dưới đây là những điều cần biết về cách dùng SUBTOTAL trong Microsoft Excel.

Nhắc tới tính tổng trong Excel, hàm SUM có lẽ thường được nghĩ tới đầu tiên. Tuy nhiên, hàm SUM chỉ hiệu quả với bảng tính nhỏ. Nếu xử lý dữ liệu lớn, hãy dùng SUBTOTAL.

Tại sao hàm SUM lại không phải là lựa chọn tối ưu?

Hàm SUM cộng tất cả mọi giá trị, bất kể chúng có đang hiển thị hay không. Tuy nhiên, khi bạn lọc dữ liệu hoặc ẩn một số hàng, SUM vẫn sẽ cộng cả những con số bị ẩn đó vào. Kết quả là tổng số bị tăng lên và âm thầm làm sai lệch các báo cáo của bạn. Đây là lúc mà hầu hết mọi người đều gặp bế tắc.

Trong khi đó, SUBTOTAL có thể xử lý mọi tác vụ của các hàm cơ bản đó mà không gây ra những rắc rối phiền toái thường gặp.

SUBTOTAL trong Excel hoạt động như thế nào?

Hãy tưởng tượng bạn có một bảng dữ liệu bán hàng với hàng trăm hoặc hàng nghìn dòng. Có thể bạn chỉ muốn xem doanh số của "Sản phẩm A", nên bạn thực hiện lọc dữ liệu trên cột đó. Các dòng khác sẽ bị ẩn đi, nhưng hàm SUM lại không "hiểu" được điều này; nó vẫn tiếp tục cộng tổng tất cả các dòng, bao gồm cả những dòng đang bị ẩn. Điều tương tự cũng xảy ra với các hàm COUNT và AVERAGE.

Ngược lại, hàm SUBTOTAL có khả năng tự điều chỉnh linh hoạt. Khi sử dụng SUBTOTAL, nếu bạn lọc dữ liệu, tổng số sẽ tự động cập nhật để chỉ tính toán dựa trên các dòng đang hiển thị (các dòng đã lọc).

=SUBTOTAL(function_num, range)

Hàm này không chỉ giới hạn ở việc tính tổng. SUBTOTAL có thể chuyển đổi giữa các phép tính như tính tổng (sum), trung bình (average), đếm (count), giá trị nhỏ nhất (min), giá trị lớn nhất (max) và nhiều phép tính hữu ích khác, chỉ bằng cách thay đổi tham số đầu tiên trong công thức. Dưới đây là bảng liệt kê đầy đủ các hàm được hỗ trợ:

Số hàm	Hàm Excel
101	AVERAGE
102	COUNT
103	COUNTA
104	MAX
105	MIN
106	PRODUCT
107	STDEV
108	STDEVP
109	SUM
110	VAR
111	VARP

Khác với SUM, hàm SUBTOTAL đủ thông minh để không tự tính lặp lại chính nó. Nếu bạn có các giá trị tổng phụ cho từng danh mục và sau đó là tổng cộng cuối cùng ở phía dưới, SUBTOTAL sẽ tự động bỏ qua các giá trị tổng phụ kia. Trong khi đó, hàm SUM chỉ cộng dồn tất cả lại với nhau, khiến các con số bị thổi phồng lên. Nếu bạn từng thấy một con số tổng cao bất thường và thắc mắc tại sao, hãy kiểm tra xem có các hàm SUM lồng nhau hay không. SUBTOTAL không gặp phải vấn đề này.

Khác với hàm SUM, hàm SUBTOTAL đủ thông minh để không tự tính lặp lại chính nó. Nếu bạn có các giá trị tổng phụ cho từng danh mục và sau đó là tổng cộng cuối cùng ở phía dưới, SUBTOTAL sẽ tự động bỏ qua các giá trị tổng phụ kia. Trong khi đó, hàm SUM chỉ cộng dồn tất cả lại với nhau, khiến các con số bị thổi phồng lên. Nếu bạn từng thấy một con số tổng cao bất thường và thắc mắc tại sao, hãy kiểm tra xem có các hàm SUM lồng nhau hay không; SUBTOTAL không gặp phải vấn đề này.

Cách dùng SUBTOTAL trong Microsoft Excel

Bảng tính Excel

Điểm khiến SUBTOTAL trở thành lựa chọn hiển nhiên chính là tính linh hoạt của nó—bạn có nhiều tùy chọn hơn hẳn mà không hề làm tăng độ phức tạp so với hàm SUM. Hãy cùng xem xét ví dụ để thấy SUBTOTAL giúp mọi việc trở nên dễ dàng hơn như thế nào.

Để tính tổng các ô, công thức sẽ như sau:

=SUBTOTAL(109, C2:C15)

Công thức này tính tổng các ô từ C2 đến C15 nhưng bỏ qua các ô bị ẩn. Áp dụng công thức này cho hai hàng còn lại và giờ đã có các giá trị tổng. Chúng hoạt động tốt với toàn bộ bảng dữ liệu và vẫn hoạt động hiệu quả ngay cả khi lọc bảng. Giờ thì các con số đã trở nên hợp lý và chính xác.

Cách dùng hàm SUBTOTAL

Tuy nhiên, các con số khác vẫn chưa hợp lý. Giá trị COUNT vẫn hiển thị là 14, và các giá trị trung bình hiện đang được tính bằng cách chia tổng phụ cho con số đó; đây là lý do tại sao các giá trị trung bình lại thấp hơn mức lẽ ra phải có. Không sao cả. Hàm SUBTOTAL cũng hỗ trợ các hàm COUNT và COUNTA.

Vì vậy, đối với ô chứa giá trị đếm (B16), thay vì viết:

=COUNTA(A2:A15)

Hãy thay thế bằng:

=SUBTOTAL(103, A2:A15)

Kết quả từ hàm SUBTOTAL

Số 103 là mã hàm dành cho COUNTA. Giờ đây, các kết quả đếm tự động điều chỉnh khi bạn lọc dữ liệu, và các giá trị trung bình luôn hiển thị chính xác. Hàm SUBTOTAL cũng hỗ trợ cả MAX và MIN – một tính năng thực sự hữu ích.

Tổng cộng, SUBTOTAL hỗ trợ 11 hàm khác nhau; mặc dù nó không thể thay thế mọi công thức Excel, nhưng 11 hàm này là tất cả những gì bạn cần cho hầu hết các bảng tổng hợp dữ liệu.

Cập nhật: 24/09/2026