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

gouweicao78

to pengqidong朋友：

看清帖规，少占楼层。已合并，呵呵。

1、看一下本帖首页，了解一些基本知识，并多看函数帮助。比如Row、Column这些基本函数的用法。

2、"常见用数组公式取得多条记录的开头都是=if(row(1:1)>XXX,""……表示当公式下拉复制超过一定行数（满足条件记录的个数）时，显示为空。"

比如，=if(Row(1:1)>Countif(A:A,"张三"),"",……此时，用A列包含多少个“张三”的记录来控制行数。假设A列有3个张三的记录，那么，公式第一行=if(Row(1:1)>3不成立，第2行=If(row(2:2)>3也不成立……到了第4行=if(Row(4:4)>3就成立了，返回""——空文本。

3、楼上的=IF(Column(A:A)>Counta……也属于这样的句型，公式横向拖动复制，第1列时Column(A:A)、第2列时变Column(B:B)……

4、楼上的公式太繁杂了些，很多可以简化。

比如OFFSET($A$5:$A$11,,MIN(ROW(1:1)))——此处，第一行公式用Offset将A5:A11单元格偏移1列，第2行偏移2列，第3行偏移3列。

那么为何Offset第3参数要用MIN(Row(1:1))而不是直接用Row(1:1)——因为Row(1:1)得到的是{1}，单元素数组，而不是单值，具有方向。套用MIN得到单值就没有方向了。事实上，这个公式并不能根据A16的姓名来选择偏移多少列，因为他与A16完全没有关系了。可以用OFFSET($A$5:$A$11,Match($A16,$B$4:$D$4,0))代替。

再者，此处用COUNTA来统计打钩也是不合适的，而应该用Countif(Offset,"√")否则，打叉也会算，只要不是空都会算。

Small(IF(Offset<>"",Row(……)),column(A:A))——如果Offset得到的区域不为空，则返回其对应的行号，这样得到所有不为空的行号，再用Small+Column(A:A)按从小到大一个个排出来。

INDEX($A$5:$A$11,Small)——用Small部分得到的满足条件并排序后的行号来从A5:A11中定位。

陌路流年

帖子地址：http://club.excelhome.net/viewthread.php?tid=268927&px=0

公式：=INDEX(A:A,SMALL(IF(COUNTIF($B$1:$B$10,$A$1:$A$20)=0,ROW($A$1:$A$20),10000),ROW(A1)))&""

这个不明白呢？

请那位给详解一下

谢谢～～

gouweicao78

QUOTE:

以下是引用陌路流年在2007-9-28 9:27:07的发言：

帖子地址：http://club.excelhome.net/viewthread.php?tid=268927&px=0

公式：=INDEX(A:A,SMALL(IF(COUNTIF($B$1:$B$10,$A$1:$A$20)=0,ROW($A$1:$A$20),10000),ROW(A1)))&""

这个不明白呢？

请那位给详解一下

谢谢～～

看看本帖前面的解释：

如果：COUNTIF($B$1:$B$10,$A$1:$A$20)=0——B列中没找到A列的值，则返回Row($A$1:$A$20)——A列区域的行号，如果B列找得到就返回10000

然后对此用Small+Row(A1)从小到大排序，Index(A:A，符合要求的行号)，——返回对应符合要求的记录，没有符合条件则返回Index(A:A,10000)&""，——A10000一般是空单元格，空单元格引用与空文本合并，返回空文本。

wang5526923

=ROUND(EVALUATE(SUBSTITUTE(SUBSTITUTE(Sheet1!$D21,"[","*ISTEXT(""["),"]","]"")")),2)

老大,帮忙解释一下该公式好吗,我不太懂希望可以说的详细一点!谢谢了

QUOTE:

已经在这里做了解释：

http://club.excelhome.net/viewthread.php?tid=269769&px=0

                                                          ——CZZQB

QUOTE:

也可参考本帖46楼得到含有备注的算式的计算结果

                                                         ——gouweicao78

[此贴子已经被gouweicao78于2007-10-10 1:25:41编辑过]

水平一般

请问这个9.99999999E+307是什么意思

IF(H4<>"",LOOKUP(9.99999999E+307,$G$1:G3)+1,"")

 

lqnqmWUW.rar (2.13 KB, 下载次数: 32)

2007-10-20 13:55 上传

点击文件名下载附件
[答疑解惑] 函数公式解释专用帖（20061125更新首页）

shun2233

IF(H4<>"",LOOKUP(9.99999999E+307,$G$1:G3)+1,"")

1、9.99999999E307是指9.99999999×10的307次方，

2、如果H4不为空，就在G列中寻找最接近9.99999999×10的307次方的数并加1

QUOTE:

gouweicao78：谢谢热心会员shun2233解答，其中第2点表述错误。更正如下：

Lookup(9.99999999E+307,$G$1:G3)——求$G$1:G3中最后一个数值。相关解释请参考本帖首页山菊花版主的链接、以及本帖“再说二分法”等帖子。

[此贴子已经被gouweicao78于2007-10-20 20:24:42编辑过]

zjqzxumin

请教群里的高手，我对于公式=REPLACE(REPLACE(A19,2*LEN(A19)-LENB(A19)+2,0,"-"),2*LEN(A19)-LENB(A19)+1,0," ")，中的2*LEN(A19)-LENB(A19)+2和2*LEN(A19)-LENB(A19)+1的意思我理解不了，请指教！ 

QUOTE:

gouweicao78：

1、请参阅本帖首页，给出具体问题附件或者链接。

2、公式中2*Len(A19)-LENB(A19)得到的是双字节字符的个数，请查阅Len与LenB函数的帮助文件，主要是用于区别双字节字符（比如说汉字）。仅从这样的数学运算来解释相信你也不会满意，参考第1点。

VQM69Ttx.rar (7.03 KB, 下载次数: 52)

2007-10-22 19:11 上传

点击文件名下载附件

谢谢gouweicao78版主精采的分析，现在我知道这个公式的意思了，谢谢！！

[此贴子已经被作者于2007-10-23 16:41:52编辑过]

gouweicao78

to zjqzxumin：

以B2公式为例：=REPLACE(REPLACE(A2,2*LEN(A2)+2-LENB(A2),0,"-"),2*LEN(A2)-LENB(A2)+1,0," ")

其中：第一参数为REPLACE(A2,2*LEN(A2)+2-LENB(A2),0,"-")

2*LEN(A2)+2-LENB(A2)——因为A2="782白月"，所以Len(A2)=5即5个字符，LenB(A2)=7即3个数字是单字节算3个，2个汉字是双字节算2*2=4个字符。

2*Len(A2)——就是把A2的字符全换算成双字节的话得到10个字符，但实际上A2并非全部双字节

2*Len(A2)-LenB(A2)=10-7=3——也就是A2的单字节字符的个数，即"782"

因此：REPLACE(A2,2*LEN(A2)+2-LENB(A2),0,"-")的含义就是：

在A2的字符中，将从第3+2=5个字开始共0个字符，替换为"-"。也就是在A2的第5个字符“月”前插入"-"，得到"782白-月"

同理对"782白-月"的第4个字符前插入空格。

zjk_999

QUOTE:

以下是引用星语心愿在2006-9-5 18:58:40的发言：

关于根据身份证号码取出生年月的公式我懂了，谢谢版主的耐心指导。再有个问题呀。同样是这个文件里面的：

=IF(LEN(A2)=15,REPLACE(A2,7,,19)&MID("10X98765432",MOD(SUM(MID(REPLACE(A2,7,,19),ROW(INDIRECT("1:17")),1)*2^(18-ROW(INDIRECT("1:17")))),11)+1,1),A2)

这个是把原来的身份证号码全部改为18位的公式。能抽空指点一下吗？谢谢啦？

ROW(INDIRECT("1:17"))部分最看不懂，琢磨了好久，ROW(INDIRECT("1:17"))应该等价ROW(1:17)，这样做应该是为了公式拖动时固定1:17不变吧。

gouweicao78

QUOTE:

以下是引用zjk_999在2007-10-24 21:28:16的发言：

ROW(INDIRECT("1:17"))部分最看不懂，琢磨了好久，ROW(INDIRECT("1:17"))应该等价ROW(1:17)，这样做应该是为了公式拖动时固定1:17不变吧。

理解没错。Row(1:17)是一个会随公式拖动改变的变量，而且还会因为删除了其引用的第1~17行而导致公式变化。

因INDIRECT("1:17")中，"1:17"是以文本常量形式存在，不随拖动和删除单元格变化。