统计学原理试验内容指导yong.docx
- 文档编号:30051525
- 上传时间:2023-08-04
- 格式:DOCX
- 页数:46
- 大小:5.24MB
统计学原理试验内容指导yong.docx
《统计学原理试验内容指导yong.docx》由会员分享,可在线阅读,更多相关《统计学原理试验内容指导yong.docx(46页珍藏版)》请在冰豆网上搜索。
统计学原理试验内容指导yong
统计学原理实验指导
——Excel在统计学中的应用
中北大学经济与管理学院
实验一EXCEL的基本统计功能
MicrosoftEXCEL是一个设计精良、功能齐全的办公软件。
它除了具有我们常用的办公功能,如通过电子表格的形式对数字数据进行组织和计算;将数字数据转化为可视化的图表和数据库管理功能外。
它还是一个十分强大而且非常易用于使用数据统计和预测工具。
EXCEL的统计功能分为基本统计和预测两部分。
我们将按照资产评估统计顺序逐一介绍。
这一节将结合评估数据整理的实例,重点介绍EXCEL2000的基本统计功能。
EXCEL2000提供的基本统计主要包括描述性统计、频数统计、等级和百分数等方法。
这些统计方法主要利用EXCEL统计函数或数据分析中的描述统计过程来实现。
一、描述统计特征值
描述性统计可通过EXCEL提供的统计函数或加载宏来完成,下面我们分别介绍EXCEL的描述性统计功能。
(一)用EXCEL统计函数进行特征值计算
EXCEL描述性统计函数主要包括一般统计函数,集中趋势函数和变异统计函数:
如图3–5所示,单元格区域B4:
B53是50名工人某日加工零件个数资料(例3–2)。
C4:
C19是一些描述性统计量的说明。
D4:
D19是一般统计结果。
其做法有如下两种。
1.在单元格D3中输入公式“=COUNT(B4:
B53)”并回车,得到B4:
B53区域中非空数值型数据的个数统计;在单元格D4中输入公式“=SUM(B3:
B53)”并回车,得到50名工人日加工零件数的总和;同样,在D5:
D15单元格中中分别输入MAX、MIN、AVERAGE、MEDIAN、GEOMEAN、HARMEAN、AVEDEV、STDEV、VAR、KURT和SKEW函数,分别得到50个数据中的最大值、最小值、平均值、中位数、几何平均数和调和平均数及变异统计的平均差、标准差、方差峰度和偏度。
图3–5统计函数
2.首先在EXCEL2000的系统工具栏中选择“插入”中的函数,其次在函数对话框中选择所计算的函数,然后根据函数向导提示一步步的完成。
(二)宏程序进行特征值计算
除了利用上述统计函数完成统计数据分析外,EXCEL还在数据分析宏程序中提供了一个描述性统计过程。
对于例3–2,我们也可以利用这个“描述性统计”宏过程来计算,其方法更为简单。
我们点击图中的“描述性统计”过程,“描述性统计”过程的菜单如图3–6A,3–6B,3–6C所示。
我们在“输入区域”中输入数据所在单元格区域“B3:
B53”,选择输出“汇总统计”和“平均数值信度”,在“K个最大值”和“K个最小值”选择中,选择系统默认值“1”,表示选择输出第1个最大值和第1个最小值。
输入“输出区域”为E1单元格,然后按“确定”,即得到图所示特征值计算结果,该结果与图中利用统计函数计算的结果是一致的。
图3–6A数据分析宏程序
图3–6B描述性统计过程对话框
图3-6C描述性统计结果
二、频数统计
EXCEL提供多种统计分组方法,如利用函数FREQUENCY进行频数统计;利用“数据分析”中的“直方图”宏程序进行频数分析;还有利用“数据透视表”进行两个或两个以上变量的交叉分组等。
我们在此只讨论与统计密切相关的两种方法。
(一)FREQUENCY函数
首先,以本章第二节表3–1“顾客购买饮料的品牌名称”为例,将50个原始数据建立在EXCEL工作表中。
如图3–7所示。
图3–7顾客购买饮料的品牌名称记录
在图3–7中,A3:
A12-E3:
E12是调查员在某天对照50名顾客购买饮料的品牌进行的记录,G3:
G8是通过键盘输入的拟进行频数统计代号。
首先,将光标移至H3单元格,按住鼠标左键,拖曳光标覆盖H3:
H8区域(如图3–7所示)。
点击EXCEL插入菜单中“函数”选项,在“统计”类函数中选择“FREQUENCY”函数(如图3–8所示。
)在“Data-array”中输入原始数据阵列“A3:
E12”,在“Bins-array”中输入分组组距阵列“G3:
G8”,然后同时按“Ctrl-Shift-Enter”键即得如图3–9所示频数统计结果。
“Ctrl-Shift-Enter”是EXCEL特别针对矩阵运算的回车符。
统计结果如图3–9。
图3–8
图3–10
其次,EXCEL除能进行品质数据和单变量值的频数统计外,还能进行分组频数的统计。
EXCEL的分组频数统计与品质频数统计操作过程大体相同。
但在分组时应注意:
EXCEL的FREQUENCY函数进行频数统计时采用的是“上限不在内”的累计方法,因此,图3–10以本章例3–2的数据为例,统计表中的E4:
E8区域内的组距值与F4:
F8中的实际组距值是一致的。
如果还想进一步计算频数百分比或累计次数,可以应用EXCEL的常用功能,通过求和、求商求得;也可以通过数据分析中宏过程完成。
图3–11是用EXCEL常用功能计算的百分数和向上累计次数。
图3–11
(二)数据分析直方图宏
对于上面同样的例子,利用“数据分析”宏中提供的“直方图”宏过程进行统计分组的步骤如下:
◆首先,点击“工具栏”,然后点击“数据分析”宏,在“数据分析”宏中选择“直方图”:
(如图3–12所示)。
图3–12
◆在“直方图”中的“输入区域”输入B4:
B53,“接收区域”输入“F4:
F8”,选定“输出区域”为同一工作表中的由D1为起点的区域,然后选择“图表输出”并点击“确定”即完成统计分组和直方图的制作过程。
输出结果如图3–13A,3–13B所示。
◆在图3–13A中如果选择累计百分数和输出图表,将输出中位数折线图和频数累计频率百分比。
如图3–14。
◆如果只想输出折线图和频数、累计频率。
可以用鼠标左键双击某一柱形图,待选中后按Delete键即可。
图3–13B
图3–14
制作统计汇总表
实例一
某24小时便利商店记录了某天内24小时的顾客人数,如图2-1所示。
请制作统计汇总表。
作法
1、打开Excel新文件后,键入数据,如图2-1所示,并存于“集中指标.XLS”的文件中。
将该工作表命名为“便利商店”,以鼠标指向左下角的“Sheet1”,然后双击,“Sheet1”就会变成黑色,表示等待编辑,然后键入“便利商店”,即可将该工作表命名为“便利商店”。
2、选取“工具”菜单中的“数据分析”命令,如图2-2所示(如没发现“数据分析”命令,工具----加载宏----分析数据库,安装即可)。
3、出现图2-3所示的对话框时,选取“描述统计”,然后单击右上角的“确定”按钮。
4、在图2-4内“输入区域”的文本框中输入所欲分析数据的地址,在此为B1:
B25。
然后选取“逐列”(此为默认),表示数据是依照字段存放,并请选取“标志位于第一行”,因为在此,B1是标记“顾客”。
如果要将输出的结果置于数据一的窗体上,则可选取“输出区域”,并键入地址,如C1。
如果要将结果置于新工作表或新活页簿,则可选取“新工作表组”或“新工作簿”。
关于“汇总统计”部分,请务必选取,不然输出的结果就不会包含如平均数、平均数的标准误差、中位数、众数、标准差、方差、峰度、偏态、范围、最小值、最大值、总和、计数、第K个最大值、第K个最小值以及置信度等统计项目。
至于“平均数置信度”的默认值是95%,如果要修改,则先选取后,直接键入所要的置信度,如90%。
“第K大值”和“第K小值”指的是依照大小排列后,第K个最大值或最小值,默认值为1,也就是极大值和极小值。
由于希望将汇总统计输出置于新的工作表内,并将该工作表命名为“便利商店汇总统计”,因此选取了“新工作表”,键入“便利商店汇总统计”,并设定“平均数置信度”为95%,如图2-4所示,单击“确定”按钮后,即可获得如图2-5所示的结果。
在一天的24h内,平均每小时的顾客人数为21.58人,中值为13.5,模式为0,样本方差和标准偏差(将在下章说明其意义)分别是378.60和19.46。
最大值是57,最小值是0,区域(全距)为57。
峰值、偏斜度标准误差等留待以后说明。
制作排位与百分比表
实例二
身为便利商店的店长一定会想知道何时顾客较多,何时较少,以利人力调度。
换句话说,就是每个小时顾客人数的排序。
而且也会想知道如果由小到大排列,累积的百分比是多少。
如果是最大值,等级是1,(累积)百分比就是100%。
作法
1、回到“便利商店”的画面,选取“工具”→“数据分析”→“排位与百分比排位”,如图2-6输入。
`
2、单击右下角的“确定”按钮即可得到如图2-7所示的结果。
在图2-7中的“排位”这一字段内,发现原来编号(原定序点)第19点,顾客是57,排位是1,在“百分比”上也就是100%。
事实上,如果只是要知道每小时顾客人数的排序,可以用“RANK”函数,作法如下:
1、光标移至“便利商店”的C2上,选取“插入”菜单中的“函数”命令,并选取“RANK”函数,如图2-8所示。
1、单击右下角的“确定”按钮后,得到如图2-9所示的对话框。
在“Number中键入B2,因为我们想知道置于B2的分数所占的名次。
在“Ref”中键入“B$2:
B$25”,因为这24小时的顾客人数置于B2至B25中,至于$符号,是用来固定地址,使之不会因为复制而变动地址。
在图2-9的左下角出现“计算结果=21”,表示该顾客人数排名21。
2、单击图2-9中的“确定”按钮后,即可得到如图2-10所示的结果。
将光标移至C2,并置于C2的右下角,即可出现“+”,表示可以复制,将之拖曳至C25,即可完成复制,然后就可以得到如图2-11所示的结果,这就是顾客人数的排名。
制作成绩等级表
实例三
身为老师,常常为了计算学生的学业成绩费尽心机,除了要记录学生段考、期末考、随堂考的分数外,还要计算总平均,排定名次,并分为五等级:
优、甲、乙、丙、丁。
作法:
1、图2-12所示,输入15位学生的学号,两次段考、期末考和三次随堂考的成绩。
两次段考成绩各占学期总平均的20%,期末考占40%,三次随堂考共占20%。
2、在H3内键入“=B3*0.2+C3*0.2+D3*0.4+(E3+F3+G3)/3*0.2”,以计算第一位学生的总平均分数,然后复制至所有15位学生,即可得到所有学生的学期总平均分数。
3、接下来就是利用总平均分来排名次,在I3中键入“=RANK(H3,H$3:
H$17)”,即可得到第一位学生的名次,然后将其复制至I17,就可以得到所有学生的名次,如图2-13所示。
如果事先界定总平均90分以上者为优等,80分以下者为甲等,70分以上者为乙等,60分以上者为丙等,60分以上者为丁等,那么就在J3中键入“=IF(H3>=90,“优”,IF(H3>=70,“乙”,IF(H3>=60,“丙”,“丁”))))”。
即可得到第一位学生的等级,然后复制至所有学生。
若要计算班上15位同学中,有多少位同学得优等、甲等、乙等、丙等和丁等,可以利用“COUNTIF”函数来计算,请在L3中键入“=COUNTIF(J$3:
J$17,“优”)”,即可得到优等的人数共3人。
同理,在L4中键入“=COUNTIF(J$3:
J$17,“甲”)”,则得到甲等人数共5人。
以次类推,结果如图2-14所示。
其他集中指标的计算
以下利用Excel提供的函数,计算几何、调和、截尾、温塞平均数。
实例四
研究者调查了20个家庭的月收入(单位:
万元),如图2-15所示,请计算各种集中指标。
作法
1、找一适当地址,如B22键入“=AVERAGE(B2:
B21)”即可得算数平均数。
键入“GEOMEAN(B2:
B21)”得几何平均数。
键入“HARMEAN(B2:
B21)”得调和平均数。
2、除直接键入函数外,也可以通过粘贴函数来计算。
首先将光标移至适当地址,如B23中,然后选取“插入”菜单中的“函数”,选取“GEOMEAN”,如图2-16所示,并单击“确定”按钮,即可出现如图2-17所示的对话框,在Number1中键入“B2:
B21”,确定后,就可以得到几何平均数。
3、同样也可以利用AVERAGE和HARMEAN求得平均数和调和平均数。
4、Excel并没有提供函数以计算截尾平均数和温塞平均数,不过有函数QUARTILE可以计算
1和
。
首先在适当地址如B25中键入“=QUARTILE(B2:
B21,1)”,其中1表示第一个四分位数。
同理,在B26中键入“=QUARTILE(B2:
B21,3)”,其中3表示第三个四分位数。
可以利用插入函数QUARTILE的方式,并按如图2-18所示内容键入,即可得到
。
最后可得图2-19所示的结果,
为5.75,
为10.25。
5、图2-19中,算数平均数为12.7。
这是数据中的最后一笔家庭的月收入高达100万元,使得算数平均数受到该值的影响而失真。
以下说明如何计算截尾平均数和温塞平均数。
在适当地址如C2中键入“=IF(B2B$26,FALSE,B2))”其中B$25放的就是
,B$26放的就是
。
如果B2放的值小于,或是大于
,就用FLASE取代它,否则就用原来的B2,然后复制至C21(复制的方法可先将光标移至B2的右下角,就会出现+号,然后拖曳至C21),如图2-20所示。
6、然后计算这20个值的算数平均数,就是截尾平均数:
“=AVERAGE(C2:
C21)”,得7.9万元。
7、同样也可以计算温塞平均数,首先在适当地址如D2键入“=IF(B2B$26,B$26,B2))”,这意味着B2的值若小于
的话,就D2中存入
;B2的值若大于
的话,就在D2中存入
;不然就放入B2,并复制至D21,如图2-21所示。
5、计算这20个值的算数平均数,就是温塞平均数:
“=AVERAGE(D2:
D21)”,得7.95万元。
截尾平均数和温塞平均数相当接近,不过和原先的算数平均数则相去甚远。
变异指标的计算
实例五
为了了解小学生每天花在看电视的时间大概有多久,某老师抽样了20位小学四年级的学生,请他们记录每天看电视的时间。
得到如图3-3所示的数据(单位:
分钟)。
请问
(1)这七天的平均数、区域、四分位数、平均绝对离差、标准差、方差各为多少?
(2)从这七天的平均数和标准差,你得到什么结论?
(3)平均来说(不分假日和非假日),每位学生花多少时间看电视?
作法
1.如图3-3所示,将所有的数据置于“电视”的工作表内。
2.Excel提供了函数AVERAGE以计算平均数;MAX计算最大值,MIN计算最小值,两者相减为区域;QUARTILE计算四分位数;STDEV计算样本标准差;VAR计算样本方差。
计算第一位学生的各种指标,该学生的数据放于B2:
H2中,在适当地址如I2中键入“=AVERAGE(B2:
H2)”,即可得到第一位学生收视时间的平均数。
在J2中键入“=MAX(B2:
H2)-MIN(B2:
H2)”,即可得到该学生的区域。
在K2中键入“=QUARTILE(B2:
H2,3)-QUARTILE(B2:
H2,1)”,即可得到该学生的四分位距(即
)。
在L2中键入“=AVEDEV(B2:
H2)”即可得到该学生的平均绝对离差。
在M2中键入“=STDEV(B2:
H2)”即可得到该学生的样本标准差。
在N2中键入“=VAR(B2:
H2)”即可得到该学生的样本方差。
然后复制至所有的学生,如图形3-4所示。
基本上可以看出平均绝对离差和标准并不相同,不过差不多。
同理,也可以计算学生们在这七天内收视时间的平均数,在B22中键入“AVERAGE(B2:
B21)”,即可得到所有学生在星期一收视时间的平均数。
在B23中键入“=MAX(B2:
B21)-MIN(B2:
B21)”,即可得到星期一收视时间的区域。
以此类推。
可以求得星期一的四分位距、平均绝对离差、标准差和方差。
然后复制至星期日和平均数,如图3-5所示。
就这七天收视时间的平均数而言,随着周末接近看电视的时间有增加的趋势,例如从平时的半小时左右到周末的约一小时。
不过就标准差而言,周末(周六和周日)的标准差大概是平时的2倍到3倍。
换句话说,在周末的时候,学生们看电视的时间呈现很明显的差异,有的学生花很多的时间看电视。
但同样的也有很多学生几乎不看电视,因此标准差比平时非假日时大。
这可能是有些学生利用周末度假去,因此无法看电视。
而没去度假外出的学生,则比平常还要花更多的时间看电视。
不过这种“度假外出”的假设,仍需进一步的求证。
I22中46.85714分钟就是所有学生平均每天收视的时间的平均数。
I26中的24.06就是其标准差。
在描述统计方面,除了用数字说明集中和分散的情形外,也可以进一步绘制分布图,让读者对数据整体情形可以一目了然。
如果是离散变量,例如全校同学的出生月份,可以用柱形图表示各个月份的人数。
利用Excel制作各种统计图的步骤,请参见第19章。
如果是连续变量,如全校同学的身高,也可仿效柱形图呈现所有同学身高的分布情形。
此时由于是连续变量,因此不叫做柱形图,而称为直方图(histogram)。
通常在绘制直方图时要先决定分为几组,以及如何分法。
如果不指定,Excel会内定分组,不过并不见得适合你的需要。
绘制直方图
实例六
假设班上的15个同学,学业成绩如图3-6所示。
因为成绩介于50至93之间,因此决定分为5组,其分法为第一组为59以下,第二组为60至69,第三组为70至79,第四组为80至89,第五组为90至99,请绘制直方图。
作法
1.在进行直方图分析时,请将59,69,79,89,99分别键入到C2至C6中,如图3-6所示。
2.选取“工具”→“数据分析”→“直方图”,打开如图3-7所示的对话框,键入适当的值,并选取“累积百分率”和“图表输出”(通常并不选取“柏拉图”,因为那会使得直方图依照次数多少排列而不是依照分数高低排列,所以没多大意义。
读者不妨选取,看看直方图变得怎样)。
最后就可以得到如图3-8所示的结果。
3.图3-8中的直方图有间隙,因此严格说来并不是直方图,而是柱形图。
因此应该取消间隙。
可点选长条,按两下就可出现图3-9。
然后选取“选项”标签,并将“间距宽度”改为0,如图3-10所示,单击“确定”按钮后即可得到没有间隙的直方图。
从直方图3-10中可以发现分数的分布左右对称,而且中间的分数(70至79分)的人最多,然后往两旁渐少。
出题的老师应该很满意这种分布。
因为大多数的人在60至90之间,而60分以下和90分以上的人相对的很少。
这表示考题能够区分出能力高和能力低的人。
如果分数集中在高分(例如大家都考90来分),那么就表示题目很简单。
反之,如果分数集中在低分(例如大家都考60来分),那就表示题目太难。
这两种情况都表示无法有效区分出学生的能力高低。
此外,直方图中的曲线代表各组累积的百分比。
实验二EXCEL区间估计与假设检验
一、CONFIDENCE(置信区间)函数
CONFIDENCE(alpha,standard-dev,size)
返回总体平均值的置信区间。
Alpha(即α)是用于计算置信度的显著水平参数。
置信度等于(1-α),亦即,如果α为0.05,则置信度为0.95。
Standard-dev数据区域的总体标准差,假设为已知(实际中,总体标准差未知时通常用样本标准差代替)。
Size样本容量(即n)。
如果假设α等于0.05,则需要计算标准正态分布曲线(1-α=0.95)之下的临界值,查表知其临界值为±1.96。
因此置信区间为:
以某厂对一批产品的质量进行抽样检验为例,抽样数据和要求如下:
采用重复抽样抽取样品200只,样本优质品率为85%,试计算当把握程度为90%时优质品率的允许误差。
我们可以在EXCEL中分别在:
◆B1单元格中输入样本容量200;
◆B2单元格中输入样本比率85%;
◆在B3单元格中输入计算样本比率的标准差公式“=SQRT(B2*(1-B))”;
◆在B4单元格输入α为10%;
◆在B5单元格中输入表达式:
“CONFIDENCE(B4,B3,B1)”,即得到
等于4.15%。
CONFIDENCE函数的应用如图1和图2所示。
图1总体优质品率的区间估计
图2CONFIDENCE函数
二、方差未知时一个总体均值的t检验
按照例6–18,将10个样本资料分别输入到B1:
B10单元格中。
◆在单元格B11中键入公式“=AVERAGE(B1:
B10)”并回车得到均值;
◆在单元格B12中键入公式“=STDEV(B1:
B10)”并回车得到标准差;
◆在单元格B13中键入公式“=COUNT(B1:
B10)”并回车得到样本数;
◆在单元格B14中键入公式“=(B11-200)/(B12/SQRT(B13))”并回车得到t值,其中“200”是题目中给出的总体均值;
◆在单元格B15中键入公式“=TINV(0.05,B13-1)”得到α=0.05,自由度=9的临界值。
图3t检验
从图3的结果来看,在自由度为9时,t(=2.62)>t0.05(=2.26),因此,应该拒绝H0假设,接受“在新工艺下,这种电子元件的平均值有所提高”的假设。
实验三EXCEL在相关与回归分析中的应用
一、相关图的绘制
将表7–1中的资料建立EXCEL工作表,如图1所示。
图1表7–1的EXCEL工作表
制作相关图的步骤如下:
选择区域A1:
B11,如图1所示;
◆点击EXCEL图表向导;
◆在“图表类型”中选择“XY散点图”;如图2。
◆在“子图表类型”中选择第一种散点图,并点击“下一步”,即可得到图2和3。
图2散点图的制作
图3散点图的制作
◆点击“完成”,并对图形进行修饰编辑,最后得到如图4所示广告投入与月平均销售额之间的散点图。
图4广告投入与月平均销售额的散点图
二、相关系数
在EXCEL中,相关系数函数和相关系数宏提供了两种计算相关系数的方法。
(一)相关系数函数
在EXCEL中,CORREL函数和PERSON函数提供了计算两个变量之间的相关系数的方法,这两个函数是等价的。
与相关系数有关的函数还有RSQ(相关系数的平方,即判定系数r2)和COVAR(协方差函数)。
在这里我们以CORREL函数和表7–1中资料为例,介绍利用函数计算相关系数的方法。
◆首先,点击EXCEL函数图钮“fx”,选择“统计”函数;
◆在统计函数点击“CORREL”,进入函数向导;
在“array1”中输入第一个变量“广告投入”的数据区域A2:
A11“array2”中输入第二个变量“月均销售额”的数据区域B2:
B11样,即可在当前光标所在单元格显示函数的计算结果。
如图5所示。
图5CORREL函数计算相关系数
(二)相关系数宏
在EXCEL数据分析宏中,EXCEL专门提供了计算相关系数宏过程。
利用此宏过程,可以计算多个变量之间的相关矩阵。
仍然以表7–1中资料为例,利用相关系数宏计算相关系数矩阵的过程如下:
◆点击EXCEL“工具”菜单,选择“数据分析”过程;
◆在“数据分析”宏过程中,选择“相关系数”过程。
如图6所示;
◆在“输入区域”中输入两个变量所在区域A2:
B11,数据以列排列,输出区域选择在同一工作表中的以D1:
E5区域里。
计算结果如图7所示。
图6相关系数宏
图7利用相关系数宏计算的相关系数矩阵
三、回归分析宏
除了回归分析宏外,EXCEL虽然提供了
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 统计学 原理 试验 内容 指导 yong
![提示](https://static.bdocx.com/images/bang_tan.gif)