Mình sẽ giới thiệu thêm cho các bạn một hàm rất hay nữa, đó là AGGREGATE. Đây cũng là một hàm bổ trợ trong các phiên bản Office rất tốt. Được ví là một trong 2 hàm mạnh nhất của Excel cũ. Cùng tìm hiểu nhé.
Link: Onedrive
Đối với biểu thức tham chiếu:
=AGGREGATE(function_num, options, ref1, [ref2], …)
- function_num từ 1 đến 19, được quy định trong bảng:
 |
| function_num của hàm AGGREGATE |
- options thiết lập điều kiện cho hàm, được quy định từ 0 tới 7:
 |
| options của hàm AGGREGATE |
- ref1: Tham số thứ nhất cho những hàm có nhiều đối số dạng số cần tính toán.
- ref2, ref3,… ref253: Tham số tùy chọn tiếp theo.
Đối với biểu thức mảng:
=AGGREGATE(function_num, options, array, [k])
- 2 đối số đầu tiên tương tự với biểu thức tham chiếu.
- array: mảng cần xét
- k: Đối số tương ứng với mảng. Cụ thể ta xem ví dụ dưới.
Note:
- Danh sách function_num sẽ được hiển thị khi bạn gõ AGGREGATE.
- Nếu không cung cấp đủ đối số ref1, ref2 khi cần thiết, công thức trả về giá trị lỗi #VALUE!
- AGGREGATE xây dựng và tính toán theo chiều dọc, không phải chiều ngang.
Ví dụ 1: Đánh số thứ tự tự động. Tự cập nhật lại khi Hide hoặc Filter.
 |
| Đánh STT bỏ qua dòng đang Hide và lọc Filter |
Ví dụ 2: Sắp xếp, tính tổng trong vùng dữ liệu lỗi bằng AGGREGATE.
 |
| AGGREGATE xử lý sắp xếp dữ liệu |
Công thức:
Sort nhỏ→lớn: =AGGREGATE(15,6,A2:A10,ROW(A2:A10)-1)
Sort nhỏ→lớn bỏ qua lỗi: =AGGREGATE(15,6,A2:A10,ROW(INDIRECT("1:"&COUNT(A2:A10))))
Sort lớn→nhỏ: =AGGREGATE(14,6,A2:A10,ROW(A2:A10)-1)
Sort lớn→nhỏ bỏ qua lỗi: =AGGREGATE(14,6,A2:A10,ROW(INDIRECT("1:"&COUNT(A2:A10))))
Tính tổng bỏ qua lỗi: =AGGREGATE(9,6,A2:A10)