管理定量分析课程实验指导书.docx

管理定量分析课程实验指导书.docx

《管理定量分析课程实验指导书.docx》由会员分享，可在线阅读，更多相关《管理定量分析课程实验指导书.docx（51页珍藏版）》请在冰豆网上搜索。

管理定量分析课程实验指导书

《管理定量分析》课程实验指导书

实验一：

用Excel计算描述统计量——集中指标、变异指标分布与形状

试验目的:

熟练使用Excel进行常用统计量的计算

试验内容:

  在本章中，我们介绍了测度数据集中趋势和离散程度的常用统计量，在本试验中，将展示如何用Excel来计算这些统计量。

为了说明方便，假定已将50个数据输入到Excel工作表的A1:

A50单元格中。

下面给出用Excel计算这些数据描述统计量的具体步骤。

表1分析用数据列表

117

122

124

129

139

107

117

130

122

125

108

131

125

117

122

133

126

122

118

108

110

118

123

126

133

134

127

123

118

112

112

134

127

123

119

113

120

123

127

135

137

114

120

128

124

115

139

128

124

121

  第1步：

选择“工具”下拉菜单。

  第2步：

选择“数据分析”选项。

（需要从Office安装盘安装数据分析库）

图1  选择“数据分析”功能　

  第3步：

在分析工具中选择“描述统计”。

  第4步：

当出现对话框时，在“输入区域”方框内键入A1:

A50；在“输出选项”中选择输出区域（在此选择“新工作表”）；然后选择“汇总统计”（该选项给出全部描述统计量）;最后选择“确定”。

图3参数设置　

    下面的附表是Excel输出的描述统计量计算结果。

用黑体显示的是本章中所介绍的描述统计量。

表2结果列表及说明

Excel2002输出名称

Excel2002输出结果 

说明

平均

122.98

标准误差

1.135149006

中值

123

中位数

模式

122

众数

标准偏差

8.026715596

样本方差

64.42816327

峰值

-0.408713596

偏斜度

9.94468E-05

偏移度

区域

32

极差

最小值

107

最大值

139

求和

6149

计数

50

最大

（1）

139

最小

（1）

107

置信度（95.0%）

2.281165949

实验二：

抽样分布于区间估计之用Excel计算分布的概率

试验目的:

熟练使用Excel进行常用概率分布概率值的计算

试验内容:

利用Excel中的函数工具，可以计算二项分布、超几何分布、泊松分布、正态分布等概率分布的概率。

在本试验中，我们将介绍二项分布概率的计算。

泊松分布、超几何分布、正态分布的概率计算与二项分布类似。

利用Excel的BINOMDIST函数可以计算出二项分布的概率分布以及累积概率。

该函数有四个参数：

Number-s（实验成功的次数）、Trials（实验的总次数）、Probability-s（每次实验成功的概率）、Cumulative（该参数是一个逻辑值，如果为True，设实验成功的次数为m，则计算出累积分布函数的概率，即P（X≤m）;如果为False，设实验成功的次数为m，则计算出概率密度函数的概率，即P（X=m））.下面我们结合一个例子说明计算二项分布概率的具体步骤。

[例]

已知一批产品的次品率为5%，现从中任抽取一个，又放回地抽取3次。

求：

（1）在所抽取的3个产品中恰好有2个次品的概率；

（2）次品数为2个及2个以下的累积概率；

第1步：

选择“插入”下拉菜单。

第2步：

选择数据“函数”选项。

图1EXCEL使用界面　

第3步：

当出现函数对话框时，选择BINOMDIST函数。

图2函数参数设置界面　

第4步：

当BINOMDIST函数对话框出现时在Number-s窗口输入2（成功的次数X）； 在Trials窗口输入3（实验的总次数n）；在Probability-s窗口输入0.05（每次实验成功的概率p）；在Cumulative窗口输入False；选择“完成”。

此时，在指定的单元格出现恰好有2个次品的概率0.007125

在计算次品数为2个及2个以下的累积概率时，步骤相同，只需在上述第四步的Cumulative窗口中输入True即可。

此时在指定的单元格出现的概率为0.999875。

如果我们计算次品数为1个及1个以下的累积概率，只需在上述的第4步的Number_s窗口输入1，在Cumulative窗口中输入True即可，此时在指定的窗口出现的概率为0.99275。

计算泊松分布、超几何分布、正态分布概率的步骤与上述过程类似，在第3步选择POISSON\HYPGEMDIST或NORMDIST函数名，根据第4步对话框的指导输入相应的值即可。

实验三：

抽样分布于区间估计之用Excel求置信区间

试验目的:

熟练使用Excel进行参数的区间估计

试验内容:

[试验]

   用Excel的函数工具以及使用者自己输入公式等组合方式，可以构造出专门用于区间估计的Excel工作表格。

下面结合一个例子说明具体的操作步骤。

[例]

   某零件加工企业生产一种螺丝钉，对某天加工的零件每隔一定时间抽出一个，共抽取12个，测得其长度（单位：

mm）数据如附表中的A2:

A13。

假定零件长度服从正态分布，试以95%的置信水平估计该企业生产的螺丝钉平均长度的置信区间。

表1 用Excel求置信区间

A

B

C

D

1

样本数据

计算指标

计算公式

计算结果

2

10.94

样本数据个数

＝COUNT（A2:

A13）

12

3

11.91

样本均值

=AVERAGE（A2:

A13）

11.074167

4

10.91

样本标准差

=STDEV（A2:

A13）

0.272746

5

10.94

样本平均值的标准差

=C4/SQRT（C2）

0.078735

6

11.03

置信水平

=0.95

0.95

7

10.97

自由度

=C2-1

11

8

11.09

t值

=TINV（1-C6,C7）

2.200986

9

11.00

误差范围

=C8*C5

0.173294

10

11.16

置信下限

=C3-C9

10.900872

11

10.94

置信上限

=C3+C9

11.2474610

12

11.03

13

10.97

   为构造区间估计的工作表，我们应在工作表中输入下列内容：

A列输入样本数据，B列输入变量名称，C列输入计算公式。

（1）本表D列为C列的计算结果，当输入完公式后，即显示D列结果。

（2）对于不同的样本数据，只要输入新的样本数据，再对C列公式中的样本数据区域加修改，置信区间就会自动给出。

如果需要不同的置信水平，填入相应的数值即可。

   我们有95%把握认为该企业生产的螺丝钉的平均长度在10.900872mm～11.247461mm之间。

   正态总体，

已知，总体均值的区间估计

已知时采用正态分布统计量构造置信区间，此时不用计算样本标准差，直接使用总体标准差；B8单元格改为Z值；C8单元格改为“=NORMSINV（（1-C6）/2）”即可。

实验四：

抽样分布于区间估计之用EXCEL进行假设检验

试验目的:

熟练使用Excel2002进行参数的假设检验

试验内容:

  本章介绍的假设检验包括一个正态总体的参数检验和两个正态总体的参数检验。

对于一个正态总体参数的检验,可利用函数工具和自己输入公式的方法计算统计量,并进行检验。

本试验主要介绍如何使用Excel进行两个正态分布的均值方差的检验.

两个总体均值之差的检验：

  下面我们结合一个例子说明检验的操作步骤。

  为了评价两个学校的教学质量,分别在两个学校抽取样本。

在A学校抽取30名学生,在B学校抽取40名学生,对两个学校的学生同时进行一次英语标准化考试,成绩如下表所示。

假设学校A考试成绩的方差为64,学校B考试成绩的方差为100。

检验两个学校的教学质量是否有显著差异。

学校A

学校B

70      97  85  87  64  73

86      90  82  83  92  74

72  94  76  89  73  88

91  79  84  76  87  88

85  78  83  84  91  74

76  91  57  62  89  82  93  64

80  78  99  59  79  82  70  85

83  87  78  84  84  70  79  72

91  93  75  85  65  74  79  64

84  66  66  85  78  83  75  74

  假定我们将上表中学校A的数据输入到工作表中的A1:

A30,学校B的数据输入到工作表的B1:

B40。

检验的步骤如下：

  第一步:

选择“工具”下拉菜单。

  第二步:

选择“数据分析”选项（需要从Office安装盘安装数据分析库）。

图1数据录入窗口

  第三步:

在分析工具中选择“Z－检验:

二样本平均差检验”。

  第四步:

当出现对话框后，在“变量1的区域”方框内键入A1:

A30；在“变量2的区域”方框内键入B1：

B40；在“假设平均差”方框内键入0；在“变量的方差”方框内键入64；在“变量2的方差”方框内键入100；在“

”方框内键入0.05；在“输出选项”中选择输出区域（在此选择“新工作表”）。

输出结果如附表。

图2  参数设置窗口　

z-检验:

双样本均值分析

变量1

变量2

平均

82.5

78

已知协方差

64

100

观测值

30

40

假设平均差

0

z

2.090574944

P（Z<=z）单尾

0.018283028

z单尾临界

1.644853476

P（Z<=z）双尾

0.036566055

z双尾临界

1.959962787

   由于

，所以拒绝

，即两个学校的教学质量有显著差异。

实验五：

用EXCEL进行单因素方差分析

试验目的:

熟练使用Excel2002进行单因素方差分析

试验内容:

    五个地区每天发生交通事故的次数如下表所示：

表1 交通事故数据

东部

北部

中部

南部

西部

15

12

10

14

13

17

10

14

9

12

14

13

13

7

9

11

17

15

10

14

—

 14

  12

  8

  10

—

—

—

7

9

   试以α=0.01显著水平检验各地区平均每天交通事故的次数是否相等。

   假设我们已将数据输入到工作表中的A3：

E8单元。

然后按下面的实验步骤进行试验。

表2数据录入表

A

B

C

D

E

1

五个地区每天发生的交通事故次数

2

东部

北部

中部

南部

西部

3

15

12

10

14

13

4

17

10

14

9

12

5

14

13

13

7

9

6

11

17

15

10

14

7

14

12

8

10

8

7

9

   第一步：

选择“工具”下拉菜单。

   第二步：

选择“数据分析”选项。

图1选择“数据分析”　

   第三步：

在分析工具中选择“单因素方差分析”。

图2选择“单方差分析”　

   第四步：

当出现对话框后，在“数据区域”方框内键入A3：

E8；在α方框内输入0.01；在“输出选项”中选择输出区域为G2；选择“确定”。

图3设置分析选项　

   输出结果如下：

表2分析样本描述

方差分析：

单因素方差分析

SUMMARY

组

计数

求和

平均

方差

列1

4

57

14.25

6.25

列2

5

66

13.2

6.7

列3

5

64

12.8

3.7

列4

6

55

9.166667

6.966667

列5

6

67

11.16667

4.566667

表3方差分析结果

方差分析

差异源

SS

df

MS

F

P-value

Fcrit

组间

82.63718

4

20.65929

3.676135

0.020229

4.368815

组内

118.0167

21

5.619841

总计

200.6538

25

   由于F=3.6761

实验六：

用EXCEL进行预测

实验步骤：

【例1-13】：

某煤矿某年1-11月份采煤量如下表：

月份

产量

月份

产量

1

2

3

4

5

6

9.03

9.06

9.12

8.73

8.94

9.30

7

8

9

10

11

12

9.15

9.36

9.45

9.30

9.24

1、用移动平均法进行预测

具体步骤：

第一步：

将原始数据录入到单元格区域A2：

A12，如图1-31所示：

图1-31EXCEL数据集

第二步：

选择菜单条上的“工具”——“数据分析”命令，弹出如图1-32所示的对话框：

图1-32数据分析对话框

第三步：

在“分析工具”框中选择“移动平均”，单击“确定”按钮，弹出移动平均对话框，相应作如下输入，即可得到如图1-33所示的对话框：

（1）   在“输出区域”内输入：

$A$2：

$A$12，即原始数据所在的单元格区域。

（2）   在“间隔”内输入：

3，表示使用三步移动平均法。

（3）   在“输出区域”内输入：

B2，即将输出区域的左上角单元格定义为B2。

（4）   选择“图表输出”复选框和“标准误差”复选框。

1-33移动平均对话框

第四步：

单击“确定”按钮，便可得到移动平均结果，如图1-34所示：

分析：

在图中，B4：

B12对应的数据即为三步移动平均的预测值；单元格区域C6：

C12即为标准误差。

1-34移动平均分析结果

2、用指数平滑法进行预测：

第一步：

将原始数据输入到单元格B2：

B12；

第二步：

选择菜单条上的“工具”——“数据分析”命令，弹出如图1-35所示的对话框：

图1-35数据分析对话框

第三步：

在“分析工具”中选择“指数平滑”，单击“确定”按钮，弹出一个对话框，作相应输入，即可得到如图1-36所示的对话框；

1-36指数平滑对话框

第四步：

单击“确定”按钮，即可得到指数平滑结果，如图1-37所示：

图1-37指数平滑结果

3、趋势预测法进行预测

第一步：

把相关数据输入到EXCEL中，其中月份输入A1－A11单元格，月产量输入B1－B11单元格，如图1-38所示：

图1-38EXCEL数据集

第二步：

在工作表中选择一个空的单元格。

在这里我们选择D2单元格。

第三步：

选择插入下拉菜单。

第四步：

选择函数选项。

第五步：

当函数对话框出现时：

在函数类别框中选择统计，在函数名字中选择FORECAST（预测），如图1-39所示：

图1-39粘贴函数对话框

第六步：

单击确定按钮，出现预测对话框，在x中输入12，在know-y’s中输入B1：

B11，在know-x’s中输入A1：

A11，如图1-40所示：

图1-40FORCAST对话框

第七步：

单击确定按钮，预测结果出现在D2单元格中，如图1-41所示：

图1-41趋势预测法预测结果

实验七：

单变量求解问题

在工作表中建立了如图1-1所示的模型

图1-1建立模型

假设单元格B1的内容为“160000”，则单元格B8怎样变化？

或者说，要求利润为“160000”，那么在其它条件不发生变化的情况下，产品的销售价格应该是多少？

在Excel中，可使用“单变量求解”命令进行该类问题的分析。

步骤如下：

（1）执行“工具”/“单变量求解”命令，屏幕显示如图1-2所示“单变量求解”对话框。

图1-2单变量求解对话框

（2）在目标单元格输入“B1”，在目标值输入框输入“160000”，在可变单元格输入“B8”。

（3）单击“确定”按钮，屏幕弹出“单变量求解状态”提示框，“确定”后，单元格B8显示变化后的值，如图1-3所示。

由此可以看出，满足条件的单价应该是57.8。

图1-3单变量求解结果

5．输入和编辑公式。

参见例1-1、1-2、1-3。

重点是：

⑴Excel公式的一般格式；

⑵运算符及其优先顺序；

（3）数组公式和数组常量；

（4）单元格引用的式样和引用的类型。

实验八：

模拟运算表

模拟运算表是工作表中的一个单元格区域，它可以显示公式中某些值的变化对运算结果的影响。

模拟运算表在经济管理中有着重要的作用。

如图1-4所示，对例1-1所介绍的模型进行单输入模拟运算。

图1-4单输入模拟运算

假设产量变化，其它条件不变。

模拟计算销售金额、营销利润的，操作步骤如下：

（1）确定模拟运算表的单元格区域：

如图所示，确定为B12：

D18。

从B13开始向下输入产量数值；B12为运算公式中的“输入引用列的单元格”地址。

（2）在第一个输入数值上一行的右侧（即C12单元格），输入销售金额公式“=单价*B12”，其右再输入利润公式“=C12—费用—固定成本—B12*单片成本”。

（3）选定包含公式和替换值的矩形区域，如图1-5所示B12：

D18，执行“数据”/“模拟运算表”命令，屏幕弹出如图1-6所示提示框。

（4）在“输入引用列的单元格”中输入“引用单元格”地址“$B$12”，单击“确定”按钮。

之后，按输入的产量序列进行计算，结果显示在每一个输入值的右侧，如图1-7所示。

附加练习：

1．对于线性方程组AX=B，设A矩阵在单元格区域A4到C6中，B矩阵在单元格区域E4到E6中，求X.

2．计算从今天到2050年1月1日之间的天数。

3．写出下列Excel公式的结果（建议先自己写出结果，然后上机计算。

如有不同，仔细进行分析）。

=123&456

结果为:

=”ABC”>”SDF”

结果为:

=SQRT（“8”+”1”）

结果为:

=”99/6/1”–“99/5/1”

结果为:

=YEAR（TODAY（））

结果为:

=”NOT”&TRUE

结果为:

=”3”+”2”

结果为:

=1+”$4.00”

结果为:

=SQRT（8&1）

结果为:

=MONTH（TODAY（））

结果为:

4．利用Excel数组运算功能，进行矩阵加、减法运算。

实验九线性规划模型建立及求解

一、实验目的及要求

（一）实验目的

1．理解线性规划原理；

2．掌握线性规划模型建立和求解基本技术；

3．理解敏感性分析的重要性，并掌握相关原理。

二、实验内容

1．线性规划模型的建立；

2．线性规划模型的求解；

3．敏感性分析。

三、实验步骤

例2-1学校准备为学生添加营养餐，每个学生每月至少需要补充60单位的碳水化合物，40单位的蛋白质和35单位的脂肪。

已知A、B两种营养品的含量及单价见表4-6。

表4-6两种营养品营养成分含量

A

B

碳水化合物

5单位

2单位

蛋白质

3单位

2单位

脂肪

5单位

1单位

单价

1.5元/斤

0.7元/斤

问买A和B分别多少斤既满足学生营养需要又省钱？

（1）决策变量。

可设x为营养品A的投入量（斤），y为营养品B的投入量（斤），x，y即为本问题的决策变量。

（2）目标函数。

（3）约束条件。

本问题共有四个约束。

最后得出它的线性规划模型如下：

s.t.

下面用Excel来求解这个问题，步骤如下：

1．输入模型参数。

参见图2-1。

图2-1：

线性规划模型

2．建立模型参数间的联系。

注意使用SUMPRODUCT（）函数。

3．运用“规划求解”定义并解答问题。

注意：

单击“规划求解”命令。

注意如果菜单中没有这个命令请使用“工具”菜单的“加载宏”安装。

在弹出的“规划求解参数”设置对话框中设置决策变量、目标函数和约束条件所在的地址以及选定求最小值。

⑴在“工具”菜单中，单击“规划求解”命令。

⑵在“目标单元格”编辑框中，键入单元格引用或目标单元格的名称。

⑶如果要使目标单元格中数值最大，单击“最大值”选项。

如果要使目标单元格中数值最小，单击“最小值”选项。

⑷在“可变单元格”编辑框中，键入每个可变单元格的名称或引用。

⑸在“约束”列表框中，输入相应的约束条件。

⑹单击“求解”按钮。

⑺如果要在工作表中保存求解后的数值，在“规划求解结果”对话框中，单击“保存规划求解结果”。

4．确定最佳决策。

5．进行敏感性分析。

建议先运用所学相关知识进行分析，然后通过反复修改模型参数进行验证。

⑴目标函数系数的敏感性分析；

⑵右边值变化的敏感性分析。

四、实验结果

1．线性规划模型；

2．模型的求解结果（3个报告的工作表）；

3．反映实验结果的电子文档。

图2-2：

对话框示例

五、实验思考题

下面是对一个有关农户生产决策线性规划模型求解所得的计算机输出结果的一部分：

MicrosoftExcel8.0敏感性报告

可变单元格

终

递减

目标式

允许的

允许的

单元格

名字

值

成本

系数

增量

减量

$B$3

水稻

20

0

195

33

20.125

$C$3

棉花

24

0

285

50

41.25

$D$3

玉米

0

-36.2

200

36.2

1E+30

约束

终

阴影

约束

允许