Video hướng dẫn chi tiết:
Sau khi mở file BS_SQL_Create report with GROUPING and FOOTER - Báo cáo phân nhóm.xls, bạn sẽ thấy file được thiết kế sẵn với một số sheet ví dụ, mỗi sheet minh họa cho một bước trong quá trình tạo báo cáo.
A. Sheet: BS_SQL A-Tools 2019 - EASY
1. Mục đích: Giới thiệu tính năng GROUP giúp nhóm dữ liệu tự động.
Phương pháp tạo báo cáo nhóm
Nhóm dữ liệu theo THANG (1), MA_KH(2)
Chỉ cần thêm khai báo GROUP vào tham số OPTIONS của hàm BS_SQL
GROUP(1,2)
Các cột số lượng, số tiền sẽ tự động được tính tổng: SUM
2. Cấu trúc/OPTIONS:
• SQL: SELECT RECNO() AS STT,MONTH(NGAY_CT) AS THANG,MA_KH,SLG,THANH_TIEN
FROM DATA
WHERE MA_KH<>'' AND NGAY_CT>=CELL(C2) AND NGAY_CT<=CELL(D2)
• OPTIONS: INSERT=YES; GROUP(1,2); SORT=4
3. Kết quả:
• Tự động tạo báo cáo phân cấp theo tháng và nhà cung cấp.
• Giúp trình bày gọn gàng, dễ theo dõi.
Điều khiển nhóm
Đặt con trỏ trong báo cáo, nhấn
CTRL + ENTER đóng, mở nhóm báo cáo tại con trỏ
CTRL + ALT + ENTER đóng, mở các nhóm của báo cáo
.png.aspx)
B. Sheet: BS_SQL A-Tools 2019 - FOOTE
1. Mục đích: Bổ sung FOOTER để thêm dòng tổng sau mỗi nhóm.
- Phương pháp tạo báo cáo nhóm
Nhóm dữ liệu theo THANG (1), MA_KH(2)
Chỉ cần thêm khai báo GROUP vào tham số OPTIONS của hàm BS_SQL
GROUP(1,2)
- Chỉ định vị trí các cột cần tính tổng
FOOTER(3,4,5,6)
Tức là các cột có vị trí trong báo cáo là: 3,4,5,6 sẽ đc tính tổng
Trong khai báo SQL chú ý biến hệ thống ở cột "Luy_ke"
Có biến LASTVALUE dùng để láy giá trị cột hiện thời liên trước
- Cách định dạng
Theo ngầm định hàm BS_SQL sẽ lấy dòng cuối cùng của báo cáo cách một dòng trống để lấy định dạng cho các dòng footer
2. Cấu trúc/OPTIONS:
• SQL: SELECT recno() as stt,month(ngay_ct) as thang, ma_kh, slg,thanh_tien as tien_hang, thanh_tien*0.1 AS thue,data(row,col-1)+data(row,col-2) as thanh_tien,
data(row-1,col)+data(row,col-1) as Luy_ke,
LASTVALUE+data(row,6) as Luy_ke_chung
FROM data WHERE loai_phieu='X' and ma_kh<>'' and ngay_ct>=cell(e2) and ngay_ct<=cell(f2)A
• OPTIONS: INSERT=YES; GROUP(1,2); FOOTER(3,4,5,6)
3. Kết quả: Tạo tổng cộng cho từng nhóm dữ liệu.
• Báo cáo có tổng số tiền, thuế, thành tiền cho từng nhóm.
• Phù hợp trình bày báo cáo mua hàng, doanh thu.
.png.aspx)
C. Sheet: BS_SQL A-Tools 2019 - FOOTER 1
1. Mục đích: Dùng FOOTER có điều kiện lọc dữ liệu khác 0.
- Một phương pháp tạo FOOTER đơn giản FOOTER(<>0,7,8)
- Tức là tính tổng tất cả ngoại trừ các cột có vị trí 0,7,8
(*)Cách định dạng:
Theo ngầm định hàm BS_SQL sẽ lấy dòng cuối cùng của báo cáo cách một dòng trống để lấy định dạng cho các dòng footer
Tùy tính định dạng thì khai báo vùng có định dạng mẫu vào tham số thứ 3 của hàm BS_SQL
=bs_Sql(B3,B4,Format!A1:E2)
(Trong sheet Format", tại vùng A1:E2 chứa mẫu định dạng)
2. Cấu trúc/OPTIONS:
• SQL: SELECT recno() as stt,month(ngay_ct) as thang, ma_kh, slg,thanh_tien as tien_hang, thanh_tien*0.1 AS thue,data(row,col-1)+data(row,col-2) as thanh_tien,
data(row-1,col)+data(row,col-1) as Luy_ke,
LASTVALUE+data(row,6) as Luy_ke_chung
FROM data WHERE loai_phieu='X' and ma_kh<>'' and ngay_ct>=cell(e2) and ngay_ct<=cell(f2)
ORDER BY 2,3
• OPTIONS: INSERT=YES; GROUP(1,2); FOOTER(<>0,7,8)
• Tổng chỉ tính các giá trị khác 0, giúp báo cáo gọn hơn.
D. Sheet: BS_SQL A-Tools 2019 - FOOTE 2
1. Mục đích: Mỗi cột có thể có hàm tổng hợp khác nhau trong Footer.
- Cách tạo FOOTER: FOOTER(3=MAX,4=SUM,5=MIN,6=MAX);
- Có nghĩa là cột 3 dùng hàm MAX, cột 4 hàm SUM, 6 dùng hàm MAX
- Các hàm thống kế:
- SUM, COUNT,, MIN, MAX, AVG,COUNTA
- FOOTER(<>0,7,8): chỉ tính tổng các giá trị khác 0 tại cột 7 và 8.
- Giúp loại bỏ các dòng nhóm không có dữ liệu hoặc giá trị bằng 0.
- Báo cáo gọn hơn, dễ đọc.
2. Cấu trúc/OPTIONS:
• SQL: SELECT recno() as stt,month(ngay_ct) as thang, ma_kh, slg,thanh_tien as tien_hang, thanh_tien*0.1 AS thue,data(row,col-1)+data(row,col-2) as thanh_tien,
data(row-1,col)+data(row,col-1) as Luy_ke,
LASTVALUE+data(row,6) as Luy_ke_chung
FROM data WHERE loai_phieu='X' and ma_kh<>'' and ngay_ct>=cell(e2) and ngay_ct<=cell(f2)
ORDER BY 2,3
• OPTIONS: INSERT=YES; GROUP(1,2); FOOTER(3=MAX,4=SUM,5=MIN,6=MAX);
• Cho phép linh hoạt tổng hợp dữ liệu theo từng cột.
E. Sheet: BS_SQL A-Tools 2019 - FOOTE 3
1. Mục đích: Dùng công thức tùy chỉnh trong Footer.
2. Cấu trúc/OPTIONS:
- Phối hợp với hàm Excel để lấy thông tin
+ FOOTER(2=[IF(data(row,2)<>'',VLOOKUP(data(row,2),L2:M5,2,0),'')] ,3,4,5,6 )
+ Khi dùng hàm Excel (ngoài những hàm thống kê A-Tools cung cấp) thì dùng hàm IF, các trường hợp khác dùng IIF 2=[IF(data(row,2)<>'',VLOOKUP(data(row,2),L2:M5,2,0),'')]
(Nếu cột 2 (tính từ 0) mà khác rỗng thì tìm tên trong vùng L2:M5 bằng hàm VLOOKUP 3,4,5,6
(Các cột 3,4,5,6 được tính tổng với hàm SUM)
• SQL: SELECT recno() as stt,month(ngay_ct) as thang, ma_kh, slg,thanh_tien as tien_hang, thanh_tien*0.1 AS thue,data(row,col-1)+data(row,col-2) as thanh_tien,
data(row-1,col)+data(row,col-1) as Luy_ke,
LASTVALUE+data(row,6) as Luy_ke_chung
FROM data WHERE loai_phieu='X' and ma_kh<>'' and ngay_ct>=cell(e2) and ngay_ct<=cell(f2)
ORDER BY 2,3
• OPTIONS: INSERT=YES; GROUP(1,2); FOOTER(2=[IF(data(row,2)<>'',VLOOKUP(data(row,2),L2:M5,2,0),'')] ,3,4,5,6 )
- Chèn nội dung tuỳ biến vào Footer.
F. Sheet: BS_SQL A-Tools 2019 - FOOTE 4
1. Mục đích: Đếm số hoá đơn bằng COUNT trong Footer.
2. Cấu trúc/OPTIONS:
Thay vì nhập vị trí cột trong hàm bạn có thể dùng tên cột
Như vậy đưa các bột vào vào hàm ta có 02 cách: tọa độ cột hoặc tên cột
Nhưng đều phải trong ngoặc nhọn < tên hoặc vị trí cột >
Muốn nội dung xuống dòng thì phải nhấn ALT+ENTER tại điểm muốn ngắt
"FOOTER(3=Số HĐ: [COUNTA(<ma_kh>)], 8=Tổng phải trả: [TEXT(SUM(<6>),'#,##0')]
Số tiền lớn nhất: [TEXT(MAX(<thanh_tien>),'#,##0')])"
• SQL: SELECT recno() as stt,month(ngay_ct) as thang, ma_kh, slg,thanh_tien as tien_hang, thanh_tien*0.1 AS thue,data(row,col-1)+data(row,col-2) as thanh_tien,
data(row-1,col)+data(row,col-1) as Luy_ke,
LASTVALUE+data(row,6) as Luy_ke_chung
FROM data WHERE loai_phieu='X' and ma_kh<>'' and ngay_ct>=cell(e2) and ngay_ct<=cell(f2)
ORDER BY 2,3
• OPTIONS: INSERT=YES; GROUP(1,2); FOOTER(3=Số HĐ: [COUNTA(<ma_kh>)], 8=Tổng phải trả: [TEXT(SUM(<6>),'#,##0')] Số tiền lớn nhất: [TEXT(MAX(<thanh_tien>),'#,##0')])
• Hiển thị 'Số HĐ:' sau mỗi nhóm.
G. Sheet: BS_SQL A-Tools 2019-FOOTER 5
1. Mục đích: Đếm số giao dịch theo tháng.
2. Cấu trúc/OPTIONS:
• SQL: SELECT recno() as stt,month(ngay_ct) as thang, ma_kh, slg,thanh_tien as tien_hang, thanh_tien*0.1 AS thue,data(row,col-1)+data(row,col-2) as thanh_tien,
data(row-1,col)+data(row,col-1) as Luy_ke,
LASTVALUE+data(row,6) as Luy_ke_chung
FROM data WHERE loai_phieu='X' and ma_kh<>'' and ngay_ct>=cell(e2) and ngay_ct<=cell(f2)
ORDER BY 2,3
• OPTIONS: INSERT=YES; GROUP(1,2); FOOTER(2=Số GD: [COUNTA(<ma_kh>)], 3,4,5,6);SD=2; FM=NO;
Tham số OPTIONS có thể hai khai báo
+ SD= Thứ tự nhóm
Như vậy muốn xem ở cấp 2 ta khai báo
SD= 2
+ FM=NO là không tự định dạng nữa
Không định dạng sẽ làm tốc độ chạy báo cáo nhanh hơn)
• Giúp theo dõi số lượng giao dịch.
H. Sheet: BS_SQL A-Tools 2019-FOOTER 6
1. Mục đích: Giống ví dụ trên nhưng thêm cột nhận biết trạng thái nhóm.
2. Cấu trúc/OPTIONS:
• SQL: SELECT recno() as stt,month(ngay_ct) as thang, ma_kh, slg,thanh_tien as tien_hang, thanh_tien*0.1 AS thue,data(row,col-1)+data(row,col-2) as thanh_tien,
data(row-1,col)+data(row,col-1) as Luy_ke,
LASTVALUE+data(row,6) as Luy_ke_chung, IIF(data(row,1)<>'', NEWGROUP,'') AS NEWGROUP, NULL AS [GROUP ID]
FROM data WHERE loai_phieu='X' and ma_kh<>'' and ngay_ct>=cell(e2) and ngay_ct<=cell(f2)
ORDER BY 2,3
• OPTIONS: INSERT=YES; GROUP(1,2); FOOTER(2=Số GD: [COUNTA(<ma_kh>)], 3,4,5,6);SD=2; FM=NO;
Các biến hệ thống các bạn có thể khai tháng sau à:
+ GROUPID: nhận giá trị từ 1,2... cho biết dòng footer thuộc cấp nhóm nào
+ NEWGROUP: nhận giá trị TRUE/FALSE cho biết dòng dữ liệu có thuộc dòng đầu tiên của nhóm không?
+ ROWTYPE: nhận giá trị:
0: body - dữ liệu trình bày chi tiết, cấp bé nhất
1: header - dòng tiêu đề của nhóm
2: footer - dòng tổng hợp của nhóm
3: grand total - dòng tổng hợp cuối báo cáo
+ LASTROW: cho biết dòng liền trước
+ LASTVALUE:
• Hỗ trợ kiểm soát nhóm dữ liệu trong báo cáo.
Tổng kết
Các ví dụ trên giúp người dùng hiểu rõ cách sử dụng GROUP và FOOTER trong hàm BS_SQL của A-Tools. Nhờ đó, việc tạo báo cáo trong Excel trở nên tự động, linh hoạt và dễ kiểm soát hơn. Người dùng có thể mở rộng các mẫu này để tạo báo cáo nhiều cấp, hoặc thêm công thức tính tổng động.
Làm chủ kiến thức Tạo báo cáo động:
Để mở rộng kiến thức và ứng dụng VBA vào các hệ thống báo cáo chuyên nghiệp, mời bạn tham khảo khóa học: