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

chemiee

QUOTE:

以下是引用cxc1248在2008-4-28 20:01:52的发言：

请问各位老师，这个表里面的那个中国式排名的公式二是什么意思，我找了好多地方都没有中文解释，我实在看不懂，因为我以前用的都是公式一，但是公式一，有跳数的弊端。我还是想用公式二，但是看不懂，特别是后面半段就更看不懂了，为什么要用1去除它？？最后又为什么要加上1呢？各位能给我讲下它的中文意思吗？？我都晕了。谢谢大家了！！

=SUM(IF($B$2:$B$21>B2,1/COUNTIF($B$2:B$21,B$2:B$21)))+1

[em06]

CZZQB注 ：   请看一下本帖7楼山菊花版主给出的链接  6-2

用1除可以参照CZZQB注。

SUM(1/COUNTIF($B$2:B$21,B$2:B$21))用来计算区域中不重复值的个数,前面的判断$B$2:$B$21>B2很关键，即仅仅当区域中的数据大于B2(当前数据)时计算在内，而小于或等于的数据为FALSE。换句话说这里实际上是计算区域中比当前数据大的数据的个数，这个就符合中国式排名的要求了，即前面有成绩相同的第一名，他们只是并列排名第一，而我还是第二。为什么要加1？那是因为判断中用的是>号，把等于的数据排除在外了。如果把>号改成>=，加1当可去除。按照运算过程，公式可以省略IF改为：

=SUM(($B$2:$B$21>B2)/COUNTIF($B$2:B$21,B$2:B$21))+1 或=SUM(($B$2:$B$21>=B2)/COUNTIF($B$2:B$21,B$2:B$21))

或用=SUMPRODUCT(($B$2:$B$21>=B2)/COUNTIF($B$2:B$21,B$2:B$21)) 普通公式

原理是用$B$2:$B$21>B2生成的数组，{TRUE;FALSE;....}除以COUNTIF()。用TURE去除以其它数，相当于用1去除以其它数，FASLE则相当0了，而0除以任何数都得0，起到屏蔽不符合要求的数据的作用。SUMPRODUCT则本省支持多重运算，所以不用以数组形式输入。

[此贴子已经被作者于2008-5-5 11:52:26编辑过]

jiangchunlin

这个函数我也看不懂。

22-33JKL 53P5-8PO 把它提取成22-33 53P5-8

的样子,我看到别人用的函数是

=LEFT(A1,MATCH(,-MID(A1,ROW($1:$99),1))) 或是

=LEFT(A6,LOOKUP(0,-MID(A6,ROW($1:$50),1),ROW($1:$50)))或是

=LEFT(A2,LOOKUP(10,--MID(A2,ROW($1:$20),1),ROW($1:$20)))

在这里ROW($1:$99)是什么意思?还有-MID(),最好是详细讲解一下这个函数.谢谢 !

hzhcong

求得如下公式,但弄不懂,请G老师帮帮忙说明一点..

=INDEX(M:M,SMALL(IF(M$4:M$120<>".",ROW(M$4:M$120),1000),ROW(1:1)))&""

还有一个问题就是:

这个公式在右拉或下拉的时候就出现"###"..?

[此贴子已经被作者于2008-5-7 12:41:02编辑过]

chemiee

QUOTE:

以下是引用jiangchunlin在2008-5-6 20:05:19的发言：

这个函数我也看不懂。

22-33JKL 53P5-8PO 把它提取成22-33 53P5-8

的样子,我看到别人用的函数是

=LEFT(A1,MATCH(,-MID(A1,ROW($1:$99),1))) 或是

=LEFT(A6,LOOKUP(0,-MID(A6,ROW($1:$50),1),ROW($1:$50)))或是

=LEFT(A2,LOOKUP(10,--MID(A2,ROW($1:$20),1),ROW($1:$20)))

在这里ROW($1:$99)是什么意思?还有-MID(),最好是详细讲解一下这个函数.谢谢 !

ROW($1:$99) 取1至99行的行号，即生成{1;2;3;4;...;99}这些数字组成的内存数组。

-MID(A1,ROW($1:$99),1)---》-MID(A1,{1;2;3;4;...;99},1)对A1中的数据进行截取，分别从{1;2;3;4;...;99}位开始截取，都取一位，得到{"2";"2";"-";"3";"3";"J";"K";"L";"";....} 后面省略的都是空文本，前面加减号-，相当于对数组内部进行乘以-1的运算得到{-2;-2;#VALUE!;-3;-3;#VALUE;...}，文本进行数学运算时得错误值;#VALUE!，然后用MATCH函数在这个数组中查找0（省略的参数）的位置，MATCH的最后一个参数省略相当于1，返回的是最后一个小于或等于0的数值的位置，即最后一个数字的位置--》5。再用LEFT取A1左边5位字符就得到结果了。

其它两个函数原理相同，自己体会。

chemiee

QUOTE:

以下是引用hzhcong在2008-5-7 12:04:10的发言：

求得如下公式,但弄不懂,请G老师帮帮忙说明一点..

=INDEX(M:M,SMALL(IF(M$4:M$120<>".",ROW(M$4:M$120),1000),ROW(1:1)))&""

还有一个问题就是:

这个公式在右拉或下拉的时候就出现"###"..?

这个公式似乎是我帮你写的，解释一下。

我测试时通过的，具体原因要你上传附件来看看出什么问题。

IF(M$4:M$120<>".",ROW(M$4:M$120),1000)----》如果M$4:M$120的值不等于"."，则返回其相应的行号ROW(M$4:M$120),否则为1000（最大可以为65536，Excel的最大行数，之所以用1000，我发现你的数据不会用到1000行，保险起见写65536较好）。比如M4的值为"."而M5不为"."，则返回{1000;5;...}一次类推。然后用small取其中的第几个最小值，row(1:1)用行号控制取第一个，往下拉则是第二个，...，当符合条件的行没有了后则返回1000。然后用INDEX取M列对应行号的值&""，得到强制文本结果。当没有条件符合时行号为1000则返回M1000&""相当于空文本""。

窦国祥

麻烦老师帮忙解释这个公式:

{=OFFSET(总表!$A$1,MATCH(1,(总表!$A$1:$A$999=分表!$B$6)*(总表!$B$1:$B$999=分表!$D$6),0)-1,ROW(12:12))&""}

谢谢!

CZZQB注：  在这个公式里，公式的这一部分 (总表!$A$1:$A$999=分表!$B$6)*(总表!$B$1:$B$999=分表!$D$6) 组成一个类似{1;1;0;1;……}这样的数组，其中1表示满足条件的行号，而0表示不满足条件。用MATCH(1, 在这样一个数组中查找，就返回了满足条件的第一个行号，在MATCH之后，整个公式变成了： OFFSET($A$1,[满足条件第一行]，ROW(12:12)) 也就是说，这个公式下拉以后，得到的分别是 表中A列和B列第一个同时满足条件的行对应的M列及其右边各列的数据。

[此贴子已经被czzqb于2008-5-13 20:52:42编辑过]

lmxlmx

满腔热情，却看不懂。看过一个

=E23*LOOKUP(D23,$C$18:$C$20,$D$18:$D$20)

请问“$C$18:$C$20,$D$18:$D$20”是怎么整上去的？

 

CZZQB： 怎么整上去的？一个个字符敲进去的呀！^_^ 当然，也可以先写入C18:C20,D18:D20 然后在编辑栏里用鼠标拉黑这一段，按一下F4，它就会变成$C$18:$C$20,$D$18:$D$20 可以多按几下F4，看看它是怎么变的……

[此贴子已经被czzqb于2008-5-13 21:18:49编辑过]

ynztljm

请教Excel高效办公职场对话系列视频课程中第六集 Excel高效办公之职场对话系列-学校管理 中关于补考名单的几个公式的解释。http://club.excelhome.net/viewthread.php?tid=322758&px=0
谢谢！！！谢谢！！！谢谢！！！

1、{=INDIRECT(TEXT(SMALL(IF($C$4:$Q$19<60,ROW($4:$19),65536),ROW(1:1)),"r0c1"),)&""}

2、{=INDIRECT(TEXT(SMALL(IF($C$4:$Q$19<60,ROW($4:$19),65536),ROW(1:1)),"r0c2"),)&""}

3、{=INDIRECT("r3c"&RIGHT(SMALL(IF($C$4:$Q$19<60,ROW($4:$19)*1000+COLUMN($C:$Q),65536255),ROW(1:1)),3),)&""}

4、{=INDIRECT("r2c"&RIGHT(SMALL(IF($C$4:$Q$19<60,ROW($4:$19)*1000+3*INT(COLUMN($C:$Q)/3),65536255),ROW(1:1)),3),)&""}

5、{=INDIRECT(TEXT(SMALL(IF($C$4:$Q$19<60,ROW($4:$19)*1000+COLUMN($C:$Q),65536255),ROW(1:1)),"r0c000"),)&""}

gouweicao78

QUOTE:

以下是引用ynztljm在2008-5-26 18:14:19的发言：

请教Excel高效办公职场对话系列视频课程中第六集 Excel高效办公之职场对话系列-学校管理 中关于补考名单的几个公式的解释。http://club.excelhome.net/viewthread.php?tid=322758&px=0
谢谢！！！谢谢！！！谢谢！！！

1、{=INDIRECT(TEXT(SMALL(IF($C$4:$Q$19<60,ROW($4:$19),65536),ROW(1:1)),"r0c1"),)&""}

2、{=INDIRECT(TEXT(SMALL(IF($C$4:$Q$19<60,ROW($4:$19),65536),ROW(1:1)),"r0c2"),)&""}

3、{=INDIRECT("r3c"&RIGHT(SMALL(IF($C$4:$Q$19<60,ROW($4:$19)*1000+COLUMN($C:$Q),65536255),ROW(1:1)),3),)&""}

4、{=INDIRECT("r2c"&RIGHT(SMALL(IF($C$4:$Q$19<60,ROW($4:$19)*1000+3*INT(COLUMN($C:$Q)/3),65536255),ROW(1:1)),3),)&""}

5、{=INDIRECT(TEXT(SMALL(IF($C$4:$Q$19<60,ROW($4:$19)*1000+COLUMN($C:$Q),65536255),ROW(1:1)),"r0c000"),)&""}

请先浏览一下本帖，上述内容多数已有解释。

IF($C$4:$Q$19<60,ROW($4:$19),65536)——如果条件成立，返回第4~19行的行号，否则返回65536（为什么要用65536？看后面）

SMALL(IF(),ROW(1:1))——将IF得到的数组结果，用Small+Row(1:1)下拖的方式，从小到大排序

TEXT(SMALL(),"r0c2")——将Small得到的数字，用Text返回为指定的数字格式的文本，比如数字10，则返回"r10c2"——在R1C1引用样式中，表示第10行第2列，也就是B10单元格。

INDIRECT(R1C1样式的文本，0)——用INDIRECT函数引用该文本对应的单元格

假设INDIRECT(R65536C2,0)——引用B65536单元格，一般情况这么后面的单元格不会有东西，B65536&“”——空单元格与空文本合并，得到空文本，相当于——IF(……,""）的效果。

cynthia02

http://club.excelhome.net/viewthread.php?tid=50146&extra=&page=1#624207  3楼老师的答复看不懂，请斑主分步解释好吗，因为要套用在其他格式，由于弄不懂无法应用，请教了。

1.={IF(E8=0,"",E8*SUM(DAYS360(IF(ROW(INDIRECT(a&":"&b))=a,B8,d),IF(ROW(INDIRECT(a&":"&b))=b,$C$2,dd))*OFFSET($C$134,a-1,0,b-a+1,1))/360)} ?

2.=INDIRECT(a&":"&b)  ?

3.=ROW(INDIRECT(a&":"&b))=a  ?

4.=IF(ROW(INDIRECT(a&":"&b))=a,B8,d) 与 =  IF(ROW(INDIRECT(a&":"&b))=b,$C$2,dd）分别是？？？

5.=OFFSET($C$134,a-1,0,b-a+1,1))/360)  ？

多谢！！  多谢！！！  多谢！！！

[此贴子已经被作者于2008-6-3 17:20:31编辑过]