ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 59305|回复: 116

[分享] 表格合并和拆分(公式及详解)

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2010-11-22 03:49 | 显示全部楼层 |阅读模式
本帖已被收录到知识树中,索引项:GET.WORKBOOK
在浏览http://club.excelhome.net/viewth ... ight=%2Bstary665533这个帖子时,重新学习了gvntw 版主的大作。在原基础上略作修改,名称定义的少了一些,希望能分享给更多的朋友。

当然,数据量大首推VBA解决问题。

汇总表的设计思路:
1。将需要合并的表格放在汇总表的前面,如附件所示,如果需要加表的话直接在汇总表的前面增加即可。
2。获得需要汇总的各个表格数据的行数,并得到相应数组,如附件三张测试表的数据行数分别为5行,4行和3行,因此用SourceDataRows获得数组{5;4;3}。
3。利用mmult公式获得上述数组的累计数(用于lookup函数中,因为lookup第一参数如果在第二参数数组中没有相等的值,就返回小于第一参数最接近的值,因此mmult产生的数组第一个元素为0){0;5;9},这样,0-4就表示第一个工作表中的数据(5个),5-8就表示第二个工作表中的数据(4个),大于等于9就表示第三个工作表中的数据。数据表多的情况以此类推。
4。最后,判断如果汇总表中的已汇总行数超过了三个表里的数据合计5+4+3即显示空,如果小于或者等于5+4+3的行数即用offset+indirect函数分别顺序引用前三个数据表中的内容并显示在汇总表中。

其中函数使用说明如下:
1。ALLSheetName=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")&T(NOW())
GET.WORKBOOK(1),此宏表函数在定义名称中使用,且仅能在定义名称中使用(了解其他宏表函数可以论坛搜索“常用宏表函数选”,这几天好像还看见来着)。其功能为获得当前工作薄中所有工作表的名称,并形成一个列数组,此附件中返回结果为:{"[合并拆分.xls]第一张测试表","[合并拆分.xls]第 二 张 测 试 表","[合并拆分.xls]3","[合并拆分.xls]汇总","[合并拆分.xls]A","[合并拆分.xls]B","[合并拆分.xls]C","[合并拆分.xls]D","[合并拆分.xls]测试"}

可以看到返回结果中包括着工作薄的名字,因此用replace()函数去掉(若直接多表求和使用的话可以不去掉工作薄的名字,参考http://club.excelhome.net/viewth ... ry665533&page=2,13楼的附件)。

Replace()函数将每个数组元素从第一个字符开始到"]"替换为空,得到返回结果如下:{"第一张测试表","第 二 张 测 试 表","3","汇总","A","B","C","D","测试"},&T(now())是为了及时重新计算使用。

2。SourceDataSheet=LOOKUP(ROW(INDIRECT("1:"&MATCH("汇总",ALLSheetName,)-1)),ROW(INDIRECT("1:"&COLUMNS(ALLSheetName))),ALLSheetName)

上一步获得了所有工作表的名字,但是我们要汇总的为“汇总”表前的所有表格,因此,用公式获得需要汇总的表格的表名。
LOOKUP的第一参数:ROW(INDIRECT("1:"&MATCH("汇总",ALLSheetName,)-1)),首先利用MATCH("汇总",ALLSheetName,)确定“汇总”表是当前工作薄中的第几个工作表,结果返回:4。那么MATCH("汇总",ALLSheetName,)-1得到需要汇总的表格总数:4-1=3。接下来用ROW(INDIRECT("1:"&MATCH("汇总",ALLSheetName,)-1))获得一个行数组{1;2;3}作为Lookup的第一个参数。

LOOKUP的第二参数:ROW(INDIRECT("1:"&COLUMNS(ALLSheetName)))获得一个全部表格数量的数组,返回{1;2;3;4;5;6;7;8;9}。

再配合第三参数所有工作表的名字即得到需要汇总的三个工作表的名字数组:SourceDataSheet=LOOKUP(ROW(INDIRECT("1:"&MATCH("汇总",ALLSheetName,)-1)),ROW(INDIRECT("1:"&COLUMNS(ALLSheetName))),ALLSheetName)={"第一张测试表";"第 二 张 测 试 表";"3"}

3。SourceDataRows=SUBTOTAL(3,INDIRECT("'"&SourceDataSheet&"'!B:B"))-1

SUBTOTAL函数支持三维引用,所谓的三维引用简单说就是指除了可以引用一个工作表的行、列(二维)数据外还可以引用其他工作表中的行、列。SUBTOTAL第一函数为3表示COUNTA(除了数字,文本也将统计在内因为我们想统计出每个工作表数据的行数)。"'"&SourceDataSheet&"'!B:B"形成对需汇总的各个工作表中B列引用的文本,之所以要加前后加各加上一个单引号是由于工作表命名的不同可能导致引用的差异。见附件的K4:K6的说明。该部分返回:{"'第一张测试表'!B:B";"'第 二 张 测 试 表'!B:B";"'3'!B:B"},外套上INDIRECT()函数转化成对各表的实际引用。最外层嵌套SUBTOTAL返回各需要统计的工作表B列数据的行数:SUBTOTAL(3,INDIRECT("'"&SourceDataSheet&"'!B:B"))={6;5;4},由于有一层标题行的存在,所以实际数据量需要减一,得到:{5;4;3}

4。AccumulatedRows=MMULT(N(ROW(INDIRECT("1:"&ROWS(SourceDataRows)))>COLUMN(INDIRECT("C1:C"&ROWS(SourceDataRows),0))),SourceDataRows)

说明这个名称之前先简单说一下LOOKUP,我们已经知道了三个需汇总的数据表数据之和为12,分别为5,4,3。由于我们用INDIRECT()函数需确定是三个工作表中的哪个工作表,这种对应关系使我们自然想到用LOOKUP来实现这个功能。对于我们来说1-12这个自然数序列利用ROW(A1)下拉即可顺利得到,三个工作表的名字我们也是已知的,因此我们只需构造LOOKUP的第二参数即可。因此我们需要得到一个三个元素的数组,其中元素为区分第几个工作表的标志,理论上我们可以用(5;9;12)作为区分,但实际上由于LOOKUP函数实际查找的是等于或者小于第一参数最大的结果对应的返回值,我们一般从ROW(A1)开始查找,因此,LOOKUP的第二函数往前推一位:及{0;5;9}(同时ROW(A1)使用时减1配合),及第一参数为0-4(5个数字)时返回0对应的{"第一张测试表";"第 二 张 测 试 表";"3"}的第一个元素,第二参数为5-8(4个数字),返回5对应的第二个元素,第一参数大于等于9时返回第三个元素。因此,我们关键就是要构造出{0;5;9}这个数组。

我们已经有每个工作表的数据行数的数组,{5;4;3},需要构造出{0;5;9},咋搞呢?请大家参考胡剑版主的大作吧。

简单说MMULT函数就是矩阵相乘,第一个矩阵的第一行和第二个矩阵的第一列相乘作为新数组的第一列的第一个元素,第一个矩阵的第二行和第二个矩阵的第一列相乘作为新数组的第一列的第二个元素……;第一个矩阵的第一行和第二个矩阵的第二列相乘作为新数组的第二列的第一个元素,第一个矩阵的第二行和第二个矩阵的第二列相乘作为新数组的第二列的第二个元素……。因此,我们需要的MMULT第一函数的第一行全是0就可以得到目标数组的第一个元素,MMULT第二行第一个元素是1,其他都是0就可以得到5,也就相当于sumproduct({1,0,0},{5,4,3})的结果,MMULT第一函数的第三行前两个元素都是1,其他是0就可以的到9,相当于sumproduct({1,1,0},{5,4,3})的结果。当然,扩大结果范围求出累计数组{0;5;9;12}也是可以的,只不过我们不用最后一个元素也是可以的。MMULT运算结果参考附件图片:

5。SubName=MID(CELL("filename",!A1),FIND("]",CELL("filename",!A1))+1,99)
这个名称与汇总数据就无关了,只是取得各个工作表的名称,做一个标题点缀而已。以我电脑的“汇总”表为例:CELL("filename")返回字符串“C:\Users\clarkshao\Desktop\[合并拆分.xls]汇总”。利用FIND()查找到"]"的位置,然后用MID()函数从"]"的下一个字符起取99(可适当减小)个字符即可获得“汇总”这个工作表名。在实现这个功能的时候,CELL("filename")即可,不过写完整的参数个人认为是个好习惯(当然竞赛题目要求字符简短除外)。

至于汇总工作表中的具体公式应该就很好理解了吧,请大家自己用F9或者复制出来公式部分用多单元格数组公式的方式把结果反映在表格中对比研究,这样更加方便自己的理解。工作表拆分部分的公式也很简单,就不赘述了。

附件中函数的优点在于被汇总工作表名称不用有规律,任意即可。要拆分成的数据表也很容易操作(见附件动画),也可以多工作表同时编辑来完成。

[ 本帖最后由 shaowu459 于 2010-11-23 21:03 编辑 ]
飞信截屏未命名.png
测试使用.gif

合并拆分.rar

11.11 KB, 下载次数: 1903

TA的精华主题

TA的得分主题

发表于 2010-11-22 08:34 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2010-11-22 09:06 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2010-11-22 10:42 | 显示全部楼层
subname的定义中!E怎么会随着单元格的变动而变动,只是!没有明确表明哪个工作表的用法意义何在,这里不明白,还有cell的用法,filename是工作薄保存位置,那么cell第二个参数可以任意吗  ="单位:"&MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99),中A1 换其他也是一样结果,这点不明白,还有在数据源方面可以用OFFSET定义动态数据源吗,第一张测试表,第二章测试表,3等定义动态数据源后又怎样操作

TA的精华主题

TA的得分主题

发表于 2010-11-22 19:28 | 显示全部楼层

TA的精华主题

TA的得分主题

 楼主| 发表于 2010-11-23 17:33 | 显示全部楼层
原帖由 stary665533 于 2010-11-22 10:42 发表
subname的定义中!E怎么会随着单元格的变动而变动,只是!没有明确表明哪个工作表的用法意义何在,这里不明白,还有cell的用法,filename是工作薄保存位置,那么cell第二个参数可以任意吗  ="单位:"&MID(CELL("filena ...

如果单纯就cell()函数来说,如果只为取得工作表和工作薄路径只需要cell(“filename")即可,写第二参数是我自己的写函数习惯而已,如果写第二参数,则第二参数写那个单元格都无所谓,不用管

TA的精华主题

TA的得分主题

发表于 2010-11-23 18:13 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
学习一下!!!!!!!

TA的精华主题

TA的得分主题

发表于 2010-11-23 19:45 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2010-11-23 19:50 | 显示全部楼层
AccumulatedRows=MMULT(N(ROW(INDIRECT("1:"&ROWS(SourceDataRows)))>COLUMN(INDIRECT("C1:C"&ROWS(SourceDataRows),0))),SourceDataRows)  中利用ROW(INDIRECT("1:"&ROWS(SourceDataRows)))  与COLUMN(INDIRECT("C1:C"&ROWS(SourceDataRows),0)))对比得出与SourceDataRows对应的参数,我看到你们的答案觉得理所应当,但估计自己就是想不到,凑的话可以,但没有灵活行了,继续学习

TA的精华主题

TA的得分主题

 楼主| 发表于 2010-11-23 19:56 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
原帖由 stary665533 于 2010-11-23 19:50 发表
AccumulatedRows=MMULT(N(ROW(INDIRECT("1:"&ROWS(SourceDataRows)))>COLUMN(INDIRECT("C1:C"&ROWS(SourceDataRows),0))),SourceDataRows)  中利用ROW(INDIRECT("1:"&ROWS(SourceDataRows)))  与COLUMN(INDIRECT("C1 ...

你这次知道了,以后再碰到问题就会想得到了。一楼正在不断更新公式说明,请参考指正。mmult()函数的矩阵设计技巧可以搜索胡剑版主的大作,应该对你有所帮助。
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-29 08:39 , Processed in 0.048920 second(s), 10 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表