如何用单个Excel函数生成指定和的随机长度整数数组并规避#spill!错误？

核心需求 #

用单个Excel函数生成满足以下要求的数组：

• 数组长度为指定范围内的随机整数
• 数组内所有整数的和精确等于给定目标值
• 需解决此类构造中常见的易失性问题，提供最优可行方案

硬性约束 #

• 仅允许使用单个Excel函数，不可拆分或嵌套依赖其他辅助函数
• 禁止使用VBA、Lambda命名区域、显式/手动求解器等自定义工具
• 数组长度必须落在指定上下限区间内
• 数组元素的和必须精确等于目标值，无误差

示例参数 #

• 数组随机长度范围：5-30个整数
• 目标和：1000

现有方案的缺陷 #

• 部分方案无法实现随机长度数组，且求和结果存在1-2的误差，无法精确命中目标值
• 另一类方案依赖辅助函数，或使用SEQUENCE(RANDBETWEEN(...))这类存在官方文档提及的易失性已知问题的写法，会导致间歇性计算异常

最优可行方案 #

函数实现（以示例参数为例） #

=IFERROR(LET(n,RANDBETWEEN(5,30),x,RANDARRAY(n,1,1,INT(1000/n),TRUE),adj,1000-SUM(x),idx,RANDBETWEEN(1,n),CHOOSE({1,2},x,IF(SEQUENCE(n)=idx,x+adj,x))),""))

方案说明 #

1. 随机长度生成：用RANDBETWEEN(5,30)确定数组长度n，规避SEQUENCE(RANDBETWEEN())的易失性问题
2. 基础随机数组：RANDARRAY(n,1,1,INT(1000/n),TRUE)生成初始随机整数，确保每个元素在合理区间内，减少调整幅度
3. 精确求和校准：计算初始数组与目标和的差值adj，随机选择一个元素加上该差值，保证总和精确等于1000
4. 异常处理：外层嵌套IFERROR，避免因易失性导致的间歇性#SPILL!错误，返回空值替代

通用化修改 #

将示例中的5,30替换为自定义长度范围，1000替换为目标和即可适配其他场景。

内容的提问来源于stack exchange，提问作者JB-007