欢迎转发和点一下“在看”，文末留言互动!
置顶公众号或设为星标及时接收更新不迷路
小伙伴们好，今天来和大家分享一道关于阈值的问题。这个问题相关的题目我们之前也介绍过很多次，LOOKUP函数是解决这类问题的最主要的函数。下面就来看看今天的题目和往期的内容有什么不同吧！
题目是这样的：
这其实就是一个简易的供应链追踪表。题目要求按照物料的订单数量和库存量来确定订单的完成日期。
接下来我们一起来看看可以写出多少种不同思路的公式吧！
01
MMULT函数累计求和
在单元格G2中输入下列公式，三键确认并向下拖曳即可。
=IFNA(LOOKUP(1,0/FREQUENCY(SUMIF(E$2:E2,E2,F$2:F2),MMULT((ROW($1:$9)>=TRANSPOSE(ROW($1:$9)))*1,C$2:C$10*(B$2:B$10=E2))),--$A$2:$A$10),"未完成")这条公式主要利用MMULT函数来做累计求和。
MMULT((ROW($1:$9)>=TRANSPOSE(ROW($1:$9)))*1,C$2:C$10*(B$2:B$10=E2))这里也有一个小技巧。(ROW($1:$9)>=TRANSPOSE(ROW($1:$9))。将它乘以1后返回下面这样一个矩阵。
利用MMULT函数就可以返回按照当前条件累计的库存量。MMULT函数返回的结果如下：
{0;0;0;100;253;453;453;453;453}。
FREQUENCY(SUMIF(E$2:E2,E2,F$2:F2),MMULT((ROW($1:$9)>=TRANSPOSE(ROW($1:$9)))*1,C$2:C$10*(B$2:B$10=E2)))这段公式里，利用SUMIF函数做一个动态的按条件求和。并以SUMIF函数返回的结果作为FREQUENCY函数的第一参数来计频。
LOOKUP(1,0/FREQUENCY(SUMIF(E$2:E2,E2,F$2:F2),MMULT((ROW($1:$9)>=TRANSPOSE(ROW($1:$9)))*1,C$2:C$10*(B$2:B$10=E2))),--$A$2:$A$10)LOOKUP函数的经典应用返回日期。
这里有一点需要特别强调一下：--$A$2:$A$10，我们给单元格区域$A$2:$A$10做了减负运算，让其变成了一个数组，而不是作为区域$A$2:$A$10直接来引用它。
原因是，当作为区域来直接引用作为目标区域时，当查找值比较到查找区域最后一个数据仍未有匹配值时，LOOKUP函数会自动对目标区域进行扩展并返回0值。
转换为数组后就不会有这样的问题。
IFNA(LOOKUP(1,0/FREQUENCY(SUMIF(E$2:E2,E2,F$2:F2),MMULT((ROW($1:$9)>=TRANSPOSE(ROW($1:$9)))*1,C$2:C$10*(B$2:B$10=E2))),--$A$2:$A$10),"未完成")最后IFNA函数将错误值转换为“未完成”。
02
SUMIFS函数累计求和
在单元格G2中输入下列公式，三键确认并向下拖曳即可。
=IFNA(LOOKUP(,0/FREQUENCY(SUMIF(E$1:E2,E2,F:F),SUMIFS(C:C,B:B,IF(E2=B$2:B$10,E2),A:A,"<="&A$2:A$10)),--A$2:A$10),"未完成")这条公式和第一条公式相比只是用SUMIFS函数替换了MMULT函数。其余都是一样的。
这里有一点需要特别强调：
SUMIFS(C:C,B:B,IF(E2=B$2:B$10,E2),A:A,"<="&A$2:A$10)这样写可以不用考虑源数据中数据排列的问题。无论是按一定顺序排列还是乱序排列，都可以得到正确答案。
03
下面这条公式构思新颖，令人耳目一新！
在单元格G2中输入下列公式，三键确认并向下拖曳即可。
=LOOKUP(1,IF({1;0},{0,"未完成"},MIN(IF(SUMIF(E$1:E2,E2,F:F)<=SUMIF(OFFSET(B$1,,,ROW($2:$10)),E2,C:C)*(E2=B$2:B$10),A$2:A$10))^{0,1}))这条公式利用SUMIF函数和OFFSET函数配合来累计求和。
SUMIF(OFFSET(B$1,,,ROW($2:$10)),E2,C:C)*(E2=B$2:B$10)OFFSET函数向下偏移，SUMIF函数按条件求和。再配合另一个条件E2=B$2:B$10，得到当前条件下的累计值。这部分的结果是{0;0;0;100;253;453;0;0;0}。
IF(SUMIF(E$1:E2,E2,F:F)<=SUMIF(OFFSET(B$1,,,ROW($2:$10)),E2,C:C)*(E2=B$2:B$10),A$2:A$10)IF函数条件判断，并返回对应的日期。
MIN(IF(SUMIF(E$1:E2,E2,F:F)<=SUMIF(OFFSET(B$1,,,ROW($2:$10)),E2,C:C)*(E2=B$2:B$10),A$2:A$10))^{0,1}MIN函数取较小的日期，幂运算后得到一个1行2列的内存数组。
当IF函数条件不为真时则全部返回逻辑值“FALSE”，MIN函数取小值得到“0”，幂运算后得到{#NUM!,0}。
IF({1;0},{0,"未完成"},MIN(IF(SUMIF(E$1:E2,E2,F:F)<=SUMIF(OFFSET(B$1,,,ROW($2:$10)),E2,C:C)*(E2=B$2:B$10),A$2:A$10))^{0,1})利用IF函数，将{0,"未完成"}和上一步得到的结果合并为新的内存数组。结果是{0,"未完成";1,45483}，或者是{0,"未完成";#NUM!,0}。
LOOKUP(1,IF({1;0},{0,"未完成"},MIN(IF(SUMIF(E$1:E2,E2,F:F)<=SUMIF(OFFSET(B$1,,,ROW($2:$10)),E2,C:C)*(E2=B$2:B$10),A$2:A$10))^{0,1}))最后LOOKUP函数返回正确日期或者“未完成”。
04
TEXT函数小试身手
在单元格G2中输入下列公式，三键确认后向下拖曳即可。
=TEXT(LOOKUP(,0/FREQUENCY(SUMIF(E$1:E2,E2,F:F),SUMIF(OFFSET(B$1,,,ROW($2:$10)),E2,C:C)),A$2:A$9),"e/m/d;;未完成")这里使用了TEXT函数来返回最后的日期或者文字。
05
主打一个让人想不到
在单元格G2中输入下列公式，三键确认后向下拖曳即可。
=IFERROR(LARGE((((B$2:B$10=E2)*C$2:C$10)>COLUMN(A:QM))*A$2:A$10,SUM((B$2:B$10=E2)*C$2:C$10)-SUM((E$2:E2=E2)*F$2:F2)+1),"未完成")朋友们，这条公式你们能自己拆解吗？有问题记得给我留言哦！
本期内容练习文件提取方式：
链接: https://pan.baidu.com/s/1fclQnnbPlqlVcGMC2_YjIQ?pwd=j9ww 提取码: j9ww
-END-
长按下方二维码关注EXCEL应用之家
面对EXCEL操作问题时不再迷茫无助
我就知道你“在看”
