AGGREGATE
AGGREGATE(mã_hàm, tuỳ_chọn_bỏ_qua, vùng, [k])Bản mạnh hơn của SUBTOTAL: ngoài việc bỏ qua dòng ẩn, nó còn BỎ QUA được cả ô LỖI nằm trong vùng, và làm được thêm những phép mà SUBTOTAL không có như lấy giá trị lớn thứ k, lấy trung vị, lấy phân vị. Nhờ vậy dòng tổng vẫn ra số ngay cả khi trong cột còn vài ô #N/A hoặc #DIV/0! chưa xử lý xong.
Các đối số
| Đối số | Bắt buộc? | Ý nghĩa |
|---|---|---|
| mã_hàm | Bắt buộc | Số từ 1 tới 19 chọn phép tính: 1 trung bình, 2 đếm số, 3 đếm ô không rỗng, 4 lớn nhất, 5 nhỏ nhất, 9 cộng, 12 trung vị, 14 lớn thứ k, 15 nhỏ thứ k, 16 phân vị. |
| tuỳ_chọn_bỏ_qua | Bắt buộc | Quyết định bỏ qua cái gì: 0 bỏ qua các SUBTOTAL và AGGREGATE lồng bên trong, 1 bỏ thêm dòng ẩn, 2 bỏ thêm ô lỗi, 3 bỏ cả dòng ẩn lẫn ô lỗi, 4 không bỏ gì, 5 chỉ bỏ dòng ẩn, 6 chỉ bỏ ô lỗi, 7 bỏ dòng ẩn và ô lỗi. Bảng vừa có Filter vừa còn ô lỗi thì chọn 7. |
| vùng | Bắt buộc | Vùng cần tính, nên là một cột dọc. |
| k | Tuỳ chọn | Thứ hạng cần lấy. Chỉ dùng với mã hàm 14 tới 19, nhưng ở đó thì BẮT BUỘC phải có, thiếu là #VALUE!. |
Ví dụ
=AGGREGATE(9, 6, D2:D500)Tổng cột thành tiền, bỏ qua các ô lỗi #N/A do dò giá chưa ra
Kết quả: 1.198.400.000
=AGGREGATE(14, 6, D2:D500, 3)Hoá đơn có giá trị lớn thứ 3 trong bảng, cột vẫn còn ô lỗi
Kết quả: 42.800.000
=AGGREGATE(9, 7, D2:D500)Tổng phần đang hiện sau khi lọc, đồng thời bỏ qua ô lỗi
Kết quả: 412.600.000
Lưu ý khi dùng
- Mã hàm từ 14 tới 19 nhận cả MẢNG ở đối số vùng, nên viết được kiểu lấy giá trị nhỏ nhất thoả điều kiện mà không cần công thức mảng Ctrl+Shift+Enter.
- Giống SUBTOTAL, hàm này chỉ xử lý dòng ẩn chứ không xử lý cột ẩn.
- Có từ Excel 2010. File phải mở trên bản cũ hơn thì dùng SUBTOTAL kèm IFERROR ở từng dòng.
Bẫy hay dính
Đặt AGGREGATE ở dòng tổng để dòng tổng khỏi bị lỗi lây: cột phía trên vẫn còn nguyên các ô #N/A nhưng con số tổng nhìn rất đẹp, nên không ai đi sửa và cuối kỳ báo cáo thiếu hẳn phần doanh thu của những dòng đó. Bỏ qua lỗi là GIẤU lỗi chứ không phải sửa lỗi. Phát hiện: đặt cạnh dòng tổng một ô COUNTIF đếm số ô lỗi trong cột, khác 0 thì phải xử lý trước khi phát hành báo cáo.
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)