统计学原理试验内容指导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虽然提供了
升级会员 