Hàm GROUPBY Và PIVOTBY: Tổng Hợp Dữ Liệu Bằng Công Thức
GROUPBY và PIVOTBY tổng hợp dữ liệu bằng công thức thay vì bằng PivotTable. Bạn viết một công thức, kết quả tràn ra thành một bảng tổng hợp có tiêu đề, có dòng tổng cộng, và tự cập nhật khi dữ liệu nguồn thay đổi mà không phải bấm Refresh.
Phiên bản nào có hai hàm này?
Đây là điểm cần làm rõ trước tiên, vì các nguồn hiện đang mâu thuẫn nhau. Một số trang tài liệu của Microsoft liệt kê hai hàm này áp dụng cho cả Excel 2021 và Excel 2024, trong khi thông báo ra mắt ban đầu và nhiều nguồn khác lại nói đây là hàm dành riêng cho bản thuê bao Microsoft 365.
Thực tế cho thấy nhóm hàm thế hệ mới thường xuất hiện ở bản thuê bao trước, và bản mua một lần chỉ có những gì đã hoàn thiện tại thời điểm phát hành.
Cách chắc chắn duy nhất là thử trên máy bạn: gõ =GROUPBY(A2:A100, B2:B100, SUM) vào một ô trống. Ra bảng thì bản của bạn có, báo #NAME? thì không có.
Nếu bạn đang cân nhắc mua Office chỉ vì cần hai hàm này, hãy nhờ kỹ thuật kiểm tra trên đúng phiên bản trước khi đặt. Đừng quyết định dựa trên bài hướng dẫn, kể cả bài này.
Cú pháp GROUPBY
=GROUPBY(cột_nhóm, cột_giá_trị, hàm_tổng_hợp, [tiêu_đề], [tổng_cộng], [thứ_tự], [lọc])
Tổng doanh thu theo khách hàng:
=GROUPBY(B2:B500, D2:D500, SUM)
Kết quả là một bảng hai cột: tên khách hàng và tổng doanh thu, kèm dòng tổng cộng ở cuối.
Thêm sắp xếp giảm dần theo giá trị:
=GROUPBY(B2:B500, D2:D500, SUM, 3, 0, -2)
Cú pháp PIVOTBY
PIVOTBY thêm một chiều nữa, tạo bảng chéo giống PivotTable:
=PIVOTBY(cột_dòng, cột_cột, cột_giá_trị, hàm_tổng_hợp, ...)
=PIVOTBY(B2:B500, C2:C500, D2:D500, SUM)
Ra bảng có khách hàng ở các dòng, khu vực ở các cột, doanh thu ở giữa.
Khác gì PivotTable?
| Tiêu chí | GROUPBY, PIVOTBY | PivotTable |
|---|---|---|
| Cập nhật khi dữ liệu đổi | Tự động | Phải bấm Refresh |
| Thay đổi cấu trúc | Sửa công thức | Kéo thả bằng chuột |
| Dùng kết quả cho công thức khác | Dễ, tham chiếu bằng dấu # | Phải dùng GETPIVOTDATA |
| Dữ liệu rất lớn | Kém hơn | Tốt hơn |
Nói ngắn gọn: hai hàm này tiện khi bạn cần một bảng tổng hợp nhỏ nằm trong luồng tính toán của bảng tính. PivotTable vẫn hợp hơn cho báo cáo lớn cần thao tác tương tác, xem PivotTable nâng cao.
Cách làm thay thế trên bản vĩnh viễn
Nếu máy bạn không có hai hàm này, ba hướng sau đều chạy được.
PivotTable. Cách chuẩn và mạnh nhất, có ở mọi phiên bản từ 2016.
UNIQUE kết hợp SUMIFS. Dùng UNIQUE sinh danh sách nhóm, rồi SUMIFS tính giá trị cho từng nhóm:
=UNIQUE(B2:B500) ở cột H, sau đó =SUMIFS($D$2:$D$500, $B$2:$B$500, H2) ở cột I.
Kết quả cũng tự cập nhật, chỉ là phải viết hai công thức thay vì một. Cách dùng hai hàm này ở bài hàm UNIQUE, SORT và SORTBY và hàm SUMIFS và COUNTIFS. Cần bản 2021 trở lên cho UNIQUE, xem key Office 2021.
Power Pivot với measure DAX. Hợp khi dữ liệu lớn và cần nhiều phép tính tổng hợp, xem Power Pivot và Data Model.
Câu hỏi thường gặp
GROUPBY có thay thế hẳn PivotTable không?
Không. Với báo cáo lớn cần kéo thả và lọc tương tác, PivotTable vẫn là công cụ chính.
Kết quả GROUPBY báo lỗi #SPILL?
Có dữ liệu chắn vùng tràn, xem cách xử lý tại mảng động và lỗi #SPILL.
Dùng được nhiều hàm tổng hợp cùng lúc không?
Được, truyền một mảng các hàm vào tham số tổng hợp, ví dụ vừa SUM vừa COUNT.
