论文部分内容阅读
摘要：电子表格Excel具有强大的计算功能，它提供了一种描述问题、建立模型、处理数据与求解的有效工具。通过汽车维修设备投资中的一个实例，说明它在计算机仿真决策中的应用。
关键词：计算机仿真；数学建模；设备投资；Excel
中图分类号：TP391文献标识码：A文章编号：1009-3044(2007)06-11678-03
现实生活中的大多数系统的动态行为都十分复杂，涉及许多不确定或未知的变化因素，很难甚至无法运用数学上的解析方法对其进行建模或求解。计算机仿真就是利用计算机模型来模仿某个复杂系统的动态行为，对其行为变化进行重复的模拟实验和动态演示，评价或预测它的行为效果，为决策提供依据。
本文通过某个汽车维修设备投资实例，在电子表格Excel上建立仿真模型，寻求最佳投资策略。
1 设备投资问题的提出
企业为了适应已经增长的市场需求，计划追加投资购置生产设备，用以扩大生产能力。在市场经济环境下，为了追求投资收益的最大化，必须对投资方案做可行性论证。下面举一个例子。
某小型汽车维修企业，其某项维修作业（例如钣金作业）现有一个维修工位，已经不能满足客户需求。常常发生有些客户因来不及等待，转到其他维修企业的情形。现在计划追加投资新增该作业的一个维修工位，要求对该计划做可行性分析。给定的已知条件是：
(1)依据排队论[1]，可假设相继两辆需要进行该项维修作业的故障车到达维修厂的间隔时间X是一个服从负指数分布的随机变量，其密度函数和分布函数分别为：
f(x)=λe-λx, x≥0
F(x)=1-e-λx , x≥0
其中，1/λ为平均到达间隔时间，根据对该企业历史数据的统计，λ=0.4（单位：1/小时）；
(2)故障车的损坏程度有轻重，对它的维修时间Y也是一个随机变量。根据对该企业历史数据的统计，Y服从期望值为3小时，标准偏差为1.0小时的正态分布；
(3)故障车到厂后，按照先到先修的原则进行。若维修工位有空，立即开始维修。若在前面已有一辆故障车待修，则不愿等待而离去；
(4)维修纯收益为25元/每小时；
(5)投资一个维修工位需要140，000元；
(6)企业采用单班工作制，每班八小时，每月工作时间按240小时计算；
(7)企业希望投资回收期为5年。
2 仿真模型的流程图
按照不添置新工位（一个维修工位）和添置新工位（二个维修工位）两种方案进行仿真分析。图1、图2分别表示方案一、方案二的仿真模型运行流程图。
图1 一个维修工位时的仿真流程图
3 和本例有关的Excel函数
(1)NORMINV(probability,mean,standard_dev)
返回指定平均值和标准偏差的正态累积分布函数的反函数。
Probability：正态分布的概率值；
Mean：分布的算术平均值；
Standard_dev：分布的标准偏差。
(2)RAND()
返回大于等于 0 及小于 1 的均匀分布随机数。
图2 二个维修工位时的仿真流程图
(3)IF(logical_test,value_if_true, value_if_false)
执行真假值判断，根据逻辑计算的真假值，返回不同结果。
Logical_test：表示计算结果为 TRUE 或 FALSE 的任意值或表达式；
Value_if_true：logical_test 为 TRUE 时返回的值；
Value_if_false：logical_test 为 FALSE 时返回的值。
(4)OR(logical1,logical2,...)
在其参数组中，任何一个参数逻辑值为 TRUE，即返回 TRUE；任何一个参数的逻辑值为 FALSE，即返回 FALSE。
Logical1,logical2,...：为需要进行检验的 1～30 个条件值，分别为 TRUE 或 FALSE。
(5)AND(logical1,logical2, ...)
所有参数的逻辑值为真时，返回 TRUE；只要一个参数的逻辑值为假，即返回 FALSE。
Logical1, logical2, ...：表示待检测的 1 ～ 30 个条件值，分别为 TRUE 或 FALSE。
(6)LN(number)
返回一个数的自然对数。
Number：是用于计算其自然对数的正实数。
(7)MAX(number1,number2,...)
返回一组值中的最大值。
Number1,number2,...：是要从中找出最大值的 1～30 个数字参数。
(8)MIN(number1,number2,...)
返回一组值中的最小值。
Number1,number2,...：是要从中找出最小值的 1～30 个数字参数。
根据概率论知识，已知定理：如果随机变量X的分布函数F（x）单调连续，则Y=F（X）确定一个新的随机变量，它服从于（0，1）上的均匀分布。[2]利用这个定理，先产生（0，1）上的均匀分布的随机数Y，则X= F-1（Y）就是一个服从F（X）的随机变量。[3]
根据负指数分布的分布函数，可求出其反函数为：
X=-1/λ×ln(1-Y)
∵1-Y也是（0，1）上的均匀分布的随机变量
令 U=1-Y
∴X=-1/λ×lnU是服从负指数分布的随机变量。
因此，Excel中的随机变量-2.5*LN(RAND())服从 =1/2.5=0.4的负指数分布，本例中作为两辆车到达的间隔时间。
同样，Excel中的随机变量NORMINV(RAND(),3,1)服从期望值为3（小时），标准偏差为1（小时）的正态分布。本例中作为故障车修复所需的随机时间。
4 Excel表上建模
4.1 一个维修工位的表上建模过程
表1是Excel表格中的一部分，用它模拟只有一个维修工位时，维修企业处理故障车的动态过程。
表1 一个维修工位时的表上仿真模型
2行（事件序列0）是初始化状态，绝对时间置零。绝对时间是指连续的维修厂开工时间，如第一天开工时间为零时，下班时间为8时，第二天开工时间为8时，以此类推。以下各种时间与此相同。
3行放置第一台（事件序列为1）车到达维修厂的动态演示。
间隔时间B3=-2.5*LN(RAND())，表示距前车到达维修厂的随机时间间隔；
时间轴一C3=B3+C2，故障车到达维修厂的绝对时间；
等待一D3=MAX(I2-C3,0)，距前第一辆车出厂的等待时间；
等待二E3 =MAX(J2-C3,0)，距前第二辆车出厂的等待时间；
维修否F3 =IF(E3>0,0,1)，若前第二辆车未出厂，则离去（F3=0）；否则加入待修车行列（F3=1）；
累计离去G3=G2+(1-F3)；
维修时间H3=F3*NORMINV(RAND(),3,1)，修复所需的随机时间；
时间轴二I3 =F3*(C3+D3+H3)+(1-F3)*I2，故障车被修复后，离开维修厂的绝对时间；
时间轴三J3 =IF(F3=1,I2,J2)，将前一辆车出厂的绝对时间移至本事件序列；
累计维修时间K3=K2+H3；
累计收益L3=K3*25。
然后，选中A3：L3区域，拖动填充柄将该区域中的公式往下复制，直至结束。复制的过程就是仿真系统模仿真实系统的动态演示过程。
4.2 维修方案二的表上建模过程
表2是Excel表格中的一部分，用它模拟有二个维修工位时，维修企业处理故障车的动态过程。
表2 二个维修工位时的表上仿真模型
2行（事件序列0）是初始化状态。
3行放置第一台（事件序列为1）车到达维修厂的动态演示。
间隔时间B3=-2.5*LN(RAND())，表示距前车到达维修厂的随机时间间隔；
时间轴一C3=B3+C2，故障车到达维修厂的绝对时间；
等待一D3=IF(OR(I2-C3<0,J2-C3<0),0,MIN(I2-C3, J2-C3))，若有空闲工位，则立即被维修，否则，计算待修时间；
等待二E3 =MAX(K2-C3,0)，距前第三辆车出厂的等待时间；
维修否F3= =IF(AND(D3>0,E3>0),0,1)，若有三辆故障车在维修厂，则离去（F3=0），否则加入待修车行列（F3=1）；
累计离去G3=H2+(1-F3)；
维修时间H3=F3*NORMINV(RAND(),3,1)，修复所需的随机时间；
时间轴二I3 =F3*(C3+D3+H3)+(1-F3)*I2，若此故障车要维修，则是此车被修复后离开维修厂的绝对时间，或者此车不维修而离去，则是前一辆车的离厂时间；
时间轴三J3 =IF(F3=1,MAX(I2,J2),J2)，将前一辆车出厂的绝对时间移至本事件序列；
时间轴四K3=IF(F3=1,MIN(J2,I2),K2)，将前第二辆车出厂的绝对时间移至本事件序列；
累计维修时间L3=L2+H3；
累计收益M3=L3*25。
然后，选中A3：M3区域，拖动填充柄将该区域中的公式往下复制，直至结束。
5 仿真结果分析
选择仿真演示的周期为一年，即240×12=2880个工作小时。按已知条件的各参数运行，修理厂某作业只有一个维修工位时，共维修故障车755辆，有381辆车不愿等待而离去，累计收益为56433.4元；有二个维修工位时，共维修故障车1044辆，有141辆车不愿等待而离去，累计收益为78539.09元。两者相差22105.69元，五年就会相差11万元左右，不能回收投资14万元。
显然，仿真系统运行效果决定于条件中的各参数。 值越大（故障车平均到达间隔时间 越小）或Y的期望值越大（平均维修时间越长），则越有利于做出新增设备的决策。Y的标准偏差对运行效果影响很小，例如将条件中的标准偏差由1.0改为0.5，其它参数不变，则运行数据如下：只有一个维修工位时，共维修故障车732辆，有320辆车不愿等待而离去，累计收益为54914元；有二个维修工位时，共维修故障车1123辆，有132辆车不愿等待而离去，累计收益为77527元。与上面一段的数据无显著差异。
6 补充说明
(1)本例中的数学模型是一个随机模型，在参数不变的情况下，每一次仿真演示的效果会有差异，但差异很小。
(2)设备投资问题有多种类型，一般比较复杂，不一定都适合用Excel解决。但Excel简单易学、功能强大、便于跟踪，有利于程序调试和模型修改，应充分开拓它的应用范围。
参考文献：
[1]吴祈宗.主编.运筹学[M].机械工业出版社,2003.2:157.
[2]复旦大学.编.概率论（第1册）[M].人民教育出版社,1979.4:148-149.
[3]周义仓,赫孝良.数学建模实验[M].西安交通大学出版社,2002.7:324-325.
本文中所涉及到的图表、注解、公式等内容请以PDF格式阅读原文。
关键词：计算机仿真；数学建模；设备投资；Excel
中图分类号：TP391文献标识码：A文章编号：1009-3044(2007)06-11678-03
现实生活中的大多数系统的动态行为都十分复杂，涉及许多不确定或未知的变化因素，很难甚至无法运用数学上的解析方法对其进行建模或求解。计算机仿真就是利用计算机模型来模仿某个复杂系统的动态行为，对其行为变化进行重复的模拟实验和动态演示，评价或预测它的行为效果，为决策提供依据。
本文通过某个汽车维修设备投资实例，在电子表格Excel上建立仿真模型，寻求最佳投资策略。
1 设备投资问题的提出
企业为了适应已经增长的市场需求，计划追加投资购置生产设备，用以扩大生产能力。在市场经济环境下，为了追求投资收益的最大化，必须对投资方案做可行性论证。下面举一个例子。
某小型汽车维修企业，其某项维修作业（例如钣金作业）现有一个维修工位，已经不能满足客户需求。常常发生有些客户因来不及等待，转到其他维修企业的情形。现在计划追加投资新增该作业的一个维修工位，要求对该计划做可行性分析。给定的已知条件是：
(1)依据排队论[1]，可假设相继两辆需要进行该项维修作业的故障车到达维修厂的间隔时间X是一个服从负指数分布的随机变量，其密度函数和分布函数分别为：
f(x)=λe-λx, x≥0
F(x)=1-e-λx , x≥0
其中，1/λ为平均到达间隔时间，根据对该企业历史数据的统计，λ=0.4（单位：1/小时）；
(2)故障车的损坏程度有轻重，对它的维修时间Y也是一个随机变量。根据对该企业历史数据的统计，Y服从期望值为3小时，标准偏差为1.0小时的正态分布；
(3)故障车到厂后，按照先到先修的原则进行。若维修工位有空，立即开始维修。若在前面已有一辆故障车待修，则不愿等待而离去；
(4)维修纯收益为25元/每小时；
(5)投资一个维修工位需要140，000元；
(6)企业采用单班工作制，每班八小时，每月工作时间按240小时计算；
(7)企业希望投资回收期为5年。
2 仿真模型的流程图
按照不添置新工位（一个维修工位）和添置新工位（二个维修工位）两种方案进行仿真分析。图1、图2分别表示方案一、方案二的仿真模型运行流程图。
图1 一个维修工位时的仿真流程图
3 和本例有关的Excel函数
(1)NORMINV(probability,mean,standard_dev)
返回指定平均值和标准偏差的正态累积分布函数的反函数。
Probability：正态分布的概率值；
Mean：分布的算术平均值；
Standard_dev：分布的标准偏差。
(2)RAND()
返回大于等于 0 及小于 1 的均匀分布随机数。
图2 二个维修工位时的仿真流程图
(3)IF(logical_test,value_if_true, value_if_false)
执行真假值判断，根据逻辑计算的真假值，返回不同结果。
Logical_test：表示计算结果为 TRUE 或 FALSE 的任意值或表达式；
Value_if_true：logical_test 为 TRUE 时返回的值；
Value_if_false：logical_test 为 FALSE 时返回的值。
(4)OR(logical1,logical2,...)
在其参数组中，任何一个参数逻辑值为 TRUE，即返回 TRUE；任何一个参数的逻辑值为 FALSE，即返回 FALSE。
Logical1,logical2,...：为需要进行检验的 1～30 个条件值，分别为 TRUE 或 FALSE。
(5)AND(logical1,logical2, ...)
所有参数的逻辑值为真时，返回 TRUE；只要一个参数的逻辑值为假，即返回 FALSE。
Logical1, logical2, ...：表示待检测的 1 ～ 30 个条件值，分别为 TRUE 或 FALSE。
(6)LN(number)
返回一个数的自然对数。
Number：是用于计算其自然对数的正实数。
(7)MAX(number1,number2,...)
返回一组值中的最大值。
Number1,number2,...：是要从中找出最大值的 1～30 个数字参数。
(8)MIN(number1,number2,...)
返回一组值中的最小值。
Number1,number2,...：是要从中找出最小值的 1～30 个数字参数。
根据概率论知识，已知定理：如果随机变量X的分布函数F（x）单调连续，则Y=F（X）确定一个新的随机变量，它服从于（0，1）上的均匀分布。[2]利用这个定理，先产生（0，1）上的均匀分布的随机数Y，则X= F-1（Y）就是一个服从F（X）的随机变量。[3]
根据负指数分布的分布函数，可求出其反函数为：
X=-1/λ×ln(1-Y)
∵1-Y也是（0，1）上的均匀分布的随机变量
令 U=1-Y
∴X=-1/λ×lnU是服从负指数分布的随机变量。
因此，Excel中的随机变量-2.5*LN(RAND())服从 =1/2.5=0.4的负指数分布，本例中作为两辆车到达的间隔时间。
同样，Excel中的随机变量NORMINV(RAND(),3,1)服从期望值为3（小时），标准偏差为1（小时）的正态分布。本例中作为故障车修复所需的随机时间。
4 Excel表上建模
4.1 一个维修工位的表上建模过程
表1是Excel表格中的一部分，用它模拟只有一个维修工位时，维修企业处理故障车的动态过程。
表1 一个维修工位时的表上仿真模型
2行（事件序列0）是初始化状态，绝对时间置零。绝对时间是指连续的维修厂开工时间，如第一天开工时间为零时，下班时间为8时，第二天开工时间为8时，以此类推。以下各种时间与此相同。
3行放置第一台（事件序列为1）车到达维修厂的动态演示。
间隔时间B3=-2.5*LN(RAND())，表示距前车到达维修厂的随机时间间隔；
时间轴一C3=B3+C2，故障车到达维修厂的绝对时间；
等待一D3=MAX(I2-C3,0)，距前第一辆车出厂的等待时间；
等待二E3 =MAX(J2-C3,0)，距前第二辆车出厂的等待时间；
维修否F3 =IF(E3>0,0,1)，若前第二辆车未出厂，则离去（F3=0）；否则加入待修车行列（F3=1）；
累计离去G3=G2+(1-F3)；
维修时间H3=F3*NORMINV(RAND(),3,1)，修复所需的随机时间；
时间轴二I3 =F3*(C3+D3+H3)+(1-F3)*I2，故障车被修复后，离开维修厂的绝对时间；
时间轴三J3 =IF(F3=1,I2,J2)，将前一辆车出厂的绝对时间移至本事件序列；
累计维修时间K3=K2+H3；
累计收益L3=K3*25。
然后，选中A3：L3区域，拖动填充柄将该区域中的公式往下复制，直至结束。复制的过程就是仿真系统模仿真实系统的动态演示过程。
4.2 维修方案二的表上建模过程
表2是Excel表格中的一部分，用它模拟有二个维修工位时，维修企业处理故障车的动态过程。
表2 二个维修工位时的表上仿真模型
2行（事件序列0）是初始化状态。
3行放置第一台（事件序列为1）车到达维修厂的动态演示。
间隔时间B3=-2.5*LN(RAND())，表示距前车到达维修厂的随机时间间隔；
时间轴一C3=B3+C2，故障车到达维修厂的绝对时间；
等待一D3=IF(OR(I2-C3<0,J2-C3<0),0,MIN(I2-C3, J2-C3))，若有空闲工位，则立即被维修，否则，计算待修时间；
等待二E3 =MAX(K2-C3,0)，距前第三辆车出厂的等待时间；
维修否F3= =IF(AND(D3>0,E3>0),0,1)，若有三辆故障车在维修厂，则离去（F3=0），否则加入待修车行列（F3=1）；
累计离去G3=H2+(1-F3)；
维修时间H3=F3*NORMINV(RAND(),3,1)，修复所需的随机时间；
时间轴二I3 =F3*(C3+D3+H3)+(1-F3)*I2，若此故障车要维修，则是此车被修复后离开维修厂的绝对时间，或者此车不维修而离去，则是前一辆车的离厂时间；
时间轴三J3 =IF(F3=1,MAX(I2,J2),J2)，将前一辆车出厂的绝对时间移至本事件序列；
时间轴四K3=IF(F3=1,MIN(J2,I2),K2)，将前第二辆车出厂的绝对时间移至本事件序列；
累计维修时间L3=L2+H3；
累计收益M3=L3*25。
然后，选中A3：M3区域，拖动填充柄将该区域中的公式往下复制，直至结束。
5 仿真结果分析
选择仿真演示的周期为一年，即240×12=2880个工作小时。按已知条件的各参数运行，修理厂某作业只有一个维修工位时，共维修故障车755辆，有381辆车不愿等待而离去，累计收益为56433.4元；有二个维修工位时，共维修故障车1044辆，有141辆车不愿等待而离去，累计收益为78539.09元。两者相差22105.69元，五年就会相差11万元左右，不能回收投资14万元。
显然，仿真系统运行效果决定于条件中的各参数。 值越大（故障车平均到达间隔时间 越小）或Y的期望值越大（平均维修时间越长），则越有利于做出新增设备的决策。Y的标准偏差对运行效果影响很小，例如将条件中的标准偏差由1.0改为0.5，其它参数不变，则运行数据如下：只有一个维修工位时，共维修故障车732辆，有320辆车不愿等待而离去，累计收益为54914元；有二个维修工位时，共维修故障车1123辆，有132辆车不愿等待而离去，累计收益为77527元。与上面一段的数据无显著差异。
6 补充说明
(1)本例中的数学模型是一个随机模型，在参数不变的情况下，每一次仿真演示的效果会有差异，但差异很小。
(2)设备投资问题有多种类型，一般比较复杂，不一定都适合用Excel解决。但Excel简单易学、功能强大、便于跟踪，有利于程序调试和模型修改，应充分开拓它的应用范围。
参考文献：
[1]吴祈宗.主编.运筹学[M].机械工业出版社,2003.2:157.
[2]复旦大学.编.概率论（第1册）[M].人民教育出版社,1979.4:148-149.
[3]周义仓,赫孝良.数学建模实验[M].西安交通大学出版社,2002.7:324-325.
本文中所涉及到的图表、注解、公式等内容请以PDF格式阅读原文。