[Excel tips] Cách sử dụng hàm SUBTOTAL

Hàm SUBTOTAL trong Excel là hàm tính toán cho một nhóm con (tính tổng phụ) trong danh sách hoặc cơ sở dữ liệu. Các hàm Excel khác được thiết kế chỉ để thực hiện 1 chức năng cụ thể, nhưng hàm SUBTOTAL lại rất linh hoạt – có thể tính toán hoặc làm phép logic như đếm số ô, tính trung bình, tìm giá trị lớn nhất/nhỏ nhất…

Công thức hàm:

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

Với các đối số sẽ là bắt buộc bao gồm:

Function_num: số từ 1-11 hoặc 101-111 được sử dụng cho tổng phụ
1-11: bao gồm hàng ẩn thủ công
101-111: loại trừ giá trị ra khỏi bảng tính

Function_num		Chức năng	Mô tả
1	101	AVERAGE	Tính trung bình các con số
2	102	COUNT	Đếm số ô chứa giá trị số
3	103	COUNTA	Đếm số ô không trống
4	104	MAX	Tìm giá trị lớn nhất
5	105	MIN	Tìm giá trị nhỏ nhất
6	106	PRODUCT	Tính kết quả của các ô
7	107	STDEV	Tính độ lệch chuẩn mẫu dựa trên mẫu
8	108	STDEVP	Tính độ lệch chuẩn dựa trên toàn bộ số
9	109	SUM	Cộng các số
10	110	VAR	Ước tính độ dao động dựa trên mẫu
11	111	VARP	Ước tính độ dao động dựa trên toàn bộ số

Ref1: phạm vi hoặc tham chiếu mà bạn cần đặt trước tiên muốn tính tổng phụ
Ref2: phạm vi hoặc chuỗi tham chiếu từ 2 trở đi mà bạn muốn tính tổng phụ cho nó

Ví dụ minh họa cụ thể:

SUBTOTAL 9 VS. SUBTOTAL 109

Như bạn đã biết, Excel SUBTOTAL chấp nhận 2 bộ số xác định chức năng 1-11 và 101-111. Cả 2 bộ số đều bỏ qua các hàng đã được lọc ra, nhưng 1-11 bao gồm các hàng được ẩn thủ công còn 101-111 thì loại trừ. Để hiểu rõ hơn về điểm khác biệt này, xem ví dụ sau:

Với tổng các hàng đã được chọn, bạn có thể dùng cả SUBTOTAL 9 và SUBTOTAL 109 như hình dưới:

Nhưng nếu có các hàng không liên quan đã ẩn thủ công bằng cách dùng lệnh Hide Rows trong Home tab > Cells group > Format > Hide & Unhide, và nhấn chuột phải vào các hàng, sau đó Hide, mà giờ bạn chỉ cần tổng giá trị trong các hàng nhìn thấy, lựa chọn SUBTOTAL 109:

Các số chức năng khác cũng làm theo cách tương tự. Ví dụ, để đếm ô không trống đã chọn, dùng SUBTOTAL 3 hoặc SUBTOTAL 103. Nhưng SUBTOTAL 103 có thể đếm các ô không trống nhìn thấy chính xác nếu có bất kì hàng ẩn nào trong dãy.

Lưu ý: Hàm Excel SUBTOTAL với số chức năng 101-111 bỏ qua giá trị trong hàng bị ẩn, không phải cột bị ẩn. Ví dụ, nếu bạn dùng công thức như SUBTOTAL(109, A1:E1) để tính tổng các số trong hàng ngang, cột bị ẩn sẽ không ảnh hưởng đến tổng phụ.

Một số chú ý khi sử dụng hàm Subtotal

Nếu trường hợp tổng phụ bất kì nào xuất hiện trong Ref1 hoặc Ref2 thì sẽ bị loại trừ để tránh việc đếm 2 lần.
Hàm subtotal sẽ bỏ qua tất cả các dãy số trong bộ lọc của bạn mặc dù bạn có sử dụng giá trị function_num nào đi nữa.
Hàm subtotal chỉ được sử dụng cho các cột dữ liệu hoặc phạm vi dọc, nó không được dùng cho dữ liệu ngang.
Bất kì tham chiếu 3-D nào xuất hiện, hàm Subtotal sẽ có giá trị là lỗi #VALUE

Ứng dụng thường gặp:

Tính giá trị trong các hàng được chọn

Vì hàm Excel SUBTOTAL bỏ qua các hàng đã được lọc ra, bạn có thể sử dụng nó để tính tổng dữ liệu 1 cách linh hoạt, các giá trị trong tổng phụ được tự động tính lại theo bộ lọc.

Ví dụ, nếu chúng ta lọc bảng doanh số bán hàng chỉ của vùng Miền Đông, công thức SUBTOTAL sẽ tự động điều chỉnh để bỏ qua tất cả vùng khác khi tính tổng.

Lưu ý:

Vì các 2 bộ số xác định chức năng (1-11 và 101-111) đều bỏ qua các ô đã được lọc ra, bạn có thể dùng công thức SUBTOTAL 9 hoặc SUBTOTAL 109.

Tính các ô nhìn thấy

Công thức SUBTOTAL với số xác định chức năng 101-111 bỏ qua các ô đã ẩn, đã lọc ra và ẩn thủ công. Vì thế, khi bạn sử dụng chức năng Excel’s Hide để ẩn dữ liệu không liên quan, dùng số chức năng 101-111 để loại bỏ các giá trị từ những hàng ẩn khỏi tổng phụ.

Bỏ qua giá trị trong công thức SUBTOTAL lồng ghép

Nếu dãy ô trong công thức SUBTOTAL có chứa công thức SUBTOTAL khác, công thức SUBTOTAL được lồng vào sẽ bị bỏ qua. Vậy nên con số trong bảng sẽ không phải tính 2 lần.

Trong hình dưới, công thức tính trung bình chính SUBTOTAL(1, C2:C10) bỏ qua kết quả của công thức SUBTOTAL trong ô C3 và C10.