在 COMBIN() 中返回嵌套序列的 Excel 函数 [英] Excel function to return a nested SEQUENCE within COMBIN()
问题描述
我目前的功能是:
=SUM(COMBIN(5,SEQUENCE($A$1,,$A$1,-1)) * COMBIN(4,SEQUENCE($A$1,,$A$1,-1)))
其中 $A$1 = 任何潜在的整数.例如,如果它是 3,它将返回 120,即
where $A$1 = any potential integer. So for example, if it were 3 it would return 120 i.e.
COMBIN(5...) | COMBIN(4...) | 总和 |
---|---|---|
10 | 4 | 40 |
10 | 6 | 60 |
5 | 4 | 20 |
120 |
我需要更改公式,以便最终数字基于 $A$1 中输入数字的嵌套序列,因此使用相同的示例,其中 $A$1 = 3 结果为 220,实际上是将公式的结果相加$A$1 递减 1 直到达到 1,即
I need to change the formula so the final number is based on a nested sequence of the input number from $A$1 so using the same example where $A$1 = 3 the result is 220 in effect summing the results of the formula with a decrementing $A$1 by 1 until it reaches 1 i.e.
COMBIN(5...) | COMBIN(4...) | 总和 |
---|---|---|
10 | 4 | 40 |
10 | 6 | 60 |
5 | 4 | 20 |
10 | 6 | 60 |
5 | 4 | 20 |
5 | 4 | 20 |
220 |
或有效 =SUM(COMBIN(5,SEQUENCE($A$1,,$A$1,-1)) * COMBIN(4,SEQUENCE($A$1,,$A$1,-1)), COMBIN(5,SEQUENCE($A$1-1,,$A$1-1,-1)) * COMBIN(4,SEQUENCE($A$1-1,,$A$1-1,-1)), COMBIN(5,SEQUENCE($A$1-2,,$A$1-2,-1)) * COMBIN(4,SEQUENCE($A$1-2,,$A$1-2,-1)))
但是因为我不知道 $A$1 是什么,所以我不能这样写,因为我不知道 $A$1-x 什么时候会达到 1 并因此停止求和.
but because I don't know what $A$1 is, I can't write it out this way as i won't know when $A$1-x will reach 1 and therefore stop summing.
推荐答案
你可以用三角矩阵来做 - 我看不出有什么特别的理由要从起始值开始倒数,你可以直接倒数矩阵将如下所示:
You could do it with a triangular matrix - I don't see any particular reason to count down from the starting value, you could just count up to it so the matrix would look like this:
1 -1 -1
1 2 -1
1 2 3
不使用带有 -1 的位置.
where the positions with -1 in aren't used.
在 Excel 365 中,您可以通过 2 个步骤来说明这一点:
In Excel 365 you can illustrate this with 2 steps:
=IF(SEQUENCE(1,A1)>SEQUENCE(A1,1),-1,SEQUENCE(1,A1))
获得(比如说)C1中的矩阵和
to get the matrix in (say) C1 and
=SUM(IFERROR(COMBIN(4,C1#)*COMBIN(5,C1#),0))
处理组合
或者使用像这样的 Let 公式来组合两个步骤:
Or use a Let formula like this to combine the two steps:
=LET(
rowSeq,SEQUENCE(A1),
colSeq,SEQUENCE(1,A1),
matrix,IF(colSeq>rowSeq,-1,colSeq),
SUM(IFERROR(COMBIN(4,matrix)*COMBIN(5,matrix),0)))
或更短:
=LET(
rowSeq,SEQUENCE(A1),
colSeq,SEQUENCE(1,A1),
SUM(IF(colSeq>rowSeq,0,COMBIN(4,colSeq)*COMBIN(5,colSeq)))
)
Lambda 版本
定义一个 Lambda 函数 SumOfCombo:
Define a Lambda function SumOfCombo:
=LAMBDA(N,IF(N<=0,0,SUM(COMBIN(4,SEQUENCE(N))*COMBIN(5,SEQUENCE(N)))+SumOfCombo(N-1)))
并调用它
=SumOfCombo(A1)
这篇关于在 COMBIN() 中返回嵌套序列的 Excel 函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!