SUBTOTAL
SUBTOTAL(mã_hàm, vùng_1, [vùng_2], ...)Tính trên một vùng nhưng BỎ QUA những dòng đang bị Filter ẩn đi, nên con số ở dòng tổng luôn khớp với thứ đang hiện trên màn hình. Một mã số ở đối số đầu quyết định đang cộng, đếm, lấy trung bình hay lấy giá trị lớn nhất.
Các đối số
| Đối số | Bắt buộc? | Ý nghĩa |
|---|---|---|
| mã_hàm | Bắt buộc | Số quyết định phép tính. Nhóm 1 tới 11 chỉ bỏ dòng bị LỌC ẩn; nhóm 101 tới 111 bỏ cả dòng bị ẩn TAY. Hay dùng: 9 hoặc 109 là cộng, 3 hoặc 103 là đếm ô không rỗng, 2 hoặc 102 là đếm ô số, 1 hoặc 101 là trung bình, 4 hoặc 104 là lớn nhất, 5 hoặc 105 là nhỏ nhất. |
| vùng_1 | Bắt buộc | Vùng cần tính, nên là một cột dọc. |
| vùng_2 | Tuỳ chọn | Thêm vùng khác nếu cần. |
Ví dụ
=SUBTOTAL(9, D2:D500)Tổng cột thành tiền của phần đang hiện sau khi lọc chi nhánh
Kết quả: 412.600.000
=SUBTOTAL(103, B2:B500)Đếm số dòng đang hiện, tiện đặt ở đầu bảng làm ô kiểm tra
Kết quả: 128
=SUBTOTAL(101, E2:E500)Đơn giá bình quân của riêng nhóm hàng đang lọc
Kết quả: 185.400
Lưu ý khi dùng
- SUBTOTAL tự BỎ QUA các ô trong vùng vốn cũng là SUBTOTAL, nên bảng có nhiều dòng tổng phụ thì dòng tổng cuối không bị cộng trùng. Đây là lý do dòng tổng của bảng nhiều tầng nên dùng hàm này thay vì cộng tay.
- Bảng đã Ctrl+T thì bật Total Row là Excel tự đặt SUBTOTAL vào, không phải gõ.
- Chỉ có tác dụng với dòng ẩn. ẨN CỘT thì mọi mã số đều vẫn tính, kể cả nhóm 101 tới 111.
- Đặt dòng tổng ở TRÊN bảng hoặc chừa một dòng trắng giữa dòng tổng và vùng dữ liệu, nếu không Filter sẽ coi dòng tổng là một dòng dữ liệu.
Bẫy hay dính
Dùng mã 9 rồi ẩn tay vài dòng không muốn xem: những dòng đó VẪN được cộng vào tổng dù màn hình không thấy chúng đâu. Người đọc báo cáo cộng nhẩm các dòng đang hiện thì không ra bằng dòng tổng, và không ai nghĩ ra là do dòng ẩn. Phát hiện: đặt tạm một ô SUBTOTAL(109, cùng vùng) bên cạnh, hai số khác nhau là trong vùng có dòng bị ẩn tay.
Chọn cái nào
SUM hay SUBTOTAL hay AGGREGATE
SUM cộng hết, kể cả dòng đang bị Filter ẩn. SUBTOTAL bỏ qua dòng bị lọc ẩn. AGGREGATE bỏ qua được cả ô LỖI trong vùng. Bảng có lọc mà dòng tổng dùng SUM là con số không khớp với thứ đang hiện trên màn hình.
=SUBTOTAL(9, D2:D500)