Hàm SUBTOTAL trong Excel – Hướng dẫn chi tiết và ví dụ thực tế

Hàm SUBTOTAL trong Excel là một trong những hàm vô cùng hữu ích khi bạn cần tính toán các giá trị trong một phạm vi dữ liệu. Đặc biệt là khi bạn muốn áp dụng các phép toán chỉ cho các dữ liệu không bị ẩn (ví dụ: sau khi lọc hoặc nhóm).

Mục lục cho bài viết

1 Hàm SUBTOTAL là gì?
2 Cấu trúc của hàm SUBTOTAL
3 Các function_num trong hàm SUBTOTAL
4 Ví dụ sử dụng hàm SUBTOTAL
5 Lợi ích khi sử dụng hàm SUBTOTAL
6 Kết luận

Hàm SUBTOTAL là gì?

Hàm SUBTOTAL là một hàm tổng hợp trong Excel giúp tính toán các giá trị theo một phạm vi nhất định, và hỗ trợ tính toán với hoặc không tính các giá trị bị ẩn (bị lọc hoặc ẩn thủ công). Hàm này rất hữu ích trong việc làm việc với các bảng dữ liệu lớn, nơi mà bạn cần thực hiện tính toán mà không muốn đưa các giá trị ẩn vào kết quả.

Cấu trúc của hàm SUBTOTAL

Cấu trúc của hàm SUBTOTAL trong Excel như sau:

function_num. Là mã số của chức năng tính toán mà bạn muốn sử dụng. Mã số này có thể từ 1 đến 11 (cho các phép toán tính tổng, trung bình, đếm, v.v…) hoặc từ 101 đến 111 (dành cho các phép toán tương tự nhưng không tính các giá trị bị ẩn).
ref1, ref2, …. Là các phạm vi hoặc các tham chiếu đến các ô mà bạn muốn tính toán.

Các function_num trong hàm SUBTOTAL

Dưới đây là danh sách các mã số function_num phổ biến trong hàm SUBTOTAL:

1 hoặc 101: Tính Average (Trung bình).
2 hoặc 102: Tính Count (Đếm số ô chứa số).
3 hoặc 103: Tính CountA (Đếm số ô không rỗng).
4 hoặc 104: Tính Max (Giá trị lớn nhất).
5 hoặc 105: Tính Min (Giá trị nhỏ nhất).
9 hoặc 109: Tính Sum (Tổng).
10 hoặc 110: Tính Variance (Phương sai).
11 hoặc 111: Tính Stdev (Độ lệch chuẩn).

Lưu ý: Các mã số từ 1 đến 11 tính toán bao gồm cả các giá trị bị ẩn. Trong khi, các mã số từ 101 đến 111 không tính các giá trị bị ẩn do bộ lọc hoặc ẩn thủ công.

Ví dụ sử dụng hàm SUBTOTAL

Ví dụ 1: Tính tổng trong bảng dữ liệu

Giả sử bạn có một bảng dữ liệu về doanh thu bán hàng trong Excel. Bạn muốn tính tổng doanh thu từ ô B2 đến B10 mà không tính các giá trị bị ẩn do bộ lọc:

Trong đó, 9 là mã số của hàm SUM, giúp tính tổng các giá trị trong phạm vi B2:B10. Nếu có các hàng bị ẩn do bộ lọc, chúng sẽ không được tính vào kết quả.

Ví dụ 2: Tính trung bình các giá trị trong cột

Nếu bạn muốn tính trung bình của các giá trị trong cột C2:C15 mà không tính các giá trị bị ẩn, bạn có thể sử dụng hàm SUBTOTAL như sau:

Trong đó, 1 là mã số của hàm AVERAGE, giúp tính trung bình các giá trị trong phạm vi C2:C15.

Ví dụ 3: Tính số lượng ô có giá trị trong cột

Giả sử bạn cần đếm số lượng các ô chứa giá trị trong phạm vi D2:D20, bạn có thể sử dụng hàm SUBTOTAL với mã số 3 (CountA):

Hàm này sẽ đếm tất cả các ô không rỗng trong phạm vi D2:D20, và cũng bỏ qua các ô bị ẩn.

Lợi ích khi sử dụng hàm SUBTOTAL

Bỏ qua các giá trị ẩn. Khi làm việc với các bảng dữ liệu lớn, bạn có thể ẩn các hàng không cần thiết và sử dụng hàm SUBTOTAL để tính toán mà không lo bị tính các giá trị ẩn đó.
Tính toán linh hoạt. Hàm SUBTOTAL không chỉ giúp tính tổng, mà còn hỗ trợ các phép toán như trung bình, đếm, min, max, phương sai, và độ lệch chuẩn.
Hỗ trợ bộ lọc. Hàm SUBTOTAL rất hữu ích khi bạn sử dụng bộ lọc dữ liệu trong Excel để chỉ tính toán các giá trị mà bạn đang hiển thị.

Kết luận

Hàm SUBTOTAL là một công cụ tiện ích trong Excel. Nó giúp tính toán dữ liệu mà không cần lo lắng về các giá trị bị ẩn hoặc bị lọc. Việc sử dụng đúng các mã số function_num giúp dễ dàng thực hiện các phép toán cần thiết như tổng, trung bình, đếm, và nhiều hơn nữa.