Excel -动态SUM公式[0,1]

nqwrtyyt  于 2023-06-25  发布在  其他
关注(0)|答案(6)|浏览(128)

我试图创建一个动态Excel公式,当一行中的值为1后跟0时,该公式会对另一行中的值求和。如果在最后一个0之后,有一个1,再次求和,直到您达到另一个1。

如图所示,F6中的第一个值必须是323(202+47+74),然后出现1,但下面没有0,因此值保持不变(1997)。然后是另一个1,后面跟着两个0,得到9(4+3+2),依此类推。

yquaqz18

yquaqz181#

这里有另一种选择,

·单元格F2中使用的公式

=LET(
a,B2:B13,
b,C2:C13,
c,SCAN(0,a,LAMBDA(x,y,x+y)),
d,UNIQUE(c),
IF(a=0,"",INDEX(MMULT(N(TOROW(c)=d),b),XMATCH(c,d))))

**注:**请参考以下每个变量的作用:

·首先,我们采用两个范围,即B2:B13C2:C13,分别定义为ab
·接下来,我们使用SCAN()函数返回一个数组,初始值为0,它迭代a序列中的每个值,将当前x添加到使用LAMBDA()的前一次迭代的y的累积值。返回值被指定为c

SCAN(0,a,LAMBDA(x,y,x+y))

·现在,我们正在使用UNIQUE()函数从c中删除任何重复项

=LET(
a,B2:B13,
b,C2:C13,
c,SCAN(0,a,LAMBDA(x,y,x+y)),
d,UNIQUE(c),
d)

·接下来,我们使用MMULT()执行矩阵乘法。

N(TOROW(c)=d)

上面的代码将数组c中的每个值与数组d中的每个值进行比较,并返回一个大小相同的BOOLEAN数组,如果相等,则为TRUE,否则为FALSE。然后,MMULT()执行矩阵乘法,其结果与b中的行数和列数相同,其中BOOLEAN的每个值与相应的b的乘积求和。

MMULT(N(TOROW(c)=d),b)

·最后,我们使用IF()INDEX()以及XMATCH()来返回所需的输出。

==> XMATCH()函数用于查找c的每个值在数组d中的位置,该数组在INDEX()中用于返回上述矩阵乘法的相应值。最后,将其 Package 在IF()函数中,该函数检查数组a中的任何值是否等于0,然后返回空值,否则它将使用INDEX()XMATCH()返回值。
当然,你不必填满,因为它会动态溢出。

vuktfyat

vuktfyat2#

这也可以在没有helper/lambda的情况下使用MMULT来完成:

=LET(a, B3:B14,
     b, C3:C14,
     x, SEQUENCE(ROWS(a)+1),
     y, DROP(MMULT(--(TOROW(x)<=x),--VSTACK(a,1)),-1),
IF(a,MMULT(--(TOROW(y)=y),b),""))

它首先存储范围ab,以便于在公式中引用。
x是范围a +1*****的行数的计数器。xy中用于检查x是否等于或大于范围内的其他值乘以范围a*****中这些行中找到的1的数量。

*****将额外的1添加到范围a中,以模拟范围a中的最终1,直到我们想要最终求和的位置。

所以y是一个计数器,从第一个找到的1开始,如果a中的值再次等于1,则加1。在计算完这个之后,不再需要末尾的助手行,因为它计算到了它应该求和的地方。因此,我们从y中删除(删除)最后一行,以便与要求和的范围(b)的大小兼容。
最后检查a是否为1。如果是,则对b中的行数求和,其中y中的行数等于y中的行数。

dkqlctbz

dkqlctbz3#

群组求和

=LET(o,B3:B14,v,C3:C14,c,1,n,"",
    r,ROWS(o),rs,SEQUENCE(r),iss,FILTER(rs,o=c,""),
    ies,IF(ROWS(iss)=1,r,VSTACK(DROP(iss-1,1),r)),
MAP(rs,LAMBDA(mr,
    IF(INDEX(o,mr)<>c,n,LET(
        ri,XMATCH(mr,iss),is,INDEX(iss,ri,1),
        ie,INDEX(ies,ri,1),sr,SEQUENCE(ie-is+1,,is),
    SUM(INDEX(v,sr)))))))
bprjcwpo

bprjcwpo4#

这里,另一阵列解决方案方法,即它一次溢出整个结果(* 公式1*):

=LET(A,A1:A12, B,B1:B12, n,ROWS(A), seq,SEQUENCE(n),
 MAP(seq, LAMBDA(s,IF(INDEX(A,s)=1,LET(end, @FILTER(seq, (seq>s) * (A=1),n+1),
 SUM(FILTER(B, (seq>=s) * (seq<end)))),""))))

或者你可以使用这个替代方法(* 公式2*)。前面的公式有一点开销计算,因为我们只需要获得第一个FILTER调用的第一个元素,但我们获得了整个FILTER输出。下面的公式避免了这一点。

=LET(A,A1:A12, B,B1:B12, n,ROWS(A), seq,SEQUENCE(n), idx,IF(A=1,seq,0),
 MAP(idx, LAMBDA(x, IF(x=0,"", SUM(FILTER(B, (seq>=x) 
  * (seq<XLOOKUP(x+1,idx,idx,n+1,1))))))))

下面是 * 公式1* 的输出:

在 * 公式1* 中,它通过MAP迭代输入(seq)的所有索引位置。在每次迭代(s)中,它检查给定索引s是否列A值(A)通过INDEX等于1。如果是这种情况(否则返回空字符串),则通过FILTER函数查找索引位置大于当前迭代值(s)的下一个1值的索引位置(end)。由于我们只对FILTER输出的第一个值(下一个1值的索引位置)感兴趣,因此我们使用Implicit intersection operator: @。如果条件不匹配,则使用FILTER的第三个输入参数返回n+1,其中n是输入数据的行数。
由于end表示下一个1值的索引位置,或者如果没有找到更多的1值,则表示行数加1,现在我们可以再次使用FILTERsend-1索引位置中选择B列值(B)并对其求和。
在 * 公式2* 中,它首先确定A等于1idx)的索引位置,否则返回0。根据定义,idx的非零值按升序排列。为了识别间隔的结束(A中以下1值的位置),它使用XLOOKUP和近似搜索(1-等于或大于)来查找x的下一个元素,即x+1 .它返回下一个1值在A中的位置,否则返回n+1。因此,要求和的B的范围针对xXLOOKUP的输出之间的索引位置seq进行过滤。

性能分析

这里总结了基于不同的场景来衡量不同的性能,为这个问题提供了答案。我正在考虑以下场景:
1.A:最差情况,第一行为1,其余为0
1.B:列A的随机集,0 s显著多于1 s
1.C:A列的随机集,1 s和0 s均匀分布。
在所有情况下,我都考虑7000行的输入。
要生成非均匀[0,1]分布,我使用以下命令:
=LET(rnd, RANDARRAY(10000,1,1,10,1),IF(rnd=1,1,0))
我正在考虑以下解决方案:
1.提供方:@VBasic2008,并在其回答的评论部分进行了更正。
1.提供方:@MayukhBhattacharya。此解决方案使用MMULT,但它适用于7500以上的行。
1.提供方:@P.B(初始方法,而不是注解部分提供的方法,该方法不提供正确的结果)。值得注意的是,此解决方案在7500行附近停止工作
1.由@DavidLeal * 公式2* 使用XLOOKUP提供。* 公式1* 效率低,因此在分析中未考虑。
以下是Excel Desktop的结果:
| 场景|马尤赫·巴塔查里亚|VBasic2008| David Leal| P.B|
| - -----|- -----|- -----|- -----|- -----|
| 我的天啊|10ms| 1,070ms| 10ms| 7,300ms|
| B| 560ms| 1,310ms| 970ms| 7,710ms|
| 我的天|2,640ms| 2,270ms| 4,780ms| 7,590ms|
我会说**@MayukhBhattacharya提供的解决方案是所有测试场景中最好的解决方案**,然后是@VBasic2008提供的解决方案,它工作得很好,但它失败了最坏的情况(A)需要大量时间。
Excel for Web(免费版)下运行相同的测试引起了我的注意。我没有得到同样的结果。MayukhBhattacharya和VBasic 2008同样是最好的解决方案,但这次VBasic 2008提供的解决方案性能更好
| 场景|马尤赫·巴塔查里亚|VBasic2008| David Leal| P.B|
| - -----|- -----|- -----|- -----|- -----|
| 一个|0ms| 0ms| 10ms| 9,950ms|
| B| 770ms| 40ms| 1,570ms| 10,080ms|
| C类|3,640ms| 850ms| 9,930ms| 10,440ms|
我还测试了@JvdV,在P.B解决方案的评论部分提供,这是@MayukhBhattacharya方法的变体。@JvdV比@P.B解更有效,但比@MayukhBhattacharya解更差。

值得一提的是,@MayukhBhattacharya解决方案可以被优化,因为来自XMATCH的输入参数lookup_array是按升序排序的,所以我们可以使用输入参数search_mode=2XMATCH中进行二分搜索。它提供了对9%的改进。同样的优化也适用于@VBasic2008解决方案,因为它以升序使用XMATCHlookup_array
下面是用于执行性能分析的Excel文件的link。结果摘要位于第一个选项卡上。请注意,该文件具有以下配置:* 公式->计算选项->手动 *,避免任何特定计算干扰其他结果。它使用易变的Excel函数(RANDARRAYNOW),因此避免了自动重新计算。

其他人验证结果将是有趣的

总结

使用@VBasic2008和@DavidLeal使用的为每个组查找开始/结束的想法。使用INDEX的性能优于FILTER。例如,@DavidLeal解决方案修改为删除FILTER,如下所示,其性能与@VBasic2008解决方案相似 *,但并不更好 *:

= LET(A,A1:A12, B,B1:B12, n,ROWS(A), seq,SEQUENCE(n), idx, 
  IF(A=1,seq,0), MAP(idx, LAMBDA(x, IF(x=0,"",
  LET(xe, XLOOKUP(x+1,idx,idx,n+1,1)-1,SUM(INDEX(B, SEQUENCE(xe-x+1,,x))))))))

@VBasic2008解决方案有一个简单的方法来找到开始/结束的时间间隔,与以前的公式相比,因此性能更好。
使用MMULT来识别每个组的解决方案,与@P.b解决方案相比,当计算涉及减少的部分时,例如在@MayukhBhattacharya解决方案中,效果更好。这也有助于避免任何可能的Excel limits

考虑到以前的考虑,这两种方法都是好的战略

仍然是一个开放的问题,为什么有些方案更好地工作取决于Excel平台(桌面,Web)使用。可能内部的功能不是以相同的方式实现的,或者使用不同的版本来实现相同的功能。

3npbholx

3npbholx5#

您可以使用(隐藏的)额外列轻松完成此操作。将累计值保留在隐藏列中,仅当[0,1]列为1时才显示累计值。
请注意,在未来的帖子中,我们可以复制数据作为表格,而不是作为屏幕截图。
匹配数据中的行,在隐藏列的第3行(F是此处的隐藏列):

=IF(B3=1, IF(B4 = 1, C3, D4+C3), IF(B4 = 1, C3, C3+D4))

把这个抄到F栏。
在显示列中的第3行(此处为G)中:

=IF(B3 = 1, F3, "")

当然,把它抄在G栏。
| | 我的天啊|B|我的天|G**|
| - -----|- -----|- -----|- -----|- -----|
| 第一章| | | | |
|2| | | | |
| 三个人| |1|二百零二|三百二十三|
| 四个人| |0|四十七名| |
| 五楼| |0|七十四| |
|| | 1|一九九七年|一九九七年|
| 第七章| |1| 4|九个|
| 第八章| |0| 3| |
| 第九章| |0| 2| |
| 10个| |1| 2| 2|
| 第11章| |1| 5个|5个|
| 第12章| |1|一九八一年|一九八一年|
|十三| | 1| 3|十一|
|14| | 0|八| |

yizd12fk

yizd12fk6#

一个比许多其他人建议的更简单的答案如下(灵感来自@RichardCook)
当B列中有多个零时,有一个辅助列:
x1c 0d1x辅助列的公式为=IF(AND(C4=0,C5=0),D4+F5,D4),最终答案的公式为=IFS(C4=0,,AND(C4=1,C5=1),D4,AND(C4=1,C5=0),D4+F5)
ifs块所做的类似于RichardCook的答案,其中公式检查初始列是1还是0,如果列是1,则检查是否有多个零。如果有多个零,它使用helper列来查找总数。

相关问题