数学建模教材36附录四Excel在统计分析与数量方法中的应用.docx
《数学建模教材36附录四Excel在统计分析与数量方法中的应用.docx》由会员分享，可在线阅读，更多相关《数学建模教材36附录四Excel在统计分析与数量方法中的应用.docx（10页珍藏版）》请在冰豆网上搜索。
数学建模教材36附录四Excel在统计分析与数量方法中的应用
§1Excel简介
MicrosoftExcel是美国微软公司开发的Windows环境下的电子表格系统，它是目前应用最为广泛的办公室表格处理软件之一。
Excel具有强有力的数据库管理功能、丰富的宏命令和函数、强有力的决策支持工具，它具有以下主要特点。
（1）分析能力
Excel除了可以做一些一般的计算工作外，还有400多个函数，用来做统计、财务、数学、字符串等操作以及各种工程上的分析与计算。
Excel还专门提供了一组现成的数据分析工具，称为“分析工具库”，这些分析工具为建立复杂的统计或计量分析工作带来极大的方便。
（2）操作简便
（3）图表能力
在Excel中，系统大约有100多种不同格式的图表可供选用，用户只要做几个简单的按键动作，就可以制作精美的图表。
通过图表指南一步步的引导，可使用不同的选项，得到所需的结果，满意的话就继续，不满意则后退一步，重新修改选项，直到最后出现完美的图表。
（4）数据库管理能力管理数据库可用专门的数据库管理软件，如FoxPro，Access，Clipper，Sybase等。
在Excel中提供了类似的数据库管理功能，保存在工作表内的数据都是按照相应的行和列存储的，这种数据结构再加上Excel提供的有关处理数据库的命令和函数，使得Excel具备了能组织和管理大量数据的能力。
（5）宏语言功能利用Excel中的宏语言功能，用户可以将经常要执行的操作的全过程记录下来，
并将此过程用一简单的组合按键或工具按扭保存起来。
这样，在下一次操作中，只需按下所定义的宏功能的相应按键或工具按钮即可，而不必重复整个过程。
例如，可以
定义一个打开最后编辑文件且可以自动执行的宏，以后当用户打开Excel后，将自动打开上一次编辑的工作簿。
在Excel中，高级用户可使用VisualBasic语言，进行宏命令的开发。
利用宏命令，用户可以将Excel的下拉菜单和对话框更改或将图形按钮的说明更换，使它们更适合于用户的工作习惯和特殊要求。
（6）样式功能在Excel中，用户可以利用各种文字格式化的工具和制图工具，制作出美观的报
表。
所谓样式，就是将一些格式化的组合用一个名称来表示，以后要使用这些格式化的组合时，只要使用此名称即可，因此可大幅度地节省报表格式化的时间。
（7）对象连接和嵌入功能利用对象连接和嵌入功能，用户可将其它软件（例如画笔）制作的图形插入到Excel
的工作表中。
当需要更改图案时，只要在图案上双击鼠标键，制作该图案的程序就会自动打开，图案将出现在该图形编辑软件内，修改、编辑后的图形也会在Excel内显示出来。
也可以将一个声音文件或动画文件嵌入到Excel工作表中，使工作表变成一幅声形并貌的报表。
（8）连接和合并功能通常，每个工作在一张工作表上执行即可，早期的工作表软件都只能在一张工作表
上执行。
但有时需要同时用到多张工作表，例如，公司内每个分公司每月都会有会计报
-858-
表，要将各分公司的资料汇总起来，就需要用到连接和合并功能。
Excel很容易将工作
表连接起来，并进行汇总工作。
Excel内一个工作簿可以存放许多工作表、图形等，每个工作簿文件最多可以由255张工作表组成。
§2Excel基本操作
2.1Excel操作方法概述要完成任一项Excel操作一般都可以找到三种操作方法：
鼠标操作、菜单操作和键
盘命令操作。
例如，想要将A1单元格的数据复制到A2单元格去，有如下几种操作方法：
（1）鼠标操作法先用鼠标选中A1单元格，然后缓慢移动鼠标到A1单元格的右下角，当鼠标的形状
变为黑色实心“十”字形之后（以后称之为“添充柄”），拖动鼠标到A2单元格，然后放开鼠标，则A1的数据就复制到A2单元格了。
（2）菜单操作法先用鼠标选中A1单元格，选择“编辑”菜单中的“复制”命令，然后用鼠标选中
A2单元格，再选择“编辑”菜单中的“粘贴”命令，数据就复制到A2单元格了。
（3）键盘命令操作法直接用鼠标选中A2单元格，从键盘输入“＝A1”命令，则复制即告完成。
以上是Excel中很典型的三种操作方法。
在实际使用过程中，应根据实际情况，
尽量选择三种方法中最简洁的操作方法，以提高操作速度。
2.2数据的输入输出操作
（1）数据的手动输入建立一个新的Excel文件之后，便可进行数据的输入操作。
Excel中以单元格为单
位进行数据的输入操作。
一般用上下左右光标键，Tab键或用鼠标选种某一单元格，然后输入数据。
Excel中的数据按类型不同通常可分为四类：
数值型，字符型，日期型，和逻辑型。
Excel根据输入数据的格式自动判断数据属于什么类型。
如日期型的数据输入格式为“月/日/年”，“月-日-年”或“时：
分：
秒”。
要输入逻辑型的数据，输入“true”（真）或“false”（假）即可。
（2）公式生成数据Excel的数据也可由公式直接生成。
例如：
在当前工作表中A1和B1单元格中已输入
了数值数据，欲将A1与B1单元格的数据相加的结果放入C1单元格中，可按如下步骤操作：
用鼠标选定C1单元格，然后输入公式“=A1+B1”或输入“=SUM（a1:
b1）”,回车之后即可完成操作。
C1单元格此时存放实际上是一个数学公式“A1+B1”，因此C1单元格的数值将随着A1、B1单元格的数值的改变而变化。
Excel提供了完整的算术运算符,如＋（加）、
－（减）、*（乘）、／（除）、％（百分比）、^（指数）和丰富的函数，如SUM（求和）、CORREL（求相关系数）、STDEV（求标准差）等，供用户对数据执行各种形式的计算操作，在Excel帮助文件中可以查到各类算术运算符和函数的完整使用说明。
（3）复制生成数据Excel中的数据也可由复制生成。
实际上，在生成的数据具有相同的规律性的时候，
大部分的数据可以由复制生成。
可以在不同单元格之间复制数据，也可以在不同工作表
或不同工作簿之间复制数据，可以一次复制一个数据，也可同时复制一批数据，为数据输入带来了极大的方便。
普通单元格的复制结果与公式单元格的复制结果相差较大，下面分别予以说明。
1．普通单元格指的是非公式的单元格。
普通单元格的复制，一般可以按如下步骤进行：
i）拖动鼠标选定待复制的区域，选定之后该区域变为黑色。
Excel可以进行整行、
-859-
-860-
（3）输入文件名之后，用鼠标单击“保存”按扭即可。
§3Excel在描述统计中的应用
在使用Excel进行数据分析时，要经常使用到Excel中一些函数和数据分析工具。
其中，函数是Excel预定义的内置公式。
它可以接受被称为参数的特定数值，按函数的内置语法结构进行特定计算，最后返回一定的函数运算结果。
例如，SUM函数对单元格或单元格区域执行相加运算，PMT函数在给定的利率、贷款期限和本金数额基础上计算偿还额。
函数的语法以函数名称开始，后面是左圆括号、以逗号隔开的参数和右圆括号。
参数可以是数字、文本、形如TRUE或FALSE的逻辑值、数组、形如#N/A的错误值，或单元格引用。
给定的参数必须能产生有效的值。
参数也可以是常量、公式或其它函数。
Excel还提供了一组数据分析工具，称为“分析工具库”，在建立复杂的统计分析时，使用现成的数据分析工具，可以节省很多时间。
只需为每一个分析工具提供必要的数据和参数，该工具就会使用适宜的统计或数学函数，在输出表格中显示相应的结果。
其中的一些工具在生成输出表格时还能同时产生图表。
如果要浏览已有的分析工具，可以单击“工具”菜单中的“数据分析”命令。
如果“数据分析”命令没有出现在“工具”菜单上，则必须通过“工具”菜单中的“加载宏”命令，在“加载宏”对话框中选择并启动它。
3.1描述统计工具
（1）简介此分析工具用于生成对输入区域中数据的单变量分析，提供数据趋中性和易变性等
有关信息。
（2）操作步骤1．用鼠标点击工作表中待分析数据的任一单元格。
2．选择“工具”菜单的“数据分析”子菜单。
3．用鼠标双击数据分析工具中的“描述统计”选项。
4．出现“描述统计”对话框，对话框内各选项的含义如下：
输入区域：
在此输入待分析数据区域的单元格范围。
一般情况下Excel会自动根据当前单元格确定待分析数据区域。
分组方式：
如果需要指出输入区域中的数据是按行还是按列排列，则单击“行”或“列”。
标志位于第一行/列：
如果输入区域的第一行中包含标志项（变量名），则选中“标志位于第一行”复选框；如果输入区域的第一列中包含标志项，则选中“标志位于第一列”复选框；如果输入区域没有标志项，则不选任何复选框，Excel将在输出表中生成适宜的数据标志。
均值置信度：
若需要输出由样本均值推断总体均值的置信区间，则选中此复选框，然后在右侧的编辑框中，输入所要使用的置信度。
第K个最大/小值：
如果需要在输出表的某一行中包含每个区域的数据的第K个最大/小值，则选中此复选框。
然后在右侧的编辑框中，输入K的数值。
输出区域：
在此框中可填写输出结果表左上角单元格地址,用于控制输出结果的存放位置。
整个输出结果分为两列,左边一列包含统计标志项，右边一列包含统计值。
根
据所选择的“分组方式”选项的不同，Excel将为输入表中的每一行或每一列生成一个两列的统计表。
新工作表：
单击此选项，可在当前工作簿中插入新工作表，并由新工作表的A1单元格开始存放计算结果。
如果需要给新工作表命名，则在右侧编辑框中键入名称。
-861-
新工作簿：
单击此选项，可创建一新工作簿，并在新工作簿的新工作表中存放计算
结果。
汇总统计：
指定输出表中生成下列统计结果，则选中此复选框。
这些统计结果有：
平均值、标准误差、中值、众数、标准偏差、方差、峰值、偏度、极差（全距）最小值、最大值、总和、样本个数。
5．填写完“描述统计”对话框之后，按“确定”按扭即可。
3.2直方图工具
（1）简介直方图工具，用于在给定工作表中数据单元格区域和接收区间的情况下，计算数据
的个别和累积频率，可以统计有限集中某个数值元素的出现次数。
例如，在一个有50名
学生的班级里，可以通过直方图确定考试成绩的分布情况,它会给出考分出现在指定成绩区间的学生个数,而用户必须把存放分段区间的单元地址范围填写在在直方图工具对话框中的“接收区域”框中。
（2）操作步骤1．用鼠标点击表中待分析数据的任一单元格。
2．选择“工具”菜单的“数据分析”子菜单。
3．用鼠标双击数据分析工具中的“直方图”选项。
4．出现“直方图”对话框，对话框内主要选项的含义如下：
输入区域：
在此输入待分析数据区域的单元格范围。
接收区域（可选）：
在此输入接收区域的单元格范围，该区域应包含一组可选的用
来计算频数的边界值。
这些值应当按升序排列。
只要存在的话，Excel将统计在各个相邻边界值之间的数据出现的次数。
如果省略此处的接收区域，Excel将在数据组的最小值和最大值之间创建一组平滑分布的接收区间。
标志：
如果输入区域的第一行或第一列中包含标志项，则选中此复选框；如果输入区域没有标志项，则清除该复选框，Excel将在输出表中生成适宜的数据标志。
输出区域：
在此输入结果输出表的左上角单元格的地址。
如果输出表将覆盖已有的数据，Excel会自动确定输出区域的大小并显示信息。
柏拉图：
选中此复选框，可以在输出表中同时显示按降序排列频率数据。
如果此复选框被清除，Excel将只按升序来排列数据。
累积百分比：
选中此复选框，可以在输出结果中添加一列累积百分比数值，并同时在直方图表中添加累积百分比折线。
如果清除此选项，则会省略以上结果。
图表输出：
选中此复选框，可以在输出表中同时生成一个嵌入式直方图表。
5．按需要填写完“直方图”对话框之后，按“确定”按扭即可。
（3）结果说明：
完整的结果通常包括三列和一个频率分布图，第一列是数值的区间范围，第二列是数值分布的频数，第三列是频数分布的累积百分比。
3.3利用Excel绘制散点图
（1）简介散点图是观察两个变量之间关系程度最为直观的工具之一，利用Excel的图表向
导，可以非常方便的创建并且改进一个散点图，也可以在一个图表中同时显示两个以上变量之间的散点图。
（2）操作步骤
数据如图1所示，可按如下步骤建立变量x-y，x-z的散点图。
-862-
（3）结果说明
如图2所示，Excel中可同时生成两个序列的散点图，并分为两种颜色显示。
通过散点图可观察出两个变量的关系，为变量之间的建立模型作准备。
3.4数据透视表工具
（1）简介数据透视表是Excel中强有力的数据列表分析工具。
它不仅可以用来作单变量数据
的次数分布或总和分析，还可以用来作双变量数据的交叉频数分析、总和分析和其它统计量的分析。
（2）操作步骤如图3所示，表中列出学生两门功课评定结果，可按如下步骤建立交叉频数表。
1．选中图3中表格中有数据的任一单元格，然后选择“数据”菜单的“数据透视
表”子菜单，进入数据透视表向导。
-863-
（3）结果说明
如图5的结果所示，数据透视表可以作为一个交叉频数分析工具。
完成数据透视表之后，可按需要修改数据表的显示格式。
例如，如果想要把表格中的频数替换成为百分比数。
可以用鼠标右击频数的任一单元格，选择“字段”子菜单，单击“选项”按扭，将“数据显示方式”替换成为“占总和的百分比”，然后单击“确定”按扭即可。
按同样方式，可将数据透视表修改成为其它不同样式。
3.5排位与百分比工具
（1）简介
-864-
此分析工具可以产生一个数据列表，在其中罗列给定数据集中各个数值的大小次序
排位和相应的百分比排位。
用来分析数据集中各数值间的相互位置关系。
（2）操作步骤1．用鼠标点击表中待分析数据的任一单元格。
2．选择“工具”菜单的“数据分析”子菜单。
3．用鼠标双击数据分析工具中的“排位与百分比”选项。
4．填写完“排位与百分比”对话框，单击“确定”按扭即可。
（3）结果说明输出的结果可分为四列，第一列“点”是数值原来的存放位置，第二列是相应的数
值，第三列是数值的排序号，第四列是数值的百分比排位，它的计算方法是：
小于该数值的数值个数/（数值总个数-1）。
§4Excel在推断统计中的应用
4.1二项分布工具
（1）简介在Excel中想要计算二项分布的概率分布、累积概率，需要利用Excel的工作表函数
BINOMDIST。
函数BINOMDIST适用于固定次数的独立实验，实验的结果只包含成功或失败二种情况，且每次实验成功的概率固定不变。
例如，已知次品概率的情况下，函数BINOMDIST可以计算10个产品中发现2个次品的概率。
以下例子说明如何在Excel中计算二项分布的概率，以及如何建立二项分布图表。
（2）操作步骤
例子如下所示，一个推销员打了六个电话，推销成功的概率是0.3，那么可以按以下步骤建立推销成功次数的概率分布图表。
1．如图6所示，先在Excel之下建立好概率分布表格的框架。
2．语法:
NEGBINOMDIST（number_f,number_s,probability_s）
number_f：
失败次数。
number_s：
成功的临界次数。
probability_s：
成功的概率。
3．举例：
例如，如果要找出5个反应敏捷的人，且已知具有这种特征的候选人的概率为0.3。
以下公式将计算出在找到5个合格候选人之前，需要面试10个候选人的概率
NEGBINOMDIST（10,5,0.3）=0.06871
（4）函数POISSON1．说明：
函数POISSON返回泊松分布。
泊松分布通常用于预测一段时间内事件发生
指定次数的概率，比如一分钟内通过收费站的轿车的数量为n的概率。
2．语法：
POISSON（x,mean,cumulative）
x：
事件数。
mean：
期望值。
cumulative：
为一逻辑值，确定所返回的概率分布形式。
如果cumulative为TRUE，
函数POISSON返回累积分布函数，即随机事件发生的次数在0和x之间（包含0和1）；如果为FALSE，则返回概率密度函数，即随机事件发生的次数恰好为x。
3．举例：
POISSON（2,5,FALSE）=0.084224表明，若某一收费站每分种通过的轿车平均数量为5辆，那么某一分钟通只2辆的概率为0.084224。
（5）正态分布函数NORMDIST1．说明：
正态分布在模拟现实世界过程和描述随机样本平均值的不确定度时有广
泛的用途。
函数NORMDIST返回给定平均值和标准偏差的正态分布的分布函数。
同样可以利用NORMDIST函数建立正态分布密度函数图，这里不再赘述。
2.语法：
NORMDIST（x,mean,standard_dev,cumulative）x：
为需要计算其分布的数值。
mean：
分布的算术平均值。
standard_dev：
分布的标准偏差。
cumulative：
为一逻辑值，指明函数的形式。
如果cumulative为TRUE，函数NORMDIST返回累积分布函数；如果为FALSE，返回概率密度函数。
3．举例：
例如，公式NORMDIST（6,5,2,0）返回平均值为5、标准差为2的正态分布当x=6时概率密度函数的数值，公式NORMDIST（60,50,4,1）返回平均值为50、标准差为4的正态分布当x=60时分布函数的数值。
（6）函数NORMSDIST：
1．说明：
函数NORMSDIST返回标准正态分布的分布函数。
2．语法：
NORMSDIST（z），其中z为需要计算其分布的数值。
3．举例：
NORMSDIST（0）=0.5
（7）函数NORMSINV1．说明：
函数NORMSINV返回标准正态分布累积函数的逆函数。
2．语法：
NORMSINV（probability），其中probability为正态分布的概率值。
3．举例：
NORMSINV（0.5）=0
（8）t分布函数TDIST1．说明：
函数TDIST返回t分布的概率。
t分布用于小样本数据集合的假设检验。
使
用此函数可以代替t分布的临界值表。
2．语法：
TDIST（x,degrees_freedom,tails）x：
为需要计算分布的数字。
degrees_freedom：
为表示自由度的整数。
-867-
4.5假设检验
（1）简介
假设检验是统计推断中的重要内容。
以下例子利用Excel的正态分布函数
-869-
1.选择“工具”菜单的“数据分析”子菜单，双击“t-检验:
双样本等方差假设”
选项，则弹出图13（a）所示对话框。
-872-
-873-
-874-
（3）结果分析
立的话，得到如上样本结果的概率是19.442%,即得到以上样本并不是小概率事件，同
样也得到不能拒绝四个总体均值相等的假设的结论。
按相似方法可进行无重复双因素方差分析，有重复双因素方差分析。
4.10线性回归分析
（1）简介线性回归分析通过对一组观察值使用“最小二乘法”拟合直线，用来分析单个因变量
是如何受一个或几个自变量影响的。
例子如图25所示，表中是我国1987年至1997年的布匹人均产量和人均纱产量，试用线性回归分析的方法分析两组数据之间的关系。
-881-
接使用回归分析要更为可靠合理。
参看以上分析步骤，用类似的方法还可以进行月份时
间序列、双循环变动时间序列等的分解分析和预测。
-882-
求最优解的步骤如下：
1）在图37中选取“工具”、“规划求解”，画面显示如图38所示。
-883-
-884-
设柳丁、木瓜、香蕉、胡萝卜的最佳需求量分别为xi（i=1,2,3,4）个。
建立如下线
性规划模型。
minz=20x1+30x2+15x3+25x4
s.t.5x1+50x2+10x3+80x4≥50015x1+30x2+40x3+20x4≥250
90x1+20x2+30x3+30x4≥600
10x1+15x2+10x3+50x4≥300
xi≥0且为整数，i=1,2,3,4
建立一个如图43所示的工作表。
-885-
6）选择“确定”，完成输入。
7）由“规划求解参数”框，选择“求解”。
8）选择“确定”，计算结果如图44所示。
2）选取“格式”、“单元格…”、“数字”。
3）在“分类”框中选定“自定义”的0格式。
4）选择“确定”，发现画面显示结果如图45所示。
-887-
升级会员 