Excel进阶- 数据建模与决策

众所周知,Excel是全世界最酷炫的软件之一,除了日常的数据处理,图表分析等基本功能外,你还可以用excel做许多匪夷所思的事情。

比如兴致大好时来一幅山水画:

或者做几个游戏,嗨一把:

又或者,自己制作一部小电影吧:

当然,小马哥作为一个萌新,还停留于用Excel处理数据的初级阶段,估计这辈子都没法达到以上的境界. 所以今天要分享的,还是利用Excel进行数据建模,来处理咱们项目管理里常见的一些场景和问题。

应用场景1:资源配置问题

资源分配问题可以说是项目经理每天都会去处理的细节问题。一句话说,就是派什么人去做什么事,来实现效率或者收益的最大化。咱们举个简单的例子来了解一下,请看案例:

假设咱们“超有爱”开发小组有8个后端程序猿,6个前端程序猿,现在要进行A和B两个类型的优化任务,每个A类型的优化任务需要两个前端和两个后端一起完成,而每个B类型的任务则需要两个后端和一个前端。每完成一件A类型的优化任务预计可以给公司带来20万的收益,而每完成一件B类型的任务可以带来15万的收益。假设完成A,B型任务所需要的时间相同,那请问如何分配任务可以达到收益的最大化呢?

以上的场景看似像个数学题,但确实是很多产品型公司的日常博弈。有限的人手,堆满的创意,除了靠经理们的“拍脑门”和“信仰之跃”大法以外,如何用更科学的办法来进行资源的分配和管理呢?下面就让我们用excel来尝试解决上面的问题吧。

解决问题第一步:载入”线性规划“插件

关于资源分配的博弈问题,大部分都适用于线性规划的数学模型。所以首先,让我们载入Excel的自带插件“线性规划”。点击Excel的“工具” ->“加载项”,勾选“规划求解”,便可以在“数据”模块里面使用它了。

解决问题第二步:建立数据模型

从案例的场景中,我们可以总结出以下一些简单的数据模型:

最大化总收益:MAX(任务A收益*任务A数量+任务B收益*任务B数量)

前端程序猿总数限制:任务A数量*2+任务B数量*1 <= 6

后端程序猿总数限制:任务A数量*2+任务B数量*2 <= 8

并且,任务A完成数量 >= 0, 任务B完成数量 >= 0

完成了以上的数学模型搭建,我们便可以进行下一步:将数据模型录入Excel了。

解决问题第三步:在Excel中导入数据模型

首先,我们输入一些不会变化的常量,并用蓝色来标识这些常量:

接着,我们建立变量单元格,并用黄色标识。在超有爱公司的场景中,变量是“任务A和任务B分别的完成数量”

下一步,我们建立目标函数单元格,并用橙色标识。目标函数也就是我们想要求得的答案,在这个例子里,便是“最大化的总收益

由第二步的数据模型我们已经得出:

总收益=任务A收益*任务A数量+任务B收益*任务B数量

所以带入Excel,便可以得出简单公式:

F8 = B8*B2 + C8*C2

在输入和建立了所有的数据后,我们进行最后一步:建立约束条件。

在这个例子里,我们有两个约束条件,分别是:

前端程序猿总数限制:任务A数量*2+任务B数量*1 <= 6

后端程序猿总数限制:任务A数量*2+任务B数量*2 <= 8

转化为excel语言,便是:

前端程序猿总数限制:B5*B8 + C5*C8 = D5 <= F5

后端程序猿总数限制:B6*B8 + C6*C8 = D6 <= F6

解决问题第四步:数据模型求解与决策

我们在完成了数据模型的excel录入后,接下来便是最后一步:规划求解了

点击excel”数据“模块右上角的“规划求解”,输入以下数据:

  1. 输入目标函数
  2. 选择求值类型 (最大值,最小值或目标值,本例为最大值)
  3. 输入自变量
  4. 输入约束条件
  5. 选择求解方法 (选择单纯线性规划)
  6. 点击”求解“

这样,我们便得到了这个例子的最优解,也就是超有爱公司的最佳资源配置方案:

 

好啦,这样我们就成功的利用Excel来实现了数据建模和决策的过程啦!:)

看到这里,你可能会说:这不就是简单的二元一次方程么?我为什么要搞这么复杂,明明笔算一分钟就搞定了呀!确实,”超有爱“公司的例子是很简单直观,并不需要如此大费周章。但是,在我们的日常管理工作中,往往会遇到比这复杂10倍,甚至一百倍的案例,这个时候,数据建模求解便体现出了它独特的优势。 比如当解决一个复杂的快递公司运输路线优化的问题时,我们可能会建立如下复杂模型:

怎么样,是不是有一种不明觉厉的感觉?小马哥想要强调的是,像上图这样的复杂运算,是几乎不可能通过笔算来进行最优解求解的,这个时候如果能运用excel进行适当的建模和求解,可以得到事半功倍的效果。

应用场景2:用PERT进行项目管理

学过PMP的小伙伴们应该非常熟悉,PERT是一种很常用的活动计划和估算方法,它大概有以下一些基本步骤:

  1. 定义活动,明确活动顺序,构建PERT网络图
  2. 估算每个活动,提供三种角度的估算“乐观估计”, “悲观估计”和“最有可能估计”,并进行加权分析
  3. 找到关键路径,对项目进行有效的资源配置、优化和预测

聪明的你一定已经发现,咱们的Excel可以在第二步和第三步上施展拳脚,为项目经理们的计划和运算提供便利。下面,就让小马哥用一个简单的例子给大家讲一讲Excel在PERT中的具体应用。

假设我们现在要盖一栋“小马哥”科技大厦,我们定义出了活动A~N,项目的PERT图也已经有了大概的雏形如下:

与此同时,各个活动的负责人们又向我们汇报了各活动的预估时间:(o表示乐观估计,m表示最有可能估计,p表示悲观估计)

在拿到了这些数据之后,小马哥便可以开始进行PERT的第二步,估算活动的加权分析了。于是,小马哥利用Excel建立了以下这个简单模型:

通过录入PERT规定的一些简单公式到Excel,小马哥可以快速的计算出每个活动的加权平均估算值 (μ),方差(σ2),并结合流程图定义出关键路径,算出项目完工的总时间和完工概率 (p)等等。

在关键路径明晰之后,小马哥可以继续利用Excel计算出每个活动的最早开始(ES), 最早结束(EF),最晚开始(LS)和最晚结束(LF),并得出活动时差:

最后,结合着excel算出的这些数据,小马哥可以将结果再反映到PERT图里,完成一个完整的项目网络的制作:

因为有Excel强大的数据自动处理能力的加成,一个复杂的项目计划被转化为了单纯的数据导入和导出的过程。这样一来,一方面为项目经理们节省了许多的计算精力和时间,另一方面又让结果更加的准确。

 

关于数据建模,还有许许多多的应用场景,比如运输问题,网络优化问题,预测问题,成本管控问题等等等等。希望大家通过这篇文章能够对数据建模有一个初步的理解和认可,并以后更多的去尝试使用这样更加科学的管理方式去对待你的项目。

 

 

 

 

 

发表评论

电子邮件地址不会被公开。 必填项已用*标注