Công thức và cách sử dụng hàm Subtotal trong Excel

Subtotal là một trong những hàm rất linh hoạt trong Excel nhưng có nhiều bạn vẫn chưa thực sự hiểu về Subtotal và ứng dụng của nó trong học tập cũng như công việc. Bởi lẽ, tên hàm Subtotal không gợi cho chúng ta nhiều ý nghĩa như các hàm Sum, Count, Vlookup,.. Ngoài ra, một điều khó nữa của Subtotal là các đối số và ý nghĩa của chúng.

Tuy nhiên, đừng lo lắng! Trong bài biết này, chúng tôi sẽ giới thiệu với các bạn cách sử dụng đầy đủ và chi tiết nhất của hàm Subtotal trong Excel.

Cách sử dụng hàm Subtotal trong Excel

1. Cấu trúc và cách sử dụng

– Công dụng: sử dụng một hàm tính toán do người dùng lựa chọn và áp dụng lên vùng dữ liệu được quy định.

– Cú pháp:

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

Trong đó:

• function_num: 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 SUBTOTAL.
• Đối số thứ nhất của hàm function_num bắt buộc bạn phải nhớ. Tuy nhiên, từ Excel 2007+, Microsoft đã bổ sung tính năng AutoComplete giúp chúng ta lựa chọn mà không cần nhớ nữa.

• 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+, 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.

Lưu ý:

– Hàm SUBTOTAL được thiết kế để tính toán cho các cột số liệu theo chiều dọc, nó không được thiết kế để tính theo chiều ngang. Do đó, một số tính năng có thể không đúng khi bạn sử dụng SUBTOTAL để tính theo chiều ngang (ví dụ, ẩn cột).

– 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.

– Nếu dữ liệu tham chiếu dạng 3-D thì hàm SUBTOTAL báo lỗi #VALUE!. Ví dụ, để tính tổng giá trị các ô A1 trong các sheet liên tục từ Sheet1 đến Sheet2, nếu sử dụng tham chiếu dạng 3-D là =SUM(Sheet1:Sheet5!A1), tuy nhiên nếu áp dụng kiểu tham chiếu này cho SUBTOTAL, bạn sẽ nhận được thông báo lỗi #VALUE!

2. Ví dụ minh họa

Ví dụ 1:  Trong ví dụ sau đây, chúng tôi minh họa cách sử dụng hàm SUBTOTAL với đối số là 9 và 109 (hàm tính tổng) và so sánh với kết quả khi sử dụng hàm SUM.

Như các bạn thấy, hàm SUM tính tổng của vùng dữ liệu bất kể nó có bị ẩn hay không. Hàm SUBTOTAL với function_num = 9 vẫn tính tổng của các vùng bị ẩn hàng. Hàm SUBTOTAL với function_num = 109 chỉ tính tổng của các vùng không bị ẩn.

Ví dụ 2: Như các bạn đã biết, tham chiếu 3-D tỏ ra đặc biệt hữu hiệu khi cần tổng hợp dữ liệu giữa các sheet có cấu trúc tương tự nhau.

Trong ví dụ sau, tại ô A1 của các sheet từ Sheet1:Sheet5 chứa một số tự nhiên lần lượt từ 1-5. Bằng cách sử dụng tham chiếu 3-D, chúng ta nhanh chóng có được tổng của các ô A1 trong 5 sheet này (tại ô F5).

Tuy nhiên, cách này không áp dụng được với hàm SUBTOTAL (ô F6). Bởi lẽ, hàm SUBTOTAL được thiết kế với tham chiếu kiểu 2-D nên sẽ báo lỗi #VALUE! khi áp dụng tham chiếu 3-D cho hàm này.

Lời kết

SUBTOTAL là một trong những hàm thường xuyên sử dụng khi bạn làm việc với dữ liệu, đặc biệt các dữ liệu lớn cần sử dụng Filter. Do đó, hi vọng các bạn đã nắm được chi tiết cách sử dụng hàm này trong Excel. Nếu như có thắc mắc nào khác liên quan đến sử dụng các hàm trong Excel thì đừng quên để lại lời bình của bạn trong phần dưới nhé.

Chúc bạn thành công!