GROUPBY & PIVOTBY — Pivot Table Bằng Công Thức

GROUPBY và PIVOTBY là 2 hàm mới nhất của Excel 365 — cho phép tạo bảng tổng hợp (pivot) trực tiếp bằng công thức, không cần PivotTable truyền thống. Bài viết hướng dẫn chi tiết cú pháp, ví dụ thực tế, và khi nào nên dùng thay PivotTable.

Tại Sao Cần GROUPBY Và PIVOTBY? #

PivotTable là tính năng mạnh nhất để tổng hợp dữ liệu. Nhưng nó có nhược điểm:

• Không tự cập nhật: Phải Refresh thủ công khi data thay đổi.

• Chiếm không gian: Cần sheet riêng hoặc vùng trống lớn.

• Khó lồng vào công thức: Không thể dùng kết quả PivotTable trực tiếp trong công thức khác.

• Định dạng riêng: PivotTable có format đặc biệt, khó tùy chỉnh bố cục.

GROUPBY và PIVOTBY giải quyết tất cả — chúng là PivotTable dạng công thức: tự cập nhật, trả về mảng thông thường, và kết hợp được với mọi hàm Excel khác.

Hàm GROUPBY — Nhóm Theo Hàng #

GROUPBY nhóm dữ liệu theo 1 hoặc nhiều cột và áp dụng hàm tổng hợp — giống PivotTable chỉ có Row fields.

Cú pháp #

=GROUPBY(row_fields, values, function, [field_headers], [total_depth], [sort_order], [filter_array])

Tham số bắt buộc:
  row_fields  — Cột dùng để nhóm (ví dụ: cột Phòng ban)
  values      — Cột chứa giá trị tính toán (ví dụ: cột Doanh thu)
  function    — Hàm tổng hợp: SUM, AVERAGE, COUNT, MAX, MIN...

Ví dụ 1: Tổng doanh thu theo phòng ban #

// Data: A = Phòng ban, B = Nhân viên, C = Doanh thu
=GROUPBY(A2:A100, C2:C100, SUM)

// Kết quả (dynamic array):
// IT          450,000,000
// Marketing   320,000,000
// Sales       680,000,000
// HR          210,000,000

Ví dụ 2: Nhóm theo 2 cột #

// Nhóm theo Phòng ban + Chức vụ:
=GROUPBY(A2:B100, C2:C100, SUM)

// Kết quả:
// IT      Manager    180,000,000
// IT      Staff      270,000,000
// Sales   Manager    250,000,000
// Sales   Staff      430,000,000

Ví dụ 3: Nhiều hàm tổng hợp #

// Tổng + Trung bình + Đếm cùng lúc:
=GROUPBY(
  A2:A100,
  C2:C100,
  HSTACK(SUM, AVERAGE, COUNT)
)
// 3 cột kết quả: Tổng | Trung bình | Số lượng

Tham số tùy chọn #

• field_headers — 0: không có header, 1: có header trong data, 2: tự tạo header, 3: cả hai. Mặc định: 1.

• total_depth — 0: không tổng, 1: Grand Total, 2: Grand + Subtotal, -1: chỉ Grand Total (cuối), -2: cả subtotal.

• sort_order — 0: giữ nguyên, 1: tăng dần, -1: giảm dần.

• filter_array — Mảng TRUE/FALSE để lọc data trước khi nhóm.

Hàm PIVOTBY — Pivot Đầy Đủ (Hàng + Cột) #

PIVOTBY mở rộng GROUPBY thêm col_fields — tạo bảng pivot hoàn chỉnh có cả Row và Column.

Cú pháp #

=PIVOTBY(row_fields, col_fields, values, function, [field_headers], [row_total_depth], [row_sort_order], [col_total_depth], [col_sort_order], [filter_array])

Ví dụ 1: Doanh thu theo Phòng ban × Quý #

// A = Phòng ban, B = Quý, C = Doanh thu
=PIVOTBY(A2:A100, B2:B100, C2:C100, SUM)

// Kết quả:
//              Q1          Q2          Q3          Q4
// IT       120,000    130,000    100,000    100,000
// Sales    180,000    200,000    150,000    150,000
// HR        50,000     60,000     50,000     50,000

Ví dụ 2: Có Grand Total #

=PIVOTBY(A2:A100, B2:B100, C2:C100, SUM, 1, 1, 1, 1)
// row_total_depth = 1, col_total_depth = 1
// → Thêm hàng tổng cuối + cột tổng bên phải

Ví dụ 3: Kết hợp với LAMBDA #

// Hàm tổng hợp tùy chỉnh — % so với tổng:
=PIVOTBY(
  A2:A100, B2:B100, C2:C100,
  LAMBDA(x, SUM(x) / SUMPRODUCT((A1:A100<>"")*C1:C100) * 100)
)
// Kết quả hiển thị % thay vì con số tuyệt đối

So Sánh GROUPBY vs PIVOTBY vs PivotTable #

Ứng Dụng Thực Tế #

Dashboard động không cần PivotTable #

// Tổng hợp doanh thu theo khu vực, lọc theo tháng:
=GROUPBY(
  A2:A1000,
  D2:D1000,
  SUM,
  1, 1, -1,
  (B2:B1000=F1)  // F1 = tháng chọn từ dropdown
)
// Đổi F1 → kết quả tự cập nhật ngay lập tức

Báo cáo cross-tab sales #

// Sản phẩm × Tháng, có tổng:
=PIVOTBY(
  A2:A500,       // row: Sản phẩm
  B2:B500,       // col: Tháng
  C2:C500,       // values: Doanh thu
  SUM,           // function
  1,             // headers trong data
  1,             // row Grand Total
  1,             // sort tăng dần
  1              // col Grand Total
)

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

GROUPBY/PIVOTBY có thay thế PivotTable? #

Với dữ liệu nhỏ-vừa (dưới 100K rows) và báo cáo cần lồng trong công thức: có. Nhưng PivotTable vẫn mạnh hơn cho dataset lớn, slicers, drill-down, và tích hợp Power Pivot.

Khi nào dùng GROUPBY, khi nào PIVOTBY? #

Dùng GROUPBY khi chỉ cần nhóm theo hàng (1 chiều). Dùng PIVOTBY khi cần cross-tab (hàng × cột). GROUPBY đơn giản hơn, PIVOTBY linh hoạt hơn.

Excel phiên bản nào hỗ trợ? #

GROUPBY và PIVOTBY chỉ khả dụng trên Excel 365 (Microsoft 365) phiên bản mới nhất. Không có trên Excel 2021 hay cũ hơn. Đây là 2 hàm mới nhất trong hệ sinh thái Excel.

Có thể dùng hàm tổng hợp tùy chỉnh không? #

Có. Dùng LAMBDA làm tham số function. Ví dụ: LAMBDA(x, PERCENTILE(x, 0.9)) để lấy percentile 90. Hoặc LAMBDA(x, STDEV(x)) cho độ lệch chuẩn.

Tổng Kết #

GROUPBY và PIVOTBY mang sức mạnh PivotTable vào thế giới công thức: tự cập nhật, kết hợp được với mọi hàm khác, format tùy ý. GROUPBY cho tổng hợp 1 chiều, PIVOTBY cho cross-tab đầy đủ. Cả 2 đều hỗ trợ LAMBDA cho hàm tổng hợp tùy chỉnh — mở ra khả năng phân tích vượt xa PivotTable truyền thống.