如何使用xlsxwriter按指定列拆分Excel并保留原格式与计算公式

方案1：VBA宏（最适配保留格式、公式的需求） #

该方案可以1:1还原原表的单元格格式、条件格式、公式、图表、数据验证规则等所有属性，是满足需求的最优选择。

• 打开你的原Excel文件，按Alt + F11调出VBA编辑器
• 右键点击左侧面板中你的工作簿名称，选择「插入」-「模块」
• 将下方代码粘贴到模块窗口，按需修改代码开头标注的自定义参数：拆分依据的列号（A列为1、B列为2，以此类推）、表头占用行数

Sub 按列拆分工作簿()
    Dim 原表 As Worksheet, 新表 As Worksheet
    Dim 拆分列 As Integer, 表头行数 As Integer, 最后一行 As Long, 最后一列 As Integer
    Dim 筛选值 As String, 保存路径 As String
    Dim 字典 As Object, 单元格 As Range
    
    ' **************** 自定义参数修改区 ****************
    拆分列 = 2 ' 示例为按第2列（B列）拆分，修改为你需要的列号
    表头行数 = 1 ' 示例表头占1行，有多行表头可修改为对应数值
    ' *************************************************
    
    Set 原表 = ActiveSheet
    最后一行 = 原表.Cells(Rows.Count, 拆分列).End(xlUp).Row
    最后一列 = 原表.Cells(表头行数, Columns.Count).End(xlToLeft).Column
    保存路径 = ThisWorkbook.Path & "\"
    Set 字典 = CreateObject("Scripting.Dictionary")
    
    ' 提取拆分列所有非重复值
    For Each 单元格 In 原表.Range(原表.Cells(表头行数 + 1, 拆分列), 原表.Cells(最后一行, 拆分列))
        If 单元格.Value <> "" And Not 字典.exists(单元格.Value) Then
            字典.Add 单元格.Value, 1
        End If
    Next 单元格
    
    ' 关闭屏幕更新提升运行速度
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    ' 遍历生成独立文件
    For Each 筛选值 In 字典.keys
        原表.Copy
        Set 新表 = ActiveWorkbook.Sheets(1)
        ' 筛选并删除非目标数据
        新表.Range(新表.Cells(表头行数, 拆分列), 新表.Cells(最后一行, 拆分列)).AutoFilter Field:=1, Criteria1:="<>" & 筛选值
        新表.Range(新表.Cells(表头行数 + 1, 1), 新表.Cells(最后一行, 最后一列)).SpecialCells(xlCellTypeVisible).Delete
        新表.AutoFilterMode = False
        ' 保存文件，文件名为拆分列对应值
        ActiveWorkbook.SaveAs Filename:=保存路径 & 筛选值 & ".xlsx", FileFormat:=xlOpenXMLWorkbook
        ActiveWorkbook.Close
    Next 筛选值
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True
    MsgBox "拆分完成！文件已保存到原文件所在文件夹。"
End Sub

• 按F5运行代码即可，拆分生成的文件会自动保存在原Excel所属文件夹中。
  注意：如果原文件包含宏代码，可将代码中FileFormat参数改为xlOpenXMLWorkbookMacroEnabled，对应文件后缀为.xlsm

方案2：Power Query 手动适配（适合无代码基础的场景） #

默认Power Query导出仅保留数值，需要额外操作实现格式和公式保留：

• 选中原表数据区域，点击「数据」选项卡-「从表格/区域」加载到Power Query编辑器
• 选中拆分依据的列，点击「转换」选项卡-「分组依据」，分组方式选择「所有行」，得到每个唯一值对应的数据组
• 关闭Power Query编辑器，将分组结果加载到新工作表
• 用格式刷将原表的格式、公式批量应用到每个拆分后的数据表，再单独保存为独立文件即可。

如果你不需要保留公式仅需保留格式，可以直接用Power Query的「拆分列-拆分为行」+「加载到不同工作表」功能实现，操作门槛更低。

拆分效果示例 #

内容的提问来源于stack exchange，提问作者Elle Li