第四期函中班入学考试题

小妖

四期函中入学考试题见附件。
答题请跟贴，本贴回贴是作者可见，所以不需要设置回复可见。
函入毕业的，提供上期毕业证明链接跟贴即可。（不过仍是建议自己做一下，如果这个题都做不下来，函中很难混的……）
水贴扣分，见谅！

PS，这是入学题，不是毕业题，不要想太难，按照题目要求做即可。
另外，请注意公式的扩展性，用IF套层的方法扩展性太差，不是函数解题的思路。

2011年3月18日开班，此贴同时结贴，此后再跟贴者仅供娱乐。

搁浅2008

=INDIRECT(TEXT(INT(ROW(A10)/10),"[DBNum1]")&"!R"&MOD(ROW(A10),10)+1&"C"&COLUMN(A10),)
利用TEXT得到工作表名一二三,利用MOD得到1-10的行号,然后得到1-5的列号 再用INDIRECT的RC格式得到单元格内容  简单公式我不会

禾火亦

=INDEX(CHOOSE(IF(ROW()-7<=COUNTA(一!$A:$A),1,IF(ROW()-7<=COUNTA(一!$A:$A)+COUNTA(二!$A:$A),2,3)),一!$1:$65536,二!$1:$65536,三!$1:$65536),IF(ROW()-7<=COUNTA(一!$A:$A),ROW()-7,IF(ROW()-7<=COUNTA(一!$A:$A)+COUNTA(二!$A:$A),ROW()-7-COUNTA(一!$A:$A),ROW()-7-COUNTA(一!$A:$A)-COUNTA(二!$A:$A))),COLUMN())

思路：
1、我用index函数从三个表里面取数，index（区域，行，列）
2、Index函数的第一个参数是索引区域，我们需要将这个区域变成动态的，那么可以用choose函数
3、choose的第一个参数我们必须让它动起来，因而公式所在的行行数减7（即ROW()-7）小于第一个表的数据行数的话，我们取第一个表的整个区域，如果：当前行标-7<第一个表的行数+第二个表的行数，那么我们取第二个表的区域，否则我们取第三个表的区域
   即index函数的区域部分为：CHOOSE(IF(ROW()-7<=COUNTA(一!$A:$A),1,IF(ROW()-7<=COUNTA(一!$A:$A)+COUNTA(二!$A:$A),2,3)),一!$1:$65536,二!$1:$65536,三!$1:$65536)
4、定义好了index的区域，那么我们要定义index函数的行，同样的道理，对于不同的表取数，我们对于行要进行类似的处理：
IF(ROW()-7<=COUNTA(一!$A:$A),ROW()-7,IF(ROW()-7<=COUNTA(一!$A:$A)+COUNTA(二!$A:$A),ROW()-7-COUNTA(一!$A:$A),ROW()-7-COUNTA(一!$A:$A)-COUNTA(二!$A:$A)))
公式所在的行行数减7（即ROW()-7）小于第一个表的数据行数的话，index的行=当前行标-7，如果：当前行标-7<第一个表的行数+第二个表的行数，那么index的行=当前行标-第一个表的数据行数-7，否则index的行=当前行标-第一个表的数据行数-第二个表的数据行数-7
5、Index的列等于当前公式所在列的列数即可。

liyh67

=INDIRECT(TEXT(INT((ROW()+2)/10),"[DBNUM1]")&"!R"&MOD(ROW()-8,10)+1&"C"&COLUMN(),)
前面三个表都是10行数据，利用这一点利用INDIRECT函数从中取出数据。TEXT函数将INT函数的结果转化为中文数字，使其与工作表名一致。

9lee

1. =IF(ROWS(二!A$1:A1)-COUNTA(一:二!A:A)>0,OFFSET(三!A$1,ROWS(三!A$1:A1)-COUNTA(一:二!A:A)-1,),IF(ROWS(二!A$1:A1)-COUNTA(一!A:A)>0,OFFSET(二!A$1,ROWS(二!A$1:A1)-COUNTA(一!A:A)-1,),一!A1))

复制代码

说明：
1 根据题意，设定三种情况下分别引用三个工作表的数据
2 第一种情况，引用第一个工作表数据：一!A1。
3 第二种情况，ROWS(二!A$1:A1)-COUNTA(一!A:A)>0，引用第二个工作表数据OFFSET(二!A$1,ROWS(二!A$1:A1)-COUNTA(一!A:A)-1,)。
4 第三种情况,ROWS(二!A$1:A1)-COUNTA(一:二!A:A)>0，引用第三个工作表数据OFFSET(三!A$1,ROWS(三!A$1:A1)-COUNTA(一:二!A:A)-1,)。

---

重新做过了，公式比较长，数组公式（按三键）：

1. =IF(ROW(A1)>COUNTA(一:三!A:A),"",OFFSET(INDIRECT(LOOKUP(ROW(A1)-1,MMULT(N(ROW(1:3)>TRANSPOSE(ROW(1:3))),SUBTOTAL(3,(INDIRECT({"一";"二";"三"}&"!a:a")))),{"一";"二";"三"})&"!a1"),ROW(A1)-1-LOOKUP(ROW(A1)-1,MMULT(N(ROW(1:3)>TRANSPOSE(ROW(1:3))),SUBTOTAL(3,(INDIRECT({"一";"二";"三"}&"!a:a"))))),COLUMN(A1)-1))

复制代码

说明：
1 还是用我比较喜欢的offset。
2 第一参数：各表的a1单元格

1. INDIRECT(LOOKUP(ROW(A1)-1,MMULT(N(ROW(1:3)>TRANSPOSE(ROW(1:3))),SUBTOTAL(3,(INDIRECT({"一";"二";"三"}&"!a:a")))),{"一";"二";"三"})&"!a1")

复制代码

3 第二参数：行位移

1. ROW(A1)-1-LOOKUP(ROW(A1)-1,MMULT(N(ROW(1:3)>TRANSPOSE(ROW(1:3))),SUBTOTAL(3,(INDIRECT({"一";"二";"三"}&"!a:a")))))

复制代码

4 第三参数：列位移

1. COLUMN(A1)-1

复制代码

5  当工作表有增减或移动时，可以修改：

• COUNTA(!A:A)中的“一:三”表示需要合并的第一个工作表和最后一个工作的名称，有增减或位置移动时需要修改
• ROW(1:3)中的3代表工作表个数，有n个工作表，就改成n
• {"一";"二";"三"}是所有需要合并的工作表名称，有增减时需要修改
  
  

6 不能用名称，不能用辅助列，不能用技巧，07中还不能用宏表函数……还要有延展性，所以，对我来说，这个题目有点难度，不知我这样的基础能不能学中级{:241:}

無心

交作业
=INDIRECT(LOOKUP(ROW(A1),{1,11,21},{"一","二","三"})&"!R"&(MOD(ROW(A10),10)+1)&"C"&COLUMN(A1),FALSE)

说明：
用indirect 的R1C1-格式引用三个表内的数据，因为三个表内都是十行五列的数据所以比较有规律
先用lookup查找表名
再找出行列规律，利用row和column拖动的规律。

只会做题，不会解释诶，嘴巴比较笨

海滨音诗

=IF(AND(ROUNDUP((ROW()-7)/10,)=1,COLUMN()<6),INDIRECT("一!r"&MOD(ROW()-8,10)+1&"C"&COLUMN(),),IF(AND(ROUNDUP((ROW()-7)/10,)=2,COLUMN()<6),INDIRECT("二!r"&MOD(ROW()-8,10)+1&"C"&COLUMN(),),IF(AND(ROUNDUP((ROW()-7)/10,)=3,COLUMN()<6),INDIRECT("三!r"&MOD(ROW()-8,10)+1&"C"&COLUMN(),),"")))
我的公式比较长哈。不过除了没达到“用最简洁的公式”这条外，其它要求好象都达到了
公式解释：
思路：if(条件一成立，取表一数据，if(条件二成立，取表二数据，if(条件三成立，取表三数据，否则为空)))
AND(ROUNDUP((ROW()-7)/10,)=1,COLUMN()<6)，用IF函数判断ROUNDUP((ROW()-7)/10,)是否等于1以及列号是否小于6，条件同时成立，用inditect()取“一”中的数据。
INDIRECT("一!r"&MOD(ROW()-8,10)+1&"C"&COLUMN(),)：MOD(ROW()-8,10)+1引用的行号。

海滨音诗

再发一个短一些的公式：=INDIRECT(TEXT(ROUNDUP((ROW()-7)/10,)+1,"aaa")&"!r"&MOD(ROW()-8,10)+1&"C"&COLUMN(),)
没有除错。

TEXT(ROUNDUP((ROW()-7)/10,)+1,"aaa"),这个公式就是要得到“一”，“二”，“三”，text(1,"aaa")是得到星期日中的“日”，text(2,"aaa")才得到“一”，所以我就把原来的结果上再加上1。当然，这仅限表少的。
其它的上面已经做了解释。

twtthduu

过路学习一下，虽然我不能上函中，但我还是想学习一下。

twtthduu

回复 小妖 的帖子

请问老师第四期函中班入学考试题我为什么打不开呢。