浏览器之家


EXCEL分表合并、总表拆分(函数版)

    一、根据各个分表的记录个数给各个分表标上一个记录指针
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)就是当公式拉下来的行数已经超过所有分表的记录个数之和,显示为空。

可参照: http://www.officeba.com.cn/common/upload/2007/10/7/93721lg.rar

评论

没安装畅言模块