研究生培养：Excel在统计分析中的应用.ppt

1、Excel在统计分析中的应用,杨泽峰 ,推荐的Excel学习站点,Excel Home 天新网网络学院办公软件Excel ,主要内容,第一部分 Excel基础 Excel简介 数据的格式化 公式和函数 图表处理 数据管理 第二部分 Excel在统计分析中的应用,1. Excel基础,Microsoft Excel 2003是当前功能强大、技术先进和使用方便灵活的电子表格系统。 Excel的历史渊源 1987年10月，美国Microsoft公司推出视窗版Excel（超越）， 被公认达到软件技术的最佳专业水平； 1993年，微软又把Word和Excel集成在Office办公套装软件内，使其能相互共

2、享数据。 Excel的应用领域 在财务、金融、统计、办公自动化等领域被广泛应用 Excel的应用特点 用户界面友好 操作简单 易学易用,1.1 Excel的基本功能,表格处理： 可制作各类表格，并具有强大的处理功能 图形处理： 系统有100种以上的图表供选用，还可自行绘制 数据运算： 有400多个函数,可完成复杂的表内和表间计算，并可进行适当的统计分析 数据库管理： 将有组织的数据清单当作数据库处理,1.2.1 Excel的界面,Excel 2003的基本概念,工作簿 在Excel中创建的文件叫做工作簿。 工作簿由工作表组成，可以是一个，也可以是多个，最多可以包括255个工作表。 工作表 工作

3、表就是人们平常所说的电子表格，它与我们日常生活中的表格基本相同，由一些横向和纵向的网格组成，横向称为行，纵向称为列，在网格中可以填写不同的数据。 一个工作表最多可有65536行，256列。,Excel 2003的基本概念,行号 工作表由65536行组成，每行用一个数字进行编号。 单击行号可选定工作表中的整行单元格，在行号上单击右键，将显示相应的快捷菜单。 拖动行号下端的边线，可以改变改行的高度。 列标 每列用字母来标识，称为列标，列标用字母 A 到 IV 表示，共 256 列。 单击列标可选定该列的全部单元格，在列标上单击右键将显示相应的快捷菜单。 拖动列标右端的边线，可改变改列的宽度，双击该

4、列标号的右边线可用自动调整改列到合适的宽度。,Excel 2003的基本概念,单元格 Excel的工作表实际上是一个二维表格，单元格就是这个表格中的一个“格子”。 单元格是输入数据、处理数据及显示数据的基本单位。 单元格中的内容可以是数字、文本或计算公式等，最多可包含32000个字符。,Excel 2003的基本概念,公式 公式是对工作表数据进行运算的表达式。 公式不仅可以进行数学运算，如加法和乘法，还可以比较工作表数据或合并文本。 在公式中可以引用同一工作表中的不同单元格、同一工作簿不同工作表中的单元格，或者其他工作簿的工作表中的单元格。 在Excel中输入公式时，第一个输入的符号是等号。

5、活动单元格 在工作表中，有时会有一个或多个单元格由粗边框包围，由粗边框包围的单元格称为活动单元格 活动单元格代表当前正在输入或编辑数据的单元格。,1.3 工作簿,工作簿是Excel管理数据的文件单位，相当于人们日常生活中的“文件夹”，它们以独立文件的形式存储在磁盘上。,工作簿,2005级学生成绩.xls,五班的成绩表,四班的成绩表,三班的成绩表,二班的成绩表,一班的成绩表,工作表,1.3 工作簿,建立新工作簿 启动Excel 2003，就自动建立一个名为“book1.xls”的新工作簿。 根据已有模板建立特殊的工作簿。 如：摇奖器、简单贷款计算器 保存工作簿 为Excel文件设置密码 设置密码

6、阻止其他人打开自己的文件。 打开权限密码 修改权限密码,1.4 输入数据,选择单元格及单元格区域 简单数据输入 过程 选定要输入数据的单元格 从键盘上输入数据 按Enter键，则本列的下一单元格成为活动单元格，也可用方向键或鼠标选择下一个要输入的单元格。,1.4 输入数据,数据输入的方式 输入数值 可采用普通记数法与科学记数法；允许输入分节号；负数可用“-78”或（78）的形式表示； 默认的对齐方式为靠右对齐 输入文本 默认对齐方式为左对齐 在默认的情况下，一个单元格只显示8个字符，如果输入的文本宽度超过了单元格宽度，表面上它会覆盖右侧单元格，但是仍是本单元格内容，不会丢失。 日期及时间 输入

7、日期的一般格式为“年/月/日”或“月/日”；按“ctrl；”输入当前系统日期；按“ctrlshift；”输入当前系统时间。,1.4 输入数据,数据输入的方式 输入公式 用公式对工作表的数据进行计算 必须以“”开头 采取复制填充的方法输入有规律的数据 对输入大量相同的数据或递增、递减性质的数据是大有帮助的。 采用填充的方法输入数据 与上种方法类似 【编辑】【填充】【序列】,1.4 输入数据,数据输入的方式 下拉列表输入 针对于整列数据只能在几个固定值中选择 如：教师职称 采用【数据】 菜单中的子菜单【有效性】 自定义填充序列 对于某些常用的数据序列，Excel把它定义为自动填充序列，如月份、星期

8、等。在输入这些数据时，只需输入第一个数据，然后就可用填充方式让系统自动生成其他数据。 对于一些常用的数据序列而Excel中尚未包含的，可以采用自定义序列的方式输入。 【工具】【选项】【自定义序列】,1.5 工作表,多表切换与数据复制 工作表的切换 选择适当的工作表标签 插入工作表 【插入】【工作表】 工作表标签处右键菜单【插入】【工作表】 删除工作表 【编辑】【删除工作表】 工作表标签处右键菜单【删除】,1.5 工作表,多表切换与数据复制 移动工作表 鼠标拖动 单元格数据的复制 工作表标签改名 双击工作表标签 工作表标签处右键菜单【重命名】,1.5 工作表,工作表行、列及单元格操作 工作表行、

9、列 选择行、列 删除行、列 插入行、列 调整行高 鼠标拖动 【格式】【行】【行高】 右键菜单【行高】 调整列宽 鼠标拖动 【格式】【列】【列宽】 右键菜单【列宽】,1.5 工作表,单元格操作 选择单元格 清楚单元格内容 删除单元格 插入单元格 移动单元格 复制单元格 合并单元格,2. 数据的格式化,对工作表数据进行格式化，不仅能够美化工作表，而且还可以使工作表数据的含义更加清晰。 例如：对不同的文本设置不同大小的字号，用不同的色彩表示不同的信息，设置数据的不同对齐方式等，可把表中数据所代表的信息表示得更清楚，更容易被他人认可和接受； 在输入某些数据时，通过数字格式化还能够减少输入，提高工作效率

10、。,2.1 工作表的格式化,自动套用格式 Excel提供了16种已做好的格式对工作表进行格式设置 【格式】【自动套用格式】,2.1 工作表的格式化,设置工作表的边框和底纹 Excel的默认边框是虚线、无底纹，所有的表格都表示为同一样式。 如果在Excel的默认方式下打印工作表，打印出的结果将没有表格线，不便于特殊信息的显示。 通过边框和底纹的设置，可使表格美观，同时也可改变信息的显示方式。 【格式】【单元格】【边框】【底纹】 右键菜单【单元格格式】【边框】【底纹】 工具栏工具,2.1 工作表的格式化,设置工作表标签的颜色 在一个工作簿中包含了 3 个甚至更多的工作表，对于工作表的内容和完成的程

11、度会因工作表太多而忘记，影响工作的进度。 Excel 2003 为用户提供了设置工作表标签的功能，这样用户就可根据内容和进度为工作表标签设置不同的颜色以做区分，这样就大大提高了工作效率。 【格式】【工作表】【工作表标签颜色】 右键菜单【工作表标签颜色】,2.2 文本的格式化设置,字体、字形、字体颜色的设置 字体有不同的样式：无格式、黑体、斜体、带下划线、加删除线等。 字体的设置非常简单，选中需要进行格式设置的单元格或字符，选择【格式】菜单的【单元格】命令，或直接利用【格式】工具栏进行设置。,2.2 文本的格式化设置,设置字符及数字的对齐方式 跨列居中 垂直对齐方式 【格式】【单元格】【对齐】

12、文本、数字的旋转 【格式】【单元格】【对齐】 单元格内文本换行 AltEnter 取消合并,2.3 格式化数据,Excel的默认数据格式 当输入“0273837”时，Excel认为输入的是一个数字，前面的0去掉。 当输入“12/5”时，Excel认为该输入为日期。 使用系统提供的数据格式化 增加或减少小数位数 设置百分比显示方式 分节显示数据 货币数据显示方式,2.3 格式化数据,条件格式 不同的数据可按不同的条件设置其显示格式。 如：一个学生成绩表中，将所有不及格的成绩显示为红色，可以轻松地看出其中不及格的成绩情况。在企业的销售表中，将利润较小的或无利润的销售数据设置为红色，可以使人在查看这

13、些数据时一目了然。 首先选中需要设置条件的数据的单元格区域 【格式】【条件格式】,3. 公式和函数,数组公式及其应用 名字 函数简介 使用Excel帮助理解函数 常用的工作表函数 逻辑函数 数学和三角函数,3.1 公式,Excel的公式与日常工作中的公式非常近似，它由运算符、数值、字符串、变量和函数组成。 公式必须以“”开头，换句话说，在Excel的单元格中，凡是以等号开头的输入数据都被认为是公式。 在等号后面可以跟数值、运算符、变量或函数，公式中还可以有括号。 例如：在单元格B5中输入的“6+7*6/3+(6/5)*56”就是一个公式，最后该公式的计算结果将显示在单元格B5中。,公式运算的优

14、先级,引用,在公式中可以使用引用，引用的意思可简单理解为：在一个单元格的公式中用到了其他单元格中的内容。 引用是通过单元格的位置或单元格的名称来实现。 在默认的状态下，Excel工作表中的单元格都是采用列标号和行号来识别的，如A1，B3，C4等都惟一确定了一个单元格。,公式,相对引用 相对引用也称为相对地址，它用列标与行号直接表示单元格，如A2，B5，D8等都是相对引用。 如果某个单元格的公式被复制到另一个单元格时，原单元格内公式中的地址在新单元格中需要发生相应的变化，就需要用相对引用来实现。,公式,绝对引用 在表示单元格的列标与行号前面加“$”符号的单元格引用就称为绝对引用。 它的最大特点是

15、在操作过程中，公式中的单元格地址始终保持不变，相对地址与绝对地址可以混合使用。 使用方法：选中要作为绝对引用的单元格后按F4键。,3.1 数组公式及其应用,在Excel中，公式是以等号（或“”）开头，由常数、函数、单元格引用或运算符组成的式子，并将计算结果显示在相应的单元格中。 在公式中可以使用的运算符有算术运算符、比较运算符、字符连接运算符（&）。 数组公式可以对两组或两组以上的数据（两个或两个以上的单元格区域）同时进行计算。 在数组公式中使用的数据称为数组参数，数组参数可以是一个数据区域，也可以是数组常量。,数组公式的建立方法,建立数组公式进行批量数据的处理，操作步骤为： 如果希望数组公式

16、返回一个结果，可先选中保存计算结果的单元格；如果数组公式返回多个结果，则需先选中要保存数组公式计算结果的单元格区域。 输入公式的内容。 公式输入完成后，按CtrlShiftEnter键结束。 实例 用数组公式计算两个数据区域的乘积 用数组公式计算多列数据之和,3.2 名字,在公式中引用单元格主要是通过单元格的行、列标号进行的，如果公式要引用另一工作表中的单元格，很不方便。 Excel允许为单元格或单元格区域定义名字，即用一个名字来代替单元格或区域的引用，在需要引用该单元格时，直接用它的名字就行了。 名字的好处 公式的意义更加清楚，便于他人理解。 在一个工作表中定义的名字，可在同一工作簿的任一工

17、作表中直接应用。,3.2 名字,名字的定义 使用菜单栏定义名字 定义方法一次只能定义一个名字 适用于一个单元格或单元格区域名字的定义 【插入】【名称】【定义】 用指定的方式定义名字 一次定义多个名字，可以使用指定方式。,3.2 名字,名字的定义 用名字栏定义名字 在名字栏输入名字后按回车键即可,名字栏,3.2 名字,引用另一个工作表中的名字 定义一个名字后，该名字就能被工作簿中的各个工作表直接引用。 行列交叉点 所谓行列交叉点，就是指行的名字 列的名字确定的单元格。 【插入】【名称】【指定】【首行】【最左列】,3.3 函数简介,函数实际上是Excel已经定义好了的一些特殊公式，它们可以对一个或

18、多个数据进行计算，然后把计算结果存放到某个单元格中。 强大的函数功能把Excel与普通的电子表格区分开来。 普通的电子表格如Word的表格、中文之星中的表格，只能完成一般的表格制作功能，进行相当简单的数据处理。 从本质上讲，函数是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。 Excel提供了大量的内置函数，涉及到许多工作领域，如财务、工程、统计、数据库、时间、数学等。,3.4.1 函数调用,函数的语法 Excel的函数结构大致可以分为函数名和参数表两部分 如：函数名（参数1,参数2, ） “函数名”表明要执行的运算 “函数名”后用圆括号括起来的是参数表，参数表说

19、明函数使用的单元格或数值,3.4.1 函数调用,AVERAGE（A1:B5,C1:D2,12,32）,等号，如果函数位于公式的开头,函数名,各参数之间用逗号隔开,参数表,参数用括号括起,3.4.1 函数调用,函数的语法 没有参数的函数称为无参函数 无参函数的调用形式为：函数名（） 无参函数名后的圆括号是必需的。 如PI函数，调用的形式为：PI(),3.4.1 函数调用,函数调用 直接输入 如果知道函数名及函数的参数，就可以在公式或表达式中直接输入函数，这是最常用的一种函数输入方式。 使用函数向导 通过函数向导，可以知道函数需要的各个参数及参数的类型，方便的输入并不熟悉的函数。 使用Excel帮

20、助理解函数,3.5 常用工作表函数,自动求和按钮 SUM函数 AVERAGE函数 SQRT函数 COUNT、COUNTA和COUNTBLANK函数 COUNT函数统计数值数据的个数 COUNTA统计非空单元格的个数 COUNTBLANK统计空白单元格的数目,3.6 逻辑函数,比较运算和逻辑运算 比较运算 比较运算可以比较两个值的大小、相等或不等关系。在Excel中，比较运算的结果是一个逻辑值，不是TRUE就是FALSE。 等于【】 大于【】 小于【=】； 小于等于【】,3.6 逻辑函数,AND、OR函数 AND逻辑与 OR逻辑或 条件函数IF IF函数也称为条件函数，它根据参数条件的真假，返回

21、不同的结果。 可以使用IF函数对数值和公式进行条件检测。 语句 IF(logical,v1,v2) 参数logical是一个条件表达式，它可以是比较式或逻辑表达式 V1是logical测试条件为真时函数的返回值 V2是logical测试条件为假时函数的返回值,3.7 数学和三角函数,几个常用的数学函数 随机函数RAND 产生一个大于等于0且小于1的均匀分布的随机数。 RANDBETWEEN（bottom，top） 产生一个介于bottom和top之间的随机整数。 取整函数INT 该函数用于取整，返回实数x舍入后不大于x的整数值。 ROUND（number，n） 该函数返回某个数字按指定位数四舍

22、五入的数字。,3.7 数学和三角函数,ROMAN（number，form） 将阿拉伯数字转换为文本形式的罗马数字。Number是需要转换的阿拉伯数字，只能取03999之间的数值。 逆矩阵函数MINVERSE（array） Array是具有相等行列数的数值数组（即方阵）。 MMULT（array1，array2） 计算两数组的矩阵乘积。 结果矩阵的行数与array1的行数相同，列数与array2的列数相同。,3.8 日期及时间函数,日期及时间函数 Now 返回当前的时间 Today 返回当天的日期 Weekday 计算格顶的日期是星期几,函数的应用,利用函数制作考研的倒计时牌,4. 图表处理,认

23、识Excel图表 图表类型 图表的建立 图表的修改 格式化图表 复杂图表的建立 数理统计图表的应用,4.1 认识Excel图表,4.2 图表类型,嵌入式图表和图表工作表 嵌入式图表是把图表直接插入到数据所在的工作表，主要用于说明数据与工作表的关系，用图表来说明和解释工作表中的数据，具有很强的说服力。 图表工作表是把图表和与之相关的源数据表分开存放，图表放在一个独立的工作表中，用于创建图表中的数据表则存放在另一个独立的工作表中。,4.2 图表类型,Excel标准图表类型 面积图 面积图用于显示不同数据系列之间的对比关系，同时也显示各数据系列与整体的比例关系，尤其强调随时间的变化幅度。 柱形图 即

24、直方图，常用来表示不同项目之间的比较结果，也可用来对比数据在一定时间内的变化情况。为Excel默认类型。 条形图 显示了各个项目之间的比较情况，纵轴表示分类，横轴表示值。,4.2 图表类型,Excel标准图表类型 折线图 强调数据的发展趋势。 饼图 强调总体与部分的关系，常用于表示各组分部分在总体中所占的百分比。 其它标准图 圆环图、气泡图、雷达图、股价图、曲面图、XY散点图、锥形图、圆柱图和棱锥图等14中标准图表类型。,4.3 图表的建立,建立图表 【插入】【图表】 在常用工具栏中点击“图表向导”按钮 图表建立的步骤 建立数据表 选择图表类型 选择图表数据系列 修改图表格式 选择图表的位置,

25、4.4 修改图表,修改图表类型 【图表】【图表类型】 数据系列操作 添加数据系列 删除数据系列 修改数据系列的产生方式 【图表】【源数据】 添加数据标志 默认状态下，图表向导创建的图表中没有数据标志。 数据标志可以是各数据点的值，也可以是与数据点有关的分类轴标志。 【图表】【图表选项】【数据标志】,4.4 修改图表,图例操作 图例用来说明数据系列使用的标志或符号，用以区分不同的数据系列。 【图表】【图表选项】【图例】 单元格与数据点的值的修改 在Excel中，工作表单元格中的数据与图表中的对应数据点是相互影响的，在任何时候，图表都是工作表数据的真实反映。 取消或设置网格线,4.5 格式化图表,

26、调整图表大小 改变图表颜色、图案、边框 设置背景图片 右键菜单【图表区格式】【图案】 【填充效果】【图片】 填充数据系列 右键菜单【数据系列格式】,4.6 复杂图表的建立,目的 在Excel图表不能满足应用需要的时候，可以运用一些小技巧建立相对复杂的图表，如建立组合图表、图片图表、为图表添加趋势线等。,4.6 复杂图表的建立,添加趋势线 趋势线以图形方式显示了数据的发展趋势，它常用于预测分析。 支持趋势线的图表类型有非堆积型面积图、条形图、柱形图、折线图、股价图、气泡图和XY散点图等。 添加趋势线 【图表】【添加趋势线】 利用趋势线进行预测 对现有趋势线进行向前（或向后）的延伸，然后根据延伸的

27、趋势线可以分析数据的发展趋势，并借此进行数据预测。,4.6 复杂图表的建立,建立组合图 组合图是指在一个图表中表示两个或两个以上的数据系列，而且不同的数据系列用不同的图表类型表示。 至少有三种不同的方法可以创建组合图表 采用图表向导建立组合图表 利用Excel内置的“自定义类型”建立组合图表 把已建立好的图表中的一个数据系列改变为另一种图表类型,4.7 数理统计图表的应用,累计频率分布图和直方图 输入源数据表 确定分组 组距（最大数据值最小数据值）/组数 每组上边界值每组下边界值组距 假设第一组的下边界值最小数据值组距 用Excel的分析工具库绘制频数分布直方图 【工具】【数据分析】 【直方图

28、】,4.7 数理统计图表的应用,一元回归预测 作出XY散点图 添加趋势线 【选项】【显示公式】,4.7 数理统计图表的应用,绘制正态分布图 计算正态分布函数值 NORMDIST（X，均值，标准差，FALSE） 计算标准正态累积分布函数值 NORMSDIST（X） 绘制正态分布图,5. 数据管理,数据排序 数据筛选 数据的分类与汇总,5.1 数据排序,数字 按从最小的负数到最大的正数进行排序。 字母 按照英文字典中的先后顺序排列，即按AZ和az的次序排列。 逻辑值 FALSE（相当于0）排在TRUE（相当于1）之前 空格 空格始终排在最后。 汉字 一是根据汉语拼音的字典顺序进行排列 二是按笔画排

29、序,5.2 数据筛选,有时需要从工作表的数据行中找出满足一定条件的几行或几列数据，这就要用到Excel的数据筛选功能。 数据筛选将工作表中所有不满足条件的数据行暂时隐藏起来，只显示那些满足条件的数据行（数据并没有丢失）。 Excel提供了两种不同的筛选方式： 自动筛选 高级筛选,5.2 数据筛选,自动筛选 自动筛选提供了快速查找工作表数据的功能，只需通过简单的操作就能够筛选出需要的数据。 操作过程： 单击表中的任一单元格 选择【数据】【筛选】【自动筛选】命令，在数据表第一行的每个列标题的右边会添加一个下拉列表标志。 单击需要筛选的列的下拉表箭头，Excel将显示出可用的筛选条件，从中选择需要的

30、条件。,5.3 数据的分类与汇总,分类汇总概述 如果一个工作表中的数据行太多，就很难从中看清某些信息。分类汇总能够对工作表数据按不同的类别进行汇总统计（求总和，平均值、最大值、最小值、统计个数等）。 分类汇总采用分级显示的方式显示工作表数据，它可以收缩或展开工作表的数据行（或列），可快速创建各种汇总报告。 分级显示可以汇总整个工作表或其中选定的一部分，Excel允许多达八层的分级显示。,5.3 数据的分类与汇总,建立分类汇总 分类汇总为分析汇总数据提供了非常灵活的方式，它可以完成以下事情： 在数据表中显示一组数据的分类汇总及总和 在数据表中显示多组数据的分类汇总及总和 在分组数据上完成不同的计

31、算 分类汇总的准备工作 要进行分类汇总的数据表的各列必需要有标题 要进行分类汇总的数据列已排好序 建立分类汇总 【数据】【分类汇总】,第二部分 统计分析功能,统计数据的搜集与整理 统计数据的描述 随机变量的概率分布 参数估计 假设测验 方差分析 相关和回归分析,1. 统计数据的搜集与整理,统计数据的搜集 组织抽样调查，首先从调查对象中抽取部分单位作为样本，然后对样本单位进行调查，再根据调查取得的样本数据推断总体的数量特征。 分析工具库中的“抽样”工具 周期模式：等距抽样或机械抽样 随机模式：适合于纯随机抽样、分类抽样、整群抽样和阶段抽样 统计数据的整理 用frequency函数进行统计分组 用

32、直方图工具进行分组、计算频率和绘图 可将数据分组，计算样本单位在各组中出现的次数（频数）和频率，绘出次数分布直方图和累计频率折线图。,2. 统计数据的描述,集中趋势的描述 众数 出现次数最多的变量值函数Mode 若是分组资料： 中位数 处于中间位置的数值函数Median 对于分组资料： 平均数 函数Average,2. 统计数据的描述,集中趋势的描述 调和平均数 用于计算平均速率。 变量道术的算术平均数的反倒数 函数Harmean 几何平均数 计算平均增长率 变量对数的算术平均数的反对数 函数Geomean,2. 统计数据的描述,离散程度的描述 标准差 求样本标准差的函数Stdev 求总体标准差的函数Stdevp 方差 求样本方差的函数Var 求总体方差的函数Varp 描述统计工具的使用 【工具】【数据分析】【描述统计】,3. 随机变量的概率分布,二项分布 0、1总体中n+1种变量有它各自的概率组成的一个分布。 计算二项分布下变