Nhắc đến Excel thì không thể không nhắc đến các loại hàm. Hàm Subtotal 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 đó:.
| 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
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!
Tham khảo các bài viết hữu ích khác của Uniace tại Chuyên Đề Excel.
Team Content Uniace.