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

ggsmart · 发表于 2009-4-30 11:11

原帖由 小钱不争 于 2009-4-30 09:41 发表
=INDEX('2'!O:O,SMALL(IF('2'!$D$4:$D$20=17,ROW('2'!$D$4:$D$20),""),ROW(J2)))

请问此公示如何解释，而且如果当引用的工作表（如表2）名是可变的，这里的17也要是可变的话，应该如何修改

这是把表“2”中D列等于17相对应的O列的数据提到到新区域的数组公式。建议公式改成：
=INDEX('2'!O:O,SMALL(IF('2'!$D$4:$D$20=17,ROW('2'!$D$4:$D$20),4^8),ROW(A1)))&""
公式的思思路一样，只是作了错误处理。

******************************************************************************************************************************************

解读公式，我喜欢从最里层开始看起：
=INDEX('2'!O:O,SMALL(IF('2'!$D$4:$D$20=17,ROW('2'!$D$4:$D$20),""),ROW(J2)))

IF('2'!$D$4:$D$20=17,ROW('2'!$D$4:$D$20),"")：一个很简单的IF函数，很容易读懂，当表“2”的$D$4:$D$20＝17时，返回所在的行号，否则返回空。
抹黑，按F9,可以看到IF生成了一个数组，得到一个结果（这是我个人举的例）：
=INDEX('2'!O:O,SMALL({4;"";"";7;8;"";10;"";12;"";14;"";"";17;"";"";""},ROW(J2)))。

好了，现在该small出场了：
=INDEX('2'!O:O,SMALL({4;"";"";7;8;"";10;"";12;"";14;"";"";17;"";"";""},ROW(J2)) )
这很容易看懂，用row()函数的结果作为small函数的第二参数，当公式下拉时会承着下拉改变，于是就返回IF公式生成的数组结果的第一小值，第二小值，第三小值....也就是表2里D列＝17,满足条件的记录所在的行号。），这个结果作为index的第二参数。

最后，用index函数返回表2里D列＝17的相应的O列的值，这个不用讲了，直接看index的函数帮助就OK了。

****************************************************************************************************************************

至于工作表名和条件可变的问题，建议把工作表名称和条件放在辅助单元格，在公式里设置成对单元格的引用。
=INDEX(indirect(工作表在的单元格&"!O:O"),SMALL(IF('2'!$D$4:$D$20=条件单元格,ROW('2'!$D$4:$D$20),""),ROW(J2)))

表名称如果放在B1单元格，条件放在C1单元格，则公式为：
=INDEX(INDIRECT($B$1&"!O:O"),SMALL(IF('2'!$D$4:$D$20=$C$1,ROW('2'!$D$4:$D$20),""),ROW(J2)))
****************************************************************************************************************************

对于你在楼下提出的问题，我已对你的附件公式作了修改：
C4等于数组公式：=INDEX(INDIRECT($I$1&"!C"&COLUMN(),),SMALL(IF(INDIRECT($I$1& "!D4:D20")=$J$1,ROW($D$4:$D$20),4^8),ROW(A1)))&""右拉下拉。

附件：

sample.rar (15.59 KB, 下载次数: 56)

[ 本帖最后由 ggsmart 于 2009-5-4 10:26 编辑 ]

kolama · 发表于 2009-5-3 21:49

DX帮忙解释下这个公式

=VLOOKUP($A2&$B2&$C2&$D2&$E2,IF({1,0},Sheet3!$B$2:$B$41&Sheet3!$C$2:$C$41&Sheet3!$D$2:$D$41&Sheet3!$E$2:$E$41&Sheet3!$F$2:$F$41,Sheet3!G$2:G$41),2,)

谢谢

gouweicao78答复：请遵守【答疑解惑】帖规，见本帖首页。

[ 本帖最后由 gouweicao78 于 2009-5-3 23:46 编辑 ]

小钱不争 · 发表于 2009-5-11 09:49

=INDEX(INDIRECT($I$1&"!C"&COLUMN(),),SMALL(IF(INDIRECT($I$1& "!D4:D20")=$J$1,ROW($D$4:$D$20),4^8),ROW(A1)))&""

请问万能的楼主，一般用INDEX对于空白单元格的引用往往会返回零，这句公示里哪个部分有消除0的功效？

另外此句中IF(INDIRECT($I$1& "!D4:D20")=$J$1,ROW($D$4:$D$20),4^8
这句为什么是要当不满足条件时返回4的八次方，这么大的一个数字。而且为什么单元格里还不显示

还有还有最后的ROW(A1),其作用我以为就是返回12345之类的连续数字，为什么不能直接用12345。。。取代。
经过试验，发现改成数字后不但无法引用而且拉动格子也不会变化

谢谢指教

gouweicao78答复：本帖第26楼“关于&""”已写，其中：4^8就是65536，也就是Excel2003到最后一行。
http://club.excelhome.net/viewthread.php?tid=161588&page=2#pid1094349

[ 本帖最后由 gouweicao78 于 2009-5-15 11:38 编辑 ]

tlh0902 · 发表于 2009-5-16 21:12

{=HYPERLINK("#库!b"&MATCH(2,1/(库!B1:B65000<>""))+1,"录入数据")}
请问这个公式中的 <> 在公式里表示什么意思？主要作用是什么？？谢谢斑竹！

gouweicao78答复：MATCH(2,1/(库!B1:B65000<>""))——查找库表中B列最后一个不为空的单元格。
<>是不等于的意思。关于MATCH(2,1/……可参考本帖138楼有关LOOKUP二分法的解释。

[ 本帖最后由 gouweicao78 于 2009-5-17 11:54 编辑 ]

first1Isdaly · 发表于 2009-5-19 12:17

原帖由 yfan17 于 2009-5-19 08:59 发表
难道编号还有重复的？
=LOOKUP(2,1/(人员!A$2:A$1941=$A2)*(人员!B$2:B$1941=$B2),人员!C$2:C$1941)

想请问函数中lookup参数2是什么意思？第一个参数不是应该是一个应该确定的值吗？还有1/是要去掉没有意义的情况吗？如果不用1/会怎么样呢

gouweicao78答复：关于LOOKUP(2,1/(……))本帖第133楼已解释
http://club.excelhome.net/viewthread.php?tid=161588&page=9#pid1333061

[ 本帖最后由 gouweicao78 于 2009-5-20 09:03 编辑 ]

ggsmart · 发表于 2009-5-19 13:28

原帖由 first1Isdaly 于 2009-5-19 12:17 发表

想请问函数中lookup参数2是什么意思？第一个参数不是应该是一个应该确定的值吗？还有1/是要去掉没有意义的情况吗？如果不用1/会怎么样呢

＝＝＝
=LOOKUP(2,1/(人员!A$2:A$1941=$A2)*(人员!B$2:B$1941=$B2),人员!C$2:C$1941)
这是一个多条件查询数据的公式，这里使用的是lookup的向量形式，括号里有三个参数，第一个参数为要查找的值，第二个参数为查找的区域（一行或一列），第三参数为返回的区域（一行或一列，且大小尺寸、位置必须与第二参数相同）， LOOKUP 第二参数表示的区域中查找第一参数的数值，然后返回第三个参数表示的区域中相同位置的数值。
需要补充说明一点的是：lookup是返回的是最后一个小于条件值的值。
＝＝＝
针对你提出的问题，看一下你的第二参数：1/(人员!A$2:A$1941=$A2)*(人员!B$2:B$1941=$B2)红色部分的很容易看懂了，两个条件的匹配，即：当“人员”工作表中的A2：A1941的值等于A2单元格的值且“人员”工作表的B2：b1941的值等于B2单元格的值，相乘后生成类似这样的数组： {1;0;0;0;0;0;0;1;0;1;0;1;0;0;1;0;1;0;1} ，结果为1的则满足同时满足两个条件，为0的，则反之。再用1作为被除数，生成的数组作为除数，相除生成这样的数组： {1;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;1;#DIV/0!;1;#DIV/0!;1;#DIV/0!;#DIV/0!;1;#DIV/0!;1;#DIV/0!;1} ，因为0不能作分母，所以，凡是不能满足两个条件的值都变成错误了。这样，第二参数就成了由满足条件的1和不满足条件的错误组成的一个新数组，第一参数是2,也就是在第二参数里查找比2小的1,当然就是我们所要查的记录了，然后返回第三参数相同位置的数值。

＝＝＝＝
不知道我这样的解释清楚了没！

oookkkooo · 发表于 2009-5-19 17:37

计算结果不同的疑惑.rar (36.69 KB, 下载次数: 36)
1、在“相等”表中，“E25”=“O19”；即把20000发下去，分毫不差。
2、在“不相等1”表中，“E25”≠“O19”；即20000≠20001.58，多出1.58来；
3、在“不相等2”表中，“E25”≠“O19”；即20000≠19977.93，少出22.07来；

[quote]
gouweicao78答复：您这问题不属于【答疑解惑】范畴，本帖是解释公式含义的。而您的问题应该是单位计算工资奖金的规则问题，设置公式的人，首先自己应理顺数据之间的关系。我不知道您这算不算自问自答“2、在“不相等1”表中，“E25”≠“O19”；即20000≠20001.58，多出1.58来；就因乙1工作表现突出，将“D5”增加了100元。请问为什么？”，难道您认为1000*0.1=100，而1100*0.1也应该等于100吗？
[/quote]

[ 本帖最后由 gouweicao78 于 2009-5-20 09:19 编辑 ]

SAMZHOU888 · 发表于 2009-5-19 19:00

=IF(OR(ISNUMBER(FIND("MDF",A25)),ISNUMBER(FIND("PB",A25))),750*D25*E25*F25*H25/1000000000,IF(A25="TUBE",IF(AND(D25="φ",F25="/"),3.14159265*E25^2/4*G25*H25*7.85/1000000,IF(D25="φ",(E25^2-(E25-2*F25)^2)*3.14159265/4*G25*H25*7.85/1000000,((D25*E25-(E25-2*F25)*(D25-2*F25))*G25*H25*7.85/1000000))),IF(A25="SHEET",D25*E25*F25/1000*7.85*H25/1000,IF(A25="GLASS",D25*E25*F25/1000000*2*H25,""))))是什么意思?

gouweicao78答复：求助前，请先阅读一下本帖首页，给出链接或附件，或者浏览本帖对各种解法的解读。

[ 本帖最后由 gouweicao78 于 2009-5-20 09:05 编辑 ]

ggsmart · 发表于 2009-5-19 22:54

原帖由 SAMZHOU888 于 2009-5-19 19:00 发表
=IF(OR(ISNUMBER(FIND("MDF",A25)),ISNUMBER(FIND("PB",A25))),750*D25*E25*F25*H25/1000000000,IF(A25="TUBE",IF(AND(D25="φ",F25="/"),3.14159265*E25^2/4*G25*H25*7.85/1000000,IF(D25="φ",(E25^2-(E25-2*F25)^ ...

建议先查看IF函数的帮助，弄明白IF函数的工作原理后，再层层解读公式。
如何解读公式，请参照本贴3楼版主的回贴，简单易懂，相信会对你有很大的收获！

[ 本帖最后由 ggsmart 于 2009-5-19 22:55 编辑 ]

旺旺0416 · 发表于 2009-5-19 23:58

小弟刚刚学习
INDIRECT("'A"&A6&"'!$C$4")怎么理解
如果A换成B，我的工作也也换成B就出不来了
谢谢
怎么把我抓的图片发上来？

[ 本帖最后由 gouweicao78 于 2009-5-20 08:58 编辑 ]

		自动登录	找回密码
密码			免费注册

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

回复 361楼 ggsmart 的帖子

请版主解惑难题：奇怪！一个某单位工资表出现不同的计算结果

INDIRECT("'A"&A6&"'!$C$4")

最新热点 /1