《基于Excel的蒙特卡罗模拟方法的实现.pdf》由会员分享，可在线阅读，更多相关《基于Excel的蒙特卡罗模拟方法的实现.pdf（4页珍藏版）》请在金锄头文库上搜索。
1、第12卷 第2期 1999年6月 聊城师院学报(自然科学版) Journal of L iaocheng Teachers U niversity(Nat. Sci) Vol . 12 No. 2 Jun. 1999 基于Excel的蒙特卡罗模拟方法的实现 姜庆华 李国锋 (山东经济学院基础部,济南250014) 摘 要 以M icrosoft Excel电子表格软件为基础,探讨了Excel在开发决策支持 系统方面的功能,在研究给出了M onte Carlo模拟算法之后,通过一案例实现了 基于Excel的M onte Carlo摸拟系统1工作表明, Excel软件在管理决策中具有较 好的应用前
2、景1 关键词 决策支持, Excel电子表格软件,蒙特卡罗模拟 分类号 TP3 蒙特卡罗模拟是利用计算机进行数值计算的一类特殊风格的方法,它是把某一现实 或抽象系统的某种特征或部分状态,用模拟模型的系统来代替或模仿,使所求问题的解正 好是模拟模型的参数或特征量,再通过统计实验,求出模型参数或特征量的估计值,得出 所求问题的近似解1该方法应用面广,适应性强.惠斯通(W eston)对美国1 000家大公司所 作的统计表明:在公司管理决策中,采用随机模拟方法的频率占29%以上,远大于其他数 学方法的使用频率1特别,该方法算法简单,但计算量大,在模拟实际问题时,要求所建模 型必须反复验证1这就离不开
3、计算机技术的帮助,自然可利用任何一门高级语言来实现这 种方法1本文首先探讨Excel电子表格软件在开发决策支持系统方面所具有的功能特点, 给出蒙特卡罗模拟算法之后,通过一案例具体实现了基于Excel的M onte Carlo模拟系 统,由于M icrosoft Excel电子表格软件强大的数据分析功能和友好的界面设计能力,使 系统实现起来颇感轻松自如1本文的努力或许对管理决策自动化有所启示1 1 Excel开发决策系统的支持能力 通常一个决策支持系统一般应包含便于用户与系统进行对话从而使他们在运用自己 的经验、 直觉和判断来解决所面临的半结构化问题时可以得到充分支持的友好界面,又应 包含适合解
4、决用户所面临的决策问题中的结构化部分的决策模型,同时还应包含便于保 存作为分析对象和分析结果的那些数据的数据库及其管理系统1Excel之所以被认为是 一种建立决策支持系统的方便工具,就是因为它在上述三个方面所具有的强大支持能力. 111 Excel具有灵活的建模能力和对所建模型进行W hat2if分析的功能 收稿日期: 1998206220 Excel可以方便地利用公式、 大量内建的财务统计函数、 以及VBA语言建立案例数 值模型,并利用模拟运算表、 方案管理器等功能模型进行 “如果决策变量如此变化的话,目 标变量将如何变化” 的分析1利用单变量求解功能求解 “为了使目标变量达到某个特定的 目
5、标值,决策变量应该如何变化” 的问题1Excel通过其规划求解功能将最优化算法的能 力加入到对工作表数据进行操纵的能力之中,这使得Excel特别适合建立线性规划、 非线 性规划等各种优化模型1 112 Excel可以在其工作表中存贮所分析的数据并具有对数据进行分析管理的能力 Excel数据文件以工作簿作为基本形式,一个工作簿可包含多达255张工作表,一张 工作表是一具有65 536行256列的二维表格,允许用户将数据保存在其工作表中1因此, 用户在Excel数据文件中具有保存数据的更大的灵活性1借助于ODBC(开放数据库联 接)工具,用户可以直接访问各种由dBase、FoxPro、A cces
6、s或者SQL Server等生成的外 部数据库1利用Excel的数据透视表功能,可以十分方便地从大量数据中提取有用的综合 统计信息1 113 Excel敞开的工作表屏幕具有直接的输入、 输出功能,它的图形处理能力提供了友 好的用户界面 Excel充分利用了W indow s图形用户界面的优点,可以让用户利用W indow s与Ex2 cel本身的菜单、 工具条、 对话框等手段在建立与维护决策支持系统时方便地进行操作1还 允许用户定义菜单、 工具条和对话框来建立自己的系统界面1Excel的图形不但质量好而 且效率高1用漂亮的图形来向用户表现决策分析的结果显然会明显提高所建立的决策支 持系统的功能
7、1 总之, Excel在建立模型与模型分析、 数据访问与管理以及用户界面三方面具有强大 功能1在这些功能基础上,借助于VBA宏,我们就能够开发出界面友好美观、 数据存取容 易、 决策分析方便易行的各种决策支持系统来1 2 蒙特卡罗案例、 算法研究及系统实现 案例取材于一个库存控制决策支持系统的示范性试验1泰丰加油站对过往船只销售 汽油1汽油需求依赖于天气条件而按下列分布波动1由于加油站位置偏僻,必须有一固定 库存5 500 gallon,且每周定货一次1经理面临的决策问题是:若定货太小,缺货损失12 Y? gallon;若定货太多,他不得不支付 5 Y?gallon的退货费1每加仑销售利润为1
8、0 Y, 现存货 300 gallon,经理的最优定货量是多少? 问题可以通过一段时间的实际实验求 解,而模拟则可以在几秒钟后给出答案,且 成本很小1为此可以指定方案:定货3 400、 3 500、3 600、3 750、4 000、4 500 gallon等模 拟计算,比较几个方案的利润,求得一个满 意解1 周需求量(gallon)概率分布 2 0000. 12 3 0000. 23 4 0000. 48 5 0000. 17 模拟的一般过程是:调查、 收集数据并设定问题,构造模拟模型,测试模型,构造方案, 模拟实验,结果评价,结果执行1蒙特卡罗模拟特点是依赖于一个特定的概率分布,模拟结 果
9、服从概率规律性1针对案例,构造模拟模型的算法步骤如下 (1) 描述问题并确定一个概率分布1案例中是一个给定的分布; (2) 定义系统目标1案例中的系统目标是周平均利润; 72第2期姜庆华等:基于Excel的蒙特卡罗模拟方法的实现 (3) 计算累积概率并确定目标代表数区间1案例中利用Excel的直接输入功能,输 入步骤1的概率分布,系统利用VBA宏自动计算累积概率和目标代表数区间; (4) 产生随机数,计算系统目标1这里的重点在于要求系统产生两位数的随机数,以 便根据步骤3给出的目标代表数区间确定随机需求1系统目标不仅给出了周平均利润,还 给出了周平均销售量、 周平均缺货量和退货量等; (5)
10、模拟计算过程,统计评价并寻求满意解1 案例模拟计算过程算法如图1.设Ib为期初库存,Ie为期末库存,Im为库存容量,D为 随机需求,S为销售量,B为退货量,U为缺货量,O rs为定货量1则周期望利润= 10S- 12U- 5B1 系统实现有以下几个问题需要考虑1 211 界面的设计 充分利用了Excel在开发决策支持系统方面所具有的功能,利用Excel工作薄文件 中多工作表现功能,将系统界面、 模拟运算表、 灵敏度分析、 模拟方案结果表等各部分各自 放在不同的工作表中;利用了Excel的建模能力、W hat2if分析能力、 控件建立能力和 VBA宏开发能力,在一个宏表中写了一些必要的宏以便对系
11、统进行控制1给出了如下操 作界面: 启动系统后,首先进入系统初始化,要求给出最大库存容量、 初始库存量、 系统状态 数,之后要求给出方案设计、 模拟周期1系统初始化后会生成一个累积概率计算状态表以 及相应的代表随机需求的随机数代表区间1在此基础上,点按一下 “模拟计算” 按钮,系统 自动生成表格并进行模拟计算1值得说明的是,状态表和模拟计算表均是动态,他们随系 统状态个数与模拟周期数而定1 212 动态区域的实现 在Excel中实现动态区域是方便的,利用了Excel的Range对象、Selection对象的 Offset属性、Resize属性和N ame属性及Select方法1这比利用其他程序
12、设计语言实现作 者认为要容易得多1下面是状态表动态区域实现的VBA程序 Range(B12). Select Selection. Offset(0, 0). Resize(RowSize: = SN um ,ColumnSize: = 4). Select Selection. N ame=RepresentsN umbers Range 其中变量SN um存储输入的状态个数1 213 系统目标的统计与评价 考虑到系统决策支持功能,算法设计为由专家设计方案,采用人机交互,这样可以发 挥专家的经验判断;系统实现模拟运算系统对每一个设定的定货方案在给出的模拟 周期内计算出相应的平均利润,记忆模拟
13、结果并与已模拟实现过的方案比较给出较好的 方案选择1算法充分体现了人机交互追求问题的满意解的现代决策思想1案例的最终模拟 结果显示4 100 gallon是最满意的1 我们所开发的是一个示范性系统,但是这一系统的性能充分说明了本文中所指出的 Excel作为一种决策支持系统开发工具或决策支持系统产生软件所具有的功能特点1模 拟方法是管理决策的重要方法之一,本文的工作说明了Excel软件工具在管理决策上的 广阔的应用前景,抛砖引玉,愿同行指教1 82聊 城 师 院 学 报(自 然 科 学版)第12卷 图1案例模拟流程图 参考文献 1 Efraim Turban. Decision support
14、and Expert system s:M anagement support system s,M acm illan Publishing Compa2 ny, 1990 2 EricW ells. M icrosoft Excel510应用开发技术精解1王华译1北京:清华大学出版社, 1995 The Monte Ca rlo S imula tionMethods on Excel Jiang Q inghua L i Guofeng (Shandong Econom ic College,Jinan 250014) Abstract This essay covers them ic
15、rosoft excels functions on decision support sys2 tem s and also, through a case, studies theM onte Carlo simulaiton system s on excel after discussing its algorithm. Therefore, it show s that m icrosoft excel has a better applica2 tion prospect in management decision. Key words Decision support,M icrosoft excel,M onte Carlo simulation 92第2期姜庆华等:基于Excel的蒙特卡罗模拟方法的实现
