[答疑解惑]函数公式解释专用帖

Matero · 发表于 2010-10-27 17:26

本回复纯属混数，请忽略

greyfire · 发表于 2010-11-8 11:03

=INDEX(F:F,SMALL(IF(COLUMN(A:AZ)>G$2:G$83,99,ROW($2:$83)),ROW()-1))&""
请问这个公式怎么解释，IF(COLUMN(A:AZ)>G$2:G$83这个部分按F9根本不行啊！

gouweicao78 · 发表于 2010-11-8 11:47

请遵守帖规，给出链接或附件。

COLUMN(A:AZ)>G$2:G$83，其中COLUMN产生1~52的数字，为52列的水平数组；G2:G83是垂直数组，因此得到一个82行*52列的TRUE、FALSE结果。所以，你按F9估计无法显示完全。

greyfire · 发表于 2010-11-8 14:15

原帖由 gouweicao78 于 2010-11-8 11:47 发表
请遵守帖规，给出链接或附件。

COLUMN(A:AZ)>G$2:G$83，其中COLUMN产生1~52的数字，为52列的水平数组；G2:G83是垂直数组，因此得到一个82行*52列的TRUE、FALSE结果。所以，你按F9估计无法显示完全。

版主能否给详细讲讲INDEX(F:F,SMALL(IF(COLUMN(A:AZ)>G$2:G$83,99,ROW($2:$83)),ROW()-1))&""的工作原理哪？
附件如下：

[ 本帖最后由 greyfire 于 2010-11-8 14:33 编辑 ]

gouweicao78 · 发表于 2010-11-8 14:22

公式的解释，其实可以小改一下：
=INDEX(F:F,SMALL(IF(COLUMN(A:AE)>G$2:G$83,99,ROW($2:$83)),ROW()-1))&""

因为COLUMN(A:AE)产生1-31的数字，而每个考场安排最多人数是30，也就是说：COLUMN(A:AE)>G2判断G2小于1~30，则返回99，否则返回ROW(2:2)，因为G2=30，所以只有1个返回99，而其他的返回了30个row(2:2)

COLUMN(A:AE)>G2:G83，道理一样，将G2:G83的值分别与1~31相比，返回个数相当的ROW(2:83)，其余返回99。

最后INDEX+SMALL取出这些ROW(2:83)对应的F列的考场号，取到99的就是F99单元格的空值了。

给个辅助列的解法，速度快而又简单：
H2输入：=SUM(G$1:G1)+1，向下复制。
D2输入：=LOOKUP(ROW()-1,H:H,F:F)，向下复制。

不用辅助列的解法（数组公式）：

=LOOKUP(ROW()-1,MMULT(--(ROW($2:$83)>TRANSPOSE(ROW($2:$83))),G$2:G$83)+1,F$2:F$83)

复制代码

[ 本帖最后由 gouweicao78 于 2010-11-8 16:04 编辑 ]

greyfire · 发表于 2010-11-8 16:40

是不是可以这样理解：
COLUMN(A:AE）是比考场最大的考生数多1，
99是比G83多的一个数（10000也无所谓）
INDEX(F:F,SMALL(IF(COLUMN(A:AE)>G$2:G$83,99,ROW($2:$83)),ROW()-1))&""
是用IF(COLUMN(A:AE)>G$2:G$83,99,ROW($2:$83））制造一个30个row（2），20个row（3）…………和“N”个99的数组，
用small函数对他排序
最后INDEX+SMALL取出这些ROW(2:83)对应的F列的考场号，
至于99，因为row（2:83）刚好填充完，‘N"个99根本就没有填充？

gouweicao78 · 发表于 2010-11-8 16:57

99，是比2-83大的行号，一般用4^8也就是65536行，即Excel 2003的最大行数，那里一般是空单元格，用来与&""返回""的。

column(a:ae)——AE必须大于等于G2:G83的最大值，比如可以用COLUMN(A:AD)，但不能再小了，再大则运算量更大。

这种大运算量的公式，不是解决问题的办法，况且给你方便的，没有必要在这个又难又慢的公式上花精力。

关于INDEX+SMALL解法以及&""的应用，在本帖前面都解释过，自己可以翻翻看。

greyfire · 发表于 2010-11-9 09:42

楼主你好，谢谢你的解答，我还是倾向于第一种解法，因为经过试验第二，和第三种解法有些问题，最后的考生实际上超出了考场安排的范围，也就是说82考场安排了8个人，而你说的后两种解法，把8个人的考场里面安排了9个考生！

gouweicao78 · 发表于 2010-11-9 12:06

简单，加判断就是了：
=IF(ROW()-1>SUM(G:G),"考场不够",LOOKUP(ROW()-1,H:H,F:F))

速度的优势，是你附件中那个公式无法比拟的。

greyfire · 发表于 2010-11-9 13:59

重新试过了，说的一点也不错，谢谢版主的无私帮助！
向版主学习！！！！

		自动登录	找回密码
密码			免费注册

[推荐] [答疑解惑]函数公式解释专用帖

求教！

回复 542楼 greyfire 的帖子

回复 544楼 greyfire 的帖子

回复 545楼 gouweicao78 的帖子

回复 547楼 gouweicao78 的帖子

回复 548楼 greyfire 的帖子

回复 549楼 gouweicao78 的帖子