数据统计与分析

1、第第4章章 数据统计与分析数据统计与分析Excel2010主讲人 姜永生4.1 Excel2010概述概述 Excel是Microsoft Office system中的电子表格程序。可以使用Excel创建工作簿并设置工作簿格式，以便分析数据。同时可以使用Excel跟踪数据，生成数据分析模型，编写公式以对数据进行计算，以多种方式透视数据，并以各种具有专业外观的图表来显示数据。4.1 Excel2010概述概述4.1.1Excel2010Excel2010的窗口组成的窗口组成 标题栏：包含了快速工具栏、控制窗口的三个按钮（最小化按钮、还原按钮、关闭按钮）、程序名称“Microsoft Excel

2、”、正在编辑的文档名称等。 状态栏：包含当前文档的页数/总页数，总字数、语言种类、当前文档的插入/改写状态、页面视图、显示缩放比例。 4.1 Excel2010概述概述4.1.1Excel2010Excel2010的窗口组成的窗口组成 功能区各选项卡简介 ： “开始”选项卡 “开始”选项卡中包括常用的剪贴板、字体、对齐方式、数字、样式、单元格和编辑7个组。 “插入”选项卡 “插入”选项卡包括表格、插图、图表、迷你图、链接、文本、符号和特殊符号8个组 “页面布局”选项卡 “页面布局”选项卡包括主题设置、页面设置、调整为合适大小、工作表选项、排列5个组 4.1 Excel2010概述概述1 1Ex

3、cel2010Excel2010的窗口组成的窗口组成 功能区各选项卡简介 ： “公式”选项卡 “公式”选项卡包括数据库、定义的名称、公式审核、计算4组，用于实现在Excel2010文档中数据统计与计算等功能 “数据”选项卡 “数据”选项卡包括获取外部数据、连接、排序和筛选、数据工具和分级显示5个组，用于在Excel2010文档中进行数据统计和筛选方面的操作 。 “审阅”选项卡 “审阅”选项卡包括校对、语言、中文简繁转换、批注、更改5个组，主要用于对Excel2010文档进行校对和更改等操作 4.1 Excel2010概述概述1 1Excel2010Excel2010的窗口组成的窗口组成 功能区

4、各选项卡简介 ： “视图”选项卡 视图”选项卡包括工作薄视图、显示、显示比例、窗口和宏5个组，主要用于设置Excel2010操作窗口的视图类型，以方便操作 “加载项”选项卡 “加载项”选项卡包括菜单命令一个分组，加载项是可为Excel2010安装附加属性，如自定义的工具栏或其它命令扩展。 选项卡面板中的按钮 按钮分为上下（或左右）两部分，单击按钮时上下两部分间有分隔线。4.1 Excel2010概述概述4.1.1 Excel20104.1.1 Excel2010的窗口组成的窗口组成 功能区各选项卡简介 ： 编辑栏、单元格、工作表 （1）编辑栏 编辑栏左边为名称栏，用于显示活动单元格或区域的地址

5、。 （2）工作区 工作区用于编辑和处理数据。 （3）工作薄与工作表 工作簿是计算和储存数据的文件，一个工作簿就是一个Excel文件，其扩展名为“.xlsx”或“.xls”。一个工作簿可以包含多个工作表，这样可使一个文件中包含多种类型的相关信息，用户可以将若干相关工作表组成一个工作簿 。4.1 Excel2010概述概述5.1.1Excel20105.1.1Excel2010的窗口组成的窗口组成 （4）单元格 Excel 2010的工作表由1048576（220）行、16384（214）列组成，每一行列交叉处即为一单元格。一个单元格最多可以保存32000个字符，每个单元格的地址由列号和行号来引用

6、 。 单元格的引用 工作表中单元格地址的引用方法分绝对引用、相对引用、混合引用。其区别是引用地址里是否有“$”符号，没有就是相对引用。 相对引用：以列标和行号组成，如A1、B8，其中不包含符号“$”。 4.1 Excel2010概述概述5.1.1Excel20105.1.1Excel2010的窗口组成的窗口组成 绝对引用：列标和行号前都加符号“$”，如$A$1。 混合引用：列标或行号前加符号，如$A1（列绝对，行相对）；A$1（列相对，行绝对）。 单元格区域的引用 单元格区域的引用与单元格相同，也适用绝对引用、相对引用、混合引用三种引用方式。 A10:A20，表示A列第10到第20行的一系列单

7、元格。 B15:E15，表示第15行从B列到E列的一系列单元格。 A10:E20，表示A10到E 20矩形区域中的所有单元格。 交叉区域的引用 两个或多个单元格区域有交叉，其中部分单元格在两个单元格区域中都包含，在计算数据时会容易出现重复计算。 4.1 Excel2010概述概述5.1.1Excel20105.1.1Excel2010的窗口组成的窗口组成 两个单元格区域间用空格分隔，如（C1:C8 A4:E4）例“=sum（C1:C8 A4:E4）”表示计算（C1:C8）与（A4:E4）两个区域的数值之和。 不同工作表中单元格的引用 引用另外一个不同工作表中的单元格，采用格式为：工作表名！单元

8、格名 。 如Sheet2！A4，表示引用工作表名为Sheet2中的A4单元格。 例“=average(Sheet3!B1:B10)”，表示利用函数计算本工作簿的“Sheet3”工作表中的B1:B10的平均值。 例“=sum(Sheet2:Sheet13!B5)”，表示把包括工作表Sheet 2和工作表Sheet 13之间所有工作表中的B5单元格的数据相加。4.1 Excel2010概述概述4.1.2 Excel2010的常用操作 “文件”选项卡 “文件”菜单在整个窗口的左上角。选择“文件”命令，弹出保存、另存为、打开、关闭、信息、最近所有文件、新建、打印、保存并发送、选项等常用的设置 。 4.

9、1 Excel2010概述概述4.1.2 Excel2010的常用操作 视图设置 Excel2010工作表包含三种视图模式，即普通视图、分页预览和页面布局视图，可以通过单击状态栏中右方的视图切换按钮，在三种视图模式之间进行切换。 单元格和区域的选择 （1）选择单元格 在工作表中用粗边框框住的单元格称为“当前活动单元格”，可以通过鼠标指针单击某单元格使其成为“当前活动单元格”；也可以用键盘上四个光标箭头移动“当前活动单元格”。 （2）选择单元格区域 选择单元格区域即选定多个单元格 4.1 Excel2010概述概述4.1.2 Excel2010的常用操作 单元格和区域的选择 （3）选定特殊区域的

10、快捷方式 单击工作表左侧相应的行号选定一整行； 单击工作表上方相应的列号选定一整列； 单击工作表左上角的全选框选定整个工作表； 选定不连续的区域，则在选中一个区域之后，按住Ctrl键，再用上述方法选定其它区域； 选定连续的区域即在选中一个区域之后，按住Shift键，再用上述方法选定其它区域。 4.1 Excel2010概述概述4.1.2 Excel2010的常用操作 “设置单元格格式”对话框 打开“设置单元格格式”对话框的常用方法有三种。 方法一：切换到“开始”选项卡，单击“数字”、“字体”、“对齐方式”组右下角的“对话框启动器”按钮。 方法二：单击“开始”“格式”按钮，弹出“格式”选项菜单，

11、选择“设置单元格格式”命令。 方法三：光标指向单元格，单击右键在弹出的快捷菜单中选择“设置单元格格式”命令。 打开“设置单元格格式”的对话框，其中包括“数字”、“对齐”、“字体”、“边框”、“填充”、“保护”6个选项卡 4.1 Excel2010概述概述4.1.2 Excel2010的常用操作 “设置单元格格式”对话框 （1）“数字”选项卡 用于设置单元格中数字的类型与格式，Excel内置了许多数字格式，供选择，如数值、日期、货币等。 （2）“对齐”选项卡“对齐”选项卡中的设置，可以在单元格中对文本和数字进行定位、更改方向并指定文本控制功能。 （3）“字体”选项卡“字体”选项卡，可以设置所选单

12、元格中的字符的字体属性，通过“预览”框可预览所做的设置。 4.1 Excel2010概述概述4.1.2 Excel2010的常用操作 “设置单元格格式”对话框 （4）“边框”选项卡 Excel中，可以给单个单元格、单元格区域周围加边框，还可以从单元格的左上角到右下角或从单元格的左下角到右上角画线；可更改线条样式、线条粗细或线条颜色。 （5）“填充”选项卡“填充”选项卡上的设置，可以为选定的单元格设置背景色 （6）“保护”选项卡“保护”选项卡提供“锁定”、“隐藏”两个选项，用来保护工作表中的数据和公式。 4.2 数据的输入数据的输入4.2.1文本输入文本输入 Excel文本包括中文、英文、数字或

13、其他键盘能键入的符号，文本输入时默认为左对齐。某些数字如电话号码、邮政编码常常当作字符处理。此时只须在输入数字前加上一个撇号“”，Excel将把它当作文本看待，采用左对齐。 如果输入的文本长度超出单元格宽度，若右边单元格无内容，则扩展到右边列；否则，截断显示。 若出现“#”字样，则表示列宽不足。 4.2 数据的输入数据的输入4.2.2 2 数值输入数值输入 Excel中，输入的数值可以是数字09、+、-、（ ）、/、E、e、$、%、小数点“.”和千分位符号“,”等，数值输入时默认为右对齐。 在输入分数时，为避免与日期相混淆，要求在数字前加一个0与空格。 输入负数时，可将负数置于括号“（ ）”中

14、。 Excel数值输入与数值显示未必相同，如输入数字长度超出单元格宽度（采用“常规”格式的数字长度为 11 位），Excel自动以科学计数法表示。 4.2 数据的输入数据的输入4.2.3日期时间输入日期时间输入 日期型数据，用斜杠“/”或减号“-”分隔日期的年、月、日部分 。 时间型数据，系统自动以24小时制表示。如果要以12小时制表示，则在输入的时间后加一个空格，然后再输入AM（上午）或PM（下午）。 Excel将日期和时间视为数字处理。在键入了Excel可以识别的日期或时间数据后，单元格的格式会从“常规”数字格式改为内置的日期或时间格式。默认状态下，日期和时间项在单元格中右对齐。如果Exc

15、el不能识别输入的日期或时间格式，输入的内容将被视作文本，并在单元格中左对齐。 4.2 数据的输入数据的输入4.2.3日期时间输入日期时间输入 如果要在同一单元格中同时键入日期和时间，日期与时间之间要用空格分隔。 输入当前日期按组合键“Ctrl+；”，输入当前时间按组合键“Ctrl+Shift+：”。4.2 数据的输入数据的输入4.2.4设置单元格数据类型设置单元格数据类型 常规单元格不包括任何特定的数据格式，通常系统按输入的数据进行自动判别。给单元格输入数据，可以先设置单元格数据类型，然后再输入数据。 设置单元格的数据类型 自定义数字格式 要创建自定义数字格式，可使用“设置单元格格式”对话框

16、中的“数字”选项卡，请在“类别”列表中单击“自定义”选项，然后在“类型”文本框中键入自定义数字格式。4.2 数据的输入数据的输入4.2.5公式与函数输入公式与函数输入 输入公式 输入公式时，有以下的规则： （1）在输入公式前，先输入一个“=”号或“+”号。 （2）公式中使用的运算符主要有四种：算术运算符、比较运算符、文本运算符、引用运算符。 算术运算符包括：+、- 、* 、/ 、%（百分数）和（乘方）； 比较运算符包括：=、； 文本运算符：，用于连接两个文本； 引用运算符包括：:（冒号）、，（逗号）、空格。 括号运算：在Excel中，括号只有一种，即圆括号“（）”，平常书写中的其它括号均要转换

17、为圆括号才能参加运算。4.2 数据的输入数据的输入4.2.5公式与函数输入公式与函数输入 输入函数 Excel 提供了许多内置函数，对数据运算和分析带来了方便。内置的函数包括：财务、日期与时间、数学与三角函数、统计、查找与引用、数据库、文本、逻辑、信息等14大类 。 函数的语法形式为：函数名（参数1，参数2，），其中的参数可以是常量、单元格、区域、区域名或其它函数。 （1）函数的输入方法：直接输入法，粘贴函数法。 （2）自动求和，Excel提供了“自动求和”功能，可以快捷地批量输入sum函数。4.2 数据的输入数据的输入4.2.5公式与函数输入公式与函数输入 公式审核 Excel2010提供了

18、公式审核功能，用于追踪引用单元格，显示公式、错误检测等。通过“公式”选项卡中的“公式审核”组所提供的相应功能，来检查公式使用情况。 4.2 数据的输入数据的输入4.2.6 数据的快速输入数据的快速输入 Excel中输入有规律的数据，可以考虑使用数据自动输入功能，方便、高效输入等差、等比、自定义序列的数据。常用快速填充数据的方法主要有以下几种： 利用鼠标拖动填充 利用命令填充 选择列表填充 自定义序列 同时给多个单元格输入数据 同时给多个工作表中输入数据 数据有效性输入 4.3数据的编辑数据的编辑4.3.1数据的编辑数据的编辑 数据修改 数据修改有3种方法：一是选择要修改的单元格，直接重新输入；

19、二是双击要修改的单元格，在单元格中修改；三是选定要修改的单元格，然后在编辑栏内进行修改。 数据删除 数据删除有两个概念：数据清除和数据删除。数据清除是把单元格的内容清除，使之成为空单元格，数据清除后单元格本身仍留在原位置不变。数据删除是把单元格连同单元格的全部内容都从工作表中删除。 4.3数据的编辑数据的编辑4.3.1数据的编辑数据的编辑 数据移动和复制 在Excel中，复制和移动单元格或单元格区域中的数据，既可以在同一张工作表中进行，也可以在不同的工作表之间进行。 数据移动和数据复制都有可以 ：利用剪贴板移动 ；利用鼠标拖动 。 选择性粘贴是指一个单元格含有多种特性，如内容、格式、批注等，另

20、外它还可能是一个公式，含有有效规则等，数据复制时往往只须复制它的部分特性；此外复制数据的同时还可以进行算术运算、行列转置等。这些都可以通过选择性粘贴来实现。 4.3数据的编辑数据的编辑4.3.1数据的编辑数据的编辑 合并与拆分单元格 Excel表格编辑过程中，通常会合并与拆分单元格，以符合编辑表格的需求，如表格的标题等。合并与拆分单元格，常用的方法如下 ： 方法一：利用“开始”选项卡的快速合拼功能。 方法二：利用“设置单元格格式”对话框。 4.3数据的编辑数据的编辑4.3.14.3.1数据的编辑数据的编辑 单元格、行、列的插入 Excel编辑表格式，根据需要可随时插入单元格、行、列，以调整表格

21、满足实际需求。 查找和替换 查找与替换的使用方法与Word中相同，查找与替换是指从当前活动单元格开始搜索指定的内容。 给单元格增加批注 虽然每个单元格可以输入多达32000个字符，但往往不能够同时表达所有的含义，可以给单元格增加批注，来标记与说明。 4.3数据的编辑数据的编辑4.3.14.3.1数据的编辑数据的编辑 撤消操作 快速设置表格格式 通过“设置单元格格式”对话框，可以设置表格与单元格格式，Excel2010提供了更加快捷的方式。切换到“开始”选项卡，在“样式”组中包含“套用表格格式”、“单元格格式”等选项，可快速的帮用户设置选中表格或单元格格式 。 单元格名称 单元格名称是指给单元格

22、重新定义的名称。 两大功能 定位：可通过名称快速查找单元格的位置。 计算：单元格或单元格区域的名称，可以直接引用到公式中，以便直观的看到公式的含义。 4.3数据的编辑数据的编辑4.3.24.3.2拆分或冻结窗格拆分或冻结窗格 窗格是文档窗口的一部分，以垂直或水平条为界限并由此与其他部分分隔开。在实际工作中，有时需要多窗口操作，或锁定Excel工作表中的表格标题。切换到“视图”选项卡，单击“拆分”与“冻结窗格”按钮，可实现分隔窗口或标题锁定功能。 4.4数据统计与分析数据统计与分析4.4.14.4.1数据的排序、筛选与分类汇总数据的排序、筛选与分类汇总 数据的排序 （1）快速排序 （2）多条件排

23、序 （3）按笔划排序 （4）自定义排序 分类汇总 分类汇总是对数据库中的数据进行分类统计。分类汇总需要分两步完成，先排序再汇总。 首先对数据按分类的字段排序， 其次执行分类汇总命令. 4.4数据统计与分析数据统计与分析4.4.14.4.1数据的排序、筛选数据的排序、筛选与分类汇总与分类汇总 自动筛选与高级筛选 （1）自动筛选 Excel2010使用自动筛选来筛选数据，可以快速、方便地查找和使用单元格区域数据的子集。 执行自动筛选操作，数据清单中必须有列标号。 （2）自定义自动筛选 自动筛选完成后，若不能达到所需的要求，则可选择“自定义筛选”命令，实现用自定义条件筛选所需的数据。 4.4数据统计

24、与分析数据统计与分析4.4.14.4.1数据的排序、筛选与分类汇总数据的排序、筛选与分类汇总 自动筛选与高级筛选 （3）高级筛选 执行高级筛选命令，数据清单必须有列标题，同时需要建立条件区域来设置复杂的筛选条件。 条件区域 在使用“高级筛选”命令之前，必须指定一个条件区域，设置出符合条件的行或列，以定义筛选条件。 条件区域至少有两行组成，第一行为字段名，从第二行起输入查找条件。条件区域中，若有多个条件（复合条件），需要在各条件间进行逻辑运算，其中包括“与”运算和“或”运算。 4.4数据统计与分析数据统计与分析4.4.14.4.1数据的排序、筛选与分类汇总数据的排序、筛选与分类汇总 “与”运算

25、即多个条件表达式中若有一个条件为假，则其值为假。在条件区域输入时，“与”运算的条件表达式要放置在同一行。 “或”运算 即多个条件表达式中若有一个条件为真，则其值为真。在条件区域输入时，“或”运算的条件表达式要放置在不同行。 4.4数据统计与分析数据统计与分析4.4.14.4.1数据的排序、筛选与分类汇总数据的排序、筛选与分类汇总 应用高级筛选 清除数据清单的筛选 对于不再需要的筛选操作，可以将其功能清除，恢复数据清单原来的状态。 4.4数据统计与分析数据统计与分析4.4.2 4.4.2 数据透视表数据透视表 数据透视表是一种可以快速汇总大量数据的交互式方法。使用数据透视表可以深入分析数值数据，

26、它可以同时灵活变换三组需要统计的字段对一组数值进行分析和统计，统计可以是求和、平均值、计数、最大、最小等。 数据透视表建立:单击“插入”“数据透视表”按钮，通过操作向导来完成创建任务，最容易出错的是“数据透视表字段列表”中“列”与“行”标签、统计“数值”项的选择与“值字段设置”。 4.4数据统计与分析数据统计与分析4.4.34.4.3常用函数的使用常用函数的使用 条件分支IF 函数 IF函数用于判断条件表达式（logical_test）是否成 立 ， 同 时 返 回 指 定 的 值 。 函 数 格 式 为 ： I F (logical_test, value_if_true, value_if_false) 频率分布统计函数 Frequency 频率分布统计函数 Frequency用于计算数值在某个区域内的出现频率，然后返回一个垂直数组。函数格式为：FREQUENCY(data_array, bins_array) 排位函数RANK 排位函数RANK，返回一个数字在数字列表中的排位。数字的排位是其大小与列表中其他值的比值，函数的