规划求解解决任务分配问题

1规划求解解决任务分配问题对于不少项目主管、生产主管来说，任务分配工作是日常工作中的一个重要环节，但是很多时候，他们在分配任务时仅仅凭借了经验和感觉，很少会有人采用科学的手段来合理分配任务，以达到人尽其责、物尽其用的目的。而事实上，使用Excel的规划求解工具，并不需要花费多少时间就可以将任务分配工作进行科学合理的规划安排，可以最大限度的利用现有的人力物力资源来提高完成工作任务的效率。在实际工作中，任务分配问题主要研究如何将一些具体的任务分配给合适的人员或设备，使得完成总任务的开销最少。考量任务开销的标志通常有任务完成时间或完成任务所需的经济成本。与物资调运问题类似，任务分配问题也存在着任务大于、等于或小于完成对象的情况，下面分三种情况分别介绍使用Excel规划求解来解决的方法。等额任务分配任务分配问题与物资调运问题有些相似，但任务分配问题有个特点，就是在同一个任务完成周期内，每个人（每台设备）只能进行一项任务，并且每一项任务也只能分配给某一个人（某一台机器），其中只存在一一对应的关系，而不存在同一个人完成多项任务、或者同一个任务分割成多个部分交给不同的人来完成的情况。这个一一对应条件是任务分配问题的逻辑基础。某软件开发项目主管需要将某个项目中的5个独立模块的开发任务分配给5个程序员，每个程序员只能分配到1个任务。通过已有的项目开发经验和程序员对任务的评估，得到5个程序员各自完成所有模块所需时间的估算表，如图1-1所示：图1-1完成各软件模块所需的时间如果单纯从谁效率高谁来做的角度出发来分配任务，那么程序员2和程序员4都最适合完成模块1，而程序员3和程序员5最适合完成模块3，但对于整个项目计划来说，需要同时考虑模块2、模块4的任务分配安排。因此，需要使用更为科学的统筹安排方法。2使用Excel规划求解工具来解决此任务分配问题的操作方法如下：步骤1根据题目需求，在原有题目条件的下方建立规划求解所需的公式模型，如图1-2所示：图1-2建立规划求解的模型其中，B9:F13单元格区域用于记录实际的任务分配情况，可用数字0表示任务未分配，用数字1表示分配此任务。此区域将作为规划求解的可变单元格区域。G列用于统计各程序员最终的任务分配情况，根据任务分配问题的特性，每个程序员最终只能分配到1个任务。在G9单元格内输入公式“=SUM(B9:F9)”，然后向下复制填充至G13单元格。第14行用于统计各模块开发任务的最终人员安排情况，根据任务分配问题的特性，每个模块开发任务最终只能安排给1个程序员。在B14单元格内输入公式“=SUM(B9:B13)”，然后向右复制填充至F14单元格。H列用于统计实际任务分配状态下，各程序员完成对应模块所需的时间，可在H9单元格内输入公式“=SUMPRODUCT(B2:F2,B9:F9)”，然后向下复制填充至H13单元格。H14单元格用于累计H9:H13单元格中的时间，即完成所有5个模块开发任务所需的总时间，可在单元格中输入公式“=SUM(H9:H13)”，此单元格将作为规划求解的目标单元格。步骤2为了提高规划求解结果的可读性，可预先设置B9:F13单元格区域的数字格式。选中B9:F13区域，按Ctrl+1组合键，打开“单元格格式”对话框，选择“数字”选项卡，在“分类”列表中选择“自定义”，然后在右侧的“类型”文本框中输入“0”，单击“确定”按钮完成设置。步骤3选中H14单元格，单击菜单“工具”→“规划求解”，打开“规划求解参数”对话框，其中在“设置目标单元格”编辑框内选择H14单元格，然后在下方选择“最小值”选项。“可变单元格”编辑框内选择B9:F13单元格区域。步骤4再单击对话框中的“添加”按钮打开“添加约束”对话框进行约束条件的添加，本例中所包含的约束条件包括：3条件1：B9:F13为二进制数条件2：G9:G13=1条件3：B14:F14=1条件1中将可变单元格B9:F13的约束条件设置为二进制数，可使得其取值在0~1之间变化。要将目标约束为二进制数，可在“添加约束”对话框中间的条件下拉列表框中选择“bin”。各条件添加完成后单击“添加约束”对话框的“确定”按钮返回“规划求解参数”对话框，显示如图1-3所示：图1-3设置规划求解参数步骤5单击“规划求解参数”对话框的“求解”按钮开始求解运算过程，并显示找到结果，单击“规划求解结果”对话框的“确定”按钮可保存此结果，显示如图1-4所示：图1-4任务分配规划求解结果规划结果显示，5位程序员分别依次对应开发模块4、模块1、模块3、模块2、模块5时，整个5人团队可以达到最高的工作效率，完成所有任务的时间为70个单位。这种模式不考虑某位程序员在完成本人任务后继续参与其他任务的情况。本案例中，待分配任务的数量和可用于完成任务的人员数量均为5个，即保持了一种等额的平衡状态。还有许多时候，任务数量和可供完成任务的人员或设备数量并不相等，存在着任务超额，或人员、设备超员的可能。当双方数量不相等时，需要调整规划求解的模型，使双方能够保持“模拟等额”的状态，具体方法可继续阅读下文。4超额任务分配当任务数量大于可供完成任务的人员或设备时，可称之为超额任务。对于此类任务分配问题，可在规划求解模型中新增一个虚拟的人员或设备，参与到当前任务的分配过程中来。而实际上，这个新增的虚拟人员或设备并非当前就可提供，因此在计算开销时暂不需要考虑这部分时间或费用。同样以《等额任务分配》中的案例为例，程序开发任务增加到6个模块，原有的开发人员和时间估算保持不变，新增任务的时间估算具体如图1-5中G列所示：图1-5完成模块开发任务所需时间使用Excel规划求解工具进行统筹安排的具体方法如下：步骤1根据题目需求，在原有题目条件的下方建立规划求解所需的公式模型，如图1-6所示：图1-6建立规划求解的模型与图1-2进行比较可以发现，这里建立的模型除了新增模块6的数据单元格外，还在第14行新增了一个“虚拟程序员”，共同参与任务分配工作，其他内容基本保持不变。其中，B9:G14单元格区域用于记录实际的任务分配情况，可用数字0表示任务未分配，用数字1表示分配此任务。此区域将作为规划求解的可变单元格区域。H列用于统计各程序员最终的任务分配情况，根据任务分配问题的特性，每个程序员最终只能分配到1个任务。在H9单元格内输入公式“=SUM(B9:G9)”，然后向下复制填充至H14单元5格。第15行用于统计各模块开发任务的最终人员安排情况，根据任务分配问题的特性，每个模块开发任务最终只能安排给1个程序员。在B15单元格内输入公式“=SUM(B9:B14)”，然后向右复制填充至G15单元格。I列用于统计实际任务分配状态下，各程序员完成对应模块所需的时间，可在I9单元格内输入公式“=SUMPRODUCT(B2:G2,B9:G9)”，然后向下复制填充至I13单元格。由于无需考虑虚拟程序员的时间开销，因此I14单元格不需要设置内容。I15单元格用于累计I9:I13单元格中的时间，即完成所有5个模块开发任务所需的总时间，可在单元格中输入公式“=SUM(I9:I13)”，此单元格将作为规划求解的目标单元格。步骤2为了提高规划求解结果的可读性，可预先将B9:G14单元格区域的数字格式自定义为“0”。具体操作方法可参见前文《等额任务分配》步骤2。步骤3选中I15单元格，单击菜单“工具”→“规划求解”，打开“规划求解参数”对话框，其中在“设置目标单元格”编辑框内选择I15单元格，然后在下方选择“最小值”选项。“可变单元格”编辑框内选择B9:G14单元格区域。步骤4再单击对话框中的“添加”按钮打开“添加约束”对话框进行约束条件的添加，本例中所包含的约束条件包括：条件1：B9:G14为二进制数条件2：H9:H14=1条件3：B15:G15=1条件1中将可变单元格B9:G14的约束条件设置为二进制数，可使得其取值在0~1之间变化。要将目标约束为二进制数，可在“添加约束”对话框中间的条件下拉列表框中选择“bin”。各条件添加完成后单击“添加约束”对话框的“确定”按钮返回“规划求解参数”对话框，显示如图1-7所示：图1-7设置规划求解参数步骤5单击“规划求解参数”对话框的“求解”按钮开始求解运算过程，并显示找到结果，单击“规划求解结果”对话框的“确定”按钮可保存此结果，显示如图1-8所示：6图1-8超额任务分配求解结果规划求解结果显示，在增加任务后，通过统筹规划，可先安排5位程序员负责模块1~4以及模块6的开发任务，余下模块5的任务暂时保留，待到有其他人力资源时再作安排。目前5个模块的开发任务总共开销只需67个单位时间，已达到最大利用现有人力资源的水平。超员任务分配当可用于任务的人员和设备超过现有需要分配的任务数量时，可称之为人员或设备超员。对于此类任务分配问题，可在规划求解模型中新增一个虚拟任务，同时作为当前需要分配的目标。而实际上，这个新增的虚拟任务并不需要任何开销，因此在计算开销时暂不需要考虑这部分时间或费用。同样以前文《等额任务分配》中的案例为例，程序开发任务仍为5个程序模块，而新增一个程序员，原有的开发人员和时间估算保持不变，新增程序员完成各个模块的估算时间具体如图1-9中第7行所示：图1-9完成模块开发任务所需时间使用Excel规划求解工具进行统筹安排的具体方法如下：步骤1根据题目需求，在原有题目条件的下方建立规划求解所需的公式模型，如图1-10所示：7图1-10建立规划求解模型与图1-2进行比较可以发现，这里建立的模型除了新增程序员6的数据单元格外，还在G列新增了一个“虚拟任务”，共同参与任务分配工作，其他内容基本保持不变。其中，B10:G15单元格区域用于记录实际的任务分配情况，可用数字0表示任务未分配，用数字1表示分配此任务。此区域将作为规划求解的可变单元格区域。H列用于统计各程序员最终的任务分配情况，根据任务分配问题的特性，每个程序员最终只能分配到1个任务。在H10单元格内输入公式“=SUM(B10:G10)”，然后向下复制填充至H15单元格。第16行用于统计各模块开发任务的最终人员安排情况，根据任务分配问题的特性，每个模块开发任务最终只能安排给1个程序员。在B16单元格内输入公式“=SUM(B10:B15)”，然后向右复制填充至G16单元格。I列用于统计实际任务分配状态下，各程序员完成对应模块所需的时间，可在I10单元格内输入公式“=SUMPRODUCT(B2:F2,B10:F10)”，然后向下复制填充至I15单元格。由于无需考虑虚拟任务的时间开销，因此这里不包含虚拟任务的时间。I16单元格用于累计I10:I15单元格中的时间，即完成所有5个模块开发任务所需的总时间，可在单元格中输入公式“=SUM(I10:I15)”，此单元格将作为规划求解的目标单元格。步骤2为了提高规划求解结果的可读性，可预先将B10:G15单元格区域的数字格式自定义为“0”。具体操作方法可参见前文《等额任务分配》步骤2。步骤3选中I16单元格，单击菜单“工具”→“规划求解”，打开“规划求解参数”对话框，其中在“设置目标单元格”编辑框内选择I16单元格，然后在下方选择“最小值”选项。“可变单元格”编辑框内选择B10:G15单元格区域。步骤4再单击对话框中的“添加”按钮打开“添加约束”对话框进行约束条件的添加，本例中所包含的约束条件包括：条件1：B10:G15为二进制数条件2：H10:H15=18条件3：B16:G16=1条件1中将可变单元格B10:G15的约束条件设置为二进制数，可使得其取值在0~1之间变化。要将目标约束为二进制数，可在“添加约束”对话框中间的条件下拉列表框中选择“bin”。各条件添加完成后单击“添加约束”对话框的“确定”按钮返回“规划求解参数”对话框，显示如图1-11所示：图1-11设置规划求解参数步骤5单击“规划求解参数”对话框的“求解”按钮开始求解运算过程，并显示找到结果，单击“规划求解结果”对话框的“确定”按钮可保存此结果，显示如图1-12所示：图1-12超员情况下的任务分配结果规划求解结果显示，虚拟任务分配给了第5个