在Excel中使用求解器查找人员分配到任务,以最大程度地降低总成本。
制定模型
我们要解决的模型在Excel中如下所示。
1.要制定此分配问题,请回答以下三个问题。
一个。要做出什么决定?对于此问题,我们需要Excel找出要分配给哪个任务的人员(是= 1,否= 0)。例如,如果我们将人员1分配给任务1,则单元格C10等于1。否则,单元格C10等于0。
b。这些决定受到哪些限制?每个人只能执行一项任务(Supply = 1)。每个任务仅需要一个人(需求= 1)。
C。这些决策的总体绩效指标是什么?绩效的总体衡量标准是作业的总成本,因此目标是最大程度地减少此数量。
2.为了使模型更易于理解,请命名以下范围。
范围名称 | 细胞 |
---|---|
成本 | C4:E6 |
分配 | C10:E12 |
分配人员 | C14:E14 |
需求 | C16:E16 |
分配的任务 | G10:G12 |
供应 | I10:I12 |
总计花费 | I16 |
3.插入以下功能。
说明:SUM函数计算分配给一个人的任务数和分配给一个任务的人数。总成本等于SUMPRODUCT成本和分配的。
试错
使用这种公式,可以轻松分析任何试验解决方案。
例如,如果我们将人员1分配给任务1,将人员2分配给任务2,将人员3分配给任务3,则分配的任务等于供应,分配的人员等于需求。该解决方案的总成本为147。
不必使用反复试验。接下来,我们将描述如何使用Excel Solver快速找到最佳解决方案。
解决模型
要找到最佳解决方案,请执行以下步骤。
1.在“数据”选项卡上的“分析”组中,单击“求解器”。
注意:找不到规划求解按钮?单击此处加载规划求解加载项。
输入求解器参数(继续)。结果应与下图一致。
您可以选择键入范围名称或单击电子表格中的单元格。
2.输入目标的TotalCost。
3.单击最小。
4.输入更改变量单元格的分配。
5.单击添加输入以下约束。
注意:二进制变量为0或1。
6.单击添加输入以下约束。
7.单击添加输入以下约束。
8.选中“使非约束变量为负”,然后选择“ Simplex LP”。
9.最后,单击解决。
结果:
最佳解决方案:
结论:最好将人员1分配给任务2,将人员2分配给任务3,将人员3分配给任务1。此解决方案的最低成本为129。满足所有约束条件。