Hàm Subtotal trong Excel: Công Thức và Cách dùng Subtotal trong Excel đầy đủ có ví dụ

Excel là một phần mềm rất quan trọng đối với dân văn phòng, các hàm trong Excel hữu ích làm cho công việc của chúng ta được xử lí đơn giản, hiệu quả hơn, tuy nhiên trong Excel  có rất nhiều hàm với những tính năng và cách sử dụng khác nhau mà chúng ta không thể nhớ hết được. Khi làm việc với Excel, hàm Subtotal được coi là một trong các hàm cơ bản của excel được dùng để  tính toán tổng phụ trong Excel một cách dễ dàng, nhanh chóng nhất, hơn nữa hàm này có thể đếm số ô trống, tính trung bình, tìm giá trị lớn nhất/nhỏ nhất( Min/Max) hay tính tổng giá trị danh sách đã lọc trong excel. Bài viết dưới đây sẽ nêu ra công thức và cách sử dụng hàm SUBTOTAL trong Excel.

1. Chức năng của hàm SUBTOTAL trong Excel

Hàm SUBTOTAL của Excel có nhiệm vụ là trả về kết quả tổng hợp cho các giá trị được cung cấp. Hàm SUBTOTAL có thể trả về SUM, AVERAGE, COUNT, MAX và nhiều hàm khác và hàm SUBTOTAL có thể bao gồm hoặc loại trừ các giá trị trong các hàng ẩn. Trong một số trường hợp ,chúng ta sử dụng “tổng phụ” (subtotal) không phải tổng các con số trong 1 dãy ô xác định. Còn những hàm khác trong Excel được sử dụng chỉ để thực hiện 1 chức năng cụ thể, nhưng hàm SUBTOTAL rất linh hoạt tức là nó có thể tính toán hoặc thực hiện một số  phép toán logic như đếm số ô, tính trung bình, tìm giá trị lớn nhất/nhỏ nhất…

2. Công thức của hàm SUBTOTAL trong Excel

Ta có công thức như sau:

=SUBTOTAL(function_num, ref1, ref2,...)

Trong đó:

• function_num: là hàm tính toán dùng để tính toán cả giá trị ẩn (do ẩn hàng). Các con số từ 1 đến 11 và 101 đến 111 quy định hàm nào sẽ được dùng để tính toán trong hàm SUBTOTAL.

•  

• Đối số thứ nhất của hàm function_num bạn phải nhớ ý nghĩa và chức năng cuả các con số. Tuy nhiên, từ Excel 2007 trở lên, Microsoft đã bổ sung tính năng AutoComplete giúp chúng ta có thể lựa chọn mà không cần nhớ. Điều này giúp ích cho chúng ta rất nhiều.

•  

• ref1, ref2, ….: Các vùng địa chỉ tham chiếu mà bạn muốn thực hiện phép tính trên đó. Đối với Excel 2007, Excel 2010, Excel 2013, bạn có thể dùng đến 254 vùng, còn với Excel 2003 trở về trước thì con số này chỉ là 29.

Một số mô tả chi tiết các chức năng của Function_num như sau:

3. Ví dụ minh họa

Cho ví dụ dưới đây là hóa đơn mua hàng các sản phẩm sách, truyện. Hãy dùng hàm SUBTOTAL để tính tổng tất cả các sản phẩm, tính trung bình tổng tiền mỗi sản phẩm, tìm ra sản phẩm có số tiền lớn nhất, nhỏ nhất.

Đầu tiên, tính tổng số tiền, ta thực hiện công thức như sau: 

= SUBTOTAL(9, E2:E9)

Thứ hai để tính trung bình số tiền mỗi sản phẩm, ta sử dụng công thức sau:

= SUBTOTAL(1, E2:E9)

Thứ ba, để tìm ra sản phẩm có số tiền lớn nhất, ta sử dụng công thức như sau:

= SUBTOTAL(4, E2:E9)

Cuối cùng để tìm ra sản phẩm có số tiền nhỏ nhất, ta sử dụng công thức sau:

= SUBTOTAL(5, E2:E9)

Như vậy, ta được kết quả như sau:

Một số lưu ý khi sử dụng hàm Subtotal:

•  Hàm SUBTOTAL được dùng để tính toán cho các cột số liệu theo chiều dọc, nó không được dùng để tính theo chiều ngang. Do đó, một số tính năng có thể không đúng khi  sử dụng SUBTOTAL để tính theo chiều ngang .
•  
• Nếu các đối số ref1, ref2,… có chứa hàm SUBTOTAL thì các công thức này sẽ được bỏ qua để tránh tính trùng 2 lần.
•  
• Nếu function_num từ 1 đến 11 thì hàm SUBTOTAL tính toán bao gồm cả các giá trị ẩn trong tập số liệu do hàng chứa vùng đó bị ẩn. Nếu function_num từ 101 đến 111 thì hàm SUBTOTAL chỉ tính toán cho các giá trị không ẩn trong tập số liệu (không tính các giá trị ở hàng ẩn).
•  
• Đối với vùng dữ liệu bị ẩn do Filter, SUBTOTAL đều bỏ qua khi tính toán dù function_num là bất cứ giá trị nào.

Qua bài viết trên, chúng ta đã có thể hiểu rõ cụ thể về hàm Subtotal, về cú pháp của hàm và cách hàm Subtotal hoạt động trong việc tính toán với những bảng tính trong Excel. Subtotal là một hàm cơ bản trong Excel, rất hữu ích trong việc tính tổng, giá trị trung bình và dò tìm dữ liệu. Trên đây là hướng dẫn chi tiết cách sử dụng hàm Subtotal cơ bản nhất và một số ví dụ cụ thể hi vọng giúp ích cho các bạn. Hi vọng các bạn đã hiểu rõ hơn về hàm SUBTOTAL và có thể ứng dụng nó tốt nhất trong công việc của mình.