Excel在统计学中应用剖析

Excel在统计学中应用2021/6/281第一节用Excel做统计图一、用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.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）画出次数分布直方图。2021/6/282[步骤]

第一步：在Excel界面中输入40个企业的数据，从上到下输入A列（也可以分组排列）。

第二步：选择“工具”下拉菜单，如图1所示。2021/6/283第三步：选择“数据分析”选项，如果没有该功能则要先行安装。“数据分析”的具体安装方法，选择“工具”下拉菜单中“加载宏”，在出现的选项中选择“分析工具库”，“确定”就可自动安装。第四步：在分析工具中选择“直方图”，如图2所示。2021/6/284第五步：当出现“直方图”对话框时，在“输入区域”方框内键入A2:A41或＄A＄2:＄A＄41（“＄”符号表示的是绝对地址起，到固定单元格的作用），40个数据已输入该区域内，如果是分组排列的，就应该选择整个分组区域。在“接收区域”方框内键入C2:C9或＄C＄2:＄C＄9，所有数据分成8组（主要根据资料的特点，决定组数、组距和组限），把各组的上限输入该区域内。在“输出区域”方框内键入E2或＄E＄2，也可以在其他位置重新建表。对话框中，还选择“累积百分率”、“图表输出”如图所示。然后点击“确定”。对话框内主要选项的含义如下：输入区域：在此输入待分析区域的单元格范围。2021/6/285接收区域（可选）：在此输入接收区域的单元格范围，该区域包含一组可选的用来计算频数的边界值。这些值应当按升序排列。只要存在，Excel将统计在各个相邻边界值之间的数据出现的次数。如果省略此处的接收区域，Excel将在数据组的最小值和最大值之间建立一组平滑分布的接收区间。2021/6/286标志：如果输入区域的第一行或第一列中包含标志项，则选中此复选框；如果输入区域没有标志项，则清除此复选框，Excel将在输出表中生成适宜的数据标志。输出区域：在此输入计算结果显示的单元格地址。如果不输入具体位置将覆盖已有的数据，Excel会自动确定输出区域的大小并显示信息。柏拉图：选中此复选框，可以在输出表中同时显示升序、将序排列频率数据。如果此复选框被清除，Excel将只按升序来排列数据。累积百分比：选中此复选框，可以在输出结果中添加一列累积百分比数值，并同时在直方图表中添加累积百分比折线。如果清除此选项，则会省略以上结果。图表输出：选中此复选框，可以在输出表中同时生成一个嵌入式直方图表。2021/6/287[结果]操作得到的结果如图所示。通常包括三列和一个频数分布图，第一列是数值的区域范围，第二列是数值分布的频数（不是频率），第三列是频数分布的累积百分比。直方图是用矩形高度来表示频数分布的图形。绘制直方图时，将所研究的变量放在横轴上，频数、频率放在纵轴上。每组的频数、频率在图上就是一个长方形，长方形的底在横轴上，宽度是组距，长方形的高就是对应的频数或频率。图13-4中是一个条形图，而不是直方图，若要把它变成直方图，可按如下操作：用鼠标左键单击图中任一直条形，然后右键单击，在弹出的快捷菜单中选取“数据系列”格式，弹出数据系列格式对话框。在对话框中选择“选项”标签，把“分类间距”宽度改为零，按确定后即可得到直方图，如图所示。2021/6/2882021/6/289二、用Excel做常用统计图Excel提供的统计图有多种，包括柱形图、条形图、折线图、饼图、散点图、面积图、环形图、雷达图、曲面图、气泡图、股价图、圆柱图、圆锥图等，各种图的作法大同小异，可根据需要的类型进行适当选择。（一）饼图的绘制饼图是用圆形以及园内扇形的面积来表示数值大小的图形，主要用于表示总体中各组成部分所占的比例，常用于研究结构性问题。[案例]某地区××年因违法被捕的男性情况如下所示，根据资料利用Excel绘制饼图。2021/6/2810[步骤]先把数据输入到工作表中，如图所示，然后按下面的步骤操作：2021/6/2811第一步：选择“插入”下拉菜单中的“图表”选项。第二步：在图表类型中选择“饼图”，然后在子图表类型中选择一种类型，这里我们选用系统默认的方式。然后单击下一步按钮，打开源数据对话框。如图7所示。2021/6/2812第三步：在图标源数据对话框中填入数据所在区域，单击下一步，在图表选项中，对“标题”、“图例”和“数据标志”适当处理。如果要对图形修改，可以用鼠标双击图表，然后用鼠标双击需要修改的部分，并进行修改。[结果]即可得如图所示的饼图。2021/6/2813（二）折线图的绘制折线图主要用于比较数据变动的方向和趋势，表现数据在不同时期发展变化的不同趋势。[案例]根据福建省2005年到2009年间三大产业的总产出情况绘制折线图，描述这几年来三大产业总产值的变化趋势。2021/6/2814[步骤]第一步：资料输入工作表后，选择“插入”下拉菜单，再选择“图表”选项。第二步：在图表类型中选择“折线图”，然后在子图表类型中选择一种类型，这里我们选用如图所示的方式。然后单击“下一步”按钮，打开源数据对话框。2021/6/2815第三步：在源数据对话框中，“数据区域”中输入相关资料（可用鼠标点击并框定数据区域）。再在“系列”的“分类（X）轴标志”区域输入年份区域，如图所示第四步：资料输入后的下一步，进入“图表选项”。分别对“标题”、“坐标轴”、“网格线”、“图例”、“数据标志”和“数据表”等选项进行设置，当然设置各选项时根据需要进行取舍。最后点击“完成”，就在工作表中得到折线图。[结果]经过上述各步骤，在工作表中得到折线图：2021/6/2816第二节用Excel计算描述统计量一、利用“数据分析”功能[案例]根据某县的一项关于乡镇企业工资的调查，下面是40名雇员的月工资按升幂排列的一个样本，对该企业雇员工资进行描述统计分析。（单位：元）2742792903263293413784054365005155415585666187087608678688698889159329429609759761014102510961118116611931194124312771304132713431398[步骤]第一步：在Excel的区域“A1:A40”的单元格中输入数据，从上到下排成4列。第二步：选择“工具”下拉菜单，再选择“数据分析”选项第三步：在分析工具中选择“描述统计”，如图11所示2021/6/28172021/6/2818第四步：当出现对话框时，在“输入区域”方框内键入A1:A40（或用鼠标选择这区域），在“输出选项”中选择输出区域（在此选择C4），再选择“汇总统计”（该选项给出全部描述统计量）。第五步：点击“确定”键。其计算结果如图所示。2021/6/28192021/6/2820二、利用“统计函数”工具计算我们可以用两种方法来得到描述统计量，除上述“数据分析”功能计算外，还可采用Excel的函数工具计算。仍以该企业40名雇员工资为例进行计算。第一步：与上面操作相同，在Excel中输入数据。第二步：选择“插入”下拉菜单中的“函数”选项，如图所示。第三步：出现的“插入函数”界面中，在“或选择类别”选项中，选“统计”。再在“选择函数”中，选“AVERAGE”（算术平均数），点击“确定”如图14所示。2021/6/282113-1313-142021/6/2822第四步：出现“AVERAGE函数参数”界面中，在“Number1”中键入A1:A40（或用鼠标选择这区域），然后点击“确定”，就能得出“算术平均数=817.85”。重复上述各步骤，还可计算“调和平均数”、“几何平均数”、“样本标准差”和“总体标准差”等统计量，只要分别选择相应的函数。常用描述统计量函数如表A-4所示2021/6/28232021/6/2824第三节用Excel进行抽样一、用Excel进行随机抽样当总体数量太大，不可能进行全面调查或者在某些情况下不需要进行全面调查又需要了解总体的情况时必须采用抽样。使用Excel进行抽样，首先要对各个总体单位进行编号，编号可以按随机原则，也可以按有关标志或无关标志，编号后，将编号输入工作表。注：本功能需要使用Excel扩展功能，如果您的Excel尚未安装数据分析，请依次选择“工具”“加载宏”，在安装光盘的支持下加载“数据分析库”。加载成功后，才可以在工具的下拉菜单中看到“数据分析”选项。2021/6/2825[案例]假定有100个总体单位，每个总体给一个编号，一共有100个编号，输入工作表后，如图所示2021/6/2826[步骤]先输入各总体单位的编号，再按照下面的步骤操作：第一步：单击菜单栏中的“工具”选项，在下拉列表框中选择“数据分析”选项，此时弹出“数据分析”对话框，分析工具选择“抽样”。如图所示2021/6/2827第二步：单击“确定”，弹出“抽样”对话框，如图所示2021/6/2828第三步：在输入区域框中输入总体单位编号所在单元格区域，在本例是$A$1:$J$10,实际操作中一般是用鼠标将编号所在区域选中即可。系统将从A列开始抽取样本，然后按顺序抽取B列至J列。如果输入区域的第一行或第一列为标志项（横行标题或纵行标题），可单击“标志”复选框。第四步：抽样方法有周期和随机两种模式。 “周期模式”即所谓的等距抽样，采用这种抽样方法，需将总体单位数除以要抽取的样本单位数，可得到取样的周期间隔。这里我们假设要在100个总体单位中抽取10个，则在“间隔”框中输入“10”。2021/6/2829“随机模式”适用于纯随机抽样、分类抽样、整群抽样和阶段抽样。采用纯随机抽样，只需在“样本数”中直接输入要抽取的样本数即可；若用分类抽样，必须先将总体按某一标志分类编号，然后再每一类中随机抽取若干单位i，这种抽样方法实际上是分组法和随机抽样方法的结合；整群抽样也要将总体单位分类编号，然后按随机原则抽取若干类作为样本，对抽中的所有单位全部进行调查。第五步：制定输出区域，这里我们键入“F12:F22”，然后单击“确定”按钮即可。[结果]10个随机抽样的样本编号显示在工作表的“F12:F22”区域2021/6/2830二、用Excel进行抽样估计——求置信区间 调用Excel中一些常用的“统计函数”可以直接进行抽样推断中的区间估计计算。下面结合案例来说明具体的操作步骤。[案例]为了解学校某班的统计学成绩情况，对该班的全体同学随机抽取13名作为调查样本，统计抽取得到的样本如A列所示。假设该班学生的统计学成绩总体上服从正态分布，用95%的置信水平估计该班学生统计学成绩的置信区间。为构造区间估计的工作表，在工作表中先输入以下内容：A列中输入样本数据，B列输入各统计指标名称，C列中输入计算公式，D列输入计算的结果。2021/6/2831自由度为样本数-1（n-1），上式有误2021/6/2832[步骤]第一步：把样本数据输入A2:A14单元格。第二步：在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”。再输入每一个公式回车后，即可得到如表的结果。2021/6/2833自由度为n-1，有误2021/6/2834[结果]从上面的结果我们可以知道，该班学生的统计学成绩的置信上限是84.0039804，置信下限是61.53448112.计算结果表明，我们有95%的把握认为该班学生的统计学成绩平均在61.53448112-84.0039804之间。利用上面的统计指标，输入不同的样本数据，根据指标所在位置的变化对计算公式作相应的更改，则可以计算不同样本的置信区间。2021/6/2835第四节用Excel进行假设检验假设检验包括一个正态总体的参数检验和两个正态总体的参数检验。对于一个正态总体参数的检验，可以直接利用函数工具和输入公式的方法计算相应的统计量，并进行检验。这里主要介绍如何利用Excel进行两个正态分布总体的均值差检验。Excel中提供了四种假设检验模式：平均值的成对二样本分析、双样本等方差假设、双样本异方差假设和双样本平均差检验。其中较为常用的是后三种，所以这里就不介绍第一种。可以用以图来说明后三种模式适用的条件。2021/6/2836[案例]设有A、B两种安眠药，为了比较他们的治疗效果。以X表示失眠病人服下A药睡眠时间延长的时数；以Y表示失眠病人服下B药睡眠时间延长的时数.现在独立观察20个病人，其中10个服下A药，另10个服下B药，延长时数的情况所图中A列和B列所示。假设X与Y都服从正态分布。试问，这两种药的疗效是否有显著的差异。2021/6/2837[步骤]第一步：选择“工具”下拉菜单，再选择“数据分析”选项第二步：在“分析工具”选择“t检验：双样本异方差检验”（这里总体方差未知，且假设两个对比样本的方差不相等）如图所示。2021/6/2838第三步：当出现对话框时，在“变量1的区域”方框内输入A2:A11，实际操作中只要选中这部分区域即可；在“变量2的区域”方框内输入B2:B11；在“假设平均差”方框内键入“0”；“”默认为0.05；在“输出选项”中选择“新工作表组”，如图所示。待所有选项设置好后，单击“确定”按钮。2021/6/2839[结果]输出结果如表所示计算的P值为0.206253，大于0.05。所以不拒绝原假设，认为两样本的均值是相等的。即在本例中，认为A、B两种药品的疗效没有显著性差异。2021/6/2840第五节用Excel进行相关与回归分析一、相关分析相关分析就是对两个变量之间线性关系的描述和度量，利用Excel进行相关分析时，先要绘制散点图大致描绘出两个变量变化趋势的关系形态，散点图的做法前面已经介绍，这里就不再赘言，如果是线性关系，再计算相关系数来度量两个变量之间的关系强度。用Excel计算相关系数有两种方法，一是直接利用相关系数函数计算，如“CORREL函数”和“PEARSON函数”；另一种是利用“数据分析”中的“相关系数”工具。这里主要介绍后者。2021/6/2841[案例]7个省区人均GDP（元）和建筑合同的价值（亿元）资料如下表2021/6/2842根据资料数据先绘制散点图如图所示2021/6/2843从散点图可以看出，两个变量之间存在一定的正线性关系下面再用Excel计算相关系数，并说明两变量相关的方向和程度。[步骤]第一步：将人均GDP和建筑合同价值两个变量的数据分别输入到A2:A8和B2:B8区域。第二步：选择“工具”下拉菜单，再选择“数据分析”选项。第三步：在分析工具中选择“相关系数”。第四步：出现对话框，在输入区域中键入“A2:B8”，在“输出区域”中选择输出区域，这里我们选择输入到“新工作表”，最后单击“确定”按钮。2021/6/2844[