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
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!
Tham khảo các bài viết hữu ích khác của Uniace tại Chuyên Đề Excel
- Cách dùng hàm sumifs trong excel
- Cách dùng hàm Count và hàm Counta trong excel
- Dùng hàm countifs và hàm countif như thế nào?
Team Content Uniace
