seychellesartprojects.org

Hàm AGGREGATE trong Excel

Hàm AGGREGATE trong excel trả về tổng hợp của một bảng dữ liệu hoặc danh sách dữ liệu đã cho, hàm này cũng có đối số đầu tiên là số hàm và các đối số khác dành cho một phạm vi tập dữ liệu, số hàm cần được ghi nhớ để biết hàm nào được sử dụng .

Cú pháp

Có hai cú pháp cho Công thức AGGREGATE:

1. Cú pháp tham chiếu

= AGGREGATE (function_num, options, ref1, ref2, ref [3],…)

2. Cú pháp mảng

= AGGREGATE (function_num, options, array, [k])

Function_num là một số biểu thị một hàm cụ thể mà chúng ta muốn sử dụng, nó là một số từ 1-19

Tùy chọn: nó cũng là một giá trị số nằm trong khoảng từ 0 đến 7 và xác định những giá trị nào sẽ bị bỏ qua trong khi tính toán

Ref1, ref2, ref [3]:  là đối số trong khi sử dụng cú pháp tham chiếu, nó là giá trị số hoặc các giá trị mà chúng ta muốn thực hiện tính toán, cần có ít nhất hai đối số còn lại là tùy chọn.

Mảng: là một mảng các giá trị mà chúng ta muốn thực hiện thao tác, nó được sử dụng trong cú pháp mảng của hàm AGGREGATE trong excel

K: là một đối số tùy chọn và là một giá trị số, nó được sử dụng khi sử dụng hàm như LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC hoặc QUARTILE.EXC trong Excel.

Các ví dụ

Bạn có thể tải xuống Mẫu Excel Hàm AGGREGATE này tại đây - Mẫu Excel Hàm AGGREGATE

Ví dụ 1

Giả sử chúng ta có một danh sách các số và chúng ta sẽ tính Trung bình, Đếm là số ô chứa một giá trị, Đếm-số ô không trống, Tối đa, Tối thiểu, tích và tổng của các giá trị số đã cho. Các giá trị được đưa ra dưới đây trong bảng:

Trước tiên, chúng ta hãy tính Trung bình trong Hàng 9, cho tất cả các giá trị đã cho. Đối với trung bình, hàm_ num là

Trong Cột C, tất cả các giá trị được đưa ra và chúng tôi sẽ không phải bỏ qua bất kỳ giá trị nào, vì vậy chúng tôi sẽ chọn Tùy chọn 4 (không bỏ qua gì)

Và chọn phạm vi giá trị C1: C8 dưới dạng một mảng các giá trị số

Vì ' k' là một đối số tùy chọn và được sử dụng khi một hàm như LARGE, SMALL trong Excel, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC hoặc QUARTILE.EXC được sử dụng nhưng trong trường hợp này, chúng tôi đang tính Trung bình nên chúng tôi sẽ bỏ qua giá trị của k.

Vì vậy, giá trị trung bình là

Tương tự, đối với phạm vi D1: D8, một lần nữa chúng ta sẽ chọn tùy chọn 4.

Đối với phạm vi E1: E8, ô E6 chứa giá trị lỗi, nếu chúng ta sử dụng cùng một công thức AGGREGATE, chúng ta sẽ gặp lỗi, nhưng khi một tùy chọn thích hợp được sử dụng, AGGREGATE trong Excel sẽ cung cấp giá trị trung bình của các giá trị còn lại bỏ qua lỗi. giá trị trong E6.

Để bỏ qua các giá trị lỗi, chúng tôi có tùy chọn 6.

Tương tự, đối với phạm vi G1: G8, chúng tôi sẽ sử dụng tùy chọn 6 (bỏ qua các giá trị lỗi)

Bây giờ, đối với phạm vi H3 nếu chúng ta đặt giá trị 64 và ẩn hàng thứ ba và sử dụng tùy chọn 5, để bỏ qua hàng ẩn, AGGREGATE trong Excel, chúng ta sẽ cung cấp giá trị trung bình chỉ cho các giá trị số hiển thị.

Đầu ra không ẩn Hàng 3

Đầu ra sau khi ẩn hàng 3

Áp dụng công thức AGGREGATE cho các phép toán khác, chúng ta có

Ví dụ - # 2

Giả sử chúng ta có một bảng cho doanh thu được tạo vào các ngày khác nhau từ các kênh khác nhau như được cung cấp bên dưới

Bây giờ, chúng tôi muốn kiểm tra doanh thu được tạo ra cho các kênh khác nhau. Vì vậy, khi chúng ta áp dụng hàm sum, chúng ta sẽ nhận được tổng doanh thu được tạo ra nhưng trong trường hợp nếu chúng ta muốn kiểm tra doanh thu được tạo cho kênh Không phải trả tiền hoặc kênh trực tiếp hoặc bất kỳ phương thức nào khác, khi chúng ta áp dụng các bộ lọc trong excel cho cùng một hàm, hàm sum sẽ luôn đưa ra tổng số tiền

Chúng tôi muốn rằng khi chúng tôi lọc kênh, chúng tôi nhận được tổng các giá trị hiển thị, vì vậy thay vì sử dụng hàm SUM, chúng tôi sẽ sử dụng hàm AGGREGATE để nhận tổng các giá trị hiển thị khi bộ lọc đã áp dụng.

Vì vậy, thay thế công thức SUM bằng một hàm AGGREGATE với mã tùy chọn 5 (bỏ qua các hàng và giá trị ẩn), chúng ta có,

Bây giờ, khi chúng tôi áp dụng bộ lọc cho các kênh khác nhau, bộ lọc sẽ chỉ hiển thị doanh thu cho kênh đó khi các hàng còn lại bị ẩn.

Tổng doanh thu được tạo cho kênh trực tiếp:

Tổng doanh thu được tạo cho kênh không phải trả tiền:

Tổng doanh thu được tạo cho kênh trả phí:

Vì vậy, chúng ta có thể thấy hàm AGGREGATE tính toán các giá trị Tổng khác nhau cho doanh thu được tạo cho các kênh khác nhau sau khi chúng được lọc. Vì vậy, hàm AGGREGATE có thể được sử dụng động để thay thế các hàm khác nhau cho các điều kiện khác nhau mà không cần sử dụng công thức điều kiện.

Giả sử đối với cùng một kênh bảng và doanh thu, một số giá trị doanh thu của chúng ta có lỗi, bây giờ chúng ta cần bỏ qua lỗi và đồng thời, nếu chúng ta muốn áp dụng bộ lọc, hàm AGGREGATE cũng nên bỏ qua các giá trị hàng ẩn.

Khi chúng tôi sử dụng tùy chọn 5, chúng tôi nhận được lỗi cho SUM của tổng doanh thu, bây giờ để bỏ qua các lỗi, chúng tôi phải sử dụng tùy chọn 6

Sử dụng tùy chọn 6, chúng tôi nhận được tổng bỏ qua các giá trị lỗi, nhưng khi chúng tôi áp dụng bộ lọc, ví dụ, lọc theo giá trị kênh Trực tiếp, chúng tôi nhận được cùng một tổng bỏ qua các lỗi nhưng đồng thời chúng tôi cũng phải bỏ qua các giá trị ẩn.

Vì vậy, trong trường hợp này, chúng tôi sẽ sử dụng tùy chọn 7 bỏ qua các giá trị lỗi và đồng thời các hàng ẩn

Những điều cần ghi nhớ

• Hàm AGGREGATE không nhận dạng hàm _ giá trị num lớn hơn 19 hoặc nhỏ hơn 1 và tương tự đối với Option number, hàm này không nhận ra các giá trị lớn hơn 7 và nhỏ hơn 1, nếu chúng tôi cung cấp bất kỳ giá trị nào khác, nó sẽ cho lỗi #VALUE ! lỗi
• Nó luôn chấp nhận giá trị số và luôn trả về giá trị số dưới dạng đầu ra
• AGGREGATE trong Excel có một giới hạn; nó chỉ bỏ qua các hàng bị ẩn nhưng không bỏ qua các cột bị ẩn.