1、项目十项目十 Excel在统计中的应用在统计中的应用学习目标目录 1任务一 Excel在统计整理中的应用2任务二 Excel在综合指标分析中的应用3任务三 Excel在抽样中的应用4任务四 Excel在时间序列分析及预测中的应用 5任务五 在统计指数中的应用6任务六 在统计指数中的应用Eccel在统计整理中的应用任务一任务一任务一 Excel Excel在统计整理中的应用在统计整理中的应用统计整理是统计分析的基础，同时也是统计调查的继续，在整个统计工作和研究过程的中间环节，起承上启下的作用。统计图是一种表现统计资料的非常直观有效的手段，统计整理中涉及大量数据的定性分析，因而绘制统计图具有重要价
2、值。在EXCEL中可以利用“插入”选项卡和数据分析工具绘制出大量统计图，为进一步分析数据指明方向。Excel在数据的预处理、图形的绘制等方面具有相当出色的作用：其内置函数数量多且使用方便。Excel具有卓越的自动填充与数据编辑、查询功能，并可迅速建立子数据清单：图形绘制功能非常强大，修饰加工的图形尤其美观：单元格数据与数据分析结果具有“联动”关系，改变其中一个单元格数据，与之相关的Excel公式或图表就会发生相应的改变，具 有“即改即见”的效果。此功能在目前的SAS、SPSS等统计软件尚无法实现。此外，Excel也能完成绝大多数常用统计分析功能，中文界面，无需编程，且一般的计算机内均安装有此软
3、件（OFFICE）。因此，在统计学原理的教学中，经常会使用EXCEL来辅助教学，本节就其在统计整理中图形绘制作一个介绍。需要指出的是这里使用的是OFFICE2016版本的EXCEL软件。Eccel在统计整理中的应用任务一现有某高校现有某高校128128名男生的身高数据名男生的身高数据（表（表10-110-1）。试使用。试使用ExcelExcel软件作统计图展示数据软件作统计图展示数据表表10-110-1Eccel在统计整理中的应用任务一一、直方图的绘制一、直方图的绘制具体步骤如下：具体步骤如下：（1）打开Excel数据表格，在B19处输入“各组上限”字样，这里将数据分为8组，分别是164，16
4、8，172，176，180，184，188，192。组距为4。（2）打开“数据”菜单栏，选中“数据分析”；如果这里没有找到“数据分析”栏，可以在“文件”菜单栏中找到“加载项”，在其中找到“分析工具库”，然后转到将“分析工具库”的复选框选中，这时便可以在“数据”菜单栏中找到“数据分析”选项了。（3）在弹出的“数据分析”对话框中选择“直方图”，如图10-110-1所示Eccel在统计整理中的应用任务一一、直方图的绘制一、直方图的绘制图图(10-1)(10-1)Eccel在统计整理中的应用任务一一、直方图的绘制一、直方图的绘制（4）单击“确定”，然后弹出“直方图”对话框中。在“输入区域”中输入原始数
5、据所在的单元格，本例选择原始数据的区域是A2：H17；在接收区域选择第一步中确定的各组上限值，即B20：B27；在“输出区域”中选择频数分布表的起点单元格，最后勾选“累计百分率”和“图表输出”复选框，如图10-210-2，单击“确定”。得到如图10-310-3所示的结果。图图(10-2)(10-2)Eccel在统计整理中的应用任务一一、直方图的绘制一、直方图的绘制 结果图：结果图：图图(10-3)(10-3)Eccel在统计整理中的应用任务一二、折线图的绘制二、折线图的绘制具体步骤如下：具体步骤如下：（1）首先将上面的数据分组结果整理出完整的闭口组分组数据，分别是160-164,164-168
6、,168-172，172-176，176-180，180-184，184-188，188-192，以及对应的频数数据。（2）选中频数，点击“插入”菜单栏，选择“图表”，在弹出的“插入图表”的对话框中选择“所有图表”，然后找到“折线图”，然后出现7种可选的模板，这里选择“带数据表及的折线图”，然后单击“确定”。如图（10-410-4）图图(10-4)(10-4)Eccel在统计整理中的应用任务一二、折线图的绘制二、折线图的绘制（3）出现的折线图较简单，所有这里需要进一步处理，点击折线图右边的加号，出现很多的可选“图表元素”，这里选中“坐标轴”，“坐标轴标题”，“图表标题”，“数据标签”以及“网络
7、线”中的“主要水平网络线”和“主要垂直网络线”的复选框。在折线图上标题和坐标轴标题上分别填入正确的名称。结果如图10-510-5所示。图（图（10-510-5）（4）折线图横坐标还是1-8不符合要求，所以这里选中折线图右击，选择“选择数据”，在弹出的对话框中，在“水平（分类）轴标签”下单击“编辑”，这里选择身高的分组数据及B31：B38，单击“确定”，如图10-610-6所示。最后单击确定，得到比较好的折线图。Eccel在统计整理中的应用任务一二、折线图的绘制二、折线图的绘制 图（图（10-610-6）“选择数据源选择数据源”对话框对话框 图图(10-7)(10-7)折线图结果折线图结果 Ec
8、cel在统计整理中的应用任务一三、饼图的绘制三、饼图的绘制具体步骤如下：具体步骤如下：（1）首先选择上述原始数据分组后的结果作为画图的数据，选中身高、人数数据及B30:C38，点击“插入”菜单栏，然后选择“图表”，在弹出的“插入图表”对话框中，选择“所有图表”，再选择“饼图”，这里显示有5中模板。选择“三维饼图”，如图10-8所示，单击确定。图（图（10-810-8）“插入图表插入图表”对话框对话框Eccel在统计整理中的应用任务一三、饼图的绘制三、饼图的绘制（2）进一步美化饼图，选中饼图后面的加号，点击“图表标题”、“数据标签”、“图例”选择右的复选框，最终得到如图10-10的饼图。从上述的
9、分析绘制过程中可以看出，Excel在图形绘制功能方面非常强大，修饰加工的图形尤其美观，绘制方法简单易懂。熟练的使用Excel，会使对原始数据的统计整理变得简便易行。图图（10-910-9）人数占比饼图人数占比饼图Eccel在统计整理中的应用任务一三三、饼饼图的绘制图的绘制图图（10-1010-10）饼图结果饼图结果Eccel在统计指标分析中的应用任务二科研人员正在三块试验田中种植一种新型西瓜，为研究这种西瓜的特征属性，现在2块试验田中根据栽种的比例分别采摘若干个西瓜，分别对其外观色泽，根蒂形态，敲击时产生的声音，表面的纹理，脐部形态，手的触感，西瓜密度以及含糖量数据进行了测定，现数据如表表10
10、-210-2所示：表表-10-2-10-2Eccel在统计指标分析中的应用任务二一、总量指标计算一、总量指标计算西瓜数据中可以看出有6个品质标志，3个数量标志。所以可以做一些简单的统计计算，以便划分总体。现在假设含糖量大于0.35为比较甜，试统计摘取的西瓜中有多少个满足条件的西瓜。由于这是有条件的筛选样本，因此需要借助Excel中的SUMIF函数，该函数可以对范围 中符合指定条件的值求和。具体用法具体用法：SUMIF(range,criteria,sum_range)，具有以下参数：range区域，必须必需。根据条件进行计算的单元格的区域。每个区域中的单元格必须是数字或名称、数组或包含数字的引
11、用。空值和文本值将被忽略。criteria必需填写。用于确定对哪些单元格求和的条件，其形式可以为数字、表达式、单元格引用、文本或函数。例如，条件可以表示为32、“32”、B5、“32”、“苹果”或 TODAY()等等。重要:任何文本条件或任何含有逻辑或数学符号的条件都必须使用双引号(“”)括起来。如果条件为数字，则无需使用双引号。最后一个参数sum_range可选参数。要求和的实际单元格（如果要对未在 range 参数中指定的单元格求和）。如果省略sum_range 参数，Excel会对在range参数中指定的单元格（即应用条件的单元格）求和。具体操作具体操作：在Excel工作簿中A20单元格
12、内输入“=SUMIF(I3:I18,=0.35,J3:J18)”，其中I3:I18是含糖量数据的位置，也就是条件变量值的所在位置，第二个参数是我们设置的条件“=0.35”,最后一个参数需要计算求和的数据区域。这样回车之后便可以得到数据了，如图图10-1110-11所示Eccel在统计指标分析中的应用任务二图图10-11 10-11 满足条件的数据求和操作满足条件的数据求和操作Eccel在统计指标分析中的应用任务二二、相对指标计算二、相对指标计算总量指标是相对指标的基础，相对指标需要有总量指标才能计算。本案例中涉及到消费者挑选西瓜的问题，大家知道如何挑西瓜是一门学问，这里人们总结了一些小经验：颜
13、色青绿色为好，白绿色不好、根蒂蜷缩的西瓜比较新鲜、敲击声是清脆的声音为熟瓜、花纹要漂亮清晰、瓜底部圆圈越小越好，要稍凹、瓜皮过软或掐不动的不好。这里就上述经验在A、B试验田中的西瓜作简单对比。按照经验，这里将各个变量值按照经验分别统计，然后计算出百分比。具体步骤，首先制作如表表10-310-3所示的表格：表表10-3 10-3 汇总表汇总表Eccel在统计指标分析中的应用任务二二、相对指标计算二、相对指标计算首先分别统计A、B两个区域的总数，利用SUM函数简单的统计J3:J10和J11:J18区域即可。然后分别利用SUMIF函数计算满足色泽为青绿、根蒂蜷缩、敲声清脆、纹理清晰、脐部稍凹的数量。
14、由于计算涉及的计算较多，这里仅以色泽为青绿一个为例讲解。在N5位置及青绿的数量下面的单元格内输入“=SUMIF(B3:B10,N3,J3:J10)”即：B3:B10是A区域色泽变量值所在位置，筛选条件的比较数值区域；N3即为“青绿”；J3:J10是最后需要统计数量的变量值区域，这样回车后便得到了色泽青绿的A区域的总量指标。以此类推，计算出其他所有的总量指标，结果如图图10-1210-12所示。图图10-12 10-12 汇总表数据汇总表数据Eccel在统计指标分析中的应用任务二二、相对指标计算二、相对指标计算从图图10-1210-12可以看出，A、B两个区域的总数不同，直接比较每个指标值下面的
15、总量指标不方便。因此这里需要借助简单的相对指标计算结果分析A、B两区域的西瓜品种。接着，计算每个指标的比例，在青绿的比例%的单元格中输入“=N5/M5*100”即用每个变量的标志值除以总数。最后的结果如图图10-1310-13所示：图图10-13 10-13 相对指标数值结果相对指标数值结果通过图图10-1310-13可以看到在需要分析的5个特征中，色泽青绿、根蒂蜷缩、敲声清脆相对指标均是B区域的西瓜较好；而在纹理清晰、脐部稍凹两个相对指标的比较重A区域的西瓜较好。可见比较相对指标可以弥补总量指标在数值分析中的某些不足，综合应用总量指标和相对指标可以使分析变的简单。Eccel在统计指标分析中的
16、应用任务二三、平均指标和标志变异指标计算三、平均指标和标志变异指标计算Excel针对每一种平均指标都有相应的函数可以实现。然后利用数据分析工具可以以最快的速度完成同样的工作。上面的数据中有两个数量标志，分别是密度和含糖量。这里就是用平均指标对这两个变量进行分析。具体步骤如下：（1）打开西瓜数据的表格。（2）点击工具栏“数据”，然后选择“数据分析”，在弹出的对话框中选中“描述统计”选项，然后单击“确定”。如图图10-1410-14所示。图图10-14 10-14 数据分析对话框数据分析对话框Eccel在统计指标分析中的应用任务二三、平均指标和标志变异指标计算三、平均指标和标志变异指标计算（3）在
17、弹出的描述性统计对话框中，首先选择“输入区域”，这里是指我们需要分析的原始数据的位置，对于本题而言数据区域为H3:H18，即密度数据；然后选择“分组方式”选中“逐列”，对于本组数据全是在第一列中，因此这里计算的就是针对第一列进行分析；再选择输出区域，这里选择A20；然后选中“汇总统计”、“平均数置信度”、“第K值大值”和“第K值小值”前的复选框；最后单击“确定”。如图图10-1510-15所示。然后对含糖量也做同样分析，讲两次分析的结果汇总在一起，得到图图10-1610-16所示：图图10-15 10-15 数据分析对话框选项数据分析对话框选项Eccel在统计指标分析中的应用任务二三、平均指标
18、和标志变异指标计算三、平均指标和标志变异指标计算图图10-16 10-16 数据分析结果数据分析结果从上图中可以看到西瓜数据的密度指标的共有16个观测值，平均值为0.64，标准差为0.27，峰度0.14为尖峰分布，偏度-0.53为左偏分布。而含糖量数据的结果显示平均数为0.32，标准差为0.07，峰度0.56尖峰分布，偏度-0.47为左偏分布。可见西瓜数据密度和含糖量数据中较大的数值较多且较集中。作为描述平均指标的代表均值和标志变异指标的代表标准差在分析数据中具有重要作用，而峰态和偏态作为描述总体分布特点的指标在实际分析中都有较强的使用价值。Eccel在抽样中的应用任务三一、在总体平均数区间估
19、计中的应用一、在总体平均数区间估计中的应用若要对总体参数进行有效的估计，统计中需要关注两个比较重要的变量。一是平均数，其次是标准差。因此如果要对总体平均进行有效的估计，需要知道样本平均数和样本标准差，最后通过计算求得估计区间的上下限。现有新采摘的西瓜100个，分别称重后得到如表表10-410-4中的数据（kg）。试使用Excel软件计算新采摘西瓜的总体平均数在95%的置信度下在什么重量范围内？图图10-4 10-4 西瓜重量数据西瓜重量数据Eccel在抽样中的应用任务三一、在总体平均数区间估计中的应用一、在总体平均数区间估计中的应用具体步骤如下：（1）打开西瓜数据的表格。（2）点击工具栏“数据
20、”，然后选择“数据分析”，在弹出的对话框中选中“描述统计”选项，然后单击“确定”。如图图10-1710-17所示。图图10-17 10-17 数据分析对话框数据分析对话框Eccel在抽样中的应用任务三一、在总体平均数区间估计中的应用一、在总体平均数区间估计中的应用（3）在弹出的描述性统计对话框中，首先选择“输入区域”，这里是指我们需要分析的原始数据的位置，对于本题而言数据区域为A2:A101；然后选择“分组方式”选中“逐列”，对于本组数据全是在第一列中，因此这里计算的就是针对第一列进行分析；再选择输出区域，这里选择D4；然后选中“汇总统计”、“平均数置信度”、“第K值大值”和“第K值小值”前的
21、复选框；最后单击“确定”。如图图10-1810-18所示。图图10-18 10-18 描述统计结果描述统计结果Eccel在抽样中的应用任务三一、在总体平均数区间估计中的应用一、在总体平均数区间估计中的应用（4）在图图10-1810-18中可以看到大量的总体描述性统计信息，其中平均便是样本均值，标准误差别是标准差，置信度便是95%置信度下对应的值。这里在G15和G16处根据计算公式分别计算出总体平均数的估计区间。Eccel在抽样中的应用任务三二二、在、在假设检验中假设检验中的应用的应用假设检验是在原假设的基础上以小概率事件一次试验不可能发生的条件下，检验原假设真假的逻辑反证法过程。整个假设检验中
22、最重要的就是需要计算出检验统计量的值以及对应的P值，对此Excel中有较为全面的函数支持，本节主要介绍如何使用Excel作为工具进行假设检验，需要掌握并熟练运用一些简单的函数处理概率问题。（一）标准正态分布（一）标准正态分布标准正态分布的平均值为0，标准偏差为1。对应的Excel函数为NORMSDIST(Z)：返回标准正态随机变量的观察值小于或等于Z的概率即：，如图图10-1910-19所所示。图图10-19 10-19 标准正态分布图标准正态分布图Px（Z）Eccel在抽样中的应用任务三二二、在、在假设检验中假设检验中的应用的应用创建一个空白Excel工作表，将工作表中的单元格A1:D10位
23、置填入几个数值，然后利用NORMSDIST(Z)函数分别计算每一个数值对应的标准正态分布的概率值，即在B2位置输入“=NORMSDIST(B2)”，在B3位置输入“=NORMSDIST(B3)”等，如图图10-2010-20所示图图1010-2020 标准正态分布概率值函数应用演示标准正态分布概率值函数应用演示Eccel在抽样中的应用任务三二二、在、在假设检验中假设检验中的应用的应用（二）（二）T T分布分布T分布用于小型样本数据集的假设检验。与标准正态分布曲线相比，自由度v越小，t分布曲线愈平坦，曲线中间愈低，曲线双侧尾部翘得愈高；自由度v愈大，t分布曲线愈接近正态分布曲线，当自由度 时，t
24、分布曲线为标准正态分布曲线。T分布对应的Excel函数是：TDIST(X,deg_freedom,tails)这里涉及参数较多，其中X 必需填写，指需要计算分布的数值。Deg_freedom必需填写，一个表示自由度数的整数。tails 必需填写。指定返回的分布函数是单尾分布还是双尾分布，如果Tails=1，则TDIST返回单尾分布。如果Tails=2，则TDIST返回双尾分布。创建一个空白Excel工作表，在A2位置填入需要计算的数据1.959999998，然后在A3位置填入自由度为60，然后分别应用A5和A6位置的公式计算双尾和单尾分布的结果，最后的结果在C5和C6位置显示。如图图10-21
25、10-21所示图图10-21 T10-21 T分布概率值函数应用演示分布概率值函数应用演示v=Eccel在抽样中的应用任务三二二、在、在假设检验中假设检验中的应用的应用（三）（三）卡方分布卡方分布若有n个相互独立的随机变量 ，均服从标准正态分布（也称独立同分布于标准正态分布），则这n个服从标准正态分布的随机变量的平方和 构成一新的随机变量，记为 卡方分布分布，其中参数n称为自由度，自由度不同就是另一个分布，正如正态分布中均值或方差不同就是另一个 正态分布一样。卡方分布可比较观察值和预期值的结果。例如，某项遗传学实验可能假设下一代植物将呈现出某一组颜色。通过使用该函数比较观察结果和理论值，可以确
26、定初始假设是否有效。卡方分布对应的Excel函数是：CHIDIST(X,deg_freedom)其中参数X必需填写，用来表示需要计算分布的数值。Deg_freedom必需填写，表示自由度数。创建一个空白Excel工作表，在A2位置填入需要计算的数据18.307，然后在A3位置填入自由度为10，然后应用A5位置的公式计算单尾卡方分布的结果，最后的结果在C5位置显示，如图图10-2110-21所示。图图10-2110-21 卡方分布概率值函数应用演示12,.nx x x2ix2()n2Eccel在抽样中的应用任务三二二、在、在假设检验中假设检验中的应用的应用（四）（四）F F分布分布F分布：设X、
27、Y为两个独立的随机变量，X服从自由度为k1 的分布，Y服从自由度为k2 的分布，这两个独立的随机变量除以各自的自由度后的比率，称之为服从自由度为k1，k2的F分布。使用此函数可以确定两组数据是否存在变化程度上的不同。例如，分析进入中学的男生、女生的考试分数，来确定女生分数的变化程度是否与男生不同。F分布对应的Excel函数为：FDIST(X,deg_freedom1,deg_freedom2)其中参数X必需填写，表示需要计算函数的值。Deg_freedom1必需填写表示分子自由度。Deg_freedom2必需填写，表示分母自由度。创建一个空白Excel工作表，在A2位置填入需要计算的数据15.
28、20686486，然后在A3位置填入分子自由度为6，在A4位置填入分母自由度为4，然后应用A6位置的公式计算F分布的结果，最后的结果在C6位置显示，如图图10-2310-23所示21()k22()kEccel在抽样中的应用任务三二二、在、在假设检验中假设检验中的应用的应用图图10-23 F10-23 F分布概率值函数应用演示分布概率值函数应用演示在假设检验的过程中针对总体的不同和抽样方式和样本数量的不同确定使用不同的检验统计量，然后结合具体的Excel函数计算得到某些数值对应的发生概率。最后通过计算所得的概率值与临界值对比得到科学的结论。在假设检验的一般过程中计算发生的概率尤为重要，而Exce
29、l中有着较为完善的函数体系可以帮助我们完成这样负责的操作，避免了大量的计算，为分析问题提供了方便。Eccel在时间序列及分析中的应用任务四截面数据反映同一时点上的所有研究对象的状态，通过横向对比找到研究事物的差异。而时间序列数据强调对事物的纵向分析，即将研究事物的过去状态逐个列出，以期望在这种连续的时间状态下找到某种规律，反映和刻画客观规律。现从国家统计局官网下载居民人均医疗保健消费支出_累计值(元)数据分季度数据，数据从2015年一季度到2018年三季度数据如图图10-2410-24所示。图图 1010-2424居民人均医疗保健消费支出分季节统计数据居民人均医疗保健消费支出分季节统计数据Ec
30、cel在时间序列及分析中的应用任务四一、计算增长量和平均增长量一、计算增长量和平均增长量在Excel工作簿的C3和C4位置分别输入逐期增长量和累计增长量，由于数据是降序排列的，因此计算逐期增长量需要在C15位置输入“=B15-B16”，然后回车，再将鼠标拖曳，将公式复制到C15到C2区域,如图图10-2510-25所示。图图1010-2-25 5逐期增长量计算结果逐期增长量计算结果Eccel在时间序列及分析中的应用任务四一、计算增长量和平均增长量一、计算增长量和平均增长量计算累计增长量时，在表格D15位置输入“=B15-$B$16”，然后回车，再将鼠标拖曳，将公式复制到D15到D2区域，如图图
31、10-2610-26所示。图图1010-2626 累计增长量计算结果累计增长量计算结果Eccel在时间序列及分析中的应用任务四一、计算增长量和平均增长量一、计算增长量和平均增长量计算平均增长量，在B17位置输入“=(B2-B16)/14”，然后回车，即可得到平均增长量的值，如图图10-2710-27所示。图图1010-2727 平均增长量的计算结果平均增长量的计算结果从结果中可以看出，从2015年一季度到2018年三季度，共15个季度的变化中，我国居民的人均医疗保健消费从2017年四季度到2018年一季度、2016年四季度到2017年一季度、2015年四季度到2016年一季度存在较大波动外其他
32、季度变动不大。即秋冬季医疗保健消费较大，其他季节较少。且这15个季度的平均医疗保健消费额增长量为71.42857143元。Eccel在时间序列及分析中的应用任务四二、发展速度和平均发展速度二、发展速度和平均发展速度在Excel工作簿的C3和C4位置分别输入定基发展速度和环比发展速度。定基发展速度：以2015年第一季度的人均医疗保健消费费用作为基期值，在C15单元格中输入“=B15/$B$16”，然后回车，再将鼠标拖曳，将公式复制到C15到C2区域，如图图10-2810-28所示图图1010-2828 定基发展速度的计算结果定基发展速度的计算结果Eccel在时间序列及分析中的应用任务四二、发展速
33、度和平均发展速度二、发展速度和平均发展速度环比发展速度：在D15位置的单元格中输入“=B15/B16”，然后回车，再将鼠标拖曳，将公式复制到D15到D2区域，如图10-29所示图图1010-2929 环比发展速度的计算结果环比发展速度的计算结果Eccel在时间序列及分析中的应用任务四二、发展速度和平均发展速度二、发展速度和平均发展速度平均发展速度：在B17位置输入“=GEOMEAN(D2:D15)”，然后回车，即可得到平均发展速度的值。这里的函数GEOMEAN()返回的是一组数的几何平均数，如图10-30所示。图图1010-3030 平均发展速度的计算结果平均发展速度的计算结果Eccel在时间
34、序列及分析中的应用任务四二、发展速度和平均发展速度二、发展速度和平均发展速度从定基发展速度的结果中可以看出，每年的一季度、二季度、三季度、四季度从定基发展速度的结果中可以看出，每年的一季度、二季度、三季度、四季度的值都在稳步增长，说明相比的值都在稳步增长，说明相比20152015年一季度，分季度发展速度每年都在增长，同时年一季度，分季度发展速度每年都在增长，同时也说明环境情况堪忧，同样的也说明了人们健康意识更加强烈，对自身保健意识更也说明环境情况堪忧，同样的也说明了人们健康意识更加强烈，对自身保健意识更加强烈；从环比发展速度中可以看出，每年的第二季度环比发展速度都是最快的，加强烈；从环比发展速
35、度中可以看出，每年的第二季度环比发展速度都是最快的，每年的第一季度环比发展速度都是最慢的。春季细菌容易滋生，导致大量流感疫情每年的第一季度环比发展速度都是最慢的。春季细菌容易滋生，导致大量流感疫情的爆发，所以此时的人均医疗保健消费相对冬季而言较高。而作为秋季过度到冬季的爆发，所以此时的人均医疗保健消费相对冬季而言较高。而作为秋季过度到冬季的第四季度和第一季度气温较为寒冷人均医疗保健消费变动不大；最后可以看出，的第四季度和第一季度气温较为寒冷人均医疗保健消费变动不大；最后可以看出，这这1515个季度的总体平均发展速度呈现逐步缓慢增长趋势。个季度的总体平均发展速度呈现逐步缓慢增长趋势。Eccel在
36、时间序列及分析中的应用任务四三、长期趋势三、长期趋势由于数据中是季度数据，因此这里计算先四期移动平均。在工作簿中C1中输入“四项移动平均”，然后在C14单元格中输入“=SUM(B13:B16)/4”，然后回车，计算得到2015年四季的移动平均值，然后，再将鼠标拖曳，将公式复制到C14到C3区域，如图图10-3110-31所示。采用移动序时平均形成的平均序列进行研究目的在于消除或削弱原序列中各指标值在短期内因偶然因素的影响所引起的波动，从而呈现出在较长时间的基本发展趋势。因此，由上述分析可知，自2015年一季度到2018年三季度，我国人均医疗保健消费费用呈现稳定的增长态势。图图10-31 10-
37、31 移动平均的计算结果移动平均的计算结果Eccel在时间序列及分析中的应用任务四四、季节变动四、季节变动将原始数据截取一部分，仅剩下2015年一季度到2017年四季度。因为数据采用的是季度数据，仅保留整年份的整季度方便下面的分析。首先需要对数据进行四项移动平均，然后计算趋势值即二项移动平均，接着需要剔除长期趋势，然后对数据按照年份和季度重新排列计算各年各季的平均数，最后计算调整系数和季节比率得出结论即可。首先进行四项移动平均，在C1单元格中输入“四项移动平均”。在C12单元格中输入“=SUM(B10:B13)/4”，然后回车，再将鼠标拖曳，将公式复制到C12到C4区域，如图图10-3210-
38、32所示。图图1010-3232四项移动平均的结果四项移动平均的结果Eccel在时间序列及分析中的应用任务四四、季节变动四、季节变动计算趋势值，在D1单元格中输入“二项移动平均”。在D11单元格中输入“=(C12+C11)/2”，然后回车，再将鼠标拖曳，将公式复制到D11到D4区域，如图图10-3310-33所示。图图1010-33 33 二项移动平均的结果二项移动平均的结果Eccel在时间序列及分析中的应用任务四四、季节变动四、季节变动剔除长期趋势，在E1单元格中输入“剔除长期趋势”，在E11单元格中输入“=B11/D11”，然后回车，再将鼠标拖曳，将公式复制到E11到E4区域，如图图10-
39、3410-34所示图图5-11 5-11 剔除长期趋势后的结果剔除长期趋势后的结果Eccel在时间序列及分析中的应用任务四四、季节变动四、季节变动整理数据，将上图中的数据按照年份和季度重新排列，得到表10-5的结果。表表1010-5 5 数据重新整理数据重新整理Eccel在时间序列及分析中的应用任务四四、季节变动四、季节变动计算各年各季度的平均数，在A19单元格中输入“平均数”，在B19单元格中输入“=AVERAGE(B16:B18)”，再将鼠标拖曳，将公式复制到B19到E19区域.如图图10-10-3 35所示。图图1010-3535 各年各季度的平均数各年各季度的平均数计算调整系数，在A2
40、0中输入“调整系数”，在B20单元格输入“=4/SUM(B19:E19)”。计算季节比率，在A21中输入“季节比率”，在B21单元格输入“=B19*$B$20”，再将鼠标拖曳，将公式复制到B21到E21区域，如图图10-3610-36所示。Eccel在时间序列及分析中的应用任务四四、季节变动四、季节变动图图1010-3636 季节比率计算结果季节比率计算结果通过季节比率可以看出，一、二季度季节比率小于通过季节比率可以看出，一、二季度季节比率小于1 1，属于淡季，说明全国居，属于淡季，说明全国居民对于医疗保健消费不高；三、四季度季节比率大于民对于医疗保健消费不高；三、四季度季节比率大于1 1，属
41、于旺季，说明全国居民，属于旺季，说明全国居民对于医疗保健消费需求较高。说明一、二季度人均医疗保健消费费用变动不大；三、对于医疗保健消费需求较高。说明一、二季度人均医疗保健消费费用变动不大；三、四季度人均医疗保健消费费用变动较大。四季度人均医疗保健消费费用变动较大。Eccel在相关与回归分析中的应用任务五一、相关与回归分析中常用一、相关与回归分析中常用ExcelExcel函数或功能模块函数或功能模块在相关与回归分析中所用函数和功能模块如表在相关与回归分析中所用函数和功能模块如表10-610-6所示。所示。表表10-610-6函数或功能模块名称函数说明语法形式或操作步骤CORREL返回单元格区域数
42、列1和数列2之间的相关系数CORREL(array1,array2)array1第一组数值单元格区域array2第二组数值单元格区域INTERCEPT利用现有的x值和y值计算直线与y轴截距INTERCEPT(known_ys,known_xs)known_ys为因变量的观察值或数据集合known_xs为自变量的观察值或数据集合SLOPE返回根据known_ys和known_xs中 的数据点拟合的线性回归直线的斜率SLOPE(known_ys,known_xs)known_ys为因变量数据点数组或单元格区域known_xs为自变量数据点集合区域LINEST可通过使用最小二乘法计算与现有数据最佳拟
43、合的直线，返回描述此直线的截距和斜率LINEST(known_ys,known_xs,count,stats)内为可选项，返回回归直线的截距和斜率（利用数组计算）STEYX返回通过线性回归法计算每个x的y预测值时所产生的标准误差STEYX(known_ys,known_xs)known_ys为因变量数据点数组或区域known_xs为自变量数据点数组或区域TREND返回一条线性回归拟合线的预测值TREND(known_ys,known_xs,new_xs,count)known_ys为因变量的观察值或数据集合known_xs为自变量的观察值或数据集合new_xs为用于预测y值的新x值相关系数工具
44、返回两列数据的相关系数矩阵操作步骤：数据-数据分析-相关系数回归工具返回相关与回归分析的各项参数及统计检验结果操作步骤：数据-数据分析-回归作图法利用散点图，采用作图法求解回归方程操作步骤：插入-图表-散点图（选中点，右击）添加趋势线-趋势线选项-显示公式和R平方值Eccel在相关与回归分析中的应用任务五二、二、ExcelExcel在相关与回归分析中的应用实例在相关与回归分析中的应用实例现有2018年某地区20家同类工业企业产量与单位成本的统计数据，如表表10-710-7所示。表表10-710-7Eccel在相关与回归分析中的应用任务五二、二、ExcelExcel在相关与回归分析中的应用实例在
45、相关与回归分析中的应用实例（一）利用（一）利用ExcelExcel绘制相关表绘制相关表新建一个Excel工作簿文件，文件名为“相关与回归分析”，将上表中的数据复制粘贴到相关与回归分析工作簿中表sheet1中，如下图图1010-3737所示。图图10-37 10-37 产量与单位成本原始数据产量与单位成本原始数据Eccel在相关与回归分析中的应用任务五二、二、ExcelExcel在相关与回归分析中的应用实例在相关与回归分析中的应用实例 将光标移至B列的任意数值上，在“开始”选项卡下，单击“编辑”组的“排序和筛选”按钮，选择升序，即可得到相关表，如图图10-3810-38所示。通过“数据”选项卡中
46、的“排序和筛选”组相关按钮，同样可以实现数据的排序。根据相关表即可对两变量间相关方向加以判断。图图10-3810-38 产量与单位成本相关表Eccel在相关与回归分析中的应用任务五二、二、ExcelExcel在相关与回归分析中的应用实例在相关与回归分析中的应用实例（二）利用（二）利用ExcelExcel绘制相关图绘制相关图相关图即散点图，是将自变量和因变量在第一象限描绘出来的点组成的图形。散点图的绘制步骤如下：在相关与回归分析工作簿sheet1工作表中，选中B1:C21单元格，“插入”选项卡-“图表”组-“散点图”下拉按钮-选第一项（仅带数据标记的散点图）得到原始相关图，选中相关图，通过“设计
47、”和“布局”选项卡，经过对标题栏及坐标栏的美化，即可得到如图图10-3910-39所示散点图。图图10-3910-39 020040060080010001200020040060080010001200单位成本单位成本产量产量产量与单位成本相关图产量与单位成本相关图Eccel在相关与回归分析中的应用任务五二、二、ExcelExcel在相关与回归分析中的应用实例在相关与回归分析中的应用实例（三）相关系数的计算（三）相关系数的计算1.1.函数计算法。函数计算法。运用Excel中的CORREL函数可以很方便计算出所要的结果。具体操作步骤如下：在相关与回归分析工作簿sheet1工作表中，E2单元格输
48、入公式“=CORREL(B2:B21,C2:C21)”,其中B2：B21表示自变量数列，C2：C21表示因变量数列，可分别通过拖动鼠标选中的形式实现。回车后即可得到两变量间的相关系数r。结果如图图10-4010-40所示。图图10-40 CORREL10-40 CORREL函数相关系数函数相关系数Eccel在相关与回归分析中的应用任务五二、二、ExcelExcel在相关与回归分析中的应用实例在相关与回归分析中的应用实例（三）相关系数的计算（三）相关系数的计算 2.2.数据分析工具法数据分析工具法（1）数据分析工具库的加载。数据分析工具库需要加载后才能使用，具体加载步骤如下：单击左上角offic
49、e按钮Excel选项加载项分析工具库转到，即出现“加载宏”对话框，在分析工具库前的方框内打“”，单击“确定”即完成分析工具库的加载。此时,在“数据”选项卡“分析”组出现了“数据分析”工具。（2）利用数据分析工具计算相关系数。在相关与回归分析工作簿shee1工作表中，单击“数据”选项卡“分析”组“数据分析”工具相关系数确定，出现“相关系数”对话框，如图图10-4110-41所示。将输入区域选中B1：C21，标志位于第一行打“”，输出区域选中E2，确定后即可计算得到相关系数计算矩阵，如图图10-10-4242所示。Eccel在相关与回归分析中的应用任务五二、二、ExcelExcel在相关与回归分析
50、中的应用实例在相关与回归分析中的应用实例图图10-41 10-41 分析工具库计算相关系数分析工具库计算相关系数图图10-42 10-42 分析工具库计算相关系数的结果分析工具库计算相关系数的结果Eccel在相关与回归分析中的应用任务五二、二、ExcelExcel在相关与回归分析中的应用实例在相关与回归分析中的应用实例（四）一元线性回归分析（四）一元线性回归分析一元线性回归分析的关键是确定回归方程 中参数a,b的值。在Excel中参数a,b的确定,常用的方法有以下三种:1.1.函数法。函数法。确定参数a,b的函数可以有两组，一组是利用SLOPE函数和INTERCEPT函数分别计算b和a的值；另
