cũng là một hàm được sử dụng trong Excel. Có thể nhiều người đã biết đến và đã sử dụng hàm này nhưng cũng có một số người chỉ mới nghe chứ chưa thực sự hiểu rõ. Vậy thì bài viết hôm nay sẽ giúp bạn hiểu rõ và biết cách ứng dụng hàm Subtotal.

Hàm Subtotal là hàm dùng để tính toán cho một nhóm của một danh sách hay một bảng dữ liệu dựa vào phép tính mà bạn lựa chọn trong đối số thứ nhất. Cú pháp của hàm này là:

SUBTOTAL(Function_num, Ref1, [Ref2],…)

Trong đó:

• Function_num: Các con số từ 1 đến 11 và 101 đến 111 quy định hàm nào sẽ được dùng để tính toán trong hàm Subtotal.
• Ref1, Ref2, …: 1 hoặc nhiều ô, hoặc dãy ô để tính tổng phụ, tối đa 254 tùy chọn. Giá trị của đối số thứ nhất

Chức năng

Mô tả

Tính toán bao gồm các giá trị ẩn Không tính toán các giá trị ẩn 1 101 AVERAGE Tính trung bình các 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 kết quả của các ô 8 108 STDEVP Tính kết quả của các ô 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ố

Những lưu ý khi sử dụng hàm Subtotal

• Hàm Subtotal được thiết kế ra dùng để tính toán cho các cột số liệu theo chiều dọc.
• Những đối số nằm trong Ref1, Ref2,… nếu đã có hàm Subtotal thì sẽ được bỏ qua để không bị tình trạng tính trùng 2 lần.
• Function_num từ 1 đến 11 thì hàm Subtotal sẽ tính toán bao gồm cả các giá trị ẩn trong tập số liệu do hàng chứa vùng đó bị ẩn. Nếu Function_num từ 101 đến 111 thì hàm Subtotal chỉ tính toán cho các giá trị không ẩn trong tập số liệu (không tính các giá trị ở hàng ẩn). Vì vậy khi sử dụng bạn cần xác định kỹ để cho ra kết quả chính xác.
• Đối với vùng dữ liệu bị ẩn do sử dụng chức năng Filter, khi đó hàm Subtotal sẽ bỏ qua.
• Bạn cũng không cần phải nhớ tất cả các hàm phép tính trong hàm Subtotal, bởi vì sau khi viết tên hàm Subtotal và bạn mở ngoặc thì có 1 bảng hàm để bạn có lựa chọn theo mục đích sử dụng của bạn.

Một số lỗi thường gặp khi sử dụng hàm Subtotal

Khi bạn đã hoàn thành hàm Subtotal nhưng khi kết quả trả về bị lỗi, thì sau đây là một số nguyên nhân thường gặp:

VALUE! Là do số xác định chức năng không nằm trong khoảng 1-11 hoặc 101-111 hay có tham chiếu (ref) là tham chiếu 3D.

DIV/0! Xảy ra khi 1 tổng cụ thể phải chia cho 0.

NAME? Do tên hàm Subtotal bị sai chính tả.

Ứng dụng của hàm Subtotal

DẠNG 1: Tính tổng cột “Đã bán” trong bảng dữ liệu dưới đây:

Để tính tổng cột “Đã bán” là dữ liệu dọc, chúng ta sẽ sử dụng hàm Subtotal. Và chúng ta sẽ chọn phép tính của hàm Sum để tính tổng cột. Cú pháp như sau:

SUBTOTAL(9,Cột “Đã bán”)

Một điều rất tuyệt khi sử dụng hàm Subtotal là khi bạn sử dụng chức năng Lọc (Filter) của Excel thì hàm Subtotal sẽ tự động loại trừ những giá trị không được lọc ra khỏi, hiện kết quả của những ô được lọc.

Tương tự như cú pháp của hàm Subtotal tính tổng bạn có thể thay các phép tính có trong hàm Subtotal để tính các giá trị như giá trị trung bình (AVERAGE), giá trị lớn nhất (MAX), giá trị nhỏ nhất (MIN),…

DẠNG 2: Tính các giá trị có thể nhìn thấy

Tính tổng của cột “Trong kho” khi bảng dữ liệu đã được lọc, bảng bên dưới:

Để có thể chỉ tính các ô mình nhìn thấy thì bạn cũng sử dụng hàm Subtotal nhưng khi chọn số của phép tính trong hàm Subtotal thì bạn phải sử dụng số từ 101 – 111. Cú pháp để tính tổng như sau:

SUBTOTAL(109,Cột “Trong kho”)

Tương tự như vậy thì các giá trị khác bạn cũng dùng các phép tính trong hàm Subtotal có số từ 101 – 111.

DẠNG 3: Tính các giá trị nhưng loại bỏ giá trị có chứa hàm Subtotal

Cho bảng bên dưới, tính tổng cột “Đã bán”.

Bảng dữ liệu trên có 2 ô được tính bằng hàm Subtotal, do hàm Subtotal có thể tự bỏ những ô có chứa hàm Subtotal. Cú pháp sẽ như sau:

SUBTOTAL(9,Cột “Đã bán”)

DẠNG 4: Dùng hàm Subtotal để đếm ô

Cho bảng dữ liệu như bên dưới, bạn hãy đếm ô ở cột “Đã bán”. Khi đã bị ẩn đi hai hàng số 6 và số 7

Nếu như ở bài này chúng ta ẩn đi hàng số 6 và số 7 thì khi sử dụng hàm Subtotal với phép tính bên trong hàm Subtotal khác nhau sẽ cho ra kết quả khác nhau.

Dùng hàm Subtotal để đếm ô ở Cột “Đã bán”, theo cú pháp:

SUBTOTAL(3,Cột “Đã bán”)

Cú pháp của hàm Subtotal này sẽ đếm cả những ô đã bị ẩn đi, nên sẽ cho ra kết quả là 5.

Nếu dùng hàm Subtotal, theo cú pháp sau:

SUBTOTAL(103,Cột “Đã bán”)

Với phép tính khác bên trong hàm Subtotal sẽ cho ra kết quả 4 vì ở phép tính này chỉ đếm các ô có giá trị mà bạn đang thấy.

Bài viết này nói về hàm Subtotal và cách ứng dụng của hàm. Hi vọng bạn sẽ áp dụng thành công hàm này để có thể tính toán các dữ liệu phức tạp. Chúc bạn thành công!