《数据整理与汇总》PPT课件.ppt

经济与管理应用软件,主讲教师 钟海,数据整理与汇总,本章学习目标,1、掌握数字与汉字排序的区别 2、掌握EXCEL排序的方法 3、理解条件区域 4、掌握数据筛选的方法 5、掌握工作表数据的分类汇总方法 6、掌握数据透视表的功能及用法,人们在分析与决策时往往首先面对的是大量原始数据，这些杂乱无章的数据使我们不能直接观察到现象的本质，因此需要对数据进行加工、整理、统计，以使现象的本质及其规则性呈现出来。 Excel的数据整理的基本工具主要包括： 数据排序 分类汇总 数据透视表进行数据整理 筛选 统计图 直方图分析工具,数据清单结构 数据清单是指工作表中包含相关数据的数据行列。 数据清单可以作为数据库使用，其中行表示记录，列表示字段。 数据清单的第一行为字段名称、变量名称、或标志。 字段名称下面的行数据称为观察值、记录，每一个单格中包含了一个变量所发生的数值。,1 数据排序,1、排序规则 数字排序规则 字母排序规则 逻辑值排序规则 汉字排序规则 汉语拼音序 按笔画排序 2、排序方式 升序 降序,3、排序案例 【例5.1】某班某次期末考试成绩如图（a）所示，已经计算出了每位同学的平均分，现需得出一个名次表，即从高分到低分的成绩表，以便为前3名同学发奖学金。,按日期排序 日期数据本质是数字，1900-1-1为数字1,按汉字笔画排序,自定义排序 有时需要按人为指定的次序对某此资料排序。 【例】某大学有7个学院，每个学院的基本情况如图所示。,要求按以下次序排序此表：计算机学院、管理学院、通信学院、财务学院、外语学院、政法学院、中文学院。,方法 建立自定义序列 在排序对话 框中，通过“选项”指定建立的序列为排序依据。,2 数据筛选,1、概念 数据筛选就是将数据表中所有不满足条件的记录行暂时隐藏起来，只显示那些满足条件的数据行 。 2、Excel的数据筛选方式 1、自动筛选 自动筛选提供了快速查找工作表数据的功能，只需通过简单的操作，就能够筛选出需要的数据 。 2、高级筛选 高级筛选能够完成自动筛选所不能实现的复杂筛选。,3、自动筛选 案例 某公司的人员档案是按招聘报到的先后次序建立的，到目前为止，该公司的人员档案如下图所示。公司的管理人员可能需要这样的信息： （1）已婚人员的名单。 （2）各部门的人员档案信息。 （3）工资高于1 200的人员有哪些。 （4）奖金高于1000的人员有哪些。 （5）各种学历的人员档案 如此等等,选择“数据”|“筛选”|“自动筛选”，Excel会显示出自动筛选标志。可借此完成各种自动筛选！,原数据表,具有自动筛标记的数据表,自动筛选的结果示例筛选出运维部的工作人员。(筛选后，箭头变为蓝色）,4.自定义筛选 工资高于1 200的人员有哪些。 年龄介于20-40的人有哪些。,5.高级筛选 案例：监考表 筛选出某教师的监考安排。 这种情况用自动筛选就达不到要求了。因为教师的姓名分布在不同的字段。自动筛选之间是逻辑并的关系。,高级筛选的条件区域 的类型 （1）“或”条件 （2）“与”条件 （3）多列的“或”条件 （4） “或”、“与”复合条件 注意事项：条件区域不要与数据区域放在同一行，以免被隐藏。,条件区域的构建方法 条件区域可放在数据列表的上方或右侧、左侧。一般放在上方，以免筛选后被隐藏起来。,高级筛选案例 查看下表中“运维部”和“信息资源部”人员中，工资高于1000且奖金高于500元的有哪些。,使用计算条件的高级筛选 什么是计算条件 条件是公式或函数，用原始数据计算出来。 计算条件的构造方法 计算条件中的标题可以是任何文本或都是空白，但不能与数据清单中的任一列标相同，这一点正好与前面讨论的条件区域相反。 必须以绝对引用的方式引用数据清单或数据库之外的单元格。 必须以相对引用的方式引用数据清单或数据库中之内的单元格。,基于计算条件的高级筛选案例 某商场有2月1号到8号的销售记录如图的A2:F10单元格区域所示，现在要找出销售额高于平均值的销售记录。,应用：值的计算,值用来表示股票（或投资组合）相对与市场指数的波动程度。 贝塔值采用回归法计算，将整个市场波动带来的风险确定为1。当某项资产的价格波动与整个市场波动一致时，其贝塔值也等于1；如果价格波动幅度大于整个市场，其贝塔值则大于1；如果价格波动小于市场波动，其贝塔值便小于1。,计算公式： 其中：ri-股票i的收益率 rm-市场指数的收益率 - ri与rm的协方差 m2-股票价格指数的方差,协方差函数COVAR(array1,array2) Array1 第一个所含数据为整数的单元格区域。 Array2 第二个所含数据为整数的单元格区域。 方差函数（总体） VARP(number1,number2,.) Number1, number2, . 为对应于样本总体的 1 到 30 个参数。,3 数据的分类与汇总,1、概述 分类汇总就是对工作表中的某类的数据进行分类统计。 分类汇总的意义：一张无序表单，统计相关数据。 它通过折叠或展开原工作表中的行数据及汇总结果，从汇总和明细两种角度显示数据，可以快捷地创建各种汇总报告。分类汇总的数据折叠层次可达8层。 2、分类汇总能够完成以下事性 在数据表中显示一组或多组数据的分类汇总及总和。 在分组数据上完成不同的计算，如求和、统计个数、求平均数、求最大（最小值）等。,案例 右图是某公司的职工工资表。 完成以下事情： 每部门有多少人； 每部门平均工资； 哪个部门的总评奖金最高， 应分发给各部门的总金额各是多少,通过函数能够完成这些事件，但分类汇总简单多了。,3、分类汇总的准备工作 分类汇总表的每个数据列(字段)都有列标题（字段名） 必须对分类字段所在的列排序。 排序的列标题称为分类汇总关键字，在进行分类汇总时，只能指定排序后的列标题为汇总关键字。,按部门排序后的工作表,4、建立分类汇总 选择“数据”|“分类汇总”菜单。,1、指定分类字段,2、指定汇总方式,3、指定汇总字段,4、指定汇总结果的保存方式,5、汇总结果的保存方式 替换当前分类汇总。选择这种方式时，最后一次的汇总会取代以前的分类汇总。 每组数据分页。选择这种方式时，各种不同的分类数据将被分页保存。 汇总结果显示在数据下方。选择这种方式时，原数据的下方会显示汇总计算的结果。 上述3种方式可同时选中。Excel的默认选择是第1和第3项。,6、分类汇总结果示例 表格的左侧是分级按钮，可增减表格的显示范围。,7、多次分类汇总 Excel可以对同一分类进行多重汇总，若要在同一汇总表中显示两个以上的汇总数据，只需对同一数据清单进行两次不同的汇总运算。第二次分类汇总在第一次的汇总结果上进行。 在前面的汇总结果基础上，统计每部门的人数。,8、删除分类汇总 对于刚建立的分类汇总，如果要将其恢复为原始数据表，可以选择“编辑”中的“撤消”菜单项取消分类汇总，或单击撤消按钮。 但若在建立分类汇总之后，又进行了其他操作，上述方法就无效了，可按如下方法删除分类汇总。 （1）单击分类汇总表中的任一单元格。 （2）选择“数据”|“分类汇总”菜单项 （3）单击“分类汇总”对话框中的“全部删除”按钮。,4 数据透视表,数据透视表的功能 数据透视表不仅能够改变数据表的行、列布局，而且能够快速汇总大量数据。也能够基于原数据表创建数据分组，对建立的分组进行汇总统计。,2、源数据表与数据透视表的对应关系,3、案例 假设某电视销售商场有6位职工，分别是劳得诺、令狐冲、陆大安、任我行、韦小宝、向问天，销售的电视产品有TCL、长虹、康佳、创维、熊猫几种品牌。每天的销售数据都记录在Excel的一个工作表中，如图所示，该数据表有300多行数据 。,完成以下统计分析工作 统计各种品牌的电视的总销售额； 统计每位职工的总销售额； 统计每位职工销售的各种类型的电视机的销售额； 统计各种运输方式的总数。 数据透视表能够很方便地完成这些工作。,单击“数据”菜单中的“数据透视表和图表报告”选项，Excel弹出数据透视表向导对话框如图所示。,上一页,下一页,返回本节首页,4、建立数据透视表的过程,根据向导要求，第1步是指定“数据源类型”和确定所需创建的“报表类型”。选择“数据清单或数据库”作为数据源，选择“数据透视表”作为报表类型。最后单击“下一步”。,上一页,下一页,返回本节首页,第2步是确定数据区域，单击“下一步”按钮，向导给出第3步操作对话框窗口如图所示。 第3步确定数据透视表的位置。选择现有工作表，并利用鼠标将数据透视表的位置确定在E1单元格中，单击“完成”按钮，可以结束操作。,上一页,下一页,返回本节首页,按向导提示做完第一、二步。在最后的第三步向导对话框中，选择“布局”按钮。会见到如图所示的对话框。,其中 行 拖放到行中的数据字段，该字段中的第一个数据项将占据透视表的一行。 列 与行相对应，放置在列中的字段，该字段中的每个项将占一列。 页 行和列相当于X轴和Y轴，由它们确定一个二维表格，页则相当于Z轴(第三维)。拖放在页中的字段，Excel将按该字段的数据项对透视表进行分页。 数据 进行计数或汇总的字段名称。,两种数据透视表生成方式： 1.在向导中进行布局。 2.在生成的空白透视表中布局。 下图为 在向导中进行布局。,也可在向导-布局 中不做任何操作，直接按“确定”，在工作表中会生成一个空白的数据透视表布局区，数据透视表字段列表、数据透视表工具栏三个组件。然后将字段列表中的字段拖入数据透视表布局区。 如果数据透视表字段列表没显现，可点击数据透视表布局区或数据透视表工具栏中最右边的按钮。,统计各销售员销售数据的透视表,双击任一单元格，会得到这些数据的来源明细数据,双击上表E5单元格得到的明细数据表,利用页字段创建分页显示,页字段,5 筛选数据 增加或减