Công thức và cách sử dụng hàm SUBTOTAL trong Excel – Sưu tầm

Khác với hàm SUM trong Excel, hàm Subtotal một trong những hàm cơ bản Excel được sử 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 nhữngh sử dụng hàm SUBTOTAL trong Excel.

Công thức SUBTOTAL trong Excel

Hàm SUBTOTAL sở hữu cú pháp =SUBTOTAL(function_num, ref1, [ref2],…). Trong đó:

• Function_num: Các con số từ 1 tới 11 và 101 tới 111 quy định hàm nào sẽ được sử 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 những cột số liệu theo chiều dọc.
• Nếu những đối số ref1, ref2,… sở hữu 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 tới 11 thì hàm SUBTOTAL tính toán bao gồm cả những giá trị ẩn trong tập số liệu do hàng chứa vùng đó bị ẩn. Nếu function_num từ 101 tới 111 thì hàm SUBTOTAL chỉ tính toán cho những giá trị ko ẩn trong tập số liệu (ko tính những 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 những 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 lúc đã lọc dữ liệu.

2. Đếm những ô được lọc ko trống

Chúng ta sẽ sử dụng SUBTOTAL 3 hoặc SUBTOTAL 103. Tuy nhiên trường hợp sở hữu hàng ẩn thì bạn phải sử dụng SUBTOTAL 103 để đếm chính xác những ô ko 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 sử 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à lúc đó Excel sẽ tự động hiển thị bộ chức năng để bạn tìm mà ko cần phải nhớ.

Kết quả lúc sử 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ị ô ko trống mà chúng ta nhìn thđấy, bỏ qua hàng ẩn.

3. Bỏ qua những giá trị trong những 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) sở hữu kết quả là 38.

Tuy nhiên lúc 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 sở hữu 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 đó.

Cách lỗi lúc 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 ko nằm trong khoảng 1-11 hoặc 101-111 hay sở hữu tham chiếu (ref) là tham chiếu 3D.
• #DIV/0!: Xảy ra lúc 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 ô ko chứa giá trị số).
• #NAME? tên hàm SUBTOTAL sai chính tả.

Xem thêm: