KutoolsforOffice — 一套方案，五大工具。事半功倍。

Excel 技巧：分割数据至多个工作表 / 根据列值拆分工作簿

作者Xiaoyang修改日期

在 Excel 中管理大型数据集时，根据指定列的值将数据拆分到多个工作表中具有显著优势——不仅能大幅提升数据的条理性，还能增强可读性，让数据分析更加高效便捷。

假设您有一份包含多项记录的大型销售数据，例如产品名称和第一季度销量。目标是根据每个产品名称将数据拆分为单独的工作表，以便分别分析各产品的销售表现。

根据列值分割数据至多个工作表

使用 VBA 代码将分割数据根据列值拆分为多个工作簿

根据列值将数据拆分到多个工作表

根据列值分割数据至多个工作表

通常，您可以先对数据列表进行排序，再逐个复制粘贴到其他新工作表中。但这种方法需要反复操作，费时又繁琐。本节将为您介绍两种简单高效的方法，助您在 Excel 中快速完成任务，节省宝贵时间并降低出错风险。

使用 VBA 代码根据列值分割数据至多个工作表

1. 按住 ALT + F11 键，即可打开 Microsoft Visual Basic for Applications 窗口。

2. 单击插入> 模块，然后将以下代码粘贴到模块窗口中。

Sub Splitdatabycol()
'updateby Extendoffice
Dim lr As Long
Dim ws As Worksheet
Dim vcol, i As Integer
Dim icol As Long
Dim myarr As Variant
Dim title As String
Dim titlerow As Integer
Dim xTRg As Range
Dim xVRg As Range
Dim xWSTRg As Worksheet
Dim xWS As Worksheet
On Error Resume Next
Set xTRg = Application.InputBox("Please select the header rows:", "Kutools for Excel", "", Type:=8)
If TypeName(xTRg) = "Nothing" Then Exit Sub
Set xVRg = Application.InputBox("Please select the column you want to split data based on:", "Kutools for Excel", "", Type:=8)
If TypeName(xVRg) = "Nothing" Then Exit Sub
vcol = xVRg.Column
Set ws = xTRg.Worksheet
lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
title = xTRg.AddressLocal
titlerow = xTRg.Cells(1).Row
icol = ws.Columns.Count
ws.Cells(1, icol) = "Unique"
Application.DisplayAlerts = False
If Not Evaluate("=ISREF('xTRgWs_Sheet!A1')") Then
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "xTRgWs_Sheet"
Else
Sheets("xTRgWs_Sheet").Delete
Sheets.Add(after:=Worksheets(Worksheets.Count)).Name = "xTRgWs_Sheet"
End If
Set xWSTRg = Sheets("xTRgWs_Sheet")
xTRg.Copy
xWSTRg.Paste Destination:=xWSTRg.Range("A1")
ws.Activate
For i = (titlerow + xTRg.Rows.Count) To lr
On Error Resume Next
If ws.Cells(i, vcol) <> "" And Application.WorksheetFunction.Match(ws.Cells(i, vcol), ws.Columns(icol), 0) = 0 Then
ws.Cells(ws.Rows.Count, icol).End(xlUp).Offset(1) = ws.Cells(i, vcol)
End If
Next
myarr = Application.WorksheetFunction.Transpose(ws.Columns(icol).SpecialCells(xlCellTypeConstants))
ws.Columns(icol).Clear
For i = 2 To UBound(myarr)
ws.Range(title).AutoFilter field:=vcol, Criteria1:=myarr(i) & ""
If Not Evaluate("=ISREF('" & myarr(i) & "'!A1)") Then
Set xWS = Sheets.Add(after:=Worksheets(Worksheets.Count))
xWS.Name = myarr(i) & ""
Else
xWS.Move after:=Worksheets(Worksheets.Count)
End If
xWSTRg.Range(title).Copy
xWS.Paste Destination:=xWS.Range("A1")
ws.Range("A" & (titlerow + xTRg.Rows.Count) & ":A" & lr).EntireRow.Copy xWS.Range("A" & (titlerow + xTRg.Rows.Count))
Sheets(myarr(i) & "").Columns.AutoFit
Next
xWSTRg.Delete
ws.AutoFilterMode = False
ws.Activate
Application.DisplayAlerts = True
End Sub

3. 然后，按下 F5 键运行代码，此时将弹出提示框，请选择标题行后单击确定。参见截图：
使用VBA代码拆分数据到工作表以选择标题行

4. 在第二个提示框中，请选择您要作为分割依据的列数据，然后单击确定。参见截图：
使用VBA代码拆分数据到工作表以选择数据范围

5. 活动工作表中的所有数据已根据列值拆分为多个工作表，生成的工作表以分割单元格中的值命名，并置于工作簿末尾。参见截图：
使用VBA代码拆分数据到工作表以获得结果

 

使用 Kutools for Excel 根据列值分割数据至多个工作表

Kutools for Excel 的分割数据工具为您带来智能功能——直接集成到您的 Excel 环境中，轻松将数据拆分为多个工作表。只需根据所选列值或指定行数，即可自动拆分数据集，确保每条信息精准归位。告别手动整理电子表格的繁琐操作，立即体验更快捷、无差错的数据管理方式！

注意：要使用此 分割数据，首先需下载 Kutools for Excel，然后即可快速轻松地应用该功能。

安装 Kutools for Excel 后，选择数据区域，然后单击 KUTOOLS PLUS 选项卡中的分割数据，即可打开分割数据至多个工作表对话框。

  1. 在分割依据部分中，选择指定列选项，并从下拉列表中选择用于拆分数据的列值。
  2. 如果您的数据包含标题行，并希望将其插入到每个新拆分的工作表中，请勾选包含标题选项。（您可以根据数据指定标题行数量，例如：若数据包含两行标题，请输入 2。）
  3. 然后，在创建工作表的名称部分下，指定拆分后的工作表名称：从规则下拉列表中选择名称规则，并可为工作表名称添加前缀或后缀。
  4. 单击确定按钮。参见截图：
    使用Kutools拆分数据到工作表以设置操作

现在，工作表中的数据已根据新工作簿拆分为多个工作表。
使用Kutools拆分数据到工作表以获得结果

使用 VBA 代码将分割数据根据列值拆分为多个工作簿

有时，将数据拆分为多个独立工作簿（而非多个工作表）更具优势，尤其是在依据关键列进行拆分时。以下是如何使用 VBA 代码，按指定列的值自动将数据拆分为多个工作簿的分步指南。

1. 按住 ALT + F11 键打开 Microsoft Visual Basic for Applications 窗口。

2. 单击插入> 模块，并将以下代码粘贴到模块窗口中。

Sub SplitDataByColToWorkbooks()
    ' Updateby Extendoffice
    Dim lr As Long
    Dim ws As Worksheet
    Dim vcol, i As Integer
    Dim myarr As Variant
    Dim title As String
    Dim titlerow As Integer
    Dim xTRg As Range
    Dim xVRg As Range
    Dim xWS As Workbook
    Dim savePath As String
    ' Set the directory to save new workbooks
    savePath = "C:\Users\AddinsVM001\Desktop\multiple files\" ' Modify this path as needed
    Application.DisplayAlerts = False
    Set xTRg = Application.InputBox("Please select the header rows:", "Kutools for Excel", Type:=8)
    If TypeName(xTRg) = "Nothing" Then Exit Sub
    Set xVRg = Application.InputBox("Please select the column you want to split data based on:", "Kutools for Excel", Type:=8)
    If TypeName(xVRg) = "Nothing" Then Exit Sub
    vcol = xVRg.Column
    Set ws = xTRg.Worksheet
    lr = ws.Cells(ws.Rows.Count, vcol).End(xlUp).Row
    title = xTRg.Address(False, False)
    titlerow = xTRg.Row
    ws.Columns(vcol).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ws.Cells(1, ws.Columns.Count), Unique:=True
    myarr = Application.Transpose(ws.Cells(1, ws.Columns.Count).Resize(ws.Cells(ws.Rows.Count, ws.Columns.Count).End(xlUp).Row).Value)
    ws.Cells(1, ws.Columns.Count).Resize(ws.Cells(ws.Rows.Count, ws.Columns.Count).End(xlUp).Row).ClearContents
    For i = 2 To UBound(myarr)
        Set xWS = Workbooks.Add
        ws.Range(title).AutoFilter Field:=vcol, Criteria1:=myarr(i)
        ws.Range("A" & titlerow & ":A" & lr).SpecialCells(xlCellTypeVisible).EntireRow.Copy
        xWS.Sheets(1).Cells(1, 1).PasteSpecial Paste:=xlPasteAll
        xWS.SaveAs Filename:=savePath & myarr(i) & ".xlsx"

        xWS.Close SaveChanges:=False
    Next i
    ws.AutoFilterMode = False
    Application.DisplayAlerts = True
    ws.Activate
End Sub
注意：在上述代码中，您应将文件路径修改为您自己的路径，用于在此脚本中保存分割工作簿：savePath = "C:\Users\AddinsVM001\Desktop\multiple files\"。

3. 然后，按 F5 键运行代码，此时将弹出提示框，提醒您选择标题行，请单击确定。参见截图：
使用VBA代码拆分数据到工作簿以选择标题行

4. 在第二个提示框中，请选择您要作为分割依据的列数据，然后单击确定。参见截图：
使用VBA代码拆分数据到工作簿以选择数据范围

5. 拆分完成后，活动工作表中的所有数据已根据列值拆分为多个工作簿，并全部保存至您指定的文件夹中。参见截图：
使用VBA代码拆分数据到工作簿以获得结果

相关文章：

  • 按行数
  • 根据指定行数将大型数据区域高效拆分为多个 Excel 工作表，可显著简化数据管理。例如，每 5 行将数据集拆分至不同工作表，能让数据更清晰、更易掌控。本指南提供两种实用方法，助您快速轻松完成此操作。
  • 根据关键列将两个或多个表格合并为一个
  • 假设工作簿中包含三个表格，现在希望根据对应的关键列将它们合并为一个表格，以获得如下图所示的结果。对大多数人而言，这可能是一项棘手的任务——但请放心，本文将为您介绍几种高效可行的解决方案。
  • 按分隔符将文本字符串拆分为多行
  • 通常，您可以使用“文本分列”功能，通过逗号、句点、分号、斜杠等特定分隔符将单元格内容拆分为多列。但有时，您可能需要将带分隔符的单元格内容拆分为多行，同时重复其他列中的数据（如下图所示）。在 Excel 中，您是否有高效处理此任务的方法？本教程将为您介绍几种实用技巧，助您轻松完成这一操作。

最佳办公效率工具

🤖KUTOOLS AI 助手：基于以下内容革新数据分析：智能执行   |  生成代码|  创建自定义公式  |  数据分析及生成图表|  调用 Kutools Functions……
热门功能：查找、高亮或标记重复项   |  删除空白行   |  合并列或单元格且不丢失数据   |  不使用公式的四舍五入……
高级 LOOKUP：多条件 VLookup  |  多值 VLookup  |   跨多工作表 VLookup   |   模糊查找……
高级下拉列表：快速创建下拉列表   |  级联下拉列表   |  多选下拉列表……
列管理器：添加指定数量的列|移动列|切换隐藏列的可见性状态|比较区域与列……
特色功能：网格聚焦   |  设计视图   |增强编辑栏   | 工作簿和表管理器   |  资源库（自动文本）|  日期提取   |  汇总工作表  |  加密/解密单元格   | 按列表发送邮件   |  超级筛选   |   特殊筛选（筛选粗体单元格/斜体/删除线……） ......
精选 15 工具集：12 文本工具（添加文本，删除特定字符，……）|   50+ 图表 类型（甘特图，……）|   40+ 实用公式（基于生日计算年龄，……）|   19 插入工具（插入二维码，从路径插入图片，……）|   12 转换工具（小写金额转大写，汇率转换，……）|   7 合并和拆分工具（高级合并行，分割单元格，……）|……更多
在您的首选语言中使用 Kutools – 支持英语、西班牙语、德语、法语、中文及 40+ 种其他语言！

使用 Kutools for Excel 大幅提升您的 Excel 技能，体验前所未有的高效。Kutools for Excel 提供 300 多项高级功能，助您提升生产力、节省时间。立即点击此处，获取您最需要的功能……

Office Tab 为 Office 带来标签式界面，让您的工作更轻松

  • 在 Word、Excel、PowerPoint、Publisher、Access、Visio 和 Project 中启用标签式编辑和阅读。
  • 在同一个窗口的新标签页中打开并创建多个文档，而非在新窗口中。
  • 将您的工作效率提升 50%，每天减少数百次鼠标点击！

所有 Kutools 插件，一个安装程序

Kutools for Office 套件捆绑了适用于 Excel、Word、Outlook 和 PowerPoint 的插件以及 Office Tab Pro，非常适合需要跨多个 Office 应用高效协作的团队。

ExcelWordOutlookTabsPowerPoint
  • 一体化套件— Excel、Word、Outlook 和 PowerPoint 插件 + Office Tab Pro
  • 一个安装程序，一个许可证— 几分钟内完成设置（支持 MSI）
  • 协同效果更佳— 在多个 Office 应用中实现高效协同
  • 30 天全功能试用— 无需注册，无需信用卡
  • 超值之选— 比单独购买插件更省钱