用Excel作计算与数据分析0204.docx
《用Excel作计算与数据分析0204.docx》由会员分享，可在线阅读，更多相关《用Excel作计算与数据分析0204.docx（28页珍藏版）》请在冰豆网上搜索。
用Excel作计算与数据分析0204
用Excel作计算与数据分析02-04
准备数据集；
计算描述统计量，如均值和方差等；
作图，如直方图等；
随机数发生器，Rand（）的应用;
相关系数、协方差；
回归。
有关学习网站推荐：
Char02
例2.1--纸牌游戏中的条件概率计算
新建Excel表---选择要输出
的单元格---键入“=（4/52）/（12/52）”---按回车
0.333333
例2.2--在班级里随机地选学生
选定单元格---输入”=0.25/0.40”---按回车
0.625
使用概率和概率表格
本部分内容的目的不是计算，而是分析和解决问题。
所以不需用Excel来计算。
例2.3---使用概率表格来计算决策树中的概率
例2.4–制造业中的质量控制
例2.5--一种新消费服务成功的概率
例2.6–谋杀案审判中的DNA检验
例2.7–一个灵活制造系统的可靠性
例2.8–生产过程中的质量问题
假设一个生产过程生产出的产品是优质产品的概率是0.92，是劣质产品的概率是0.08。
假设此生产过程每次生产140单位的产品。
用X表示每次生产的优质产品数。
则X服从二项分布，样本容量n=140，发生概率p=0.92。
注意X可以取0,1,2,3,…,140中的任何值，取到不同的值的概率也不同。
Excel计算概率公式：
P（X=x）=BINOMDIST（x,140,0.92,FALSE）
于是计算例中X=130概率如下，
方法一：
选定单元格---输入“=BINOMDIST（130,140,0.92,FALSE）“---回车确认;
0.120773381
方法二：
用函数工具图标。
选定单元格---点击函数图标---在常用函数中选BINOMDIST---在Number_s输入130---Trails输入140---probability_s输入0.92---Cumulative输入false---回车确认;
例2.9–掷硬币10,求正面出现6次的概率。
正面出现次数H服从二项分布，样本容量n=10，发生概率p=0.5。
Excel计算概率公式：
P（H=6）=BINOMDIST（6,10,0.5,FALSE）
方法一：
选定单元格---输入“=BINOMDIST（6,10,0.5,FALSE）“---回车确认;
0.205078125
方法二：
用函数工具图标。
表2.14n=8,p=0.7的二项分布概率
X
概率
0
0.00007
1
0.00122
2
0.01000
3
0.04668
4
0.13614
5
0.25412
6
0.29648
7
0.19765
8
0.05765
Excel产生表2.14的操作过程：
方法一：
新建Excel表---在A1:
A9写入0至8---在B1中写入公式“=BINOMDIST（A1,8,0.7,FALSE）”---回车---复制粘贴
方法二：
新建Excel表---在A1:
A9写入0至8---选单元格B1---在常用函数中选BINOMDIST---在Number_s后面的图标---选择单元格A1---Trails输入8---probability_s输入0.7---Cumulative输入false---回车确认;
例2.10–办公室里的计算机
假设办公室里有8台计算机。
在午饭时间任何一台计算机使用的概率是0.70。
用X表示午饭时间正在使用的计算机的个数。
则X服从二项分布，样本容量n=8，成功概率p=0.70。
Excel计算概率公式：
P（X=x）=BINOMDIST（x,8,0.7,FALSE）
例2.11–生产过程中的质量控制
假设一个生产过程生产出的产品是优质产品的概率是0.83，是劣质产品的概率是0.17。
假设此生产过程每次生产5单位的产品。
用X表示每次生产出的劣质产品的单位数。
正好生产出2个劣质产品的概率为P（X=2）：
P（X=2）=BINOMDIST（2,5,0.17,FALSE）
选定单元格---输入“=BINOMDIST（2,5,0.17,FALSE）’---回车.
0.165246
每次至少生产出1单位劣质产品的概率为
：
P（X≥1）=1-BINOMDIST（0,5,0.17,TRUE）
选定单元格---输入“=1-BINOMDIST（0,5,0.17,TRUE）’---回车.
0.606095936
注意上面公式中TRUE表示返回累积概率值，FALSE表示返回等于某值的概率值。
例2.12–NetwayComputers公司服务中心的人员安排问题
用Excel计算方法同前。
P（X≥2）=1-BINOMDIST（1,50,0.02,TRUE）
0.264228606
P（X≥3）=1-BINOMDIST（2,50,0.02,TRUE）
0.078427748
P（X≥4）=1-BINOMDIST（3,50,0.02,TRUE）
0.017758081
或计算“=CRITBINOM（50,0.02,0.95）“，计算结果为3。
函数CRITBINOM（trails,probability_s,alpha）返回一个数值，该值为使累积二项分布的函数值大于等于临界值alpha的最小整数。
例2.13–掷骰子---求期望
1
1/6
2
1/6
3
1/6
4
1/6
5
1/6
6
1/6
选定单元格A1---键入1----在第二个键入=1+A1，回车----复制粘贴公式
选定存放均值的单元格---键入“=SUM（A1:
A6*B1:
B6）“按Ctrl+Shift+Enter
3.5
例2.14–GeneralAvionics636飞机的订单数---求期望
表2.13Avionics飞机订单数的概率分布
Avionics飞机订单数xi
概率pi
42
0.05
43
0.10
44
0.15
45
0.20
46
0.25
47
0.15
48
0.10
复制表2.13---新建Excel表---粘贴
方法一：
选定要计算均值的单元格---键入“=SUM（A2:
A8*b2:
b8）”---按Ctrl+Shift+Enter
45.35
方法二：
先计算各单项乘积，后求和;
例2.15–两个地区的销售情况---求均值。
表2.16东部地区销售情况
销售额X（$million）
概率p
3
0.05
4
0.20
5
0.35
6
0.30
7
0.10
表2.17西部地区销售情况
销售额X（$million）
概率p
3
0.15
4
0.20
5
0.25
6
0.15
7
0.15
8
0.10
首先用Excel作柱状分布图：
复制表2.16到Excel---插入---图表---选柱状图类型1---点下一步---选系列产生中的列---在数据区域选Y变量数据列---选系列---在分类（X）轴标志下选X变量数据列---点完成。
如果不点完成---
还可以继续选“标题”---图表标题栏输入“柱状图”---分类（X）轴输入“销售额（$million）”---数值轴（Y）输入“概率”。
作法同上例：
“=SUM（A2:
A6*B2:
B6）“---按Ctrl+Shift+Enter
5.2
“=SUM（A2:
A7*B2:
B7）“---按Ctrl+Shift+Enter
5.25
例2.16–掷骰子---求方差
首先计算期望（比如在B7中）---选择要存放方差的单元格---键入
“=SUM（（A1:
A6-B7）^2*B1:
B6）“---按Ctrl+Shift+Enter
2.916667
例2.17–GeneralAvionics636飞机的订单数---求方差
表2.13Avionics飞机订单数的概率分布
Avionics飞机订单数xi
概率pi
42
0.05
43
0.10
44
0.15
45
0.20
46
0.25
47
0.15
48
0.10
首先如例2.14计算期望（比如在A10中）“=SUM（A2:
A8*B2:
B8）”----选择要计算方差的单元格，键入“=SUM（（A2:
A8-A10）^2*B2:
B8）“---按Ctrl+Shift+Enter
2.6275
例2.18–两个地区的销售情况---求方差
表2.16东部地区销售情况
销售额X（$million）
概率p
3
0.05
4
0.20
5
0.35
6
0.30
7
0.10
在单元格B8输入“=SUM（A2:
A6*B2:
B6）“---按Ctrl+Shift+Enter，这样求得的期望（5.2）存于B8中。
在存放方差的单元格中输入“=SUM（（A2:
A7-B8）^2*B2:
B7）“---按Ctrl+Shift+Enter
1.06
表2.17西部地区销售情况
销售额X（$million）
概率p
3
0.15
4
0.20
5
0.25
6
0.15
7
0.15
8
0.10
在单元格B8输入“=SUM（A2:
A7*B2:
B7）“---按Ctrl+Shift+Enter，这样求得的期望（5.25）存于B8中。
在存放方差的单元格中输入“=SUM（（A2:
A7-B8）^2*B2:
B7）“---按Ctrl+Shift+Enter
2.3875
例2.19–二项分布的均值和标准差公式。
例2.20–NetwayComputers公司服务中心的人员安排问题
按公式直接求;
在选定的单元格直接输入：
=50*0.02
=50*0.02*（1-0.02）
=SQRT（50*0.02*（1-0.02））
计算结果：
1
0.98
0.989949
例2.21–概率分布和决策树
留作练习;
例2.22–一名经理的报酬
表2.22
工资X（$）
概率p
95,000
0.15
100,000
0.20
103,000
0.25
107,000
0.20
109,000
0.20
用X表示Karen现在公司明年给她的工资。
则可计算出E（X），VAR（X）和σX。
而如果Karen加入竞争对手公司，则她的工资为Y=1.20×X+12,000。
然后可以计算出E（Y），VAR（Y）和σY。
直接将表2.22复制到Excel表中---选定要存放E（X）的单元格（设为C2）---键入“=SUM（A2:
A6*B2:
B6）”---按Ctrl+Shift+Enter
103200
选定要存放Var（X）的单元格（设为C3）---键入“=SUM（（A2:
A6-C2）^2*B2:
B6）”---按Ctrl+Shift+Enter
21760000
选定要存放σX的单元格（设为C4）---键入“＝sqrt（C3）”---按回车
4664.761516
选定要存放E（Y）的单元格（设为D2）---键入“=1.20*C2+12000”---按回车
135840
选定要存放Var（Y）的单元格（设为D3）---键入“=1.2^2*C3”---按回车
31334400
选定要存放σY的单元格（设为D4）---键入“=sqrt（D3）”---按回车
5597.714
例2.23–股票市场的一个简单模型
例2.24–共同基金（MutualFunds）降低投资风险
例2.25–生日问题
例2.26–信息在股票市场上投资的作用
zExercise2.3（计算概率）
zExercise2.9（全概率公式，条件概率）
zExercise2.14（期望、方差）
zExercise2.24（相关性度量）
zExercise2.27（Excel，二项分布）
Char03
例3.1–汽车排气系统的寿命
例3.2–钢板的宽
例3.3-汽车排气系统的寿命
正态分布-Normal
用Excel画正态密度函数图：
新建Excel表---在A列键入-3，-2.8，…3---在单元格B1输出公式“=EXP（-1*（A1^2/2））/SQRT（2*PI（））“---复制粘贴公式（即用鼠标点单元格B1,下拉）
选择数据区域---插入---图表---XY散点图---无数据点平滑线散点图---各种表格属性---完成
分布函数图形
新建Excel表---在A列键入-3，-2.8，…3---在C列建立公式“=NORMDIST（A1,0,1,TRUE）“---复制粘贴公式
选择数据区域---插入---图表---XY散点图---无数据点平滑线散点图---各种表格属性---完成
例3.4–Valley纺织品公司月收益率的分布
表3.1
Month
Rateof
Return
（%）
Month
Rateof
Return
（%）
Month
Rateof
Return
（%）
Month
Rateof
Return
（%）
Jan-88
3.58
May-90
-4.77
Sep-92
5.26
Jan-95
1.74
Feb-88
2.41
Jun-90
6.43
Oct-92
0.86
Feb-95
3.99
Mar-88
2.47
Jul-90
-4.07
Nov-92
2.30
Mar-95
2.74
Apr-88
1.99
Aug-90
1.30
Dec-92
-2.10
Apr-95
4.56
May-88
-1.40
Sep-90
0.24
Jan-93
-2.38
May-95
-0.07
Jun-88
7.50
Oct-90
-1.46
Feb-93
1.51
Jun-95
-1.39
Jul-88
4.60
Nov-90
1.85
Mar-93
1.54
Jul-95
5.69
Aug-88
4.18
Dec-90
2.77
Apr-93
2.02
Aug-95
3.82
Sep-88
-2.13
Jan-91
5.72
May-93
3.35
Sep-95
2.89
Oct-88
4.84
Feb-91
5.94
Jun-93
-0.72
Oct-95
-2.64
Nov-88
5.00
Mar-91
-1.11
Jul-93
-0.44
Nov-95
-1.06
Dec-88
3.53
Apr-91
2.76
Aug-93
-1.56
Dec-95
3.10
Jan-89
3.22
May-91
2.28
Sep-93
3.69
Jan-96
1.46
Feb-89
5.32
Jun-91
-2.19
Oct-93
2.28
Feb-96
0.72
Mar-89
-2.18
Jul-91
3.33
Nov-93
-3.48
Mar-96
7.24
Apr-89
-1.47
Aug-91
-0.46
Dec-93
3.68
Apr-96
-1.65
May-89
-5.69
Sep-91
0.65
Jan-94
-1.78
May-96
2.66
Jun-89
6.65
Oct-91
4.80
Mar-94
3.66
Jun-96
7.87
Jul-89
8.76
Nov-91
0.46
Mar-94
1.12
Jul-96
7.00
Aug-89
-0.24
Dec-91
2.03
Apr-94
0.03
Aug-96
5.93
Sep-89
3.00
Jan-92
-0.07
May-94
2.99
Sep-96
0.89
Oct-89
-1.19
Feb-92
5.11
Jun-94
4.64
Oct-96
6.52
Nov-89
-1.12
Mar-92
-7.68
Jul-94
-2.69
Nov-96
0.76
Dec-89
-1.63
Apr-92
4.16
Aug-94
-0.75
Dec-96
-1.43
Jan-90
2.16
May-92
1.89
Sep-94
-4.83
Jan-97
0.43
Feb-90
1.73
Jun-92
2.12
Oct-94
0.23
Feb-97
0.56
Mar-90
6.40
Jul-92
-3.51
Nov-94
5.06
Apr-90
-5.59
Aug-92
2.07
Dec-94
-0.90
选定本章表格表3.1---复制---建立新Excel表---粘贴；
将4列的RateofReturn合并到1列上（如B列）,对该进行排序，得到最小、最大的RateofReturn值：
最小值：
-7.68
最大值：
8.76
共有112个数值，于是大约可以分成11个区间组，根据最小、最大值，将-7.5到7.5等分为11个区间组，每个区间长度为1.5
选定单元格D33---输入“-7.5“---在单元格D34输入”=D33+1.5“---下拉开成如下区间组：
-7.5
-6
-4.5
-3
-1.5
0
1.5
3
4.5
6
7.5
9
插入函数---选FREQUENCY---在data-array中选择要计算频率的变量值---在Bins-array选择刚计算好的区间组---确定---下拉全选与区间组对应的空白区域---按F2---再按Ctrl+Shift+Enter
再将频率转换为百分比。
插入---图表---选柱状图类型1---点下一步---选系列产生中的列---在数据区域选Y变量数据列---选系列---在分类（X）轴标志下选X变量数据列---点完成。
例3.5–Simco食品公司在各销售区域的收入分布
表3.2
District
NetRevenues（$Million）
District
NetRevenues（$Million）
District
NetRevenues（$Million）
District
NetRevenues（$Million）
1
-6.83
48
15.77
95
16.98
142
3.55
2
13.64
49
15.94
96
10.04
143
9.48
3
16.72
50
-3.65
97
12.30
144
11.42
4
14.23
51
27.37
98
3.82
145
8.01
5
16.65
52
11.03
99
17.30
146
13.83
6
12.37
53
14.38
100
8.43
147
-9.82
7
12.87
54
15.12
101
14.14
148
12.89
8
7.80
55
4.96
102
13.65
149
5.10
9
6.41
56
16.39
103
10.10
150
19.32
10
8.06
57
15.48
104
8.02
151
31.18
11
26.56
58
7.58
105
22.68
152
6.77
12
-3.23
59
19.16
106
-4.40
153
12.52
13
10.10
60
7.24
107
18.53
154
6.15
14
16.17
61
8.57
108
10.78
155
14.66
15
14.08
62
8.97
109
7.63
156
16.89
16
10.31
63
14.79
110
19.59
157
9.25
17
9.10
64
23.36
111
18.08
158
6.20
18
9.82
65
7.44
112
-6.82
159
11.57
19
13.00
66
3.38
113
15.86
160
23.27
20
13.06
67
-0.94
114
26.88
161
-0.59
21
14.60
68
19.28
115
16.55
162
26.31
22
6.24
69
-5.17
116
13.44
163
16.47
23
4.82
70
3.06
117
9.35
164
16.84
24
11.31
71
10.79
118
14.02
165
7.18
25
4.03
72
6.15
119
16.67
166
11.63
26
9.92
73
1.67
120
0.58
167
12.64
27
6.62
74
6.98
121
9.12
168
12.53
28
22.29
75
3.70
122
-0.04
169
9.23
29
-3.71
76
12.15
123
13.17
170
19.39
30
6.50
77
9.54
124
5.35
171
17.93
31
-4.18
78
14.34
125
14.28
172
14.88
32
8.21
79
3.68
126
26.83
173
1.14
33
12.13
80
3.85
127
31.85
174
2.22
34
-5.90
81
-1.95
128
1.49
175
11.84
35
10.32
82
9.04
129
17.44
176
12.09
36
13.97
83
8.22
130
12.16
177
-3.03
37
4.45
84
13.71
131
26.42
178
10.90
38
3.46
85
25.99
132
-4.62
179
6.71
39
19.53
86
6.64
133
22.73
180
15.31
40
20.97
升级会员 