Công thức và hướng dẫn sử dụng hàm SUBTOTAL trong Excel đơn giản

Giới thiệu hàm SUBTOTAL

Công thức hàm SUBTOTAL trong Excel

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

Các tham số của hàm SUBTOTAL:

• Function_num: Với các con số từ 1 đến 11 và 101 đến 111. Dùng để xác định hàm nào sẽ được dùng để xác định điều kiện trong hàm SUBTOTAL.
• Ref1, Ref2, …: Tối đa 254 ô (dãy ô) được sử dụng để tính tổng phụ.

Lưu ý khi sử dụng hàm SUBTOTAL trong Excel

• Hàm SUBTOTAL được sử dụng để tính tổng cho các cột số liệu (theo chiều dọc)
• Để tính việc trùng 2 lần, các đối số Ref1, Ref2,… có chứa hàm SUBTOTAL sẽ được bỏ qua.
• Hàm SUBTOTAL sẽ tính toán bao gồm những giá trị bị ẩn do hàng chứa vùng dữ liệu bị ẩn với Function_num từ 1 đến 11. Hàm SUBTOTAL sẽ chỉ tính toán với các giá trị hiện hữu trong tập số liệu (bỏ qua các giá trị hàng ẩn) với Function_num từ 101 đến 111.
• Hàm SUBTOTAL sẽ bỏ qua đối với những vùng dữ liệu bị ẩn với Filter.

Ứng dụng và minh họa cách sử dụng hàm SUBTOTAL trong Excel

Để giúp mọi người nhanh chóng nắm bắt được cách sử dụng hàm SUBTOTAL trong Excel, ta hãy đi đến phần ứng dụng và minh họa của hàm. Sử dụng Danh sách tồn kho cửa hàng Abe làm ví dụ.

Đếm những ô được lọc không bỏ trống

Đề: Sử dụng hàm SUBTOTAL để đếm xem có bao nhiêu mặt hàng đã hết.

Để thực hiện yêu cầu này, mọi người sẽ áp dụng hàm SUBTOTAL 3 hay SUBTOTAL 103. Bạn sẽ sử dụng SUBTOTAL 103 khi không muốn đếm các dữ liệu đã bị ẩn đi.

Trong ví dụ này, ta sẽ ẩn đi 2 hàng 5 và 6. Khi sử dụng hàm SUBTOTAL 3 và hàm SUBTOTAL 103 sẽ mang đến 2 kết quả khác nhau.

Tại ô sử dụng hàm SUBTOTAL 3, ta nhập vào công thức =SUBTOTAL(3,D3:D7).

2 sẽ là kết quả khi sử dụng SUBTOTAL 3, do tính cả những ô đã ẩn đi.

Tại ô hàm SUBTOTAL 103, ta nhập vào công thức =SUBTOTAL(103,D3:D7).

1 là kết quả của hàm SUBTOTAL hiển thị, do không tính các ô đã ẩn đi.

Bỏ qua những giá trị tồn tại trong công thức SUBTOTAL lồng nhau

Khi sử dụng hàm SUBTOTAL, dãy ô trong công thức đã có công thức SUBTOTAL khác lồng vào thì sẽ bị bỏ qua. Chúng chỉ tính những dãy ô không có công thức SUBTOTAL.

Sử dụng Danh sách tồn kho cửa hàng OGF làm ví dụ.

Đề: Sử dụng ví dụ trên để tính giá trị trung bình của từng trung tâm A, B và trung bình của cả hai trung tâm.

Để tính trung bình, ta cần chọn hàm AVERAGE, tương đương với số 1 hoặc 101.

Ở đây, mình nhập vào ô chứa kết quả trung bình cộng trung tâm A =SUBTOTAL(1,C3:C5).

Kết quả trung bình cộng trung tâm A là 50,667.

Nhập vào ô chứa kết quả tính trung bình cộng trung tâm B =SUBTOTAL(1,C7:C8)

Kết quả trung bình cộng của trung tâm B là 72.5

Đến phần tính trung bình cộng hai trung tâm, bạn cũng thực hiện công thức tương tự =SUBTOTAL(1,C3:C9) vào ô chứa kết quả.

Kết quả trung bình đã được tính trước đó của trung tâm A và B sẽ được loại bỏ. Chúng chỉ tính những dãy ô không chứa hàm SUBTOTAL. Do đó, kết quả là 59.4.

Những lỗi khi sử dụng hàm SUBTOTAL trong Excel

Khi mọi người sử dụng hàm SUBTOTAL để tính toán trong Excel, một vài lỗi sau sẽ thường gặp:

• #VALUE!: Function_num (số xác định chức năng) không nằm trong 1 – 11 hay 101 – 111 hoặc tham chiếu 3D được sử dụng như tham chiếu (Ref).
• #DIV/0!: Lỗi sẽ xuất hiện khi 1 tổng được xác định phải chia cho 0 (ví dụ: tính trung bình tổng hay độ lệch chuẩn của 1 dãy ô không tồn tại dữ liệu số).
• #NAME?: Sai chính tả hàm SUBTOTAL.

Kết lại

Có thể nói, sử dụng hàm SUBTOTAL trong Excel có thể giúp mọi người nhanh chóng tính toán các số liệu mà không cần phải phụ thuộc vào bảng tổng hợp phức tạp. Hy vọng qua bài viết này, bạn sẽ có thêm thủ thuật mới về Excel để phục vụ công việc hiệu quả hơn.

Cảm ơn bạn đã đọc hết bài viết.

Xem thêm:

• Cách sử dụng hàm chia trong Excel cực đơn giản có ví dụ dễ hiểu
• Cách tính giá trị tuyệt đối bằng hàm ABS trong Excel
• Cách sử dụng hàm TRIM trong Excel