Khi làm việc với bảng tính có dữ liệu lỗi, hàng ẩn hoặc đã lọc, các hàm thông thường đôi khi không cho kết quả như mong muốn. Hàm AGGREGATE là công cụ mạnh mẽ giúp tổng hợp dữ liệu linh hoạt và chính xác trong nhiều tình huống. Hãy cùng tìm hiểu cú pháp, cách sử dụng và những ví dụ thực tế để khai thác hiệu quả hàm này trong Excel.

Hàm AGGREGATE trong Excel là gì?

Khi làm việc với các bảng tính lớn, người dùng thường phải tính tổng, trung bình, giá trị lớn nhất hoặc nhỏ nhất trên tập dữ liệu có chứa lỗi, hàng ẩn hoặc đã áp dụng bộ lọc. Nếu sử dụng các hàm truyền thống như SUM, AVERAGE, MAX hay MIN, kết quả đôi khi không phản ánh đúng dữ liệu mong muốn. Đây là lúc hàm AGGREGATE phát huy ưu thế.

Hàm AGGREGATE trong Excel là một hàm tổng hợp được Microsoft giới thiệu từ Excel 2010. Điểm nổi bật của hàm là có thể thực hiện tới 19 phép tính khác nhau, đồng thời cho phép bỏ qua lỗi, hàng bị ẩn hoặc các kết quả từ hàm SUBTOTAL và AGGREGATE khác.

Thay vì phải kết hợp nhiều hàm như IFERROR, SUBTOTAL hay LARGE, người dùng chỉ cần sử dụng một công thức duy nhất để xử lý dữ liệu phức tạp. Chính vì vậy, hàm AGGREGATE được đánh giá là một trong những hàm mạnh mẽ và linh hoạt nhất trong Excel.

Ví dụ, một bảng doanh số có vài ô báo lỗi #DIV/0! hoặc #N/A. Nếu dùng hàm SUM thông thường, kết quả sẽ trả về lỗi. Tuy nhiên, với AGGREGATE, bạn có thể yêu cầu Excel tự động bỏ qua các lỗi này và vẫn tính tổng chính xác.

Cú pháp của hàm AGGREGATE trong Excel

Microsoft thiết kế AGGREGATE với hai dạng cú pháp nhằm phục vụ nhiều mục đích xử lý dữ liệu khác nhau.

Cú pháp AGGREGATE dạng tham chiếu

Đây là dạng được sử dụng phổ biến nhất.

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

Trong đó:

• function_num: Chỉ định phép tính cần thực hiện.
• options: Quy định cách bỏ qua lỗi, hàng ẩn hoặc hàm lồng.
• ref1: Vùng dữ liệu chính.
• ref2: Các vùng dữ liệu bổ sung (nếu có).

Dạng này thường dùng để tính tổng, trung bình, đếm, tìm giá trị lớn nhất hoặc nhỏ nhất trên nhiều vùng dữ liệu.

Cú pháp AGGREGATE dạng mảng

Khi cần tìm giá trị lớn thứ n hoặc nhỏ thứ n trong một mảng dữ liệu, bạn sử dụng cú pháp sau: =AGGREGATE(function_num, options, array, [k])

Trong đó:

• array là vùng dữ liệu cần xử lý.
• k là thứ hạng cần tìm, chẳng hạn giá trị lớn thứ 2 hoặc nhỏ thứ 5.

Ví dụ: =AGGREGATE(14,6,A2:A20,3)

Công thức trên trả về giá trị lớn thứ ba trong vùng A2:A20, đồng thời bỏ qua các ô lỗi.

Cách chọn phép tính và tùy chọn xử lý dữ liệu

Để hàm AGGREGATE hoạt động chính xác, người dùng cần hiểu ý nghĩa của hai tham số quan trọng là Function_num và Options.

Chọn phép tính tổng hợp phù hợp

AGGREGATE hỗ trợ tới 19 phép tính khác nhau. Một số giá trị phổ biến gồm:

Function_num	Phép tính
1	AVERAGE
2	COUNT
3	COUNTA
4	MAX
5	MIN
6	PRODUCT
9	SUM
12	MEDIAN
14	LARGE
15	SMALL

Ví dụ: =AGGREGATE(9,6,B2:B20)

Kết quả là tổng các giá trị trong vùng B2:B20 sau khi bỏ qua lỗi.

Nếu muốn tìm giá trị lớn nhất: =AGGREGATE(4,6,B2:B20)

Chọn tùy chọn bỏ qua lỗi hoặc hàng ẩn

Tham số Options quyết định cách Excel xử lý dữ liệu. Một số lựa chọn thường gặp:

Options	Ý nghĩa
0	Không bỏ qua dữ liệu
1	Bỏ qua hàng ẩn
2	Bỏ qua giá trị lỗi
3	Bỏ qua hàng ẩn và lỗi
4	Bỏ qua hàm lồng
5	Bỏ qua hàng ẩn và hàm lồng
6	Bỏ qua lỗi
7	Bỏ qua hàng ẩn, lỗi và hàm lồng

Excel sẽ tính tổng nhưng tự động bỏ qua:

• Hàng bị ẩn
• Giá trị lỗi
• Các hàm SUBTOTAL hoặc AGGREGATE lồng nhau

Đây là ưu điểm khiến hàm AGGREGATE vượt trội hơn nhiều hàm tổng hợp truyền thống.

Cách sử dụng hàm AGGREGATE từng bước

Nếu mới làm quen với AGGREGATE, bạn có thể thực hiện theo quy trình sau:

Bước 1: Chuẩn bị bảng dữ liệu cần tính toán.

Ví dụ gồm cột Doanh thu với một vài ô chứa lỗi hoặc hàng đã được lọc.

Bước 2: Xác định phép tính mong muốn.

Ví dụ:

• Tính tổng
• Trung bình
• Giá trị lớn nhất
• Giá trị nhỏ nhất

Bước 3: Chọn tham số Function_num tương ứng.

Ví dụ SUM là số 9.

Bước 4: Chọn Options.

Nếu muốn bỏ qua lỗi thì dùng số 6.

Bước 5: Chọn vùng dữ liệu.

Ví dụ: =AGGREGATE(9,6,D2:D100)

Bước 6: Nhấn Enter để nhận kết quả.

Nếu dữ liệu thay đổi, kết quả sẽ tự động cập nhật giống các hàm Excel khác.

Ví dụ dùng hàm AGGREGATE trong Excel

Ví dụ 1: Tính tổng và tự động loại trừ các ô lỗi

Trong trường hợp bảng tính có xuất hiện các giá trị lỗi, bạn vẫn có thể cộng tổng chính xác bằng cách sử dụng hàm AGGREGATE với tùy chọn 6, cho phép Excel bỏ qua những ô không hợp lệ trong quá trình tính toán.

Công thức: =AGGREGATE(9,6,B2:B12)

Kết quả: 560

Giá trị trả về là tổng của toàn bộ các ô hợp lệ trong vùng dữ liệu, không bị ảnh hưởng bởi các lỗi.

Giải thích công thức:

• 9: Chỉ định phép tính SUM.
• 6: Yêu cầu Excel bỏ qua mọi giá trị lỗi.
• B2: Phạm vi chứa dữ liệu cần tính tổng.

Ví dụ 2: Tính tổng khi không bỏ qua giá trị lỗi

Nếu không kích hoạt chế độ loại trừ lỗi mà sử dụng tùy chọn 4, chỉ cần một ô trong vùng dữ liệu xuất hiện lỗi thì kết quả của công thức cũng sẽ trả về lỗi.

Công thức: =AGGREGATE(9,4,B2:B12)

Kết quả: #DIV/0! (hoặc một mã lỗi tương ứng)

Điều này xảy ra do vùng dữ liệu có chứa các lỗi như #DIV/0!, #VALUE! hoặc #NUM!, trong khi tùy chọn 4 không loại bỏ các giá trị này khỏi phép tính.

Giải thích công thức:

• 9: Thực hiện phép cộng tổng (SUM).
• 4: Không bỏ qua các ô lỗi.
• B2: Phạm vi dữ liệu có chứa nhiều giá trị lỗi.

Ví dụ 3: Xác định giá trị lớn nhất và bỏ qua lỗi

Khi cần tìm số lớn nhất trong một danh sách nhưng dữ liệu có xen lẫn các ô lỗi, bạn có thể kết hợp hàm MAX trong AGGREGATE với tùy chọn 6 để chỉ xét những giá trị hợp lệ.

Công thức: =AGGREGATE(4,6,B2:B12)

Kết quả: 92

Excel sẽ tự động loại bỏ các ô lỗi và trả về giá trị lớn nhất trong những dữ liệu còn lại.

Giải thích công thức:

• 4: Đại diện cho hàm MAX.
• 6: Bỏ qua toàn bộ các giá trị lỗi.
• B2: Vùng dữ liệu cần tìm giá trị lớn nhất.

Ví dụ 4: Tính trung bình và loại trừ các ô lỗi

Trong nhiều trường hợp, việc tính trung bình sẽ không chính xác nếu phạm vi dữ liệu chứa lỗi. Sử dụng hàm AGGREGATE với tùy chọn 6 giúp Excel chỉ tính trên những giá trị hợp lệ.

Công thức: =AGGREGATE(1,6,B2:B12)

Kết quả: 70

Giá trị này được tính từ các số hợp lệ gồm: 85, 92, 60, 73, 40, 55, 65 và 90, trong khi các ô lỗi sẽ được tự động loại bỏ.

Giải thích công thức:

• 1: Đại diện cho phép tính AVERAGE.
• 6: Thiết lập chế độ bỏ qua các ô chứa lỗi.
• B2: Phạm vi dữ liệu dùng để tính giá trị trung bình.

Tổng hợp danh sách có giá trị lỗi

Trong thực tế, bảng dữ liệu thường phát sinh các lỗi như:

• #DIV/0!
• #VALUE!
• #N/A
• #NUM!

Các lỗi này có thể xuất hiện do nhập sai công thức hoặc thiếu dữ liệu.

Thông thường người dùng phải kết hợp:

• IFERROR
• ISERROR
• SUM

Tuy nhiên, chỉ với một công thức AGGREGATE: =AGGREGATE(9,6,E2:E100)

Excel sẽ tự động loại bỏ toàn bộ ô lỗi trước khi thực hiện phép tính.

Theo tài liệu Microsoft, đây là một trong những chức năng được sử dụng nhiều nhất khi xử lý dữ liệu doanh nghiệp vì giúp giảm đáng kể số lượng công thức phụ trợ.

Tính toán dữ liệu sau khi lọc hoặc có hàng ẩn

Đây là tình huống phổ biến trong kế toán, bán hàng và phân tích dữ liệu.

Ví dụ bạn lọc danh sách chỉ còn nhân viên thuộc chi nhánh Hà Nội.

Nếu dùng SUM thông thường, Excel vẫn cộng cả những hàng đang bị ẩn.

Trong khi đó: =AGGREGATE(1,5,C2:C100)

Excel chỉ tính các hàng đang hiển thị.

Điều này đặc biệt hữu ích khi lập báo cáo doanh số theo từng khu vực hoặc theo từng tháng mà không cần tạo bảng dữ liệu mới.

Xử lý bảng có các hàm tổng hợp lồng nhau

Một vấn đề khác khi làm việc với dữ liệu lớn là bảng tính đã chứa nhiều hàm SUBTOTAL hoặc AGGREGATE.

Nếu tiếp tục dùng SUM hoặc AVERAGE, rất dễ xảy ra tình trạng cộng lặp dữ liệu.

AGGREGATE giải quyết vấn đề này thông qua tùy chọn bỏ qua các hàm tổng hợp lồng nhau.

Ví dụ: =AGGREGATE(9,4,G2:G50)

Excel sẽ không cộng lại những kết quả được tạo bởi SUBTOTAL hoặc AGGREGATE trong vùng dữ liệu. Nhờ vậy, báo cáo cuối cùng luôn đảm bảo tính chính xác và hạn chế sai sót khi tổng hợp nhiều bảng.

Ứng dụng hàm AGGREGATE trong công việc văn phòng

Ngày nay, hàm AGGREGATE được sử dụng rộng rãi trong nhiều lĩnh vực như kế toán, tài chính, bán hàng, nhân sự và phân tích dữ liệu.

Đối với kế toán, hàm giúp tổng hợp doanh thu hoặc chi phí mà không bị ảnh hưởng bởi các ô lỗi phát sinh trong quá trình nhập liệu. Với nhân viên kinh doanh, AGGREGATE hỗ trợ tính doanh số theo từng nhóm dữ liệu đã lọc, từ đó lập báo cáo nhanh chóng và chính xác.

Trong lĩnh vực nhân sự, người quản lý có thể sử dụng hàm để thống kê lương, thưởng hoặc số ngày công ngay cả khi bảng dữ liệu chứa nhiều công thức khác nhau. Đối với chuyên viên phân tích dữ liệu, AGGREGATE giúp tìm giá trị lớn nhất, nhỏ nhất hoặc xếp hạng dữ liệu mà không cần viết công thức mảng phức tạp.

Bên cạnh đó, hàm còn kết hợp hiệu quả với FILTER, SORT, XLOOKUP, INDEX và MATCH để xây dựng các báo cáo động, dashboard quản trị hoặc hệ thống thống kê tự động trong Excel.

Kết luận

AGGREGATE là một trong những hàm mạnh mẽ nhất của Excel nhờ khả năng thực hiện nhiều phép tính tổng hợp và xử lý linh hoạt các tình huống như dữ liệu chứa lỗi, hàng bị ẩn hoặc các hàm lồng nhau. Khi nắm vững cú pháp và cách lựa chọn tham số phù hợp, bạn sẽ rút ngắn đáng kể thời gian xử lý bảng tính, đồng thời nâng cao độ chính xác cho báo cáo và phân tích dữ liệu.

Nếu thường xuyên học tập, làm việc với Excel hoặc các phần mềm văn phòng, một chiếc laptop có cấu hình ổn định sẽ giúp thao tác nhanh hơn và xử lý dữ liệu mượt mà. Hãy đến Viettel Store để lựa chọn laptop chính hãng từ các thương hiệu uy tín như Dell, HP, Lenovo, ASUS, Acer, MSI hay Apple cùng nhiều chương trình ưu đãi hấp dẫn, trả góp linh hoạt và chế độ bảo hành chính hãng, giúp bạn yên tâm đồng hành trong học tập và công việc.

Xem thêm:

• You can’t change part of an array Excel là gì? Cách sửa lỗi nhanh chóng và hiệu quả
• Các hàm tính lương thưởng trong Excel mà kế toán cần biết