VBA 宏编辑
一键转换净值与清除#N/A格式
下面集合了通过对B,C,D,E四列的格式转换，如把格式为空及公式匹配为#N/A的转换为空，将公式转换的值转为净值的操作，同时包含有对转换后标题样式及内容区域样式的设置，如标题是微软雅黑加粗11号字体且垂直居中，区域内容为微软雅黑不加粗10号字体且垂直居中。
Sub 一键处理JKLM()Dim ws As WorksheetSet ws = ActiveSheetDim lastRowB As Long, lastRowC As LongDim lastRowD As Long, lastRowE As LonglastRowB = ws.Cells(ws.Rows.Count, "B").End(xlUp).RowlastRowC = ws.Cells(ws.Rows.Count, "C").End(xlUp).RowlastRowD = ws.Cells(ws.Rows.Count, "D").End(xlUp).RowlastRowE = ws.Cells(ws.Rows.Count, "E").End(xlUp).RowIf lastRowB < 2 Then lastRowB = 2If lastRowC < 2 Then lastRowC = 2If lastRowD < 2 Then lastRowD = 2If lastRowE < 2 Then lastRowE = 2' ===== 列标题 =====With ws.Range("J1:M1").Value = Array("物料编码", "转化SKU", "转化项目号", "转化95码").Font.Name = "Microsoft YaHei".Font.Size = 11.Font.Bold = True.HorizontalAlignment = xlCenter.VerticalAlignment = xlCenterEnd With' ===== 设置列宽 =====ws.Columns("J:M").ColumnWidth = 12.8' ===== J 列（依赖 B）=====With ws.Range("J2:J" & lastRowB).Formula = "=IF(ISNA(B2),"""",IF(B2="""","""",B2))".Value = .ValueEnd With' ===== K 列（依赖 C）=====With ws.Range("K2:K" & lastRowC).Formula = "=IF(ISNA(C2),"""",IF(C2="""","""",C2))".Value = .ValueEnd With' ===== L 列（依赖 D）=====With ws.Range("L2:L" & lastRowD).Formula = "=IF(ISNA(D2),"""",IF(LEN(D2)=4,D2&""J"",LEFT(D2,5)))".Value = .ValueEnd With' ===== M 列（依赖 E）=====With ws.Range("M2:M" & lastRowE).Formula = "=IF(ISNA(E2),"""",IF(E2="""","""",E2))".Value = .ValueEnd With' ===== 内容区域样式 =====With ws.Range("J2:M" & Application.Max(lastRowB, lastRowC, lastRowD, lastRowE)).Font.Name = "Microsoft YaHei".Font.Size = 10.Font.Bold = False.HorizontalAlignment = xlCenter.VerticalAlignment = xlCenterEnd WithEnd Sub
一键表格为三线表(带内部虚线版)
VB宏编辑器
Sub 企业级三线表()Dim tbl As TableFor Each tbl In ActiveDocument.TablesOn Error Resume Next'=====================' 自动适应页面宽度'=====================tbl.AutoFitBehavior wdAutoFitWindow'=====================' 尝试平均分配列宽'=====================tbl.AllowAutoFit = Falsetbl.Columns.DistributeWidth'=====================' 水平居中'=====================tbl.Range.ParagraphFormat.Alignment = wdAlignParagraphCenter'=====================' 垂直居中'=====================tbl.Range.Cells.VerticalAlignment = wdCellAlignVerticalCenter'=====================' 行高自动'=====================tbl.Rows.HeightRule = wdRowHeightAuto'=====================' 清除所有边框'=====================tbl.Borders.Enable = False'=====================' 顶线（1.5磅）'=====================With tbl.Borders(wdBorderTop).LineStyle = wdLineStyleSingle.LineWidth = wdLineWidth150ptEnd With'=====================' 底线（1.5磅）'=====================With tbl.Borders(wdBorderBottom).LineStyle = wdLineStyleSingle.LineWidth = wdLineWidth150ptEnd With'=====================' 内部横线（点状虚线）'=====================With tbl.Borders(wdBorderHorizontal).LineStyle = wdLineStyleDot.LineWidth = wdLineWidth050ptEnd With'=====================' 内部竖线（点状虚线）'=====================With tbl.Borders(wdBorderVertical).LineStyle = wdLineStyleDot.LineWidth = wdLineWidth050ptEnd With'=====================' 去掉左右边框'=====================tbl.Borders(wdBorderLeft).LineStyle = wdLineStyleNonetbl.Borders(wdBorderRight).LineStyle = wdLineStyleNone'=====================' 栏目线（第一行下边框）'=====================With tbl.Rows(1).Borders(wdBorderBottom).LineStyle = wdLineStyleSingle.LineWidth = wdLineWidth075ptEnd WithOn Error GoTo 0Next tblMsgBox "企业报告表格样式处理完成！", vbInformationEnd Sub
WPS宏编辑器
function 标准三线表() {for (let 表格 of ActiveDocument.Tables) {// 自动适应页面表格.AutoFitBehavior(2);// 平均分布列宽表格.Columns.DistributeWidth();// 水平居中表格.Range.ParagraphFormat.Alignment = 1;// 垂直居中表格.Range.Cells.VerticalAlignment = 1;// 行高自动表格.Rows.Height = 0;// 字体表格.Range.Font.Name = "宋体";表格.Range.Font.Size = 10.5;// 表头加粗if (表格.Rows.Count >= 1) {表格.Rows(1).Range.Bold = true;}// 清除所有边框表格.Borders.Enable = false;// ========= 顶线（1.5磅） =========表格.Borders.Item(-1).LineStyle = 1;表格.Borders.Item(-1).LineWidth = 12;// ========= 底线（1.5磅） =========表格.Borders.Item(-3).LineStyle = 1;表格.Borders.Item(-3).LineWidth = 12;// ========= 栏目线（0.75磅） =========if (表格.Rows.Count >= 1) {表格.Rows(1).Borders.Item(-3).LineStyle = 1;表格.Rows(1).Borders.Item(-3).LineWidth = 6;}// 去除左右边框表格.Borders.Item(-2).LineStyle = 0;表格.Borders.Item(-4).LineStyle = 0;}
}