Excel办公自动化实战手册：VBA脚本与函数速查

Excel办公自动化实战手册20个即用脚本·覆盖函数/数据清洗/VBA宏·附可运行代码适用人群：行政、人事、财务、采购、运营等需要高频处理表格的职场人。本手册所有代码均经实测可直接运行；示例数据为虚构，请按需替换列名与区域。第一章Excel自动化的三种武器函数与动态数组：适合“一次性、结果随数据变化”的计算，改数据即改结果。数据透视表：适合“分组汇总”，3秒出报表，无需写公式。VBA宏：适合“重复动作固定”的批量操作，一键完成几十步。决策口诀：结果要更新→用函数；要看汇总→透视表；动作要重复→录宏改宏。第二章高频函数速查手册2.1查找与匹配需求推荐函数示例按编号查名称XLOOKUP=XLOOKUP(A2,编号列,名称列,"未找到")兼容旧版VLOOKUP=VLOOKUP(A2,$E$2:$F$99,2,0)双向查找INDEX+MATCH=INDEX(B1:Z1,MATCH(A2,A1:A99,0))2.2条件统计需求函数示例多条件求和SUMIFS=SUMIFS(金额,部门,"采购部",月份,9)多条件计数COUNTIFS=COUNTIFS(状态,"已完成",负责人,"张三")多条件均值AVERAGEIFS=AVERAGEIFS(单价,类别,"耗材")2.3文本与日期TEXTJOIN("、",TRUE,A2:C2)——合并并忽略空值。TRIM+SUBSTITUTE(A2,"","")——清除多余空格。LEFT/RIGHT/MID/TEXTSPLIT——截取与分列。DATEDIF(入职日,TODAY(),"Y")——计算工龄（年）。EOMONTH(TODAY(),-1)+1——上月初；用于月度报表自动取区间。2.4动态数组（Excel365/2021）=UNIQUE(区域)一键去重；=SORT(区域,1,-1)按第1列降序。=FILTER(区域,条件)按条件筛选，结果自动扩展。=SORT(UNIQUE(FILTER(A2:A999,A2:A999<>"")),1,1)——去重+排序+剔除空白。第三章10个VBA实战脚本使用方式：按Alt+F11打开VBE→插入模块→粘贴代码→回表按Alt+F8运行。3.1一键合并当前工作簿所有工作表Sub合并所有工作表()DimwsAsWorksheet,tgtAsWorksheet,rAsLongSettgt=Worksheets.Add(Before:=Worksheets(1))tgt.Name="合并结果"ForEachwsInThisWorkbook.WorksheetsIfws.Name<>"合并结果"Thenr=tgt.Cells(tgt.Rows.Count,1).End(xlUp).Row+1ws.UsedRange.Copytgt.Cells(r,1)EndIfNextwsMsgBox"合并完成！"EndSub3.2按某列拆分为多个工作表Sub按列拆分工作表()DimdAsObject,cAsRange,k$,wsAsWorksheetSetd=CreateObject("Scripting.Dictionary")ForEachcInRange("A2:A"&Cells(Rows.Count,1).End(xlUp).Row)Ifc.Value<>""Thend(c.Value)=""NextcForEachkInd.keysSetws=Worksheets.Add(After:=Worksheets(Worksheets.Count))ws.Name=kRange("A1").CurrentRegion.AutoFilter1,kRange("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copyws.Range("A1")NextkRange("A1").CurrentRegion.AutoFilterMsgBox"拆分完成，共"&d.Count&"个子表"EndSub3.3批量重命名工作表（按A列名称）Sub批量重命名()DimiAsLongFori=1To20IfCells(i,1).Value<>""Andi<=Worksheets.CountThenWorksheets(i).Name=Cells(i,1).ValueEndIfNextiEndSub3.4一键删除所有空行Sub删除空行()DimiAsLongFori=Cells(Rows.Count,1).End(xlUp).RowTo1Step-1IfApplication.WorksheetFunction.CountA(Rows(i))=0ThenRows(i).DeleteNextiEndSub3.5一键导出为PDFSub导出PDF()Dimp$p=ThisWorkbook.Path&"\"&ActiveSheet.Name&".pdf"ActiveSheet.ExportAsFixedFormatType:=xlTypePDF,Filename:=pMsgBox"已导出："&pEndSub3.6按条件批量标色（金额>10000标红）Sub条件标色()DimcAsRangeForEachcInRange("C2:C"&Cells(Rows.Count,3).End(xlUp).Row)IfIsNumeric(c.Value)Andc.Value>10000Thenc.Interior.Color=RGB(255,199,206)NextcEndSub3.7批量把公式转为数值（防泄露公式）Sub公式转数值()ActiveSheet.UsedRange.Value=ActiveSheet.UsedRange.ValueMsgBox"已全部转为数值"EndSub3.8批量发送邮件（需Outlook）Sub批量发邮件()DimiAsLong,olAsObject,mAsObjectSetol=CreateObject("Outlook.Application")Fori=2ToCells(Rows.Count,1).End(xlUp).RowSetm=ol.CreateItem(0)m.To=Cells(i,1).Valuem.Subject="月度对账单"m.Body="您好，"&Cells(i,2).Value&"，请查收附件。"m.SendNextiEndSub3.9自动生成带超链接的目录页Sub生成目录()DimwsAsWorksheet,tgtAsWorksheet,rAsLongSettgt=Worksheets.Add(Before:=Worksheets(1)):tgt.Name="目录"r=2ForEachwsInThisWorkbook.WorksheetsIfws.Name<>"目录"Thentgt.Hyperlinks.AddAnchor:=tgt.Cells(r,1),Address:="",SubAddress:="'"&ws.Name&"'!A1",TextToDisplay:=ws.Namer=r+1EndIfNextwsEndSub3.10数据清洗：去重+去空格+统一格式Sub数据清洗()DimrgAsRangeSetrg=ActiveSheet.UsedRangerg.ReplaceWhat:="",Replacement:="",LookAt:=xlPartrg.RemoveDuplicatesColumns:=Array(1),Header:=xlYesrg.Columns.AutoFitMsgBox"清洗完成"EndSub第四章实战案例：月度考勤汇总自动化场景：每月需把30张“部门打卡表”合并、清洗、统计迟到次数并出汇总。第1步：用脚本3.1合并所有工作表；第2步：用脚本3.10去重去空格，统一姓名格式；第3步：用COUNTIFS统计每人迟到次数：=COUNTIFS(姓名列,A2,状态列,"迟到")；第4步：用数据透视表按部门汇总