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)就是当公式拉下来的行数已经超过所有分表的记录个数之和，显示为空。

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

声明：欢迎各大网站转载本站文章，还请保留一条能直接指向本站的超级链接，谢谢！

时间:2007-10-07 09:38:35,点击:65824