实用Excel技巧分享:快速拆分工作表的两种方法

千伟吖_6910

千伟吖_6910

2023-01-30

13794人浏览

转载

工作表有汇总就有拆分。如何快速拆分工作表呢?本篇文章大家分享效率提高99.99%的快速拆分工作表的两种方法,希望对大家有所帮助!

各位小伙伴有没有遇到过这样的问题:当我们把所有的信息汇总在一张表里后,又需要将这张大表按某一条件再拆分成多个工作表。那怎么才能实现呢?可能最笨的方法就是在原工作表筛选数据然后复制粘贴到新工作表,不过这种方法不适合数据多的案例,并且新工作表也需要一一重命名,显得繁琐。今天就给大家介绍两种快捷实用的工作表拆分方法。

如图,现在要把这个工作表的内容按城市拆分成多个工作表。

第1种:极速拆分——VBA(文中提供有代码)

    VBA是EXCEL处理大量重复工作最好用的工具。不过很多人对VBA一窍不通,所以今天给大家分享一段代码,并且详细解释了如何根据实际表格修改代码值,方便大家在工作中使用。

(1)按住Alt+F11打开VBA编辑器,点击“插入”菜单下的“模块”。

(2)在右侧代码窗口输入下列代码。不想动手输入的可以加群下载已经准备好的代码文件,直接复制粘贴即可。

Sub 拆分表()

    Dim i, iRow, iCol, t, iNum As Integer, sh As Worksheet, str As String
    Application.ScreenUpdating = False
    With Worksheets("Sheet1")
     iRow = .Range("A65535").End(xlUp).Row
     iCol = .Range("IV1").End(xlToLeft).Column
     t = 3
        For i = 2 To iRow
            str = .Cells(i, t).Value
            On Error Resume Next
            Set sh = Worksheets(str)
            If Err.Number  0 Then
                Set sh = Worksheets.Add(, Worksheets(Worksheets.Count))
                sh.Name = str
            End If
           
            sh.Range("A1").Resize(1, iCol).Value = .Range("A1").Resize(1, iCol).Value
            iNum = sh.Range("A" & Rows.Count).End(xlUp).Row
            sh.Range("A" & iNum + 1).Resize(1, iCol).Value = .Range("A" & i).Resize(1, iCol).Value
            Next i
     End With
Application.ScreenUpdating = True
End Sub

代码解析:

这里用红色文字表示需要根据实际修改的代码参数;'用于表示注释,其后的文字并不影响代码的运行,只是用于说明代码的。这里特意用灰色表示注释文字。

Sub 拆分表 '文件名称,根据自己的文件名修改

    Dim i, iRow, iCol, t, iNum As Integer, sh As Worksheet, str As String

    Application.ScreenUpdating = False '关闭屏幕刷新

    With Worksheets("Sheet1")'双引号内是工作簿名称,根据实际工作簿名称修改

     iRow = .Range("A65535").End(xlUp).Row '从A列的最后一行开始向上获取工作表的行数,一般只改动Range中的列参数,如要工作表有效区域是从B列开始的,值就是B65535

     iCol = .Range("IV1").End(xlToLeft).Column'从最后列(IV)第1行开始向左获取工作表的列数,一般只改动Range中的行参数,如要工作表有效区域是从第2行开始的,值就是IV2

 

     t = 3 't为列数,设置依据哪一列进行拆分,譬如,如果是按E列拆分,这里就是t=5

        For i = 2 To iRow  'i为行数,设置从第几行开始获取拆分值,要根据工作表实际改动

            str = .Cells(i, t).Value '获取单元格(i, t)的值作为拆分后的表格名称

            On Error Resume Next

            Set sh = Worksheets(str) '创建以上述获取值为名的工作表

            If Err.Number  0 Then '如果不存在这个工作表则添加一个并命名

                Set sh = Worksheets.Add(, Worksheets(Worksheets.Count))

                sh.Name = str

            End If '如果存在这个工作表

            sh.Range("A1").Resize(1, iCol).Value = .Range("A1").Resize(1, iCol).Value '获取工作表标题,一般只改动Range的列值和Resize中的行值,譬如工作表的标题是从B列第3行开始的,则这句代码就变成 sh.Range("B1").Resize(3, iCol).Value = .Range("B1").Resize(3, iCol).Value '

            iNum = sh.Range("A" & Rows.Count).End(xlUp).Row '一般只改Range中的列值,如工作表是从B列开始的,这里就变成Range("B" & Rows.Count).End(xlUp).Row

            sh.Range("A" & iNum + 1).Resize(1, iCol).Value = .Range("A" & i).Resize(1, iCol).Value

           '在新表中粘贴工作表数据,一般只改动Range的列值,若工作表是从B列开始的,则就改成B变成Range("B" & iNum + 1).Resize(1, iCol).Value = .Range("B" & i).Resize(1, iCol).Value

        Next i

     End With

    Application.ScreenUpdating = True '打开屏幕刷新

End Sub

(3)代码输入完成后,点击菜单栏里的“运行子过程”。这样工作表就拆分完成了。

完成如下:

这样就通过这种方式一键完成工作表拆分了。

第2种:常规拆分——数据透视表

    数据透视表真的非常好用,它不仅在数据统计分析上拥有绝对的优势,而且利用筛选页也可以帮助我们实现拆分工作表的功能。步骤如下:

(1)选择数据源任一单元格,单击插入选项卡下的“数据透视表”。位置选择现有工作表,单击确定。

(2)把要拆分的字段“城市”放到筛选字段,“日期”“业务员”字段放在行字段,“销售额”放在值字段。

Excel公式工具(专业版)
Excel公式工具(专业版)

从自然语言描述生成Excel公式,诊断表格错误,支持VLOOKUP、条件求和等常用函数。

下载

(3)修改数据透视表格式,便于在生成新工作表的时候形成表格格式。

选择“数据透视表工具”下方“设计”选项卡里的“报表布局”下拉菜单的“以表格形式显示”。

选择“数据透视表工具”下方“设计”选项卡里的“报表布局”下拉菜单的“重复所有项目标签”。

选择“数据透视表工具”下方“设计”选项卡里的“分类汇总”下拉菜单的“不显示分类汇总”。

完成结果如下:

(4)最后把透视表拆分到各个工作表。选择“数据透视表工具”下方“分析”选项卡“数据透视表”功能块里的“选项”下拉菜单的“显示报表筛选页”,选定要显示的报表筛选页字段为“城市”。

(5)为了方便后续处理,把数据透视表修改成普通表格。选择第一个工作表 “北京”,按住Shift,点击最后一个工作表“重庆”,形成工作表组。这样就能批量对所有工作表进行统一操作。

全选复制粘贴为值。

删除前两行,再把日期这列列宽调整一下就完成了。结果如下:

数据透视表这种方法比较容易上手,但是步骤比较多,而VBA操作简单,但需要学习的东西很多。大家根据自己实际情况选择使用,觉得不错的话点赞吧!

相关学习推荐:excel教程

相关专题

更多
string转int
string转int

在编程中,我们经常会遇到需要将字符串(str)转换为整数(int)的情况。这可能是因为我们需要对字符串进行数值计算,或者需要将用户输入的字符串转换为整数进行处理。php中文网给大家带来了相关的教程以及文章,欢迎大家前来学习阅读。

2023.08.02

7739

3

counta和count的区别
counta和count的区别

Count函数用于计算指定范围内数字的个数,而CountA函数用于计算指定范围内非空单元格的个数。本专题为大家提供相关的文章、下载、课程内容,供大家免费下载体验。

2023.11.20

4048

5

scripterror怎么解决
scripterror怎么解决

scripterror的解决办法有检查语法、文件路径、检查网络连接、浏览器兼容性、使用try-catch语句、使用开发者工具进行调试、更新浏览器和JavaScript库或寻求专业帮助等。本专题为大家提供相关的文章、下载、课程内容,供大家免费下载体验。

2023.10.18

1089

5

500error怎么解决
500error怎么解决

500error的解决办法有检查服务器日志、检查代码、检查服务器配置、更新软件版本、重新启动服务、调试代码和寻求帮助等。本专题为大家提供相关的文章、下载、课程内容,供大家免费下载体验。

2023.10.25

3620

5

excel对比两列数据异同
excel对比两列数据异同

Excel作为数据的小型载体,在日常工作中经常会遇到需要核对两列数据的情况,本专题为大家提供excel对比两列数据异同相关的文章,大家可以免费体验。

2023.07.25

6281

7

excel重复项筛选标色
excel重复项筛选标色

excel的重复项筛选标色功能使我们能够快速找到和处理数据中的重复值。本专题为大家提供excel重复项筛选标色的相关的文章、下载、课程内容,供大家免费下载体验。

2023.07.31

3996

5

excel复制表格怎么复制出来和原来一样大
excel复制表格怎么复制出来和原来一样大

本专题为大家带来excel复制表格怎么复制出来和原来一样大相关文章,帮助大家解决问题。

2023.08.02

3780

3

excel表格斜线一分为二
excel表格斜线一分为二

在Excel表格中,我们可以使用斜线将单元格一分为二。本专题为大家带来excel表格斜线一分为二怎么弄的相关文章,希望可以帮到大家。

2023.08.02

1444

3

excel斜线表头一分为二
excel斜线表头一分为二

excel斜线表头一分为二的方法有使用合并单元格功能方法、使用文本框功能方法、使用自定义格式方法。本专题为大家提供excel斜线表头一分为二相关的各种文章、以及下载和课程。

2023.08.02

737

5

热门下载

更多
网站特效
/
网站源码
/
网站素材
/
前端模板

精品课程

更多
相关推荐
/
热门推荐
/
最新课程
Excel 教程
Excel 教程

共162课时 | 45.9万人学习

成为PHP架构师-自制PHP框架
成为PHP架构师-自制PHP框架

共28课时 | 3.7万人学习