Tự động hóa báo cáo bằng Excel VBA

Nếu bạn đang dành hàng giờ mỗi tuần để tải dữ liệu thô, sao chép vào bảng tính, kéo thả công thức và định dạng ô để tạo ra cùng một mẫu báo cáo hàng tuần, bạn đang lãng phí thời gian quý giá. Việc làm báo cáo thủ công không chỉ nhàm chán mà còn rất dễ xảy ra sai sót. May mắn thay, bạn có thể loại bỏ hoàn toàn công việc lặp đi lặp lại này bằng cách tự động hóa báo cáo với Excel VBA (Visual Basic for Applications).

VBA là ngôn ngữ lập trình được tích hợp sẵn của Excel. Nó cho phép bạn viết các tập lệnh—thường được gọi là macro—để thực thi một chuỗi các hành động ngay lập tức. Trong hướng dẫn này, chúng tôi sẽ dẫn dắt bạn qua quá trình xây dựng một hệ thống báo cáo hoàn toàn tự động từ con số không. Bạn sẽ học cách xóa dữ liệu cũ, chèn công thức động, định dạng báo cáo và xuất nó thành một file PDF chuyên nghiệp.

Tại sao nên sử dụng VBA để tự động hóa báo cáo?

Mặc dù các công cụ mới hơn như Power Query đã làm cho việc chuyển đổi dữ liệu trở nên dễ dàng hơn, VBA vẫn là "vị vua" không thể bàn cãi trong việc tự động hóa tác vụ toàn diện (end-to-end) trên Excel. Dưới đây là lý do tại sao việc học cách tự động hóa báo cáo bằng VBA sẽ tạo ra sự khác biệt lớn:

• Tốc độ: Một quy trình báo cáo mất hai giờ làm thủ công có thể được hoàn thành trong hai giây chỉ với một cú nhấp chuột vào nút VBA.
• Tính nhất quán: VBA thực hiện các bước tính toán chính xác tuyệt đối. Bạn sẽ không bao giờ còn vô tình xóa nhầm một hàng hoặc dán đè lên công thức nữa.
• Tự động hóa toàn diện: Trong khi công thức chỉ tính toán dữ liệu, VBA thực sự có thể thao tác với giao diện Excel—như định dạng ô, ẩn trang tính (sheet) và lưu tệp vào máy tính của bạn.

Nếu bạn chưa từng sử dụng macro trước đây, việc hiểu các khái niệm cơ bản sẽ rất hữu ích. Bạn có thể bắt đầu bằng cách đơn giản là ghi lại macro đầu tiên của mình, nhưng để xây dựng các hệ thống báo cáo động và mạnh mẽ, việc tự viết mã VBA là điều cần thiết.

Các thành phần thiết yếu của một hệ thống báo cáo VBA

Một báo cáo tự động chuyên nghiệp không phụ thuộc vào một khối mã khổng lồ, duy nhất. Thay vào đó, nó được chia nhỏ thành các bước mô-đun (modular). Một quy trình báo cáo tiêu chuẩn bao gồm:

1. Khởi tạo: Xóa dữ liệu của tuần trước hoặc tháng trước để dọn chỗ cho dữ liệu mới.
2. Chèn và tính toán dữ liệu: Lấy dữ liệu mới vào và tự động áp dụng các hàm logic như IF, VLOOKUP hoặc SUMIFS.
3. Định dạng: Áp dụng viền, in đậm văn bản và chỉnh độ rộng cột để báo cáo trông chuyên nghiệp hơn.
4. Tạo đầu ra: Lưu sản phẩm cuối cùng dưới dạng file Excel mới hoặc file PDF.

Bước 1: Chuẩn bị môi trường Excel của bạn

Trước khi có thể viết bất kỳ mã VBA nào, bạn cần đảm bảo môi trường Excel của mình đã được thiết lập sẵn sàng cho việc lập trình.

Đầu tiên, bạn cần bật thẻ Developer (Nhà phát triển). Đi tới File > Options > Customize Ribbon. Ở khung bên phải, hãy đánh dấu vào hộp kiểm bên cạnh mục Developer và nhấn OK. Thẻ Developer giờ đây sẽ xuất hiện ở trên cùng của cửa sổ Excel.

Tiếp theo, bạn phải lưu bảng tính (workbook) của mình đúng cách. Các tệp Excel tiêu chuẩn (.xlsx) không thể lưu trữ macro. Bạn phải vào File > Save As và thay đổi loại tệp thành Excel Macro-Enabled Workbook (*.xlsm). Nếu bạn cần ôn lại cách điều hướng trong trình soạn thảo VBA, việc xem lại chương trình Excel đầu tiên của bạn sẽ giúp bạn làm quen lại dễ dàng hơn.

Bước 2: Viết mã VBA để chuẩn bị trang tính

Để bắt đầu, hãy mở Trình soạn thảo VBA (VBA Editor) bằng cách nhấn ALT + F11. Nhấp vào Insert > Module. Không gian trống này chính là nơi chúng ta sẽ viết mã code của mình.

Bước đầu tiên trong bất kỳ báo cáo định kỳ nào là xóa dữ liệu cũ. Nếu dữ liệu thô mới của bạn có ít hàng hơn dữ liệu của tháng trước, việc dán đè lên sẽ để lại các hàng thừa và không chính xác. Chúng ta cần một macro để xóa sạch khu vực báo cáo cũ trước khi thực hiện bất kỳ điều gì khác.

Sub ClearOldData()
    ' Declare worksheet variable
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Report")
    
    ' Clear the contents and formats of the reporting range
    ' Assuming our report populates from A2 to F1000
    ws.Range("A2:F1000").Clear
    
    MsgBox "Old data cleared. Ready for new report."
End Sub

Đoạn mã này đảm bảo rằng các hàng từ A2 đến F1000 được xóa sạch hoàn toàn—bao gồm cả dữ liệu và bất kỳ định dạng nào còn sót lại. Phương thức ClearContents sẽ chỉ xóa văn bản, nhưng Clear sẽ xóa cả đường viền và màu sắc của ô.

Bước 3: Tự động hóa tính toán và công thức

Sau khi bạn đã nhập dữ liệu thô của mình vào một trang tính ẩn chạy ngầm (hãy gọi nó là "RawData"), trang tính báo cáo của bạn cần tổng hợp thông tin đó. Chúng ta có thể sử dụng VBA để ngay lập tức chèn các công thức phức tạp dọc theo toàn bộ cột mà không cần phải thao tác kéo chuột thủ công.

Giả sử chúng ta muốn lấy giá của một sản phẩm từ danh sách giá gốc bằng cách sử dụng hàm VLOOKUP, và sau đó tính tổng doanh thu.

Sub InsertFormulas()
    Dim ws As Worksheet
    Dim lastRow As Long
    Set ws = ThisWorkbook.Sheets("Report")
    
    ' Find the last row of the newly pasted data in column A
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' Insert VLOOKUP to pull price into Column D
    ws.Range("D2:D" & lastRow).Formula = "=VLOOKUP(A2, 'PricingList'!A:B, 2, FALSE)"
    
    ' Insert formula to calculate Revenue (Quantity * Price) into Column E
    ws.Range("E2:E" & lastRow).Formula = "=C2*D2"
    
    ' Convert formulas to values (optional, but saves processing power)
    ws.Range("D2:E" & lastRow).Value = ws.Range("D2:E" & lastRow).Value
End Sub

Bằng cách tìm lastRow một cách tự động, macro của bạn sẽ luôn xử lý chính xác số lượng hàng, bất kể tháng này bạn có 50 hay 5.000 giao dịch bán hàng. Việc thành thạo kỹ thuật xác định vùng dữ liệu động (dynamic range) này là rất quan trọng. Ngoài ra, việc viết công thức trong VBA hoàn toàn giống với việc gõ nó trong Excel—nếu bạn cần xem lại cú pháp, hãy tham khảo hướng dẫn toàn tập về hàm VLOOKUP của chúng tôi.

Bước 4: Định dạng báo cáo tự động

Một báo cáo chỉ thực sự hữu ích nếu nó dễ đọc. Các bên liên quan luôn kỳ vọng một định dạng rõ ràng, các tiêu đề nổi bật và các con số được căn chỉnh hợp lý. VBA xử lý việc định dạng vô cùng xuất sắc.

Macro dưới đây sẽ in đậm văn bản và thêm màu nền cho hàng tiêu đề của chúng ta, định dạng cột doanh thu dưới dạng tiền tệ (Currency) và tự động điều chỉnh kích thước (autofit) toàn bộ các cột để không có dữ liệu nào bị khuất.

Sub FormatReport()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Report")
    
    With ws
        ' Format Headers
        .Range("A1:E1").Font.Bold = True
        .Range("A1:E1").Interior.Color = RGB(0, 112, 192) ' Professional Blue
        .Range("A1:E1").Font.Color = RGB(255, 255, 255) ' White Text
        
        ' Format Revenue column as Currency
        .Columns("E").NumberFormat = "$#,##0.00"
        
        ' AutoFit all columns for readability
        .Columns("A:E").AutoFit
        
        ' Add borders to the data
        .Range("A1").CurrentRegion.Borders.LineStyle = xlContinuous
    End With
End Sub

Việc sử dụng câu lệnh With giúp mã code của bạn gọn gàng và chạy nhanh hơn, vì Excel sẽ không cần phải đánh giá lại tham chiếu trang tính trên mỗi dòng mã.

Bước 5: Xuất báo cáo sang tệp PDF

Bước cuối cùng trong vòng đời của một báo cáo là phân phối. Việc chia sẻ một tệp Excel thô chứa macro với ban quản lý có thể tiềm ẩn rủi ro, vì họ có thể vô tình thay đổi các công thức. Việc tạo ra một tệp PDF đảm bảo bố cục báo cáo được giữ nguyên vẹn và dữ liệu được khóa an toàn.

Sub ExportToPDF()
    Dim ws As Worksheet
    Dim filePath As String
    Set ws = ThisWorkbook.Sheets("Report")
    
    ' Define the file path and dynamic file name based on today's date
    filePath = ThisWorkbook.Path & "\Monthly_Sales_Report_" & Format(Date, "yyyymmdd") & ".pdf"
    
    ' Export the sheet as a PDF
    ws.ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=filePath, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=True
        
    MsgBox "PDF Report successfully generated!"
End Sub

Khi mã này được thực thi, Excel sẽ âm thầm tạo tệp PDF trong cùng thư mục lưu trữ bảng tính của bạn và ngay lập tức mở nó lên để xem lại. Để đảm bảo các tệp PDF được in hoặc xuất ra trông thật hoàn hảo, bạn có thể kết hợp điều này với một số mẹo in ấn Excel cho các báo cáo hoàn hảo, chẳng hạn như xác định vùng in (print area) bằng VBA.

Tổng hợp lại: Macro chính (Master Macro)

Bây giờ chúng ta đã có bốn tập lệnh riêng biệt, dạng mô-đun. Việc chạy từng tập lệnh một sẽ làm mất đi ý nghĩa của việc tự động hóa. Cách thực hành tốt nhất là tạo ra một macro "Chính" (Master) để gọi từng chương trình con (subroutine) theo đúng thứ tự.

Sub RunWeeklyReport()
    ' Turn off screen updating to make the macro run significantly faster
    Application.ScreenUpdating = False
    
    Call ClearOldData
    ' (Assume a step here that pastes new data into A2:C)
    Call InsertFormulas
    Call FormatReport
    Call ExportToPDF
    
    ' Turn screen updating back on
    Application.ScreenUpdating = True
    
    MsgBox "Weekly reporting process complete!"
End Sub

Bạn có thể gán macro RunWeeklyReport này cho một hình khối (shape) hoặc nút bấm (button) đơn giản trên trang tính Excel của mình. Giờ đây, toàn bộ khối lượng công việc của cả một buổi sáng sẽ được thực thi chỉ bằng một cú nhấp chuột.

Một ví dụ thực tế: Theo dõi doanh số bán hàng hàng tháng

Hãy xem xét tác động của việc này đối với một doanh nghiệp. Hãy tưởng tượng bạn nhận được một tệp CSV thô từ cổng thanh toán của mình mỗi tuần. Nó trông lộn xộn, thiếu định dạng và không bao gồm các danh mục sản phẩm của công ty bạn.

Đầu vào thô (Định dạng CSV)	Đầu ra VBA tự động (Báo cáo cuối cùng)
Ngày tháng chưa định dạng (VD: 20231005)	Ngày tháng được định dạng chuẩn (VD: 05-Oct-2023)
ID sản phẩm thô (VD: PRD-992)	Tên sản phẩm đầy đủ thông qua VLOOKUP tự động
Số lượng cơ bản	Tổng đã tính toán, tính tổng qua SUMIFS, định dạng Tiền tệ
Khối văn bản thô kệch, không viền	Bảng dữ liệu chuyên nghiệp, có màu sắc, có viền được xuất ra PDF

Bằng cách triển khai một tập lệnh chính xác như những gì được trình bày ở trên, việc thao tác thủ công nhàm chán sẽ hoàn toàn bị loại bỏ. Trên thực tế, việc học cách vận dụng chính những phương pháp này là cách một startup đã tiết kiệm được 20 giờ mỗi tuần, cho phép đội ngũ của họ tập trung vào phân tích dữ liệu thay vì nhập liệu.

Đơn giản hóa tự động hóa Excel với GPTExcel

Viết mã VBA từ con số không cực kỳ mạnh mẽ, nhưng nếu bạn là người mới làm quen với lập trình, việc nắm bắt cú pháp sao cho hoàn hảo có thể gây nản chí. Một dấu phẩy bị thiếu hay một tham chiếu đối tượng bị sai chính tả cũng sẽ gây ra lỗi thực thi (run-time error).

Đây là lúc AI thu hẹp khoảng cách. Nếu bạn từng gặp khó khăn khi viết một hàm INDEX MATCH phức tạp, xây dựng một câu lệnh lồng IF, hoặc thậm chí phác thảo logic cho một macro VBA, GPTExcel có thể giúp bạn. Bạn chỉ cần mô tả những gì bạn muốn đạt được bằng ngôn ngữ tự nhiên—ví dụ: "Viết công thức để tra cứu giá của một mặt hàng ở sheet 2 và nhân với số lượng ở cột C"—và GPTExcel sẽ ngay lập tức tạo ra công thức chính xác. Điều này giúp cho việc xây dựng các báo cáo tự động trở nên nhanh chóng và bớt đáng sợ hơn rất nhiều.

Câu hỏi thường gặp

VBA có sắp bị loại bỏ trong các phiên bản Excel mới hơn không?

Không. Mặc dù Microsoft đã giới thiệu Office Scripts (dựa trên TypeScript) cho tự động hóa trên nền tảng web, VBA vẫn được hỗ trợ hoàn toàn và tiếp tục là công cụ mạnh mẽ nhất cho tự động hóa Excel trên máy tính (desktop). Hàng triệu bảng tính của các doanh nghiệp vẫn đang phụ thuộc vào nó.

Tôi có thể tự động hóa báo cáo mà không cần biết VBA không?

Có. Bạn có thể đạt được mức độ tự động hóa đáng kể bằng cách sử dụng bộ ghi macro (macro recorder) tích hợp sẵn của Excel, công cụ này sẽ tự động chuyển các thao tác nhấp chuột của bạn thành mã VBA. Ngoài ra, các công cụ như Power Query có thể tự động hóa quy trình trích xuất và dọn dẹp dữ liệu mà không yêu cầu bạn phải viết mã.

Làm cách nào để báo cáo tự động của tôi chạy ngay khi tôi mở tệp?

Bạn có thể sử dụng một trình xử lý sự kiện (event handler) trong VBA được gọi là Workbook_Open. Bằng cách đặt lệnh gọi macro chính của bạn vào bên trong chương trình con cụ thể này thuộc module "ThisWorkbook", tập lệnh báo cáo của bạn sẽ thực thi ngay giây phút tệp được mở ra.

Tại sao macro của tôi chạy quá chậm khi định dạng các tập dữ liệu lớn?

Khi VBA chạy, Excel sẽ cố gắng cập nhật trực quan trên màn hình đối với mỗi một thay đổi nhỏ. Bằng cách thêm Application.ScreenUpdating = False vào đầu tập lệnh của bạn, và chuyển nó về lại True ở phần cuối, macro của bạn sẽ chạy nhanh hơn đáng kể vì Excel ngừng việc cố gắng hiển thị (render) các thay đổi đồ họa theo thời gian thực.