CÁCH DÙNG HÀM SUBTOTAL VÀ ỨNG DỤNG CỦA HÀM

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.
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ố
  • Ref1, Ref2, …: 1 hoặc nhiều ô, hoặc dãy ô để tính tổng phụ, tối đa 254 tùy chọn.

Xem thêm: Cách sử dụng hàm index, hàm match và cách kết hợp của hai hàm

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

  • 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.

Đọc thêm: Hướng dẫn cách thay đổi nguồn và cập nhật bảng tổng hợp PivotTable

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:

CÁCH DÙNG HÀM SUBTOTAL VÀ ỨNG DỤNG CỦA HÀM 1
Ứng dụng của hàm Subtotal dạng 1

Để 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”)

CÁCH DÙNG HÀM SUBTOTAL VÀ ỨNG DỤNG CỦA HÀM 2
Ứng dụng của hàm Subtotal dạng 1

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ÁCH DÙNG HÀM SUBTOTAL VÀ ỨNG DỤNG CỦA HÀM 3
Ứng dụng của hàm Subtotal dạng 2

Để 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”)

CÁCH DÙNG HÀM SUBTOTAL VÀ ỨNG DỤNG CỦA HÀM 4
Ứng dụng của hàm Subtotal dạng 2

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”.

CÁCH DÙNG HÀM SUBTOTAL VÀ ỨNG DỤNG CỦA HÀM 5
Ứng dụng của hàm Subtotal dạng 3

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”)

CÁCH DÙNG HÀM SUBTOTAL VÀ ỨNG DỤNG CỦA HÀM 6
Ứng dụng của hàm Subtotal dạng 3

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

CÁCH DÙNG HÀM SUBTOTAL VÀ ỨNG DỤNG CỦA HÀM 7
Ứng dụng của hàm Subtotal dạng 4

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.

CÁCH DÙNG HÀM SUBTOTAL VÀ ỨNG DỤNG CỦA HÀM 8
Ứng dụng của hàm Subtotal dạng 4

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

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

CÁCH DÙNG HÀM SUBTOTAL VÀ ỨNG DỤNG CỦA HÀM 9
Ứng dụng của hàm Subtotal dạng 4

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”)

CÁCH DÙNG HÀM SUBTOTAL VÀ ỨNG DỤNG CỦA HÀM 10
Ứng dụng của hàm Subtotal dạng 4

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!

Có thể bạn quan tâm: Hướng dẫn Power Query cơ bản cho người mới bắt đầu. (Phần II)

Tham khảo các bài viết hữu ích khác của Uniace tại Chuyên Đề Excel

Trả lời

Website UNIACE có sử dụng Cookie và thu thập thông tin cá nhân của học viên tuân thủ chính sách bảo vệ thông tin cá nhân của người tiêu dùng theo quy định của pháp luận. Tiếp tục sử dụng Website đồng nghĩa với việc bạn đồng ý về các điều khoản này. Xem thêm chi tiết tại https://uniace.vn/chinh-sach-bao-mat-thong-tin/