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

同在星空下

示例16-7 利用LOOKUP函数无序查询，详见作了一点修改的附件。记得看到过您说LOOKUP函数能完成的查找任务比VLOOKUP函数和HLOOKUP函数都多。对于此句并不是非常了解了原理，接触函数并不是很久。这几天我也看过了有关LOOKUP函数的通用公式即LOOKUP(查找值，查找范围，[目标范围]）其中带有中括号的参数为可选的，因有两种语法形式：向量和数组。
        本人不善表述，为了下面说明清楚，我已经对E2：E9和F2：F9分别做了定义名称，附件中有说明。可能让您见笑了，因确实对公式不太了解，大全中这样可以增强公式的可读性，便于理解。尤其是对于像我这样的人来说就显得有必要这样。附件中B8单元格中的公式=LOOKUP(1,0/(姓名列=B5),部门列)。首先我对比了一下VLOOKUP中的查找值，它必须是数据表中的具体的可见的匹配的一个值，但为什么LOOKUP函数中的查找值在附件当中却是1，是一个自然数而不是姓名列（E1：E9)中具体的某个人的姓名。这是不明白的地方之一。其二是：查找范围为何写成“0/（姓名列＝B5)”这个样子！其中的0是从哪里冒出来的？有劳您具体解释一下其具体的含义及作用。其三是：“E2：E9=B5比较结果为{FALSE；FALSE；FALSE；FALSE；FALSE；FALSE；TRUE；FALSE}，再利用0除以这个内存数组，结果为{#DIV/0!；#DIV/0!；#DIV/0!；#DIV/0!；#DIV/0!；#DIV/0!；0；#DIV/0!} “引号内也应用大全当中的。
        先说一下我对数组了解，这两个都是内存数组，一维的即5行1列的数组，仅此而已，至于为何会出现“#DIV/0!”这个，实在不解，不明白其具体原因。
        版主我的问题阐述完毕，说了一堆废话，也不知自己有没有清楚，不过这个问题对我很重要！不是我没有事先在论坛搜索一下相关的函数，有些看不懂！还是拿自己稍了解的事例比较好一点！
        期待您的解答！Thank  you  very  much!祝您天天健康快乐！

[ 本帖最后由 同在星空下 于 2009-8-23 22:19 编辑 ]

gouweicao78

关于LOOKUP函数，本题134楼前后有比较多的解释，LOOKUP查找策略一帖更是非常精彩地展示过（曾被评为2个精华）。
首先，LOOKUP函数本身要求第2参数（或数组语法中的第2参数的首行或首列）升序排列，并且当找不到第1参数时，将返回一个与之匹配的小于等于第1参数的最大值。
0/(条件)——条件返回TRUE、FALSE，代入四则运算时，TRUE=1，FALSE=0
因此0/(条件)——构建了一个0、#DIV/0!的数组。在这个数组中，最大的值是0，要查找1，则将返回一个与1匹配的小于等于1的最大值，也就是把第2参数数组找出来不断地与1比较，返回最后一个0的位置——>也就是最后一个TRUE的位置→即：最后一个满足条件的记录。

mrfox

今天研读了一天了，可是还没有结果——可见我有多笨
只好求救了http://club.excelhome.net/thread-476358-3-1.html的22楼， liuzj2s给出了公式，可我就是看不懂啊

sss444

各位教师，我很菜，请指教一下这个公式的意思和SUMPRODUCT函数的用法。
这个公式是用来查找重复单元格的。=IF(SUMPRODUCT(N($B$1:$B$100=B2))>1,"重复","不重复")
谢谢了！

gouweicao78

1、B1:B100=B2是判断B1:B100单元格中是否等于B2，得到100行的TRUE或FALSE组成的数据。
2、N（）将TRUE、FALSE转换为1、0
3、SUMPRODUCT将N（）转换的数值求和。
如果和大于1，也就是B1:B100包含不只1个B2的值，就表示重复。

可以用普通公式：
=IF(COUNTIF($B$1:$B$100,B2)>1,"重复","不重复")

mrfox

版主怎么不理我在403楼提的问题呢，能否解释一下这个公式是如何起作用的，=MAX(IF(NOT(SUMPRODUCT(-(A1:A$2=A2),-(B1:B$2>0)))+(COUNTIF(A$2:A$33,A2)=COUNTIF(A2:A$33,A2)),MIN(B$2:B$33)),B2-SUMPRODUCT(-(A3:A$33=A2),B3:B$33-D3:D$33))

SUMPRODUCT(-(A3:A$33=A2),B3:B$33-D3:D$33)，另外我在http://club.excelhome.net/thread-476358-4-1.html写的流程图能不能转化为公式？

gouweicao78

原帖由 mrfox 于 2009-9-3 11:26 发表
版主怎么不理我在403楼提的问题呢，能否解释一下这个公式是如何起作用的，=MAX(IF(NOT(SUMPRODUCT(-(A1:A$2=A2),-(B1:B$2>0)))+(COUNTIF(A$2:A$33,A2)=COUNTIF(A2:A$33,A2)),MIN(B$2:B$33)),B2-SUMPRODUCT(-(A3:A$33 ...

这个问题我还没看明白你那一帖提问的“规则、要求”是什么，建议让给你答案的人解释，他能理解你的问题。

mrfox

我的规则简单讲一下，希望版主能帮到我。

实际上这个问题对应的是一个退货冲减的问题，就是求真正的当期发货。比如一种货品有六笔交易记录，第一次发3，第二次发2，第三次退1，第四发4，第五次退2，第六次退1个。即交易记录是3，2，-1，4，-2，-1（即B2-B7）。现在求实际的当期发货数量（我省略了时间列，但实际上是有顺序的）。设A列为品种，B列为交易数量，C列为要求的当期发货。那么，要从最后一行开始看（退货要向上冲减之前的发货）。最后一笔是-1，则C7=0（第一行是标题）；倒数第二笔是-2，故C6=0；倒数第三笔是4，是正数，因此可冲减，后面要冲-2+-1=-3，因此C5=1；倒数第四笔是-1，是负数，故C4=0；倒数第五笔是2，要冲后面的退货-1，故C3=1；倒数第六笔是3，因为后面已经是正的没有可冲的，故C2=1。

我想交易记录会分三个大类，一是全正，一是全负，一是正负交叉。全正和全负的时候C列都是B列的原值。正负交叉地复杂些，负数就要向上冲，如果其上面一笔还是负数，那就累计后向上冲，如果上面的正数够冲，就用正数+负数，如果不够，就把正数冲成0后继续将没冲完的负数向上冲，如果上面还是负数而且前面也全是负数或是已经是第一笔，就在此笔累计这个负数，即当时的发货是一个负数（这是因为其实是前面肯定有发货，只是系统没记录；全负是一种特殊情况，就不再向上累计了）。我在原帖38楼对各种情况用表格进行了总结，同时在39楼画了流程图（已经更新在43楼），我花了很多时间来分析liuzj2s给的公式（有一夜就没睡），一是我的函数理解不到位（这两天正在恶补），二是他讲了个思路，但是我搞不明白他是如何做到一眼看出来，然后再化为公式的。UPDATE：早止liuzj2s大侠又更新了公式：
=MAX(IF(SUMPRODUCT(-(A1:A$1=A2),-(B1:B$1>0)),,MIN(B$2:B$33)),B2-SUMPRODUCT(-(A3:A$33=A2),B3:B$33-D3:D$33))
看公式是对IF(SUMPRODUCT(-(A1:A$1=A2),-(B1:B$1>0)),,MIN(B$2:B$33))和B2-SUMPRODUCT(-(A3:A$33=A2),B3:B$33-D3:D$33)这两个算式的结果取最大值，请版主给讲讲公式的运算过程，特别是第一个IF的条件和结果是什么？

如果我没写清楚，版主看不懂规则的话，也可以帮我细致讲讲这里包含的几个函数分别是如何计算的，我再自己拼凑下。

BTW：其实我觉得我最大的问题是不能按照要求分析出合适的解决办法，所以我花了很长时间来分析结果做流程图，我想我现在的分解是正确的，但是即使这样我也写不出公式了，我希望一是能弄明白他的公式的原理（他在贴子里讲了，但我没懂，因为函数和情况分解都有些复杂），另一方面我想按自己的分解写出公式，这样才能真正学会，下次才不会来麻烦版主，谢谢你有耐心看完。

[ 本帖最后由 mrfox 于 2009-9-4 11:58 编辑 ]

jeffay001

不好意思，刚才还不知道有专门的“函数解释专用贴”，所以在“函数与公式”区发了一个重复的贴。还望各位高手谁能“详细解释”以下几个数组公式？？？
1.B1=TEXT(SUM(LARGE(--LEFT(MID(A1,ROW($1:$15),1)&0),ROW($1:$15))*10^(ROW($1:$15)-1)),REPT(0,LEN(A1)))
数组公式，先不要回车，按Ctrl+Shift+Enter结束计算。
注：请重点解释&0

2.B1=RIGHT(10^15&SUM(LARGE(--(0&MID(A1,ROW($1:$15),1)),ROW($1:$15))*10^ROW($2:$16))%,LEN(A1))
也是数组公式
注：请重点解释10^15&和%

3.在B1输入公式：
=IF(ISNUMBER(FIND(0,A1)),0,"")&SUBSTITUTE(SUM(LARGE(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),ROW(INDIRECT("1:"&LEN(A1))))*10^ROW(INDIRECT("1:"&LEN(A1)))),0,"")
数组公式,公式在编辑状态下按Ctrl+Shift+Enter结束输入。

越详细越好。谢谢！

gouweicao78

1、MID(A1,ROW(1:15),1)——分别取出A1单元格的第1~第15位字符，很多情况下，A1并没有15位，比如A1是1234，则第5个字符开始都是空文本""，因而--转换之后就会出现#VALUE！错误，如果用了&0则可以避免错误，比如第1位LEFT(MID(A1,1,1)&0)就是LEFT("1"&0)→LEFT("10")即"1"，因此--LEFT得到1，不会改变MID取出的数字；而到了第5位，就是LEFT(""&0)→得到"0"，因此--LEFT得到0，不会出错。

这样，就是将A1的字符用后面用0补足15位，然后分别取出后用LARGE从大到小排序，再通过*10^(ROW(1:15)-1)也就是分别乘以10^0、10^1……10^14（也就是大的数字乘以小的倍数，数字越大越靠近个位）再SUM求和，将排序后的数字填写到对应位置。
最后再通过TEXT+REPT(0,LEN(A1))返回与A1数字相同个数的文本数字。

2、（1）同理，0&MID也是避免取到""出错。比如，MID取到的"1"，则变为--"01"还是1，不会改变原来的值。
（2）*10^ROW(2:16)——从10^2（即100）开始乘，所以得到的SUM会扩大100倍，因而再用%缩小100倍可以还原。
（3）10^15——先给一个1000000000000000再合并SUM%得到的排序后的值，如果A1的数字包含3个0，比如305020，则SUM%得到235，需要在前面补3个0用了合并之后，显然RIGHT(……,LEN(A1))不会取到最左边的1，而又预留了足够的0让SUM%来补。

3、这个公式，就是比较传统的利用ISNUMBER+FIND判断A1是否有0，但没有考虑A1中0超过1个的情况，因此效果与上两个公式不同。
此外，这个公式利用ROW(INDIRECT("1:"&LEN(A1)))——直接用A1的字符数限定MID取数的次数，而不是预先按15位数字处理，而SUM得到的多余的0，用SUBTITUTE替换掉了。其他都方便理解。