|
gvntw版主的只用函数实现分表合并与总表拆分。 公式解释： （应用实例：同一文件夹下多个Excel工作簿分类汇总的汇总，见 [求助]原材料汇总表的函数汇总公式 第6楼。） 一、根据各个分表的记录个数给各个分表标上一个记录指针 Num_1=COUNTIF(INDIRECT(sh&"!A3:A100"),">0")——意在取得sh（={1;2;3}）三个分表中A3:A100区域>0（即有编号）的记录个数。得到3行1列的数组（与sh同），在本例中结果为{5;4;3} Num_2=MMULT(N(ROW(INDIRECT("1:"&ROWS(Num_1)))>COLUMN(INDIRECT("C1:C"&ROWS(Num_1),0))),Num_1) ——意在取得以0开头的Num_1（{5;4;3}）的累加数组，即0、0+5、0+5+4，由此给每个分表标上一个记录指针。 其中： ROWS(Num_1)——取得Num_1的行数，实际上也就是分表的个数，可以改为Rows(sh)，本例中为3行， 则Row(Indirect("1:3"))===得到Row(1:3)，即{1;2;3}——3行1列 Column(Indirect("C1:C3",0))===相当于得到Column(A:C)，即{1,2,3}——1行3列。 ROW>COLUMN==={1;2;3}>{1,2,3}===得到3行3列的数组： {FALSE,FALSE,FALSE;TRUE,FALSE,FALSE;TRUE,TRUE,FALSE} N()函数将True和False转换为1和0，也可以用--、*1、/1等方法转化。得到{0,0,0;1,0,0;1,1,0}。 MMULT(array1,array2)返回两数组的矩阵乘积。结果矩阵的行数与 array1 的行数相同，矩阵的列数与 array2 的列数相同。 MMULT({0,0,0;1,0,0;1,1,0},{5;4;3})==这两个矩阵相乘，得到Num_2: {0;5;9}——事实上，这个就是Num_1的累积求和，即第一张表5个记录，第2个表4个记录，第3个表3个记录——0~4对应第一张表的5个记录，5~9对应第2张表的4个记录（5+4），超过9之后就是第3张表的记录。 二、利用Lookup通过记录指针来查找对应的表格名称 Lookup(Row(1:1)-1,Num_2,sh)—— 当公式在第1行时Row(1:1)-1=0，即：Lookup(0,{0;5;9},{1;2;3})，得到1，即表1。 当公式在第6行时Row(6:6)-1=5，即：Lookup(5,{0;5;9},{1;2;3})得到2，即表2， ……这就实现了根据各个分表的记录个数来自动选择引用的表格的功能。 =IF(ROW(1:1)>SUM(Num_1),"",OFFSET(INDIRECT(LOOKUP(ROW(1:1)-1,Num_2,sh)&"!A2"),ROW(1:1)-LOOKUP(ROW(1:1)-1,Num_2),COLUMN()-1)) 所以，Offset()的第一个参数引用位置为与公式所在行数对应的分表的A2单元格。 ROW(1:1)-LOOKUP(ROW(1:1)-1,Num_2)即偏移的行数，同理，Lookup查到的是应该扣去的行数。 比如：第1张表5个记录，那么在第6行的时候（即Row(6:6)）引用第2张表则应该从2！A2偏移1行而不是6行，Row(6:6)-Lookup(Row(6:6)-1,{0;5;9})得到6-5=1。即记录重新开始算个数。 最后Row(1:1)>Sum(Num_1)就是当公式拉下来的行数已经超过所有分表的记录个数之和，显示为空。
[此贴子已经被作者于2006-11-25 11:46:19编辑过] | 
楼主: gouweicao78
[复制链接]
沪公网安备 31011702000001号 沪ICP备11019229号-2
楼主
发表于 2006-10-23 14:54
发表于 2006-10-23 16:08
