统计中Excel的应用.ppt
《统计中Excel的应用.ppt》由会员分享,可在线阅读,更多相关《统计中Excel的应用.ppt(57页珍藏版)》请在三一办公上搜索。
1、9 统计中Excel的应用,本章主要讲解了Excel的特点及其在统计分析中的各种应用。通过本章的学习,读者应重点掌握以下内容:Excel特点及功能;利用Excel对原始数据进行分组整理,制作统计表和统计图;利用Excel计算各种静态指标和动态指标;利用Excel计算两类总指数、对指数体系进行因素分析;利用Excel作随机抽样和区间估计;利用Excel计算相关系数、作一元回归分析。,教学目标,关键词汇,统计分析(Statistical Analysis)统计推理(Statistical Inference)回归分析(Regression Analysis),9 统计中Excel的应用,9.1 E
2、xcel的特点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”之间的和,则可在编
9、辑栏输入“=SUM(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根据输入数据的格式自动判断数据属于什么类型。如日期型的数据输入格式为“年月日”、“年月日”或“时:分:秒”。要输入逻辑型的数据
12、,输入“True”(真)或“False”(假)即可。若数据由数字与小数点构成,Excel自动将其识别为,数字型,Excel允许在数值型数据前加入货币符号,Excel将其视为货币数值型,Excel也允许数值型数据用科学计数法表示,如2109在Excel中可表示为2E+9。除了以上三种格式以外的数据,Excel将其视为字符型处理。2公式生成数据 Excel的数据中也可由公式直接生成。例如,在当前工作表中A1和B1单元格中已输入了数值数据,欲将A1与B1单元格的数据相加的结果放入C1单元格中,可按如下步骤操作:用鼠标选定C1单元格,然后输入公式“=A1+B1”或输入“=SUM(a1:b1)”,回车之
13、后即可完成操作。C1单元格此时存放实际上是一个数学公式“A1+B1”,因此C1单元格的数值将随着A1、B1单元格的数值的改变而变化。Excel提供了完整的算术运算符,如+(加)、-(减)、(乘)、(除)、(百分比)、(指数)和丰富的函数,如SUM(求和)、CORREL(求相关系数)、STDEV(求标准差)等,供用户对数据执行各种形式的计算操作。在Excel帮助文件中可以查到各类算术运算符和函数的完整使用说明。3复制生成数据 Excel中的数据也可由复制生成。实际上,在生成的数据具有相同的规律性的时候,大部分的数据可以由复制生成。可以在不同单元格之间复制数据,也可以在不同工作表或不同工作簿之间复
14、制数据,可以一次复制一个数据,也可同时复制一批数据,为数据输入带来了极大的方便。普通单元格的复制结果与公式单元格的复制结果相差较大,下面分别予以说明。(1)普通单元格指的是非公式的单元格。拖动鼠标选定待复制的区域,选定之后该区域变为黑色。Excel可以进行整行、整列或整个表格的选定操作。例如,如果要选定表格的第一列,可直接用鼠标单击列标“A”,如果要选定表格的第一行,可直接用鼠标单击行标“1”,如果要选定整个表格,可直接点击最左上角的全选按钮。,选定区域之后,用鼠标右击该区域,在弹出的菜单中选择“复制”命令,将区域内容复制到粘贴板之中。可以发现该区域已被虚线包围。用鼠标右击目标区域,在弹出的菜
15、单中选择“粘贴”命令,则单元格区域的复制即告完成。(2)公式单元格的复制一般可分为两种,一种是值复制,一种是公式复制。值复制指的是只复制公式的计算结果到目标区域,公式复制指的是仅复制公式本身到目标区域。下面对它们的操作步骤分别予以说明。值复制:a拖动鼠标选定待复制区域。b用鼠标右击选定区域,选择“复制”选项。c用鼠标右击目标区域,再单击“选择性粘贴”子菜单。出现复制选项,选定“数值”选项,然后单击“确定”按钮,则值复制即告完成。公式复制:公式复制是Excel数据成批计算的重要操作方法。要熟练公式复制的操作首先要区分好两个概念:单元格的相对引用与绝对引用。4数据自动填充 数据的自动填充可以采用“
16、填充柄”、“Ctrl+Enter”组合键或“填充”命令来完成。利用“填充”命令进行自动填充时,首先要在一个单元格中输入第一个数据,然后选定一个单元格区域,使输入的第一个数据位于单元格区域的顶行、底行、最左边或最右边,单击“编辑”菜单中的“填充”命令,在出现的级连菜单中根据需要选择“向下填充”、“向上填充”、“向左填充”或“向右填充”,可以完成行或列的填充。以上操作也可以通过选取“填充序列”命令,在“序列”对话框中选择“自动填充”来完成。通过“序列”对话框,还可以完成等差数列、等比数列的填充。,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)分类汇总 为了使数据清单的内容更加清晰明确,可
22、以利用Excel的分类汇总功能将数据归组(类),并进行求和、均值等计算,并将计算结果显示出来,以便对数据进行进一步分析。,(2)利用频数分布函数(FREQUENCY)分组 单纯利用排序与分类汇总还不能很好地描述数据的分布状态,为此,Excel提供了一个频数分布函数(FREQUENCY),利用它可以对数据进行分组,建立频数分布,从而更好地描述数据分布状态。该函数以一列垂直数组返回某个区域中数据的频率分布。例如,使用函数FREQUENCY可以计算在给定的分数范围内测验分数的个数。语法:FREQUENCY(data_array,bins_array)其中:data_array为一数组或对一组数值的引
23、用,用来计算频率。如果data_array中不包含任何数值,函数FREQUENCY返回零数组。bins_array为间隔的数组或对间隔的引用,该间隔用于对data_array中的数值进行分组。如果 bins_array中不包含任何数值,函数FREQUENCY返回data_array中元素的个数。在选定相邻单元格区域(该区域用于显示返回的分布结果)后,函数FREQUENCY应以数组公式的形式输入。返回的数组中的元素个数比bins_array(数组)中的元素个数多1。返回的数组中所多出来的元素表示超出最高间隔的数值个数。例如,如果要计算输入到三个单元格中的三个数值区间(间隔),一定要在四个单元格中
24、输入FREQUENCY函数计算的结果。多出来的单元格将返回data_array中大于第三个间隔值的数值个数。,9.2.3 制作统计图,1统计分析图表制作 Excel为绘制统计图表提供了一整套便利的制作技术,可以从工作表数据中创建既复杂又准确、漂亮的图表。【例9.5】下面以饼图为例,说明创建图表一般要经过的几个步骤:(1)确定制图的目的,根据制图的目的搜集和审核统计资料。(2)将统计资料输入到Excel中去。例如图9.4所示教师职称的原始统计资料。,图9.4 教师职称的原始统计资料,(3)计算职工总数。选中单元格B6,在编辑栏内输入公式“=SUM(B2:B5)”,按回车键。(4)计算教师比重。选
25、中单元格C2,并把单元格的数字格式设成“百分比”,在编辑栏内输入公式“=B2/B5”,按回车键。照此方法依次计算单元格C3、C4、C5中的数据。选中单元格C6,在编辑栏内输入公式“=SUM(C2:C5)”,按回车键,即得教师职称计算结果。(5)在想绘制图的数据中任选一个单元格,然后在“插入”菜单中选中“图表”命令,或单击工具栏上的“图表向导”按钮,弹出“图表向导4步骤之1”对话框。在“图表向导4步骤之1”对话框中包括“取消”、“下一步”、“完成”和“按下不放可查看示例”按钮。单击“取消”即停止创建图表,单击“按下不放可查看示例”可以查看所选图表的效果,完成指定图表属性后即可单击“完成”,图表就
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 统计 Excel 应用

链接地址:https://www.31ppt.com/p-5440514.html