运用Excel的规划求解工具解决最佳人员分配问题

赵旭娟

摘要：Excel是用户最熟悉和便捷的办公应用电子表格软件，具备强大的数据处理、数据分析等功能。实际管理中遇到的问题纷繁复杂、各式各样，既消耗管理人员的大量精力，也困扰管理人员做出正确决策。如何使用便捷、高效地工作方式解决管理中的优化问题，考验着管理人员的素质和技能。该文以求解某企业特定岗位人员需求为例，提出了解决问题的基本思路、方法及步骤，能促使管理者有效解决实际工作中遇到的人员分配问题的优化方案。

关键词：Excel；规划求解；数学模型；线性规划

中图分类号：TP317 文献标识码：A 文章编号：1009-3044（2018）16-0270-03

1 背景

随着计算机技术普及和信息化手段的运用，办公软件应用成为现代人工作、学习、生活的必备工具之一。而Microsoft公司（微软）开发的Excel可谓是当前覆盖面最广的电子表格软件，其在各大中小学校学习普及率和在各大类企事业单位使用率远远高于其他同类软件。以浙江省为例，无论是省高校计算机等级考试中的一级Windows、二级办公软件高级应用技术，还是计算机职称考试，均是基于微软公司办公软件而开发相应考试平台。

追其根本，原因在于该公司开发的Excel具备强大数据处理、数据分析功能，能方便、高效地为人们解决各类实际问题。同时，该公司开发的Excel能加载规划求解工具，使用Frontline Systems公司的John Watson 和Dan Flastra 提供的有节变量单纯性法和分值界定法的实现算法1。该工具具有易用、便捷、高效、准确率高等特点，能有效解决线性规划问题。

2 求解问题的基本思路

管理科学派（即数量学派）的学者们认为，管理是制定和运用数学模型和程序的系统，应用科学方法解决管理实际问题，通过各职能之间的互相关系分析，再用数学符号和公式来表达计划、组织、控制、决策等管理过程中的合乎逻辑的程序，求出最优的解答，以达到企业的管理目标。其主张应用计算机管理和技术，提高管理的经济效益。

该文以快速解决某企业特定同岗位所需人员问题为例。在求解过程当中，以管理科学派思想为指引，首先基于实际问题本身开展认真分析，再建立运筹学知识中所提及的线性规划数学模型，并将该模型转化到Excel中进行表达，然后利用规划求解工具，方便、快捷地求出最佳答案（也即最优解）。如此，方可为企业管理人员最终决策提出依据。求解管理问题的基础思路框架图如下图1所示。

3 案例与问题阐述

该文案例：解决某企业特定同岗位所需人员问题

具体为：某企业的某个特定岗位，其所需要的岗位上班人数具体如下表1所示。为确保在该企业工作的人员，符合“每周工作五天，连续休息两天”的规定，现需要进行合理安排，以满足该岗位每天需求的最少人数。请问，企业管理人员应如何决策更合理，最佳方案应是什么？

4.1 概念界定

所谓线性规划，指的是一种解决在一定约束条件下追求最大或最小的线性目标函数的办法2。同时，基本的线性规划数学模型建立一般分为三步：第一步，假设决策变量；第二步，建立目标函数；第三步，寻找约束条件。

4.2 问题分析

要符合工作人员每周连续休息两天的规定，同时要满足该岗位最少需求人数，对企业管理者来说，实际上就是要求得该岗位最少的总人数，以及求得每天都要开始上班的人数。并且，工作人员每周连续休息两天，也就意味着每个人都是工作五天，连续休息两天。因此，只要能求出连续休息两天的岗位人数，也就可以计算出岗位总人数。

4.3 模型建立

结合线性规划模型建立步骤和问题分析，进而得出，该案例的数学模型建立过程，具体如下：

第一步，假设决策变量

假定[Xi]为周[i]开始休息的人数，[i=1，2，3，4，5，6，7]。把连续休息两天的人员按照开始休息时间分成为7类，各类的人数分别为[X1]，[X2]，[X3]，[X4]，[X5]，[X6]，[X7]。

第二步，建立目标函数

总岗位人数[Z]最少值MIN，就是[X1]，[X2]，[X3]，[X4]，[X5]，[X6]，[X7]之和的最小值。

目标函数即为：[MINZ]=[X1]+[X2]+[X3]+[X4]+[X5]+[X6]+[X7]

第三步，寻找约束条件（即模型建立）

根据岗位需求人数统计表，可以得到如下的约束条件表达式，也即数学模型：

[Xi≥0，Xi是整数，i= 1，2，3，4，5，6，7]

从上述建立的模型可知，该案例的问题实际上就是纯整数规划问题，也属线性规划问题之一。实际当中，数学模型的线性规划求解方法可以有图解法、Excel求解法、Python算法等。考慮到Excel的普及程度高、适用性强、易用性好、准确率高等特点，该文将采用Excel求解法，就是运用Excel电子表格软件对上述模型进行转化，并用规划求解工具，得出最优解。

5 模型转化与Excel求解

5.1 创建文件及表信息

即Excel中转化数学模型信息。根据问题分析及模型建立步骤，启动Excel程序，新建文件，在表中按下图2输入相应信息。所输入的信息，要对目标函数、决策变量、约束条件等进行合理布局，便于后期通过公式计算，利用规划求解工具得出结果，同时便于对相应原始数据进行修改。

打开Excel程序后，在“文件|选项|加载项”对话框中，选择下方的“管理-Excel加载项”右侧的“转到……”按钮，单击，选择“规划求解加载项”，确定。若Excel程序中曾加载过规划求解工具，则此步骤可忽略。

5.3 设置公式和函数

为求岗位总人数，B14单元格需要用到的函数为SUM；各约束条件对应单元格（I8～I14）应按照约束条件要求设置相应的公式。具体公式和函数设置参照下表2进行。

5.4 运用规划求解工具解题

将光标放任意位置，点击“数据”工具栏上的“规划求解”按钮。并对照数学模型和前期单元格设置信息，将目标函数、决策变量、约束条件进行依次设置。①目标值选择B2、最小值；②可变单元格（即为决策变量）选择B5：H5；③添加约束条件7个，对应模型中约束条件的不等式，选择对应单元格；④设置选择求解方法为“单纯线性规划”。具体参数设置结果参照下图3所示。⑤完成后，单击“求解”按钮。在跳出对话框中选择“保留规划求解的解”，如下图4所示，再单击确定。表中立即显示了最佳求解结果，如下图5所示。

由图5可知，该案例问题的最优方案已经得出。作为该企业管理人员，选择最佳的解决方案就是：应最少安排33人在该岗位，其中周一至周日开始上班人数依次为10、4、7、3、0、2、7，每周一至周日在岗人数依次为24、16、19、22、23、30、31。本例中，若对岗位最少人数进行修改，只需要重复“5.4运用规划求解工具解题”步骤，即可重新获得最新结果，简单、方便、快捷。

6 总结

企业管理人员在管理运筹过程中，最优方案的决策问题，体现了管理者的水平和能力。与该案例相近的同类管理问题，可借鉴该文提出的求解最佳人员分配问题的基本思路和方法，以建立数学模型为基础，通过Excel合理布局模型中的各要素信息，并通过设置相应的公式和函数，再运用Excel的规划求解工具，求出最优解。如此便可为企业管理人员解决实际需求，提高管理效率，促进企业目标达成。

参考文献：

[1] 杨秋辉， 游志胜， 攀鸿， 等. 用线性规划求解工程混合料最优配料问题[J]. 四川大学学报： 自然科学版， 2005（1）：198-201.

[2] 陈芳. 基于Excel的线性规划求解模型设计初探[J]. 科技展望， 2017（9）： 176-177.