1、v运用Excel进行混凝土的统计计算 在建筑工程主体结构施工过程中，技术人员经常遇到混凝土抗压强度数理统计评定，即计算同一验收批混凝土立方体抗压强度的标准差以及回弹法检测混凝土抗压强度时的标准差计算。计算过程如采用手工计算则相当繁琐，现在工程技术人员普遍采用科学计算器中的统计功能进行计算，大大提高了工效及准确性。但如果同一验收批的数据量较多时，采用手工输入计算器的方法经常会出现某个数据输入错误或遗漏造成全部重来的现象，有时甚至算了两三次其结果都不同。导入案例v 在GBJ 107-87混凝土强度检测评定标准及JGJ/T 23-2001回弹法检测混凝土抗压强度技术规程中的标准差可以利用Excel中
2、标准差函数STDEV计算，方便快速地计算出结果，且输入、修改、计算、复核过程可视化。如有100个C40的混凝土试块数据(必须是28天龄期的试压报告试块数据)，在Excel中依次输入表格，一边输入一边核对，然后利用函数计算即可，如需增加或减少样本个数可以随时修改，整个过程可视化，即时自动计算。导入案例v导入案例分析：技术人员是怎样借助Excel工具，将这些大量的、分散的原始资料进行科学整理，使它系统化、条理化，同时如何借助Excel工具计算相关的统计指标，进行统计分析，以符合统计研究的需要呢？通过学习本章的内容就能找到答案。目录1第一节Excel在统计数据整理和显示中的应用2第二节Excel在描
3、述统计量中的应用3第三节Excel在时间序列分析中的应用4第四节Excel在指数分析中的应用5第五节Excel在抽样估计中的应用6第六节Excel在相关与回归分析中的应用第一节Excel在统计数据整理和显示中的应用1一Excel在统计数据整理和显示中的应用一、用Excel进行分组和编制频数分布表 用Excel进行统计分组和编制频数分布表有两种方法，一是函数法；二是利用数据分析中的“直方图”工具。这里重点介绍利用“直方图”工具进行统计分组。直方图分析工具是一个用于确定数据的频数分布、累计频数分布，并提供直方图的分析模块。它在给定工作表中数据单元格区域和接收区间的情况下，计算数据的频数和累积频数。
4、具体使用方法如下。一Excel在统计数据整理和显示中的应用【例9-1】某班50名学生的英语成绩如下，试编制此调查数据的频数分布表。表9-1 某班学生英语成绩学号成绩（分）学号成绩（分）310178312675310289312784310392312877310466312966310580313090310652313173310777313278310896313384310963313466311082313559311180313663311275313780311348313890311474313974311563314066311671314181311788314270一Exc
5、el在统计数据整理和显示中的应用操作步骤：1在“工具”菜单中，单击“数据分析”选项，弹出“数据分析”对话框，如图9-1所示。图9-1 数据分析对话框一Excel在统计数据整理和显示中的应用 注意：如果用户在Excel的“工具”菜单中没有找到“数据分析”选项，说明用户安装的Excel不完整，必须在Excel中重新安装“分析工具库”内容。具体安装方法如下。在“工具”菜单中，单击“加载宏”选项。选中“分析工具库”和“分析工具库VBA函数”复选框，单击“确定”按钮，将会引导用户进行安装，如图9-2所示。图9-2 加载宏对话框一Excel在统计数据整理和显示中的应用 如果用户在安装Excel时选择的是“
6、典型安装”，则需要使用CDROM进行安装，如果用户在安装Excel时选择的是“完全安装”，则Excel会从硬盘中直接进行安装。安装完毕后，“数据分析”选项会自动出现在Excel的工具菜单中。一Excel在统计数据整理和显示中的应用图9-3 直方图对话框 2在“分析工具”列表框中，单击“直方图”分析工具，会弹出“直方图”对话框，如图9-3所示。一 Excel在统计数据整理和显示中的应用 3选择输入选项。输入区域：在此输入待分析数据区域的单元格引用。接收区域：表示分组标志所在的区域，在此输入接收区域的单元格引用。该区域应包含一组可选的用来定义接收区间的边界值，这些值应当按升序排列。在“输入区域”中
7、，输入$B$10；$B$59；选好接收区域的内容：$E$2：$E$7。一 Excel在统计数据整理和显示中的应用 4选择输出选项。输出选项中可以选择输出区域、新工作表组或新工作簿。在这里选择输出区域，可以直接选择一个区域，也可以直接输出一个单元格，该单元格代表输出区域的左上角，这里常常只输入一个单元格。如本例中我们输入$Ill，因为我们往往事先并不知道具体的输出区域有多大。一 Excel在统计数据整理和显示中的应用 输出选项中还有以下选项：柏拉图：选中此复选框，可以在输出表中同时按降序排列频率数据。如果此复选框被清除，Excel将只按升序来排列数据。累积百分比：选中此复选框，可以在输出表中添加
8、一列累积百分比数值，并同时在直方图表中添加累积百分比折线。如果清除此选项，则会省略累积百分比。图表输出：选中此复选框，可以在输出表中同时生成一个嵌入式直方图表。本例中，我们选中“累积百分比”和“图表输出”两个复选框。一 Excel在统计数据整理和显示中的应用5单击“确定”按钮，可得输出结果。如图9-4所示。图9-4 频数分布和直方图一 Excel在统计数据整理和显示中的应用 在默认的直方图中，柱形之间彼此分开，如果要将其连接起来，操作步骤如下：（1）单击某个柱形，单击鼠标右键，在弹出菜单中，选择“数据系列格式”选项，弹出“数据系列格式”对话框，如图9-5所示。注意：图9-5 数据系列格式对话框
9、一 Excel在统计数据整理和显示中的应用 （2）在对话框中选择“选项”标签，将间距宽度由“150”改成“0”，点击“依数据点分色”，再单击“确定”按钮，则得到直方图如图9-6所示。图9-6 调整后的直方图一 Excel在统计数据整理和显示中的应用二、用Excel绘制统计图 利用Excel绘制统计图比较方便，Excel提供了“图表向导”工具，帮助创建适合于数据信息和分析目的的各种图表。Excel提供的统计图有多种，包括柱形图、条形图、折线图、饼图、散点图、面积图、环形图等，各种图形的绘制方法大同小异。图表操作的基本步骤如下：1使用“图表向导”工具制作图表之前，选定数据所在的单元格，如果希望数据
10、的行列标题显示在图表中，则选定区域还应包括含有标题的单元格。一 Excel在统计数据整理和显示中的应用 2单击“插入”菜单中的“图表”选项，按照“图表向导”所列示的4个步骤的指令进行操作。“图表类型”：每种类型的图表都有两种以上的子类型，选中所需要的图表类形以及它的子图形，单击“下一步”按钮。“图表数据源”：选定包含数据和行列标志的工作表单元格，即便工作表包含多个行列标志，也能在图表中显示它们。生成图表时，应将各行列标志包含到选定区域中。若预览图表看上去很合适，则表示所选数据区正确。单击“下一步”按钮。“图表选项”：为选定的图表设置某些标准选项。修改这些设置时，随时查看预览图表可以帮助我们确定
11、设置是否合适。单击“下一步”按钮。“图表位置”：可以将图表放置在工作表上，或者单独置于一张新的图表工作表上。在这一步中，我们可以为图表工作表命名，或者选择现有工作表的名称，单击“完成”按钮。一 Excel在统计数据整理和显示中的应用根据图9-7广告类型和从业人数的数据，制作拼图。【例9-2】图9-7 广告从业数据一 Excel在统计数据整理和显示中的应用 操作步骤：1选中某一单元格，单击“插入”菜单，选择“图表”选项，弹出“图表向导”对话框。在图表类型中选择饼图，然后在子图表类型中选择一种类型，这里选用系统默认的方式。如图9-8所示。图9-8 插入图表对话框一 Excel在统计数据整理和显示中
12、的应用 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种股票
13、价格当日的算术平均值。代码证券名价格（元）代码证券名价格（元）600000浦发银行7.24600011华能国际6.97600004白云机场8.54600015华夏银行4.05600006东风汽车2.94600016民生银行5.69600008首创股份8.20600018上港集箱16.37600009上海机场16.65600019宝钢股份6.17表9-2 2005年3月20日10种股票收盘价二 Excel在描述统计量中的应用 采用AVERAGE函数求算术平均值。除采用公式外，Excel还给出了AVERAGE函数来求算术平均值，具体操作步骤如下：1建一工作表，输入表头“股票价格算术平均值”和表9-
14、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.4
15、82004092591.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函数的
16、函数参考对话框，单击Num-berl后的折叠按钮，选择D4：D15对应的单元格，完成后单击确定按钮。二 Excel在描述统计量中的应用 3单击D16单元格，单击编辑栏，在编辑GEOMEAN函数后输入“1”。从图9-14可以看出，该段时间上证180指数的平均收益率为-1.491%。图9-14 使用GEOMEAN函数求几何平均值二 Excel在描述统计量中的应用（三）众数 【例9-5】表9-4中给出了某公司员工工资的频数分布，试确定员工工资的众数。表9-4 某公司员工工资频数分布工资（元）员工数（人）工资（元）员工数（人）20002500835004000142500300010400045001
17、03000350016450050003二 Excel在描述统计量中的应用 利用公式求员工工资的众数，具本操作步骤如下：1新建工作表，输入表头“组数据的众数”，输入表9-4中已知信息。2确定包含众数组的下组限，对应频数最大组为“30003500”即是众数组，对应下组限为3000。3确定众数所在组的频数减去前一组频数，单击B9单元格，在编辑栏输入“=B5B4”，按回车键；确定众数所在组的频数减去后一组的频数，单击B10单元格，在编辑栏输入“=B5B6”，按回车键。二 Excel在描述统计量中的应用图9-15 某公司员工工资的众数 4根据公式求出众数。单击B11单元格，在编辑输入“=3000+（B
18、9/（B9+B10）*500”，完成后按回车键，最终结果如图9-15所示。从图9-15中可以看出，该公司员工工资的众数为3 375元。二 Excel在描述统计量中的应用（四）中位数表9-5 上证180指数数据 【例9-6】表9-5中给出上证180指数某时间段的观测值数目和累积频数，试确定上证180指数此时间段内的中位数。组号上证180观测值数目累积频数组号上证180观测值数目累积频数12400116280029009252240025003472900300032832500260059830003100331426002700312931003200334527002800416103200
19、3300236二 Excel在描述统计量中的应用 具体操作步骤如下：1新建工作表，输入表头“组数据的中位数”，输入表9-5中已知信息。2确定中位数的观测值位置，为（观测值总数+1）/2。单击D12单元格，在编辑栏输入“=（C11+1）/2”。3按插值法确定指数的中位数，单击D13单元格，在编辑栏输入“=2800+（D12C6）/D7*100”，按回车键。二 Excel在描述统计量中的应用图9-16 组数据的中位数 最终结果如图9-16所示。从图9-16中可以看出，该段时间内上证180指数的中位数为2 827.778。二 Excel在描述统计量中的应用（五）调和平均数【例9-7】求5，8，12，
20、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在描述统计量中的应用二、标志变异指标（一）方差 如果选用算术平均值作为度量集中趋势的方法，则方差和标
21、准差就是合适的离中趋势度量指标，方差和标准差被广泛地应用于金融领域中作为风险和不确定性的度量。标志变异指标较常用的方法有：方差和标准差。二 Excel在描述统计量中的应用表9-6 浦发银行股价 【例9-8】表9-6中有浦发银行（600000）10天的股票价格数据，试确定股价样本的方差。日期价格（元）日期价格（元）200503077.65200503147.37200503087.78200503157.16200503097.57200503167.16200503107.45200503177.13200503117.41200503187.19二 Excel在描述统计量中的应用 除了采用定
22、义来求样本的方差外，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资料确定股价样本的标准差。日期
23、价格（元）日期价格（元）200503077.65200503147.37200503087.78200503157.16200503097.57200503167.16200503107.45200503177.13200503117.41200503187.19二 Excel在描述统计量中的应用图9-19 未分组数据的标准差 采用STDEV函数求样本的标准差，具体操作步骤如下：1建工作表，输入表9-6中日期和价格。2求样本的标准差，单击B13单元格，在编辑栏输入“=STDEV（B3：B12）”，按回车键。从图9-19中可以看出，浦发银行股价的标准差为0.2284。第三节Excel在时间序列分
24、析中的应用3三Excel在时间序列分析中的应用一、测定增长量和平均增长量图9-20 我国19982007年国内生产总值 【例9-10】根据我国19982007年国内生产总值，计算逐期增长量、累计增长量和平均增长量。原始数据如图9-20。三Excel在时间序列分析中的应用图9-21 计算逐期增长量、累计增长量和平均增长量的结果 计算步骤如下。1计算逐期增长量。在C3中输入公式：=B3B2，并用鼠标拖曳将公式复制到C3：C11区域。2计算累计增长量。在D3中输入公式：=B3$B$2，并用鼠标拖曳公式复制到D3：D11区域。3计算平均增长量（水平法）。在C13中输入公式：=（B11B2）/9，按回车
25、键，即可得到平均增长量。见图9-21三Excel在时间序列分析中的应用二、测定发展速度和平均发展速度 【例9-11】以【例9-10】中我国19982007年国内生产总值为例，说明如何计算定基发展速度、环比发展速度和平均发展速。1计算定基发展速度。在C3中输入公式：=B3/$B$2，并用鼠标拖曳将公式复制到C3：C11区域。2计算环比发展速度。在D3中输入公式：=B3/B2，并用鼠标拖曳将公式复制到D3：D11区域。3计算平均发展速度（水平法）。选中D13单元格，单击插入菜单，选择函数选项，出现插入函数对话框后，选择GEOMEAN（返回几何平均值）函数，在数值区域中输入D3：D11即可。三Exc
26、el在时间序列分析中的应用图9-22 计算发展速度和平均发展速度结果三Excel在时间序列分析中的应用三、计算长期趋势 【例9-12】我们用我国19882007年国内生产总值资料来说明如何用移动平均法计算长期趋势。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 计算长期趋势资
27、料及结果三 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”，并用鼠标拖曳将公式
28、复制到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。第四节Ex
29、cel在指数分析中的应用4四 Excel在指数分析中的应用 指数分析是利用指标间形成的指标体系，在编制指数的基础上进行的因素分析。从数量上分析研究对象的变动，分别受各因素影响的方向、程度及绝对的数量。例如，在分析工人平均工资变动时，要分析有多大程度是受各工资级别的人数比重变化的影响，有多大程度取决于各工资级别的工资平均变动的影响。利用Excel工具表和各个指标的分解形式，可以分析经济现象的指数体系，以及在此基础上进行因素分析。本节主要讨论利用Excel计算综合指数和平均指数，并进行因素分析。四 Excel在指数分析中的应用一、综合指数的计算表9-7 4种产品的产量和出厂价格【例9-14】某工厂
30、生产的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确
31、认，然后拖动鼠标至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，即
32、该工厂全部工业产品价格综合上涨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，得出
33、工厂基期销售额与个体销售指数的乘积。4分别对F列和H列加总求和。四 Excel在指数分析中的应用图9-29 计算销售量指数 5在H7中输入“=H6/F6，即可得到销售量指数。由表中可以看出与用综合指数法算得的结果相同。具体的操作结果见图9-29。四 Excel在指数分析中的应用三、因素分析（一）总量指标的因素分析表9-8 某工厂2006年生产情况对于总量指标的因素分析，本部分只介绍总量指标变动的两因素分析。【例9-16】某工厂2006年生产情况如表9-8所示。对生产费用的变动进行因素分析。产品名称单位产量单位成品成本/元基期报告期基期报告期甲台160020001200900乙吨60604500
34、4000丙件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分别
35、对F列、G列、H列加总求和。4在F6中输入“=H5/F5”，得出生产支出总额指数为94.3%，F7中输入“=H5F5”，得出结果为25万元。说明该工厂生产支出总额报告期比基期下降了5.7%，绝对额减少了25万元。在G6中输入“=H5/G5”，得出产品成本指数为83.13%，在G7中输入“=H5G5”，得出结果为84万元。说明该工厂单位产品成本水平下降了16.87%。从而节约了生产费用84万元。在H6中输入“=G5/F5”，得出产品产量指数为113.4%，在H7中输入“=G5F5”，得到结果为59万元，说明该工厂由于产品产量增加了13.4%，使得支出总额增加了59万元。具体结果如图9-32所示。
36、四 Excel在指数分析中的应用图9-32 计算生产支出总额指数、产品成本指数、产品产量指数四 Excel在指数分析中的应用（二）平均指标变动的因素分析表9-9 某工厂职工的工资资料对于平均指标变动的因素分析，举例加以说明。【例9-17】某工厂职工的工资资料如表9-9所示。分析职工工资水平和工人结构变动对平均工资的影响。月工资/元工人数/人基期报告期基期报告期技术工人580620245250辅助工人400420120800四 Excel在指数分析中的应用图9-33 输入基期、报告期的月工资和工人数1在工作中表输入数据，输入结果如图9-33所示。四 Excel在指数分析中的应用图9-34 计算基
37、期、报告期月工资和工人数的乘职 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中输
38、入“=（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中一个总体均值、总体方
39、差和总体比例的抽样估计可以用使用公式和函数或数据分析工具进行，两个总体均值之差也可按类似方法进行，下面举例说明。【例9-18】某饭店在7周内抽查49位顾客的消费额（元）如表9-10所示，估计概率保证程度为90%时顾客平均消费额的区间。表9-10 49位顾客的消费额数据15243826304218302526344420352426344818284619303642243245362147262831424536242827323647532224324626五Excel在抽样估计中的应用图9-36 顾客的消费额数据1把数据输入到A2：A50单元格，如图9-36所示五Excel在抽样估计中的应
40、用图9-37 顾客的消费额区间估计结果 2在C2中输入公式“=COUNT（A2：A50）”，C3中输入“=AVERAGE（A2：A50）”，在C4中输入“STDEV（A2：A50）”，在C5中输入“=C4/SQRT（C2）”，在C6中输入0.90，在C7中输入“=C21”，在C8中输入，“=TINY（1-C6，C7）”，在C9中输入“=C8*C5”，在C10中输入“=C3C9”，在C11中输入“=C3+C9”。在输入每一个公式按回车键后，便可得到上面的结果。从上面的结果可以知道，顾客平均消费额的置信下限为29.73536，置信上限为34.26464，如图9-37所示。五Excel在抽样估计中的
41、应用二、数据分析工具图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（置信区间下限），键入单元
42、格公式“=C3C18”，选择单元格D3（置信区间上限），键入单元格公式“=C3+C18”，C3是样本均值，C18是误差范围（极限误差）。结果如图9-40所示。第六节Excel在相关与回归分析中的应用6六 Excel在相关与回归分析中的应用一、相关图的绘制表9-11 8个同类企业产品产量和生产费用的相关表【例9-19】根据表9-11相关数据，绘制相关图。企业编号月产品产量（千吨）月生产费用（万元）12.412424.117036.016247.5200510.0225612.4265714.1280816.2310六 Excel在相关与回归分析中的应用图9-41 8个同类企业产品产量和生产费用数
43、据 制作相关图的步骤如下：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、44所示。六 Excel在相关与回归分析中的应用图9-45月产品产量与生产费用的趋势线图 5选择选项中“”显示公式和“”显示R平方值，点击确定，最后得到如图9-45所示的趋势线图、回归方程和R平方值。六 Excel在相关与回归分析中的应用二、相关系数 在Excel中，相关系数函数和相关系数宏提供了两种计算相关系数的方法。1.相关系数函数。在Excel中，CORBEL函数和PERSON函数提供了计算两个变量之间的相关系数的方法，这两个函数是等价的。与相关系数有关的函数还有RSQ（相关系数的平方，即判定系数）和COVAR（协方差函数）。六 Excel在相关与回归分析中的应用【例9-20】以表9-1
45、1中资料为例，利用函数CORBEL函数计算相关系数。表9-11 8个同类企业产品产量和生产费用的相关表企业编号月产品产量（千吨）月生产费用（万元）12.412424.117036.016247.5200510.0225612.4265714.1280816.2310六 Excel在相关与回归分析中的应用图9-46 CORBEL函数计算相关系数 1点击Excel函数图钮“”，选择“统计”函数。2在统计函数点击“CORBEL”，进入函数向导。3在“Array1”中输入第一个变量“月产品产量”的数据区域A2：A9。在“Array2”中输入第二个变量“生产费用”的数据区域B2：B9字样，即可在当前光标
46、所在单元格显示函数的计算结果，如图9-46所示。六 Excel在相关与回归分析中的应用 2.相关系数宏。在Excel数据分析宏中，Excel专门提供了计算相关系数宏的过程。利用此宏过程，可以计算多个变量之间的相关矩阵。【例9-21】以表9-11中资料为例，利用相关系数宏计算相关系数矩阵。表9-11 8个同类企业产品产量和生产费用的相关表企业编号月产品产量（千吨）月生产费用（万元）12.412424.117036.016247.5200510.0225612.4265714.1280816.2310六 Excel在相关与回归分析中的应用图9-47 相关系数宏 1点击Excel“工具”菜单，选择“
47、数据分析”过程。2在“数据分析”宏过程中，选择“相关系数”过程，如图9-47所示。六 Excel在相关与回归分析中的应用图9-48 利用相关系数宏计算的相关系数矩阵 3在“输入区域”中输入两个变量所在区域A2：B9，数据以列排列。当光标在“输出区域”闪动时，用鼠标点击D1位置，计算结果如图9-48所示。六 Excel在相关与回归分析中的应用三、回归分析宏 除了回归分析宏外，Excel还提供了9个函数用于建立回归模型和回归预测。这9个函数列于表9-12中。但Excel提供的回归分析宏仍然具有更方便的特点。【例9-22】以表9-11中资料为例，利用一元线性回归方程确定两个变量之间的定量关系。表9-
48、12用于回归分析的工作表函数函数名定 义INTERCEPT一元线性回归模型截距的估计值SLOPE一元线性回归模型斜率的估计值RSQ一元线性回归模型的判定系数（）FORECAST依照一元线性回归模型的预测值STEYX依照一元线性回归模型的预测值的标准误差TREND依照多元线性回归模型的预测值GROWTH依照多元指数回归模型的预测值LINEST估计多元线性回归模型的未知参数LOGEST估计多元指数回归模型的未知参数六 Excel在相关与回归分析中的应用图9-49 回归分析宏过程 回归宏确定两个变量之间定量关系的过程如下：1在“工具栏”菜单“数据分析”过程中选择“回归”宏过程。2在“Y值输入区域”内
49、输入B2：B9，在“X值输入区域”输入A2：A9，如果是多元线性回归，则X值的输入区就是除Y变量以外的全部解释变量。3选择置信度水平为95%，当光标在“输出区域”闪动时，用鼠标点击D1位置，结果见图9-49。六 Excel在相关与回归分析中的应用图9-50 回归分析结果 4选择“残差分析”并绘制回归拟合图，点击“确定”即得到图9-50所示的回归分析结果和图9-51的残差分析表。六 Excel在相关与回归分析中的应用图9-51 残差分析表v1某生产企业60名工人日加工零件数资料如下：思考与练习654314322434546635331333253433345464322433253534334333222434要求：（1）根据以上资料分成如下几组：12，23，34，45，56，计算出各组的频数（次数）和频率。（2）利用Excel工具计算工人生产该零件的平均日产量。v2某商店两种商品的销售量资料如下：思考与练习要求：利用Excel工具分析销售量及价格变动对销售额的影响。产 品销售量/万斤单位成本/（元斤）2012年2013年2012年2013年A商品3003600.420.45B商品2002000.300.36谢 谢 观 赏教师教师
