Hàm SUBTOTAL trong Excel dùng để tính toán giá trị tổng phụ trong một phạm vi ô nhất định tùy theo hàm tính toán mà bạn chọn lựa. Trong bài viết này, chúng ta sẽ tìm hiểu kỹ hơn về cách dùng hàm SUBTOTAL, ứng dụng của hàm SUBTOTAL trong Excel và thực tế.
Trong Excel, chúng ta có sẵn các hàm tính toán như hàm SUM, hàm AVERAGE, hàm MIN để phân tích dữ liệu. Nếu muốn tính tổng phụ thì bạn có thể dùng hàm SUBTOTAL kèm theo các hàm vừa nêu. Bạn có thể dùng tới 11 hàm trong hàm SUBTOTAL, mỗi hàm lại tương ứng với một công thức khác nhau.
Mẹo dùng hàm SUBTOTAL trong Excel
Cách dùng hàm SUBTOTAL trong Excel?
1. Cú pháp hàm SUBTOTAL
=SUBTOTAL(function_num, ref1, ref2,…)
Các tham số:
+ Function_num: các số 1 đến 11 (hoặc 101 đến 111) xác định hàm nào được dùng để tính toán trong phép tính SUBTOTAL. Bạn có thể xem bảng mã số quy ước của Function_num ở bên dưới.
+ Ref1, ref2: là các phạm vi tham chiếu mà bạn muốn làm phép tính tổng phụ.
Mã số của Function_num như sau:
Như vậy, bạn chỉ cần thế 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 giống như hàm đó.
Nếu bạn dùng 1-11 cho đối số function_num thì hàm SUBTOTAL sẽ tính cả giá trị ẩn, trong khi đối số 101-111 sẽ không tính các giá trị ẩn.
dùng một giá trị khác 1-11 hoặc 101-111 thì Excel sẽ trả về lỗi #VALUE!.
2. Ví dụ dùng hàm SUBTOTAL trong Excel
Ví dụ 1: Tính trung bình cộng vùng được chọn
Giả sử bạn có một bảng tính Excel đã nhập sẵn dữ liệu như hình dưới và muốn tính trung bình cộng các giá trị thì làm như sau:
Click vào một ô trống rồi chọn thanh cú pháp. Bạn nhập cú pháp của hàm SUBTOTAL vào thanh cú pháp, bắt đầu với =SUBTOTAL( .
Tiếp theo 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 bỏ qua giá trị ẩn tương ứng với 101 nên chúng ta nhập =SUBTOTAL(101 .
Tiếp theo, chúng ta xác định phạm vi cần tính trung bình cộng các giá trị. Ở ví dụ này, chúng ta sẽ tính tổng các giá trị trong phạm vi ô từ A1 đến A10.
Như vậy, hàm SUBTOTAL sẽ có cú pháp đầy đủ là =SUBTOTAL(101,A1:A10).
Kết quả sẽ giống như hình dưới:
Nếu muốn tính tổng phụ của nhiều phạm vi thì bạn thêm các phạm vi ô ngăn cách nhau bằng dấu phẩy phía 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 các dữ liệu như hình dưới.
Để đếm các ô không trống nhằm xác định số nhân viên đã gửi link dữ liệu, bạn làm như sau:
Click vào một ô trống trong bảng Excel rồi chọn thanh cú pháp.
Bạn nhập cú pháp của hàm SUBTOTAL vào thanh cú pháp, bắt đầu với =SUBTOTAL(.
Trong ví dụ này, chúng ta cần đếm các ô không trống nên sẽ chọn hàm COUNTA, tương ứng với số 3. Do đó function_num sẽ là 3, ref1 = B2:B8 là phạm vi cần tính tổng.
Như vậy, cú pháp đầy đủ sẽ là =SUBTOTAL(3,B2:B8)
Kết quả cuối cùng sẽ như sau:
Ngoài ra bạn đọc cũng có thể dùng hàm SUM để tính tổng nhanh chóng và chính xác nhất.
Hàm SUBTOTAL là một tool hữu ích giúp bạn xử lý dữ liệu nhanh chóng. Bạn sẽ thấy nhiều ứng dụng khác của SUBTOTAL khi dùng Excel. Hy vọng qua bài viết trên của Trường Hải Tiến Giang, bạn sẽ nắm rõ về cách dùng hàm SUBTOTAL trong Excel.
Trong Excel, chúng ta có sẵn các hàm tính toán như hàm SUM, hàm AVERAGE, hàm MIN để phân tích dữ liệu. Nếu muốn tính tổng phụ thì bạn có thể dùng hàm SUBTOTAL kèm theo các hàm vừa nêu. Bạn có thể dùng tới 11 hàm trong hàm SUBTOTAL, mỗi hàm lại tương ứng với một công thức khác nhau.
