Excel 如何计算钢材切割方式?

公司有一钢材长4759mm,实际工作中需要用到图6.100中A2:A21的20种规格的材料,现在的问题是如何切割这根钢材,使其刚好满足需求,又不浪费材料。

解题步骤

钢材切割思路有两种:一是每种规格的材料只生成一件,组合长度等于 4759,用程序语言来描述就是“计算 A2:A21 中哪些单元格的值相加刚好等于 4759”;二是不限制每种规格的产品数量,只要满足“不浪费材料”这个需求即可。两种切割方式的实现方式不同。

此外还要注意一点,如果没有等于4759的值,那么找出合计最接近4759的值。

实现此需求的唯一工具是规划求解,以切割方式为例,具体操作步骤如下。

1.在D1中输入“剩余材料”,在D2中输入公式“=C2-SUMPRODUCT(A2:A21,B2:B21)”。

2.单击功能区的“数据”→“规划求解”,从而弹出“规划求解参数”对话框。

3.将目标设置为D2,然后在“最大值”、“最小值”和“目标值”3个选项中选择“目标值”,且在右方输入“0”,最后将可变单元格设置为B2:B21,设置界面如图6.101所示。

图6.101 设置目标和可变单元格

4.单击右方的“添加”按钮,从而弹出“改变约束”对话框,然后按图6.102所示的方式设置选项,表示B2:B21区域只能产生二进制的值。

图6.102 设置约束条件

5.单击下方的“求解”按钮,当弹出“规划求解结果”对话框时直接单击“确定”按钮关闭对话框,然后在工作表中将产生图6.103所示的求解结果。

图6.103 规划求解结果

图6.103表示A9、A12、A13、A14、A15和A17的数值合计等于4759,应将钢材切割为957+945+670+928+620+639。

假设切割时不限制同一个规格的产品数量,那么应按以下步骤操作。

1.单击功能区的“数据”→“规划求解”,从而弹出“规划求解参数”对话框。

2.删除对话框中的约束条件,然后重新添加图6.104和图6.105所示的两个条件。

图6.104 添加约束条件一

图6.105 添加约束条件二

3.返回“规划求解参数”对话框,单击下方的“求解”按钮,当弹出“规划求解结果”对话框时直接单击“确定”按钮关闭对话框,然后在工作表中将产生图6.106所示的结果。

图6.106 规划求解结果

图6.106表示A15+A16+A17×4+A20刚好等于4759,此切割方式允许某个规格的产品切割多件。当然,如果需要也可以设置上限,如每种规格的产品不超过5件等,可以自行设置约束条件。

知识扩展

1.本例中要求“没有等于4759的值时,找出合计最接近4759的值”,由于已经找到了合计刚好等于4759的组合,因此不再测试合计最接近4759的组合。

2.求解过程就是逐一测试从而找到最优解的过程,数据量越大则运算过程越久,本例有可能用5秒钟也可能用半分钟以上,视计算机硬件的优劣而定。

3.本例中用到了bin二进制的数据,其实就是0和1,当B2:B21限定为二进制数据时,该区域只能产生0或1,当值为1时,表示该值为本例的解。

4.事实上本例有多组解,使用不同计算机执行以上步骤有可能得到不同的解。A12+A16×2+A17×2+A21,即945+862×2+639×2+812也等于4759。

发布者

Excel22

专为职场萌新准备的免费全面的Excel入门及提高学习网站,也可作为Excel老司机杂耍各种函数的宝典 —— Excel22.com 网址超好记

发表评论

邮箱地址不会被公开。 必填项已用*标注