统计中Excel的应用

《统计中Excel的应用》由会员分享，可在线阅读，更多相关《统计中Excel的应用（57页珍藏版）》请在装配图网上搜索。

1、9 统计中Excel的应用 本章主要讲解了Excel的特点及其在统计分析中的各种应用。通过本章的学习，读者应重点掌握以下内容:Excel特点及功能；利用Excel对原始数据进行分组整理，制作统计表和统计图；利用Excel计算各种静态指标和动态指标；利用Excel计算两类总指数、对指数体系进行因素分析；利用Excel作随机抽样和区间估计；利用Excel计算相关系数、作一元回归分析。教学目标关键词汇统计分析（Statistical Analysis）统计推理(Statistical Inference)回归分析(Regression Analysis)9 统计中Excel的应用9.1 Excel的

2、特点9.2 统计整理中Excel的应用 9.3 静态指标分析中Excel的应用9.4 动态指标分析中Excel的应用9.5 统计指数中Excel的应用9.6 抽样推断中Excel的应用9.7 相关分析和回归分析中Excel的应用 知识归纳 习题与思考题9.1 Excel的特点9.1.1 Excel程序的统计功能 1Excel的函数统计功能 函数是Excel预定义的内置公式，它可以接受被称为参数的特定值，按函数的内置语法进行特定的运算，返回一定的函数运算结果。Excel提供了丰富的函数，其中统计函数就多达80种。在Excel中，要使用函数，通常有两种方法:一种是点击“插入”菜单，选择“函数”命令

3、，然后在“插入函数”对话框中选择具体函数，即可打开函数参数设置对话框进行参数设置；另一种是直接在参数单元格内输入函数及参数。2Excel的数据管理功能 利用Excel可以把工作表中的数据做成一个类似数据库的数据清单，可以实现记录的增加、修改、删除、查找与编辑，具有排序、筛选、分类汇总功能。数据清单中的列是数据库的字段，列标志是字段名称，数据清单中的每一行对应数据库中的一个记录。3Excel绘图功能 Excel具有强大的绘制统计图表功能，可以绘制柱状图、条形图、折线图、饼图、散点图、面积图、圆形图、雷达图、曲面图、气泡图、股价图、圆锥图、棱锥图等，而且每一种类型又提供了几种不同的子类型，利用图表

4、向导就可轻松完成。4分析工具库 “分析工具库”包括下述工具:方差分析、相关系数、协方差、描述统计、指数平滑、双样本F检验分析、傅利叶分析、直方图、移动平均、随机数发生器、排位与百分比排位、回归分析、抽样分析、t检验、z检验。若要使用这些工具，单击“工具”菜单中的“数据分析”命令，在“数据分析”对话框中选择相应分析工具即可。如果“工具”菜单中没有显示“数据分析”命令，则需要装载“分析工具库”。单击“工具”菜单中“加载宏”，打开加载宏对话框，复选框“分析工具库”即可。9.1.2 公式与函数的操作 公式是Excel中的核心，它由单元格内的一系列数值(或者能够代表数值的单元格、单元格区域名称)、运算符

5、和工作表函数组成。利用公式，用户不仅可以进行数学运算(如加、减、乘、除等)和逻辑判断(如真与假)，还可以完成工作表数据比较及文本合并等。函数是Excel中内置的公式，函数使各种运算变得更加简便。如能熟练掌握函数，并能加以利用，可大大提高工作效率。与公式相比，函数具有特定的语法结构，必须严格遵守，否则Excel会弹出错误提示信息。函数以函数名称(如SUM)开始，紧接着是左圆括号、参数1、参数 2、右圆括号，当有多个参数时，参数间以逗号分隔。使用函数时，函数名称前的符号“=”和其后的括号“()”是必不可少的，因此“=”和“()”被称为函数的结构。所谓参数是指函数中用来执行操作或计算的值。一个函数可

6、以没有参数，也可有一个或多个参数。常用的参数类型有:数值、文本、形如TRUE或FALSE的逻辑值、数组、引用单元格、错误值#NA等。常量、公式或其他函数也可在函数中作为参数。如在IF函数“=IF(AVERAGE(B3:G3)50,SUM(B4:G4)，0)”中，有AVERAGE函数、SUM函数作为其参数。1公式的创建与编辑 (1)公式的创建 为了建立公式，必须首先键入“=”号，以表示编辑公式即将开始。在一个完整的公式中，Excel将从左到右采用运算符，并按照运算符的优先顺序进行计算。如果要更改公式的运算顺序，需要使用圆括号。一般可使用下列操作步骤建立公式：选择要建立公式的单元格。键入“=”。一

7、般先将鼠标指针指向编辑栏，并单击，然后输入等号“=”；如果要利用Excel函数建立公式，则可在编辑栏左侧单击插入函数按钮。输入公式的内容，比如“=SUM(B4:G4)”。为了完成公式的编辑，确认输入的公式，可按Enter键或用鼠标单击编辑栏左侧的“”按钮；如果要取消编辑的公式，则可单击编辑栏的“”按钮。（2）公式中的运算符 顾名思义，运算符就是用来进行运算的符号，在Excel中有4种类型的运算符，即算术运算符、比较运算符、文本运算符和引用运算符。算术运算符 算术运算符可以完成基本的数学运算，如加、减、乘、除、乘方(如乘幂的2次方)、开方(如乘幂的12=0.5次方)等。比较运算符 比较运算符可以

8、用于比较两个值，并产生逻辑值True或False。文本运算符 在Excel中，文本运算符只有一种，即“”，该运算符可将文本或数值连接成串。引用运算符 除了以上运算符外，当Excel公式引用一个单元格区域或多个单元格区域时，还需要采用引用符号。常用的引用运算符有个“:”，“，”，“”(空格)，分别表示引用范围、合并引用、交叉引用。当引用一个单元格区域时，需要知道区域的左上角位置和右下角位置，如左上角位置为第2列第5行(B5)，右下角位置为第4列第8行(D8)，则该区域可表示为“B5:D8”。如果要计算三个单元格区域“B5:D8”，“A1:A6”和“H1:H5”之间的和，则可在编辑栏输入“=SUM

9、(B5:D8,A1:A6,H1:H5)”来完成。运算符的优先顺序 如果公式中包含了相同优先级的运算符(如+、-)，则计算从左到右进行。当公式中包括有多个运算符时，必须考虑优先级别的问题。只有弄清了运算符的优先级别，哪些运算符的优先级别相同，哪些优先级别排在前面，哪些排在后面，才不至于造成运算的错误。(3)公式编辑 编辑公式时，单元格绝对引用和混合引用需要输入“”号，绝对引用要分别在列标和行号之前输入“”号，混合引用要在列标或行号之前输入“”号。输入“”号时，需要将输入方式改变为英文，且需按Shift键输入“”号。为了操作更简便，可采用F4功能键解决此问题。如需对单元格A1加“”号，可将光标放在

10、A1的旁边，第一次按F4功能键A1会变为A1，第二次按F4功能键会变为A1，第三次按F4功能键会变为A1，第四次按F4功能键又会返回到相对引用状态，即A1。在默认状态下，在输入公式的单元格中显示的只是结果，而不是公式。若想在单元格中显示公式，可以使用快捷键“Ctrl+”进行公式内容与结果的切换。2Excel常用函数 Excel为用户提供了11类(数学和三角函数、统计函数、日期与时间函数、文本函数、逻辑函数、查询和引用函数、数据库函数、信息函数、工程函数、财务函数、用户定义函数)，约400个函数。数据分析涉及的数学函数、统计函数、日期与时间函数、文本函数、逻辑函数等较多，应用时可参见本书的有关章

11、节。返回9.2 统计整理中Excel的应用Excel提供了多种数据整理工具，主要有:数据排序和筛选；频数分布函数；数据透视表;统计图；直方图分析工具。9.2.1 数据录入 1 数据的手动输入 建立一个新的Excel文件之后，便可进行数据的输入操作。Excel中以单元格为单位进行数据的输入操作。一般用上下左右光标键，Tab键或鼠标选中某一单元格，然后输入数据。Excel中的数据按类型不同通常可分为四类:数值型、字符型、日期型和逻辑型。Excel根据输入数据的格式自动判断数据属于什么类型。如日期型的数据输入格式为“年月日”、“年月日”或“时：分：秒”。要输入逻辑型的数据，输入“True”(真)或“

12、False”(假)即可。若数据由数字与小数点构成，Excel自动将其识别为数字型，Excel允许在数值型数据前加入货币符号，Excel将其视为货币数值型，Excel也允许数值型数据用科学计数法表示，如2109在Excel中可表示为2E+9。除了以上三种格式以外的数据，Excel将其视为字符型处理。2公式生成数据 Excel的数据中也可由公式直接生成。例如，在当前工作表中A1和B1单元格中已输入了数值数据，欲将A1与B1单元格的数据相加的结果放入C1单元格中，可按如下步骤操作:用鼠标选定C1单元格，然后输入公式“=A1+B1”或输入“=SUM(a1:b1)”，回车之后即可完成操作。C1单元格此时

13、存放实际上是一个数学公式“A1+B1”，因此C1单元格的数值将随着A1、B1单元格的数值的改变而变化。Excel提供了完整的算术运算符，如+(加)、-(减)、(乘)、(除)、(百分比)、(指数)和丰富的函数，如SUM(求和)、CORREL(求相关系数)、STDEV(求标准差)等，供用户对数据执行各种形式的计算操作。在Excel帮助文件中可以查到各类算术运算符和函数的完整使用说明。3复制生成数据 Excel中的数据也可由复制生成。实际上，在生成的数据具有相同的规律性的时候，大部分的数据可以由复制生成。可以在不同单元格之间复制数据，也可以在不同工作表或不同工作簿之间复制数据，可以一次复制一个数据，

14、也可同时复制一批数据，为数据输入带来了极大的方便。普通单元格的复制结果与公式单元格的复制结果相差较大，下面分别予以说明。（1）普通单元格指的是非公式的单元格。拖动鼠标选定待复制的区域，选定之后该区域变为黑色。Excel可以进行整行、整列或整个表格的选定操作。例如，如果要选定表格的第一列，可直接用鼠标单击列标“A”，如果要选定表格的第一行，可直接用鼠标单击行标“1”，如果要选定整个表格，可直接点击最左上角的全选按钮。选定区域之后，用鼠标右击该区域，在弹出的菜单中选择“复制”命令，将区域内容复制到粘贴板之中。可以发现该区域已被虚线包围。用鼠标右击目标区域，在弹出的菜单中选择“粘贴”命令，则单元格区

15、域的复制即告完成。（2）公式单元格的复制一般可分为两种，一种是值复制，一种是公式复制。值复制指的是只复制公式的计算结果到目标区域，公式复制指的是仅复制公式本身到目标区域。下面对它们的操作步骤分别予以说明。值复制:a拖动鼠标选定待复制区域。b用鼠标右击选定区域，选择“复制”选项。c用鼠标右击目标区域，再单击“选择性粘贴”子菜单。出现复制选项，选定“数值”选项，然后单击“确定”按钮，则值复制即告完成。公式复制:公式复制是Excel数据成批计算的重要操作方法。要熟练公式复制的操作首先要区分好两个概念:单元格的相对引用与绝对引用。4数据自动填充 数据的自动填充可以采用“填充柄”、“Ctrl+Enter

16、”组合键或“填充”命令来完成。利用“填充”命令进行自动填充时，首先要在一个单元格中输入第一个数据，然后选定一个单元格区域，使输入的第一个数据位于单元格区域的顶行、底行、最左边或最右边，单击“编辑”菜单中的“填充”命令，在出现的级连菜单中根据需要选择“向下填充”、“向上填充”、“向左填充”或“向右填充”，可以完成行或列的填充。以上操作也可以通过选取“填充序列”命令，在“序列”对话框中选择“自动填充”来完成。通过“序列”对话框，还可以完成等差数列、等比数列的填充。5利用“记录单”编辑数据 比较简单的数据清单，数据的编辑可以直接在数据清单中操作。如果数据清单中的数据非常复杂，就可以利用“记录单”数据

17、的操作。数据“记录单”具有浏览记录、添加记录、修改记录和删除记录等作用。使用“记录单”命令，可以在数据清单中一次输入、显示、查找或删除一行完整记录。选中数据清单中的任意单元格，在“数据”菜单中选择“记录单”命令，则弹出“数据清单”对话框。在“数据清单”对话框中，可以完成以下操作:（1）浏览记录 （2）浏览符合条件的记录 （3）添加记录 （4）修改记录 （5）删除记录9.2.2 统计分组 1统计数据的排序与筛选 （1）统计数据排序数据的排序是以数据清单中的一个或几个字段为关键字，对整个数据清单的所有个体进行重新排列。排序可以按升序，也可以按降序。对于数字型字段，排序是按数值的大小；对于字符型字段

18、，排序是按ASCII码大小；中文字段按拼音或笔画排序。通过排序，可以清楚地反映数据之间的大小关系。按单字段排序 按多字段排序【例9.1】6个企业某年的主要财务指标如图9.1所示。图9.1 某年度的企业财务数据 a以“主营业务收入”为关键字，按升序排列以上数据。选中“主营业务收入”字段下任一单元格，再单击工具栏上的“升序排序”按钮。b以“主营业务收入”为主要关键字，“主要业务利润”为次要关键字，“净利润”为第三关键字，对数据全部进行降序排序。单击“数据”菜单中的“排序”命令，弹出“排序”对话框。单击“主要关键字”下拉列表框右面的下拉按钮，在下拉列表中选择“主营业务收入”，然后在右边的单选按钮中选

19、中“降序”。照此方法，依次设置“次要关键字”和“第三关键字”。如果需要进一步设置，可单击“排序”对话框中的左下角的“选项”按钮，在弹出“排序选项”对话框中进行详细设置。在“排序”对话框中，单击“确定”按钮，完成数据排序。(2)统计数据的筛选 利用Excel提供的筛选功能，可以把符合要求的数据集中在一起，把不符合要求的数据隐藏起来。使用自动筛选的步骤如下:自动筛选 自动筛选是一种快速的筛选方法，它可以方便地将满足条件的数据显示在工作表上，将不满足条件的数据隐藏起来。使用自动筛选的步骤如下:在数据清单中选择任一单元格为当前单元格。选中“数据”菜单中的“筛选自动筛选”命令，会看到在数据清单中的每一列

20、字段名旁都会出现一个下拉箭头按钮。单击某一个下拉按钮，在列表框中选定筛选的条件。筛选条件主要有:前10个、自定义和具体的数值等。“前10个”选项只对数字型变量有效，筛选的数据个数不一定只是10个，可以根据需要设定需要筛选的数据个数。方法是单击“前10个”选项，弹出“自动筛选前10个”对话框。在对话框中可以设定筛选的条件为最大或最小，以及筛选值的项数(或百分比)。完成自动筛选后，再次单击数据菜单中的“筛选自动筛选”命令将退出自动筛选状态，字段名旁的下拉按钮同时消失。高级筛选 自动筛选只能适用于比较简单的条件，如果需要指定的筛选条件比较多，就需要使用Excel的高级筛选功能。高级筛选的关键是条件区

21、域的设定。通常是将条件区域放在整个数据清单的下边(以防止被筛选隐含，并且不改动数据清单的位置)，至少要用一个空行隔开。条件区域的第1行为字段名，第2行及以下各行为条件值。同一行条件之间为“与”的关系，不同行条件之间为“或”的关系，可采用的条件符号有、。设置好条件区域后，选定数据清单中的任一单元格，在“数据”菜单中选择“筛选高级筛选”命令，在弹出的“高级筛选”对话框中，根据需要选择显示筛选结果的方式，并分别指定数据清单(列表区域)和条件区域所在单元格的位置，单击“确定”按钮即完成高级筛选。2统计数据的分组（类）汇总 (1)分类汇总 为了使数据清单的内容更加清晰明确，可以利用Excel的分类汇总功

22、能将数据归组（类），并进行求和、均值等计算，并将计算结果显示出来，以便对数据进行进一步分析。(2)利用频数分布函数(FREQUENCY)分组 单纯利用排序与分类汇总还不能很好地描述数据的分布状态，为此，Excel提供了一个频数分布函数(FREQUENCY)，利用它可以对数据进行分组，建立频数分布，从而更好地描述数据分布状态。该函数以一列垂直数组返回某个区域中数据的频率分布。例如，使用函数FREQUENCY可以计算在给定的分数范围内测验分数的个数。语法:FREQUENCY(data_array,bins_array)其中:data_array为一数组或对一组数值的引用，用来计算频率。如果data

23、_array中不包含任何数值，函数FREQUENCY返回零数组。bins_array为间隔的数组或对间隔的引用，该间隔用于对data_array中的数值进行分组。如果 bins_array中不包含任何数值，函数FREQUENCY返回data_array中元素的个数。在选定相邻单元格区域(该区域用于显示返回的分布结果)后，函数FREQUENCY应以数组公式的形式输入。返回的数组中的元素个数比bins_array(数组)中的元素个数多1。返回的数组中所多出来的元素表示超出最高间隔的数值个数。例如，如果要计算输入到三个单元格中的三个数值区间(间隔)，一定要在四个单元格中输入FREQUENCY函数计算

24、的结果。多出来的单元格将返回data_array中大于第三个间隔值的数值个数。9.2.3 制作统计图 1统计分析图表制作 Excel为绘制统计图表提供了一整套便利的制作技术，可以从工作表数据中创建既复杂又准确、漂亮的图表。【例9.5】下面以饼图为例，说明创建图表一般要经过的几个步骤:（1）确定制图的目的，根据制图的目的搜集和审核统计资料。（2）将统计资料输入到Excel中去。例如图9.4所示教师职称的原始统计资料。图9.4 教师职称的原始统计资料 （3）计算职工总数。选中单元格B6，在编辑栏内输入公式“=SUM（B2:B5）”，按回车键。（4）计算教师比重。选中单元格C2，并把单元格的数字格式

25、设成“百分比”，在编辑栏内输入公式“=B2/B5”，按回车键。照此方法依次计算单元格C3、C4、C5中的数据。选中单元格C6，在编辑栏内输入公式“=SUM（C2:C5）”，按回车键，即得教师职称计算结果。（5）在想绘制图的数据中任选一个单元格，然后在“插入”菜单中选中“图表”命令，或单击工具栏上的“图表向导”按钮，弹出“图表向导4步骤之1”对话框。在“图表向导4步骤之1”对话框中包括“取消”、“下一步”、“完成”和“按下不放可查看示例”按钮。单击“取消”即停止创建图表，单击“按下不放可查看示例”可以查看所选图表的效果，完成指定图表属性后即可单击“完成”，图表就立刻被创建出来。（6）决定图形。在

26、“图表向导4步骤之1图表类型”对话框中选中所需图表类型，在子图表类型中再选中一种子类型，比如饼图三维饼图，单击“下一步”按钮。（7）确定要绘制图表的数据范围。此步可规定图表使用的数据区域以及每一数据系列名字和数值的区域。进入“图表向导4步骤之2图表源数据”对话框，单击“数据区域”后输入框右侧的“拾取”按钮，从工作表中选择单元格区域A1:A5,C1:C5,在“系列产生在”后的单选框中选“列”。(8)给图表命名。单击“下一步”按钮进入“图表向导4步骤之3图表选项”对话框，在“标题”选项卡的“图表标题”下的文本框中输入“教师职称结构”。单击“数据标志”选项卡，在“数据标签包括”下的复选框中选择“百分

27、比”和“类别名称”，如图9.5所示。图9.5 图表向导4步骤之3图表选项 （9）图表位置的选中。单击“下一步”按钮，弹出“图表向导4步骤之4图表位置”对话框。有两个选项：“新工作表”意味着将图表作为一个对象放在一个独立的工作表中；“嵌入工作表”是将图表作为一个对象放在工作表中。选择默认选项，单击“完成”按钮，即可完成图表制作。（10）鼠标指向图中的标识双击，都可以对图形中的内容进行修饰。如鼠标指向“教师职称结构”双击，弹出“图表标题格式”对话框。在“字体”选项卡中设置字体为“隶书、常规、14号”，即可修改图形。2直方图制作 【例9.6】以例9.3资料为例，说明如何用直方图分析工具对学生成绩进行

28、分析。(1)打开工作表，将成绩按升序排序。(2)在单元格D1输入文字“分组”，在单元格区域D2:D6分别输入59、69、79、89、99，表示分组区间的间隔点。(3)选择“工具”菜单下的“数据分析”选项，在“数据分析”对话框中选择“直方图”，单击“确定”按钮。(4)在“直方图”对话框的“输入区域”后输入单元格区域B1:B11；在“接收区域”后输入D1:D6，选中“标志”复选框；在“输出区域”后输入E1，选中“图表输出”和“累积百分率”复选框，如图9.6 所示。图9.6“直方图”对话框选项设置 (5)单击“确定”按钮，Excel会同时生成一个频率分布表和一个图表。注意:直方图显示等于或大于区间值

29、并小于下一个区间值的输入值的数量，并将其显示于“频率”列中。表中的最后一个值等于或大于最后一个区间值的输入值的数量。返回9.3 静态指标分析中Excel的应用9.3.1 用函数方法计算均值和变异指标 统计数据的描述均可以利用Excel中提供的有关统计函数和公式或描述统计分析工具来完成。下面就描述统计中有关静态平均指标、标志变异指标如何在Excel中进行计算加以说明。1 静态平均指标 静态平均指标有算术平均数、调和平均数、位置平均数(众数、中位数)等。(1)算术平均数 算术平均数分为简单算术平均数和加权算术平均数。简单算术平均数可利用AVERAGE函数计算。例如，某公司10名高级管理人员的工资(

30、单位:元)分别为:2500、2000、3000、2600、2100、2300、2500、3500、4000、3600，则平均工资的计算过程为:首先，将这10名高级管理人员的月工资输入A1A10单元格内，排序不排序均可。然后，单击任一空单元格，输入“=AVERAGE(A1:A10)”，回车确定即可得到月平均工资2830元/人。加权算术平均数，首先列出计算表，然后利用公式计算。（2）调和平均数 简单调和平均数可以用HARMEAN函数计算。例如，买同一种菜，分三次买，每次价格不同，分别为0.25元千克、0.4元千克、0.50元千克，则可先单击任一单元格，再输入“=HARMEAN(0.25，0.4，0

31、.5)”，确定后，便得到三次购买的平均价格0.38元。加权调和平均数也可列表按照公式计算。例如，有一加权调和平均数计算资料，将金额和价格输入后，如表9.5所示。ABCD1购买次数金额m价格x金额/价格2一10253二371.85204三271.80155合计7440表9.5 金额和价格资料 下面先计算购买量:单击D2单元格，输入公式“=B2C2”后回车，得出第一次购买量5，并利用填充柄功能计算出第二、第三次购买量。然后单击D5单元格，输入“=SUM(D2:D4)”求出购买总量，并利用填充柄功能，计算出总金额，最后单击任一空格单元格，输入“=B5D5”，即得出加权调和平均价格1.85元。（3）位

32、置平均数 位置平均数有众数和中位数，如果掌握的数据是原始资料，将原始数据输入Excel后，可利用统计函数MODE、MEDIAN分别计算。如果掌握的数据是分组资料，可按下限或上限公式计算。2标志变异指标标志变异指标有全距、平均差、标准差和标志变异系数。（1）全距 如果数据排序后，那么最大标志值和最小标志值自然就显示出来，全距就容易求出。如果数据没有排序，可用MAX和MIN函数求得最大标志值和最小标志值来计算全距。（2）平均差 （3）标准差 在Excel中，对于未分组的资料，可直接用统计函数STDEV计算标准差；对于已分组的资料，由于各组权数不同，计算时必须加权计算。其操作过程和上例求平均差类似，

33、在计算E列时，输入“=(C2-920)2”或输入“=(C2-920)*(C2-920)”，即可计算（x-x）2项。（4）标志变异系数 标准差除以算术平均数得到标志变异系数，因此，要计算标志变异系数，可先按照上述方法计算算术平均数和标准差，再计算标志变异系数。9.3.2 用描述统计工具测度均值和变异指标 描述统计分析工具用于生成数据源区域中数据的单变量统计分析报表，它可以同时计算出一组数据的多个常用统计量，提供有关数据集中趋势和离中趋势以及分布形态等方面的信息。【例9.10】调查某企业生产车间10名工人的月工资水平，资料如图9.7所示。用描述统计工具对工人工资数据进行分析。图9.7 工资资料 首

34、先，选择“工具”菜单的“数据分析”选项，在弹出的“数据分析”对话框的分析工具中选择“描述统计”，单击“确定”按钮，弹出“描述统计”对话框。“描述统计”对话框包括以下内容:输入区域:输入待分析数据所在的单元格区域。本例输入B1:B11。分组方式:如果需要指出输入区域中的数据是按行还是按列排列，则单击“逐行”或“逐列”。本例选择“逐列”。标志位于第一行:若输入区域包括列标志行，则必须选中此复选框。否则，不能选中该复选框，此时Excel自动以列1、列2、列3作为数据的列标志。本例选中此复选框。输出选项:下有三个单选按钮为“输出区域”、“新工作表组”和“新工作簿”。如果指定输出到当前工作表的某个单元格

35、区域，这时需在“输出区域”框键入输出单元格区域的左上角单元格地址；如果指定输出到新工作表组，这时需要输入工作表名称；也可以指定输出到新工作簿。本例选中将结果输出到“输出区域”，并输入左上角单元格地址D1。汇总统计：若选中，则显示描述统计结果，否则不显示结果。本例选中。均值置信度:若需要输出包含均值的置信度，则选中此复选框，然后在右侧的编辑框中，输入所要使用的置信度。本例输入95，表明要计算在显著性水平为5时的均值置信度。第K大/小值:如果需要在输出表的某一行中包含每个区域的数据的第K个最大小值，则选中此复选框。然后在右侧的编辑框中，输入K的数值。本例均选，并输入数值1，表示要求输出第1大/小的

36、数值。其次，在“描述统计”对话框中，单击“确定”按钮。返回9.4 动态指标分析中Excel的应用9.4.1 计算增长量和平均增长量 1计算增长量 增长量即增加量，其一般计算公式为:增长量=报告期水平基期水平。在比较发展水平时，按采用基期的固定与否，增长量可以分为逐期增长量和累积增长量两种形式。【例9.11】某单位本年度12个月的销售资料如图9.8所示。用Excel计算逐期增长量，单击单元格C3，输入公式“=B3-B2”，回车确定后即得第2期的逐期增长量。利用填充柄功能，即鼠标指向C3单元格右下角小黑方块，鼠标指针变为黑“十”字时，按下左键，并向下拖拽至C13单元格后松开，得出各期的逐期增长量。

37、用Excel计算累积增长量，单击单元格D3，输入公式“=B3-750”，回车确定后即得第2期的累积增长量。利用填充柄功能，即鼠标指向D3单元格右下角小黑方块，鼠标指针变为黑“十”字时，按下左键，并向下拖拽至D13单元格后松开，得出各期的累积增长量。图9.8 某单位本年度销售资料 2计算平均增长量 平均增长量是一种序时平均数。其计算公式为:平均增长量=第n期发展水平-基期发展水平逐项增长量项数其中，逐项增长量项数等于时期数列项数1。用Excel计算平均增长量，单击单元格E1，输入公式“=（B13-B2）/（12-1）”，回车确定即可得到平均增长量18.18182。9.4.2 计算发展速度和平均发

38、展速度 1计算发展速度 发展速度是相对指标，一般用百分数表示。计算公式为:根据采用基期的不同，发展速度可以分为定基发展速度和环比发展速度。仍以例9.11说明如下：由于基期都是固定的，用Excel计算定基发展速度与计算累积增长量基本相似，只是公式不同。单击单元格C3，输入公式“=（B3/750）*100%”，回车确定后即得第2期的定基发展速度。利用填充柄功能，即鼠标指向C3单元格右下角小黑方块，鼠标指针变为黑“十”字时，按下左键，并向下拖拽至C13单元格后松开，得出各期的定基发展速度。用Excel计算环比发展速度与用Excel计算逐期增长量基本相似，只是公式不同。单击单元格D3，输入公式“=（B

39、3/B2）*100%”，回车确定后即得第2期的环比发展速度。利用填充柄功能，即鼠标指向D3单元格右下角小黑方块，鼠标指针变为黑“十”字时，按下左键，并向下拖曳至D13单元格后松开，得出各期的环比发展速度。2计算平均发展速度 平均发展速度是各期环比发展速度的动态序时平均数。它自能根据需要采用几何平均法和方程法来计算。方程法需要求解比较复杂的高次方程，实际工作中都根据事先编制好的“平均发展速度查对表”来计算。这里只介绍几何平均法。其计算公式一般采用：，其中，n为逐项增长量项数即等于时期数列项数1；an是动态数列第n期发展水平；a0是动态数列初期发展水平。以上例9.11说明如下：用Excel计算平均

40、发展速度，单击单元格E1，输入公式“=（B13-B2）（1/（12-1）”，回车确定即可得到平均发展速度1.02172246。9.4.3 计算长期发展趋势 长期发展趋势的测定方法主要有时距扩大法、移动平均法和最小平方法。对于时距扩大法，可通过SUM函数(求和函数)方便求得，这里就不再举例说明。1移动平均法 在Excel中，移动平均法可使用移动平均工具进行。【例9.12】以图9.9资料为例，进行四项、五项平均。原始资料输入Excel后，如图9.9所示A、B两列。图9.9 移动平均原始数据及输出结果 首先，进行四项移动平均。单击“工具”菜单中“数据分析”命令，选择“移动平均”工具，打开移动平均设置

41、对话框。其次，在“输入区域”内输入数据所在区域“B2:B17”；“间隔”栏内输入“4”(再进行移动平均时输入“2”)；“输出区域”栏内输入放置结果区域“C2:C17”，确定即可。五项移动平均与此类似。2最小平方法 利用最小平方法可以对直线趋势进行测定，也可以对曲线趋势进行测定。【例9.13】以上例9.12资料为例，说明在Excel中如何运用最小平方法来建立直线趋势方程。原始资料输入Excel后，如图9.10所示。图9.10 最小平方法的原始数据与结果 计算C列。单击C2，输入“=A2*A2”，并用填充柄功能计算C3C17；再计算D列，单击D2，输入“=A2*B2”，并利用填充柄功能计算D3D1

42、7。然后计算合计，单击A18，输入“=SUM(A2:A17)”得136，再次利用填充柄功能，计算B、C、D各列的合计数(即18行的值)。下面计算参数a、b:先计算b，单击任一单元格，输入“=(D18-A18*B1816)(C18-A18*A1816)”确定后即得b的值0.2014706。再计算a，单击任一单元格，输入“=B1816-0.2014706*A18/16”得a的值 4.725。于是，建立直线趋势方程:y=4.725+0.2014706t。若计算各对应年份的趋势值，单击E2，输入“=4.725+0.2014706*A2”，并用填充柄计算即可。最小平方法实际上就是回归分析，只不过这里是以

43、时间为可控制变量(X)，所分析的动态指标为因变量(y)，因此可使用LINEST、INTERCEPT、SLOPE等函数或回归分析工具来建立趋势方程。返回9.5 统计指数中Excel的应用 Excel中没有专门的函数来计算统计指数，但是用一般的公式和函数计算也很方便。下面我们举例说明综合指数和平均数指数的计算方法以及如何利用指数体系进行因素分析。9.5.1 计算综合指数 综合指数又分为数量指标综合指数和质量指标综合指数。【例9.14】下面以某商场三种商品的价格及销售量资料为例，输入Excel后，如图9.11所示。图9.11 Excel综合指数计算结果 首先，要计算G、H、I、J四列的销售额。对于G

44、列，单击G3单元格，输入“=C3*E3”，并利用填充柄功能计算G4和G5；H、I、J列均可仿此计算；然后单击G6单元格，输入“=SUM(G3:G5)”，并利用填充柄功能，计算出G、H、I、J各列的总销售额。1数量指标综合指数的计算 数量指标综合指数就是以价格作为同度量因素(权数)，来编制商品销售量指数，并反映销售量的变动情况。在一般情况下，同度量因素(价格)要固定在基期。因此，单击任一空单元格，例如图9.11，在I7中，输入“=I6G6*100”得100.275，即商品销售量综合指数Kq为100.275。它说明了该商场三种商品总的销售量，报告期比基期平均增长了0.275，而由于销售量的增长使商

45、品销售额增加了(=I6-G6)，即6100元。2质量指标综合指数的计算 质量指标综合指数就是以商品销售量作为同度量因素(权数)，来编制价格指数，反映商品价格的变动情况。一般情况下，同度量因素(销售量)固定在报告期。因此，单击任一空单元格，例如，H7，输入“=H6I6*100”，得96，即商品价格综合指数Kp为96。它说明了该商场三种商品综合价格指数下降了4，由于价格的下降，使商品销售额减少了(=H6-I6)89000元。3销售额指数 单击任一单元格，例如，G7，输入“=H6G6*100”，得96.26。说明由于价格和销售量共同影响，使报告期销售额下降了3.74，共减少了82900元。它是由于销

46、售量提高使销售额增加6100元和价格降低使销售额减少了89000元两个因素共同影响的结果。9.5.2 计算平均指数 1加权算术平均指数 在一定条件下，根据基期同一度量因素编制的数量指标综合指数可以变形为加权算术平均数指数。一般来说，加权算术平均数指数公式多用于计算数量指标指数。下面以图9.12 资料为例，说明如何计算算术平均数指数。图9.12 计算算术平均数指数 在图9.12中，分别插入并计算G、J两列；单击G3单元格，输入“=F3E3”；然后利用填充柄功能计算G4、G5的值；单击J3，输入“=G3*H3”，并利用填充柄功能计算J4、J5；最后计算合计数，单击H6，输入“=SUM(H3:H5)

47、”求出基期总销售额，并利用填充柄功能计算J6。单击任一空单元格，例如J7，输入“=J6H6”，即得销售量的算术平均数指数为100.275。2加权调和平均指数在一定条件下，根据报告期同度量因素计算的质量指标综合指数可以变形为加权调和平均指数。一般来说，加权调和平均指数公式多用于计算质量指标指数。具体方法与加权算术平均指数类似。9.5.3 进行因素分析 【例9.15】现以某企业各类职工月工资及职工人数资料为例，说明平均工资指数体系的编制操作方法，如图9.13 所示。图9.13 平均工资指数体系的因素分析 首先计算月工资总额：单击F3，输入“=B3*D3”，并用填充柄功能计算F4；单击G3，输入“=

48、C3*E3”，并用填充柄功能计算G4；单击H3，输入“=B3*E3”，并用填充柄功能计算H4。然后计算合计数，单击D5，输入“=SUM(D3:D4)”得1000，并利用填充柄功能，计算E、F、G、H列的合计数。可变构成指数(平均工资指数)：单击一空单元格，例如，F7，输入“=(G5E5)(F5D5)”得1.1964。说明总平均工资增长了19.64，每个职工平均增长了(=G5E5-F5D5)90元。固定构成指数：单击一空单元格，例如，G7，输入“=(G5E5)(H5E5)”，得1.2884。说明各类职工月工资的提高使总平均工资提高了28.84，月工资的提高使每个职工平均增加(=G5E5-H5E5

49、)150元。结构影响指数：单击一空单元格，输入“=(H5E5)(F5D5)”，得0.9286。说明各类职工人数的变动，使总平均工资下降了7.14，平均每个职工减少(=H5E5-F5D5)40元。返回9.6 抽样推断中Excel的应用 9.6.1 按不同模式抽选调查单位 抽样组织形式主要有简单随机抽样（即纯随机抽样）、机械抽样、类型抽样、整群抽样及多阶段抽样等。机械抽样是等距离抽样，类型抽样是分层（类）随机抽样，整群抽样是根据随机原则成群（组）抽样，多阶段抽样可以说是多阶段地简单随机抽样。它们都是根据随机原则，结合具体研究对象的性质、调查工作的目的和条件，合理有效地取得所需多样本。因此，本文主要

50、介绍如何利用Excel进行随机抽样过程。Excel提供了随机数发生器工具，可用来产生基于均匀分布、正态分布、二项分布、泊松分布以及一般离散分布的随机数。下面从总体容量为20个数据中抽取一个容量为10的样本。(1)建立一个总体容量为20个数据的工作表。(2)选择B1:B10区域，在工具栏中单击“函数”按钮，打开“插入函数”对话框，在“选择类别”列表中选择“数学与三角函数”，在“函数名”列表中选择随机函数 “RAND”，打开随机函数对话框，同时按住Ctrl+Shift键，单击“确定”按钮，B1:B10 单元格区域中将显示一组大于0、小于1的随机数。(3)将单元格B1中的公式改为“10*RAND()

51、”，同时按住Ctrl+Shift键，回车确定，可以看到B1:B10中的数值均扩大10倍。(4)选择C1:C10区域，在工具栏中单击“函数”按钮，打开“插入函数”对话框，在“选择类别”列表中选择“数学与三角函数”，在“函数名”列表中选择取整函数“CEILING”，打开取整函数对话框，在“Number”中输入“B1:B10”，在“Significance”中输入“1”，按住Ctrl+Shift键，同时单击“确定”按钮。(5)选择D1:D10区域，在工具栏中单击“函数”按钮，打开“粘贴函数”对话框，在“选择类别”列表中选择“查找与引用”，在“函数名”列表中选择索引函数“INDEX”，打开“选定参数”

52、对话框，选择引用(Reference)，单击“确定”按钮，进入索引函数INDEX窗口。(6)在“reference”中输入“A1:A20”，在“Rownum”中输入“C1:C10”。按住Ctrl+Shift键，同时单击“确定”按钮。即可得到所需的样本。9.6.2 用函数方法进行区间估计 利用Excel提供的有关统计函数，可以对总体平均数进行区间估计。现以图9.14为例，假定要求在95的概率保证程度下，对该企业工人的平均日产量进行估计，以及假定要求平均日产量的允许误差范围x=3件，估计该企业工人的平均日产量。首先计算出样本平均数和标准差(假定它们分别放在H1、H2单元格内)。再计算样本平均误差：

53、单击H3，输入“=H2SQRT(20)”。对于第一类问题，利用函数NORMINV计算平均日产量的上限和下限。下面使用函数对话框方式使用函数。单击H4单元格，选择“插入”菜单中“函数”命令，打开选择函数对话框，在“函数分类”中选择“统计”类别，在“函数名”栏内选择“NORMINV函数”，单击确定，打开NORMINV函数设置对话框。本例中，给定的概率保证程度为95，因此在进行NORMINV函数参数设置时，Probability项要输入“0.95+(1-0.95)2”即0.975；在Mean和Standard_dev项内输入样本平均数和样本平均误差，即H1、H3，确定后计算出样本平均数的上限44.2

54、2。在Probability项中输入“(1-095)2”即0.025，计算出样本平均数的下限37.78。对于第二类问题，样本平均数的上限和下限容易计算，分别为44和38。计算概率保证程度可使用NORMDIST函数。打开NORMDIST函数参数设置对话框。在X栏内分别输入计算出的上限和下限，在Mean和Standard_dev栏内输入样本平均数和样本平均误差，在Cumulative栏内输入“true”，这样就分别计算出上限和下限的累积概率0.9661和0.0339，最后上限累积概率减去下限累积概率即为概率保证程度0.93。总体成数本身是样本平均数的特例，因此，总体成数的区间估计与此相同。图9.1

55、4 20个工人日产量分组资料及估计结论返回9.7 相关分析和回归分析中Excel的应用9.7.1 相关分析 利用Excel计算相关系数分析变量之间的相关性，可以按照相关系数公式使用常规方法计算，还可使用CORREL函数或相关系数分析工具计算。【例9.16】现以图9.15生产同类产品的6个企业的月产量和单位成本资料，说明月产量与单位成本之间的相关系数如何利用Excel进行计算。图9.15 月产量与单位成本之间的相关系数计算表 1使用常规方法计算相关系数 使用常规方法就是利用公式计算相关系数：首先，将数据输入Excel中，例如图中B、C两列，然后计算相关系数所需要的各项合计数。单击D2单元格，输入

56、“=B2*B2”，并利用填充柄功能，计算D列。类此，可以计算出E、F两列。然后，单击B8单元格，输入“=SUM(B2:B7)”，求出B列合计数，并利用填充柄功能，按住鼠标左键向右拖至F8单元格计算出C、D、E、F各列的合计数。最后，按照公式计算相关系数，可以首先计算分子，单击任一空单元格，输入“6*E8-B8*C8”，得出结果-162。然后再计算分母，单击另一单元格，输入“=SQRT(6*D8-B8*B8)*(6*F8-C8*C8)”，得出结果168.997。然后单击一单元格输入“=-162168.997”，即得相关系数为-0.9586。也可将分子、分母合成一个式子计算，输入“=(6*E8-B

57、8*C8)SQRT(6*D8-B8*B8)*(6*F8-C8*C8)”。2用CORREL函数计算相关系数 单击“插入”菜单里的“函数”命令，选择函数类别“统计”里的“CORREL函数”，打开相关系数函数CORREL对话框。在Array1、Array2里分别输入两列数据所在区域“B2:B7”和“C2:C7”，即可求得相关系数-0.9586。3利用相关系数分析工具计算 单击“工具”菜单中“数据分析”命令，选择相关函数分析工具。在输入区域内输入“B2:C7”；分组方式需要指出数据是按列还是按行排列。如果输入数据区域第一行中包含标志项，选中“标志位于第一行”。在输出选项中，选择结果放置位置，是在原工作

58、表、新工作表还是新工作簿中。设置完成单击“确定”，得到变量间的相关系数。利用相关系数分析工具可进行多个变量间的相关系数的计算。若取得的资料是分组资料，只有利用常规方法，相应地利用以下公式计算相关系数：其操作过程与未分组资料的计算类似。9.7.2 一元线性回归分析 在Excel中拟合回归直线，可使用常规方法，建立回归直线方程，也可以使用回归函数或回归分析工具来建立回归方程。回归分析包括线性回归分析和非线性回归分析，而线性回归分析又可分为一元回归分析和多元回归分析。本章只介绍一元线性回归分析方法。现仍以图9.15生产同类产品的6个企业的月产量和单位成本资料，说明一元线性回归分析方法。1用常规方法建

59、立回归直线方程 通过公式求得参数a、b的值。其操作方法与计算相关系数相同，不再具体说明。2用统计函数建立回归直线方程 Excel提供了一个求截距的函数INTERCEPT和一个求斜率的函数SLOPE，用这两个函数可以建立回归直线方程。另外，Excel还提供了一个既能用于一元线性回归，又能用于多元线性回归和自回归的函数LINEST，也可以用它来建立回归直线方程。（1）使用截距函数INTERCEPT和斜率函数SLOPE来建立回归方程 单击任一单元格，输入“=INTERCEPT(C2:C7，B2:B7)”求得回归方程的截距79.87。单击另一单元格，输入“=SLOPE(C2:C7，B2:B7)”求得回

60、归方程的斜率-1.106。这样我们就建立了回归直线方程yc=79.87-1.106x。（2）使用LINEST函数来建立回归直线方程 在LINEST函数中，回归方程的表达式与常规不同，它是用y=mx+b的形式来表示的，而且给出的结果除斜率和截距外，还给出估计标准误差等数值，并以数组的形式给出。具体操作如下：首先，选定两列五行作为放置计算结果的单元格区域，例如选定D2:E6。然后单击“插入”菜单中的“函数”命令，选择函数类别“统计”里的“LINEST函数”，打开回归函数LINEST对话框。在Known_ ys里输入因变量数据所在区域“C2:C7”；在Known_ xs里输入自变量数据所在区域“B2

61、:B7”。Const栏内要输入逻辑值，用以指定是否要强制常数b为0。如果输入“TRUE”或忽略，则给出正常B值，如果输入“FALSE”，则给出B=0。我们输入“TRUE”。Stats栏内也要输入逻辑值，如果要给出附加的回归统计值，则输入“TRUE”，如果只需给出斜率和截距，则输入“FALSE”；我们输入“TRUE”。输入后，按Ctrl+Shift+Enter组合键，则计算结果计入选定的区域，如图9.16所示。图9.16 回归函数LINEST分析结果 其中，D列和E列中2行分别为斜率和截距；第3行分别为斜率和截距的标准差；第4行分别为判定系数和估计标准误差；第5行分别为F统计量和自由度；第6行分

62、别为回归平方和剩余平方和。这些数据用于回归直线拟合度和显著性的检验。LINEST函数可用于多元线性分析，在Known_xs里输入多变量数据所在区域，其他项不变。返回知识归纳 本章主要讲解了Excel的特点、基本操作及其在统计分析中的各种应用。Excel具有四项统计功能：函数统计功能、数据管理功能、绘图功能和分析工具库，公式是Excel中的核心，函数是Excel中内置的公式，函数使各种运算变得更加简便。Excel在统计分析中的初级应用，主要包括数据录入、统计分组和制作统计图。利用Excel可以进行静态统计指标分析和动态指标分析。静态指标主要包括平均指标和变异指标。在Excel中对这些指标进行计算

63、时，是通过运用Excel提供的各种函数实现的。动态指标主要包括（平均）增长量、（平均）发展速度和长期发展趋势。其中，（平均）增长量、（平均）发展速度的计算是通过相应的公式实现的，比较简单，而长期发展速度的计算较为复杂。对长期发展趋势的测定，主要有时距扩大法、移动平均法和最小二乘法。因时距扩大法可通过SUM函数（求和函数）方便求得，本章没有讲解。对于计算统计指数，进行抽样推断，也可以应用Excel。统计指数分析主要包括综合指数、平均指数的计算以及因素分析。抽样推断主要包括按不同模式对调查单位的抽选和用函数方法进行区间估计。相关分析和回归分析中Excel的应用。这应属于Excel在统计分析中的高级

64、应用。相关分析就是应用各种方法计算相关系数。在回归分析中只介绍了简单的一元线性回归。相关回归方程的确定主要通过常规方法、统计函数和回归分析工具三种途径实现。返回习题与思考题 1小学四年级甲班同学的成绩如下，试编制相关的柱状图。课程姓名语文数学自然叶红896990王伟657563张力748673黄兴929182李焱636478秦晶717688 2根据下表用Excel中相关的公式和函数计算：（1）法律基础的平均成绩；（2）计算机基础不及格学生的个数；（3）法律基础和计算机基础课程的最高分。课程姓名法律基础计算机基础张力8059李琦7584王殷6376刘美9663郑丽芳5878李永红7181阎娜64

65、90 3计算季度产量和单位成本间的相关系数。月产量x（百件）单位成本y（元）x2X*yy2180211241504175 已知数据为B、C两列。填列计算相关系数时所需的各项合计数。4据调查，每个人平均会花45分钟时间听音乐，下面是其中的样本资料。83.34.34.67.09.20.099.234.981.70.085.40.017.545.053.329.128.80.098.964.54.467.994.27.656.652.9154.670.465.163.6要求：（1）计算均值、众数、方差；（2）计算中位数、偏度；（3）这组资料显示的结果与调查结论是否一致。5.已知某地区农民家庭按年人均

66、收入分组的资料如下表所示。按人均收入分组（元）家庭户数占总户数比重（%）100以下2.310020013.720030019.730040015.240050020.050060014.0600以上100合计 计算该地区平均人均收入的中位数、均值及标准差。6某城市居民2003年家庭人均可支配收入（元/月）如下表所示。家庭人均可支配收入（元/月）家庭（户）200以下122003002530040075400500186500600254600700174700800102800以上45合计873请根据上表数据，用Excel绘制：（1）直方图；（2）曲线图；（3）折线图。7.经济学家担心，失业救济措施可能会使商业循环衰退期阶段被临时解雇的人数发生扭曲。其中一个基本的猜测是：失业救济将会使公司裁减更多的雇佣人员。为验证这一猜测是否成立，收集了如下一组样本资料。救 济 水 平（%）解 雇 率（%）5712.54321.78327.10298.832711.72365.10324.44619.83237.34 试就给定的资料进行回归分析。8.一家大型家具制造商，在14个城市的试营业额及广告费用资