Buzz
Trong Excel, có nhiều hàm tính toán như SUM, AVERAGE, MIN để phân tích dữ liệu. Để tính tổng phụ, bạn có thể sử dụng hàm SUBTOTAL kết hợp với các hàm trên. Hàm SUBTOTAL hỗ trợ 11 hàm tính toán khác nhau, mỗi hàm tương ứng với một công thức riêng.
Mẹo sử dụng hàm SUBTOTAL trong Excel
Cách áp dụng hàm SUBTOTAL trong Excel?
1. Cú pháp của hàm SUBTOTAL
=SUBTOTAL(số_hàm, tham_chiếu1, tham_chiếu2,...)
Danh sách các tham số:
+ Số_hàm: các số 1 đến 11 (hoặc 101 đến 111) xác định loại hàm sẽ được sử dụng trong phép tính SUBTOTAL. Bạn có thể tham khảo bảng mã số tương ứng của Số_hàm ở dưới đây.
+ Tham_chiếu1, tham_chiếu2
Bảng mã số của Số_hàm như sau:
Do đó, bạn chỉ cần nhập số từ 1 đến 11 (hoặc 101 đến 111) vào cú pháp hàm SUBTOTAL thì hàm SUBTOTAL sẽ hoạt động tương tự như hàm đó.
Khi bạn sử dụng các số từ 1 đến 11 cho đối số function_num, hàm SUBTOTAL sẽ tính cả giá trị ẩn, trong khi các số từ 101 đến 111 sẽ không tính các giá trị ẩn.
Nếu bạn nhập một giá trị khác ngoài khoảng từ 1 đến 11 hoặc 101 đến 111, Excel sẽ trả về lỗi #VALUE!.
2. Ví dụ minh họa cách sử dụng hàm SUBTOTAL trong Excel
Ví dụ 1: Tính trung bình cộng của vùng được chọn
Giả sử bạn đã có một bảng tính Excel với dữ liệu như dưới đây và muốn tính trung bình cộng các giá trị, bạn thực hiện như sau:
Nhấp chuột vào một ô trống và chọn thanh công cụ công thức. Nhập cú pháp của hàm SUBTOTAL vào thanh công cụ, bắt đầu với =SUBTOTAL( .
Sau đó là đối số function_num tương ứng với một hàm Excel. Trong ví dụ này, chúng ta sử dụng hàm Average để tính trung bình cộng các giá trị trong phạm vi ô được chọn và không bao gồm các giá trị ẩn. Hàm Average không tính giá trị ẩn, có mã số 101, nên chúng ta nhập =SUBTOTAL(101 .
Tiếp theo, xác định phạm vi cần tính trung bình cộng các giá trị. Trong ví dụ này, chúng ta tính tổng các giá trị trong phạm vi ô từ A1 đến A10.
Do đó, cú pháp đầy đủ của hàm SUBTOTAL sẽ là =SUBTOTAL(101,A1:A10).
Kết quả sẽ như hình dưới đây:
Nếu bạn muốn tính tổng phụ của nhiều phạm vi, chỉ cần thêm các phạm vi ô và ngăn cách nhau bằng dấu phẩy sau đối số function_num.
Ví dụ 2: Đếm các ô không trống trong bảng tính
Giả sử bạn đã có một bảng tính với dữ liệu như bên dưới.
Để đếm số ô không trống và xác định số nhân viên đã gửi liên kết dữ liệu, bạn thực hiện như sau:
Nhấn vào một ô trống trong bảng Excel và chọn thanh công cụ công thức.
Sau đó, nhập cú pháp của hàm SUBTOTAL vào thanh công cụ công thức, bắt đầu bằng =SUBTOTAL(.
Trong ví dụ này, để đếm các ô không trống, chúng ta sẽ sử dụng hàm COUNTA với số 3. Do đó, function_num sẽ là 3 và ref1 = B2:B8 là phạm vi cần tính tổng.
Cú pháp đầy đủ sẽ là =SUBTOTAL(3,B2:B8)
Kết quả cuối cùng sẽ như sau:
Bên cạnh đó, bạn cũng có thể sử dụng hàm SUM để tính tổng một cách nhanh chóng và chính xác nhất.
- Xem thêm: Hàm SUM trong Excel
Hàm SUBTOTAL là một công cụ quan trọng giúp xử lý dữ liệu nhanh chóng trong Excel. Hy vọng qua bài viết này, bạn sẽ hiểu rõ về cách sử dụng hàm SUBTOTAL.
