Hàm AGGREGATE — 19 Phép Tính, Bỏ Qua Lỗi & Ẩn

AGGREGATE là phiên bản 'siêu cấp' của SUBTOTAL — hỗ trợ 19 phép tính (thay vì 11), 7 tùy chọn bỏ qua lỗi/ẩn, và có thể hoạt động với mảng. Đây là hàm mạnh nhất Excel để tính toán trên dữ liệu 'bẩn' mà không cần dọn dẹp trước.

AGGREGATE Là Gì? Tại Sao Cần Nó? #

Khi dữ liệu chứa lỗi #DIV/0!, #VALUE!, #N/A — hầu hết hàm thống kê đều trả về lỗi. SUM, AVERAGE, COUNT đều 'chết' nếu gặp 1 ô lỗi. SUBTOTAL giải quyết phần nào nhưng chỉ có 11 function. AGGREGATE nâng lên 19 function và thêm khả năng bỏ qua lỗi, hàng ẩn, hàng SUBTOTAL/AGGREGATE lồng nhau.

Cú Pháp AGGREGATE #

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

// --- HOẶC dạng ARRAY (cho function 14-19): ---
=AGGREGATE(function_num, options, array, [k])

function_num  — Số từ 1-19 chỉ định phép tính
options       — Số từ 0-7 chỉ định loại giá trị bỏ qua
ref1          — Vùng dữ liệu
k             — Tham số thứ k (cho LARGE, SMALL, PERCENTILE...)

19 Phép Tính (function_num) #

7 Options — Bỏ Qua Cái Gì? #

Mẹo nhớ: Option 6 (bỏ lỗi) và option 7 (bỏ ẩn + lỗi) là 2 option được dùng nhiều nhất. Nếu không chắc, dùng option 6.

Ví Dụ 1: SUM Bỏ Qua Lỗi #

// Dữ liệu cột B: 100, 200, #DIV/0!, 300, #N/A, 150

=SUM(B2:B7)
// → #DIV/0! (chết vì lỗi!)

=AGGREGATE(9, 6, B2:B7)
// → 750 (9=SUM, 6=bỏ qua lỗi)
// Tính 100+200+300+150 = 750, skip 2 ô lỗi

// So sánh SUBTOTAL: không thể bỏ qua lỗi!
=SUBTOTAL(9, B2:B7)
// → #DIV/0! (cũng chết)

Ví Dụ 2: AVERAGE Hàng Đã Filter #

// Bảng doanh số đã filter (ẩn một số hàng)
// Chỉ tính trung bình những hàng HIỆN trên màn hình:

=AGGREGATE(1, 5, C2:C100)
// 1 = AVERAGE, 5 = bỏ qua hàng ẩn
// Kết quả thay đổi khi bạn filter khác!

// Bỏ qua cả lỗi VÀ hàng ẩn:
=AGGREGATE(1, 7, C2:C100)
// 1 = AVERAGE, 7 = bỏ cả ẩn + lỗi
// An toàn nhất cho dữ liệu thực tế

Ví Dụ 3: LARGE / SMALL Bỏ Qua Lỗi #

// Tìm giá trị lớn thứ 3, bỏ qua lỗi:
=AGGREGATE(14, 6, B2:B100, 3)
// 14 = LARGE, 6 = bỏ lỗi, k = 3

// Top 3 giá trị nhỏ nhất:
=AGGREGATE(15, 6, B2:B100, 1)  // nhỏ nhất
=AGGREGATE(15, 6, B2:B100, 2)  // nhỏ thứ 2
=AGGREGATE(15, 6, B2:B100, 3)  // nhỏ thứ 3

// Với LARGE/SMALL thường: nếu có lỗi → chết
// AGGREGATE giải quyết gọn!

AGGREGATE vs SUBTOTAL #

Ứng Dụng Nâng Cao #

Conditional LARGE (top 3 theo điều kiện) #

// Top 3 doanh số của phòng "Kinh Doanh" (bỏ lỗi):
=AGGREGATE(14, 6, 
  (A2:A100="Kinh Doanh") * C2:C100 + 
  (A2:A100<>"Kinh Doanh") * (-9^99),
  {1;2;3}
)
// Dùng mảng {1;2;3} → spill top 3 giá trị
// Trick: nhân điều kiện FALSE với -9^99 để loại bỏ

MEDIAN bỏ qua lỗi (không hàm nào khác làm được) #

// MEDIAN(B2:B100) → lỗi nếu có bất kỳ ô lỗi nào
// IFERROR + MEDIAN cũng không hoạt động đúng

=AGGREGATE(12, 6, B2:B100)
// 12 = MEDIAN, 6 = bỏ lỗi
// Đây là cách DUY NHẤT tính median bỏ lỗi!

// MODE bỏ lỗi:
=AGGREGATE(19, 6, B2:B100)
// 19 = MODE.SNGL, 6 = bỏ lỗi

Kết hợp với IF cho Report Dashboard #

// Dashboard cell: chọn phép tính qua dropdown
// E1 = dropdown: "SUM", "AVG", "MAX", "MIN", "MEDIAN"
=LET(
  func, SWITCH(E1,
    "SUM", 9,
    "AVG", 1,
    "MAX", 4,
    "MIN", 5,
    "MEDIAN", 12
  ),
  AGGREGATE(func, 7, B2:B500)
)
// 1 công thức thay 5 công thức! User chọn phép tính.

Mẹo Và Lưu Ý Quan Trọng #

1. AGGREGATE không hoạt động với hàng ngang (row). Nó chỉ tính theo cột dọc. Nếu dữ liệu nằm ngang, cần TRANSPOSE trước.

2. Function 14-19 dùng cú pháp khác. Thay vì ref1, ref2 riêng, chúng dùng array và k. Ví dụ: AGGREGATE(14, 6, array, 3) — không có dấu phẩy giữa các tham chiếu.

3. AGGREGATE có từ Excel 2010. Không phải hàm mới — nhưng ít người biết vì giao diện phức tạp hơn SUM/AVERAGE. Hoạt động trên mọi phiên bản Excel hiện tại.

Câu Hỏi Thường Gặp (FAQ) #

Khi nào nên dùng AGGREGATE thay vì SUBTOTAL? #

Luôn dùng AGGREGATE khi: (1) dữ liệu có thể chứa lỗi, (2) bạn cần LARGE/SMALL/MEDIAN/PERCENTILE, (3) bạn muốn linh hoạt chọn bỏ qua lỗi hoặc hàng ẩn. SUBTOTAL chỉ nên dùng khi cần backward compatibility với Excel 2007 trở về trước.

AGGREGATE có hoạt động trong PivotTable không? #

Không trực tiếp. PivotTable dùng hệ thống tính toán riêng. Tuy nhiên bạn có thể dùng AGGREGATE trong các ô bên ngoài PivotTable để tính toán bổ sung từ source data.

Tại sao function_num nhảy số (không có 8, 10, 11, 13, 18)? #

Các số 8 (STDEV.P), 10 (VAR.S), 11 (VAR.P), 13 (MODE.SNGL — phiên bản cũ) và 18 (PERCENTILE.EXC) đều tồn tại! Bảng ở trên chỉ liệt kê các function phổ biến nhất. Tổng cộng có đủ 19 function từ 1 đến 19.

Có thể dùng AGGREGATE với FILTER không? #

Được! Ví dụ: AGGREGATE(9, 6, FILTER(B:B, A:A="X")) — SUM các giá trị đã filter, bỏ lỗi. Tuy nhiên trên Excel 365, bạn cũng có thể dùng trực tiếp SUM(FILTER(...)) vì FILTER đã loại bỏ các hàng không khớp.

Tổng Kết #

AGGREGATE là hàm thống kê mạnh nhất Excel — 19 phép tính, 7 option bỏ qua, hỗ trợ mảng. Với option 6 (bỏ lỗi), bạn có thể SUM, AVERAGE, MEDIAN, LARGE, SMALL trên bất kỳ dữ liệu nào mà không sợ lỗi. Đây là vũ khí bí mật cho dashboard và báo cáo chuyên nghiệp.