Cách sử dụng hàm SUBTOTAL trong Excel và ví dụ 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 sử dụng hàm SUBTOTAL trong Excel

Cách sử 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 thực hiện 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 sử 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.

Sử 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ụ sử 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ẽ 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ể sử dụng hàm SUM để tính tổng 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ụ 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 sử dụng Excel. Hy vọng qua bài viết trên của Taimienphi.vn, bạn sẽ nắm rõ về cách sử dụng hàm SUBTOTAL trong Excel.

Công thức hàm IF trong Excel, cú pháp và ví dụ sử dụng

Excel - Hàm GAMMAINV trong Excel, Ví dụ minh họa và cách dùng

Excel - Hàm CUMPRINC trong Excel, Ví dụ và cách dùng

Hàm RIGHT trong Excel, cú pháp và ví dụ minh họa

Hàm CUMIPMT, sử dụng hàm tính số tiền lãi cộng dồn trong Excel

Hàm DAVERAGE trong Excel, Ví dụ và cách dùng