SUMPRODUCT函数里如何嵌套内存数组

干勇平

各位，如附件表中，如何在不用辅助列的情况下，给出黄色区域的公式。

目前是自己使用了辅助列，逐个录入SUMPRODUCT函数，觉得比较差。
求助，用别的函数达到结果也是可以的。小红花酬谢。

高个子

1. =SUM(L10:L13*(MMULT(--$K10:L13,ROW(INDIRECT("1:"&COLUMNS($K:L)))^0)/$D$10:$D$13>0)*(MMULT(--$K10:L13,ROW(INDIRECT("1:"&COLUMNS($K:L)))^0)/$D$10:$D$13<=$F$10:$F$13))

复制代码

1. =SUM(L10:L13*(MMULT(--$K10:L13,ROW(INDIRECT("1:"&COLUMNS($K:L)))^0)/$D$10:$D$13>$F$10:$F$13)*(MMULT(--$K10:L13,ROW(INDIRECT("1:"&COLUMNS($K:L)))^0)/$D$10:$D$13<=$F$10:$F$13+$G$10:$G$13))

复制代码

1. =SUM(L10:L13*(MMULT(--$K10:L13,ROW(INDIRECT("1:"&COLUMNS($K:L)))^0)/$D$10:$D$13>$F$10:$F$13+$G$10:$G$13)*(MMULT(--$K10:L13,ROW(INDIRECT("1:"&COLUMNS($K:L)))^0)/$D$10:$D$13<=1))

复制代码

三键结束，右拉

干勇平

高个子 发表于 2024-6-10 10:06
三键结束，右拉

谢谢高手先，我试试，这么复杂的公式，应该全对，哈哈

干勇平

高个子 发表于 2024-6-10 10:06
三键结束，右拉

大侠，再追问一下。因为合同数量一直在增在，当增加行时，因为还没填合同额，EXCEL就当作0了，公式里除数不能为0，就报错了。怎么样改下公式，将为0的跳过或屏蔽之。感谢

高个子

干勇平 发表于 2024-6-10 12:14
大侠，再追问一下。因为合同数量一直在增在，当增加行时，因为还没填合同额，EXCEL就当作0了，公式里除数 ...

举个例子。

干勇平

高个子 发表于 2024-6-10 15:58
举个例子。

比如在第12行插入一行，结果那黄色区域都变成#VALUE了。
在你帮忙写的公式MMULT前面套了一个IFERROR，就调好了。感谢

jivy1212

1. =SUMPRODUCT((1-ISERR(VDB(,,$F$10:$F$13,1%%%,SUBTOTAL(9,OFFSET($K10,{0;1;2;3},,,COLUMN(B1)))/$D$10:$D$13)))*L$10:L$13)

复制代码

1. =SUMPRODUCT((1-ISERR(VDB(,,$F$10:$F$13+$G$10:$G$13,$F$10:$F$13+1%%%,SUBTOTAL(9,OFFSET($K10,{0;1;2;3},,,COLUMN(B1)))/$D$10:$D$13)))*L10:L13)

复制代码

1. =SUMPRODUCT((1-ISERR(VDB(,,1,$F$10:$F$13+$G$10:$G$13+1%%%,SUBTOTAL(9,OFFSET($K10,{0;1;2;3},,,COLUMN(B1)))/$D$10:$D$13)))*L10:L13)

复制代码

规则有点坑，不然三个公式可以合并的

干勇平

jivy1212 发表于 2024-6-11 09:24
规则有点坑，不然三个公式可以合并的

谢谢哈，这个也还很景，设计的很巧。百度了一下VDB函数，认识了一下。

干勇平

jivy1212 发表于 2024-6-11 09:24
规则有点坑，不然三个公式可以合并的

还想问一下，函数中使用了{0; 1;2; 3}，示例中项目是4个项目，假设中间插入2行变成6个项目结果不对，在最后插入2行结果是对的。
试了下，收款记录是1-3月，随着时间流逝，变成1-5月，公式右拖结果也是对的。

干勇平

jivy1212 发表于 2024-6-11 09:24
规则有点坑，不然三个公式可以合并的

那个VDB中叁数1%%%是啥意思啊，没悟出来