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

gouweicao78

这些解释在本帖里都好几次提过的。
4^8是65536，即Excel 2003的最大行数，配合&""用以将A65536单元格这个空单元格返回为空文本，以便容错的。

ganheye

各位大师，能帮忙解释一下数组公式SUM(MOD(SMALL(IF($B$2:$B$50=LEFT($H3),RANK($F$2:$F$50,$F$2:$F$50)*1000000+$A$2:$A$50*1000+C$2:C$50,90^9),ROW($1:$5)),1000))/5
求各班总分前5名学生（注意：以总分为标准）各科平均分，如果第5名有多人，选序号在前者。网友给的公式，水平有限，真的看不懂。特别是加颜色部分，实在是搞不懂。谢谢指教。 成绩.rar (2.85 KB, 下载次数: 37)

2010-11-29 09:37 上传

点击文件名下载附件

感谢大师的指导，茅塞顿开呀！

[ 本帖最后由 ganheye 于 2010-12-3 17:00 编辑 ]

gouweicao78

本帖28楼，按多个关键字排序技术。

SMALL里面，条件是班级=LEFT(H3)，满足着返回RANK排名*10^6+A列序号*1000+C列成绩，否则返回90^9。
那么RANK排名的权重最大，是主要关键字；接下来A列序号权重次之，是次要关键字；再是成绩。比如说成绩有3个成绩为：10、30、10，Rank排名数字越小就越靠前，排名为2、1、2，序号分别为1、2、3的话，那么乘上权重之后变为2001010、1002030、2003010，因此SMALL排序后为1002030、2001010、2003010——即名次越靠前拍前面，同样分数，序号越小排前面。
MOD(加权后的数字，1000）——除权，返回不足1000部分的值，也就是成绩本身，依次得到30（序号2）、（序号1）10、10（序号3）。

laowuwyx

草版主，你好，对一个前辈写的关于跨表提取不重复的公式理解不透，特别是对于Disc和ALLDATA这个两个名称，太复杂了，请指点迷津。谢谢！

gouweicao78

1. =INDIRECT("Sheet"&TEXT(MIN(IF(COUNTIF(B$2:B2,T(INDIRECT("Sheet"&{1,2,3}&"!A"&ROW($2:$15)))),3999,{1,2,3}*1000+ROW($2:$15))),"0\!A000"))&""

复制代码

一个公式搞定。

laowuwyx

原帖由 gouweicao78 于 2010-12-4 12:57 发表
=INDIRECT("Sheet"&TEXT(MIN(IF(COUNTIF(B$2:B2,T(INDIRECT("Sheet"&{1,2,3}&"!A"&ROW($2:$15)))),3999,{1,2,3}*1000+ROW($2:$15))),"0\!A000"))&""一个公式搞定。

草版果然厉害，还以为这种问题必须要复杂无比公式才能解决，受教了！
但是有个小问题，为何B2单元格必须要是空单元格才行呢，否则提出的姓名就会不连续，中间始终有一个单元格“空着“？

gouweicao78

B2要为空单元格或空文本，因为COUNTIF统计也把空文本看作是一个“文本值”之一。
这个公式的解释，实际上本帖也已经有了。
思路：
1、大框架使用与多行多列不重复值一样，比如常用的TEXT(SMALL(IF(满足条件，行数*1000+列数),ROW(1:1)),"R0C000")，来将满足条件的单元格行、列数一次性转换成R1C1数字格式，再供INDIRECT函数引用。
2、当条件使用COUNTIF的时候，往往配合MIN来使用，比如MIN(IF(COUNTIF,大行数,对应行数))——当COUNTIF也就是“包含”了，就返回大的行数，否则返回对应行号。始终是取最小的，因为一旦列出来了，就成了不满足条件的。这是COUNTIF法求不重复值的一个特性。
3、把第1点中的行号、列号换成工作表名的数字、行号，就成了这个公式的核心。
4、COUNTIF的第2参数INDIRECT产生多维引用，必须使用T函数来将其转为文本。

laowuwyx

还有一个问题，就是如果工作表名称不规则是规则的1/2/3，而是一些中文名称，如“上，中，下”，又如何处理？谢谢！
今天我也买了一本草版的新书。

gouweicao78

不规则表名，问题就变麻烦了。要使用宏表函数GET.WORKBOOK(1)取得全部工作表名内存数组，如果汇总表的位置不定，还需要再取得除去汇总表外的其他工作表名内存数组，以及各表内记录个数等等，类似于你前面求解释的那样。

这种情况，我个人不会建议使用函数来解决。可以使用合并计算、数据透视、sql汇总等各种其他方法，就是不适合用公式，呵呵。

关于新书，谢谢支持！

42921783

原帖由 gouweicao78 于 2006-11-25 10:07 发表
QUOTE:[函数用法讨论系列10] LOOKUP的查找策略！之流程图再谈二分法不要急，一开始我看了许久也没看懂的，后来查了一些“二分法”（也称“半分法”）方面的东西就简单了。比如数据=1、2、3……、100——升序排列，则 ...

看了139楼和另外一个帖子--【公式解析系列】之LOOKUP(2,1/(条件),查找数组或区域)（http://club.excelhome.net/viewth ... e=1&authorid=112469），未明白的地方：

A.【公式解析系列】中，要在{#DIV/0!;1;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;#DIV/0!}中查找2，你在回复 64楼 ppluexcel 时提到“此解法利用的正是LOOKUP函数避开错误值”，意思是说“lookup函数避开了错误值#DIV/0!，只考虑在2个"1"来查找”吗？

B.在理解LOOKUP(1,0/(条件),查找数组或区域)时，假设要在{#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!;;#DIV/0!;#DIV/0!;#DIV/0!}中查找1，0就是该数组中最大的值吗，意思是错误值#DIV/0!就比0小？还是干脆就避开了#DIV/0!就只在1个0中来选择最大值。

C.看了你制作的那个动画，感觉看起来还是比较吃力，建议考虑用PPT来演示动画过程，因为PPT可以控制播放节奏，想停就停，想继续就继续，想倒退就倒退。

[ 本帖最后由 42921783 于 2010-12-10 18:56 编辑 ]