在我们的工作当中,常常会遇到这样的工作场景,我们需要将一个汇总的工作表按照某列的字段拆分为多个工作表。按照惯例,我们还是通过实际的一个例子来给大家进行形象的讲解吧。下面为某学校高一年级的成绩汇总表,我们需要按照班级为单位,将每个班级拆分成一个工作表。

excel自动拆分成多个表(Excel单工作表拆分成多个工作表)(1)

我们需要操作的是根据班级列,将花名册拆分为5个工作表,分别以班级名称命名工作表的名称。

我们使用数据透视表,可以按照班级名称进行透视汇总,在数据透视表中有一个功能,双击汇总的数据行,可以生成一个该汇总行的组成明细的工作表,我们根据Excel数据透视表的这一个功能特征,就可以将一个汇总表按照需要的字段进行拆分。某一时候可能会需要根据多列进行拆分,这个时候最简单的办法就是大家新建一个辅助列,将作为条件的那几列合并在辅助列中,这样就能轻松实现。下面介绍具体的操作步骤。

1、插入透视表。

在【插入】选项卡下面的【表格】组中找到【数据透视表】,单击,弹出创建数据透视表对话框,点击确定就可以。

excel自动拆分成多个表(Excel单工作表拆分成多个工作表)(2)

2.生成数据透视表

以班级作为行标签,以学号作为值,汇总方式为计数,生成如下的透视表。

excel自动拆分成多个表(Excel单工作表拆分成多个工作表)(3)

3.通过数据透视表生成工作表。

依次双击生成的数据透视表中的计数项,就可以生成工作表。

excel自动拆分成多个表(Excel单工作表拆分成多个工作表)(4)

4.通过录制宏的方式批量设置工作表格式。

某些时候,我们需要对每个生成的工作表进行一些格式的统一,如统一设置行高为20,设置字体为宋体,设置对齐方式为居中,同时设置列宽。这个时候我们就可以使用到Excel中录制宏的操作方式(也即Excel的VBA功能),我们可以在设置一个工作表的时候进行录制宏,然后稍稍对生成的代码进行更改,然后就可以在其他生成的工作表中批量重复同样的操作。

excel自动拆分成多个表(Excel单工作表拆分成多个工作表)(5)

录制后生成如下代码:

Sub 宏1()

' 宏1 宏

Range("表2[#All]").Select

Selection.RowHeight = 20

With Selection

.HorizontalAlignment = xlCenter

.VerticalAlignment = xlCenter

.WrapText = False

.Orientation = 0

.AddIndent = False

.IndentLevel = 0

.ShrinkToFit = False

.ReadingOrder = xlContext

.MergeCells = False

End With

With Selection.Font

.Name = "宋体"

.Size = 11

.Strikethrough = False

.Superscript = False

.Subscript = False

.OutlineFont = False

.Shadow = False

.Underline = xlUnderlineStyleNone

.ThemeColor = xlThemeColorLight1

.TintAndShade = 0

.ThemeFont = xlThemeFontNone

End With

With Selection.Font

.Name = "宋体"

.Size = 12

.Strikethrough = False

.Superscript = False

.Subscript = False

.OutlineFont = False

.Shadow = False

.Underline = xlUnderlineStyleNone

.ThemeColor = xlThemeColorLight1

.TintAndShade = 0

.ThemeFont = xlThemeFontNone

End With

Selection.Borders(xlDiagonalDown).LineStyle = xlNone

Selection.Borders(xlDiagonalUp).LineStyle = xlNone

With Selection.Borders(xlEdgeLeft)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With Selection.Borders(xlEdgeTop)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With Selection.Borders(xlEdgeBottom)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With Selection.Borders(xlEdgeRight)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With Selection.Borders(xlInsideVertical)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

With Selection.Borders(xlInsideHorizontal)

.LineStyle = xlContinuous

.ColorIndex = 0

.TintAndShade = 0

.Weight = xlThin

End With

ActiveWindow.SmallScroll Down:=-9

Range("A2").Select

ActiveCell.FormulaR1C1 = "1"

Range("A3").Select

ActiveCell.FormulaR1C1 = "2"

Range("A2:A3").Select

Selection.AutoFill Destination:=Range("表2[序号]")

Range("表2[序号]").Select

ActiveWindow.SmallScroll Down:=48

Sheets("Sheet3").Select

Sheets("Sheet3").Name = "高1班"

Range("D70").Select

ActiveWindow.SmallScroll Down:=-84

End Sub

在其他工作表中,大家需要操作的仅仅是将表2更改为新表的名称即可。最后的工作表重命名代码处如果大家对VBA有点基础的话可以做如下更改:

Sheets("Sheet3").Select

Sheets("Sheet3").Name = "高1班"

修改为:

ActiveSheet.Name = Cells(2, 3)

这样就可以自动对当前的工作表进行重命名。

5.结果如下:

excel自动拆分成多个表(Excel单工作表拆分成多个工作表)(6)