Cách tự viết chương trình tổng hợp, thống kê bằng VBA trên Excel

Đối với những bạn thuộc dân làm việc văn phòng thì hầu như hàng tháng hay hàng quý đều phải làm để báo cáo cho công ty, doanh nghiệp. Tuy nhiên việc thao tác thủ công phải lặp đi lặp lại này gây mất khá nhiều thời gian và công sức khi thực hiện.

Nếu như bạn đang tìm một giải pháp tối ưu hơn thì trong bài hướng dẫn sau đây sẽ giúp các bạn làm một chương trình tổng hợp, thống kê bằng VBA trên Excel có thể tạo được bảng báo cáo tương tư ví dụ như hình bên dưới.

Hướng dẫn cách viết VBA làm tổng hợp Excel

Bước 1: Bật chức năng chạy được chương trình có VBA

Bật tính năng VBA trên Excel để có thể chạy được chương trình vì tính năng này kích hoạt có khả năng liên quan đến vấn đề bảo mật nên mặc định Excel sẽ chặn tính năng này.

Bước 2: Thêm tính năng đọc VBA cho Office (nếu bị thiếu trong quá trình cài đặt Offices)

Nếu đã thực hiện Bước 1 nhưng vẫn chưa chạy được chương trình thì có thể kiểm tra máy tính của bạn đã cài đặt VBA hay chưa bằng cách vào Control Panel >> All Control Panel Items >> Programs and Features chọn vào chương trình Office đã cài đặt >> chuột phải và chọn Change.

Tiếp theo chọn vào Add or Remove Features >> Continue.

Chọn Tab Office Shared Features >> xem mục Visual Basic for Applications nếu trạng thái là Not Available thì bạn chọn cài đặt bổ sung.

Bước 3: Mở thêm Tab để xây dựng chương trình. Vào File >> Options >> Customize Ribbon >> thêm Tab Developer.

Bước 4: Tạo nút lấy dữ liệu cho chương trình. Vào Insert >> Shape để thêm một nút như hình bên dưới.

Tiếp theo Tab Developer >> Visual Basic >> Mục Modules >> Chọn chuột phải và Insert module.

Sau đó code một đoạn code như hình bên dưới để chọn dữ liệu cần tổng hợp cho chương trình.

Lên đầu trang ↑

Đoạn code VBA

Sub ThanhToanNGOAI()
     With Application.FileDialog(1)
     On Error Resume Next
         .InitialFileName = ThisWorkbook.Path
         .Title = "Chon file NGOAI TRU - Xuat tu HMS"
         .FilterIndex = 3
         .AllowMultiSelect = False
         Do
             .Show
             If .SelectedItems.Count = 0 Then Exit Sub
             If .SelectedItems(1) = ThisWorkbook.FullName Then MsgBox "Khong chon file nay!"
         Loop Until .SelectedItems(1) <> ThisWorkbook.FullName
         With Workbooks.Open(.SelectedItems(1))
             .Sheets(1).Cells.Copy ThisWorkbook.Sheets(3).[A1]
             .Close False
         End With
     End With
     '---Ket qua Ngoai tru---
     Sheet7.Visible = xlSheetVisible
     Sheet7.Select
     Cells.EntireColumn.AutoFit
     ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
     Range("a3").Select
     MsgBox "TONG HOP BHXH THANH TOAN NGOAI TRU HOAN THANH ......", , "HOAN THANH"
 End Sub

Vì trong chương trình ví dụ mình có nhiều Sheet nên cần ghi vị trí lưu dữ liệu đầu vào.

Đoạn code tiếp theo Sheet7.Visible = XlSheetVisible nghĩa là sau khi lưu dữ liệu vào chương trình các hàm thống kê được thiết lập trong Sheet7 (Dòng 5 trong bước 5) sẽ tổng hợp số liệu sau đó từ trạng thái đang ẩn sẽ hiển thị lên số liệu tổng hợp.

Sau đó gán Button vừa tạo ở trên vào hàm mẫu tên ThanhToanNgoai() để tiến hàng lấy dữ liệu và lưu trữ cho chương trình bằng cách bấm chuột phải vào Button >> Assign Marco >> chọn ThanhToanNgoai().

Bước 5: Thiết kế mẫu báo cáo, thống kê

Tạo thêm 1 Sheet mới để thiết kế mấu báo cáo, thống kê theo ý muốn trong ví dụ thì Sheet mẫu báo cáo của mình tên là KetQuaNgoaiTru.

Sau đó tạo biến tham chiếu để tổng hợp vào Formulas >> Name Manager >> New >> Đặt tên biến và tham chiếu đến cột dữ liệu cần tổng hợp. Trong chương trình ví dụ của mình cần đếm tất cả đối tượng cột ở vị trí cột G.

Dữ liệu tham chiếu ví dụ mình cần tổng hợp như hình bên dưới.

Nếu muốn tổng hợp các cột tiếp theo chỉ cần thêm biến tham chiếu và dẫn đến vị trí cột đó. Ví dụ mình cần tổng hợp cột tổng tiền tại vị trí cột T.

Tiếp tục cài đặt hàm để báo cáo, thống kê theo một số điều kiện theo ý muốn của bạn. Ví dụ tổng hợp của chương trình của mình.

Các hàm khác còn lại tùy thuộc và mục đích cần tổng hợp, thống kê của bạn mà có thể viết hàm tương ứng. Ngoài ra nên tạo thêm Button Copy để lưu trữ lại dữ liệu sau khi tổng hợp thì nếu Save trực tiếp trên chương trình sẽ làm mất đi các hàm đã viết của bạn.

Vào chương trình viết thêm đoạn code Copy và gán cho Button Copy để tạo bản lưu trữ.

Sau khi hoàn tất các bước các bạn Save chương trình lại với định dạng là Excel Marco-Enable Wokbook để thể hiện là chương trình Excel có Marco.

Chương trình sau khi hoàn chỉnh tất cả các bước với dữ liệu đầu vào và bảng tổng hợp.

Lên đầu trang ↑

Lời kết

Chức năng dùng VBA làm chương trình tổng hợp nếu so với các chương trình khác thì nó có lợi thế hơn vì có thể tổng hợp rất nhanh chóng đối với cả dữ liệu cần tổng hợp khá lớn. Sau khi xem xong bài hướng dẫn hy vọng các bạn có thể làm chương trình tổng hợp phù hợp với nhu cầu công việc hiện tại.

Và đừng quên chia sẻ với chúng tôi nếu như bạn có bất kỳ vấn đề hoặc thắc mắc nào trong khi thực hiện hướng dẫn trong phần dưới đây nhé!

Chúc bạn thành công!