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

Khác với hàm SUM trong Excel, hàm Subtotal một trong các hàm cơ bản Excel được dùng để tính tổng phụ trong danh sách hoặc cơ sở dữ liệu, đếm số ô, tính trung bình trong Excel, tìm giá trị lớn nhất/nhỏ nhất hay tính tổng giá trị danh sách đã lọc trong Excel mà Quản trị mạng đã hướng dẫn bạn đọc. Bài viết dưới đây sẽ hướng dẫn bạn đọc cách sử dụng hàm SUBTOTAL trong Excel.

Công thức SUBTOTAL trong Excel

Hàm SUBTOTAL có 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.
• Ref1, Ref2, …: 1 hoặc nhiều ô, hoặc dãy ô để tính tổng phụ, tối đa 254.

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ếu các đối số ref1, ref2,… có chứa hàm SUBTOTAL thì 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 sẽ bỏ qua.

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

1. Tính tổng các hàng được lọc 

Để hiểu rõ về kiểu tính tổng này bạn tham khảo bài viết Tính tổng giá trị danh sách đã lọc trong Excel. Về cơ bản, hàm SUBTOTAL trong trường hợp này sẽ là:

=SUBTOTAL(9,pham_vi)

pham_vi ở đây là vùng bạn muốn tính tổng sau khi đã lọc dữ liệu.

2. Đếm các ô được lọc không trống

Chúng ta sẽ sử dụng SUBTOTAL 3 hoặc SUBTOTAL 103. Tuy nhiên trường hợp có hàng ẩn thì bạn phải sử dụng SUBTOTAL 103 để đếm chính xác các ô không trống nhìn thấy.

Chẳng hạn với bảng dữ liệu trên sẽ ẩn đi 2 hàng là 4 và 5. Khi dùng SUBTOTAL 3 hoặc SUBTOTAL 103 sẽ cho ra 2 kết quả khác nhau.

Chúng ta nhập công thức và khi đó Excel sẽ tự động hiển thị bộ chức năng để bạn chọn mà không cần phải nhớ.

Kết quả khi dùng SUBTOTAL 3 sẽ cho ra 3, tính cả ô đã ẩn đi trong hàng.

Còn với SUBTOTAL 103 sẽ chỉ hiển thị ô không trống mà chúng ta nhìn thấy, bỏ qua hàng ẩn.

3. Bỏ qua các giá trị trong các công thức Subtotal lồng nhau

Chẳng hạn chúng ta sẽ tính trung bình tổng số kg vải của kho A1 và A2.

Công thức tính trung bình cho kho A2= SUBTOTAL(1,C2:C4) và cho kết quả 19.

Công thức tính trung bình cho kho A1 = SUBTOTAL(1,C5:C7) có kết quả là 38.

Tuy nhiên khi tính trung bình tổng số vải ở 2 kho thì sẽ bỏ qua kết quả tính trung bình ở 2 kho. Chúng ta có công thức =SUBTOTAL(1,C2:C9) và kết quả sẽ tự động loại bỏ kết quả trung bình đã tính trước đó.

Những điều cần cân nhắc trước khi sử dụng hàm SUBTOTAL trong Excel

Mặc dù SUBTOTAL có những ưu điểm, nhưng cũng có một số điều bạn cần cân nhắc trước khi bắt đầu sử dụng hàm này, bao gồm những điểm sau:

• Như đã đề cập, sử dụng 1-11 cho đối số function_num sẽ đảm bảo rằng SUBTOTAL bao gồm các giá trị ẩn, trong khi 101-111 bỏ qua chúng.
• Sử dụng giá trị khác 1-11 hoặc 101-111 sẽ khiến Excel trả về lỗi #VALUE!. Điều này cũng sẽ xảy ra đối với tham chiếu ô 3D (trong đó cùng một ô, trên nhiều trang tính, được tham chiếu trong một phạm vi).
• Khi các phạm vi ô ngang như A1:D1 được sử dụng, các giá trị ẩn sẽ tự động được đưa vào (bất kể giá trị đối số function_num). Đây là một hạn chế của hàm SUBTOTAL và không thể khắc phục được.
• Sử dụng SUBTOTAL trên dữ liệu được lọc sẽ đảm bảo rằng các giá trị ẩn luôn bị bỏ qua, bất kể đối số function_num được sử dụng.
• SUBTOTAL có thể được sử dụng dưới dạng hàm lồng nhau như một phần của các hàm khác, bao gồm những công thức có chứa hàm IF.
• Nếu một công thức SUBTOTAL khác nằm trong phạm vi dữ liệu ref1, thì SUBTOTAL sẽ bỏ qua và loại trừ nó khỏi phép tính tổng thể.
• Excel cho phép sử dụng tối đa 254 phạm vi ô trong công thức SUBTOTAL, mặc dù chỉ cần 1 phạm vi ô để công thức hoạt động.

Cách lỗi khi thực hiện hàm SUBTOTAL trong Excel

Khi bạn tính toán hàm SUBTOTAL trong Excel, sẽ thường gặp một số lỗi cơ bản:

• #VALUE!: Số xác định chức năng không nằm trong khoảng 1-11 hoặc 101-111 hay có tham chiếu (ref) là tham chiếu 3D.
• #DIV/0!: Xảy ra khi 1 tổng cụ thể phải chia cho 0 (ví dụ: tính trung bình cộng hoặc độ lệch chuẩn của 1 dãy ô không chứa giá trị số).
• #NAME? tên hàm SUBTOTAL sai chính tả.

Sử dụng hàm SUBTOTAL có thể giúp bạn nhanh chóng phân tích tập dữ liệu mà không cần dựa vào các bảng tổng hợp phức tạp hơn. Nếu gặp khó khăn, bạn cũng có thể sử dụng tính năng Subtotal, được tìm thấy trong tab Data trên thanh ribbon, để tự động tạo công thức SUBTOTAL mà sau đó bạn có thể chỉnh sửa hoặc sao chép ở nơi khác.

Xem thêm:

• Hàm SUMPRODUCT trong Excel: Tính tổng tích các giá trị tương ứng
• Cách gộp 2 cột Họ và Tên trong Excel không mất nội dung