Cách sử dụng hàm AGGREGATE trong Excel -

Mục lục

Tóm lược

Hàm AGGREGATE trong Excel trả về một phép tính tổng hợp như AVERAGE, COUNT, MAX, v.v., tùy chọn bỏ qua các hàng và lỗi ẩn. Có tổng cộng 19 hoạt động, được chỉ định bởi số hàm trong đối số đầu tiên (xem bảng để biết các tùy chọn).

Mục đích

Trả lại tính toán tổng hợp

Giá trị trả lại

Phụ thuộc vào chức năng được chỉ định

Cú pháp

= AGGREGATE (function_num, options, ref1, ref2)

Tranh luận

function_num - Thao tác thực hiện (1-19).
tùy chọn - Giá trị cần bỏ qua (0-7).
ref1 - Đối số đầu tiên.
ref2 - Đối số thứ hai (k).

Phiên bản

Excel 2010

Ghi chú sử dụng

Hàm AGGREGATE trả về kết quả của một phép tính tổng hợp như AVERAGE, COUNT, MAX, MIN, v.v. Có tổng cộng 19 phép toán và hoạt động cần thực hiện được chỉ định dưới dạng số, xuất hiện dưới dạng đối số đầu tiên trong hàm. Đối số thứ hai, các tùy chọn, kiểm soát cách AGGREGATE xử lý lỗi và giá trị trong các hàng ẩn. Xem bảng bên dưới để biết tất cả các tùy chọn có sẵn.

Ví dụ 1

Để trả về giá trị MAX trong phạm vi A1: A10, bỏ qua cả lỗi và hàng ẩn, hãy cung cấp 4 cho số hàm và 7 cho các tùy chọn:

=AGGREGATE(4,7,A1:A10) // max value

Để trả về giá trị MIN với các tùy chọn tương tự, hãy thay đổi số hàm thành 5:

=AGGREGATE(5,7,A1:A10) // min value

Ví dụ số 2

Trong ví dụ được hiển thị ở trên, công thức trong D5 là:

=AGGREGATE(4,6,values)

trong đó "giá trị" là phạm vi được đặt tên B5: B14. Số của hàm là 4, chỉ định MAX. Các tùy chọn được cung cấp dưới dạng 6, chỉ để bỏ qua lỗi.

Ví dụ # 3 - lớn thứ n

Các công thức trong D8: D10 trình bày cách trả về giá trị "lớn nhất thứ n":

=AGGREGATE(14,6,values,1) // 1st largest =AGGREGATE(14,6,values,2) // 2nd largest =AGGREGATE(14,6,values,3) // 3rd largest

Số hàm ở đây là 14, hàm này chạy hàm LARGE. Vì hàm LARGE yêu cầu đối số ak nên nó xuất hiện dưới dạng đối số cuối cùng trong ba công thức trên.

Ví dụ # 4 - hoạt động mảng

Điều làm cho AGGREGATE đặc biệt hữu ích cho các công thức phức tạp hơn là nó có thể xử lý các mảng nguyên bản khi số của hàm là 14-19. Ví dụ: để tìm giá trị MAX vào các ngày Thứ Hai, với dữ liệu bao gồm ngày và giá trị, bạn có thể sử dụng AGGREGATE như sau:

=AGGREGATE(14,6,values/(TEXT(dates,"ddd")="Mon"),1)

Ở đây chúng tôi chỉ định 14 cho chức năng (LARGE) và 6 cho tùy chọn (bỏ qua lỗi). Sau đó, chúng tôi xây dựng một biểu thức logic bằng cách sử dụng hàm TEXT để kiểm tra tất cả các ngày cho các ngày Thứ Hai. Kết quả của phép toán này là một mảng các giá trị TRUE / FALSE, trở thành mẫu số của các giá trị ban đầu. FALSE đánh giá là 0 và ném # DIV / 0! lỗi. TRUE đánh giá là 1 và trả về giá trị ban đầu. Mảng giá trị và lỗi cuối cùng hoạt động giống như một bộ lọc. AGGREGATE bỏ qua tất cả các lỗi và trả về giá trị lớn nhất (tối đa) trong số các giá trị còn sót lại. Ví dụ đầy đủ hơn ở đây.

Số hàm

Bảng dưới đây liệt kê các số hàm có sẵn cho hàm AGGREGATE, cùng với tên của hàm được liên kết. Cột thứ ba, Ref2, cho biết giá trị đối số thứ hai được mong đợi khi được yêu cầu.

Chức năng	Chức năng	Ref2
1	TRUNG BÌNH CỘNG
2	ĐẾM
3	COUNTA
4	TỐI ĐA
5	MIN
6	SẢN PHẨM
7	STDEV.S
số 8	STDEV.P
9	TỔNG
10	VAR.S
11	VAR.P
12	TRUNG BÌNH
13	MODE.SNGL
14	LỚN	k
15	NHỎ	k
16	PERCENTILE.INC	k
17	QUARTILE.INC	quart
18	PERCENTILE.EXC	k
19	QUARTILE.EXC	quart

Tùy chọn hành vi

Hàm AGGREGATE có nhiều tùy chọn để bỏ qua lỗi, hàng ẩn và các hàm khác. Các giá trị có thể là 0-7, như được hiển thị trong bảng dưới đây.

Lựa chọn	Hành vi
0	Bỏ qua các hàm SUBTOTAL và AGGREGATE
1	Bỏ qua các hàng ẩn, hàm SUBTOTAL và AGGREGATE
2	Bỏ qua các giá trị lỗi, hàm SUBTOTAL và AGGREGATE
3	Bỏ qua các hàng ẩn, giá trị lỗi, hàm SUBTOTAL và AGGREGATE
4	Bỏ qua không có 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à giá trị lỗi

Ghi chú

AGGREGATE trả về lỗi #VALUE! lỗi nếu đối số hàm thứ hai là bắt buộc, nhưng không được cung cấp.
Tham chiếu 3D không hoạt động với AGGREGATE.
Hàm AGGREGATE được thiết kế theo dải dọc, không phải dải ngang.