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

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.

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à:

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:

- 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!