ARTICLE · 1173121
2026年10月13日:Excel宏与VBA入门,5案例日省2小时
导语：为什么你还在手动做那些重复的工作？
职场中有这样一个残酷的现实：大多数职场人每天花费在Excel上的2-3小时中，有70%以上是在做重复性操作。据一项针对办公室白领的调研显示，平均每个员工每周要完成约150次鼠标点击和键盘输入操作，其中绝大多数都是机械性的重复动作。
你是否也曾陷入这样的困境？
• 每天都要把几十个Excel文件的数据汇总到一张表，手动复制粘贴到崩溃
• 每周要给不同区域的负责人发送格式完全相同的报表，只是数据不同
• 每月要做上百张发票，每张都要填写相同的抬头和账户信息
• 每次数据更新后，都要手动刷新数据透视表、重新设置格式、更新图表
• 面对杂乱的数据，需要批量清洗、去重、格式化，却只能一行行手动处理
如果你有以上任何一个痛点，说明你迫切需要学习Excel宏和VBA。
本文将带你从零掌握Excel宏的录制、VBA编辑器的使用，以及5个可直接复制使用的自动化案例。学完本文，你每天至少可以节省2小时重复工作时间。
---
一、Excel宏基础认知
1.1 什么是Excel宏？
宏（Macro）是Excel中一系列命令和指令的集合，它能够自动化执行重复性任务。你可以把宏理解为Excel的"录音机"——你操作一遍，Excel记录下来，之后一键回放，自动完成同样的操作。
宏的核心价值：
• 将数小时的重复工作压缩到几秒钟
• 消除人为操作失误
• 标准化工件流程
• 实现Excel本身不具备的自定义功能
1.2 什么是VBA？
VBA（Visual Basic for Applications）是内置在Microsoft Office中的编程语言。相比录制的宏，VBA可以：
• 实现更复杂的逻辑判断
• 循环处理大量数据
• 与用户交互（输入框、消息框）
• 调用外部程序和文件
• 创建自定义函数和界面
简单理解：录制宏是"傻瓜式"操作，而VBA是"编程式"操作。录制宏能解决60%的自动化需求，VBA则能解决剩余40%的复杂需求。
1.3 宏的启用与安全设置
Excel 2007-2016：
点击【文件】→【选项】
选择【信任中心】→【信任中心设置】
选择【宏设置】
选择"禁用宏并发出通知"或"启用宏"
Excel 2016/2019/365：
【文件】→【选项】→【信任中心】→【信任中心设置】
【宏设置】→选择"禁用宏并发出通知"
打开文件时选择"启用内容"（如果是可信文件）
推荐设置：选择"禁用宏并发出通知"，这样可以手动控制宏的启用。
---
二、录制宏：最简单的方式
2.1 录制宏的基本步骤
操作流程：
准备阶段
打开Excel
点击【开发工具】选项卡（如果没有，需要先添加）
【开发工具】→【录制宏】
录制设置
宏名称：输入有意义的名称（如"格式化销售表"）
快捷键：设置Ctrl+字母组合（如Ctrl+Shift+S）
保存位置：选择保存到"当前工作簿"或"个人宏工作簿"
说明：添加简要描述
执行操作
按照正常方式操作Excel
所有操作都会被记录
停止录制
【开发工具】→【停止录制】
2.2 添加开发工具选项卡
如果Excel菜单栏没有"开发工具"，按以下步骤添加：
Excel 2016/2019/365：
右键功能区 →【自定义功能区和快速访问工具栏】
左侧选择【自定义功能区】
勾选【开发工具】
点击【确定】
2.3 录制宏实战案例：批量格式化报表
场景：每周都要将原始销售数据表格式化为标准报表格式
手动操作步骤（录制前先想好）：
设置标题行字体为微软雅黑、14号、加粗
设置标题行填充色为深蓝色、字体为白色
设置数据区域边框为所有边框
设置金额列为货币格式
设置日期列格式为yyyy-mm-dd
自动调整列宽
冻结首行
录制过程：
【开发工具】→【录制宏】
宏名：FormatSalesReport
快捷键：Ctrl+Shift+S
执行上述7个操作
【停止录制】
使用效果：
• 以后只需按Ctrl+Shift+S，即可一键完成格式化
• 注意：录制宏前先想好操作顺序，中途不要做无关操作
2.4 录制宏的局限性
---
三、VBA编辑器使用指南
3.1 打开VBA编辑器
方法一：按 Alt + F11
方法二：【开发工具】→【Visual Basic】
方法三：右键工作表标签 →【查看代码】
3.2 VBA编辑器界面
┌─────────────────────────────────────────────────┐
│ 菜单栏（文件/编辑/视图/插入/格式/调试/运行/工具）│
├─────────────────────────────────────────────────┤
│ 工具栏（标准/编辑/用户窗体等） │
├───────────────┬─────────────────────────────────┤
│ │ │
│ 项目资源管理器 │ 代码窗口 │
│ (显示工作簿/ │ (编写和查看VBA代码) │
│ 工作表对象) │ │
│ │ │
├───────────────┼─────────────────────────────────┤
│ 属性窗口 │ 立即窗口 │
│ (显示对象属性) │ (快速测试代码/输出结果) │
│ │ │
└───────────────┴─────────────────────────────────┘
3.3 VBA代码基本结构
Sub 程序名称()
'这里是注释，解释代码功能
Dim 变量名 As 数据类型
'代码内容
Range("A1").Value = "Hello"
End Sub
3.4 VBA常用语法速查
---
四、5个可直接复制使用的VBA代码
案例1：批量格式化——自动美化销售报表
适用场景：每周/月需要将原始数据格式化为标准报表
VBA代码：
Sub FormatSalesReport()
'批量格式化销售报表
'使用方法：Alt+F8，选择FormatSalesReport，点击运行
Dim ws As Worksheet
Dim lastRow As Long
Dim lastCol As Long
'获取当前工作表
Set ws = ActiveSheet
'获取最后一行和最后一列
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
'设置标题行格式
With ws.Range(ws.Cells(1, 1), ws.Cells(1, lastCol))
.Font.Name = "微软雅黑"
.Font.Size = 12
.Font.Bold = True
.Font.Color = RGB(255, 255, 255)
.Interior.Color = RGB(0, 112, 192)
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
'设置数据区域格式
With ws.Range(ws.Cells(2, 1), ws.Cells(lastRow, lastCol))
.Font.Name = "微软雅黑"
.Font.Size = 10
.Borders.LineStyle = xlContinuous
.Borders.Weight = xlThin
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
'设置金额列格式（第5列示例）
ws.Columns(5).NumberFormat = "¥#,##0.00"
'设置日期列格式（第3列示例）
ws.Columns(3).NumberFormat = "yyyy-mm-dd"
'自动调整列宽
ws.Columns("A:Z").AutoFit
'冻结首行
ws.Activate
ActiveWindow.FreezePanes = True
MsgBox "报表格式化完成！", vbInformation, "提示"
End Sub
参数说明：
• lastRow：自动检测最后一行
• RGB(0, 112, 192)：深蓝色，可根据品牌色调整
• ws.Columns(5)：第5列为金额列，可根据实际调整
• ws.Columns(3)：第3列为日期列，可根据实际调整
案例2：自动汇总——批量合并多个工作表
适用场景：每月汇总各部门/分公司的Excel数据
VBA代码：
Sub MergeAllSheets()
'合并当前工作簿中所有工作表的数据到"汇总"表
'使用方法：Alt+F8，选择MergeAllSheets，点击运行
Dim ws As Worksheet
Dim destWS As Worksheet
Dim destRng As Range
Dim srcRng As Range
Dim sheetCount As Integer
Dim dataStartRow As Long
Dim i As Long
Application.ScreenUpdating = False
'在最后创建汇总表
On Error Resume Next
Set destWS = Worksheets("汇总")
If destWS Is Nothing Then
Set destWS = Worksheets.Add(After:=Worksheets(Worksheets.Count))
destWS.Name = "汇总"
Else
destWS.Cells.Clear
End If
On Error GoTo 0
dataStartRow = 2 '数据起始行（跳过标题）
sheetCount = 0
'遍历所有工作表
For Each ws In Worksheets
If ws.Name <> "汇总" Then
sheetCount = sheetCount + 1
'复制表头（仅第一次）
If sheetCount = 1 Then
ws.Range("A1").CurrentRegion.Rows(1).Copy _
Destination:=destWS.Range("A1")
End If
'复制数据
Dim lastRow As Long
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
If lastRow >= dataStartRow Then
Set srcRng = ws.Range(ws.Cells(dataStartRow, 1), _
ws.Cells(lastRow, ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column))
Set destRng = destWS.Cells(destWS.Rows.Count, 1).End(xlUp).Offset(1, 0)
srcRng.Copy Destination:=destRng
End If
End If
Next ws
'添加序号列
destWS.Columns(1).Insert
For i = 2 To destWS.Cells(destWS.Rows.Count, 2).End(xlUp).Row - 1
destWS.Cells(i + 1, 1).Value = i - 1
Next i
destWS.Columns("A:Z").AutoFit
Application.ScreenUpdating = True
MsgBox "已完成！共合并了 " & sheetCount & " 个工作表的数据。", vbInformation, "合并完成"
End Sub
实战应用：
• 将多个部门发来的Excel文件合并到一个工作簿
• 统一格式后运行此代码
• 快速生成月度汇总报表
案例3：邮件发送——批量发送个性化邮件
适用场景：每月向客户/员工发送个性化邮件和附件
前置要求：
需要Outlook客户端
Outlook需要保持登录状态
表格中包含：邮箱地址、姓名、附件路径等列
VBA代码：
Sub SendMassEmails()
'批量发送个性化邮件
'表格格式：A列=邮箱，B列=姓名，C列=附件路径
Dim outlookApp As Object
Dim outlookMail As Object
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim recipientEmail As String
Dim recipientName As String
Dim attachmentPath As String
Set ws = ActiveSheet
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
'创建Outlook对象
On Error Resume Next
Set outlookApp = CreateObject("Outlook.Application")
On Error GoTo 0
If outlookApp Is Nothing Then
MsgBox "无法创建Outlook对象，请确保已安装Outlook", vbCritical
Exit Sub
End If
Application.ScreenUpdating = False
For i = 2 To lastRow '假设第1行是标题
recipientEmail = ws.Cells(i, 1).Value 'A列：邮箱
recipientName = ws.Cells(i, 2).Value 'B列：姓名
attachmentPath = ws.Cells(i, 3).Value 'C列：附件路径
'创建邮件
Set outlookMail = outlookApp.CreateItem(0)
With outlookMail
.To = recipientEmail
.Subject = "【重要】" & recipientName & "，您的月度报表已生成"
.Body = "您好，" & recipientName & vbCrLf & vbCrLf & _
"请查收附件中的月度报表。" & vbCrLf & vbCrLf & _
"如有任何问题，请回复此邮件。" & vbCrLf & vbCrLf & _
"祝好！"
'添加附件
If attachmentPath <> "" Then
.Attachments.Add attachmentPath
End If
'发送邮件
.Send
'在Excel中标记已发送
ws.Cells(i, 4).Value = "已发送"
ws.Cells(i, 4).Interior.Color = RGB(0, 176, 80)
End With
Set outlookMail = Nothing
'间隔2秒，避免被识别为垃圾邮件
Application.Wait (Now + TimeValue("0:00:02"))
Next i
Application.ScreenUpdating = True
MsgBox "邮件发送完成！", vbInformation, "批量发送"
End Sub
安全提示：
• 首次运行会弹出Outlook安全授权提示
• 建议先在小范围测试
• 批量发送建议添加时间间隔
案例4：数据清洗——自动去重和标准化
适用场景：清洗从系统导出的杂乱数据
VBA代码：
Sub CleanData()
'数据清洗自动化
'功能：去除空格、统一格式、删除重复项、填充空值
Dim ws As Worksheet
Dim lastRow As Long
Dim lastCol As Long
Dim i As Long
Dim j As Long
Set ws = ActiveSheet
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
'1. 去除首尾空格
For i = 1 To lastRow
For j = 1 To lastCol
If ws.Cells(i, j).Value <> "" Then
ws.Cells(i, j).Value = Trim(ws.Cells(i, j).Value)
End If
Next j
Next i
'2. 统一手机号格式（11位数字格式化为138-xxxx-xxxx）
For i = 2 To lastRow
For j = 1 To lastCol
If ws.Cells(i, j).NumberFormat = "@" Or Len(ws.Cells(i, j).Value) = 11 Then
If Len(Replace(ws.Cells(i, j).Value, "-", "")) = 11 Then
Dim phoneNum As String
phoneNum = Replace(ws.Cells(i, j).Value, "-", "")
phoneNum = Replace(phoneNum, " ", "")
If IsNumeric(phoneNum) Then
ws.Cells(i, j).Value = Left(phoneNum, 3) & "-" & _
Mid(phoneNum, 4, 4) & "-" & _
Right(phoneNum, 4)
End If
End If
End If
Next j
Next i
'3. 填充空白单元格
For j = 1 To lastCol
For i = 2 To lastRow
If ws.Cells(i, j).Value = "" And i > 2 Then
If ws.Cells(i - 1, j).Value <> "" Then
ws.Cells(i, j).Value = ws.Cells(i - 1, j).Value
End If
End If
Next i
Next j
'4. 删除重复项
ws.Range("A1").CurrentRegion.RemoveDuplicates Columns:=1, Header:=xlYes
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
MsgBox "数据清洗完成！", vbInformation, "提示"
End Sub
案例5：自动生成报表——一键生成多Sheet工作簿
适用场景：月底自动生成各部门汇总报表
VBA代码：
Sub GenerateMonthlyReport()
'自动生成月度报表
'包含：汇总表、各部门明细、图表
Dim wb As Workbook
Dim ws As Worksheet
Dim destWS As Worksheet
Dim deptNames As Variant
Dim deptName As Variant
Dim lastRow As Long
Dim reportDate As String
Application.ScreenUpdating = False
Set wb = ThisWorkbook
Set ws = wb.Sheets("原始数据")
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
reportDate = Format(Date, "yyyy年mm月")
'部门列表
deptNames = Array("销售部", "市场部", "技术部", "财务部", "人事部")
'删除旧报表（如果存在）
Application.DisplayAlerts = False
On Error Resume Next
For Each ws In wb.Sheets
If ws.Name <> "原始数据" Then ws.Delete
Next ws
On Error GoTo 0
Application.DisplayAlerts = True
'1. 创建汇总表
Set destWS = wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count))
destWS.Name = "月度汇总"
ws.Range("A1:D1).Copy Destination:=destWS.Range("A1")
destWS.Range("A1:D1").Font.Bold = True
'复制汇总数据
ws.Range("A2:D" & lastRow).Copy
destWS.Range("A2").PasteSpecial xlPasteValues
'2. 为每个部门创建明细表
For Each deptName In deptNames
Set destWS = wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count))
destWS.Name = deptName
'复制表头
ws.Range("A1:D1").Copy Destination:=destWS.Range("A1")
destWS.Range("A1:D1").Font.Bold = True
'筛选并复制数据（部门列假设在B列）
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
Dim rowIndex As Long
rowIndex = 2
For i = 2 To lastRow
If ws.Cells(i, 2).Value = deptName Then
ws.Range("A" & i & ":D" & i).Copy
destWS.Range("A" & rowIndex).PasteSpecial xlPasteValues
rowIndex = rowIndex + 1
End If
Next i
destWS.Columns("A:D").AutoFit
'添加合计行
destWS.Cells(rowIndex, 1).Value = "合计"
destWS.Cells(rowIndex, 1).Font.Bold = True
destWS.Cells(rowIndex, 4).Formula = "=SUM(D2:D" & rowIndex - 1 & ")"
Next deptName
'3. 生成图表
Set destWS = wb.Sheets("月度汇总")
destWS.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=destWS.Range("A1:D" & destWS.Cells(destWS.Rows.Count, 1).End(xlUp).Row)
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="数据分析图"
Application.ScreenUpdating = True
Application.CutCopyMode = False
MsgBox reportDate & "报表生成完成！共创建 " & (UBound(deptNames) + 2) & " 个工作表。", _
vbInformation, "报表生成"
End Sub
---
五、VBA代码进阶技巧
5.1 错误处理
Sub SafeCode()
On Error GoTo ErrorHandler
'你的代码
Dim ws As Worksheet
Set ws = Worksheets("不存在的表")
Exit Sub
ErrorHandler:
MsgBox "发生错误：" & Err.Description, vbCritical
Resume Next '或Exit Sub
End Sub
5.2 屏幕刷新控制
Application.ScreenUpdating = False '关闭屏幕刷新，提升速度
Application.Calculation = xlCalculationManual '关闭自动计算
Application.EnableEvents = False '关闭事件触发
'你的代码...
Application.ScreenUpdating = True '恢复
Application.Calculation = xlCalculationAutomatic '恢复
Application.EnableEvents = True '恢复
5.3 调试技巧
---
六、常见错误与避坑指南
错误1：录制的宏无法跨文件使用
错误2：代码运行报运行时错误
错误3：代码运行缓慢
错误4：宏保存后丢失
---
七、素材工具推荐
制作专业的Excel自动化报表，除了掌握VBA编程，还需要精美的模板和素材支持。
推荐使用 [畅榴云]() 获取高质量的Excel仪表盘模板和数据可视化素材。该平台提供超过1000+专业的报表模板，包含销售分析、财务管理、项目跟踪等多种场景。所有模板都采用现代设计风格，图表配色科学合理，配合VBA自动化使用，可以让你的报表既专业又高效。鎏云 让你的Excel自动化更加完美。
---
总结
Excel宏和VBA是提升办公效率的终极武器。本文介绍了5个可直接复制使用的自动化案例：
记住这三点建议：
先录制再优化：先用录制功能实现基本需求，再根据需要改写为VBA代码
添加错误处理：生产环境使用的代码必须添加错误处理，防止意外中断
善用屏幕刷新控制：大循环中关闭屏幕刷新可提升10倍以上速度
更多高质量创意插画素材，尽在 [畅榴云]() —— 让创意触手可及。
🌟 加关注获取每日资源推荐
畅榴云平台为你提供海量素材每日更新各类精选资源推荐让创作更高效，让灵感不间断
💡 温馨提示
更多精彩内容，欢迎关注我们觉得有用请点个「在看」支持一下吧~
