设置主页 | 收藏本站 Excel学习网-公益型的EXCEL在线学习网站,助您轻松办公!

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

2014年04月17日 10:15 发布来源:Excel网

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

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

Excel工作分配

表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)”即可。

Excel约束条件

图1

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

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

Excel规划求解参数

图2

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

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

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

Excel添加约束

图3

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

Excel规划求解结果

图4

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

原创文章如转载请注明:转载自Excel学习网 [ http://www.excelcn.com/]
需要保留本文链接地址:http://www.excelcn.com/shujufenxi/476.html
分享到:
网站地图 | XML地图 | 免责声明 | 关于我们 | Excel学习网:优秀的EXCEL在线学习和资源分享网站。
版权所有: CopyRight © 2013-2018 www.excelcn.com All Rights Reserved.
豫ICP备12002644号