Lập báo cáo động kết hợp VBA và A-Tools
Trong thực tế công việc văn phòng, các bạn nhân viên thường phải làm nhiều loại báo cáo đặc thù theo nhu cầu của doanh nghiệp. Ngoài việc dùng công thức Excel thuần túy, nhiều người đã tìm hiểu về VBA trong excel để thiết kế những báo cáo đơn giản, hiệu quả, giảm dung lượng file và tăng tốc độ xử lý công thức, dữ liệu trong file. Hôm nay mình sẽ hướng dẫn các bạn làm một báo cáo động có kết hợp VBA và một công cụ mới là A-Tools. Mục đích là tạo được báo cáo động, lọc theo một điều kiện “Mã hàng” nhưng sẽ “lọc theo một hoặc nhiều mã hàng” .
Giả sử ta có bài toán như sau: Có một bảng dữ liệu gồm nhiều cột, trong đó có cột “Mã hàng”. Vậy yêu cầu bài toán là: Tôi muốn xem báo cáo theo một mã hàng hoặc nhiều mã hàng, khi tôi chọn một mã hàng thì lọc toàn bộ dữ liệu của mã hàng đấy, khi chọn 2 thì lọc toàn bộ dữ liệu của 2 mã hàng,… tương tự cho n mã hàng.
Để giải quyết bài toán trên chúng ta làm như sau:
Bước 1: Thiết kế mẫu file Excel gồm 3 sheet như sau:
Sheet Setup: để lưu các thiết lập chung, ở đây chỉ có 1 sheet báo cáo nên chỉ cần lưu bảng dữ liệu điệu kiện gồm tất cả mã và tên hàng. Tôi tên vùng dữ liệu là DMCT_DT chẳng hạn, các bạn có thể đặt tên theo cách hiểu hoặc cấu trúc dữ liệu của mình (note: tên tiếng việt viết liền không giống, ngăn cách nhau bởi dấu gạch dưới).
SheetData: Là nơi lưu trữ cơ sở dữ liệu của các bạn, hiện tại mình đang thiết kế theo cấu trúc “Sheetdata” vừa là nơi nhập liệu, vừa lưu trữ dữ liệu. Sau này các bạn có thể tìm hiểu thêm về VBA thiết kế Form nhập liệu riêng, sau khi lưu sẽ vào “Sheetdata”, ở đây vùng dữ liệu mình đặt ten là DATA.
SheetReport: Là sheet để xem báo cáo, cấu trúc báo cáo thì mình thiết kế như hình dưới đây, gồm 1 Shap để chọn mã hàng, Shap này sẽ hiển thị Form chọn danh mục mã hàng theo ý. Các dòng tiêu đề mình thiết kế bằng tiếng việt, có dòng tổng cộng ở phía dưới, khi lập công thức chỉ việc lập từ dòng thứ 2.
Bước 2: Tiến hành thiết kế Form trong VBA.
Các bạn vào môi trường lập trình VBA (ALT+F11), vào menu Insert ->chọn UserForm, trong Form các bạn thiết kế theo cấu trúc tùy ý, còn trong bài toán của mình thì mình thiết kế hình dưới đây, ở đây mình dùng gồm “CheckBook” (để chọn tất cả), “ListBook” (để lấy danh sách) và các “Button” (để thực hiện lệnh).
Để nạp danh sách vào “Listbox”, các bạn chọn vào “Listbox”, ở bên trái phần “Propties”, các bạn vào “Categorized”, tại phần “RowSource”, các bạn gõ tên bảng chứa mã và tên mà chúng ta đã đặt trong “SheetSetup”. Ở đây các bạn chú ý trường “ColumnCount”: số cột trong bảng mà các bạn cần hiển thị trên “ListBook”, hiện tại mình để là 2 thì có cả mã và tên cùng hiển thị.
Sau khi thiết lập xong, các bạn double click vào Button “Chọn” rồi copy code dưới đây vào trong thủ tục bạn vừa tạo.
Dim i As Long
Dim s As String
If cbAll.Value = True Then
For i = 0 To lbDanhMuc.ListCount – 1
If s = “” Then
s = lbDanhMuc.List(i, 0)
Else
s = s & “‘,'” & lbDanhMuc.List(i, 0)
End If
Next i
Else
For i = 0 To lbDanhMuc.ListCount – 1
If lbDanhMuc.Selected(i) = True Then
If s = “” Then
s = lbDanhMuc.List(i, 0)
Else
s = s & “‘,'” & lbDanhMuc.List(i, 0)
End If
End If
Next i
End If
s = “‘” & s & “‘”
ThisWorkbook.Sheets(“Report”).Range(“C1”).Value = s
Unload Me
Các bạn chú ý dòng : ThisWorkbook.Sheets(“Report”).Range(“C1”).Value = s, chữ “Report” chính là tên sheet báo cáo mình đã thiết lập.
Tương tự với nút “Đóng”, các bạn chỉ cần gõ vào dòng lệnh “Unload Me” mục đích để thoát Form như hình trên.
Bước 3: Sau khi hoàn thành các bước trên, các bạn Insert một module (vào Insert -> Module) để Show UserForm như hình dướ đây:
Sau khi hoàn thành các bước trên, các bạn Insert một module (vào Insert -> Module) để Show UserForm như hình dướ đây:
Bước 4: Gán thủ tục Runform vào Shap đã thiết kế ở trong SheetReport như sau:
Các bạn chuột phải vào “Shap”, chọn tới phần “Assign Macro”.
Tiếp theo các bạn chọn vào macro “Runform” và chọn Ok.
Tiếp theo các bạn chọn vào macro “Runform” và chọn Ok.
Bước 5: Các bạn lập công thức bằng SQL Buider (tìm hiểu các video về báo cáo động trong Excel và A-Tools) hoặc copy công thức dưới đây vào trong ô đầu tiên của bảng kết quả (ô D4).
Để chạy được phần này, các bạn cài cho mình phần mềm A-Tools Free: Download
Các bạn có thể tự tìm hiểu để tự gõ hoặc dùng chức năng SQL Builder để tạo câu lệnh, các bạn xem video hướng dẫn về công thức động tại đây: Link video
=bs_Sql(“SELECT DATA.*
FROM DATA
WHERE DATA.MA_VLSPHH in ” & “(‘” &C1 &”)”,”HR=NO;INSERT=YES;”)
Sau khi copy công thức vào ô D3 xong, các bạn nhấn Enter để nhận kết quả, do chưa có điều kiện lọc nên kết quả sẽ hiển thị như bảng dưới đây.
Sau khi copy công thức vào ô D3 xong, các bạn nhấn Enter để nhận kết quả, do chưa có điều kiện lọc nên kết quả sẽ hiển thị như bảng dưới đây.
Bước 6: Thay đổi điều kiện và nhận kết quả:
Các bạn click vào “Chọn mã hàng”, sẽ hiển thị Form mà mình đã thiết kế, các bạn tick chọn vào những mã hàng cần lọc (1 hoặc nhiều mã, để chọn tất cả các bạn tick vào chọn tất cả ở bên trên, nó sẽ ngầm định là chọn tất cả dù các mã hàng không tích trên Form).
Sau khi chọn xong, các bạn click vào nút chọn để nhận kết quả:
Tiếp tục các bạn chọn nhiều mã khác cũng bằng cách click vào “Chọn mã hàng” để chọn những mã theo điều kiện cần hiển thị.
Sau đó các bạn click vào nút chọn để nhận kết quả:
Các bạn tải file ví dụ về chạy thử tại đây: Link download file
Đây là một bài toán cơ bản có kết hợp VBA và công thức tạo báo cáo động bằng A-Tools, sau khi các bạn tìm hiểu kỹ về cách làm báo cáo động bằng A-Tools kết hợp kiến thức VBA cơ bản, các bạn có thể thiết kế các file Excel nhanh gọn, đơn giản, giống như các phần mềm chuyên nghiệp trên Excel.
Sau khi chọn xong, các bạn click vào nút chọn để nhận kết quả:Tiếp tục các bạn chọn nhiều mã khác cũng bằng cách click vào “Chọn mã hàng” để chọn những mã theo điều kiện cần hiển thị.Sau đó các bạn click vào nút chọn để nhận kết quả:
Là nơi lưu trữ cơ sở dữ liệu của các bạn, hiện tại mình đang thiết kế theo cấu trúc “Sheetdata” vừa là nơi nhập liệu, vừa lưu trữ dữ liệu. Sau này các bạn có thể tìm hiểu thêm về VBA thiết kế Form nhập liệu riêng, sau khi lưu sẽ vào “Sheetdata”, ở đây vùng dữ liệu mình đặt ten là DATA.Để nạp danh sách vào “Listbox”, các bạn chọn vào “Listbox”, ở bên trái phần “Propties”, các bạn vào “Categorized”, tại phần “RowSource”, các bạn gõ tên bảng chứa mã và tên mà chúng ta đã đặt trong “SheetSetup”. Ở đây các bạn chú ý trường “ColumnCount”: số cột trong bảng mà các bạn cần hiển thị trên “ListBook”, hiện tại mình để là 2 thì có cả mã và tên cùng hiển thị.
