Khi tính toán và tổng hợp dữ liệu trên Excel, có đôi khi bạn sẽ gặp phải trường hợp một giá trị trong dãy dữ liệu bị lỗi khiến cho kết quả trả lại cũng bị lỗi. Hàm AGGREGATE là một hàm tổng hợp có khả năng giúp bạn giải quyết vấn đề này và còn kèm theo nhiều chức năng hữu ích khác nữa.
Hàm AGGREGATE là gì?
Hàm AGGREGATE (Aggregate: tổng hợp, gộp lại) là hàm tổng hợp các hàm khác trong Excel và trả về kết quả tổng gộp trong một cơ sở dữ liệu. Đặc biệt, hàm AGGREGATE còn cho người dùng có thêm tùy chọn bỏ qua các hàng hoặc giá trị bị ẩn hay bị lỗi.
Vì là hàm tổng hợp các hàm khác, nên hàm AGGREGATE có thể được dùng để thay thế cho các hàm khác như AVERAGE, SUM, COUNT, MAX, MIN,… Đây là lý do hàm AGGREGATE có thể hỗ trợ bạn rút ngắn thời gian khi tổng hợp dữ liệu trên Excel.
Cách sử dụng hàm AGGREGATE trong Excel
- Công thức hàm AGGREGATE
Đối với biểu mẫu tham chiếu:
=AGGREGATE(function_num, options, ref1, [ref2],…)
Đối với biểu mẫu mảng:
=AGGREGATE(function_num, options, array, [k])
Trong đó:
- Bảng function_num trong hàm AGGREGATE
| Function_num | Hàm |
| 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
| options | Điều kiện |
| 0 | Bỏ qua hàng có hàm SUBTOTAL và AGGREGATE |
| 1 | Bỏ qua hàng ẩn và hàm SUBTOTAL và AGGREGATE |
| 2 | Bỏ qua giá trị lỗi, hàm lồng SUBTOTAL và AGGREGATE |
| 3 | Bỏ qua hàng ẩn, giá trị lỗi, hàm lồng SUBTOTAL và AGGREGATE |
| 4 | Không bỏ qua bất kỳ thứ gì |
| 5 | Bỏ qua hàng ẩn |
| 6 | Bỏ qua giá trị lỗi |
| 7 | Bỏ qua hàng ẩn và giá trị lỗi |
Một số ví dụ của hàm AGGREGATE trong Excel
Để hiểu rõ hơn về cách hoạt động của hàm AGGREGATE, hãy cùng xem qua một vài ví dụ cụ thể nhé!
Ta có bảng dữ liệu trong Excel như sau: Vùng dữ liệu từ B4:B14, trong đó có dòng B7, B9, B12 là các giá trị lỗi.
Ví dụ 1: Tính tổng các giá trị trong vùng B4:B14, bỏ qua các giá trị lỗi.
Công thức:
=AGGREGATE(9,6,B4:B14)
=> Kết quả: 441.
Ví dụ 2: Tính tổng các giá trị trong vùng B4:B14, không bỏ qua các giá trị lỗi.
Công thức:
=AGGREGATE(9,4,B4:B14)
=> Kết quả: Do không bỏ qua giá trị lỗi nên kết quả trả về lỗi.
Ví dụ 3: Tính giá trị lớn nhất trong vùng B4:B14, bỏ qua các giá trị lỗi.
Công thức:
=AGGREGATE(4,6, B4:B14)
=> Kết quả: 102.
Ví dụ 4: Tính giá trị trung bình trong vùng B4:B14, bỏ qua các giá trị lỗi.
Công thức:
=AGGREGATE(1,6,B4:B14)
=> Kết quả: 55.125.
Lưu ý khi sử dụng hàm AGGREGATE
Bảng function_num sẽ hiển thị đầy đủ ngay sau khi bạn gõ AGGREGATE(.
Bảng options sẽ hiển thị đầy đủ sau khi bạn chọn function_num và thêm dấu “,”.
Nếu không có đủ đối số ref1 (hay thêm ref2 khi cần thiết), hàm AGGREGATE sẽ trả về kết quả là giá trị lỗi #VALUE!.
Hàm không hoạt động trong tham chiếu 3D.
Hàm AGGREGATE được xây dựng để tính toán theo phạm vi chiều dọc, không phải chiều ngang.
Kết luận
Hàm AGGREGATE là một trong những hàm mạnh nhất trong Excel vì có khả năng thay thế nhiều hàm khác kèm điều kiện loại bỏ các giá trị lỗi, giúp trả về kết quả nhanh chóng cho người dùng. Hy vọng thông qua bài viết này của UniTrain, bạn có thể nắm rõ hơn cách sử dụng hàm AGGREGATE để tăng năng suất làm việc với các dữ liệu trên Excel.
Xem thêm
Khóa học Combo Excel for Professionals