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

Ericall

前几天看到这么个问题，“从N多银行名称中挑出比如"中行","农业","建设","中国银行","交通","招商","中信","浦发","光大","民生","华夏","广发","发展","兴业","商业";;;;;等大概要区别20多个关键字，需要从一列中挑出符合的银行并标记出来”，有人给了这么个函数=MID("  "&B2,-LOOKUP(,-FIND({"","中行","农业","建设","中国银行","交通","招商","中信","浦发","光大","民生","华夏","广发","发展","兴业","商业"},"  "&B2)),2)，觉得这个函数了不起，但就是看不明白，而且函数前面还可以加负号这是怎么回事，还有"  "&B2，""，加这些是干嘛的希望指点。

gouweicao78

关于LOOKUP的用法，本帖已有阐述（重要点在于查找“最后一个”满足条件记录），请看133楼：http://club.excelhome.net/viewth ... p;page=9#pid1333061

1、FIND部分，在" "&B2中找""、"中行"等字符，数组中第一个是空文本，不管FIND在任何字符串找空文本都会返回1，用以容错（也就是当所有数组中的字符都找不到时，至少还有一个空文本返回的1而不是全部都#VALUE!错误）。
2、FIND返回的是1及以上的数字和#VALUE!错误值组成的数组，-FIND就变成负数和错误值；
3、LOOKUP(,-FIND……第1参数简写后，实际上是LOOKUP(0,-FIND……也就是在负数和错误值数组中找绝对比负数大的0，由此找到“最后一个”负数。然后-LOOKUP将之再变为正数，也就是FIND找到的起始位置；
4、MID从FIND的起始位置开始截取2个字符（如果是FIND找不到别的字符，只能找到空文本""，也就是1，则从第1个字符开始截取2个字，因此前面用"  "&B2，让MID返回2个空格。

鸿泰

版主您好！请您帮我解释一下红色1至11工作表多表按条件计数汇总中两个公式：
｛ =SUM(COUNTIF(INDIRECT(ROW($1:$11)&"!A:A"),A2))｝
｛=SUM(COUNTIF(INDIRECT("'"&ROW($1:$11)&"'!A1:A1000"),汇总!A2))｝
特别是(INDIRECT(ROW($1:$11)&"!A:A")和(INDIRECT("'"&ROW($1:$11)&"'!A1:A1000")是什么意思？它们有何不同？"'"&是什么意思？有何作用？

同时麻烦版主解释下绿色1月至3月工作表多表按条件求和的公式中红色部分的意思？
=SUMPRODUCT(SUMIF(INDIRECT("'"&ROW($1:$3)&"月'!A1:A30"),A3,INDIRECT("'"&ROW($1:$3)&"月'!b1:b30")))

  最后拜托版主一定要帮我的问题就是：当我把工作表名称改为文字时以上三道公式的结果都是：#REF!
不能得出正确的答案。难道上面公式红色部分的引用跟工作表名称的类型（数字、文字）有关吗？
    如果工作表名称是文字时，应怎样设置公式？
如把红色工作表名称1至11改为大写的一至十一；把绿色工作表名称1月至3月改为一月至三月；怎样设置上面公式中红色部分的引用？


     拜托了，在此先说声谢谢！！！

鸿泰

多表按条件求和及计数公式附件上传

gouweicao78

问题1、工作表名称与跨表引用关系
请参阅：[函数用法讨论系列21]关于Indirect函数引用工作表名的书写方式及对定义名称的再引用，链接：http://club.excelhome.net/thread-159686-1-1.html
其中，工作表名称如果包含空格或特殊字符，则跨表引用必须在工作表名前后加上一对半角单引号。或者，可以说正确的跨表引用就是必须加这对单引号，只不过不含那些字符的可以省略。

问题2、函数产生多维引用
ROW($1:$11)产生{1;2;3;4;5;6;7;8;9;10;11}数组，使用数组作为OFFSET或INDIRECT函数的参数，可以产生多维引用，这个“多维”是不同于眼睛看到的行、列的另一个方向，比如：INDIRECT(ROW($1:$11)&"!A1:A100")返回分别对工作表1~11的A1:A100单元格区域的引用，共有11个区域。
当工作表名称改变时，比如工作表1改为“1月”，那么没有“1”这个表之后，仍然刻舟求剑式去引用“1”表，必然出现错误。

yangjp226

您好 附件LOOKUP 公式用了后不知道什么意思 小弟菜鸟 烦请高手帮忙解析一下 谢谢！还望赐教
0/COUNTIF($B3:$BJ3,$BM$13:$BM$17) 什么意思 用0除以这个公式怎么解释

[ 本帖最后由 yangjp226 于 2010-8-6 13:10 编辑 ]

gouweicao78

LOOKUP(1,0/(条件),查找区域)
用法已有解释，就是用0除以条件产生的TRUE、FALSE，使之变成0和#DIV/0!错误数组
然后在这个永远小于1的数组中，查找1，以此得到最后一个0的记录，也就是最后一个TRUE的记录。

COUNTIF作为“条件”，道理也是相同。

liuhyc

解释的太好，越看越晕，所以还是问下：
SUMPRODUCT((Sheet2!$A$2:$A$309=Sheet1!A2)/COUNTIF(Sheet2!$B$2:$B$309,Sheet2!$B$2:$B$309&""))
这里的/具体发挥什么作用。谢谢
原帖：http://club.excelhome.net/thread-610823-1-1.html
谢谢
已经搞明白了

gouweicao78答复：嗯。本帖首页有关于1/COUNTIF计算不重复的解释。

[ 本帖最后由 gouweicao78 于 2010-8-13 13:14 编辑 ]

luckylu123

如附件所示，当更改单元格的数字时，求和公式并不立即执行，只有重新点一次或者保存的时候才执行函数，不知道是为什么。

luckylu123

刚才那个附件传错了，重新上传一个原版的，疑问部分已经用蓝色背景标出。