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, 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. Để hiểu rõ hơn về cách sử dụng hàm Subtotal trong excel, các bạn cùng theo dõi bài viết của mình nhé
1/ Cú pháp của hàm Subtotal:
Cú pháp hàm SUBTOTAL:
SUBTOTAL(function_num, ref1, [ref2],…)
Trong đó:
Function_num: con số xác định chức năng thực hiện
Ref1, Ref2, …: 1 hoặc nhiều ô, hoặc dãy ô để tính tổng phụ. Cần phải có Ref 1, từ Ref 2 đến 254 là tuỳ chọn.
Số xác định chức năng thực hiện có 2 loại sau:
Tính năng được áp dụng nhiều nhất của hàm subtotal là tính Trung bình (Average: 1 hoặc 101) và tính Tổng (Sum: 9 hoặc 109).
Khi bạn dùng công thức SUBTOTAL với chức năng tính tổng như SUM hay AVERAGE, Excel sẽ chỉ tính các ô chứa số, bỏ qua ô trống và ô chứa giá trị không phải số.
Tại sao lại là Subtotal mà không phải dùng Sum hay Average. Chúng ta hãy cùng đi tiếp sang phần 2 – Lý do sử dụng hàm Subtotal trong excel.
2. Tại sao lại dùng hàm Subtotal – Các trường hợp sử dụng hàm Subtotal trong excel:
2.1 Tính giá trị trong danh sách được lọc
Vì hàm Excel SUBTOTAL bỏ qua các hàng đã được lọc ra, ta có thể sử dụng nó để tính tổng dữ liệu 1 cách linh hoạt, các giá trị trong tổng phụ được tự động tính lại theo bộ lọc.
Vd: Trong bảng tồn kho, ta điền công thức subtotal, số chức năng là 9, dùng để tính tổng số lượng sản phẩm của kho số 1.
Kết quả đầu ta được tổng toàn bộ sản phẩm trong kho (chưa lọc điều kiện)
Kết quả tiếp theo ta lọc phân loại các sản phẩm tốt, kết quả số lượng hàng trong kho 1 thay đổi, chỉ tính tổng các hàng sau khi đã lọc dữ liệu.
2.2 Tính toán các ô được nhìn thấy.
Bạn lưu ý rằng, khái niệm các ô được nhìn thấy sẽ không gồm 2 nhóm:
- Nhóm 1: Các ô bị ẩn đi do dùng chức năng lọc dữ liệu
- Nhóm 2: Các ô bị ẩn đi 1 cách thủ công
Hàm Subtotal với 2 bộ số chức năng:
- Từ 1 đến 9: tính toán và bỏ qua các ô đã được lọc ra (nhóm 1)
- Từ 101 đến 109: tính toán và bỏ qua các ô đã được lọc ra (nhóm 1) và bỏ qua các ô bị ẩn thủ công (nhóm 2)
2.3 Bỏ qua các giá trị khi hàm Subtotal lồng nhau
Trong trường hợp hàm subtotal lồng nhau, công thức SUBTOTAL được lồng vào sẽ bị bỏ qua. Vậy nên con số trong bảng sẽ không phải tính 2 lần. Ở ví dụ trong hình bên dưới, bạn sẽ thấy kết quả Tổng cộng đã bỏ qua giá trị ở Tổng kho 1 và Tổng kho 2
3. Các lỗi thường gặp khi dùng hàm Subtotal().
Nếu hàm SUBTOTAL trả về lỗi, thì nguyên nhân có thể là một trong những lý do sau đây:
#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ả.