我试图创建一个动态Excel公式,当一行中的值为1后跟0时,该公式会对另一行中的值求和。如果在最后一个0之后,有一个1,再次求和,直到您达到另一个1。
如图所示,F6中的第一个值必须是323(202+47+74),然后出现1,但下面没有0,因此值保持不变(1997)。然后是另一个1,后面跟着两个0,得到9(4+3+2),依此类推。
yquaqz181#
这里有另一种选择,
·单元格F2中使用的公式
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:B13和C2:C13,分别定义为a和b。·接下来,我们使用SCAN()函数返回一个数组,初始值为0,它迭代a序列中的每个值,将当前x添加到使用LAMBDA()的前一次迭代的y的累积值。返回值被指定为c。
B2:B13
C2:C13
a
b
SCAN()
0
x
LAMBDA()
y
c
SCAN(0,a,LAMBDA(x,y,x+y))
·现在,我们正在使用UNIQUE()函数从c中删除任何重复项
UNIQUE()
=LET( a,B2:B13, b,C2:C13, c,SCAN(0,a,LAMBDA(x,y,x+y)), d,UNIQUE(c), d)
·接下来,我们使用MMULT()执行矩阵乘法。
MMULT()
N(TOROW(c)=d)
上面的代码将数组c中的每个值与数组d中的每个值进行比较,并返回一个大小相同的BOOLEAN数组,如果相等,则为TRUE,否则为FALSE。然后,MMULT()执行矩阵乘法,其结果与b中的行数和列数相同,其中BOOLEAN的每个值与相应的b的乘积求和。
d
BOOLEAN
TRUE
FALSE
MMULT(N(TOROW(c)=d),b)
·最后,我们使用IF()和INDEX()以及XMATCH()来返回所需的输出。
IF()
INDEX()
XMATCH()
==> XMATCH()函数用于查找c的每个值在数组d中的位置,该数组在INDEX()中用于返回上述矩阵乘法的相应值。最后,将其 Package 在IF()函数中,该函数检查数组a中的任何值是否等于0,然后返回空值,否则它将使用INDEX()和XMATCH()返回值。当然,你不必填满,因为它会动态溢出。
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),""))
它首先存储范围a和b,以便于在公式中引用。则x是范围a +1*****的行数的计数器。x在y中用于检查x是否等于或大于范围内的其他值乘以范围a*****中这些行中找到的1的数量。
1
*****将额外的1添加到范围a中,以模拟范围a中的最终1,直到我们想要最终求和的位置。
所以y是一个计数器,从第一个找到的1开始,如果a中的值再次等于1,则加1。在计算完这个之后,不再需要末尾的助手行,因为它计算到了它应该求和的地方。因此,我们从y中删除(删除)最后一行,以便与要求和的范围(b)的大小兼容。最后检查a是否为1。如果是,则对b中的行数求和,其中y中的行数等于y中的行数。
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)))))))
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输出。下面的公式避免了这一点。
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,现在我们可以再次使用FILTER从s到end-1索引位置中选择B列值(B)并对其求和。在 * 公式2* 中,它首先确定A等于1(idx)的索引位置,否则返回0。根据定义,idx的非零值按升序排列。为了识别间隔的结束(A中以下1值的位置),它使用XLOOKUP和近似搜索(1-等于或大于)来查找x的下一个元素,即x+1 .它返回下一个1值在A中的位置,否则返回n+1。因此,要求和的B的范围针对x和XLOOKUP的输出之间的索引位置seq进行过滤。
MAP
seq
s
A
INDEX
end
n+1
n
end-1
B
idx
XLOOKUP
x+1
这里总结了基于不同的场景来衡量不同的性能,为这个问题提供了答案。我正在考虑以下场景:1.A:最差情况,第一行为1,其余为01.B:列A的随机集,0 s显著多于1 s1.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解更差。
7000
[0,1]
=LET(rnd, RANDARRAY(10000,1,1,10,1),IF(rnd=1,1,0))
MMULT
7500
10ms
1,070ms
7,300ms
560ms
1,310ms
970ms
7,710ms
2,640ms
2,270ms
4,780ms
7,590ms
0ms
9,950ms
770ms
40ms
1,570ms
10,080ms
3,640ms
850ms
9,930ms
10,440ms
值得一提的是,@MayukhBhattacharya解决方案可以被优化,因为来自XMATCH的输入参数lookup_array是按升序排序的,所以我们可以使用输入参数search_mode=2在XMATCH中进行二分搜索。它提供了对9%的改进。同样的优化也适用于@VBasic2008解决方案,因为它以升序使用XMATCH和lookup_array。下面是用于执行性能分析的Excel文件的link。结果摘要位于第一个选项卡上。请注意,该文件具有以下配置:* 公式->计算选项->手动 *,避免任何特定计算干扰其他结果。它使用易变的Excel函数(RANDARRAY,NOW),因此避免了自动重新计算。
XMATCH
lookup_array
search_mode=2
9%
RANDARRAY
NOW
其他人验证结果将是有趣的。
使用@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)使用。可能内部的功能不是以相同的方式实现的,或者使用不同的版本来实现相同的功能。
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|八| |
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列来查找总数。
=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)
6条答案
按热度按时间yquaqz181#
这里有另一种选择,
·单元格
F2
中使用的公式**注:**请参考以下每个变量的作用:
·首先,我们采用两个范围,即
B2:B13
和C2:C13
,分别定义为a
和b
。·接下来,我们使用
SCAN()
函数返回一个数组,初始值为0
,它迭代a
序列中的每个值,将当前x
添加到使用LAMBDA()
的前一次迭代的y
的累积值。返回值被指定为c
。·现在,我们正在使用
UNIQUE()
函数从c
中删除任何重复项·接下来,我们使用
MMULT()
执行矩阵乘法。上面的代码将数组
c
中的每个值与数组d
中的每个值进行比较,并返回一个大小相同的BOOLEAN
数组,如果相等,则为TRUE
,否则为FALSE
。然后,MMULT()
执行矩阵乘法,其结果与b
中的行数和列数相同,其中BOOLEAN
的每个值与相应的b
的乘积求和。·最后,我们使用
IF()
和INDEX()
以及XMATCH()
来返回所需的输出。==>
XMATCH()
函数用于查找c
的每个值在数组d
中的位置,该数组在INDEX()
中用于返回上述矩阵乘法的相应值。最后,将其 Package 在IF()
函数中,该函数检查数组a
中的任何值是否等于0
,然后返回空值,否则它将使用INDEX()
和XMATCH()
返回值。当然,你不必填满,因为它会动态溢出。
vuktfyat2#
这也可以在没有helper/lambda的情况下使用MMULT来完成:
它首先存储范围
a
和b
,以便于在公式中引用。则
x
是范围a
+1*****的行数的计数器。x
在y
中用于检查x是否等于或大于范围内的其他值乘以范围a
*****中这些行中找到的1
的数量。*****将额外的
1
添加到范围a
中,以模拟范围a
中的最终1
,直到我们想要最终求和的位置。所以
y
是一个计数器,从第一个找到的1开始,如果a
中的值再次等于1,则加1。在计算完这个之后,不再需要末尾的助手行,因为它计算到了它应该求和的地方。因此,我们从y
中删除(删除)最后一行,以便与要求和的范围(b
)的大小兼容。最后检查
a
是否为1
。如果是,则对b
中的行数求和,其中y
中的行数等于y
中的行数。dkqlctbz3#
群组求和
bprjcwpo4#
这里,另一阵列解决方案方法,即它一次溢出整个结果(* 公式1*):
或者你可以使用这个替代方法(* 公式2*)。前面的公式有一点开销计算,因为我们只需要获得第一个
FILTER
调用的第一个元素,但我们获得了整个FILTER
输出。下面的公式避免了这一点。下面是 * 公式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,现在我们可以再次使用FILTER
从s
到end-1
索引位置中选择B列值(B
)并对其求和。在 * 公式2* 中,它首先确定
A
等于1
(idx
)的索引位置,否则返回0
。根据定义,idx
的非零值按升序排列。为了识别间隔的结束(A
中以下1
值的位置),它使用XLOOKUP
和近似搜索(1
-等于或大于)来查找x
的下一个元素,即x+1
.它返回下一个1
值在A
中的位置,否则返回n+1
。因此,要求和的B
的范围针对x
和XLOOKUP
的输出之间的索引位置seq
进行过滤。性能分析
这里总结了基于不同的场景来衡量不同的性能,为这个问题提供了答案。我正在考虑以下场景:
1.A:最差情况,第一行为1,其余为0
1.B:列A的随机集,
0
s显著多于1
s1.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=2
在XMATCH
中进行二分搜索。它提供了对9%
的改进。同样的优化也适用于@VBasic2008解决方案,因为它以升序使用XMATCH
和lookup_array
。下面是用于执行性能分析的Excel文件的link。结果摘要位于第一个选项卡上。请注意,该文件具有以下配置:* 公式->计算选项->手动 *,避免任何特定计算干扰其他结果。它使用易变的Excel函数(
RANDARRAY
,NOW
),因此避免了自动重新计算。其他人验证结果将是有趣的。
总结
使用@VBasic2008和@DavidLeal使用的为每个组查找开始/结束的想法。使用
INDEX
的性能优于FILTER
。例如,@DavidLeal解决方案修改为删除FILTER
,如下所示,其性能与@VBasic2008解决方案相似 *,但并不更好 *:@VBasic2008解决方案有一个简单的方法来找到开始/结束的时间间隔,与以前的公式相比,因此性能更好。
使用
MMULT
来识别每个组的解决方案,与@P.b解决方案相比,当计算涉及减少的部分时,例如在@MayukhBhattacharya解决方案中,效果更好。这也有助于避免任何可能的Excel limits。考虑到以前的考虑,这两种方法都是好的战略。
仍然是一个开放的问题,为什么有些方案更好地工作取决于Excel平台(桌面,Web)使用。可能内部的功能不是以相同的方式实现的,或者使用不同的版本来实现相同的功能。
3npbholx5#
您可以使用(隐藏的)额外列轻松完成此操作。将累计值保留在隐藏列中,仅当[0,1]列为1时才显示累计值。
请注意,在未来的帖子中,我们可以复制数据作为表格,而不是作为屏幕截图。
匹配数据中的行,在隐藏列的第3行(F是此处的隐藏列):
把这个抄到F栏。
在显示列中的第3行(此处为G)中:
当然,把它抄在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|八| |
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列来查找总数。