小兔网

制定模型   |  反复试验   |  解决模型

Excel中使用求解器查找人员分配到任务,以最大程度地降低总成本。

制定模型

我们要解决的模型在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。满足所有约束条件。