excel中替代规划求解的方法（Excel之道-规划求解问题）

如下表，某工地购买了一批长度为30m的钢筋，根据施工图纸，需要截断成4.4m、5.6m、7.2m、8.3m 4种长度的钢筋段以加个成所需形状，假设不考虑缺口损耗，试列出所有可能的截断方案。

1、如上表，先输入如上数据，注意【B2、B3：E3】格式，先只输入数字，然后设置单元格格式-数字-自定义，在类型里面输入：【G/通用格式"m/根"】，然后确定。

2、插入【复选框】，主要是用来刷新数据的。放到【D2】单元格中，右键【复选框】-【设置控件格式】-【控制】，在单元格链接里输入【$E$2】，即复选框控件值返回在$E$2单元格中。

3、选中【B4】单元格，输入公式【=IF($E$2,IF(AND($B$2-SUMPRODUCT($B4:$E4,$B$3:$E$3)<MIN($B$3:$E$3),SUMPRODUCT($B4:$E4,$B$3:$E$3)<=$B$2),B4,RANDBETWEEN(0,INT($B$2/B$3))),"")】，选中验证列【F4】单元格，输入公式【=SUMPRODUCT($B$3:$E$3,B4:E4)】，判断其值是否小于钢筋总长度，从而验证方案是否正确。公式解释：and两个逻辑条件同时成立【余料$B$2-SUMPRODUCT($B4:$E4,$B$3:$E$3)小于最短截断段；总长SUMPRODUCT($B4:$E4,$B$3:$E$3)小于钢筋总长度】来判断【B4】数据，若不成立，则【B4】重新生成随机整数【RANDBETWEEN(0,INT($B$2/B$3)))】再次判断，最多判断500次直到成立为止。

将【B4】公式拖选向右向下复制100行，双击【F4】单元格右下角，同样将公式复制100行。

4、设置迭代运算。点击菜单栏【文件】-【Excel选项】-【公式】，勾选启用迭代计算，迭代次数500次或更多，最大精度0.1，通过勾选【复选框】案件完成计算。

5、因为是随机生成，方案中可能有重复。选中【B4：F103】，点击菜单栏【数据】-数据工具】-【删除重复项】-【全选】-【确定】，删掉重复项，然后以【验证列】进行排序即可。完成。