Cách sử dụng hàm AGGREGATE trong Excel

Hàm AGGREGATE là một hàm khá phức tạp, tuy nhiên nó được coi là một trong những hàm Excel mạnh nhất vì nó có thể sử dụng thay thế cho nhiều hàm khác trong Excel. Trong bài viết này, Gitiho sẽ cùng bạn tìm hiểu về sức mạnh của hàm AGGREGATE trong Excel nhé!

Hàm AGGREGATE trong Excel

AGGREGATE là hàm gì?

Hàm AGGREGATE Excel kết hợp nhiều chức năng thành một, được thêm vào từ phiên bản Excel 2010. Cú pháp của hàm AGGREGATE gồm nhiều Options nên chúng ta có thể sử dụng hàm AGGREGATE để thay thế cho các hàm như: SUM, COUNT, MAX, MIN, AVERAGE, LARGE, SMALL,… Đây chính là sức mạnh vượt trội của hàm AGGREGATE trong Excel.

Cú pháp hàm AGGREGATE là gì?

Cú pháp hàm AGGREGATE dạng tham chiếu như sau:

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

Cú pháp hàm AGGREGATE dạng mảng:

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

Trong đó:

• function_num: (1-19) biểu thị hàm cần dùng tương ứng như sau:

function_num	Hàm	Miêu tả	Dạng
1	AVERAGE	Trung bình	Tham chiếu
2	COUNT	Đếm số ô chứa giá trị số	Tham chiếu
3	COUNTA	Đếm số ô chứa giá trị (khác rỗng)	Tham chiếu
4	MAX	Giá trị lớn nhất	Tham chiếu
5	MIN	Giá trị nhỏ nhất	Tham chiếu
6	PRODUCT	Nhân các giá trị với nhau	Tham chiếu
7	STDEV.S	Tính độ lệch chuẩn của mẫu	Tham chiếu
8	STDEV.P	Tính độ lệch chuẩn của tổng thể	Tham chiếu
9	SUM	Tính tổng các giá trị	Tham chiếu
10	VAR.S	Tính phương sai của mẫu	Tham chiếu
11	VAR.P	Tính phương sai của tổng thế	Tham chiếu
12	MEDIAN	Trung vị (giá trị ở giữa) của các giá trị	Tham chiếu
13	MODE.SNGL	Giá trị xuất hiện nhiều nhất	Tham chiếu
14	LARGE	Giá trị lớn thứ n	Mảng
15	SMALL	Giá trị nhỏ thứ n	Mảng
16	PERCENTILE.INC	Phân vị thứ k của các giá trị trong phạm vi	Mảng
17	QUARTILE.INC	Tứ phân vị của tập dữ liệu	Mảng
18	PERCENTILE.EXC	Phân vị thứ k của các giá trị trong phạm vi	Mảng
19	QUARTILE.EXC	Tứ phân vị của tập dữ liệu	Mảng

• options: (0-7) thể hiện điều kiện của hàm như sau:

options	Điều kiện
0 hoặc để trống	Bỏ qua những ô SUBTOTAL hoặc AGGREGATEA khác.
1	Bỏ qua các dòng bị ẩn, Bỏ qua những ô SUBTOTAL hoặc AGGREGATE khác.
2	Bỏ qua các giá trị lỗi, Bỏ qua những ô SUBTOTAL hoặc AGGREGATEA khác.
3	Bỏ qua các dòng bị ẩn, các giá trị lỗi và Bỏ qua những ô SUBTOTAL hoặc AGGREGATEA khác.
4	Bỏ qua các dòng trống
5	Bỏ qua các dòng bị ẩn
6	Bỏ qua giá trị lỗi
7	Bỏ qua các dòng bị ẩn và các giá trị lỗi

• ref1: Giá trị đầu tiên khi có nhiều tham số cần tính toán
• ref2, ref3,...ref253: Các giá trị cần tính toán tiếp theo
• array: Mảng cần xét
• k: Đối tượng tương ứng với mảng cần xét. 

Ví dụ: Hàm AGGREGATE đánh số thứ tự để tìm kiếm giá trị nhỏ nhất hoặc lớn nhất như sau:

Hàm LARGE: Là thứ tự n của giá trị lớn nhất cần tìm trong mảng
Hàm SMALL: Là thứ tự n của giá trị bé nhất cần tìm trong mảng

Tham khảo: Giới thiệu về hàm LARGE và hàm SMALL trong Excel

Lưu ý:

• Ngay sau khi bạn nhập tên hàm AGGREGATE trong Excel, danh sách function_num và options sẽ xuất hiện, bạn có thể căn cứ vào đó để lựa chọn con số tương ứng.

• Bỏ trống ref1 và ref2 khi cần dẫn đến lỗi #VALUE trong kết quả của hàm AGGREGATE. Các hàm như LARGE (array;k), SMALL (array;k), PERCENTILE.INC (array;k), QUARTILE.INC (array;quart), PERCENTILE.EXC (array;k), QUARTILE.EXC (array;quart) yêu cầu có đối số ref2.
• Hàm AGGREGATE tính toán theo chiều dọc. Ví dụ: Sử dụng function_num = 1 để tính tổng phụ của một phạm vi ngang với công thức: =AGGREGATE(1;1;ref1), kết quả không bị ảnh hưởng nếu bạn ẩn một cột. Tuy nhiên, nếu ẩn một hàng trong phạm vi dọc thì kết quả sẽ thay đổi.
• Hàm AGGREGATE không hoạt động trong tham chiếu 3D.

Ví dụ cách dùng hàm AGGREGATE

Sử dụng hàm AGGREGATE nâng cao để tính toán các giá trị sau:

- Tìm giá trị lớn nhất trong vùng B2:B10, bỏ qua giá trị lỗi: 

=AGGREGATE(4;6;B2:B10)

- Tìm giá trị lớn thứ 4 trong vùng B2:B10, bỏ qua giá trị lỗi: 

=AGGREGATE(14;6;B2:B10;4)

- Tìm tổng của vùng B2:B10, bỏ qua dòng bị ẩn: 

=AGGREGATE(9;7;B2:B10)

- Tìm trung vị của vùng B2:B10 và C2:C10 bỏ qua giá trị lỗi: 

=AGGREGATE(12;6;B2:B10;C2:C10)

Kết quả thu được như sau:

Kết luận

Trong bài viết trên, Gitiho đã cùng bạn tìm hiểu về hàm AGGREGATE trong Excel, một trong những hàm mạnh nhất và nhiều công dụng nhất trong Excel. Hy vọng bạn đã hiểu rõ và áp dụng thành công cho công việc của mình. Đừng quên theo dõi chúng mình để xem thêm các bài viết bổ ích khác nhé.