1、1第十三章 Excel在统计学中应用2第一节第一节 用用ExcelExcel做统计图做统计图一、 用Excel做频率分布表和直方图一般统计数据有两大类，即定性数据和定量数据。我们这里就不讨论把定性数据转换成定量数据了，而是主要学习定量数据的作图。下面的例子说明如何利用Excel进行分组，并作频率分布表和直方图。案例2010年1月四十个大中城市房屋销售价格指数如下：110.2、109.6、102.6、102.6、105.9、102.7、105、104.5、105.9、108.8、110.4、112.7、108、105.2、103.2、108.9、107.1、105.7、106、104.9、103
2、.9、108.8、110.6、120.6、105.5、131.8、108.8、105.3、109.6、110.3、110、105.1、105.9、113、106.2、99.5、104.8、103.1、105.9、103.8（1）据此编制分布数列（提示：价格指数是连续变量）；（2）计算向上累计频数（率）；（3）画出次数分布直方图。3步骤第一步：在Excel界面中输入40个企业的数据，从上到下输入A列（也可以分组排列）。第二步：选择“工具”下拉菜单，如图13-1所示。4 第三步：选择“数据分析”选项，如果没有该功能则要先行安装。“数据分析”的具体安装方法，选择“工具”下拉菜单中“加载宏”，在出现的
3、选项中选择“分析工具库”，“确定”就可自动安装。 第四步：在分析工具中选择“直方图”，如图13-2所示。5 第五步：当出现“直方图”对话框时，在“输入区域”方框内键入A2:A41或A2:A41（“”符号表示的是绝对地址起，到固定单元格的作用），40个数据已输入该区域内，如果是分组排列的，就应该选择整个分组区域。在“接收区域”方框内键入C2:C9或C2: C9，所有数据分成8组（主要根据资料的特点，决定组数、组距和组限），把各组的上限输入该区域内。在“输出区域”方框内键入E2或E2，也可以在其他位置重新建表。对话框中，还选择“累积百分率”、“图表输出”（如图13-3）所示。然后点击“确定”。 对
4、话框内主要选项的含义如下： 输入区域：在此输入待分析区域的单元格范围。6 接收区域（可选）：在此输入接收区域的单元格范围，该区域包含一组可选的用来计算频数的边界值。这些值应当按升序排列。只要存在，Excel将统计在各个相邻边界值之间的数据出现的次数。如果省略此处的接收区域，Excel将在数据组的最小值和最大值之间建立一组平滑分布的接收区间。7 标志：如果输入区域的第一行或第一列中包含标志项，则选中此复选框；如果输入区域没有标志项，则清除此复选框，Excel将在输出表中生成适宜的数据标志。 输出区域：在此输入计算结果显示的单元格地址。如果不输入具体位置将覆盖已有的数据，Excel会自动确定输出区
5、域的大小并显示信息。 柏拉图：选中此复选框，可以在输出表中同时显示升序、将序排列频率数据。如果此复选框被清除，Excel将只按升序来排列数据。 累积百分比：选中此复选框，可以在输出结果中添加一列累积百分比数值，并同时在直方图表中添加累积百分比折线。如果清除此选项，则会省略以上结果。 图表输出：选中此复选框，可以在输出表中同时生成一个嵌入式直方图表。8 结果 操作得到的结果如图13-4所示。通常包括三列和一个频数分布图，第一列是数值的区域范围，第二列是数值分布的频数（不是频率），第三列是频数分布的累积百分比。 直方图是用矩形高度来表示频数分布的图形。绘制直方图时，将所研究的变量放在横轴上，频数、
6、频率放在纵轴上。每组的频数、频率在图上就是一个长方形，长方形的底在横轴上，宽度是组距，长方形的高就是对应的频数或频率。图13-4中是一个条形图，而不是直方图，若要把它变成直方图，可按如下操作： 用鼠标左键单击图中任一直条形，然后右键单击，在弹出的快捷菜单中选取“数据系列”格式，弹出数据系列格式对话框。在对话框中选择“选项”标签，把“分类间距”宽度改为零，按确定后即可得到直方图，如图13-5所示。910 二、二、 用用Excel做常用统计图做常用统计图 Excel提供的统计图有多种，包括柱形图、条形图、折线图、饼图、散点图、面积图、环形图、雷达图、曲面图、气泡图、股价图、圆柱图、圆锥图等，各种图
7、的作法大同小异，可根据需要的类型进行适当选择。 （一）（一） 饼图的绘制饼图的绘制 饼图是用圆形以及园内扇形的面积来表示数值大小的图形，主要用于表示总体中各组成部分所占的比例，常用于研究结构性问题。 案例 某地区年因违法被捕的男性情况如下所示，根据资料利用Excel绘制饼图。11步骤先把数据输入到工作表中，如图13-6所示，然后按下面的步骤操作：12 第一步：选择“插入”下拉菜单中的“图表”选项。 第二步：在图表类型中选择“饼图”，然后在子图表类型中选择一种类型，这里我们选用系统默认的方式。然后单击下一步按钮，打开源数据对话框。如图13-7所示。13 第三步：在图标源数据对话框中填入数据所在区
8、域，单击下一步，在图表选项中，对“标题”、“图例”和“数据标志”适当处理。如果要对图形修改，可以用鼠标双击图表，然后用鼠标双击需要修改的部分，并进行修改。 结果 即可得如图13-8所示的饼图。14 （二）折线图的绘制 折线图主要用于比较数据变动的方向和趋势，表现数据在不同时期发展变化的不同趋势。 案例 根据福建省2005年到2009年间三大产业的总产出情况绘制折线图，描述这几年来三大产业总产值的变化趋势。15 步骤 第一步：资料输入工作表后，选择“插入”下拉菜单，再选择“图表”选项。 第二步：在图表类型中选择“折线图”，然后在子图表类型中选择一种类型，这里我们选用如图13-9所示的方式。然后单
9、击“下一步”按钮，打开源数据对话框。16 第三步：在源数据对话框中，“数据区域”中输入相关资料（可用鼠标点击并框定数据区域）。再在“系列”的“分类（X）轴标志”区域输入年份区域，如图13-10所示 第四步：资料输入后的下一步，进入“图表选项”。分别对“标题”、“坐标轴”、“网格线”、“图例”、“数据标志”和“数据表”等选项进行设置，当然设置各选项时根据需要进行取舍。最后点击“完成”，就在工作表中得到折线图。 结果 经过上述各步骤，在工作表中得到折线图：17第二节 用Excel计算描述统计量 一、利用“数据分析”功能 案例根据某县的一项关于乡镇企业工资的调查，下面是40名雇员的月工资按升幂排列的
10、一个样本，对该企业雇员工资进行描述统计分析。（单位：元） 274 279 290 326 329 341 378 405 436 500 515 541 558 566 618 708 760 867 868 869 888 915 932 942 960 975 976 1014 1025 1096 1118 1166 1193 1194 1243 1277 1304 1327 1343 1398 步骤 第一步：在Excel的区域“A1:A40”的单元格中输入数据，从上到下排成4列。 第二步：选择“工具”下拉菜单，再选择“数据分析”选项 第三步：在分析工具中选择“描述统计”，如图13-11所
11、示1819 第四步：当出现对话框时，在“输入区域”方框内键入A1:A40（或用鼠标选择这区域），在“输出选项”中选择输出区域（在此选择C4），再选择“汇总统计”（该选项给出全部描述统计量）。 第五步：点击“确定”键。 其计算结果如图13-12所示。2021 二、二、 利用利用“统计函数统计函数”工具计算工具计算 我们可以用两种方法来得到描述统计量，除上述“数据分析”功能计算外，还可采用Excel的函数工具计算。仍以该企业40名雇员工资为例进行计算。 第一步：与上面操作相同，在Excel中输入数据。 第二步：选择“插入”下拉菜单中的“函数”选项，如图13-13所示。 第三步：出现的“插入函数”界
12、面中，在“或选择类别”选项中，选“统计”。再在“选择函数”中，选“AVERAGE”（算术平均数），点击“确定”如图13-14所示。2213-1313-1423 第四步：出现“AVERAGE函数参数”界面中，在“Number1”中键入A1:A40（或用鼠标选择这区域），然后点击“确定”，就能得出“算术平均数=817.85”。 重复上述各步骤，还可计算“调和平均数”、“几何平均数”、“样本标准差”和“总体标准差”等统计量，只要分别选择相应的函数。常用描述统计量函数如表A-4所示2425第三节第三节 用用ExcelExcel进行抽样进行抽样 一、用Excel进行随机抽样 当总体数量太大，不可能进行全
13、面调查或者在某些情况下不需要进行全面调查又需要了解总体的情况时必须采用抽样。使用Excel进行抽样，首先要对各个总体单位进行编号，编号可以按随机原则，也可以按有关标志或无关标志，编号后，将编号输入工作表。 注：本功能需要使用Excel扩展功能，如果您的Excel尚未安装数据分析，请依次选择“工具” “加载宏”，在安装光盘的支持下加载“数据分析库”。加载成功后，才可以在工具的下拉菜单中看到“数据分析”选项。26 案例 假定有100个总体单位，每个总体给一个编号，一共有100个编号，输入工作表后，如图13-15所示27 步骤 先输入各总体单位的编号，再按照下面的步骤操作： 第一步： 单击菜单栏中的
14、“工具”选项，在下拉列表框中选择“数据分析”选项，此时弹出“数据分析”对话框，分析工具选择“抽样”。如图A-17所示28 第二步：单击“确定”，弹出“抽样”对话框，如图13-17所示29 第三步：在输入区域框中输入总体单位编号所在单元格区域，在本例是$A$1:$J$10,实际操作中一般是用鼠标将编号所在区域选中即可。系统将从A列开始抽取样本，然后按顺序抽取B列至J列。如果输入区域的第一行或第一列为标志项（横行标题或纵行标题），可单击“标志”复选框。 第四步：抽样方法有周期和随机两种模式。“周期模式”即所谓的等距抽样，采用这种抽样方法，需将总体单位数除以要抽取的样本单位数，可得到取样的周期间隔。
15、这里我们假设要在100个总体单位中抽取10个，则在“间隔”框中输入“10”。30 “随机模式”适用于纯随机抽样、分类抽样、整群抽样和阶段抽样。采用纯随机抽样，只需在“样本数”中直接输入要抽取的样本数即可；若用分类抽样，必须先将总体按某一标志分类编号，然后再每一类中随机抽取若干单位i，这种抽样方法实际上是分组法和随机抽样方法的结合；整群抽样也要将总体单位分类编号，然后按随机原则抽取若干类作为样本，对抽中的所有单位全部进行调查。 第五步：制定输出区域，这里我们键入“F12:F22”，然后单击“确定”按钮即可。 结果 10个随机抽样的样本编号显示在工作表的“F12:F22”区域31 二、用二、用Ex
16、celExcel进行抽样估计进行抽样估计求置信区间求置信区间调用Excel中一些常用的“统计函数”可以直接进行抽样推断中的区间估计计算。下面结合案例来说明具体的操作步骤。 案例 为了解学校某班的统计学成绩情况，对该班的全体同学随机抽取13名作为调查样本，统计抽取得到的样本如图13-18的 A列所示。假设该班学生的统计学成绩总体上服从正态分布，用95%的置信水平估计该班学生统计学成绩的置信区间。 为构造区间估计的工作表，在工作表中先输入以下内容：A列中输入样本数据，B列输入各统计指标名称，C列中输入计算公式，D列输入计算的结果。3233 步骤 第一步：把样本数据输入A2:A14单元格。 第二步：
17、在D2中输入公式“COUNT(A2:A14)”,得到计算结果“13”。“COUNT”是计数函数，得到样本含量（n=13）。 第三步：在D3中输入“=AVERAGE(A2:A14)”，在D4中输入“=STDEV(A2:A14)” 在D5中输入“=D4/SQRT(D2)”,在D6中输入0.95，在D7中输入“=D2-1”，在D8中输入“=TINV(1-D6，D7)”,在C9中输入“=D8*D5”,在D10中输入“=D3-D9”,在D11中输入“=D3+D9”。 再输入每一个公式回车后，即可得到如表13-5的结果。3435 结果 从上面的结果我们可以知道，该班学生的统计学成绩的置信上限是84.003
18、9804，置信下限是61.53448112.计算结果表明，我们有95%的把握认为该班学生的统计学成绩平均在61.53448112-84.0039804之间。 利用上面的统计指标，输入不同的样本数据，根据指标所在位置的变化对计算公式作相应的更改，则可以计算不同样本的置信区间。36第四节第四节 用用ExcelExcel进行假设检验进行假设检验 假设检验包括一个正态总体的参数检验和两个正态总体的参数检验。对于一个正态总体参数的检验，可以直接利用函数工具和输入公式的方法计算相应的统计量，并进行检验。这里主要介绍如何利用Excel进行两个正态分布总体的均值差检验。 Excel中提供了四种假设检验模式 ：
19、平均值的成对二样本分析、双样本等方差假设、双样本异方差假设和双样本平均差检验。其中较为常用的是后三种，所以这里就不介绍第一种。可以用以图A-20来说明后三种模式适用的条件。37 案例 设有A 、B两种安眠药，为了比较他们的治疗效果。以X表示失眠病人服下A药睡眠时间延长的时数；以Y表示失眠病人服下B药睡眠时间延长的时数.现在独立观察20个病人，其中10个服下A药，另10个服下B药，延长时数的情况所图A-21中A列和B列所示。假设X与Y都服从正态分布。试问，这两种药的疗效是否有显著的差异。38 步骤 第一步：选择“工具”下拉菜单，再选择“数据分析”选项 第二步：在“分析工具”选择“t检验：双样本异
20、方差检验”（这里总体方差未知，且假设两个对比样本的方差不相等）如图13-20所示。39 第三步：当出现对话框时，在“变量1的区域”方框内输入A2:A11，实际操作中只要选中这部分区域即可；在“变量2的区域”方框内输入B2:B11；在“假设平均差”方框内键入“0”；“”默认 为0.05；在“输出选项”中选择“新工作表组”，如图13-21所示。待所有选项设置好后，单击“确定”按钮。40 结果输出结果如表13-6所示计算的P值为0.206253，大于0.05。所以不拒绝原假设，认为两样本的均值是相等的。即在本例中，认为A、B两种药品的疗效没有显著性差异。41第五节第五节 用用ExcelExcel进行
21、列联分析进行列联分析 案例 调查339名45岁以上吸烟习惯与患肺癌的关系，数据见表13-7。试用列联表分析方法分析吸烟者与不吸烟者患肺癌的概率是否存在明显的差异。42 步骤 第一步：首先将原始数据整理成22 的列联表，如下图所示 43 第二步：建立期望频数表。如表13-8所示，先建立期望频数表的框架，然后在B7单元格输入公式“=B$5*$D3/$D$5”，再利用“填充柄”将公式复制到表格的其它单元格，最后利 用Excel 的求和函数sum 计算行和与列和。44 第三步：计算 概率值。在A12 单元格输入标志项“卡方概率值”，先点击B12单元格，从“插入”菜单中的“函数”子菜单，选择“统计函数”
22、中的“CHITEST”函数，单击“确定”按扭，然后在弹出的对话框中分别填入实际频数范围“B3:C4和期望频数范围 “B8:C9”。最后单击“确定”按钮，即可得到计算结果0.00627785,如图A-23所示。 24546 第四步：建立 统计表。如表13-10所示，先建立表格的框架，然后在B17单元格输入公式“(B3-B8)2/B8”，再利用填充柄将公式复制到表格的其它单元格。最后计算卡方统计量，分别在A21 与B21 单元输入标志项与计算公式 247第五步：进行假设检验。如图13-24所示，分别输入置信水平、临界值，其中CHIINV 函数的自由度=(第一类属性的分类数-1)*( 第二类属性的分
23、类数-1)=(2-1)*(2-1)=1。 结果 其中，B12单元格的卡方概率值与B21 单元格的卡方统计量是表格的两个重要计算结果。其中卡方概率值等于0.00627785，这个概率几乎接近于0，所以可以认为总体的这两个属性是显著相关的。48第六节第六节 用用ExcelExcel进行相关与回归分析进行相关与回归分析 一、相关分析一、相关分析 相关分析就是对两个变量之间线性关系的描述和度量，利用Excel进行相关分析时，先要绘制散点图大致描绘出两个变量变化趋势的关系形态，散点图的做法前面已经介绍，这里就不再赘言，如果是线性关系，再计算相关系数来度量两个变量之间的关系强度。用Excel计算相关系数有
24、两种方法，一是直接利用相关系数函数计算，如“CORREL函数”和“PEARSON函数”；另一种是利用“数据分析”中的“相关系数”工具。这里主要介绍后者。49案例7个省区人均GDP（元）和建筑合同的价值（亿元）资料如下表13-1150根据资料数据先绘制散点图如图13-25所示51 从散点图可以看出，两个变量之间存在一定的正线性关系 下面再用Excel计算相关系数，并说明两变量相关的方向和程度。 步骤 第一步：将人均GDP和建筑合同价值两个变量的数据分别输入到A2:A8和B2:B8区域。 第二步：选择“工具”下拉菜单，再选择“数据分析”选项。 第三步：在分析工具中选择“相关系数”。 第四步：出现对
25、话框，在输入区域中键入“A2:B8”，在“输出区域”中选择输出区域，这里我们选择输入到“新工作表”，最后单击“确定”按钮。52 结果 由Excel计算得到的相关系数矩阵如图13-26所示，结合资料分析，可以得到这7个省区的“人均GDP”和 “建筑合同价值”的相关系数为0.693511，属于中度正相关 53 二、回归分析二、回归分析 相关分析的目的在于测度变量之间的关系强度，而大部分情况下，仅仅知道相关程度是远远不够的，我们更希望能够确定两个或者多个变量之间具体的数学关系式，这就需要进行回归分析。利用Excel可以很容易地进行回归分析，包括一元线性回归和多元线性回归。 案例20个家庭住房支出与年
26、收入资料，如表13-12 所示54 要求编制直线回归方程，计算回归的标准误差，并估计当家庭年收入为25时，住房支出的可能值。 步骤 第一步：选择“工具”下拉菜单，再选择“数据分析”项 第二步：在分析工具中选择“回归”； 第三步：弹出对话框，在“输入Y的区域”方框内输入A2:A21，在“输入X的区域”方框输入B2:B21，在“输出区域”选择“新工作表”，最后单击“确定”即可。 结果 输出结果如图13-27所示5556 Excel输出的回归结果包括以下几个部分： 第一部分是“回归统计”，这部分给出了回归分析中一些常用统计量，包括相关系数（Multiple R）、判定系数（R Square）、调整的
27、判定系数（Adjusted R Square）、标准误差、观测值的个数等，这里判定系数R=0.933511，说明回归直线对数据的拟合优度很高，根据判定系数的实际意义有：住房支出的差异有93%以上是年收入决定的，两者有很强的线性关系； 第二部分是“方差分析”，这部分给出的F检验的显著性水平（Significance F）远小于0.05表明回归方程是显著的；5758第七节第七节 用用Excel进行时间序列分析进行时间序列分析 一、季节变动分析一、季节变动分析 为介绍Excel在季节变动分析中的应用，我们以实例操作，采取移动平均趋势剔除法计算季节指数。 案例 某地火车站三年来各季度客运量数据如表13
28、-13所示：59 步骤 把数据按顺序输入到工作表中的B2:B13，用Excel构造一张季节变动分析表（如下图13-28所示），再按下面的步骤计算 第一步：计算移动平均值（如果是季度数据就采用4项移动平均，月份数据则采用12项移动平均），所以这里我们在C3单元格输入公式“=AVERAGE(B2:B5)”,然后将公式复制到C4:C11单元格。结果如图13-28中的C列所示 第二步：计算移动平均趋势值（中心化移动平均数），也就是对C列的结果再进行一次2项的移动平均。在D4单元格输入公式“=AVERAGE(C3:C4)”,然后将公式复制到D5:D11单元格。结果如图中的D列。 第三步：计算移动平均的比
29、值（季节比率），即将序列的各观测值除以相应的移动平均趋势值。在E4单元格输入公式“=B4/D4”，然后将公式复制到E5:E11。结果如图中的E列。60 第四步：计算同季平均。在F2单元格输入公式“=（E6+E10）/2”，在F3单元格中输入公式“=（E7+E11）/2”，在F4单元格输入公式“=（E4+E8）/2”，在F5单元格中输入公式“=（E5+E9）/2”。结果如图中的F列。 第五步：计算总平均值。在G2单元格输入公式“=AVERAGE(E4:E11)”； 第六步：计算季节指数。将同季平均值除以总平均值。在H2单元格中输入公式“=F2/G2”，在H3单元格中输入公式“=F3/G2”，在H
30、4单元格中输入公式“=F4/G2”，在H5单元中输入公式“=F5/G2”。61结果计算结果如图中的H列。62二、用Excel进行时间序列预测资料我国人均国内生产总值（GDP）时间序列数据如表13-14所示。63 （一）用移动平均法进行预测 步骤 第一步：将原始数据输入到单元格区域：B2:B15，如图13-29所示。 第二步：选择菜单栏中的“工具”，再选择“数据分析”项，弹出如图13-29的对话框，在“分析工具”框中选中“移动平均”，单击“确定”按钮。64 第三步：弹出移动平均对话框，在“输入区域”键入“B2:B15”，即原始数据所在的单元格区域；在“间隔”内输入“3”，表明使用的是三步移动平均
31、法；在“输出区域”内输入“C2”，即将输出区域的左上角单元格设为C2，选择“图表输出”复选框和“标准误差”复选框，最后单击“确定”。65结果移动平均的结果如下图所示。分析：在图13-31中，“C2:C15”对应的数据即为三步移动平均的预测值；单元格区域“D6:D15”即为标准误差。66 （二）用指数平滑法进行预测（二）用指数平滑法进行预测 步骤 第一步：将原始数据输入到单元格“B2:B12”； 第二步：点击菜单栏上的“工具”，选择“数据分析”选项， 第三步：弹出对话框，在分析工具中选择“指数平滑”，单击“确定”按钮 第四步：弹出如图13-32所示的对话框，在“输入区域”中输入“B2:B15”；
32、在“阻尼系数”输入的值（这里我们将设为0.5）；在输出区域中输入“C2”； 选择“图表输出”复选框和“标准误差”复选框，最后单击“确定”即可。 67 结果 经过以上步骤的处理，可得到指数平滑的结果，如图13-33所示。其中“C3:C15”对应的数据即为指数平滑的预测值；单元格区域“D6：D15”即为标准误差。6869 （二）用指数平滑法进行预测 步骤 第一步：将原始数据输入到单元格“B2:B12”； 第二步：点击菜单栏上的“工具”，选择“数据分析”选项， 第三步：弹出对话框，在分析工具中选择“指数平滑”，单击“确定”按钮 第四步：弹出如图13-32所示的对话框，在“输入区域”中输入“B2:B1
33、5”；在“阻尼系数”输入 的值（这里我们将 设为0.5）；在输出区域中输入“C2”； 选择“图表输出”复选框和“标准误差”复选框，最后单击“确定”即可。170 结果 经过以上步骤的处理，可得到指数平滑的结果，如图13-33所示。其中“C3:C15”对应的数据即为指数平滑的预测值；单元格区域“D6：D15”即为标准误差。7172 （三）用趋势预测法进行预测要求根据案例的数据进行趋势预测，并且预测2005年的人均GDP。 步骤 第一步：对原始数据进行整理，根据年份的先后顺序补上一个时间序列变量t，将数据输入到Excel中，如图13-34所示 第二步：选择单元格“D2”，再选择“插入”下拉菜单中的“
34、函数”选项， 第三步：弹出“函数”对话框，在函数类别中选择“统计”，在函数名中选择“FORECAST(线性回归拟合预测)”或者在“搜索函数”中直接输入函数名“FORECAST(线性回归拟合预测)”也可，如图13-34所示。73 第四步：单击“确定”按钮，出现“函数参数”对话框。在“X”中输入“1”（对应的预测年份是1991年），在“Known_ys”中输入“C2:C15”(因变量),在“Known_xs”中输入“B2:B15”（自变量）,如图13-35所示。 第五步：单击“确定”按钮，则“D2”单元格上显示的就是1991年的趋势预测值，相应地，我们仿照上面的步骤，继续计算1992-2005年的趋势预测值。74结果经过以上各步骤操作，即可得到各年人均GDP的趋势预测值，预测结果如图13-36所示。其中“D16=10474.956”,即2005年人均GDP约为10474.956 元。7576
