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 ODDLPRICE – Hàm trả về giá trên mỗi mệnh giá 100 USD của chứng khoán với chu kỳ cuối cùng là chu kỳ lẻ 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 IMCSC – Hàm trả về cosec của một số phức với định dạng văn bản 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!
