在 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屋！