Trả về tổng gộp trong một danh sách hoặc một cơ sở dữ liệu. Hàm AGGREGATE có thể áp dụng các hàm tổng gộp khác nhau cho một danh sách hoặc cơ sở dữ liệu với tùy chọn bỏ qua hoặc ẩn các hàng hoặc giá trị lỗi.
Cú pháp
Biểu mẫu tham chiếu
AGGREGATE(function_num, options, ref1, [ref2], …)
Biểu mẫu mảng
AGGREGATE(function_num, options, array, [k])
Cú pháp của hàm AGGREGATE có các đối số sau đây:
- Function_num Yêu cầu. Một số từ 1 đến 19 chỉ rõ hàm cần dùng.
| Function_num | Chức năng |
|---|---|
| 1 | AVERAGE |
| 2 | COUNT |
| 3 | COUNTA |
| 4 | MAX |
| 5 | MIN |
| 6 | PRODUCT |
| 7 | STDEV.S |
| 8 | STDEV.P |
| 9 | SUM |
| 10 | VAR.S |
| 11 | VAR.P |
| 12 | MEDIAN |
| 13 | MODE.SNGL |
| 14 | LARGE |
| 15 | SMALL |
| 16 | PERCENTILE.INC |
| 17 | QUARTILE.INC |
| 18 | PERCENTILE.EXC |
| 19 | QUARTILE.EXC |
Tùy chọn Yêu cầu. Một giá trị số xác định những giá trị nào cần bỏ qua trong phạm vi định trị của hàm.
Lưu ý
Hàm sẽ không bỏ qua các hàng ẩn, tổng phụ lồng nhau hoặc tổng hợp lồng nhau nếu đối số mảng bao gồm một phép tính, ví dụ: =AGGREGATE(14,3,A1:A100*(A1:A100>0),1)
| Tùy chọn | Hành vi |
|---|---|
| 0 hoặc bỏ qua | Bỏ qua các hàm lồng SUBTOTAL và AGGREGATE |
| 1 | Bỏ qua các hàng ẩn, hàm lồng SUBTOTAL và AGGREGATE |
| 2 | Bỏ qua các giá trị lỗi, hàm lồng SUBTOTAL và AGGREGATE |
| 3 | Bỏ qua các hàng ẩn, giá trị lỗi, hàm lồng SUBTOTAL và AGGREGATE |
| 4 | Không bỏ qua bất kỳ thứ gì |
| 5 | Bỏ qua các hàng ẩn |
| 6 | Bỏ qua các giá trị lỗi |
| 7 | Bỏ qua các hàng ẩn và các giá trị lỗi |
- Ref1 Yêu cầu. Đối số dạng số thứ nhất cho những hàm có nhiều đối số dạng số mà bạn muốn có giá trị tổng gộp.
-
Ref2,... Tùy chọn. Đối số dạng số 2 đến 253 mà bạn muốn có giá trị tổng gộp.
Đối với những hàm lấy một mảng, ref1 là một mảng, một công thức mảng hoặc một tham chiếu tới phạm vi ô mà bạn muốn có giá trị tổng gộp. Ref2 là đối số thứ hai cần thiết cho một số hàm nhất định. Các hàm sau đây đòi hỏi phải có đối số ref2:
| Chức năng |
|---|
| LARGE(array,k) |
| SMALL(array,k) |
| PERCENTILE.INC(array,k) |
| QUARTILE.INC(array,quart) |
| PERCENTILE.EXC(array,k) |
| QUARTILE.EXC(array,quart) |
Chú thích
Function_num:
- Ngay khi bạn gõ nhập đối số function_num lúc bạn nhập hàm AGGREGATE vào một ô trên trang tính, bạn sẽ thấy danh sách tất cả các hàm mà bạn có thể dùng làm đối số.
Lỗi:
- Nếu cần phải có đối số ref thứ hai nhưng đối số này không được cung cấp, thì hàm AGGREGATE trả về giá trị lỗi #VALUE! .
- Nếu một hoặc nhiều tham chiếu là tham chiếu 3-D, thì hàm AGGREGATE trả về giá trị lỗi #VALUE! .
Kiểu Phạm vi:
- Hàm AGGREGATE được thiết kế để dùng cho các cột dữ liệu hoặc phạm vi dọc. Nó không được thiết kế để dùng cho các hàng dữ liệu, hay các phạm vi ngang. Ví dụ, khi bạn cộng tổng phụ của một phạm vi ngang bằng cách dùng tùy chọn 1, chẳng hạn như AGGREGATE(1, 1, ref1), thì việc ẩn một cột sẽ không ảnh hưởng đến giá trị tổng cộng. Tuy nhiên, việc ẩn một hàng trong phạm vi dọc sẽ ảnh hưởng tới tổng gộp.
Ví dụ
Sao chép dữ liệu ví dụ trong bảng sau đây và dán vào ô A1 của một bảng tính Excel mới. Để các công thức hiển thị kết quả, hãy chọn chúng, nhấn F2 và sau đó nhấn Enter. Nếu cần, bạn có thể điều chỉnh độ rộng cột để xem tất cả dữ liệu.
| #DIV/0! | 82 | |
|---|---|---|
| 72 | 65 | |
| 30 | 95 | |
| #NUM! | 63 | |
| 31 | 53 | |
| 96 | 71 | |
| 32 | 55 | |
| 81 | 83 | |
| 33 | 100 | |
| 53 | 91 | |
| 34 | 89 | |
| Công thức | Mô tả | Kết quả |
| =AGGREGATE(4, 6, A1:A11) | Tính giá trị tối đa trong khi bỏ qua các giá trị lỗi trong phạm vi | 96 |
| =AGGREGATE(14, 6, A1:A11, 3) | Tính giá trị lớn thứ 3 trong khi bỏ qua các giá trị lỗi trong phạm vi | 72 |
| =AGGREGATE(15, 6, A1:A11) | Sẽ trả về lỗi #VALUE! . Đó là vì hàm AGGREGATE đang chờ đợi đối số tham chiếu thứ hai, vì hàm (SMALL) yêu cầu một đối số. | #VALUE! |
| =AGGREGATE(12, 6, A1:A11, B1:B11) | Tính giá trị trung bình trong khi bỏ qua các giá trị lỗi trong phạm vi | 68 |
| =MAX(A1:A2) | Sẽ trả về giá trị lỗi vì có các giá trị lỗi trong phạm vi đánh giá. | #DIV/0! |