Tôi Thay Pivot Table Bằng Hàm GROUPBY Và Tiết Kiệm 3 Giờ Mỗi Tuần

Mỗi thứ Hai, tôi mở file báo cáo doanh thu và click vào 8 bảng Pivot Table khác nhau. Sau khi cập nhật dữ liệu từ hệ thống, tôi phải refresh từng bảng một, kiểm tra xem có dòng nào bị lỗi không, rồi điều chỉnh filter lại cho đúng. Toàn bộ quy trình này tốn 45 phút. Giờ đây cùng một file báo cáo, mọi thứ tự động cập nhật chỉ trong 3 giây nhờ hàm GROUPBY trong Excel 365.

Mục Lục

Pivot Table buộc tôi làm thủ công quá nhiều

Vấn đề lớn nhất với Pivot Table không phải là độ phức tạp mà là tính thủ công. Mỗi lần dữ liệu nguồn thay đổi, tôi phải chuột phải vào từng bảng và chọn Refresh. File báo cáo của tôi có 8 bảng Pivot Table phân tích dữ liệu theo năm, theo sản phẩm, theo khu vực. Refresh hết tốn 5 phút, chưa kể thời gian kiểm tra xem có dòng nào bị sai format hay filter bị mất không.

Pivot Table cũng chiếm dung lượng file đáng kể. File dữ liệu gốc 2MB của tôi sau khi thêm 8 bảng Pivot Table nhảy lên 15MB. Gửi email mất thời gian, lưu trên cloud chậm, mở file trên laptop yếu thì phải đợi 8 giây mới load xong.

Điều tệ nhất là không thể tham chiếu kết quả từ Pivot Table vào công thức khác một cách trực tiếp. Nếu muốn dùng tổng doanh thu năm 2024 từ Pivot Table trong công thức tính tỷ lệ, tôi phải copy giá trị sang ô khác hoặc dùng hàm GETPIVOTDATA phức tạp.

Hàm GROUPBY hoạt động như công thức thông thường

GROUPBY là hàm mảng động trong Excel 365 cho phép nhóm dữ liệu và tính tổng hợp ngay trong một công thức duy nhất. Không cần tạo bảng riêng, không cần refresh thủ công, không chiếm dung lượng file. Kết quả tự động cập nhật khi dữ liệu nguồn thay đổi.

Cú pháp cơ bản chỉ cần 3 tham số:

=GROUPBY(row_fields, values, function)

row_fields: Cột chứa các giá trị cần nhóm (ví dụ: tên sản phẩm, năm, khu vực)
values: Cột chứa số liệu cần tính toán (ví dụ: doanh thu, số lượng)
function: Hàm tính toán áp dụng (SUM, AVERAGE, COUNT, MAX, MIN)

Giả sử bảng dữ liệu doanh thu ở vùng A2:C100 với cột A là năm, cột B là sản phẩm, cột C là doanh thu. Để tổng hợp doanh thu theo năm, công thức đơn giản:

=GROUPBY(A2:A100, C2:C100, SUM)

Công thức này trả về một bảng gồm 2 cột: danh sách các năm duy nhất và tổng doanh thu tương ứng. Kết quả tự động mở rộng xuống các ô bên dưới và sang bên phải tùy theo số nhóm có trong dữ liệu.

XEM THÊM: Tôi Thay VLOOKUP Bằng Hàm MATCH Và Tiết Kiệm 70% Thời Gian Dò Tìm Dữ Liệu

Thêm tiêu đề và tổng cộng cho bảng kết quả

Theo mặc định, GROUPBY không hiển thị tiêu đề cột mặc dù dữ liệu nguồn có tiêu đề. Tham số thứ 4 điều khiển việc này:

=GROUPBY(A2:C100, C2:C100, SUM, 3)

Giá trị 3 cho tham số field_headers có nghĩa là vùng dữ liệu bao gồm cả tiêu đề (hàng đầu tiên) và hiển thị tiêu đề trong kết quả. Bảng kết quả giờ có tên cột rõ ràng thay vì chỉ toàn số liệu.

Tham số thứ 5 thêm dòng tổng cộng vào cuối bảng:

=GROUPBY(A2:C100, C2:C100, SUM, 3, 1)

Giá trị 1 tạo dòng Grand Total ở cuối. Nếu muốn cả tổng cộng và tổng phụ khi nhóm theo nhiều cột, dùng giá trị 2. Giá trị 0 loại bỏ hết tổng cộng. Giá trị âm đặt dòng tổng ở đầu thay vì cuối.

Sắp xếp kết quả theo thứ tự tùy chỉnh

Theo mặc định, GROUPBY sắp xếp theo thứ tự tăng dần của cột đầu tiên trong row_fields. Tham số thứ 6 cho phép sắp xếp theo bất kỳ cột nào trong kết quả:

=GROUPBY(A2:C100, C2:C100, SUM, 3, 1, -2)

Giá trị -2 có nghĩa là sắp xếp theo cột thứ 2 (cột doanh thu) theo thứ tự giảm dần. Số dương sắp xếp tăng dần, số âm sắp xếp giảm dần. Số 1 hoặc -1 sắp xếp theo cột đầu tiên, số 2 hoặc -2 sắp xếp theo cột thứ hai.

Với công thức trên, bảng kết quả hiển thị các năm có doanh thu cao nhất ở trên cùng, giúp dễ dàng nhìn thấy xu hướng tăng trưởng. Trong báo cáo quý của tôi, tôi sắp xếp theo doanh thu giảm dần để các sản phẩm bán chạy nhất luôn nằm ở đầu danh sách.

Lọc dữ liệu trực tiếp trong công thức

Tham số thứ 7 cho phép lọc chỉ hiển thị các dòng thỏa mãn điều kiện cụ thể:

=GROUPBY(A2:C100, C2:C100, SUM, 3, 1, -2, B2:B100="Laptop")

Công thức này chỉ tổng hợp doanh thu cho sản phẩm Laptop, bỏ qua tất cả sản phẩm khác trong dữ liệu nguồn. Điều kiện lọc phải trả về mảng TRUE hoặc FALSE có cùng số hàng với dữ liệu nguồn.

Có thể kết hợp nhiều điều kiện bằng phép nhân:

=GROUPBY(A2:C100, C2:C100, SUM, 3, 1, -2, (A2:A100>=2023)*(A2:A100<=2024))

Công thức này chỉ tổng hợp dữ liệu từ năm 2023 đến 2024. Thay vì tạo nhiều bảng Pivot Table với filter khác nhau, tôi tạo nhiều công thức GROUPBY cạnh nhau, mỗi công thức với điều kiện lọc riêng. Mọi thứ tự động cập nhật khi dữ liệu thay đổi.

XEM THÊM: Gộp 5 Bảng Excel Trong 10 Giây Thay Vì 30 Phút Với Hàm VSTACK

Nhóm theo nhiều cột cùng lúc

GROUPBY hỗ trợ nhóm theo nhiều cột để tạo cấu trúc phân cấp. Ví dụ nhóm theo khu vực rồi theo sản phẩm:

=GROUPBY(A2:B100, C2:C100, SUM, 3, 2, -3)

Trong công thức này, A2:B100 chứa hai cột (khu vực và sản phẩm). Excel tự động tạo cấu trúc phân cấp với khu vực ở cấp trên và sản phẩm ở cấp dưới. Tham số total_depth bằng 2 tạo cả tổng phụ cho từng khu vực và tổng cộng ở cuối.

Tham số thứ 8 kiểm soát cách sắp xếp khi có nhiều cột nhóm:

=GROUPBY(A2:B100, C2:C100, SUM, 3, 2, -3, , 0)

Giá trị 0 giữ cấu trúc phân cấp (mặc định), các sản phẩm trong cùng khu vực được nhóm lại với nhau. Giá trị 1 coi như bảng thông thường, sắp xếp toàn bộ theo tiêu chí sort_order không phân biệt cấp độ. Khi dùng giá trị 1, tổng phụ không thể hiển thị được.

Tính nhiều chỉ số cùng lúc với cột values

Tham số values có thể nhận nhiều cột để tính nhiều chỉ số trong một công thức:

=GROUPBY(A2:A100, C2:D100, SUM, 3, 1, -2)

Nếu C2:D100 chứa hai cột (doanh thu và số lượng), kết quả sẽ có 3 cột: năm, tổng doanh thu, tổng số lượng. Tuy nhiên hàm function chỉ được chọn một loại duy nhất áp dụng cho tất cả các cột values.

Để tính SUM cho cột này và AVERAGE cho cột khác, cần tạo hai công thức GROUPBY riêng rồi kết hợp bằng HSTACK hoặc đặt cạnh nhau và format thủ công. Đây là một hạn chế so với Pivot Table vốn cho phép chọn hàm khác nhau cho từng trường values.

So sánh GROUPBY với Pivot Table trong thực tế

Sau 3 tháng dùng GROUPBY thay thế hầu hết Pivot Table trong file báo cáo, tôi nhận thấy những khác biệt rõ rệt:

Tốc độ cập nhật: Pivot Table yêu cầu refresh thủ công mất 5 phút cho 8 bảng. GROUPBY tự động cập nhật trong 3 giây khi dữ liệu thay đổi. Mỗi tuần tiết kiệm 15 phút chỉ riêng việc này.

Dung lượng file: File 15MB với Pivot Table giảm xuống 3MB khi dùng GROUPBY. Email gửi nhanh hơn, đồng bộ cloud không còn chậm. Trên laptop yếu, file mở trong 2 giây thay vì 8 giây.

Tính linh hoạt: GROUPBY cho phép tham chiếu kết quả trực tiếp trong công thức khác. Tôi dùng kết quả GROUPBY để tính tỷ lệ phần trăm, vẽ biểu đồ, hoặc làm điều kiện trong hàm IF. Với Pivot Table phải copy values hoặc dùng GETPIVOTDATA phức tạp.

Hạn chế format: Pivot Table có sẵn nhiều style và layout đẹp mắt. GROUPBY trả về bảng dữ liệu thô cần format thủ công. Tôi dùng Conditional Formatting để tự động tô màu các dòng tổng cộng và tổng phụ.

XEM THÊM: Hàm ROWS Excel: Từ Cú Pháp Đến Kết Hợp Với 5 Hàm Phổ Biến

Khả năng tương tác: Pivot Table hỗ trợ Slicer để lọc trực quan. GROUPBY cần kết hợp với dropdown list hoặc checkbox để tạo tính tương tác. Tôi dùng Data Validation tạo dropdown chọn năm, rồi tham chiếu ô dropdown trong tham số filter_array của GROUPBY.

Yêu cầu phiên bản và khả năng tương thích

Hàm GROUPBY chỉ có sẵn trong Excel for Microsoft 365 phiên bản mới nhất. Cụ thể, cần đăng ký Microsoft 365 với kênh cập nhật Current Channel hoặc Beta Channel. Phiên bản Excel 2021, Excel 2019 và các phiên bản cũ hơn không hỗ trợ hàm này.

Để kiểm tra xem Excel của bạn có GROUPBY không, mở một file mới và gõ =GROUPBY( vào ô bất kỳ. Nếu Excel hiện gợi ý cú pháp với danh sách tham số là bạn đã có hàm này. Nếu Excel báo lỗi NAME thì phiên bản chưa được cập nhật.

Trong trường hợp Excel chưa hỗ trợ GROUPBY, giải pháp thay thế là dùng Power Query. Vào tab Data, chọn Get Data > From Table/Range, sau đó trong Power Query Editor chọn cột cần nhóm và click Group By. Kết quả tương tự GROUPBY nhưng yêu cầu nhiều bước hơn và không tự động cập nhật nhanh như công thức.

File chứa công thức GROUPBY mở được trên các phiên bản Excel cũ nhưng hiển thị lỗi VALUE trong các ô có công thức. Dữ liệu nguồn vẫn nguyên vẹn, chỉ phần kết quả tính toán không hiển thị được.

Khi nào nên dùng GROUPBY thay vì Pivot Table

GROUPBY phù hợp nhất khi cần báo cáo tự động cập nhật dựa trên dữ liệu thay đổi thường xuyên. Trong file theo dõi dự án của tôi, dữ liệu được nhập mỗi ngày và các bảng tổng hợp phải luôn chính xác mà không cần refresh thủ công. GROUPBY xử lý việc này hoàn hảo.

Pivot Table vẫn tốt hơn cho khám phá dữ liệu tương tác. Khi chưa rõ cần phân tích theo góc độ nào, Pivot Table cho phép kéo thả các trường, thử nghiệm các cách nhóm khác nhau một cách trực quan. Sau khi xác định được cấu trúc báo cáo cần thiết, chuyển sang GROUPBY để tự động hóa.

Đối với báo cáo cần gửi cho người không dùng Excel 365, Pivot Table an toàn hơn vì tương thích ngược với mọi phiên bản. File chứa GROUPBY gửi cho người dùng Excel 2019 sẽ hiện lỗi, trong khi Pivot Table vẫn hoạt động bình thường.

Kết quả sau 3 tháng chuyển đổi

Quy trình báo cáo hàng tuần của tôi giảm từ 45 phút xuống 5 phút. Thời gian tiết kiệm chủ yếu từ việc không cần refresh từng Pivot Table một và không phải kiểm tra lại filter bị mất. Mỗi tháng tiết kiệm 3 giờ, tương đương 36 giờ mỗi năm.

Dung lượng file giảm 80 phần trăm từ 15MB xuống 3MB giúp gửi email nhanh hơn và đồng bộ OneDrive không còn gặp lỗi timeout. Đồng nghiệp có laptop cũ phàn nàn file mở chậm giờ cũng không còn vấn đề này.

Các công thức GROUPBY có thể bật lại thành Pivot Table bất cứ lúc nào nếu cần khám phá dữ liệu theo cách khác. Excel 365 phiên bản hiện tại hỗ trợ chuyển đổi hai chiều khá mượt mà. Hàm PIVOTBY tương tự GROUPBY nhưng hỗ trợ thêm nhóm theo cột, phù hợp cho các bảng chéo phức tạp hơn.