如何在Excel中生成满足行和固定、列和上限约束的随机矩阵？

纯Excel公式的局限性 #

只用普通Excel公式几乎无法稳定满足每行和固定、每列和不超上限的双重约束：

• 满足行约束的常规公式（比如=行总计 - SUM(同行前N-1个随机值)），本质是先随机生成前N-1个值，最后一个值补全行和。这种方式生成的列总和完全随机，无法主动控制不超过上限。
• 公式是静态计算逻辑，没有迭代修正能力，一旦列总和超标，无法自动调整单元格数值来同时适配两个约束。即使强行用循环引用+迭代计算做近似修正，不仅设置复杂，还经常出现无解或收敛失败的情况，实用性极低。

推荐使用Solver工具 #

这属于典型的带约束的随机优化问题，Solver是最直接的解决方案，步骤如下：

1. 初始化矩阵：在目标矩阵区域填入初始值，可以用满足行约束的公式生成（比如=RANDARRAY(1,COLUMNS()-1,0,行总计,TRUE)，最后一列用行总计减去前几列之和），也可以直接填随机数。
2. 打开Solver：在「数据」选项卡中找到Solver（如果没有需要先加载「规划求解加载项」）。
3. 设置参数：
   • 目标单元格：任选一个空白单元格即可（我们不需要优化特定目标，只需要找满足约束的随机解），目标值设为「值为」任意数。
   • 可变单元格：选中整个目标矩阵区域。
   • 添加约束：
     • 每行的求和单元格 = 对应的行总计（硬约束）
     • 每列的求和单元格 ≤ 对应的列总计上限（软约束）
     • 矩阵所有单元格 ≥ 0（如果需要非负随机值）
4. 求解：选择「进化」求解方法（适配随机类约束问题），点击「求解」即可得到满足双重约束的随机矩阵。重复求解可生成不同的随机解。

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