Bài viết dưới đây giới thiệu chi tiết ý nghĩa và cách sử dụng hàm AGGREGATE – 1 trong những hàm nằm trong nhóm hàm lượng giác và toán học hay được ưa dùng hiện nay.
Bạn đang đọc: Hàm AGGREGATE – Hàm trả về một giá trị tổng hợp trong một danh sách hoặc 1 cơ sở dữ liệu trong Excel
Mô tả: Hàm thực hiện trả về 1 giá trị tổng gộp trong một danh sách hoặc 1 cơ sở dữ liệu, hàm có thể áp dụng cho các hàm tổng gộp khác nhau cho 1 danh sách hoặc 1 cơ sở dữ liệu với tùy chọn bỏ qua hoặc bỏ qua các hàng và giá trị lỗi.
Cú pháp: AGGREGATE(function_num, options, ref1, [ref2], …).
Trong đó:
– function_num: Là giá trị số từ 1 đến 19 tương ứng với tên hàm cần dùng:
+ function_num = 1: Tương ứng với hàm AVERAGE.
+ function_num = 2: Tương ứng với hàm COUNT.
+ function_num = 3: Tương ứng với hàm COUNTA.
+ function_num = 4: Tương ứng với hàm MAX.
+ function_num = 5: Tương ứng với hàm MIN.
+ function_num = 6: Tương ứng với hàm PRODUCT.
+ function_num = 7: Tương ứng với hàm STDEV.S.
+ function_num = 8: Tương ứng với hàm STDEV.P.
+ function_num = 9: Tương ứng với hàm SUM.
+ function_num = 10: Tương ứng với hàm VAR.S.
+ function_num = 11: Tương ứng với hàm VAR.P.
+ function_num = 12: Tương ứng với hàm MEDIAN.
+ function_num = 13: Tương ứng với hàm MODE.SNGL.
+ function_num = 14: Tương ứng với hàm LARGE.
+ function_num = 15: Tương ứng với hàm SMALL.
+ function_num = 16: Tương ứng với hàm PERCENTILE.INC.
+ function_num = 17: Tương ứng với hàm QUARTILE.INC.
+ function_num = 18: Tương ứng với hàm PERCENTILE.EXC.
+ function_num = 19: Tương ứng với hàm QUARTILE.EXC.
– options: Giá trị số xác định những giá trị nào cần bỏ qua trong phạm vi của hàm, là tham số bắt buộc có các giá trị sau:
+ options = 0 hoặc bỏ qua: Bỏ qua các hàm lồng Subtotal và Aggregate.
+ options = 1: Bỏ qua hàng ẩn, hàm lồng Subtotal và Aggregate.
+ options = 2: Bỏ qua các giá trị lỗi, hàm lồng Subtotal và Aggregate.
+ options = 3: Bỏ qua các hàng ẩn, giá trị lỗi, hàm lồng Subtotal và Aggregate.
+ options = 4: Không bỏ qua bất kỳ giá trị nào.
+ options = 5: Bỏ qua các hàng ẩn.
+ options = 6: Bỏ qua các giá trị lỗi.
+ options = 7: Bỏ qua các hàng ẩn và các giá trị lỗi.
– ref1: Đối số dạng thứ nhất cho những hàm có nhiều đối số muốn có giá trị tổng gộp, là tham số bắt buộc.
– ref2: Đối số dạng số 2 đến 253 muốn có giá trị tổng gộp, là tham số tùy ý. Đối với những hàm lấy 1 mảng, ref1 là một mảng hoặc 1 công thức mảng hay một tham chiếu, ref2 là đối số thứ 2 cần thiết cho một số hàm nhất định. Với những hàm sau bắt buộc phải có đối số ref2:
1. SMALL(array,k).
2. LARGE(array,k).
3. PERCENTILE.INC(array,k).
4. QUARTILE.INC(array,quart).
5. PERCENTILE.EXC(array,k).
6. QUARTILE.EXC(array,quart).
Chú ý:
– Ngay sau khi bạn gõ tên hàm AGGREGATE 1 danh sách các hàm được hiển thị cho phép bạn lựa chọn.
– Nếu bắt buộc có tham số thứ 2 ref2 nhưng đối số này không được cung cấp -> hàm trả về giá trị lỗi #VALUE!
– Nếu chứa 1 hoặc nhiều tham chiếu là tham chiếu 3 – D -> hàm trả về giá trị lỗi #VALUE!
– Hàm AGGREGATE được thiết kế để dùng cho các cột dữ liệu hoặc phạm vi dọc vì vậy việc ẩn 1 cột không ảnh hưởng tới giá trị hàm. Tuy nhiên nếu ẩn 1 hàng trong phạm vi dọc sẽ ảnh hưởng tới giá trị hàm.
Ví dụ:
Tìm các giá trị theo mô tả sau, sử dụng hàm AGGREGATE.
1. Tìm giá trị lớn nhất trong dãy, bỏ qua giá trị lỗi.
– Tại ô cần tính nhập công thức: =AGGREGATE(4,6,C6:D10).
Mô tả công thức: Ở đây hàm AGGREGATE sử dụng hàm thứ 4 là hàm MAX – tìm giá trị lớn nhất bỏ qua giá trị lỗi (sử dụng option =6) tìm kiếm trong vùng C6:D10.
– Kết quả giá trị lớn nhất trong vùng C6:D10 là:
2. Tính giá trị trung bình của các giá trị trong vùng C6:D10, bỏ qua giá trị lỗi.
– Tại ô cần tính nhập công thức: =AGGREGATE(12,6,C6:D10).
Mô tả công thức: Ở đây hàm AGGREGATE sử dụng hàm thứ 12 là hàm MEDIAN – tìm trung bình của các giá trị bỏ qua giá trị lỗi (sử dụng option =6) tìm kiếm trong vùng C6:D10.
– Kết quả trung bình các giá trị trong vùng C6:D10 là:
Tìm hiểu thêm: Hàm MDURATION – Trả về thời hạn Macauley có điều chỉnh của một chứng khoán với mệnh giá giả định $100 trong Excel
3. Tính tổng các giá trị trong vùng C6:D10, bỏ qua giá trị lỗi.
– Tại ô cần tính nhập công thức: =AGGREGATE(9,6,C6:D10).
Mô tả công thức: Ở đây hàm AGGREGATE sử dụng hàm thứ 9 là hàm SUM – tính tổng các giá trị bỏ qua giá trị lỗi (sử dụng option =6) tìm kiếm trong vùng C6:D10.
– Kết quả tổng các giá trị trong vùng C6:D10 là:
4. Tìm số hạng nhỏ thứ 3 trong vùng C6:D10, bỏ qua giá trị lỗi.
– Tại ô cần tính nhập công thức: =AGGREGATE(15,6,C6:D10,3).
Mô tả công thức: Ở đây hàm AGGREGATE sử dụng hàm thứ 15 là hàm SMALL – tìm giá trị nhỏ thứ 3 bỏ qua giá trị lỗi (sử dụng option = 6) tìm kiếm trong vùng C6:D10.
– Kết quả giá trị nhỏ thứ 3 trong vùng C6:D10 là:
4. Trường hợp hàm báo lỗi khi không khai báo đủ tham số ref2.
– Nếu trong trường hợp tìm số hạng nhỏ thứ 3 trong dãy bạn bỏ qua đối số thứ 2 (ref2) là giá trị nhỏ thứ 3 -> hàm trả về giá trị lỗi #VALUE!
– Tại ô cần tính nhập công thức: =AGGREGATE(15,6,C6:D10).
Mô tả công thức: Mô tả công thức: Ở đây hàm AGGREGATE sử dụng hàm thứ 15 là hàm SMALL – hàm yêu cầu bắt buộc tham số ref2 (sử dụng option = 6) tìm kiếm trong vùng C6:D10.
– Kết quả hàm trả về giá trị lỗi:
>>>>>Xem thêm: Hàm COUPDAYSNC – Hàm trả về số ngày tính từ ngày kết toán tới ngày phiếu lãi tiếp theo trong Excel
– Do hàm SMALL là 1 trong 6 hàm yêu cầu bắt buộc đối số ref2 như vậy khi bạn không nhập đối số ref2 hàm AGGREGATE trả về giá trị lỗi #VALUE.
Trên đây là cách sử dụng và một số chú ý khi làm việc với hàm AGGREGATE.
Chúc các bạn thành công!
