Excel动态计算多组二项分布组合概率：行号和达标时乘积求和

需求概述 #

现有多组（最多5组）二项分布数据，每组包含成功次数（整数）和对应概率值两列（标记为p1、p2…p5）。需计算所有场景组合后，总成功次数等于目标值的最终概率——即所有组成功次数之和等于目标值时，对应各组概率的乘积之和。

例如计算总成功次数为3的概率时，需累加所有满足row1 + row2 + row3 = 3的概率组合乘积：

pFinal =
p1-3 * p2-0 * p3-0  +
p1-2 * p2-1 * p3-0  +
p1-2 * p2-0 * p3-1  +
p1-1 * p2-2 * p3-0  +
p1-1 * p2-1 * p3-1  +
p1-1 * p2-0 * p3-2  +
p1-0 * p2-3 * p3-0  +
p1-0 * p2-2 * p3-1  +
p1-0 * p2-1 * p3-2  +
p1-0 * p2-0 * p3-3

现有问题 #

已实现仅2组数据的计算逻辑（使用INDIRECT、ADDRESS、SORTBY函数处理数组），但新增第3组及以上数据后，原有方案无法适配。

简化场景示例 #

• 第一组：掷3枚骰子，点数≥4算成功（50%概率），成功次数分布为：0→12.5%、1→37.5%、2→37.5%、3→12.5%
• 第二组：掷2枚骰子，点数≥5算成功（33%概率），成功次数分布为：0→44.4%、1→44.4%、2→11.1%、3→0%

计算总成功次数为4的概率时，需累加以下组合的乘积：

• 第一组3次成功 + 第二组1次成功：12.5% * 44.4% = 5.56%
• 第一组2次成功 + 第二组2次成功：37.5% * 11.1% = 4.17%
• 第一组1次成功 + 第二组3次成功：37.5% * 0% = 0%

最终总概率：5.56% + 4.17% + 0% = 9.73%

动态适配Excel公式方案 #

前提准备 #

将每组二项分布数据整理为两列结构：第一列为成功次数（从0开始的连续整数），第二列为对应概率。假设最多5组数据分别放在以下区域：

• 组1：A2:B[max_row1]
• 组2：D2:E[max_row2]
• 组3：G2:H[max_row3]
• 组4：J2:K[max_row4]
• 组5：M2:N[max_row5]

核心公式（支持1-5组动态适配） #

使用LET函数封装逻辑，结合MMULT、TOCOL、TOROW实现多组卷积计算，无需手动修改公式即可适配新增组：

=LET(
    // 定义各组数据：成功次数列、概率列，空组用空数组占位
    r1, TOCOL(A2:INDEX(A:A,COUNTA(A:A)),1), p1, TOCOL(B2:INDEX(B:B,COUNTA(B:B)),1),
    r2, TOCOL(D2:INDEX(D:D,COUNTA(D:D)),1), p2, TOCOL(E2:INDEX(E:E,COUNTA(E:E)),1),
    r3, TOCOL(G2:INDEX(G:G,COUNTA(G:G)),1), p3, TOCOL(H2:INDEX(H:H,COUNTA(H:H)),1),
    r4, TOCOL(J2:INDEX(J:J,COUNTA(J:J)),1), p4, TOCOL(K2:INDEX(K:K,COUNTA(K:K)),1),
    r5, TOCOL(M2:INDEX(M:M,COUNTA(M:M)),1), p5, TOCOL(N2:INDEX(N:N,COUNTA(N:N)),1),
    // 过滤空组（仅保留有数据的组）
    groups, FILTER(CHOOSE({1,2},HSTACK(r1,r2,r3,r4,r5),HSTACK(p1,p2,p3,p4,p5)),BYCOL(HSTACK(r1,r2,r3,r4,r5),LAMBDA(c,COUNTA(c)>0))),
    // 初始化卷积结果：第一组数据
    conv_r, INDEX(groups,,1), conv_p, INDEX(groups,,2),
    // 循环处理剩余组，逐步卷积
    conv, LAMBDA(r,p,new_r,new_p,LET(
        all_p, MMULT(TOCOL(p),TOROW(new_p)),
        all_r, MMULT(TOCOL(r),TOROW(1+0*new_r)) + MMULT(TOCOL(1+0*r),TOROW(new_r)),
        grouped, GROUPBY(all_r, all_p, SUM, 0),
        HSTACK(INDEX(grouped,,1),INDEX(grouped,,2))
    )),
    result, REDUCE(HSTACK(conv_r,conv_p),DROP(groups,1),LAMBDA(acc,next,conv(INDEX(acc,,1),INDEX(acc,,2),INDEX(next,,1),INDEX(next,,2)))),
    // 输出结果：匹配目标值的概率（将X替换为实际目标值）
    X, 4,
    XLOOKUP(X,INDEX(result,,1),INDEX(result,,2),0)
)

公式说明 #

1. 数据定义：通过TOCOL和INDEX自动获取每组的成功次数和概率列，空组自动识别为无效数据。
2. 组过滤：仅保留有数据的组，实现动态适配1-5组的需求。
3. 卷积计算：使用REDUCE循环处理每组数据，通过MMULT生成所有可能的成功次数组合和概率乘积，再用GROUPBY合并相同总成功次数的概率之和。
4. 目标值匹配：最后用XLOOKUP提取目标值对应的最终概率，将公式中的X替换为实际需要计算的总成功次数即可。

替代方案（兼容旧版Excel，无动态数组） #

如果使用无动态数组的旧版Excel，可通过嵌套SUMPRODUCT结合数组常量实现，以3组为例：

=SUMPRODUCT(
    --(A2:A5+D2:D5+G2:G5=X),
    B2:B5,E2:E5,H2:H5
)

注：该方案需手动扩展组数（最多5组时需添加对应列的数组），且需确保每组成功次数列长度一致（不足补0概率）。

内容的提问来源于stack exchange，提问作者Vallis