SUBTOTAL Và AGGREGATE: Tính Toán Thông Minh Với Dữ Liệu Lọc Trong Excel

1. Vấn đề với SUM trên dữ liệu lọc #

Khi bạn Filter bảng dữ liệu → SUM vẫn tính TẤT CẢ các hàng (kể cả hàng bị ẩn). Kết quả sai!

=SUM(C2:C100)    → Tổng TẤT CẢ, kể cả hàng ẩn
=SUBTOTAL(9, C2:C100)  → Tổng CHỈ các hàng hiển thị

SUBTOTAL và AGGREGATE giải quyết vấn đề này.

2. SUBTOTAL — Tính toán trên dữ liệu lọc #

2.1. Cú pháp #

=SUBTOTAL(function_num, ref1, [ref2], ...)

2.2. Bảng function_num #

2.3. Khác biệt 1-11 vs 101-111 #

Dùng 101-111 nếu muốn bỏ qua CẢ hàng ẩn tay.

2.4. Ví dụ: Tổng doanh số sau Filter #

=SUBTOTAL(9, C2:C100)

Filter phòng "Kinh doanh" → SUBTOTAL chỉ tính hàng Kinh doanh hiển thị.

2.5. Ví dụ: Đếm sau Filter #

=SUBTOTAL(3, B2:B100)

COUNTA trên dữ liệu lọc → đếm số hàng hiển thị.

2.6. Ví dụ: Trung bình sau Filter #

=SUBTOTAL(1, C2:C100)

AVERAGE trên dữ liệu lọc.

3. AGGREGATE — SUBTOTAL nâng cấp #

3.1. Cú pháp #

=AGGREGATE(function_num, options, ref1, [ref2])
hoặc
=AGGREGATE(function_num, options, array, k)

3.2. Hàm bổ sung (so với SUBTOTAL) #

AGGREGATE có LARGE, SMALL, MEDIAN, MODE — SUBTOTAL không có!

3.3. Options (bỏ qua gì) #

3.4. Ví dụ: SUM bỏ qua lỗi #

=AGGREGATE(9, 6, C2:C100)

function_num=9 (SUM), option=6 (bỏ error) → tính tổng dù có ô lỗi.

3.5. Ví dụ: LARGE bỏ qua lỗi #

=AGGREGATE(14, 6, C2:C100, 1)    → Giá trị lớn nhất (bỏ lỗi)
=AGGREGATE(14, 6, C2:C100, 3)    → Giá trị lớn thứ 3 (bỏ lỗi)

3.6. Ví dụ: MEDIAN trên dữ liệu lọc #

=AGGREGATE(12, 5, C2:C100)

MEDIAN chỉ trên hàng hiển thị.

4. So sánh SUM vs SUBTOTAL vs AGGREGATE #

5. Ứng dụng thực tế #

5.1. Dashboard với Filter #

Ô tóm tắt (không bị ảnh hưởng bởi filter):

Tổng:     =SUBTOTAL(9, C2:C1000)
Đếm:      =SUBTOTAL(3, B2:B1000)
Trung bình: =SUBTOTAL(1, C2:C1000)
Max:       =SUBTOTAL(4, C2:C1000)
Min:       =SUBTOTAL(5, C2:C1000)

→ Filter phòng ban → ô tóm tắt tự cập nhật!

5.2. Top N với dữ liệu lỗi #

=AGGREGATE(14, 6, Doanh_so, ROW(A1))

Copy xuống: Row 1 → Top 1, Row 2 → Top 2... Bỏ qua ô lỗi.

5.3. Subtotal trong Outline/Group #

Data → Subtotal → Excel tự thêm SUBTOTAL cho mỗi nhóm. Tổng cuối cùng (Grand Total) dùng SUBTOTAL → không bị tính trùng subtotal con.

5.4. Status bar #

Khi chọn vùng dữ liệu → Status bar (góc dưới phải) hiện SUM, AVERAGE, COUNT.

Click phải Status bar → chọn thêm: MIN, MAX, NUMERICAL COUNT.

6. SUBTOTAL tự động trong Table #

Khi dùng Excel Table (Ctrl+T):

• Bật Total Row: tick ở Table Design tab

• Excel tự thêm SUBTOTAL (không phải SUM!)

• Click dropdown ở Total Row → chọn: Sum, Average, Count, Max, Min...

7. Mẹo SUBTOTAL & AGGREGATE #

1. Luôn dùng SUBTOTAL thay SUM khi dữ liệu có thể bị filter

2. AGGREGATE(14,6,...) cho Top N an toàn (bỏ lỗi)

3. Table + Total Row: Cách nhanh nhất thêm SUBTOTAL

4. Function 109 thay 9: Bỏ qua cả hàng ẩn tay

5. AGGREGATE option=7: An toàn nhất — bỏ cả hidden + errors

8. Tổng kết #

SUBTOTAL và AGGREGATE là 2 hàm BẮT BUỘC PHẢI BIẾT khi làm việc với dữ liệu lọc. SUM truyền thống cho kết quả SAI trên dữ liệu filter — SUBTOTAL/AGGREGATE cho kết quả ĐÚNG. Đặc biệt AGGREGATE với khả năng bỏ lỗi và hỗ trợ LARGE/SMALL/MEDIAN là công cụ thống kê mạnh nhất trong Excel.