【重要例子！】用Excel仿真---运营与供应管理（20）

Hi，朋友们，我们又见面了。阅兵场面是不是有点想流泪的感觉？感觉祖国的强大才能给我们民众带来应有的尊严哦。

我们继续EXCEL仿真的话题。

如果想要我们的结果更加有可视化，我们可以采用EXCEL中的VBA编程来使得更加清晰。

如何打开VBA呢？我们只要在EXCEL中的文件-》选项-》自定义功能区增加开发工具就可以了。这样我们可以在EXCEL的Tab上面看到VBA了。如下图：

有了这个，我们就可以开始在VB中编程了。

我们结合一个小例子开始我们的建模仿真。

例：我们观察20分钟的餐馆。有5位顾客在0-10分钟的阶段按照随机的时间进入餐馆，假设顾客一进入餐馆就能立即获得服务，餐馆的服务时间是一个正态分布函数，平均值是10分钟，标准差是0.5分钟。问在这20分钟内，餐馆的顾客数量变化是怎样的？需要多少张座位就能满足90%情况下的需求？

想想看，如果没有仿真，我们如何下手？可能直接投降吧，但是有了仿真我们就不虚！

我们一步一步来建立模型：

1、我们首先要使用上期讲的随机函数生成这5个顾客进入店面的时间。分别显示在我们的第一列，同时用上期讲的生成正态分布函数 + 进入店面时间 = 离开店面时间，显示在第二列，代码如下：

For i = 1 To 5

Cells(i, 1).Value = Rnd() * 10

Cells(i, 2).Value = Application.WorksheetFunction.NormInv(Rnd(), 10, 1) + Cells(i, 1).Value

Next

结果：

2、接着，我们就可以用这两个时间来划线，起点是顾客进入店面时间，终点是顾客离开店面时间。VBA中划线函数是AddLine大家可以琢磨下，具体代码是：

ActiveSheet.Shapes.AddLine(startpoint + Val(Cells(i, 1)) * ColumnWidth, (i - 0.5) * hg, startpoint + Val(Cells(i, 2)) * ColumnWidth, (i - 0.5) * hg).Select

结果：

如果用一条竖线划下来，与横线的交叉点的个数就是那个时刻餐馆内顾客的数量（也就是需要的椅子的个数）如下图：

这样我们可以看出来在3-4分钟的时候3个顾客在餐馆。10分钟这个整点，貌似有5个人。

3、统计每个时间点顾客人数。

像上面这样用肉眼去观测，很不合理。所以我们得弄一个统计的东西。我们每6秒（就是我们仿真中需要的Time Increment）统计一次，看看有多少个顾客在餐馆中。

代码为：

For j = 1 To 200

inventorynumber = 0

For i = 1 To 5

If Val(Cells(i, 1)) <= j / 10 Then inventorynumber = inventorynumber + 1

If Val(Cells(i, 2)) <= j / 10 Then inventorynumber = inventorynumber - 1

Next

ActiveSheet.Shapes.AddLine(startpoint + j / 10 * ColumnWidth, (inventorynumber + 7) * hg, startpoint + j / 10 * ColumnWidth, (inventory + 7) * hg).Select

Next

结果是：（在该时间点上蓝色区域占几格，那么该时间点餐馆内就有几名顾客）

这样看起来更加方便了！看来这次仿真运行中只有大约10分钟整的时候才有5个人呢！

4、但是只运行一次很不合理，我们需要多运行几次。

结果二：

结果三：

哦，看上去结果很不一样呢！

5、运行到这里，小伙伴们是不是知道接下去应该怎么做，去算座位需求个数啦？如果服务时间是方差2分钟呢？

这个是模块中的所有代码，小伙伴们可以拷贝到自己的EXCEL中，调整一下里面的参数，试试看有什么不同的结果哦！9月的《酱油侃IE》开课啦，9月我们将讨论1、计时计件薪酬。2、精益薪酬。3、OEE计算。原文链接是报名地址哦~

Sub drawline()

For Each s In ActiveSheet.Shapes

s.Delete

Next

hg = ActiveSheet.Rows("1:1").Height

ColumnWidth = ActiveSheet.Columns("C:C").Width

startpoint = ActiveSheet.Columns("A:B").Width

For i = 1 To 5

Cells(i, 1).Value = Rnd() * 10

Cells(i, 2).Value = Application.WorksheetFunction.NormInv(Rnd(), 10, 1) + Cells(i, 1).Value

ActiveSheet.Shapes.AddLine(startpoint + Val(Cells(i, 1)) * ColumnWidth, (i - 0.5) * hg, startpoint + Val(Cells(i, 2)) * ColumnWidth, (i - 0.5) * hg).Select

Next

For j = 1 To 200

inventorynumber = 0

For i = 1 To 5

If Val(Cells(i, 1)) <= j / 10 Then inventorynumber = inventorynumber + 1

If Val(Cells(i, 2)) <= j / 10 Then inventorynumber = inventorynumber - 1

Next

ActiveSheet.Shapes.AddLine(startpoint + j / 10 * ColumnWidth, (inventorynumber + 7) * hg, startpoint + j / 10 * ColumnWidth, (inventory + 7) * hg).Select

Next

End Sub