精彩课程推荐

EXCEL你复制粘贴的那么认真,难怪天天加班

[复制链接]
查看: 168|回复: 0
发表于 2020-1-15 13:32:20 | 显示全部楼层 |阅读模式

马上注册,结交更多好友,享用更多功能,让你轻松玩转社区。

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
哈喽,大家好!在平时的工作中,我们经常会遇到将工作表拆分,或者合并的问题。大多数人还只会用复制粘贴的方式来解决,虽然操作简单,但是当遇到数据量较大的情况,无疑会拖垮我们的工作效率。其实工作表的拆分和合并没有大家想象中的那么难,本系列将分为上下两篇教程,分别讲解工作表拆分与合并的方法,本篇是上篇,将给大家带来4种工作表拆分的方法,赶紧来看看吧!

【前言】
“小张,把采购明细表,按供应商拆分成一个一个的文件,发出去对账。”
“小张,把每个库管的采购明细,汇总成一个总明细表,发给我。”
“小张,这个月部门的工资条做了吗?做完给每个人都发一下。”
【正文】
“总-分式套表”,好像是每个行业每个统计岗都会遇到的表格形式,十分的普遍,我们在日常工作中,也经常需要把各种表格,拆分又合并,合并再拆分,这就是“分久必合,合久必分”的千古铁律。可是如果我们只会筛选后复制、粘贴,确实效率低下,那么这篇文章就给大家带来几种拆分与合并的操作方法,希望在工作中能够帮到同学们。

1、总表拆分为工作表——函数流
【数据源】
下图是比较常见数据,我们现在的需求:按照总表中的供应商数据做出各个分表,把数据分别做到每个对应供应商的工作表中。
image.jpg

步骤1:当我们使用函数拆分工作表时,需要先“手工”创建各个分表。我们先确定表中的供应商名称分别是:“永达”,“安达”,“洋子”三家公司,然后手动添加一个名为《永达》的分表:
image.jpg

步骤2:制作供应商分表的“表头”。分表的表头可以和总表的一样,也可以不一样,具体问题具体分析,但是一定要注意,在分表中出现的字段一定是可以从总表中引用的,或者是可以通过数据计算的。
我们制作的分表表头如下:
133221v39cj4qj39ww3wqc.jpg

步骤3:在总表中制作辅助列,作为分表引用的“关键词”。
(“关键词”的作用是用于唯一地标识表中的某一条记录或某一个字段属性,具有唯一性的原则。我们在《比VLOOKUP重要10倍,更容易让你晋升Excel高手的3张“王牌”》的文章说明过,不太熟悉的小伙伴可以点击链接复习一下~)


A列函数:=I2&COUNTIF($I$2:I2,I2)
通过COUNTIF函数和区域“混合引用”的方法,得到每个供应商,在总表中出现的顺序号,再与供应商名连接,形成一个新的引用关键字。此类引用方法在之前的教程中介绍过,小伙伴们可以点击教程《同样是countifs函数,为什么同事却使得比你好?原因在这里!》学习,此处不做赘述了。
步骤4:在分表中制作引用数据的函数。当仁不让,我们一定会使用到常用函数VLOOKUP。
image.jpg

《永达》分表中A6单元格函数:
=IFERROR(VLOOKUP($B$2&ROW(A1),总表!$A$1K$50000,MATCH(A$5,总表!$A$1K$1,0),0),"")
这是一个典型的IFERROR+VLOOKUP+MATCH函数的嵌套使用:
$B$2&ROW(A1)是供应商名称&行号,这样就和我们刚才在总表中做的辅助列字段相呼应,可以作为VLOOKUP函数的引用标准。
用MATCH函数得到表头字段在总表中的序列号,可以确定VLOOKUP函数引用的第几列的数据。
最后再用IFERROR函数规避#N/A值。
同学们可以看到这里用了很多的“区域引用技巧”,这是函数应用基础的一部分,就不在这里多说了,不会的话赶紧在部落窝补补课。
输入函数后,右拉填充,再下拉填充,一个分表的自动化拆分就做好了。
“小常识”:
这里介绍一个右拉、再下拉填充公式的快捷方式,在A6单元格输入公式后,接着在名称框中输入A6:J10000,按回车键选中需要填充的区域,再按CTRL+D组合键向下填充,再按CTRL+R组合键向右填充,完工。
image.jpg

步骤5:调整单元格各个字段的格式,比如“日期”列的数据需设置成日期格式,如下:
image.jpg

步骤6:以此表为模板,制作其他供应商的分表。我们可以新建一个空白工作表,再全选、复制《永达》工作表的内容,然后粘贴到新建的空白表中,再更改工作表名称和B2单元格的引用条件,如下:
image.jpg

是不是很简单呢?而且如果总表中有了新的修改或新增内容,分表中也会自动调整。但是这种函数的拆分方式,在数据量较多的情况,就会显得有一些卡顿,那么我们就继续来看看下面的拆分方法吧。
2、总表拆分为工作表——数透流
步骤1:选中数据区域A1:J25,在工具栏中选择插入——数据透视表。
image.jpg

按下图,设置数据透视表的放置位置。
image.jpg

步骤2:设置数据透视表的字段。我们还是按“供应商”拆分工作表,如下设置字段的布局。
image.jpg

得到下面的数据透视表:
image.jpg

步骤3:选中数据透视表中某一个单元格,在工具栏顶端出现的“数据透视表工具”选项卡中,点击其中的“分析”菜单,接着选择下图所示的“显示报表筛选页”功能。
image.jpg

弹出下面的窗口,点击“确定”。


现在你的分表是不是已经被拆分出来了?但它依然还是数据透视表的结构。
image.jpg

找到这个数据透视表的最末行,双击“总计”框,就可以得到一个分表的列表。
image.jpg

3、总表拆分为工作表——高筛流
高级筛选功能估计很多同学都是听说过,但没怎么用过,那么就借今天“拆分工作表”的主题,带着大家一起再学习一次吧。
步骤1:新建空白表,制作筛选条件区域。
image.jpg

步骤2:在工具栏的“数据”选项卡中,排序和筛选工作组中,点击“高级”,弹出下面的窗口。
image.jpg

image.jpg

步骤3:按照下图的设置内容,分别设置高级筛选的“列表区域”、“条件区域”、“复制到”的区域。
image.jpg

注意:当我们点选“将筛选结果复制到其他位置”的选项后,“复制到”的输入框才可以输入内容,然后点击“确定”按钮,得到如下图所示的筛选数据。
image.jpg

重复上面的操作过程,可以制作其他供应商的分表。另外,高级筛选可以多条件的提取数据,是比较方便的一个工具。
关于高级筛选详细的使用方法,小伙伴们可以参考之前的教程《常用筛选的表哥表妹,更好用、更强大的增强版筛选来了!》,此处不做赘述了。
【小结】
通过“函数流”、“数透流”、“高筛流”三种方式的操作,作者E图表述还是认为函数的方式更加符合我们“一劳永逸”的建模思路,而数据透视表和高级筛选只能说应急的时候操作比较快,但不方便形成模板,如果有了数据删改,需要我们再次的操作。
可对于函数操作来说,如果数据量较大的时候,又会造成卡顿的情况,那么下面我们再来看一种更优的拆分工作表的方式——VBA流。
4、总表拆分为工作表——VBA流
不废话,先来一个效果图:
image.jpg

是不是很方便?而且每次修改、删除、增加总表记录的时候,再次点击按钮就可以自动更新数据!下面我们就一起来看看操作方法吧~
步骤1:按ALT+F11组合键,打开VBE界面;
image.jpg

步骤2:在左边工程窗口处,单击鼠标右键,在弹出的菜单中选择“插入”——“模块”;
image.jpg

步骤3:双击新生成的模块,在右侧代码区,输入如下代码:
Sub 拆分表
Application.ScreenUpdating=False
Application.DisplayAlerts=False
On Error Resume Next
Dim arr, brr, d
’“总表”是作者测试数据的工作表名称,如果你的总表工作表名称是其他的,如:XXX,把代码中所有的“总表”替换(CTRL+H)成XXX即可。
a=Sheets("总表").[B65000].End(3).Row
’A2:J & a 是作者测试数据中的区域,大家可以改成自己的列表范围
arr=Sheets("总表").Range("A2:J" & a)
Set d=CreateObject("scripting.dictionary")
For i=1 To UBound(arr)
’为什么是arr(i,8)呢?因为我们是按照数据范围中的第8列内容也就是“供应商”列拆分总表。大家可以按照自己的需要改成某列号即可,下面的arr(i,8)都是这样的修改方式。
d(arr(i, 8))=d(arr(i, 8)) + ""
Next i
x=Sheets.Count
For j=x To 1 Step -1
If Sheets(j).Name <> "总表" Then
Sheets(j).Delete
End If
Next j
x=Sheets.Count
For Each dic In d
ReDim brr(1 To UBound(arr), 1 To UBound(arr, 2))
Sheets.Add after:=Sheets(x)
x=x + 1
Sheets(x).Name=dic
For i=1 To UBound(arr)
If arr(i, 8)=dic Then
k=k + 1
For j=1 To UBound(arr, 2)
brr(k, j)=arr(i, j)
Next j
End If
Next i
Sheets("总表").Range("1:1").Copy Sheets(x).Range("1:1")
’ Range("A2"),是作者被粘贴区域的首个单元格,如果大家需要从其他部分粘贴,就把这里改一下。
Sheets(x).Range("A2").Resize(UBound(brr), UBound(brr, 2))=brr
Erase brr
k=0
Next
End Sub
步骤4:运行代码,测试代码是否运行正常。
image.jpg

步骤5:如果测试代码无误,将.XLSX文件另存为.XLSM文件(启用宏的EXCEL工作薄)。作者E图表述的很多学生在初学VBA的时候,经常会忘记另存为.XLSM文件,虽然也能保存,但是保存的是工作表区域的数据,VBE界面的代码是没有被保存的,辛苦付之东流。
image.jpg

虽然没有解释代码的含义,但却给出了代码的修改方式。这样一来,会VBA的同学可以看懂;而不会VBA的同学,可以根据不同的场景,修改代码。对于“拆分”工作表的操作,作者总结了4种方式,选择有把握的方式来处理工作上的问题,是解决问题的最快途径!






上一篇:财务这样和老板沟通,有效又顺利
下一篇:数字看得脑壳痛?一招让Excel数据变得高级

2834

主题

3107

帖子

16万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
160564

突出贡献优秀版主荣誉管理论坛元老管理员

推荐:原价298现活动价只需¥198 充值开通VIP会员就可以学习本站全部资源【点击立即开通】
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

商务合作

客服热线:400-808-2188
点击这里给我发消息

关注微信

Copyright;  ©2013-2020  天天会计网  网站地图  友情链接   ( 粤ICP备16059295号-1 )