Spreadsheet建模

1 2 3 4 5 6 7 8
0.125 0.125 0.125 0.125 0.125 0.125 0.125 0.125
IE
26
§2. Spreadsheet建模
表2-2 服务时间分布 服务时间（分钟） 概率 1 2 3 4 5 6 0.10 0.20 0.30 0.25 0.10 0.05 累积概率 0.10 0.30 0.60 0.85 0.95 1.00
Simulation
Spreadsheet建模
§1. Excel基础
§2. Spreadsheet建模
§3. Spreadsheet建模实例 小结 习题
IE
§1. Excel基础
Simulation
公式与函数：公式是对工作表中的值进行计算的等式，以“=”等号 开头。=A3+E5，=sin() ；公式由单元格、函数、常量、运算符四个 部分组成，或全部包含或部分包含。 Excel内置函数：200多个，唯一的名称；引用时“=函数名（）” 函数导向按钮
数据表作为一个函数，单从函数的意义看虽然可以有多个自变量，但由 于数据表格只是行和列2维的，即不同的输入只能通过行、列来实现 ，因此数据表可控制或调节的自变量最多只有2个，也就是说只有1维 和2维数据表。
IE 8
§1. Excel基础
Simulation
1、一维数据表：求解投资组合的标准差和期望收益率曲线，原始数据如 下表
例如：有随机变量x1,x2,…,x7,每个变量的样本量为5个，自x2起，xi6的
取值按以下规则：若前一组变量的5个样本均值大于30，则xi6为本组样 本均值；若前一组变量的5个样本均值小于30而大于25，则xi6为前一 组均值；否则为0。 为此，只需要创建符合题意的公式即可完成求解。
=if(…)
IE
29
§2. Spreadsheet建模
Simulation
RANDBETWEEN 函数：返回位于指定的两个数之间的一个随机 整数。每次计算工作表时都将返回一个新的随机整数。 语法 ：RANDBETWEEN(bottom,top) Bottom 函数 RANDBETWEEN 将返回的最小整数，Top RANDBETWEEN 将返回的最大整数。 函数
IE 21
§1. Excel基础
Simulation
宏的用途是使常用任务自动化。虽然有些宏仅仅是记录键击或鼠标单 击操作，但开发人员可以使用代码编写功能更强大的 VBA 宏，这些 宏可以在计算机上运行多条命令。因此，VBA 宏会引起潜在的安全风 险。黑客可以通过某个文档引入恶意宏，一旦打开该文档，这个恶意 宏就会运行，并且可能在计算机上传播病毒 （病毒:一种计算机程序 或宏，通过在计算机文件中插入自身的副本而“感染”这些文件。感 染文件被装入内存后，病毒还要感染其他文件。病毒通常带有有害的 副作用。）。 VBA （Visual Basic for Applications）:Microsoft Visual Basic 的宏语 言版本，用于编写基于 Microsoft Windows 的应用程序，内置于多个 Microsoft 程序中。它是基于Visual Basic发展而来，它们具有相似的 语言结构。
Cumulative 为一逻辑值，决定函数的形式。如果 cumulative 为 TRUE，函数 NORMDIST 返回累积分布函数；如果为 FALSE，返回概 率密度函数。
IE
15
§1. Excel基础
审核
Simulation
一维数据表：投资组合的
IE
16
§1. Excel基础
Simulation
“{ }”。
例如：把上例数据精确到小数点后3位。
解：利用数组和函数ROUND就可实现。 在目标数据区（A11：G15）的第一个数据栏A11输入
=round(A2:G6,3)
然后，同时按住“Ctrl和Shift + Enter”键，花括号“{ }”就上去了，输入 栏就变成{=round(A2:G6,3)} ，运算就完成了。
IE 30
§2. Spreadsheet建模
Simulation
顾客号 6 12 13 22 37 42 64
时间间隔（分钟） 顾客号 1 82 97 100
单击“确定”，得到值： 0.543795；构建行变量0.00~0.09置于C5~L5
列变量0.0~1.1置于B6~B17；选定存放数据的目标数据区C6~L17后，依次 单击菜单的“数据”–“ 假设分析”– “数据表”选项，在动态对话框 输入栏“引用行单元格”选择B3， “引用行单元格”选择B4，点击“ 确定”，即得正态分布表。
Simulation
随机数字分配 01~10 11~30 31~60 61~85 86~95 96~00
IE
27
§2. Spreadsheet建模
Simulation
IE
28
§2. Spreadsheet建模
Simulation
RAND 函数：返回大于等于 0 及小于 1 的均匀分布随机实数，每次 计算工作表时都将返回一个新的随机实数。 语法 RAND( ) 若要生成 a 与 b 之间的随机实数，请使用：RAND()*(b-a)+a
如果要使用函数 RAND 生成一随机数，并且使之不随单元格计算而 改变，可以在编辑栏中输入“=RAND()”，保持编辑状态，然后按 F9 ，将公式永久性地改为随机数。
“=RAND() ”生成介于 0 到 1 之间的一个随机数（变量） “=RAND()*100 ” 生成大于等于 0 但小于 100 的一个随机数（变量）
2. 相邻的随机数是相互独立的。
利用Excel表格方式仿真。
IE
25
§2. Spreadsheet建模
表2-1 到达间隔时间分布
到达时间间隔（分钟） 概率
Simulation
累积概率 0.125 0.250 0.375 0.500 0.625 0.750 0.875 1.000
随机数字分配 001~125 126~250 251~375 376~500 501~625 626~750 751~875 876~000
IE 7
§1. Excel基础
Simulation
二：模拟运算表：Excel的某些单元格是通过其他单元格运算而得，可以 把这些单元格看成是其他单元格的函数。 分别记为：x1, x2, x3,… , xn 结果单元格 y=f(x1, x2, x3,… , xn), 其中f为运算关系，可以是一般的代数 函数运算，也可以是复杂的规划求解，甚至可以是模型的运算。模拟 运算表就是实现这一功能的工具。 在Excel2007中，模拟运算表称为数据表。通过数据表可以查看多个不同 输入的结果，为建模和结果分析提供了很大方便。
“=RANDBETWEEN(1,100) ”生成介于 1 到 100 之间的一个随机数 （变量） “=RANDBETWEEN(-1,1) ”生成介于 -1 到 1 之间的一个随机数（ 变量） 随机产生1000个数字，000~999，按题设数字筛选得以下表格，用以 确定到达时间间隔；服务时间的生成依此形成。
IE
22
§1. Excel基础
Simulation
IE
23
§1. Excel基础
Simulation
IE
24
§2. Spreadsheet建模
Simulation
例题：排队系统的仿真 — 一个拥有一个出纳台的小杂货铺。顾客相 隔1~8分钟随机到达出纳台，每个到达间隔时间的可能取值具有相同 的发生概率，如下表2-1；服务时间在1~6分钟间变化，其概率见表22。希望通过100个顾客的到达和接受服务情况来分析该系统。 排队系统的仿真，总的说来是需要维护一个事件列表来确定下一步将 会发生什么事情。 为了产生到达出纳台的时间，需要一组均匀分布的随机数。满足： 1. 这些随机数在0~1间均匀分布；
IE
5
§1. Excel基础
Simulation
=IF(AVERAGE(A2:A6)>30, AVERAGE(B2:B6),IF(AVERAGE(A2:A6)>25 AVERAGE(A2:A6), 0))
IE
6
§1. Excel基础
Simulation
数组与模拟运算：
一、数组：是以一个范围内的整个数据作为运算对象的特殊公式，其形 式比较特殊，是在公式的“=”前面与公式结尾处加一组花括号
64.83
29.49
48.86
35.17
53.24
IE
9
§1. Excel基础
一维数据表
Simulation
IE
10
§1. Excel基础
Simulation
可以看出，引用时“{=TABLE（，B1）}”，要 输入的只是单个单元格。
IE
11
§1. Excel基础
Simulation
2、二维数据表：与一维数据表不同之处是要输入“输入引用列的单元格” 和“输入引用行的单元格”。
月份 股票A 0 1 2 19.03 3 21.92 4 15.97 5 41.26 6 34.67
25.99 28.55
股票B
月份
47.06 72.88
7 8
63.61
9
23.21
10
47.71
11
60.82
12
63.26
股票A
股票B
27.79 29.57
60.5 47.2
29.6
4表编制变量精确到小数点后2位的正态分布表 在单元格B1、B2输入均值0和标准差1，在B3、B4分别输入变量x的百分位 数和精确到小数点后一位的数，比如0.01（1%）和0.1； 单元格B5则调用正态分布函数NORMDIST（），在编辑栏输入： = NORMDIST(B3+B4, B1, B2, TRUE) （ ► 单元格B22重新演示位置）