Cách dùng hàm SUBTOTAL trong Excel

Hàm tổng phụ trong các hàm Excel cơ bản được sử dụng trong nhiều trường hợp khác nhau. Nó giúp bạn tính tổng các tổng phụ trong danh sách hoặc cơ sở dữ liệu, không giống như hàm SUM trong Excel, ví dụ: B. Đếm ô, trung bình trong Excel, tìm giá trị lớn nhất / nhỏ nhất hoặc tính tổng giá trị danh sách đã lọc trong Excel mà người quản trị mạng đã hướng dẫn đọc Bài viết dưới đây sẽ hướng dẫn các bạn cách sử dụng hàm SUBTOTAL trong Excel.

Hàm SUBTOTAL trong Excel

Hàm SUBTOTAL có cú pháp = SUBTOTAL (function_num, ref1, [ref2], …). Trong vòng:

• Số hàm: Các số từ 1 đến 11 và 101 đến 111 cho biết hàm nào được sử dụng để tính toán trong SUBTOTAL.
• Ref1, Ref2,…: 1 hoặc nhiều ô hoặc phạm vi ô cho tổng phụ lên đến 254.

Ghi chú:

• Hàm SUBTOTAL được sử dụng để tính toán các cột dữ liệu dọc.
• Nếu các đối số ref1, ref2,… chứa hàm SUBTOTAL, chúng sẽ bị bỏ qua để tránh trùng lặp kép.
• Nếu function_num nằm trong khoảng từ 1 đến 11, hàm SUBTOTAL sẽ tính toán rằng các giá trị ẩn sẽ được đưa vào bản ghi vì hàng chứa phạm vi bị ẩn. Nếu function_num nằm trong khoảng từ 101 đến 111, SUBTOTAL chỉ tính toán các giá trị không bị ẩn trong tập dữ liệu (giá trị hàng ẩn bị loại trừ).
• SUBTOTAL bỏ qua dữ liệu bị ẩn bởi bộ lọc.

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

1. Tính tổng các dòng đã lọc

Để biết thông tin về loại tổng này, hãy xem bài viết Cách tính giá trị danh sách đã lọc trong Excel.

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

Chúng tôi sẽ sử dụng SUBTOTAL 3 hoặc SUBTOTAL 103. Tuy nhiên, nếu có một hàng ẩn, bạn phải sử dụng SUBTOTAL 103 để đếm các ô không trống.

Ví dụ, trong bảng dữ liệu trên, 2 hàng 4 và 5 bị ẩn. Sử dụng LÔ ĐỀ 3 hoặc CẦU ĐỀ 103 cho ra 2 kết quả khác nhau.

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

Kết quả khi sử dụng Tổng phụ 3 trả về 3 bao gồm các ô ẩn trong hàng.

Đối với TỔNG CỘNG 103 chỉ hiển thị các ô không trống mà chúng tôi nhìn thấy, bỏ qua hàng ẩn.

3. Bỏ qua các giá trị trong công thức tổng phụ lồng nhau

Ví dụ, chúng tôi tính tổng trọng lượng trung bình kg vải ở các kho A1 và A2.

công thức Trung bình cho kho A2 = TỔNG (1, C2: C4) và cho kết quả 19.

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

Tuy nhiên, khi tính tổng số chất trung bình trong 2 cửa hàng, kết quả trung bình trong 2 cửa hàng được bỏ qua. 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.

Lỗi khi thực thi hàm SUBTOTAL trong Excel

Có một số lỗi cơ bản khi tính hàm SUBTOTAL trong Excel:

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

Xem thêm: