Trong bài viết này, Bluesofts sẽ hướng dẫn bạn phương pháp làm báo cáo gộp nhóm trên Excel — vừa hiển thị chi tiết, vừa tổng hợp linh hoạt theo từng tháng, từng khách hàng.
Đây là phương pháp báo cáo động hiện đại, áp dụng với Add-in A-Tools – công cụ hỗ trợ mạnh mẽ nhất cho Excel, giúp bạn tạo các báo cáo chuyên nghiệp mà không cần lập trình VBA.
Video hướng dẫn chi tiết:
1. Báo cáo gộp nhóm là gì?
- Báo cáo gộp nhóm (Grouping Report) là dạng báo cáo cho phép người dùng:
- Xem tổng hợp dữ liệu theo các cấp độ (tháng, khách hàng, sản phẩm...).
- Đồng thời có thể mở rộng chi tiết đến từng hóa đơn, từng dòng dữ liệu.
- Báo cáo được thiết kế động, người dùng có thể co giãn nhóm nhanh chóng chỉ bằng một thao tác.
Ví dụ:
Cấp 1: Tổng hợp theo tháng (Tháng 8, Tháng 9, Tháng 10).
Cấp 2: Trong từng tháng, gộp theo từng mã khách hàng.
Cấp 3: Chi tiết từng dòng chứng từ của khách hàng đó.
Bạn có thể bấm các nút [1] [2] [3] hoặc nhấn Ctrl + Enter để mở rộng/thu gọn nhóm dữ liệu cực kỳ tiện lợi.
2. Chuẩn bị dữ liệu và công cụ
2.1. Cài đặt Add-in A-Tools:
Để thực hiện được báo cáo này, bạn cần cài đặt Add-in A-Tools trong Excel.
Các bước cài đặt:
- Truy cập website: https://bluesofts.net/Chọn Giải pháp phần mềm → Add-in A-Tools
- Tải bản cài đặt phù hợp và tiến hành cài trên máy tính của bạn.
- Sau khi cài đặt, mở Excel → A-Tools sẽ xuất hiện trên thanh công cụ.
2.2. File ví dụ mẫu
Sau khi cài đặt, bạn mở thư mục:
C:\A-Tools\Help & Demos
Tại đây có nhiều ví dụ thực hành. Đối với hướng dẫn này, bạn mở file:
BS_SQL_Create report with GROUPING and FOOTER - Báo cáo phân nhóm.xls
File này đã có sẵn dữ liệu và ví dụ về báo cáo gộp nhóm để bạn thực hành theo.
3. Chuẩn bị dữ liệu nguồn
Giả sử bạn có bảng dữ liệu bán hàng trong sheet Data như sau:
Lưu ý:
- Dữ liệu bắt đầu từ dòng 3 (dòng tiêu đề).
- Tên các cột không có dấu tiếng Việt.
- Đặt Name cho vùng dữ liệu:
+ Chọn toàn bộ bảng (bao gồm tiêu đề).
+ Vào Formulas → Define Name → Data.
4. Sử dụng hàm BS_SQL để lấy dữ liệu
4.1. Giới thiệu hàm BS_SQL
Hàm BS_SQL của A-Tools cho phép bạn truy vấn dữ liệu trong Excel bằng ngôn ngữ SQL.
Cú pháp:
=BS_SQL(SQL, [Options])
- SQL : Là một chuỗi khai báo câu lệnh truy vấn (SQL). SQL là một ngôn ngữ truy vấn CSDL như trong Access, Foxpro, dBASE, SQLServer, Oracle…
- Options: Là tham số tùy chọn, giúp định nghĩa thêm các chế độ của báo cáo (như gộp nhóm, tính tổng, chèn dòng, định dạng...)
4.2. Viết câu lệnh SQL cơ bản
Tạo khung báo cáo, đặt tham số:
Từ ngày: ô E2
Đến ngày: ô F2
Sau đó nhập câu lệnh SQL để lấy số liệu các cột Ngày hiển thị giá trị lọc theo tháng, Mã Khách hàng, Số lượng, Thành tiền tại ô B3:
SELECT recno() as [STT],month(ngay_ct) as [ngày],ma_kh,slg,thanh_tien FROM data WHERE ngay_ct>=cell(E2) and ngay_ct<=cell(F2) and loai_phieu='X' and ma_kh<>''
4.3. Đặt công thức BS_SQL
Trong ô báo cáo (ví dụ ô A7), nhập công thức:
=BS_SQL(B3 & LEFT(E2&F2,0)
Trong đó B3 chứa câu lệnh SQL trên.
Khi thay đổi thời gian trong E2 hoặc F2, chỉ cần bấm chuột phải chọn Refresh, báo cáo sẽ tự động cập nhật theo điều kiện.
5. Thêm cột Thuế và Tổng Tiền
Giả sử bạn cần tính thêm:
Thuế = Thành tiền × 10%
Tổng tiền = Thành tiền + Thuế
Bổ sung vào câu lệnh SQL:
SELECT recno() as [STT],month(ngay_ct) as [ngày],ma_kh,slg,thanh_tien,thanh_tien*0.1 AS [Thuế], data(row,4)+data(row,5) as [TTiền] FROM data WHERE ngay_ct>=cell(E2) and ngay_ct<=cell(F2) and loai_phieu='X' and ma_kh<>''
6. Gộp nhóm nhiều cấp
Để tạo báo cáo gộp nhóm theo tháng và khách hàng, bạn thêm tham số Options trong hàm BS_SQL:
OPTIONS:INSERT=YES; GROUP(1,2);
Insert=Yes → Cho phép co giãn nhóm.
Group=(1,2) → Gộp nhóm cấp 1 là cột Tháng, cấp 2 là cột Mã KH.
Giờ bạn có thể:
Bấm [1] để xem tổng hợp theo tháng.
Bấm [2] để xem theo từng khách hàng.
Bấm [3] để mở toàn bộ chi tiết.
7. Định dạng màu và mẫu báo cáo
Bạn có thể định dạng màu cho từng cấp nhóm:
Tạo sheet mẫu định dạng (ví dụ Format).
Thiết kế màu nền, font chữ cho cấp 1, cấp 2, cấp tổng cộng.
Trong hàm BS_SQL, thêm vùng format:
=BS_SQL(B3 & LEFT(E2&F2,0),B4,'Format - Help'!A1:E2)
Khi đó, các dòng tổng hợp sẽ tự động lấy màu từ mẫu định dạng, giúp báo cáo đẹp, rõ ràng, dễ đọc.
8. Cộng dồn
Để tính lũy kế theo nhóm, bạn thêm công thức:
=Data(Row-1,7) + Data(Row,6)
Data(Row-1,7): giá trị dòng trước (cột 7).
Data(Row,6): giá trị hiện tại (cột 6).
Đặt tên cột là Lũy kế nhóm.
SELECT recno() as [STT],month(ngay_ct) as [ngày],ma_kh,slg,thanh_tien,thanh_tien*0.1 AS [Thuế], data(row,4)+data(row,5) AS [TTiền], data(row-1,7)+data(row,6) AS [Lũy kế nhóm] FROM data WHERE ngay_ct>=cell(E2) and ngay_ct<=cell(F2) and loai_phieu='X' and ma_kh<>''
Nếu muốn cộng dồn toàn bộ không phân nhóm, dùng biến hệ thống LastValue:
=LastValue + Data(Row,6)
9. Giới hạn cột tính tổng
Trong báo cáo nhóm, bạn có thể chọn cột nào được tính tổng bằng tham số Footer:
=BS_SQL(A1, "Insert=Yes;Group=(1,2);Footer=(3,4,5,6)")
Các cột khác sẽ không được tính tổng, giúp kết quả chính xác hơn.
10. Phím tắt thao tác nhanh
Tổ hợp phímChức năng
Ctrl + EnterMở/thu nhóm đang chọn
Ctrl + Alt + EnterMở/thu tất cả nhóm
Double clickMở/thu nhóm bằng chuột
11. Học Excel chuyên sâu – Nhận tặng Add-in A-Tools Pro
Công ty Bluesofts hiện tổ chức các khóa học Excel nâng cao, báo cáo động và lập trình VBA, giúp bạn:
Làm chủ kỹ thuật báo cáo chuyên nghiệp.
Tự động hóa công việc trên Excel.
Tiết kiệm thời gian và nâng cao hiệu suất làm việc.
* Một số chú ý khi lập báo cáo phân nhóm trên A-Tools: