Các Macro Hay Trong Excel: Từ Cơ Bản Đến Nâng Cao Giúp Bạn Tối Ưu Hoá Công Việc

Có thể bạn quan tâm: Khóa Học Excel Kế Toán Online: Nâng Cao Kỹ Năng, Tăng Cường Năng Suất Làm Việc

Giới thiệu chung về Macro trong Excel

Macro là một tập hợp các lệnh được ghi lại hoặc viết bằng ngôn ngữ lập trình VBA (Visual Basic for Applications) nhằm tự động hoá các thao tác lặp đi lặp lại trong Excel. Khi làm việc với dữ liệu khối lượng lớn, việc thực hiện thủ công các bước như lọc, sắp xếp, tính toán, định dạng hay tạo báo cáo sẽ tốn rất nhiều thời gian và dễ gây ra sai sót. Macro không chỉ giúp giảm thiểu thời gian mà còn tăng độ chính xác, đồng thời mở ra khả năng tùy biến linh hoạt cho người dùng.

Trong bài viết này, chúng ta sẽ khám phá các macro hay trong Excel từ mức cơ bản đến nâng cao, cung cấp chi tiết cách tạo, chỉnh sửa và áp dụng chúng trong thực tiễn công việc. Bài viết được chia thành các phần chính:

1. Khái niệm và lợi ích của macro
2. Cách ghi lại macro cơ bản
3. Các macro phổ biến trong quản lý dữ liệu
4. Macro tự động báo cáo và tạo biểu đồ
5. Macro nâng cao với VBA: vòng lặp, điều kiện, và xử lý lỗi
6. Kỹ thuật tối ưu hoá macro để chạy nhanh hơn
7. Bảo mật macro và cách chia sẻ macro an toàn
8. Một số ví dụ thực tế và mẫu macro sẵn có

Mỗi phần sẽ đi sâu vào chi tiết, kèm theo đoạn mã mẫu, hướng dẫn từng bước và các lưu ý quan trọng. Đọc xong bài này, bạn sẽ có thể tự tin xây dựng và triển khai các macro hiệu quả, biến Excel thành công cụ mạnh mẽ hỗ trợ mọi công việc từ kế toán, tài chính, nhân sự tới phân tích dữ liệu.

Có thể bạn quan tâm: Các Hàm Excel Tính Lương: Hướng Dẫn Chi Tiết Từ A‑z Để Xây Dựng Bảng Lương Chuyên Nghiệp

1. Khái niệm và lợi ích của macro

1.1 Macro là gì?

Macro trong Excel là một chuỗi các hành động (như nhấn phím, di chuyển chuột, nhập dữ liệu, áp dụng công thức) được ghi lại hoặc viết bằng mã VBA. Khi chạy macro, Excel sẽ tự động thực hiện các hành động đó một cách nhanh chóng và nhất quán.

1.2 Lợi ích khi sử dụng macro

Có thể bạn quan tâm: Các Hàm Excel Kế Toán: Công Cụ Không Thể Thiếu Cho Mọi Kế Toán Viên

2. Cách ghi lại macro cơ bản

2.1 Bật tab “Developer”

1. Vào File → Options → Customize Ribbon.
2. Đánh dấu Developer trong danh sách bên phải và nhấn OK.

2.2 Ghi lại một macro đơn giản

1. Developer → Record Macro.
2. Đặt tên macro (không dấu cách, không ký tự đặc biệt), ví dụ: CopyPasteValues.
3. Chọn Store macro in: This Workbook hoặc Personal Macro Workbook.
4. Thực hiện các thao tác cần ghi (ví dụ: chọn cột A, sao chép, dán giá trị vào cột B).
5. Nhấn Stop Recording.

2.3 Xem và chỉnh sửa macro

• Developer → Visual Basic mở cửa sổ VBA.
• Trong Modules, tìm module chứa macro và mở để xem mã nguồn.

Sub CopyPasteValues() Columns("A:A").Copy Columns("B:B").PasteSpecial xlPasteValues Application.CutCopyMode = False
End Sub

2.4 Gán macro vào nút

1. Insert → Shapes chọn một hình (ví dụ hình chữ nhật).
2. Nhấp chuột phải → Assign Macro… → chọn CopyPasteValues.
3. Đặt tên nút và hoàn thành.

3. Các macro phổ biến trong quản lý dữ liệu

3.1 Macro lọc dữ liệu tự động

Giúp lọc bảng theo tiêu chí đa điều kiện mà không cần mở hộp thoại Filter.

Sub AutoFilterByCriteria() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("Data") With ws .AutoFilterMode = False .Range("A1:D1").AutoFilter Field:=2, Criteria1:=">1000" .Range("A1:D1").AutoFilter Field:=4, Criteria1:="=Active" End With
End Sub

3.2 Macro xóa các dòng trùng lặp

Xóa nhanh các bản ghi lặp lại dựa trên một hoặc nhiều cột.

Sub RemoveDuplicates() Dim ws As Worksheet Set ws = ActiveSheet ws.Range("A1").CurrentRegion.RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes
End Sub

3.3 Macro chia dữ liệu thành các file riêng

Thường dùng khi cần tách dữ liệu khách hàng theo khu vực, mỗi khu vực một file.

Sub SplitDataToWorkbooks() Dim ws As Worksheet, rng As Range, dict As Object Dim key As Variant, wbNew As Workbook, wsNew As Worksheet Set ws = ThisWorkbook.Sheets("Master") Set dict = CreateObject("Scripting.Dictionary") ' Giả sử cột C chứa khu vực For Each rng In ws.Range("C2:C" & ws.Cells(ws.Rows.Count, "C").End(xlUp).Row) If Not dict.Exists(rng.Value) Then dict.Add rng.Value, Nothing Next rng Application.ScreenUpdating = False For Each key In dict.Keys Set wbNew = Workbooks.Add Set wsNew = wbNew.Sheets(1) ws.Rows(1).Copy wsNew.Rows(1) ' sao chép tiêu đề ws.Range("A1").CurrentRegion.AutoFilter Field:=3, Criteria1:=key ws.Range("A2").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy wsNew.Range("A2") ws.AutoFilterMode = False wbNew.SaveAs Filename:=ThisWorkbook.Path & "\" & key & ".xlsx", FileFormat:=xlOpenXMLWorkbook wbNew.Close SaveChanges:=False Next key Application.ScreenUpdating = True MsgBox "Hoàn thành chia dữ liệu!"
End Sub

3.4 Macro tự động tính tổng và chèn công thức

Thêm công thức tổng cho cột cuối cùng của một bảng có số dòng thay đổi.

Sub InsertSumFormula() Dim ws As Worksheet, lastRow As Long, lastCol As Long Set ws = ActiveSheet lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column ws.Cells(lastRow + 1, lastCol).Formula = "=SUM(" & ws.Cells(2, lastCol).Address & ":" & ws.Cells(lastRow, lastCol).Address & ")" ws.Cells(lastRow + 1, lastCol).Font.Bold = True
End Sub

4. Macro tự động báo cáo và tạo biểu đồ

4.1 Tạo báo cáo tổng hợp hàng tháng

Giả sử có bảng giao dịch, macro sẽ lọc theo tháng, tính tổng doanh thu, và ghi vào sheet “Report”.

Sub MonthlyReport() Dim wsSrc As Worksheet, wsRpt As Worksheet Dim pt As PivotTable, pc As PivotCache Dim rng As Range, monthName As String Set wsSrc = ThisWorkbook.Sheets("Transactions") Set wsRpt = ThisWorkbook.Sheets("Report") wsRpt.Cells.Clear ' Xác định vùng dữ liệu Set rng = wsSrc.Range("A1").CurrentRegion ' Tạo PivotCache Set pc = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=rng) ' Tạo PivotTable Set pt = pc.CreatePivotTable(TableDestination:=wsRpt.Range("A3"), TableName:="MonthlyPivot") With pt .PivotFields("Date").Orientation = xlRowField .PivotFields("Date").NumberFormat = "mmmm yyyy" .PivotFields("Amount").Orientation = xlDataField .PivotFields("Amount").Function = xlSum .PivotFields("Amount").NumberFormat = "#,##0" End With wsRpt.Range("A1").Value = "Báo cáo doanh thu tháng" wsRpt.Range("A2").Value = "Tháng" wsRpt.Range("B2").Value = "Tổng doanh thu" MsgBox "Báo cáo đã được tạo tại sheet Report."
End Sub

4.2 Macro tạo biểu đồ tự động

Sau khi tạo PivotTable, chúng ta có thể nhanh chóng chèn biểu đồ cột.

Sub CreateChartFromPivot() Dim wsRpt As Worksheet, cht As ChartObject Set wsRpt = ThisWorkbook.Sheets("Report") Set cht = wsRpt.ChartObjects.Add(Left:=300, Top:=50, Width:=500, Height:=300) With cht.Chart .SetSourceData Source:=wsRpt.Range("A3").CurrentRegion .ChartType = xlColumnClustered .HasTitle = True .ChartTitle.Text = "Doanh thu theo tháng" .Axes(xlCategory).HasTitle = True .Axes(xlCategory).AxisTitle.Text = "Tháng" .Axes(xlValue).HasTitle = True .Axes(xlValue).AxisTitle.Text = "Doanh thu (VNĐ)" End With
End Sub

Kết hợp hai macro trên, chỉ cần một nút bấm, toàn bộ báo cáo và biểu đồ sẽ xuất hiện.

5. Macro nâng cao với VBA: vòng lặp, điều kiện, và xử lý lỗi

Có thể bạn quan tâm: Các Loại Biểu Đồ Trong Excel: Hướng Dẫn Toàn Diện Từ Cơ Bản Đến Nâng Cao

5.1 Vòng lặp For…Next

Dùng để duyệt qua các hàng, cột hoặc mảng.

Sub LoopThroughRows() Dim ws As Worksheet, i As Long, lastRow As Long Set ws = ActiveSheet lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row For i = 2 To lastRow If ws.Cells(i, "C").Value < 0 Then ws.Cells(i, "D").Value = "Nợ" Else ws.Cells(i, "D").Value = "Có" End If Next i
End Sub

5.2 Vòng lặp Do…Loop

Thích hợp khi không biết trước số lần lặp.

Sub DoLoopExample() Dim i As Long: i = 1 Do While Cells(i, "A").Value <> "" Cells(i, "B").Value = Cells(i, "A").Value 1.1 ' tăng 10% i = i + 1 Loop
End Sub

5.3 Cấu trúc If…Then…Else

If Range("A1").Value = "" Then MsgBox "Ô A1 trống!"
ElseIf IsNumeric(Range("A1").Value) Then MsgBox "Giá trị là số."
Else MsgBox "Giá trị không phải số."
End If

5.4 Xử lý lỗi với On Error

Sub SafeOpenFile() On Error GoTo ErrHandler Workbooks.Open Filename:="C:\Data\MissingFile.xlsx" Exit Sub ErrHandler: MsgBox "Không thể mở file: " & Err.Description, vbCritical
End Sub

5.5 Sử dụng Dictionary để tăng tốc độ

Dictionary (Scripting.Dictionary) giúp tra cứu nhanh hơn so với vòng lặp lồng nhau.

Sub CountUniqueValues() Dim dict As Object, rng As Range, cell As Range Set dict = CreateObject("Scripting.Dictionary") Set rng = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row) For Each cell In rng If Not dict.Exists(cell.Value) Then dict.Add cell.Value, 1 Next cell MsgBox "Có " & dict.Count & " giá trị duy nhất."
End Sub

6. Kỹ thuật tối ưu hoá macro để chạy nhanh hơn

1. Tắt ScreenUpdating
   vba Application.ScreenUpdating = False '... code ... Application.ScreenUpdating = True

2. Tắt Calculation tự động
   vba Application.Calculation = xlCalculationManual '... code ... Application.Calculation = xlCalculationAutomatic

3. Sử dụng Variant array để đọc/ghi dữ liệu
   Thay vì thao tác trên ô từng cái, đọc toàn bộ vùng vào mảng, xử lý trong bộ nhớ, rồi ghi lại một lần.

“`vba
Sub FastReadWrite()
Dim arr, i As Long, lastRow As Long
lastRow = Cells(Rows.Count, “A”).End(xlUp).Row
arr = Range(“A1:B” & lastRow).Value ‘ đọc vào mảng 2 chiều

 For i = 1 To UBound(arr, 1) arr(i, 2) = arr(i, 1) 2 ' ví dụ tính gấp đôi Next i Range("A1:B" & lastRow).Value = arr ' ghi lại

End Sub
“`

1. Tránh Select và Activate
   Thay vì Range("A1").Select → Selection.Copy, dùng trực tiếp Range("A1").Copy.

2. Giảm số lần gọi hàm WorksheetFunction
   Nếu cần tính tổng, trung bình,… hãy dùng vòng lặp trên mảng thay vì gọi WorksheetFunction.Sum nhiều lần.

7. Bảo mật macro và cách chia sẻ macro an toàn

7.1 Cấp độ bảo mật Macro

• Disable all macros without notification – không cho phép macro nào chạy.
• Disable all macros with notification – người dùng sẽ nhận cảnh báo và có thể bật macro.
• Disable all macros except digitally signed macros – chỉ cho phép macro đã ký số.
• Enable all macros – cho phép mọi macro, không an toàn.

Cài đặt tại File → Options → Trust Center → Trust Center Settings → Macro Settings.

7.2 Ký số macro

Để macro được tin cậy, bạn có thể ký số bằng chứng chỉ số:

1. Mở VB Editor → Tools → Digital Signature.
2. Chọn Choose → Create để tạo chứng chỉ tự ký (SelfCert) hoặc dùng chứng chỉ từ CA.
3. Lưu file, khi người dùng mở sẽ thấy macro đã ký và có thể bật tự động.

7.3 Chia sẻ macro dưới dạng Add‑in

1. File → Save As → chọn Excel Add‑in (.xlam).
2. Người dùng khác cài đặt bằng File → Options → Add‑ins → Excel Add‑ins → Browse.
3. Add‑in sẽ xuất hiện trong tab Add‑ins và có thể gán phím tắt.

7.4 Kiểm tra mã nguồn

Trước khi chạy macro từ nguồn không rõ, mở VB Editor và xem mã. Kiểm tra:

• Không có lệnh Kill, DeleteFile, Shell gọi chương trình ngoài.
• Không có vòng lặp vô hạn hoặc Application.OnTime để chạy mã độc.

8. Một số ví dụ thực tế và mẫu macro sẵn có

8.1 Mẫu macro “Copy‑Paste Values Only”

Sub CopyPasteValuesOnly() Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues Application.CutCopyMode = False
End Sub

8.2 Mẫu macro “Convert Text to Columns (Fixed Width)”

Sub TextToColumnsFixed() With Selection .TextToColumns Destination:=.Cells(1, 1), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), Array(10, 1), Array(20, 1)), _ TrailingMinusNumbers:=True End With
End Sub

8.3 Mẫu macro “Highlight Duplicates in Column”

Sub HighlightDuplicates() Dim rng As Range Set rng = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row) rng.FormatConditions.Delete rng.FormatConditions.AddUniqueValues rng.FormatConditions(rng.FormatConditions.Count).SetFirstPriority With rng.FormatConditions(1) .DupeUnique = xlDuplicate .Interior.Color = RGB(255, 199, 206) ' màu hồng nhạt End With
End Sub

8.4 Mẫu macro “Generate Random Passwords”

Function RandomPassword(ByVal length As Long) As String Dim chars As String, i As Long, pwd As String chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789!@#$%^&()" Randomize For i = 1 To length pwd = pwd & Mid(chars, Int(Rnd() Len(chars) + 1), 1) Next i RandomPassword = pwd
End Function Sub InsertRandomPasswords() Dim rng As Range, cell As Range Set rng = Range("B2:B" & Cells(Rows.Count, "A").End(xlUp).Row) For Each cell In rng cell.Value = RandomPassword(12) Next cell
End Sub

8.5 Mẫu macro “Send Email with Outlook”

Sub SendMail() Dim OutApp As Object, OutMail As Object Dim rng As Range, bodyText As String Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.CreateItem(0) Set rng = Sheets("Data").Range("A2:C5") ' dữ liệu muốn đính kèm bodyText = "Kính gửi," & vbCrLf & vbCrLf & "Xin gửi kèm bảng dữ liệu." & vbCrLf & vbCrLf With OutMail .To = "example@domain.com" .Subject = "Bảng dữ liệu yêu cầu" .Body = bodyText .Attachments.Add rng.Parent.FullName ' đính kèm file hiện tại .Display ' hoặc .Send để gửi ngay End With Set OutMail = Nothing Set OutApp = Nothing
End Sub

9. Tổng kết

Macro trong Excel không chỉ là công cụ tự động hoá mà còn là nền tảng để xây dựng giải pháp kinh doanh thông minh. Từ những macro đơn giản ghi lại thao tác, đến các hàm VBA phức tạp xử lý hàng triệu dòng dữ liệu, khả năng tùy biến và mở rộng là vô hạn. Khi áp dụng đúng cách, bạn sẽ:

• Tiết kiệm thời gian đáng kể cho các công việc lặp lại.
• Tăng độ chính xác và giảm thiểu sai sót con người.
• Tạo ra các báo cáo, biểu đồ tự động, hỗ trợ quyết định nhanh chóng.
• Chia sẻ kiến thức qua add‑in hoặc file macro, nâng cao năng lực đồng đội.

Hãy bắt đầu từ việc ghi lại một macro cơ bản, sau đó dần dần học cách viết VBA, tối ưu hoá và bảo mật. Khi đã nắm vững các macro hay trong Excel, bạn sẽ cảm nhận được sức mạnh thực sự của công cụ bảng tính này và có thể biến nó thành “cánh tay thứ ba” cho mọi công việc kinh doanh và phân tích dữ liệu. Chúc bạn thành công!

Tiến sĩ Lê Thống Nhất

Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.

Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.

Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.