Hàm AGGREGATE trong Excel là gì? Cú pháp và ví dụ cách dùng hàm

Posted on 26/07/2025 by Quang Hùng Media

nội dung

1 Hàm AGGREGATE trong Excel là gì?
2 Cách sử dụng hàm AGGREGATE trong Excel
3 Ví dụ minh họa cách sử dụng hàm AGGREGATE trong Excel
4 Lưu ý khi sử dụng hàm AGGREGATE
5 Kết luận

AGGREGATE là một hàm mạnh mẽ trong Excel, cho phép thực hiện nhiều phép tính tổng hợp như trung bình, tổng, đếm, giá trị lớn nhất/nhỏ nhất… đồng thời có thể bỏ qua các lỗi hoặc hàng ẩn trong dữ liệu. Trong bài viết này, chúng ta sẽ tìm hiểu cú pháp của hàm này, các chức năng mà nó hỗ trợ, cũng như ví dụ minh họa cách sử dụng hàm này một cách hiệu quả.

Hàm AGGREGATE trong Excel là gì?

Đây là một công cụ hữu ích được thiết kế để thực hiện các phép tính tổng hợp một cách linh hoạt và chính xác. Không giống như các hàm đơn lẻ như SUM, AVERAGE, COUNT, MAX hay MIN, hàm tập hợp có khả năng tích hợp nhiều chức năng tính toán khác nhau chỉ trong một công thức duy nhất. Điều này giúp người dùng dễ dàng lựa chọn loại phép tính phù hợp với mục đích xử lý dữ liệu mà không cần sử dụng nhiều hàm riêng lẻ.

Một điểm nổi bật của hàm tổng hợp này là khả năng xử lý thông minh đối với các ô chứa lỗi hoặc bị ẩn. Với các tùy chọn bổ sung, bạn có thể yêu cầu hàm bỏ qua các hàng đã bị lọc, các giá trị lỗi hoặc thậm chí là các ô không hiển thị, điều mà nhiều hàm Excel thông thường không thể làm được. Nhờ vậy, hàm giúp đảm bảo kết quả tính toán luôn chính xác và phù hợp với dữ liệu thực tế.

Ngoài ra, vì hỗ trợ đến 19 chức năng thống kê và toán học khác nhau, hàm tập hợp đặc biệt hữu ích trong các bảng tính phức tạp, nơi yêu cầu xử lý khối lượng lớn dữ liệu có điều kiện. Hàm này không chỉ giúp giảm số lượng công thức cần dùng mà còn tăng tính hiệu quả trong quá trình phân tích dữ liệu.

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

Để sử dụng hàm tổng hợp trong Excel hiệu quả, bạn cần hiểu rõ cú pháp, các tham số và chức năng mà hàm hỗ trợ. Dưới đây là hướng dẫn chi tiết cách dùng hàm, kèm theo ví dụ minh họa giúp bạn áp dụng linh hoạt trong nhiều tình huống tính toán và xử lý dữ liệu.

Công thức hàm AGGREGATE

Hàm tích lũy trong Excel có hai dạng cú pháp tùy thuộc vào loại hàm bạn muốn sử dụng: dạng tham chiếu và dạng mảng. Mỗi dạng phục vụ mục đích riêng, phù hợp với từng kiểu dữ liệu và nhu cầu xử lý.

Với biểu mẫu tham chiếu, cú pháp được viết như sau:

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

Cách này áp dụng khi bạn làm việc với nhiều vùng dữ liệu cụ thể. Trong đó, function_num là số đại diện cho phép tính bạn muốn thực hiện như trung bình, tổng, đếm,… Options cho phép kiểm soát việc loại bỏ lỗi, hàng ẩn hoặc giá trị bị lọc. Ref1 là vùng dữ liệu đầu tiên, và bạn có thể thêm nhiều vùng khác nếu cần.

Đối với biểu mẫu mảng, cú pháp như sau:

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

Dạng này thường được sử dụng với các phép tính yêu cầu xử lý mảng hoặc tìm kiếm giá trị lớn/thứ hạng. Trong đó, array là mảng dữ liệu cần tính và k là tham số tùy chọn, thường dùng với các hàm cần thứ tự như LARGE, SMALL, PERCENTILE,…

Bảng function_num trong hàm AGGREGATE

Trong cú pháp của hàm này, tham số đầu tiên là function_num, dùng để xác định loại phép tính mà bạn muốn thực hiện. Mỗi giá trị số nguyên từ 1 đến 19 tương ứng với một hàm thống kê hoặc toán học cụ thể trong Excel. Đây là cách hàm gộp nhiều hàm đơn lẻ lại thành một công cụ đa năng duy nhất.

function_num	Hàm tương ứng
1	AVERAGE
2	COUNT
3	COUNTA
4	MAX
5	MIN
6	PRODUCT
7	STDEV.S
8	STDEV.P
9	SUM
10	VAR.S
11	VAR.P
12	MEDIAN
13	MODE.SNGL
14	LARGE
15	SMALL
16	PERCENTILE.INC
17	QUARTILE.INC
18	PERCENTILE.EXC
19	QUARTILE.EXC

Bảng options trong hàm AGGREGATE

Tham số options đóng vai trò thiết lập cách xử lý dữ liệu đặc biệt trong quá trình tính toán. Nhờ vào tùy chọn này, người dùng có thể dễ dàng loại bỏ dữ liệu không cần thiết, tránh sai lệch kết quả khi làm việc với bảng tính chứa các dòng bị ẩn, công thức lỗi hoặc các hàm phụ.

options	Điều kiện áp dụng
0	Bỏ qua hàng chứa hàm SUBTOTAL và AGGREGATE
1	Bỏ qua hàng bị ẩn, cùng với SUBTOTAL và AGGREGATE
2	Bỏ qua giá trị lỗi, đồng thời loại bỏ SUBTOTAL và AGGREGATE
3	Bỏ qua cả hàng ẩn, lỗi, cùng SUBTOTAL và AGGREGATE
4	Không loại bỏ bất cứ yếu tố nào
5	Bỏ qua các hàng bị ẩn
6	Bỏ qua các giá trị gây lỗi
7	Bỏ qua cả hàng ẩn và các giá trị lỗi

Ví dụ minh họa cách sử dụng hàm AGGREGATE trong Excel

Để hiểu rõ cách hoạt động của hàm trong thực tế, bạn cần xem qua một số ví dụ minh họa cụ thể. Dưới đây là các tình huống ứng dụng giúp bạn hình dung cách sử dụng hàm này để tính toán, xử lý dữ liệu có lỗi hoặc hàng bị ẩn trong Excel một cách hiệu quả và linh hoạt.

Ví dụ 1

Giả sử bạn có một danh sách số liệu từ ô B4 đến B14, trong đó một vài ô có thể chứa lỗi như #DIV/0! hoặc #VALUE!. Thay vì dùng SUM và bị gián đoạn kết quả, ta sẽ sử dụng hàm tổng hợp với function_num là 9 (đại diện cho hàm SUM), và options là 6, nghĩa là yêu cầu bỏ qua mọi giá trị lỗi trong vùng dữ liệu được chọn.

Công thức áp dụng như sau: =AGGREGATE(9,6,B4:B14)

Trong trường hợp này, công thức sẽ tự động loại bỏ những ô có lỗi, chỉ tính tổng các giá trị hợp lệ còn lại trong vùng B4:B14, và trả về kết quả chính xác là 441.

Điều này đặc biệt hữu ích khi bạn đang xử lý các bảng dữ liệu lớn, nơi khó kiểm soát hoàn toàn lỗi phát sinh. Hàm này không chỉ giúp bạn tránh bị gián đoạn do lỗi, mà còn duy trì hiệu quả và độ tin cậy trong quá trình tính toán trên Excel.

Ví dụ 2

Khi dùng hàm tổng hợp với function_num = 9 (tương ứng với phép tính tổng) và options = 4, bạn đã chọn không bỏ qua bất kỳ yếu tố nào, bao gồm cả lỗi xuất hiện trong phạm vi được chỉ định.

Công thức thực hiện: =AGGREGATE(9,4,B4:B14)

Do thiết lập options = 4 không yêu cầu bỏ qua lỗi, nên hàm sẽ cố gắng xử lý toàn bộ dữ liệu trong phạm vi, bao gồm cả các ô sai. Kết quả là công thức không thể hoàn thành phép tính tổng, và sẽ trả về lỗi tương tự như khi dùng hàm SUM trong trường hợp có giá trị sai trong vùng.

Kết luận từ ví dụ này cho thấy, khi xử lý bảng tính có thể phát sinh lỗi, lựa chọn giá trị phù hợp cho tham số options là rất quan trọng. Nếu không chủ động loại bỏ lỗi, kết quả trả về có thể không khả dụng, gây ảnh hưởng đến toàn bộ phép tính. Đây là lý do tại sao hàm tập hợp lại có lợi thế lớn trong việc kiểm soát dữ liệu phức tạp và không hoàn hảo.

Ví dụ 3

Ví dụ cụ thể, bạn có một vùng dữ liệu từ ô B4 đến B14, trong đó một số ô chứa lỗi. Thay vì dùng hàm MAX, bạn sẽ áp dụng hàm tổng hợp với function_num = 4 (đại diện cho phép tính giá trị lớn nhất) và options = 6 (thiết lập bỏ qua các giá trị lỗi).

Công thức như sau: =AGGREGATE(4,6,B4:B14)

Nhờ vào tùy chọn này, hàm sẽ tự động bỏ qua các ô có lỗi và chỉ xét các giá trị hợp lệ trong vùng dữ liệu. Kết quả trả về là 102, chính là số lớn nhất trong danh sách không bị ảnh hưởng bởi các ô sai.

Ví dụ này cho thấy hàm gộp không chỉ hữu ích khi cộng tổng hay đếm số liệu, mà còn rất hiệu quả trong các phép so sánh như tìm giá trị lớn nhất. Bằng cách linh hoạt lựa chọn loại phép tính và cách xử lý dữ liệu lỗi, hàm này sẽ giúp đảm bảo độ tin cậy và tính chính xác trong mọi tình huống phân tích dữ liệu trên Excel.

Ví dụ 4

Trong ví dụ này, vùng dữ liệu từ ô B4 đến B14 có thể có một số ô gặp lỗi hoặc dữ liệu không hợp lệ. Thay vì sử dụng hàm AVERAGE truyền thống, hàm tổng hợp với tham số function_num = 1 (đại diện cho phép tính trung bình) và options = 6 (bỏ qua các giá trị lỗi) sẽ giúp loại trừ những ô sai khỏi quá trình tính toán.

Công thức áp dụng như sau: =AGGREGATE(1,6,B4:B14)

Kết quả trả về là 55.125, đây là giá trị trung bình chính xác của các ô hợp lệ trong vùng B4:B14, không bị ảnh hưởng bởi những ô có lỗi. Nhờ đó, bạn có thể yên tâm rằng phép tính sẽ luôn phản ánh đúng thực tế dữ liệu mà không bị gián đoạn bởi lỗi.

Ví dụ này minh họa rõ ưu điểm nổi bật của hàm tập hợp trong việc xử lý các bảng tính phức tạp, giúp người dùng tiết kiệm thời gian và tránh các sai sót thường gặp khi làm việc với dữ liệu có vấn đề.

Lưu ý khi sử dụng hàm AGGREGATE

Đầu tiên, khi bắt đầu nhập công thức hàm, Excel sẽ tự động hiển thị bảng function_num, giúp bạn dễ dàng lựa chọn loại hàm tổng hợp phù hợp với nhu cầu. Tiếp theo, sau khi chọn một giá trị function_num và nhập dấu phẩy “,”, bảng options cũng sẽ xuất hiện để bạn thiết lập cách xử lý dữ liệu như bỏ qua lỗi hay hàng ẩn.

Một lỗi phổ biến thường gặp là khi không cung cấp đủ đối số bắt buộc như ref1 hoặc thêm các vùng dữ liệu như ref2 nếu cần thiết, hàm tổng hợp sẽ trả về lỗi #VALUE!. Vì vậy, hãy chắc chắn rằng bạn đã nhập đầy đủ các tham số cần thiết để công thức hoạt động chính xác.

Ngoài ra, hàm này không hỗ trợ tham chiếu 3D, nghĩa là không thể áp dụng cho vùng dữ liệu trải dài trên nhiều sheet khác nhau cùng lúc. Bạn cũng cần lưu ý rằng hàm tổng hợp được thiết kế để làm việc tốt nhất với dữ liệu theo chiều dọc (cột), không thích hợp khi áp dụng cho các vùng theo chiều ngang (hàng).

Kết luận

Việc nắm vững cú pháp, bảng function_num và options sẽ giúp bạn khai thác tối đa sức mạnh của hàm AGGREGATE trong các bài toán phân tích dữ liệu. Tóm lại, hàm tổng hợp không chỉ hỗ trợ xử lý dữ liệu chính xác hơn mà còn giúp đơn giản hóa các thao tác phức tạp, là một trong những hàm không thể thiếu đối với người làm việc chuyên sâu trên Excel. Hy vọng với những nội dung mà Hoàng Hà Mobile chia sẻ trên đây sẽ giúp bạn áp dụng công thức hàm Excel hiệu quả, cũng như xử lý data tối ưu hơn nhé.

Xem thêm:

Hàm SUM trong Excel: Cú pháp và cách dùng kèm ví dụ minh họa chi tiết
Hàm VALUE trong Excel: Cú pháp và cách sử dụng, kết hợp với MID, LEFT, RIGHT