Excel的规划求解给员工分配工作

Excel教程 2022-03-13 21:13:05

今天我们根据一个关于软件开发项目的实例，使用Excel的规划求解给员工分配工作，以便达到最优配置，让员工的工作效率大大提高。

假设5名SE（系统工程师）负责某个项目。表1是把工作分配给SE的成本（或者时间）数据。

表1

将每一项怎样分配给SE，才能使总成本最小呢？

这种问题叫做分配问题，在实际生活中经常遇到。例如，把船舶分配到哪个锚位的问题。把飞机分配到哪个登机口的问题，把乘务员分配给哪个航班的问题，把求职者分配给哪个面试负责人的问题，以及其他同类的问题都属于分配问题。此类问题可以用0-1规划法求解。下面介绍用数学公式表达分配问题的方法。

假设Xij第j项工作分配给第1名SE。例如，X24表示把检查工作分配给铃木。当Xij是O时，表示不分配工作；当Xij是1时，表示分配工作。例如，若X24=0，表示不把检查工作分配给铃木；若X24=1.表示把检查工作分配给铃木。因此，Xij有O或1两个数值（这种变量叫做双值变量或者二进制变量）。

目标函数如工式1所示。

总成本

工程方式1

由于当不分配工作时，Xij的值是0，因此，总成本等于分配个数的费用总和，目的是求总成本的最小值。设定初始约束条件：给每个SE分配一项工作。

接着，为了将每项工作分别分配给每个SE，追加下述约束条件。

最终约束条件是0-1约束。

Xij=0,1（i=1,2,3,4,5;j=1,2,3,4,5）

使用规划求解时，首先需要制作如图1所示的工作表。将变量X11-X55的数据导入单元格B11一F15中（可变单元格）。然后，输入计算公式。

首先，需要把计算总成本的公式输入单元格B20，但是若直接输入方程公式1的计算公式，则非常麻烦。因此，使用SUMPRODUCTT函数（积和函数），只需要在单元格B20中输人“=SUMPRODUCT（B3:F7,B11:F15）”即可。

图1

在单元格G11——G15中，输人计算给每名SE分配工作的成本的求和公式；在单元格B16——F16中，输入计算把每项工作分配给SE的成本的求和公式。完成这些操作之后，运行规划求解。

单击“工具”-“规划求解”，弹出“规划求解参数”对话框。在“设置目标单元格”中指定显示总成本数据的单元格B20；在“等于”中选择“最小值”；在“可变单元格”中指定单元格B11——F15（图2）。

图2

接下来，指定约束条件。首先指定变量是0一1数据。

单击图2的“添加”按钮，弹出“添加约束”对话框（图3）。在对话框左边的“单元格引用位置”中指定可变单元格（B11——F15），在中间的比较符号中选择“bln”，在右边的“约束值”中自动显示出“二进制”（0-1数据）。

然后，单击图3的“添加”按钮，设定下述约束条件：给每名SE分配工作的总成本是1；把每项工作分配给SE的总成本也是1。单击“确定”。接着，单击“规划求解参数”对话框中的“求解”按钮。

图3

求出如图4所示的最优解。可知采取如下工作分配方法时：设计-铃木，管理-高桥，编程-小林，检查-山田，文件-吉田，总成本达到最小值35。

图4

根据这节的Excel教程，我们通过Excel可以对员工进行工作分配，让员工的工作更加精细，从而达到最好的工作状态和效率。