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

gouweicao78

请参考附件。Sheet1的N列做辅助列判断是否与输入条件相等，并以此返回1、2、3等序号以供查询。

窦国祥

不好意思！经过试用以后，发现还有点小问题：如果sheet2中B1到B4单元格中一个数据也不输入的话，下面会把全部的数据显示出来，不知道是怎么回事？
非常不好意思，麻烦了！

gouweicao78

一个都不输入的话，也就是没有条件限制。可以在原先基础上增加判断，比如：
=IF(COUNTA($B$1:$B$4),IF(ROW(1:1)>MAX(Sheet1!$N:$N),"",LOOKUP(ROW(1:1),Sheet1!$N:$N,Sheet1!A:A)),"")

其实第2、3次提问是解题、而不是解释公式，都不属于【答疑解惑】范畴，您应该另行发帖求助。

pursh

=SUM(B9:K10)-SUM(SMALL(B9:K10,{1,1}))-SUM(LARGE(B9:K10,{1,1}))
B9:K10为数据源
这个是一个去掉最大值和最小值的求和数组公式，看不明白。
SUM(LARGE(B9:K10,{1,1})中的{1,1}，看了公式审核也看不明白，不够详细
请大侠们帮忙解释下。

gouweicao78

请注意本帖帖规。要么上传附件，要么给链接，你给的图片别人无法编辑。
这个公式不合理，SMALL(B9:K10,{1,1})求的是2次B9:K10的最小值，再用SUM求和，那就直接可以用2*MIN(B9:K10)，同理LARGE可以换成MAX

也就是说，你给的这个公式是去掉了2次最大值、2次最小值再求和，而不是1次，如果要一次只需要：
=SUM(B9:K10)-MAX(B9:K10)-MIN(B9:K10)   即可。

zh596

请帮忙解释下公式，看不懂，但很需要。
公式出处：http://club.excelhome.net/viewth ... =%B0%B4%D7%DC%B7%D6，2楼山菊花，内有附件WTmFtns6.rar 。

=IF(ROW(1:1)>COUNTIF(七年级!C:C,B$1),"",INDEX(七年级!B:B,RIGHT(LARGE(data*10^6+ROW(data),ROW(1:1)),4)))

data =IF(成绩查询!$B$2="",10000-OFFSET(七年级!$B$2,MATCH(成绩查询!$B$1,七年级!$C:$C,)-2,,COUNTIF(七年级!$C:$C,成绩查询!$B$1)),OFFSET(七年级!$D$2,MATCH(成绩查询!$B$1,七年级!$C:$C,)-2,MATCH(成绩查询!$B$2,成绩查询!$D$3:$L$3,),COUNTIF(七年级!$C:$C,成绩查询!$B$1)))

【gouweicao78答复】
1、IF(ROW>COUNTIF,""——如果超过七年级表C列中内容为B1单元格的记录个数，返回空文本
2、LARGE(data*10^6+row(data),row(1:1))——这个是本帖“关键字排序技术”解释过的，将data乘以10的6次方后，再加data的行号，也就是按data的大小排序，因为*10^6，且data可能有2位小数，所以加的行号只可能在最后4位数，所以用RIGHT(LARGE,4)取得行号。
3、data——分解开，无法就是一个个OFFSET而已
OFFSET(起始单元格，偏移的行数，偏移的列数，结果的高度，结果的宽度）
比如：
OFFSET(七年级!$D$2,MATCH(成绩查询!$B$1,七年级!$C:$C,)-2,MATCH(成绩查询!$B$2,成绩查询!$D$3:$L$3,),COUNTIF(七年级!$C:$C,成绩查询!$B$1))
起始单元格：七年级!$D$2
偏移行数：MATCH(成绩查询!$B$1,七年级!$C:$C,)-2——查询表B1的内容，在七年级表C列第一次出现的位置，减去2
偏移列数：MATCH(成绩查询!$B$2,成绩查询!$D$3:$L$3,)——查询表B2的内容，在查询表D3:L3第一次出现的位置
结果高度：COUNTIF(七年级!$C:$C,成绩查询!$B$1)——七年级表C列有多少个查询的B1的内容，就返回多少行记录。

[ 本帖最后由 gouweicao78 于 2009-12-10 11:13 编辑 ]

pursh

请帮忙解释下公式：
=IF(SUM(1/COUNTIF($B$7:$B$20,$B$7:$B$20))>=ROW(B3),INDEX($B$7:$B$20,SMALL(IF(ROW($B$7:$B$20)-6=MATCH($B$7:$B$20,$B$7:$B$20,0),ROW($B$7:$B$20)-6,"0"),ROW(B3))),"END")
公式我大致可以看个明白，就是不知道思路是如何得出结果，不知道公式里面的精髓所在？
具体是什么题得出该公式的，请见附件。
在这里感谢版主。顶

【gouweicao78回复】：本帖首页可以看到关于SUM(1/COUNTIF)求不重复个数的解释链接；index+small（if，row）的经典解法，本帖也有多个回复。而附件中的公式用的解法是早期的解法。
精髓所在：
1、SUM(1/countif)——将个数变为分母，假设有3个，则分别为1/3、1/3、1/3，再用SUM求和得1——也就是重复的3个只算1个；
2、row-6=match——match判断的是数据在数据中首次出现的位置，row-6（起始行号）得到1、2、3……的等差序列，也就是第一次出现位置如果与序号相同，则这个数据是“首次出现”的，因而“第2次”及以后出现的都被这个条件去掉了。

[ 本帖最后由 gouweicao78 于 2009-12-10 11:02 编辑 ]

9a128

另请问是否正确：
A：{=SMALL（DATA2，ROW（DATA2）}多单元格数组公式只能从首行执行。
B：如DATA2未从首行开始，一定要ROW（DATA2）-N〈N为DATA2名称的上一行行号〉。

【gouweicao78答复】请遵守帖规，没附件？没链接？没头没尾问这么两个问题，确实不知道怎么答。

[ 本帖最后由 gouweicao78 于 2009-12-10 11:04 编辑 ]

xiatide334

这是目前我遇到比较复杂的公式了，庖丁解牛中关节老是拿不准，麻烦陈版主给解释下。我得去从首页慢慢开始看消化了

MID(A1,FIND(LEFT(TRIM(A1)),A1),FIND("☆",SUBSTITUTE(A1,RIGHT(TRIM(A1)),"☆",LEN(A1) - LEN(SUBSTITUTE(A1,RIGHT(TRIM(A1)),""))))-FIND(LEFT(TRIM(A1)),A1)+1)

谢谢

【gouweicao78答复】你先看帖规吧，没附件，没链接？

[ 本帖最后由 gouweicao78 于 2009-12-10 11:03 编辑 ]

nectar

请教下面这个公式：
=MATCH(B4,LOOKUP(SMALL(x,ROW(INDIRECT("1:"&COUNT(IF(x<>65536,1,""))))),ROW(myb),myb),0)
Book1.rar (2 KB, 下载次数: 28)

2009-12-10 19:56 上传

点击文件名下载附件