《统计学原理与实务》课件第九章 Excel 在统计中的具体应用

运用Excel进行混凝土的统计计算

在建筑工程主体结构施工过程中，技术人员经常遇到混凝土抗压强度数理统计评定，即计算同一验收批混凝土立方体抗压强度的标准差以及回弹法检测混凝土抗压强度时的标准差计算。计算过程如采用手工计算则相当繁琐，现在工程技术人员普遍采用科学计算器中的统计功能进行计算，大大提高了工效及准确性。但如果同一验收批的数据量较多时，采用手工输入计算器的方法经常会出现某个数据输入错误或遗漏造成全部重来的现象，有时甚至算了两三次其结果都不同。导入案例

在GBJ107-87混凝土强度检测评定标准及JGJ/T23-2001回弹法检测混凝土抗压强度技术规程中的标准差可以利用Excel中标准差函数STDEV计算，方便快速地计算出结果，且输入、修改、计算、复核过程可视化。如有100个C40的混凝土试块数据(必须是28天龄期的试压报告试块数据)，在Excel中依次输入表格，一边输入一边核对，然后利用函数计算即可，如需增加或减少样本个数可以随时修改，整个过程可视化，即时自动计算。导入案例

导入案例分析：技术人员是怎样借助Excel工具，将这些大量的、分散的原始资料进行科学整理，使它系统化、条理化，同时如何借助Excel工具计算相关的统计指标，进行统计分析，以符合统计研究的需要呢？通过学习本章的内容就能找到答案。目录1第一节Excel在统计数据整理和显示中的应用2第二节Excel在描述统计量中的应用3第三节Excel在时间序列分析中的应用4第四节Excel在指数分析中的应用5第五节Excel在抽样估计中的应用6第六节Excel在相关与回归分析中的应用第一节Excel在统计数据整理和显示中的应用1一

Excel在统计数据整理和显示中的应用一、用Excel进行分组和编制频数分布表用Excel进行统计分组和编制频数分布表有两种方法，一是函数法；二是利用数据分析中的“直方图”工具。这里重点介绍利用“直方图”工具进行统计分组。直方图分析工具是一个用于确定数据的频数分布、累计频数分布，并提供直方图的分析模块。它在给定工作表中数据单元格区域和接收区间的情况下，计算数据的频数和累积频数。具体使用方法如下。一

Excel在统计数据整理和显示中的应用【例9-1】某班50名学生的英语成绩如下，试编制此调查数据的频数分布表。表9-1某班学生英语成绩学号成绩（分）学号成绩（分）310178312675310289312784310392312877310466312966310580313090310652313173310777313278310896313384310963313466311082313559311180313663311275313780311348313890311474313974311563314066311671314181311788314270一

Excel在统计数据整理和显示中的应用操作步骤：1．在“工具”菜单中，单击“数据分析”选项，弹出“数据分析”对话框，如图9-1所示。图9-1数据分析对话框一

Excel在统计数据整理和显示中的应用注意：如果用户在Excel的“工具”菜单中没有找到“数据分析”选项，说明用户安装的Excel不完整，必须在Excel中重新安装“分析工具库”内容。具体安装方法如下。①在“工具”菜单中，单击“加载宏”选项。②选中“分析工具库”和“分析工具库—VBA函数”复选框，单击“确定”按钮，将会引导用户进行安装，如图9-2所示。图9-2加载宏对话框一

Excel在统计数据整理和显示中的应用如果用户在安装Excel时选择的是“典型安装”，则需要使用CD－ROM进行安装，如果用户在安装Excel时选择的是“完全安装”，则Excel会从硬盘中直接进行安装。③安装完毕后，“数据分析”选项会自动出现在Excel的工具菜单中。一

Excel在统计数据整理和显示中的应用图9-3直方图对话框2．在“分析工具”列表框中，单击“直方图”分析工具，会弹出“直方图”对话框，如图9-3所示。一

Excel在统计数据整理和显示中的应用3．选择输入选项。输入区域：在此输入待分析数据区域的单元格引用。接收区域：表示分组标志所在的区域，在此输入接收区域的单元格引用。该区域应包含一组可选的用来定义接收区间的边界值，这些值应当按升序排列。在“输入区域”中，输入$B$10；$B$59；选好接收区域的内容：$E$2：$E$7。一

Excel在统计数据整理和显示中的应用4．选择输出选项。输出选项中可以选择输出区域、新工作表组或新工作簿。在这里选择输出区域，可以直接选择一个区域，也可以直接输出一个单元格，该单元格代表输出区域的左上角，这里常常只输入一个单元格。如本例中我们输入$Ill，因为我们往往事先并不知道具体的输出区域有多大。一

Excel在统计数据整理和显示中的应用输出选项中还有以下选项：柏拉图：选中此复选框，可以在输出表中同时按降序排列频率数据。如果此复选框被清除，Excel将只按升序来排列数据。累积百分比：选中此复选框，可以在输出表中添加一列累积百分比数值，并同时在直方图表中添加累积百分比折线。如果清除此选项，则会省略累积百分比。图表输出：选中此复选框，可以在输出表中同时生成一个嵌入式直方图表。本例中，我们选中“累积百分比”和“图表输出”两个复选框。一

Excel在统计数据整理和显示中的应用5．单击“确定”按钮，可得输出结果。如图9-4所示。图9-4频数分布和直方图一

Excel在统计数据整理和显示中的应用在默认的直方图中，柱形之间彼此分开，如果要将其连接起来，操作步骤如下：

（1）单击某个柱形，单击鼠标右键，在弹出菜单中，选择“数据系列格式”选项，弹出“数据系列格式”对话框，如图9-5所示。注意：图9-5数据系列格式对话框一

Excel在统计数据整理和显示中的应用（2）在对话框中选择“选项”标签，将间距宽度由“150”改成“0”，点击“依数据点分色”，再单击“确定”按钮，则得到直方图如图9-6所示。图9-6调整后的直方图一

Excel在统计数据整理和显示中的应用二、用Excel绘制统计图利用Excel绘制统计图比较方便，Excel提供了“图表向导”工具，帮助创建适合于数据信息和分析目的的各种图表。Excel提供的统计图有多种，包括柱形图、条形图、折线图、饼图、散点图、面积图、环形图等，各种图形的绘制方法大同小异。图表操作的基本步骤如下：1．使用“图表向导”工具制作图表之前，选定数据所在的单元格，如果希望数据的行列标题显示在图表中，则选定区域还应包括含有标题的单元格。一

Excel在统计数据整理和显示中的应用2．单击“插入”菜单中的“图表”选项，按照“图表向导”所列示的4个步骤的指令进行操作。①“图表类型”：每种类型的图表都有两种以上的子类型，选中所需要的图表类形以及它的子图形，单击“下一步”按钮。②“图表数据源”：选定包含数据和行列标志的工作表单元格，即便工作表包含多个行列标志，也能在图表中显示它们。生成图表时，应将各行列标志包含到选定区域中。若预览图表看上去很合适，则表示所选数据区正确。单击“下一步”按钮。③“图表选项”：为选定的图表设置某些标准选项。修改这些设置时，随时查看预览图表可以帮助我们确定设置是否合适。单击“下一步”按钮。④“图表位置”：可以将图表放置在工作表上，或者单独置于一张新的图表工作表上。在这一步中，我们可以为图表工作表命名，或者选择现有工作表的名称，单击“完成”按钮。一

Excel在统计数据整理和显示中的应用根据图9-7广告类型和从业人数的数据，制作拼图。【例9-2】图9-7广告从业数据一

Excel在统计数据整理和显示中的应用操作步骤：1．选中某一单元格，单击“插入”菜单，选择“图表”选项，弹出“图表向导”对话框。在图表类型中选择饼图，然后在子图表类型中选择一种类型，这里选用系统默认的方式。如图9-8所示。图9-8插入图表对话框一

Excel在统计数据整理和显示中的应用2．单击“确定”按钮，即可得到简单的饼图，如图9-9所示。图9-9简单饼图3．选定图表，单击“布局”菜单，选取“图表标题”按钮，如图9-10所示。图9-10输入饼图标题对话框一

Excel在统计数据整理和显示中的应用4．输入图表的标题，如本例中输入“广告从业人员数”，如图9-11所示。图9-11含标题的饼图5．单击“设计”菜单，选取“图表布局”某一按钮，如图9-12所示。图9-12广告从业人员数饼图第二节Excel在描述统计量中的应用2二

Excel在描述统计量中的应用一、平均指标（一）算术平均值【例9-3】2005年3月20日10种股票的收盘价格如表9-2所示，试求该10种股票价格当日的算术平均值。代码证券名价格（元）代码证券名价格（元）600000浦发银行7.24600011华能国际6.97600004白云机场8.54600015华夏银行4.05600006东风汽车2.94600016民生银行5.69600008首创股份8.20600018上港集箱16.37600009上海机场16.65600019宝钢股份6.17表9-22005年3月20日10种股票收盘价二

Excel在描述统计量中的应用采用AVERAGE函数求算术平均值。除采用公式外，Excel还给出了AVERAGE函数来求算术平均值，具体操作步骤如下：1．建一工作表，输入表头“股票价格算术平均值”和表9-1中信息。2．单击C13单元格，单击插入函数按钮，选择[统计]/[AVERAGE]，Excel默认对C3：C12单元格求算术平均值，按回车键即可。从图9-13可以看出，10种股票的平均价格为8.282元。图9-13采用AVERAGE函数求算术平均值二

Excel在描述统计量中的应用（二）几何平均值【例9-4】上证180指数的2003年12月和2004年每月的指数，如表9-3所示，求2004年12个月指数的平均收益率。表9-3上证180指数13个月观测值月份指数值月份指数值2003122828.802004072548.492004013019.042004082479.162004023140.482004092591.002004033213.482004102452.732004042912.812004112490.282004052819.492004122362.072004062528.44——二

Excel在描述统计量中的应用使用GEOMEAN函数求几何平均值。Excel给出了GEOMEAN函数来求几何平均值，具体操作步骤如下。1．在工作表中输入已知信息，求出对应12个月收益率r和12个月l+r的值，具体步骤参见公式求几何平均数。2．在获得12个月1+r后运用GEOMEAN函数求几何平均值。单击Dl6单元格，单击插入函数按钮，选择[统计]/[GEOMEAN]，在出现GEOMEAN函数的[函数参考]对话框，单击[Num-berl]后的折叠按钮，选择D4：D15对应的单元格，完成后单击[确定]按钮。二

Excel在描述统计量中的应用3．单击D16单元格，单击编辑栏，在编辑GEOMEAN函数后输入“－1”。从图9-14可以看出，该段时间上证180指数的平均收益率为-1.491%。图9-14使用GEOMEAN函数求几何平均值二

Excel在描述统计量中的应用（三）众数【例9-5】表9-4中给出了某公司员工工资的频数分布，试确定员工工资的众数。表9-4

某公司员工工资频数分布工资（元）员工数（人）工资（元）员工数（人）2000～250083500～4000142500～3000104000～4500103000～3500164500～50003二

Excel在描述统计量中的应用利用公式求员工工资的众数，具本操作步骤如下：1．新建工作表，输入表头“组数据的众数”，输入表9-4中已知信息。2．确定包含众数组的下组限，对应频数最大组为“3000～3500”即是众数组，对应下组限为3000。3．确定众数所在组的频数减去前一组频数，单击B9单元格，在编辑栏输入“=B5－B4”，按回车键；确定众数所在组的频数减去后一组的频数，单击B10单元格，在编辑栏输入“=B5－B6”，按回车键。二

Excel在描述统计量中的应用图9-15某公司员工工资的众数

4．根据公式求出众数。单击B11单元格，在编辑输入“=3000+（B9/（B9+B10））*500”，完成后按回车键，最终结果如图9-15所示。从图9-15中可以看出，该公司员工工资的众数为3375元。二

Excel在描述统计量中的应用（四）中位数表9-5上证180指数数据【例9-6】表9-5中给出上证180指数某时间段的观测值数目和累积频数，试确定上证180指数此时间段内的中位数。组号上证180观测值数目累积频数组号上证180观测值数目累积频数1≤24001162800～290092522400～25003472900～300032832500～26005983000～310033142600～270031293100～320033452700～2800416103200～3300236二

Excel在描述统计量中的应用具体操作步骤如下：1．新建工作表，输入表头“组数据的中位数”，输入表9-5中已知信息。2．确定中位数的观测值位置，为（观测值总数+1）/2。单击D12单元格，在编辑栏输入“=（C11+1）/2”。3．按插值法确定指数的中位数，单击D13单元格，在编辑栏输入“=2800+（D12－C6）/D7*100”，按回车键。二

Excel在描述统计量中的应用图9-16组数据的中位数最终结果如图9-16所示。

从图9-16中可以看出，该段时间内上证180指数的中位数为2827.778。二

Excel在描述统计量中的应用（五）调和平均数【例9-7】求5，8，12，16，17，9，20的调和平均数。根据公式求调和平均数，具体操作步骤如下：1．新建工作表，输入表头“求调和平均数”输入已知数据X。2．求1/X，单击B3单元格，在编辑栏输入“=1/A3”。单击B3单元格，拖动鼠标至B9单元格，运用自动填充单元格求出所有的1/X。3．根据公式求调和平均数，单击B10单元格，在编辑栏输入“=7/SUM（B3：B9）”。二

Excel在描述统计量中的应用图9-17求调和平均数最终结果如图9-17所示。从图9-17中可以看出，对应数据的调和平均数为10.13。二

Excel在描述统计量中的应用二、标志变异指标（一）方差如果选用算术平均值作为度量集中趋势的方法，则方差和标准差就是合适的离中趋势度量指标，方差和标准差被广泛地应用于金融领域中作为风险和不确定性的度量。标志变异指标较常用的方法有：方差和标准差。二

Excel在描述统计量中的应用表9-6浦发银行股价

【例9-8】表9-6中有浦发银行（600000）10天的股票价格数据，试确定股价样本的方差。日期价格（元）日期价格（元）200503077.65200503147.37200503087.78200503157.16200503097.57200503167.16200503107.4520050317741200503187.19二

Excel在描述统计量中的应用除了采用定义来求样本的方差外，EXCEL还专门给出了样本方差VAR来实现样本方差的快速求解。采用VAR函数求股价样本的方差，具体操作步骤如下：1．在工作表中，输入股价信息。2．单击B28单元格，单击插入函数按钮，选择[统计]/[VAR]，在[函数参考]对话框，单击[NUMBERI]中折叠按钮，选择BT8：B27对应的单元格区域，完成后单击[确定]按钮。二

Excel在描述统计量中的应用图9-18使用VAR求样本方差最终结果如图9-18所示。从图9-18中可以看出，浦发银行股价样本的方差为0.05220。二

Excel在描述统计量中的应用（二）标准差表9-6浦发银行股价【例9-9】仍用表9-6资料确定股价样本的标准差。日期价格（元）日期价格（元）200503077.65200503147.37200503087.78200503157.16200503097.57200503167.16200503107.4520050317741200503187.19二

Excel在描述统计量中的应用图9-19未分组数据的标准差采用STDEV函数求样本的标准差，具体操作步骤如下：1．建工作表，输入表9-6中日期和价格。2．求样本的标准差，单击B13单元格，在编辑栏输入“=STDEV（B3：B12）”，按回车键。从图9-19中可以看出，浦发银行股价的标准差为0.2284。第三节Excel在时间序列分析中的应用3三Excel在时间序列分析中的应用一、测定增长量和平均增长量图9-20我国1998～2007年国内生产总值【例9-10】根据我国1998～2007年国内生产总值，计算逐期增长量、累计增长量和平均增长量。原始数据如图9-20。三Excel在时间序列分析中的应用图9-21计算逐期增长量、累计增长量和平均增长量的结果计算步骤如下。1．计算逐期增长量。在C3中输入公式：=B3－B2，并用鼠标拖曳将公式复制到C3：C11区域。2．计算累计增长量。在D3中输入公式：=B3－$B$2，并用鼠标拖曳公式复制到D3：D11区域。3．计算平均增长量（水平法）。在C13中输入公式：=（B11－B2）/9，按回车键，即可得到平均增长量。见图9-21三Excel在时间序列分析中的应用二、测定发展速度和平均发展速度【例9-11】以【例9-10】中我国1998～2007年国内生产总值为例，说明如何计算定基发展速度、环比发展速度和平均发展速。1．计算定基发展速度。在C3中输入公式：=B3/$B$2，并用鼠标拖曳将公式复制到C3：C11区域。2．计算环比发展速度。在D3中输入公式：=B3/B2，并用鼠标拖曳将公式复制到D3：D11区域。3．计算平均发展速度（水平法）。选中D13单元格，单击插入菜单，选择函数选项，出现插入函数对话框后，选择GEOMEAN（返回几何平均值）函数，在数值区域中输入D3：D11即可。三Excel在时间序列分析中的应用图9-22计算发展速度和平均发展速度结果三Excel在时间序列分析中的应用三、计算长期趋势【例9-12】我们用我国1988～2007年国内生产总值资料来说明如何用移动平均法计算长期趋势。1．计算三项移动平均。在C3中输入“（B2+B3+B4）/3”，并用鼠标拖曳将公式复制到C4：C20区域。2．计算四项移动平均。在D4中输入“=SUM（B2：B5）/4”，并用鼠标拖曳将公式复制到D5：D20区域。3．计算二项移动平均：在E4中输入“（D4+D5）/2”，并用鼠标拖曳将公式复制到E5：E19区域。

结果见图9-23三Excel在时间序列分析中的应用图9-23计算长期趋势资料及结果三

Excel在时间序列分析中的应用四、计算季节变动图9-24计算季节变动资料

【例9-13】利用某种商品四年分季度的销售额资料，说明如何用移动平均趋势剔除法测定季节变动。如图9-24所示。三

Excel在时间序列分析中的应用1．按图上的格式在A列输入年份，在B列输入季别，在C列输入销售额。2．计算四项移动平均。在D3中输入“=SUM（C2：C4）/4”，并用鼠标拖曳将公式复制到D3：D15区域。3．计算趋势值（即二项移动平均）T。在E4中输入“=（D3+D4）/2”，并用鼠标拖曳将公式复制到E4：E15区域。4．剔除长期趋势，即计算Y/T。在F4中输入“=C4/E4”，并用鼠标拖曳将公式复制到F4：F15区域。5．重新排列F4：F15区域中的数字，使同季的数字位于一列，共排成四列。6．计算各年同季平均数。在B6单元格中输入公式：=average（B3：B5）；在C6中输入公式=average（C3：C5）；在D6中输入公式=average（D2：D4）；在E6中输入公式=average（E2：E4）。三

Excel在时间序列分析中的应用图9-25计算季节变动结果7．计算调整系数。在B8中输入公式：=4/sum（B6：E6）8．计算季节比率。在B7中输入公式：=B6*$B$8，并用鼠标拖曳将公式复制到单元格区域B7：E7，就可以得到季节比率的值，具体结果见图9-25。第四节Excel在指数分析中的应用4四

Excel在指数分析中的应用指数分析是利用指标间形成的指标体系，在编制指数的基础上进行的因素分析。从数量上分析研究对象的变动，分别受各因素影响的方向、程度及绝对的数量。例如，在分析工人平均工资变动时，要分析有多大程度是受各工资级别的人数比重变化的影响，有多大程度取决于各工资级别的工资平均变动的影响。利用Excel工具表和各个指标的分解形式，可以分析经济现象的指数体系，以及在此基础上进行因素分析。本节主要讨论利用Excel计算综合指数和平均指数，并进行因素分析。四

Excel在指数分析中的应用一、综合指数的计算表9-74种产品的产量和出厂价格【例9-14】某工厂生产的4种产品的产量和出厂价格如表9-7所示。产品名称单位产量价格基期报告期基期报告期甲台202177乙吨80884241丙件32301012丁套586089四

Excel在指数分析中的应用图9-26输入基期、报告期的产量和价格1．将数据输入工作表中，如图9-26所示。四

Excel在指数分析中的应用图9-27计算期、报告期产量和出厂价格和乘职2．在F2单元格中输入“=B2*D2”，按Enter确认，然后拖动鼠标至F5，得出基期产量和基期价格的乘积；在G2中输入“=C2*D2”，按Enter确认，然后拖动鼠标至G5，得出报告期产量和基期价格的乘积；在H2中输入“=C2*E2”，按Enter确认，然后拖动鼠标至H5，得出报告期产量和报告期价格的乘积。结果分别如图9-27第F列、G列、H列所示。四

Excel在指数分析中的应用3．光标移至F6单元格，点击工具栏上的自动求和符号，按Enter确认，得出基期产量和基期价格乘积的总和。在G6和H6单元格中进行相同操作，可以分别得出基期价格和报告期产量乘积的总和以及报告期产量和报告期价格乘积的总和。四

Excel在指数分析中的应用图9-28综合指数计算结果4．在G7单元格中输入“=G6/F6"，即可得出产量综合指数为1.0791，即该工厂全部工业产品产量综合增长了7.91%；在H7中输入“=H6/G6”，即可得出价格综合指数为1.0069，即该工厂全部工业产品价格综合上涨0.69%。结果如图9-28。四

Excel在指数分析中的应用二、平均指数的计算平均指数法有加权算术平均法、加权调和平均法和固定权数加权平均法。本节主要介绍利用Excel求加权算术平均指数。【例9-15】以【例9-14】的资料为例，利用加权算术平均法计算该工厂的销售量指数。1．在F2单元格中输入“=B2*D2”，按Enter确认，然后拖动鼠标至F5，得出基期产量和基期价格的乘积。2．求出个体数量指标指数，在G2中输入“=C2/B2”，按Enter确认，然后拖动鼠标至G5，得出个体数量指标指数。3．在H2中输入“=G2*F2”，按Enter确认，然后拖动鼠标至H5，得出工厂基期销售额与个体销售指数的乘积。4．分别对F列和H列加总求和。四

Excel在指数分析中的应用图9-29计算销售量指数5．在H7中输入“=H6/F6"，即可得到销售量指数。由表中可以看出与用综合指数法算得的结果相同。具体的操作结果见图9-29。四

Excel在指数分析中的应用三、因素分析（一）总量指标的因素分析表9-8某工厂2006年生产情况对于总量指标的因素分析，本部分只介绍总量指标变动的两因素分析。【例9-16】某工厂2006年生产情况如表9-8所示。对生产费用的变动进行因素分析。产品名称单位产量单位成品成本/元基期报告期基期报告期甲台160020001200900乙吨606045004000丙件40425500050000四

Excel在指数分析中的应用图9-30输入基期、报告期的产量和成本1．把数据输入工作表中，如图9-30所示。四

Excel在指数分析中的应用图9-31计算基期、报告期产量和成本的乘积2．在F2单元格中输入“=B2*D2”，按Enter确认，然后拖动鼠标至F4，得出基期产量和基期成本的乘积；在G2中输入“=C2*D2”，按Enter确认，然后拖动鼠标至G4，得出报告期产量和基期成本的乘积；在H2中输入“=C2*E2”，按Enter确认，然后拖动鼠标至H4，得出报告期产量和报告期成本的乘积。操作如图9-31所示。四

Excel在指数分析中的应用3．分别对F列、G列、H列加总求和。4．在F6中输入“=H5/F5”，得出生产支出总额指数为94.3%，F7中输入“=H5－F5”，得出结果为－25万元。说明该工厂生产支出总额报告期比基期下降了5.7%，绝对额减少了25万元。在G6中输入“=H5/G5”，得出产品成本指数为83.13%，在G7中输入“=H5－G5”，得出结果为－84万元。说明该工厂单位产品成本水平下降了16.87%。从而节约了生产费用84万元。在H6中输入“=G5/F5”，得出产品产量指数为113.4%，在H7中输入“=G5－F5”，得到结果为59万元，说明该工厂由于产品产量增加了13.4%，使得支出总额增加了59万元。具体结果如图9-32所示。四

Excel在指数分析中的应用图9-32计算生产支出总额指数、产品成本指数、产品产量指数四

Excel在指数分析中的应用（二）平均指标变动的因素分析表9-9某工厂职工的工资资料对于平均指标变动的因素分析，举例加以说明。

【例9-17】某工厂职工的工资资料如表9-9所示。分析职工工资水平和工人结构变动对平均工资的影响。

月工资/元工人数/人基期报告期基期报告期技术工人580620245250辅助工人400420120800四

Excel在指数分析中的应用图9-33输入基期、报告期的月工资和工人数1．在工作中表输入数据，输入结果如图9-33所示。四

Excel在指数分析中的应用图9-34计算基期、报告期月工资和工人数的乘职2．在F2中输入“=B2*D2”，拖动鼠标至F3；在G2中输入“=B2*E2”，拖动鼠标至G3；在H2中输入“=C2*E2”拖动鼠标至H3。3．分别对D列、E列、F列、G列、H列加总求和。结果如图9-34所示。四

Excel在指数分析中的应用4．在F5中输入公式“=（H4/E4）/（F4/D4）”，得出工资平均指数为89.78%；在F6中输入“=（H4/E4）－（F4/D4）”，得出结果为－53.2元。说明该工厂职工平均工资报告期比基期下降了10.21%，绝对额减少了53.2元。在G5中输入“=（G4/E4）/（F4/D4）”，得出结构影响指数为85.03%；在G6中输入“=（G4/E4）－（F4/D4）”，得到结果为－77.96元。说明在该工厂职工工资水平不变的情况下，由于职工人数结构发生变化使得平均工资减少了77.96元。在H5中输入“=（H4/E4）/（G4/E4）”，得出固定构成指数为105.59%；在H6中输入“（H4/E4）－（G4/E4）”，得到结果为24.76元。说明在人数构成不变的情况下，由于职工工资水平变化使得工资增加了24.76元。具体结果如图9-35所示。四

Excel在指数分析中的应用图9-35计算工资平均指数、结构影响指数和固定构成指数第五节Excel在抽样估计中的应用5五Excel在抽样估计中的应用Excel中一个总体均值、总体方差和总体比例的抽样估计可以用使用公式和函数或数据分析工具进行，两个总体均值之差也可按类似方法进行，下面举例说明。【例9-18】某饭店在7周内抽查49位顾客的消费额（元）如表9-10所示，估计概率保证程度为90%时顾客平均消费额的区间。表9-1049位顾客的消费额数据15243826304218302526344420352426344818284619303642243245362147262831424536242827323647532224324626

五Excel在抽样估计中的应用图9-36顾客的消费额数据1．把数据输入到A2：A50单元格，如图9-36所示五Excel在抽样估计中的应用图9-37顾客的消费额区间估计结果2．在C2中输入公式“=COUNT（A2：A50）”，C3中输入“=AVERAGE（A2：A50）”，在C4中输入“STDEV（A2：A50）”，在C5中输入“=C4/SQRT（C2）”，在C6中输入0.90，在C7中输入“=C2－1”，在C8中输入，“=TINY（1-C6，C7）”，在C9中输入“=C8*C5”，在C10中输入“=C3－C9”，在C11中输入“=C3+C9”。在输入每一个公式按回车键后，便可得到上面的结果。从上面的结果可以知道，顾客平均消费额的置信下限为29.73536，置信上限为34.26464，如图9-37所示。五Excel在抽样估计中的应用二、数据分析工具图9-36顾客的消费额数据1．把数据输入到A2：A50单元格，如图9-36所示。五Excel在抽样估计中的应用图9-38描述统计对话框（置信度90%）2．选择“工具”下拉菜单，单击“数据分析”，在分析工具中选择“描述统计”，在“输入区域”键入“A2：A50”，在“输出区域”键入“B1”，选择“汇总统计”，平均数置信度选择“90%”，如图9-38所示。五Excel在抽样估计中的应用图9-39描述统计结果（置信度90%）3．选择“确定”，得到输出结果，如图9-39所示。五Excel在抽样估计中的应用图9-40顾客的消费额区间估计结果4．选择单元格D2（置信区间下限），键入单元格公式“=C3－C18”，选择单元格D3（置信区间上限），键入单元格公式“=C3+C18”，C3是样本均值，C18是误差范围（极限误差）。结果如图9-40所示。第六节Excel在相关与回归

分析中的应用6六

Excel在相关与回归分析中的应用一、相关图的绘制表9-118个同类企业产品产量和生产费用的相关表【例9-19】根据表9-11相关数据，绘制相关图。企业编号月产品产量（千吨）月生产费用（万元）12.412424.117036.016247.5200510.0225612.4265714.1280816.2310六

Excel在相关与回归分析中的应用图9-418个同类企业产品产量和生产费用数据制作相关图的步骤如下：1．建立工作表，输入表9-11相关数据，选择区域Al：B9，如图9-41所示。六

Excel在相关与回归分析中的应用图9-42散点图类型的选择

2．点击“插入图表”中选择“XY散点图”；如附图9-42所示。在“子图表类型”中选择第一种散点图，并点击“确定”。六

Excel在相关与回归分析中的应用图9-43月产品产量与生产费用的散点图

3．点击“确定”，并对图形进行修饰编辑，得到图9-43所示月产品产量与生产费用之间的散点图。六

Excel在相关与回归分析中的应用图9-44设置趋势线格式选择图

4．在散点图的任意一个点上按右键—添加趋势线—类型，选与散点图近似的趋势线如图9-44所示。六

Excel在相关与回归分析中的应用图9-45月产品产量与生产费用的趋势线图

5．选择选项中“☑“”显示公式和“☑”显示R平方值，点击确定，最后得到如图9-45所示的趋势线图、回归方程和R平方值。六

Excel在相关与回归分析中的应用二、相关系数在Excel中，相关系数函数和相关系数宏提供了两种计算相关系数的方法。1.相关系数函数。在Excel中，CORBEL函数和PERSON函数提供了计算两个变量之间的相关系数的方法，这两个函数是等价的。与相关系数有关的函数还有RSQ（相关系数的平方，即判定系数）和COVAR（协方差函数）。六

Excel在相关与回归分析中的应用【例9-20】以表9-11中资料为例，利用函数CORBEL函数计算相关系数。表9-118个同类企业产品产量和生产费用的相关表企业编号月产品产量（千吨）月生产费用（万元）12.412424.117036.016247.5200510.0225612.4265714.1280816.2310六

Excel在相关与回归分析中的应用图9-46CORBEL函数计算相关系数1．点击Excel函数图钮“”，选择“统计”函数。2．在统计函数点击“CORBEL”，进入函数向导。3．在“Array1”中输入第一个变量“月产品产量”的数据区域A2：A9。在“Array2”中输入第二个变量“生产费用”的数据区域B