excel-数据透视表的巧用

《excel-数据透视表的巧用》由会员分享，可在线阅读，更多相关《excel-数据透视表的巧用（111页珍藏版）》请在金锄头文库上搜索。

1、Excel与数据处理,第五章 数据管理与数据透视表（精彩的讲解）,本章教学目的与要求,1、掌握工作表数据的排序 2、掌握工作表数据的自动筛选 3、掌握工作表数据的高级筛选 4、掌握工作表数据的分类汇总 5、掌握数据透视表的应用 6、掌握数据链接的应用 7、掌握多表数据的合并计算,本章重点、难点及学时数,重点： 1、数据的排序 2、数据的筛选 3、数据的分类汇总 4、数据透视表的应用 5、数据链接应用 6、数据的合并计算 难点： 数据的高级筛选 学时数： 12学时（上机6学时）,本章目录,5.1 数据排序 5.2 数据筛选 5.3 数据的分类与汇总 5.4 数据透视表 5.5 数据链接 5.6

2、数据合并计算 小结 思考与练习,5.1 数据排序,1.排序的规则升序：数字字符逻辑值错误值空格降序：错误值逻辑值字符数字空格 数字排序规则：最小负数到最大正数 字母排序规则：逐个对应字符相比以不相同字符大小确定字符串大小 字符顺序：09 空格 #$%?_|-+AZ 逻辑值排序规则：FALSE小于TRUE 汉字排序规则：汉语拼音排序和笔画排序,5.1 数据排序,2.排序的方法： 菜单数据排序对话框中选择关键字 选择升降序确定 数值排序 例：某班某次期末考试成绩，已经计算出每位同学的平均分，现需得出一个从高分到低分的成绩表. 见数据排序.xls数值排序 日期排序 例：某公司的工资表，要求按照日期的

3、升序进行排列.见数据排序.xls日期排序,5.1 数据排序,汉字字符排序 例：某公司的工资表，要求按照部门的降序（默认为拼音）和姓名的笔画降序进行排列.见数据排序.xls汉字字符排序,5.1 数据排序,自定义排序:按照人为指定的次序进行排序. 例：某大学有7个学院，要求按以下次序排序此表：计算机学院、管理学院、通信学院、财务学院、外语学院、政法学院、中文学院。 见数据排序.xls自定义排序 方法： 菜单工具选项对话框中选择自定义序列，按照要求的排序次序设置一个自定义序列; 菜单数据排序对话框中选择选项按钮选择排序序列确定,5.1 数据排序,多重排序:对数据表中的数据按照两个或以上的关键字进行排

4、序。在第一关键字相同的情况下按照第二关键字排序，在第二关键字相同的情况下按照第三关键字排序. 例：有2004年某杂货店各雇员的销售数据表，要求以第一季度为第一关键字，第二季度为第二关键字，第三季度为第三关键字进行排序。 见数据排序.xls多重排序 方法：菜单数据排序对话框中选择主、次、第三关键字选择升降序确定,目录,5.2 数据筛选,概述数据筛选就是将数据表中所有不满足条件的记录行暂时隐藏起来，只显示那些满足条件的数据行 。 筛选的方式 自动筛选 自动筛选提供了快速查找工作表数据的功能，只需通过简单的操作，就能够筛选出需要的数据 。 高级筛选 高级筛选能够完成自动筛选所不能实现的多条件复杂筛选

5、。 筛选的方法选择菜单“数据”“筛选”“自动筛选”或 “高级筛选”命令,5.2 数据筛选,1.自动筛选选择菜单“数据”“筛选”“自动筛选”命令， Excel会显示出自动筛选标志,即下拉列表标志。 筛选的下拉列表中各项列表的意义： 前10个：只显示数据表中满足指定条件的前若干个数据行。（可用于选择最大或最小的若干个数据行） 自定义：表示用户自己定义筛选条件。并在“自定义自动筛选方式”对话框中键入条件。 全部：将工作表恢复到筛选前的状态，显示所有数据行。注： “自定义自动筛选方式”对话框中运算符的解释： 始于：表示从左边截取若干字符如：学历始于大，即首字符为大，大学，大专等 止于：表示截取最后若干

6、字符如：学历止于士，即末字符为士，硕士、博士等 包含：其中含有给定的字符串。,5.2 数据筛选,例：某公司的人员档案是按招聘报到的先后次序建立的， 见数据筛选.xls自动筛选,公司的管理人员可能需要这样的信息： （1）已婚人员的名单。 （2）各部门的人员档案信息。 （3）工资高于1200的人员信息。 （4）奖金高于1000的人员信息。 （5）各种学历的人员分类档案如此等等,5.2 数据筛选,2.高级筛选 条件区域的类型 “或”条件：不同行上具有多个条件 “与”条件：同行上具有多个条件 条件区域的构建 条件区域首行必须包含与数据清单中完全相同的列标; 首行之下为筛选条件 见数据筛选.xls条件例

7、,5.2 数据筛选,高级筛选操作 为高级筛选建立条件区域; 单击数据清单中任一非空单元格，选择“数据”菜单中“筛选”子菜单中的“高级筛选”命令; 在“高级筛选对话框”中对“数据区域”中的单元格区域进行检验，然后在“条件区域”输入框中输入筛选条件所在的单元格区域; 上述操作后可在数据清单中显示筛选结果，若要在其他区域显示筛选结果，只要在“高级筛选对话框”中“方式”中作选择，且给出结果区域,5.2 数据筛选,例：查看 “运维部”和“信息资源部” 工资高于1000且奖金高于500元的人员。 见数据筛选.xls高级筛选,5.2 数据筛选,使用计算条件的高级筛选 计算条件:是包括任何测试而不是一个简单的

8、列值同常量相比较的结果如:条件例中找出比平均销售量高的销售员. 计算条件的构造方法 计算条件中的标题不能与数据清单中的任一列标相同，可以是任何文本或都是空白。 必须以绝对引用的方式引用数据清单或数据库中之外的单元格。 必须以相对引用的方式引用数据清单或数据库中之内的单元格。,5.2 数据筛选,例：某商场有2月1号到8号的销售记录如图A2:F10单元格区域所示，现在要找出销售额高于中间值的销售记录。见数据筛选.xls计算条件筛选,5.2 数据筛选,MEDIAN函数 功能：返回给定数值集合的中位数。中位数是在一组数据中居于中间的数。即在这组数据中有一半的数据比它大，有一半的数据比它小。 格式：ME

9、DIAN(number1,number2, .) Number1, number2,. 是需要找出中位数的 1 到 30 个数字参数。 说明： 参数必须是数字、名称、数组或包含数字的引用。 如果数组或引用参数中包含有文字、逻辑值或空白单元格，则忽略这些值，但是其值为零的单元格会计算在内。 例：MEDIAN(1, 2, 3, 4, 5) 等于 3MEDIAN(1, 2, 3, 4, 5, 6) 等于 3.5，即 3 与 4 的平均值,目录,5.3 数据的分类与汇总,1.概述分类汇总是对工作表中指定的行或列中的数据进行汇总统计。通过折叠或展开原工作表中的行、列数据及汇总结果，从汇总和明细两种角度显

10、示数据，可快捷地创建各种汇总报告。分类汇总的数据折叠层次可达8层。 2. 功能在数据表中显示一组或多组数据的分类汇总数据，在分组数据上完成不同的计算，如求和、统计个数、求平均数、求最大（最小值）等。,5.3 数据的分类与汇总,3.建立分类汇总 准备工作： 分类汇总表的每个数据列都有列标题； 对要进行分类的列排序; 说明:排序的列标题称为分类汇总关键字，在进行分类汇总时，只能指定排序后的列标题为汇总关键字。 操作方法: 选择“数据”“分类汇总”菜单，打开分类汇总对话框,在对话框中完成操作。,5.3 数据的分类与汇总,5.3 数据的分类与汇总,汇总结果保存 替换当前分类汇总:最后一次的汇总会取代以

11、前的分类汇总。 每组数据分页:各种不同的分类数据将被分页保存。 汇总结果显示在数据下方:原数据的下方会显示汇总计算的结果。说明：以上3种方式可同时选中；Excel的默认选择是第1和第3项。,5.3 数据的分类与汇总,分类汇总的删除 对刚建立的分类汇总，若要恢复为原始数据表，则选择“编辑”中的“撤消”菜单项取消分类汇总，或单击撤消按钮。 若建立分类汇总之后又进行了其他操作，可按如下方法删除分类汇总： （1）单击分类汇总表中的任一单元格； （2）选择“数据”|“分类汇总”菜单项； （3）单击“分类汇总”对话框中的“全部删除”按钮。,5.3 数据的分类与汇总,例： 在职工工资表中完成以下工作：见分类

12、汇总.xls一级分类汇总1、2、3 1、各部门有多少人； 2、分发给各部门的奖金总金额； 3、各部门的平均工资。,通过函数能够完成这些事件，但分类汇总简单多了。,5.3 数据的分类与汇总,多次分类汇总 Excel可以对同一分类进行多次汇总，若要在同一汇总表中显示两个以上的汇总数据，只需对同一数据清单进行两次不同的汇总运算。第二次汇总在第一次的汇总结果上进行。 例：在汇总各部门奖金总金额的基础上，统计每部门的人数。见分类汇总.xls二级分类汇总 多重分类汇总 对多个字段进行多重排序之后分别汇总,目录,5.4 数据透视表,1.数据透视表的作用 能够改变数据表的行、列布局; 能够快速汇总大量数据;

13、能够基于原数据表创建数据分组，并对分组进行汇总统计。,5.4 数据透视表,2. 数据透视表的建立 选择“数据”“数据透视表和数据透视图”; 按向导提示做完1，2步; 在第3步中选择“版式”按钮，出现如图所示的对话框。,5.4 数据透视表,解释: 行：拖放到行中的数据字段中的每个数据项将占据透视表的一行。 列：拖放到列中的数据字段中的每个数据项将占据透视表的一列。行和列确定一个二维表格. 页：拖放在页中的字段，Excel将按该字段的数据项对透视表进行分页。 数据：进行计数或汇总的字段名称。,5.4 数据透视表,例：假设某电视销售商场有6位职工销售几种品牌电视产品,每天销售数据都记录在Excel的

14、一个工作表中.数据透视表. Xls 欲完成以下统计分析工作: 统计每位销售员的总销售量(例1)； 统计各销售员销售各类电视的数量及销售额 (例2)； 统计销售员陈诺销售TCL品牌的明细表 (例3,可从例2得到)； 统计每天各销售员对各种电视的销售数及销售额 (例4)；(试采用页行列共用方式） 统计各销售员对各种电视按不同运输方式的销售数 (例5) （试采用多重字段方式),5.4 数据透视表,例1中统计每位销售员的总销售量, 透视表布局如下图所示,5.4 数据透视表,3.修改数据透视表 显示或隐藏行、列中的数据项 在行或列字段的下拉列表条中选择或取消复选标志。 重新组织数据透视表 行、列置换 ：

15、鼠标移至行字段上按住拖动到列字段上，列方法类似。(例2) 在字段内移动个别项 ：鼠标按在要移动字段边框线上拖动到指定位置放开。 在原有行列字段上增加或减少字段 增加：单击数据透视表,在数据透视表工具栏上将所需字段拖入行或列的字段处. 减少：选择数据透视表行或列的字段名，拖到表外 例：在例2中分别增加和减去行字段：运输方式,5.4 数据透视表,修改数据透视表的概要函数 概要函数：即数据透视表中使用的透视函数。如：求和、计数、平均、百分比、最大值、标准偏差等。 操作方法： 选择数据透视表中数据字段； 在对象菜单或数据透视表工具栏中选择字段设置； 在数据透视表对话框的汇总方式列表中选择所需函数。,5

16、.4 数据透视表,修改透视表数据的显示方式 见数据透视表修改数据透视表的显示方式 操作方法: 选择数据透视表中数据字段； 在对象菜单或数据透视表工具栏中选择字段设置 在数据透视表对话框中单击选项按钮； 在数据显示方式列表中选择所需的显示方式。,以百分比显示数据,5.4 数据透视表,4.数据透视图表 概述：数据透视图表是根据数据透视表制作的图表，与数据透视表相关联，它们具有彼此对应的字段，若数据透视表中内容更改，数据透视图表中的内容也随之更改。 建立数据透视图表的方法单击数据透视表中的非空单元格，选择“插入”菜单的图表命令，或单击常用工具栏中的图表工具，Excel会自动在当前工作簿中插入一个新的数据透视图。,目录,5.5 数据链接,1.概述 链接就是指两个不同工作表或不同文档（可以是不同应用程序建立的文档，如WORD和EXCEL之间）存在的数据连接。 链接让一个工作簿可以共享另一个工作薄中的数据，可以链接单元格、单元格区域、命名公式、常量或工作表 。 链接具有以下优点： 在不同的工作簿和工作表之间，可以进行数据共享。 小工作簿比大工作簿的运行效率更高。 分布在不同地域的数据管理可以在不同工作簿中完成.通过超级链接可以远程数据采集、更新和汇总。 可以在不同的工作薄中修改、更新数据，大家可以同时工作。,