|
|
在浏览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 编辑 ] |
|
[复制链接]
沪公网安备 31011702000001号 沪ICP备11019229号-2
发表于 2010-11-22 03:49
发表于 2010-11-22 08:34
楼主
