Trong Excel, hàm SUBTOTAL được sử dụng để tính tổng dãy số với điều kiện được thiết lập. Nó sẽ giúp mọi người tiết kiệm thời gian và nâng cao hiệu quả làm việc. Nếu mọi người vẫn chưa biết cách sử dụng hàm SUBOTOTAL này, hãy cùng FPT tham khảo ngay bài viết dưới đây nhé!
Giới thiệu hàm SUBTOTAL
Công thức hàm SUBTOTAL trong Excel
=SUBTOTAL(function_num, ref1, [ref2],…)
Bạn đang đọc: Công thức và hướng dẫn sử dụng hàm SUBTOTAL trong Excel đơn giản
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.
Tìm hiểu thêm: Cảnh báo This Connection Is Not Private là gì? Cách xử lý nó
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.
>>>>>Xem thêm: Cách vẽ lục giác trong CAD bằng lệnh Polygon đơn giản, có ví dụ minh hoạ
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.
Tham khảo các mẫu laptop cao cấp, chính hãng đang được ưu đãi tại Blogkienthuc.edu.vn: Laptop chính hãng
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
