EXCEL在统计学中的应用.doc

Excel在统计学中的应用统计分析常用的软件有SAS、SPSS和Excel等，其中Excel应用较为普遍。本附录主要介绍Excel在统计中的应用，并用实例操作的形式进行介绍。一、用Excel作数据的频率分布表和直方图利用Excel处理数据，可以建立频率分布表和条形图。一般统计数据有两大类，即定性数据和定量数据。定性数据用代码转化为定量数据后再处理，这里就不涉及了，下面主要以定量数据为例来说明如何利用Excel进行分组，并作频率分布表和直方图。 资料现有某管理局下属40个企业产值计划完成百分比资料如下：97、123、119、112、113、117、105、107、120、107、125、142、103、115、119、88、115、158、146、126、108、110、137、136、108、127、118、87、114、105、117、124、129、138、100、103、92、95、127、104(1)据此编制分布数列（提示：产值计划完成百分比是连续变量）；(2)计算向上累计频数（率）；(3)画出次数分布直方图。步骤第1步：打开Excel界面，输入40个企业的数据，从上到下输入A列（也可分组排列）。第2步：选择“工具”下拉菜单，如附图11。附图11第3步：选择“数据分析”选项，如果没有该功能则要先行安装。“数据分析”的具体安装方法，选择“工具”下拉菜单中“加载宏”，在出现的选项中选择“分析工具库”，并“确定”就可自动安装。附图12 第4步：在分析工具中选择“直方图”，如附图12。附图13第5步：当出现“直方图”对话框时，在“输入区域”方框内键入A2:A41或$A$2：$A$41（“$”符号起到固定单元格坐标的作用，表示的是绝对地址），40个数据已输入该区域内，如果是分组排列的，就应选择整个分组区域。在“接收区域”方框内键入C2：C9或$C$2：$C$9，所有数据分成8组（主要根据资料的特点，决定组数、组距和组限），把各组的上限输入该区域内。在“输出区域”方框内键入E2或$E$2，也可重新建表在其他位置。对话框中，还选择“累积百分率”、“图表输出”（如附图13）。最后：点“确定”，就可得到结果。对话框内主要选项的含义如下：输入区域：在此输入待分析数据区域的单元格范围。接收区域（可选）：在此输入接收区域的单元格范围，该区域应包含一组可选的用来计算频数的边界值。这些值应当按升序排列。只要存在的话，Excel 将统计在各个相邻边界值之间的数据出现的次数。如果省略此处的接收区域，Excel 将在数据组的最小值和最大值之间创建一组平滑分布的接收区间。标志：如果输入区域的第一行或第一列中包含标志项，则选中此复选框；如果输入区域没有标志项，则清除此该复选框，Excel 将在输出表中生成适宜的数据标志。输出区域：在此输入计算结果显示的单元格地址。如果不输入具体位置将覆盖已有的数据，Excel 会自动确定输出区域的大小并显示信息。柏拉图：选中此复选框，可以在输出表中同时显示按升序、降序排列频率数据。如果此复选框被清除，Excel 将只按升序来排列数据。累积百分比：选中此复选框，可以在输出结果中添加一列累积百分比数值，并同时在直方图表中添加累积百分比折线。如果清除此选项，则会省略以上结果。图表输出：选中此复选框，可以在输出表中同时生成一个嵌入式直方图表。结果有关结果如附图14。完整的结果通常包括三列和一个频数分布图，第一列是数值的区间范围，第二列是数值分布的频数（不是频率），第三列是频数分布的累积百分比。 附图14直方图是用矩形的宽度和高度来表示频数分布的图形。绘制直方图时，将所研究的变量放在横轴上，频数、频率放在纵轴上。每组的频数、频率在图上就是一个长方形，长方形的底在横轴上，宽度是组距，长方形的高就是对应的频数或频率。应当注意，上图实际上是一个条形图，而不是直方图，若要把它变成直方图，可按如下操作：用鼠标左键单击图中任一直条形，然后右键单击，在弹出的快捷菜单中选取“数据系列”格式，弹出数据系列格式对话框。在对话框中选择“选项”标签，把“分类间距”宽度改为0，按确定后即可得到直方图，如附图15所示。附图15 二、用Excel作常用统计图Excel有较强的作图功能，可根据需要选择各类型的图形。Excel提供的统计图有多种，包括柱形图、条形图、折线图、饼图、散点图、面积图、环形图、雷达图、曲面图、气泡图、股价图、圆柱图、圆锥图等，各种图的作法大同小异。（一）饼图的绘制饼图也称圆形图，是用圆形及圆内扇形的面积来表示数值大小的图形。饼图主要用于表示总体中各组成部分所占的比例，对于研究结构性问题十分有用。资料据中国互联网络信息中心2006年6月底的统计，我国目前网民的年龄分布如下表（附表21），根据资料利用Excel绘制饼图。我国目前网民的年龄分布结构表附表21年龄比重%18岁以下14.90%1824岁38.90%2530岁18.40%3135岁10.10%3640岁7.50%4150岁7.00%5160岁2.40%60岁以上0.80%步骤先把数据输入到工作表中，如附图21所示，可按下面的步骤操作。第1步：选择“插入”下拉菜单，选择“图表”。第2步：在图表类型中选择“饼图”，然后在子图表类型中选择一种类型，这里我们选用系统默认的方式。然后单击下一步按钮，打开源数据对话框。如附图22。附图21 附图22第3步：在图表源数据对话框中填入数据所在区域，单击下一步，在图表选项中，对“标题”、“图例”和“数据标志”适当处理。如果要对图形修改，可用鼠标双击图表，然后用鼠标双击需要修改的部分，并进行修改。结果即可得如附图23所示的饼图。附图23 （二）折线图的绘制折线图主要用于比较几类数据变动的方向和趋势，表现数据在不同时期发展变化的不同趋势。资料根据我国20012005年外贸货物进出口总额资料（如附表22），绘制折线图，描述我国近年来货物进出口额的变化趋势。附表22单位：人民币亿元年份20012002200320042005货物进出口总额 42183.651378.270483.595539.1116921.8出口总额22024.426947.936287.949103.362648.1进口总额20159.224430.334195.646435.854273.7 步骤第1步：资料输入工作表后，选择“插入”下拉菜单，再选择“图表”。第2步：在图表类型中选择“折线图”，然后在子图表类型中选择一种类型，这里我们选用如附图24的方式。然后单击下一步按钮，打开源数据对话框。附图24第3步：在源数据对话框中，“数据区域”中输入相关资料（可用用鼠标点击并框定数据区域）。再在“系列”的“分类（x）轴标志”区域输入年份区域，如附图25。第4步：资料输入后的下一步，进入“图表选项”。分别对“标题”、“坐标轴”、“网格线”、“图例”、“数据标志”和“数据表”等选项进行设置，当然设置各选项时根据需要进行取舍。最后点“完成”，就在工作表中得到折线图。附图25结果经过上述各步骤，在工作表中得到折线图，如附图26所示。附图26 三、用Excel计算描述统计量 我们学习了平均指标，也掌握了测定数据的集中趋势和离散程度的常用统计量，下面将利用Excel来计算这些统计量。为了说明该方法，仍用实例操作。（一）利用“数据分析”功能计算资料设某班40名学生统计学考试成绩分别为：66 89 88 84 86 87 75 73 72 68 75 82 9758 81 54 79 76 95 76 71 60 90 65 76 7276 85 89 92 64 57 83 81 78 77 72 61 7081对该班学生的考试成绩进行描述统计分析。步骤 第1步：在Excel的工作表界面中，输入40个学生的成绩数据，从上到下输入A列，放入区域“A1：A40”的单元格中。 第2步：选择“工具”下拉菜单，再选择“数据分析”选项。 第3步：在分析工具中选择“描述统计”，如附图31。附图31  第4步：当出现对话框时，在“输入区域”方框内键入A1：A40(或用鼠标选择这区域)，在“输出选项”中选择输出区域(在此选择C4)，再选择“汇总统计”(该选项给出全部描述统计量)； 最后：选择确定。 结果其计算结果如附图32所示。附图32结果分析附表31  平均(算术平均值)76.525标准误差(抽样标准误差)1.69160351中值(中位数)76模式(众数)76标准偏差(样本标准差)10.69863998样本方差(方差)114.4608974峰值(峰度系数)-0.510964335偏斜度(偏度系数)-0.206203168区域(极差或全距)43最小值(第K个最小值)54最大值(第K个最大值)97求和(标志值总和)3061计数(总频数)40置信度(95.0%)3.421587697  （二）利用“统计函数”工具计算描述统计量除上述“数据分析”功能计算外，还可采用Excel的函数工具计算。仍以40名学生统计学考试成绩为例进行计算。步骤 第1步：在Excel的工作表界面中，输入40个学生的成绩数据，从上到下输入A列，放入区域“A1：A40”的单元格中。第2步：选择“插入”下拉菜单，再选择“函数”选项，如附图33。附图33第3步：出现的“插入函数”界面中，在“或选择类别”选项中，选“统计”。再在“选择函数”中，选“AVERAGE”（算术平均数），点击确定如附图34。第4步：出现“AVERAGE函数参数”界面中，在“Number1”中键入A1：A40(或用鼠标选择这区域)，然后点“确定”，就能得出“算术平均数76.525”。附图34重复上述各步骤，还可计算“调和平均数”、“几何平均数”、“样本标准差”和“总体标准差”等统计量，只是要分别选择相应的函数。常用描述统计量函数如附表32所示。EXCEL中常用描述统计量函数对照表 附表32函数名称（英）函数名称（中）公式或符号AVEDEV平均差AVERAGE算术平均数GEOMEAN几何平均数HARMEAN调和平均数MAX最大值 MEDIAN中位数MIN最小值 MODE众数STDEV样本标准差（标准偏差）STDEVP总体标准差VAR样本方差VARP总体方差 四、用Excel进行随机抽样使用Excel进行抽样，首先要对各个总体单位进行编号，编号可以按随机原则，也可以按有关标志或无关标志，具体可参见教材有关抽样的章节，编号后，将编号输入工作表。资料假定有80个总体单位，每个总体单位给一个编号，共有从1到80个编号，输入工作表后如附图41所示：附图41步骤输入各总体单位的编号后，可按以下步骤操作：第1步：单击工具菜单，选择“数据分析”选项，打开“数据分析”对话框，从中选择“抽样”，如附图42所示。附图42 第2步：单击“抽样”选项，弹出“抽样”对话框，如附图43。附图43第3步：在输入区域框中输入总体单位编号所在的单元格区域，在本例是$A$1:$H$10，系统将从A列开始抽取样本，然后按顺序抽取B列至H列。如果输入区域的第一行或第一列为标志项（横行标题或纵列标题），可单击标志复选框。第4步：在抽样方法项下，有周期和随机两种抽样模式：“周期”模式即所谓的等距抽样，采用这种抽样方法，需将总体单位数除以要抽取的样本单位数，求得取样的周期间隔。如我们要在80个总体单位中抽取10个，则在“间隔”框中输入8。“随机模式”适用于纯随机抽样、分类抽样、整群抽样和阶段抽样。采用纯随机抽样，只需在“样本数”框中输入要抽取的样本单位数即可；若采用分类抽样，必须先将总体单位按某一标志分类编号，然后在每一类中随机抽取若干单位，这种抽样方法实际是分组法与随机抽样的结合；整群抽样也要先将总体单位分类编号，然后按随机原则抽取若干类作为样本，对抽中的类的所有单位全部进行调查。可以看出，此例的编号输入方法，只适用于等距抽样和纯随机抽样。第5步：指定输出区域，在这里我们输入$A$12，单击确定后，即可得到抽样结果。结果8个随机抽取的样本编号就显示在区域“A12：A19”单元格中。 五、用Excel求置信区间 用Excel的“统计函数”工具进行抽样推断中的区间估计测算。下面结合实例来说明具体的操作步骤。资料某商店随机抽查10名营业员，统计他们的日营业额(千元)如附图51中的“A2：A11”。假定该商店各营业员的日营业额是服从正态分布，试以95%的置信水平估计该商店营业员的日营业额的置信区间。为构造区间估计的工作表，在工作表中输入下列内容：A列输入样本数据，B列输入变量名称，C列输入计算公式，其实C列中当计算公式输入后显现的是计算结果，为了说明计算过程，在D列中展示C列的计算公式。附图51步骤第1步：把样本数据输入到A2：A11单元格第2步：在C2中输入公式“=COUNT（A2：A11）”，得到计算结果“10”。“COUNT”是计数函数，得出样本容量(n=10)。第3步：在C3中输入“=AVERAGE（A2：A11）”，在C4中输入“=STDEV（A2：A11）”，在C5中输入“=C4/SQRT（C2）”，在C6中输入0.95，在C7中输入“=C2-1”，在C8中输入“=TINV（1-C6，C7）”，在C9中输入“=C8*C5”，在C10中输入“=C3-C9”，在C11中输入“=C3+C9”。在输入每一个公式回车后，便可得到如附表51的结果。附表51样本数据计算指标计算公式计算结果42样本数据个数C2=COUNT(A2:A11)1045样本均值C3=AVERAGE(A2:A11)38.443样本标准差C4=STDEV(A2：A11)4.19523539340抽样平均误差C5=C4/SQRT(C2) 1.32664991638置信水平C6=0.950.9536自由度C7=C2-1935t值C8=TINV(1-C6，C7)2.26215888732误差范围C9=C8*C53.00109289834置信下限C10=C3-C935.398907139置信上限C11=C3+C941.4010929 结果从上面的结果我们可以知道，该商店营业员的日营业额的置信下限为35.3989071 (千元)，置信上限为41.4010929 (千元)。计算结果可以得出，我们有95%的把握认为该商店营业员的日营业额平均在35.3989071(千元)到41.4010929(千元)之间。 在附表51中，对于不同的样本数据，依上表的格式，只要输入新的样本数据，再对C列公式略加修改，置信区间就会自动给出。 六、用Excel进行假设检验假设检验包括一个正态总体的参数检验和两个正态总体的参数检验。对于一个正态总体参数的检验,可利用函数工具和自己输入公式的方法计算统计量,并进行检验。本例主要介绍如何使用Excel进行两个正态分布的均值方差的检验。资料为了评价两个学校的教学质量,分别在两个学校抽取样本。在A学校抽取30名学生,在B学校抽取40名学生,对两个学校的学生同时进行一次英语标准化考试，成绩如附表61所示。假设学校A考试成绩的方差为64，学校B考试成绩的方差为100。检验两个学校的教学质量是否有显著差异。（=0.05） 附表61学校A学校B70   97   85   87   64   7386   90   82   83   92   7472   94   76   89   73   8891   79   84   76   87   8885   78   83   84   91   7476   91   57   62   89   82   93   6480   78   99   59   79   82   70   8583   87   78   84   84   70   79   7291   93   75   85   65   74   79   6484   66   66   85   78   83   75   74假定我们将上表中学校A的数据输入到工作表中的A2：A31,学校B的数据输入到工作表的B2：B41。步骤第1步：选择“工具”下拉菜单。再选择“数据分析”选项。第2步：在分析工具中选择“Z检验:双样本平均差检验”，如附图61。附图61 第3步：当出现对话框后，在“变量1的区域”方框内键入A2：A31；在“变量2的区域”方框内键入B2：B41；在“假设平均差”方框内键入0；在“变量1的方差”方框内键入64；在“变量2的方差”方框内键入100；在“”方框内键入0.05；在“输出选项”中选择输出区域(在此选择“新工作表”)。如附图62所示。附图62第4步：所有选项设置好，选择确定。结果输出结果如附表62。附表62 变量 1变量 2平均数82.578已知协方差64100观测值个数3040假设平均差0 z2.0905749 P(Z<=z) 单尾0.018283 z 单尾临界1.6448535 P(Z<=z) 双尾0.0365661 z 双尾临界1.9599628 由于，所以拒绝，即两个学校的教学质量有显著差异。     七、用Excel进行相关与回归分析（一）相关分析相关分析可用于判断两组数据之间的关系。我们可以使用“相关分析”来确定两个区域中数据的变化是否相关。用Excel进行相关分析有两种方法，一是利用相关系数函数计算，如“CORREL函数”和“PERSON函数”；另一种是利用“数据分析”功能相关分析宏计算。这里主要介绍后者。资料有10个同类企业生产性固定资产年均价值和工业增加值资料如附表71：附表71企业编号生产性固定资产价值（万元）工业增加值（万元）131852429101019320063844098155415913650292873146058121015169102212191012251624合计65259801要求根据资料计算相关系数，并说明两变量相关的方向和程度。步骤将数据输入工作表后，按如下步骤： 第1步：选择“工具”下拉菜单，再选择“数据分析”选项。 第2步：在分析工具中选择“相关系数”。 第3步：当出现对话框时，在“输入区域”方框内键入A2：B11，在“输出选项”中选择输出区域(在此我们选择“新工作表”)。 最后：“确定”，得出附图71。附图71 结果根据上述步骤计算的相关系数矩阵如附图71所示。表中得出了两个变量之间的相关系数，如“生产性固定资产价值（万元）”与“工业增加值（万元）”的相关系数为0.947757，属于高度正相关。（二）回归分析 利用Excel可以很容易地进行回归分析，包括一元线性回归和多元线性回归。资料根据附表71的资料，编制直线回归方程，计算估计标准误，并估计生产性固定资产（自变量）为1100万元时，工业增加值（因变量）的可能值。步骤我们仍结合上面的例子说明其操作步骤： 第1步：选择“工具”下拉菜单。 第2步：选择“数据分析”选项。 第3步：在分析工具中选择“回归”。 第4步：当出现对话框时，在“输入Y的区域”方框内键入B2：B11，在“输入X的区域” 方框内键入A2：A11，在“输出选项”中选择输出区域(这里我们选择“新工作表”)。 最后：“确定”。结果得到附图72所示的结果。 附图72为了让大家看清楚，我们把有关的指标稍作解释。附图72中回归统计部分给出了判定系数、调整后的、估计标准误差等；方差分析表部分给出的显著水平F值表明回归方程是显著的；最下面的一部分是，。以及参数的标准差、t检验的统计量、p-值、下限95%和上限95%给出了参数置信区间。比如，我们有95%的把握确信，在210.4844和580.64964之间，在0.650009和1.1416632之间。除表中输出的结果外，我们还可以根据需要给出残差图、线性拟合图等。所以，该例题中得到的回归方程为：，回归估计标准误为：126.6279。当生产性固定资产万元时，工业总产值为：（万元）。 八、用Excel进行季节变动分析 为介绍Excel在季节变动分析中的应用，我们以实例操作，采用趋势剔除法计算季节指数。资料某小型企业销售收入如附表81。附表81单位：万元年份春夏秋冬200279486810720039766851342004113911001482005136105125174步骤把数据输入到工作表中的B2：B17。用Excel构造一张季节变动分析表（如附图81），计算的步骤如下：附图81  第1步：计算4项移动平均数。在C3单元格输入公式“=AVERAGE(B2：B5)”，然后将公式复制到C4：C15单元格。结果如附图82的C列。 第2步：计算计算移动平均趋势值(中心化移动平均数)。也就是对C列的结果再进行一次二项移动平均。在D4单元格输入公式“AVERAGE(C3：C4)”，然后将公式复制到D5：D15单元格。结果如附图82中的D列。 第3步：将实际值除以相应的趋势值。在E4单元格输入公式“=B4/D4”，然后将公式复制到E5：E15单元格。结果如附图82中的E列。 第4步：计算同季平均。在F2单元格输入公式“=(E6+E10+E14)/3，在F3单元格输入公式“=(E7+E11+E15)/3”，在F4单元格输入公式“=(E4+E8+E12)/3”，在F5单元格输入公式“=(E5+E9+E13)/3”。结果如附图82中的F列。 第5步：计算总平均值。在G2单元格输入公式“=AVERAGE(E4：E15)”。 第6步：计算季节指数。将同季平均值除以总平均值。在H2单元格输入公式“=F2/G2”，在H3单元格输入公式“=F3/G2”，在H4单元格输入公式“=F4/G2”，在H5单元格输入公式“=F5/G2”。结果计算结果如附图82中的H列。附图82九、用Excel进行时序预测资料某煤矿某年1-11月份采煤量如附表91：附表91单位：万吨月份产量月份产量1234569.039.069.128.738.949.307891011129.159.369.459.309.24（一）用移动平均法进行预测步骤第1步：将原始数据录入到单元格区域B2：B12，如附图91所示。附图91第2步：选择菜单条上的“工具”“数据分析”命令，弹出如附图92所示的对话框。附图92第3步：在“分析工具”框中选择“移动平均”，单击“确定”按钮，弹出移动平均对话框，相应作如下输入，即可得到如附图93所示的对话框：（1）在“输出区域”内输入：$B$2：$B$12，即原始数据所在的单元格区域。（2）在“间隔”内输入：“3”，表示使用三步移动平均法。（3）在“输出区域”内输入：“C2”，即将输出区域的左上角单元格定义为C2。（4）选择“图表输出”复选框和“标准误差”复选框。第4步：单击“确定”按钮。结果便可得到移动平均结果，如附图94所示。分析：在附图94中，“C4：C12”对应的数据即为三步移动平均的预测值；单元格区域“D6：D12”即为标准误差。 附图93附图94（二）用指数平滑法进行预测步骤第1步：将原始数据输入到单元格“B2：B12”。第2步：选择菜单条上的“工具”“数据分析”命令，弹出如附图95所示的对话框。附图95第3步：在“分析工具”中选择“指数平滑”，单击“确定”按钮，弹出一个对话框，作相应输入，即可得到如附图96所示的对话框。附图96第4步：单击“确定”按钮。结果经过以上各步骤操作，即可得到指数平滑结果，如附图97所示。“C3：C12”对应的数据即为指数平滑的预测值；单元格区域“D6：D12”即为标准误差。附图97（三）用趋势预测法进行预测步骤第1步：把相关数据输入到Excel中，其中月份输入“A2：A12”单元格，月产量输入“B2：B12”单元格。第2步：在工作表中选择一个空的单元格。在这里我们选择D2单元格。第3步：选择插入下拉菜单。第4步：选择函数选项。第5步：当函数对话框出现时：在函数类别框中选择统计，在函数名字中选择“FORECAST(预测)”，如附图98所示。 附图98第6步：单击确定按钮，出现预测对话框。在“x”中输入“12”（预测的是12月），在“know-ys”中输入“B2：B12”（因变量），在“know-xs”中输入“A2：A12”（自变量），如附图99所示。附图99 第7步：单击确定按钮。结果经过以上各步骤操作，即可得到趋势预测的结果，“D29.396545”，即该煤矿12月份的采煤量约为9.3965（万吨）。