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

hw.73

这是一个定义名称公式，在定义名称单元格内输入带有注释的计算式，可有另一个单元格内得出结果，如在C3中输入“3.2*4.5[卧室]+4.2*5[客厅]”，D3中就会显示计算结果35.4。但如果输入“[5#房]3.2*4.5[卧室]+4.2*5[客厅]”，就不能得出结果。这是为什么呢？

C列定义名称：计算式1   引用位置输入：=EVALUATE(SUBSTITUTE(SUBSTITUTE($C:$C,"[","*ISTEXT(""["),"]","]"")")

D3中输入：=IF(ISERROR(计算式1),"", 计算式1)

请帮忙解答一下，谢谢！

QUOTE:

gouweicao78答复：请认真查阅本帖首页帖规、导航以及第46楼的解答。这个公式已经解释过了。

[此贴子已经被gouweicao78于2008-10-13 8:06:04编辑过]

happySCH

=IF(ROW(A1)>COUNTA(A$1:A$20),"",INDEX($A$1:$A$20,LARGE(($A$1:$A$20<>"")*ROW($A$1:$A$20),ROW(A1)),))
此公式在动态引用其它工作表非空单元格时，为什么会出现错误信息？

[ 本帖最后由 happySCH 于 2008-10-27 14:26 编辑 ]

happySCH

使用match函数查找时，最后一个参数用绝对匹配为什么也会返回错误信息？

[ 本帖最后由 happySCH 于 2008-10-27 14:57 编辑 ]

gouweicao78

原帖由 happySCH 于 2008-10-27 14:24 发表
=IF(ROW(A1)>COUNTA(A$1:A$20),"",INDEX($A$1:$A$20,LARGE(($A$1:$A$20"")*ROW($A$1:$A$20),ROW(A1)),))
此公式在动态引用其它工作表非空单元格时，为什么会出现错误信息？

请参阅本帖首页，关于Row，你甚至不知到公式的哪一段是什么，该改什么：
1、ROW(A1)或者ROW(1:1)用来向下拖动公式时产生1、2、3……的等差序列，而你的公式却从ROW('08年'!D3)开始，请改为ROW(1:1)或ROW(A1)，这里也跟工作表无关，无需用08年表的单元格。
2、INDEX('08年'!$D$3:$D$40……那么要获得D3的值，INDEX的第2参数必须是1而不是3，如果是：
INDEX('08年'!$D:$D，……则要获得D3的值，INDEX的第2参数必须是3而不是1，公式怎么改，要与用法配套。
关于条件筛选的数组公式（此处筛选条件是不为空），论坛上有很多，本帖前几页也有很多类似的，你可以参考。比如数组公式：=INDEX('08年'!D:D,SMALL(IF('08年'!D$3:D$40<>"",ROW($3:$40),65536),ROW(1:1)))&""

原帖由 happySCH 于 2008-10-27 14:55 发表
使用match函数查找时，最后一个参数用绝对匹配为什么也会返回错误信息？

MATCH函数支持通配符，比如MATCH("1*2W",A1:A10,0)实际上1*2W的*号被识别为通配符，代表以1开头且以2W结尾的任意字符，比如1/2*2W、1 1/4*2W、或者1abcd你好2W等，都可以与之匹配，看先找到谁就是谁了。

[ 本帖最后由 gouweicao78 于 2008-10-27 22:28 编辑 ]

glhdex

1、=IF(OR(A1=A2,A2=A3),1,0)

2、=ASC(J2)

3、=WIDECHAR(J2)

4、=IF(ISERROR(FIND("办事处",J2))=TRUE,"",MID(J2,1,FIND("办事处",J2)+2))&IF(ISERROR(FIND("镇",J2))=TRUE,"",MID(J2,1,FIND("镇",J2)))&IF(ISERROR(FIND("镇",J2))=TRUE,IF(ISERROR(FIND("乡",J2))=TRUE,"",MID(J2,1,FIND("乡",J2))),"")

5、=ASC(IF(K2="",J2,IF(ISERROR(FIND("办事处",J2))=TRUE,"",MID(J2,FIND("办事处",J2)+3,20))&IF(ISERROR(FIND("镇",J2))=TRUE,"",MID(J2,FIND("镇",J2)+1,20))&IF(ISERROR(FIND("镇",J2))=TRUE,IF(ISERROR(FIND("乡",J2))=TRUE,"",MID(J2,FIND("乡",J2)+1,20)),"")))

6、=IF(ISERROR(VLOOKUP(K9,cc,2,FALSE))=TRUE,"421124000000",""&VLOOKUP(K9,cc,2,FALSE))

7、=IF(ISERROR(FIND("社区",L2))=TRUE,"",MID(L2,1,FIND("社区",L2)+1))&IF(ISERROR(FIND("村",L2))=TRUE,"",MID(L2,1,FIND("村",L2))&"委会")

8、=IF(N2="",M2,IF(ISERROR(VLOOKUP(N2,cc,2,FALSE))=TRUE,M2,VLOOKUP——公式不全

9、=REPT("0",9-LEN(B3))&B3(N2,cc,2,FALSE)))&""——这么看，B3是一个自定义函数，肯定你公式不全

gouweicao78答复：请遵守【答疑解惑】帖规，需解释公式请传附件或给链接。另，能够自己查函数帮助的比如=ASC(J2)、=WIDECHAR(J2)等，你查一下函数帮助不就知道了吗？

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

hczpost

在：http://club.excelhome.net/thread-345294-1-1.html　中二楼的数组公式：
I2=SUM(--RIGHT(D2:H2,LEN(D2:H2)*2-LENB(D2:H2)))
，为什么要在RIGHT前面用－－？


gouweicao78答复：请看本帖首页，这是将文本型数字转化为数值型数字的一种方法。

[ 本帖最后由 gouweicao78 于 2008-11-2 11:13 编辑 ]

vxsq

请问在http://club.excelhome.net/viewth ... page%3D1#pid2336198中4楼的函数LARGE(IF(MMULT(--(SUMIF(OFFSET(A2:F2,ROW(1:10)-1,),{6,9})={6,9}),{1;1})=2,ROW(1:10)),{1,2})+1中的{6,9}能不能换成单元格引用

原帖由 vxsq 于 2008-11-2 15:31 发表
我采用的6和9所在的单元格是在同一行，但是并不是相邻的，所以出错了

gouweicao78答复：您已自己找到答案了。

[ 本帖最后由 gouweicao78 于 2008-11-3 11:12 编辑 ]

glhdex

请老师解答

happySCH

将文本转为数值，数组公式：=COUNTIF(名称,"<"&名称)*10^6+ROW(名称)，不明白怎么得到{2;1000003;6000004;2000005;4000006;3000007;7000008;5000009}这些数值。
对名称进行升序排列：=IF(ROW(1:1)>COUNT(x),"",INDEX(名称,RIGHT(SMALL(x,ROW(1:1)),5)-1))，用RIGHT取最后面5个字符与名称中包含的字符数有什么关系，为什么不可以省略，不可以直接返回第K个最小值？能否帮忙详细解释这两个公式。

gouweicao78答复：
请参考本帖28楼——数值按多个关键字排序
COUNTIF(名称,"<"&名称)，利用COUNTIF函数对“文本”进行比较，也就是排序功能。假设A1:A3共c、a、b这3个文本的排序应该是：3、1、2，那么这3个文本中小于自己的分别就是2、0、1，将这个数放大10^6倍，然后加上不足以影响大小顺序的行号1、2、3分别得到200001、000002、100003，用Small排序得到000002、100003、200001，取后5位数也就是按文本排序后的行号2、3、1，这样再INDEX取出来的就是：第2行的a、第3行的b、第1行的c。
为什么是5位数：Excel2003共有65536行，即5位数。

[ 本帖最后由 gouweicao78 于 2008-11-25 11:52 编辑 ]

ho_ky

=INDEX($A$3:$A$72,SMALL(IF(B$3:B$72=L4,ROW(B$3:B$72)-2),COUNTIF(L$4:L4,L4)))
此公式何解？http://club.excelhome.net/thread-375980-1-1.html

gouweicao78答复：此类公式解释在本帖前面已有较多。请翻阅一下。
IF(B$3:B$72=L4,ROW(B$3:B$72)-2即：如果B3:B72的成绩=L4，那么返回其行号并减去2——这与INDEX第1参数有关，可以优化为=INDEX($A:$A,SMALL(IF(B$3:B$72=L4,ROW(B$3:B$72)),COUNTIF(L$4:L4,L4)))
然后对返回的行号用SMALL(返回的行号,countif)排序。公式关键所在是用COUNTIF(L$4:L4,L4)来作为SMALL函数的第2参数以解决同分数不同人的问题。假设某个分数没有重复，那么countif返回1，SMALL(……,1)也只返回那个唯一的行号，如果分数第2次出现，countif返回2，则SMALL返回第2次出现的行号。

[ 本帖最后由 gouweicao78 于 2008-12-3 09:48 编辑 ]