|
前段时间，领导问我，有没有办法不用代码来汇总多个EXCEL表（或者工作簿），我印象当中好像数据透视是可以的，就是实现方式有点不确定。后面又找了一下资料，发现合并计算也是可以的。当然关于多个工作表或者多个工作簿的汇总、合并，如果用上代码的话，办法实在太多，这里使用数据透视，合并计算完成。EXCEL版本Microsoft EXCEL 2016,win1032位 一、使用数据透视向导汇总多表 1. 需要汇总的工作簿必须提前打开 2. 新版EXCEL数据透视向导在菜单栏找不到，需要通过快捷方式或自定义工具栏添加 3. 格式要求 4. 实现过程 5.支持刷新数据透视表 二、合并计算 1. 先选择需要输出的结果的位置 2. 格式要求 3. 实现过程 4. 支持刷新数据 5. 结果顺序会重新排序，第一列的表表头会丢失 6. 如果实现vlookup的功能 7. Vba的实现 三、 废话 一、使用数据透视向导汇总多表1.需要汇总的工作簿必须提前打开此办法支持对多个工作簿进行汇总，但是在做汇总的时候，需要提前打开所有工作簿。选取区域的时候直接选取添加。界面有个浏览也可以打开文件，但是不能选择区域，最后的结果会有报错。2.新版的EXCEL数据透视向导在菜单栏找不到，需要通过快捷方式或自定义工具栏添加快捷方式是ALT +D+P，按键顺序，先按住ALT不放，再按下D（可放开），再按下P。自定义工具栏添加则在菜单栏任意空白地方右键，选择“自定义功能区”，然后找到“数据透视表和数据透视图向导”，选择需要添加到的地方，确定即可。3.格式要求格式要求，表头和列头只能是一行和一列。但是不需要每个表的格式完全一致。比如，某一个表里面内容比其它表多一列，同样的列表头所在的位置可以不在同一位置。至于行列表头是否唯一，此办法则没有要求，如果有两列的表头一样，系统会将两列合并计算。如例子里面的“数量”，在同一个表可以有多列“数量”都没有关系。4.实现过程以两个工作簿为例子，同一工作簿多个工作表同理操作。具体见动图gif。 
5.支持刷新数据透视表跟我们认识的数据透视一样，虽然是多个数据源，哪怕多个工作簿，任一数据源修改后，在数据透视表右击，刷新都可以得到新的结果。二、合并计算合并计算这个功能，听起来并不熟悉。帮助的说明也挺简单，就汇总单独区域中的数据。不过，其实这个汇总的功能，我们如果适当利用，也可以实现其它的结果，比如类似VLOOKUP公式的结果。 1. 操作前要先选择需要输出的结果的位置 建议新建一表来存储结果（并不是新建一个工作簿，当然如果你需要这样也无妨），一来更方便体现结果，二来避免无法创建指向数据源的链接（作用于修改数据源时，结果更新）。2.格式要求3. 实现过程本文两个办法操作过程略为复杂，截图就没意思了，要看图的可以直接点EXCEL帮助，这里还是使用动图。
4. 支持刷新数据 跟数据透视方法一样，我们这个方法也是支持刷新数据的。在我们修改数据源后，不需要右击刷新，结果表就会随着数据源的更改而变动。但是，这个必须是在创建的过程中，勾选了“创建指向数据源的链接”的情况下才能实现。 选了创建链接后，结果还有个区别是，我们可以在结果里看到每个汇总的来源，这个跟分类汇总有点相似，在表前也会显示层级。5.结果顺序会重新排序，第一列的表表头会丢失合并计算后，得到的结果并不维持原来的表出现的顺序。列顺序好像是按字母顺序重新排序，行顺序则视合并内容顺序（据我观察好像是这样）6.如何实现vlookup的功能或者不用我再另外说明，聪明的你估计也能想到。例如我们有两个表，一个包含货号和数量，另一个包含货号和价格，那么我们用合并计算的结果就是一个三列的表，包含货号，数量和价格。不过这里需要注意的是，价格表的明细就需要唯一了。猜想一下，如果同一个货号有两个价格，最后得到的价格不就是这两个的和了吗？这样就不是我们想要得到的结果了。7.Vba的实现不上点代码都不好意思发文章，这里来个简单的多表汇总吧，需要先创建一个名字为结果的新表。Consolidate即合并，具体用法： expression.Consolidate(Sources, Function, TopRow, LeftColumn, CreateLinks) expression为存放结果的目标单元格，Sources为数据源集，Function指定合并类型，后面三项就是我们在合并计算的操作过程中碰到的首行，最左列，及创建链接。Sub 合并计算()Dim Rarr() As String'新增工作表，表名为”结果“ For Each sht In Sheets If sht.Name = '结果' Then sht.Delete NextSheets.Add().Name = '结果''合需要合并计算的数据源ReDim Rarr(1 To Sheets.Count - 1)For Each sht In Sheets If sht.Name <> '结果' Then i = i + 1 Rarr(i) = ''' & sht.Name & ''!' & _ sht.Range('A1').CurrentRegion.Address(ReferenceStyle:=xlR1C1) End If NextSheets('结果').Range('A1').Consolidate Rarr, xlSum, True, True, TrueSheets('结果').Range('A1').Value = '名称' '首列表头会丢失，这里补回去。End Sub
三、废话最近国际形势动荡不安，国内疫情尚不明朗，洪水台风络绎不绝，导致精神紧张，加上家务繁忙，而且文章所产生的回馈廖廖，等等原因（简单点说我懒了），所以许久没有更新。突然兴致看了一眼公众号，居然还有新增关注，老脸一红。其实自己不就最讨厌烂尾吗？更新慢点总比不更的好吧。所以觉得还是得分享一二，不一定实用，知道、了解一下这么个功能也无妨。或者有机会在菜鸟面前装高手呢？？
|
