Cách sử dụng hàm AGGREGATE trong Excel
Hàm AGGREGATE trong excel là phiên bản nâng cao của hàm SUBTOTAL trong Excel. Hàm AGGREGATE đã được giới thiệu trong Excel 2010.
Nó được sử dụng để thực hiện các hoạt động đơn giản trên tập dữ liệu, như SUM, AVERAGE, MAX, v.v. giống như SUBTOTAL.
Cú pháp của hàm AGGREGATE
=AGGREGATE(function num, options, array,[k]) (Array Form) =AGGREGATE(function num, options, ref1, ref2...) (Reference Form)
Có hai dạng Hàm AGGREGATE trong excel. Biểu mẫu mảng và Biểu mẫu tham chiếu. Khi chúng ta muốn cung cấp dữ liệu dưới dạng một phạm vi (ví dụ: A1: A3), chúng ta sử dụng dạng mảng. Khi chúng tôi cần cung cấp các tham chiếu khác nhau (ví dụ: A1, B3, C11, v.v.) so với chúng tôi sử dụng Biểu mẫu tham chiếu.
Hàm số: Từ số 1 đến số 19, mỗi số được kết hợp với một số phép toán. Chúng tôi cung cấp số lượng hàm mà chúng tôi muốn sử dụng trên phạm vi hoặc cơ sở dữ liệu. Đây là danh sách.
|
Function Num |
Function Name |
|
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: Kiểm soát mà tôi đã nói đến, tùy chọn này là kiểm soát đó. Nó cho phép bạn chọn, cách bạn muốn tính toán. Những gì bạn muốn xem xét trong khi tính toán và những gì không. Danh sách các tùy chọn có sẵn như dưới đây.
|
Num |
Options |
|
0 |
Ignore nested SUBTOTAL and AGGREGATE functions |
|
1 |
Ignore nested SUBTOTAL, AGGREGATE functions, and hidden rows |
|
2 |
Ignore nested SUBTOTAL, AGGREGATE functions, and error values |
|
3 |
Ignore nested SUBTOTAL, AGGREGATE functions, hidden rows & error values |
|
4 |
Ignore nothing |
|
5 |
Ignore hidden rows |
|
6 |
Ignore error values |
|
7 |
Ignore hidden rows and error values |
Array hoặc Ref: Đây là phạm vi mà bạn muốn thực hiện các thao tác.
Nó có thể là một cơ sở dữ liệu, một ô hoặc một loạt các ô không được liên kết.
[k]: Đây là một đối số tùy chọn. Nó phải được sử dụng với các chức năng yêu cầu phím. Như NHỎ, LỚN, v.v.
Hãy xem một ví dụ để làm rõ mọi thứ.
===
Ví dụ: Tính tổng phạm vi hiển thị, bỏ qua lỗi bằng cách sử dụng hàm AGGREGATE Ở đây tôi có một tập hợp nhỏ các số mà tôi muốn tính tổng. Bây giờ điều kiện là, tôi muốn tính tổng các giá trị chỉ hiển thị. Tôi muốn bỏ qua bất kỳ lỗi nào, công thức SUBTOTAL và AGGREGATE ở giữa phạm vi.
Viết công thức này trong ô B8.
=AGGREGATE(9,3,B2:B7)
Công thức AGGREGATE trên sẽ trả về câu trả lời đúng như chúng ta mong đợi.
Nếu bạn sử dụng hàm SUBTOTAL, nó sẽ xử lý các hàng ẩn nhưng nó sẽ không xử lý được lỗi.
Bạn cũng có thể nhận được giá trị NHỎ NHẤT hoặc LỚN NHẤT thứ n bằng cách sử dụng công thức AGGREGATE trong excel.
=AGGREGATE(14,3,B2:B7,2)
Công thức trên sẽ trả về giá trị lớn thứ hai trong phạm vi B2: B7.
Ở đây là 40.
=AGGREGATE(4,3,B7,B3)
Công thức AGGREGATE ở trên là kiểu tham chiếu và được sử dụng để nhận giá trị tối đa giữa B7 và B3.
Ghi chú:
-
Nếu một hàm không yêu cầu khóa [k] và, AGGREGATE sẽ trả về lỗi #VALUE.
-
Nếu một hàm yêu cầu khóa [k] và bạn không cung cấp khóa, AGGREGATE sẽ trả về lỗi #VALUE.
Bài viết liên quan:
Bài viết phổ biến
liên kết: / logic-công thức-nếu-hàm-với-ký tự đại diện [Nếu có ký tự đại diện]