Hàm SUBTOTAL trong Excel - Cách sử dụng hàm SUBTOTAL và ví dụ cụ thể

Hàm SUBTOTAL là hàm trả về tổng phụ trong một danh sách hay một cơ sở dữ liệu. Trong hàm SUBTOTAL hỗ trợ 11 function khiến nó có thể thực hiện được chức năng của 11 hàm khác nhau như SUM, COUNT, AVERAGE, MAX, MIN…v.v…Có thể nói đây là một hàm rất đa năng và linh hoạt. Vậy hàm này dùng có khó hay không và dùng thế nào, mời các bạn cùng theo dõi bài viết sau đây của ThuThuat123.com

HÀM SUBTOTAL TRONG EXCEL – CÁCH SỬ DỤNG VÀ VÍ DỤ CỤ THỂ

1. Cú pháp của hàm SUBTOTAL

Cú pháp: SUBTOTAL(function_num,ref1,[ref2],...)

Trong đó:

- Fuction_num là các con số từ 1 đến 11 hoặc từ 101 đến 111. Đây là các con số xác định chức năng mà hàm sẽ thực hiện.

Tuy nhiên các hàm từ 1-11 sẽ tính tổng phụ bao gồm cả các giá trị bị ẩn đi, còn các hàm từ 101-111 sẽ bỏ qua các giá trị bị ẩn. Để biết chức năng của các Function các bạn hãy theo dõi bảng dưới đây:

Các bạn cũng không nhất thiết phải nhớ các Function này bởi khi nhập hàm SUBTOTAL trong Excel các function sẽ tự động hiện ra để gợi ý cho bạn.

- ref1, ref2, ref3 : là phạm vi, mảng hay các ô mà bạn muốn tính tổng phụ. Trong đó ref1 là bắt buộc phải có, từ ref2 trở đi có thể có hoặc không.
Một số lưu ý:

• Nếu có hàm SUBTOTAL khác trong giá trị ref1, ref2,…thì các hàm lồng vào này sẽ được bỏ qua để không bị đếm 2 lần.
• Các hàm từ 1 đến 11 sẽ tính toán bao gồm các giá trị ẩn trong vùng dữ liệu được chọn. Các hàm từ 101 đến 111 sẽ loại trừ các giá trị ẩn trong vùng dữ liệu (chỉ tính toán với các giá trị hiển thị).
• Hàm SUBTOTAL với function_num bất kỳ sẽ không được áp dụng nếu hàng bị ẩn bởi lệnh Filter (Auto Filter).
• Hàm SUBTOTAL chỉ dùng cho cột dữ liệu hoặc phạm vi dọc mà không dùng cho hàng hay phạm vi ngang. Vì thế khi bạn ẩn cột sẽ không ảnh hưởng tới giá trị tính toán, còn ẩn hàng thì có ảnh hưởng.
• Các tham chiếu nào là tham chiếu 3-D thì hàm SUBTOTAL sẽ trả về giá trị #VALUE!
  

2. Cách dùng và ví dụ về hàm SUBTOTAL

Ví dụ: Chúng ta có bảng dữ liệu giá bán các mặt hàng như dưới đây, nhiệm vụ là tính tổng giá trị của cột giá bán C4:C8. Chúng ta sẽ sử dụng công thức SUBTOTAL 9 để cộng tổng các giá trị từ C4 đến C8. Cú pháp sẽ như sau:

=SUBTOTAL(9,C4:C8)

Tương tự như vậy các bạn có thể dùng hàm SUBTOTAL 1, SUBTOTAL 2, SUBTOTAL 3..để tính Average, count, max, min..như hình dưới đây:

Tiếp theo chúng ta xem sự khác biệt của các Function_num từ 1-11 và 101-111.

Ở đây mình đã ẩn đi mặt hàng D, các bạn có thể thấy hàm SUBTOTAL 9 vẫn tính tổng giá bán của mặt hàng bị ẩn, còn hàm SUBTOTAL 109 đã bỏ qua mặt hàng bị ẩn nên tổng giá bán đã giảm đi, nếu các bạn dùng hàm SUM thông thường thì gí trị bị ẩn cũng được tính vào tổng. Đây chính là điểm mạnh của hàm SUBTOTAL.

Trên đây là bài giới thiệu về hàm SUBTOTAL, cách sử dụng và ví dụ cụ thể. Hy vọng qua bài viết này các bạn đã hiểu rõ về hàm SUBTOTAL và biết cách sử dụng hàm này một cách hiệu quả. Chúc các bạn thành công!