Excel数据分析高级应用-数据分析.pdf

《Excel数据分析高级应用-数据分析.pdf》由会员分享，可在线阅读，更多相关《Excel数据分析高级应用-数据分析.pdf（62页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、1.认识Excel 2.如何设计表格 3.函数的使用 4.图表的制作 5.数据分析 （1 1）排序）排序 （2 2）数据筛选）数据筛选 （3 3）数据分类汇总）数据分类汇总 （4 4）数据透视表）数据透视表 （5 5）合并计算）合并计算 （1 1）排序）排序 （2 2）数据筛选）数据筛选 （3 3）数据分类汇总）数据分类汇总 （4 4）数据透视表）数据透视表 （5 5）合并计算）合并计算 1 1、排序规则、排序规则 数字排序规则 字母排序规则 逻辑值排序规则 汉字排序规则 汉语拼音序 按笔画排序 2 2、排序方式、排序方式 升序 降序 排序案例排序案例 某班某次期末考试成绩如图（a）所示，已经

2、计算出了每位同学的平均分，现需得出一个名次表，即从高分到低分的成绩表，以便为前3名同学发奖学金。 按日期排序 按汉字笔画排序 1、选中要排序的数、选中要排序的数据区域后据区域后,单击单击”排排序和筛选”序和筛选”按钮按钮 2、单击、单击”自定义排自定义排序”序”按钮，弹出按钮，弹出“排序排序”对话框”对话框 3、单击、单击”选项”选项”按按钮，弹出“钮，弹出“选项选项”对话框对话框 3、指定、指定”笔画序”笔画序” 自定义排序 有时需要按人为指定的次序对某此资料排序。 某大学有7个学院，每个学院的基本情况如图所示。 要求按以下次序排要求按以下次序排序此表：计算机学序此表：计算机学院、管理学院、

3、通院、管理学院、通信学院、财务学院、信学院、财务学院、外语学院、政法学外语学院、政法学院、中文学院。院、中文学院。 方法 单击Office 按钮 “文件”“Excel选项”“高级”“编辑自定义列表”，Excel会弹出“选项”对话框 按前面的方式显示出“排序”对话 框中，通过“序序”下面的“自定义序列”指定建立的序列为排序依据。 在此输入自在此输入自定义的排序定义的排序序列序列 多关键字排序多关键字排序 就是对数据表中的数据按两个或两个以上的关键字进行排序。多关键字排序可使数据在第一关键字相同的情况下，按第二关键字排序，在第一、第二关键字都相同的情况下，数据按第三关键字有序，其余的以此类推。 在

4、Excel 2007中，最多允许有64个排序关键字，但不管有多少关键字，排序之后的数据总是按第一关键字有序的。 2004年某杂货店各雇员的销售数据如图（a）的A1:F7所示，以第一季度为第一关键字、第二季度为第二关键字、第三季度为第三关键字的排序结果如图（b）的A9:F15区域所示，排序的方式是递增。 1、源数据表、源数据表 2、单击、单击“开始”“开始”“排“排序和筛选”序和筛选”“自定义排“自定义排序”序”命令，弹出“排序”命令，弹出“排序”对话框对话框 3、依次指定排序、依次指定排序关键字关键字 3、排序结果、排序结果 （1 1）排序）排序 （2 2）数据筛选）数据筛选 （3 3）数据分

5、类汇总）数据分类汇总 （4 4）数据透视表）数据透视表 （5 5）合并计算）合并计算 1、概念、概念 数据筛选就是将数据表中所有不满足条件的记数据筛选就是将数据表中所有不满足条件的记录行暂时隐藏起来，只显示那些满足条件的数录行暂时隐藏起来，只显示那些满足条件的数据行据行 。 2、Excel的数据筛选方式的数据筛选方式 1、自动筛选、自动筛选 自动筛选提供了快速查找工作表数据的功能，只需通过简自动筛选提供了快速查找工作表数据的功能，只需通过简单的操作，就能够筛选出需要的数据单的操作，就能够筛选出需要的数据 。 2、高级筛选、高级筛选 高级筛选能够完成自动筛选所不能实现的复杂筛选。高级筛选能够完成

6、自动筛选所不能实现的复杂筛选。 某公司的人员档案是按招聘报到的先后次序建立的，如下图某公司的人员档案是按招聘报到的先后次序建立的，如下图所示。可以看出，图中的档案比较混乱，公司的管理人员所示。可以看出，图中的档案比较混乱，公司的管理人员可能需要这样的信息：已婚人员的名单，各部门的人员档可能需要这样的信息：已婚人员的名单，各部门的人员档案信息，工资高于案信息，工资高于1 200的人员有哪些，奖金高于的人员有哪些，奖金高于1000的的人员有哪些，各种学历的人员档案人员有哪些，各种学历的人员档案 （1）已婚人员的名单。）已婚人员的名单。 （2）各部门的人员档案信息。）各部门的人员档案信息。 （3）工

7、资高于）工资高于1 200的人员有哪些。的人员有哪些。 （4）奖金高于）奖金高于1000的人员有哪些。的人员有哪些。 （5）各种学历的人员档案）各种学历的人员档案 如此等等如此等等 原数据原数据表表 具有自具有自动筛标动筛标记的数记的数据表据表 单击单击“数据数据”|“筛选筛选”，Excel会在表格的标题边显示会在表格的标题边显示出自动筛选标志。可借此完成各种自动筛选！出自动筛选标志。可借此完成各种自动筛选！ 如果筛选关键字对应的列是数值型数据，可通过“数字筛选”命令指定自定义筛选条件，实现灵活的数据查询。 在下图所示的职工档案表中，筛选年龄最大的3名职工。 1、单击、单击“数“数据”！“筛据

8、”！“筛选”选”为数据表为数据表显示出自动筛选显示出自动筛选标志标志 2、单击、单击“年龄”“年龄”右边的自动右边的自动筛选下拉箭筛选下拉箭头头 3、单击、单击“10个最大的个最大的值”，值”，弹出对弹出对话框话框 4、将、将“10”改为改为3，即可即可筛选出年龄最筛选出年龄最大的大的3位员工！位员工！ 1、指定工作表的、指定工作表的“筛选筛选”标志”标志 2、单击“工资、单击“工资筛筛选选”中的“”中的“大于大于”命令命令 3、在弹出的对话框、在弹出的对话框中输入中输入工资数据工资数据 条件区域的构建方法条件区域的构建方法 条件条件“与筛选与筛选” 查看下图中查看下图中“运维部运维部”工资和

9、奖金都高于工资和奖金都高于500元的人员。元的人员。 1、单击“高级高级”按钮，弹出高级筛选设置对话框 2、指定筛选结果、指定筛选结果的保存方式。的保存方式。 2、指定筛选条件、指定筛选条件和数据区域。和数据区域。 基于计算条件的高级筛选案例 某商场2003年2月1号到8号的销售记录如图7.18的区域A2:F10所示，找出其中销售额高于平均值的销售记录。 （1 1）排序）排序 （2 2）数据筛选）数据筛选 （3 3）数据分类汇总）数据分类汇总 （4 4）数据透视表）数据透视表 （5 5）合并计算）合并计算 1、概述 分类汇总就是对工作表中指定的行或列中的数据进行汇总统计，它通过折叠或展开原工作

10、表中的行、列数据及汇总结果，从汇总和明细两种角度显示数据，可以快捷地创建各种汇总报告。分类汇总的数据折叠层次可达8层。 2、分类汇总能够完成以下事性 在数据表中显示一组或多组数据的分类汇总及总和。 在分组数据上完成不同的计算，如求和、统计个数、求平均数、求最大（最小值）等。 案例 右图是某公司的职工工资表。 完成以下事情： 每部门有多少人； 每部门平均工资； 应分发给各部门的总金额各是多少 通过函数能够完成通过函数能够完成这些事件，但分类这些事件，但分类汇总简单多了。汇总简单多了。 3、分类汇总的准备工作 分类汇总表的每个数据列都有列标题； 必须对要进行分类汇总的列排序。 排序的列标题称为分类

11、汇总关键字，在进行分类汇总时，只能指定排序后的列标题为汇总关键字。 按部门按部门排序后排序后的工作的工作表表 4、建立分类汇总 单击“数据”选项卡中的“分类汇总”命令,显示“分类汇总”对话框。 1 1、指定分类字段、指定分类字段 2 2、指定汇总方式、指定汇总方式 3 3、指定汇总字段、指定汇总字段 4 4、指定汇总结果、指定汇总结果的保存方式的保存方式 5、汇总结果的保存方式 1.替换当前分类汇总。选择这种方式时，最后一次的汇总会取代以前的分类汇总。 2.每组数据分页。选择这种方式时，各种不同的分类数据将被分页保存。 3.汇总结果显示在数据下方。选择这种方式时，原数据的下方会显示汇总计算的结

12、果。 上述3种方式可同时选中。Excel的默认选择是第1和第3项。 6、分类汇总结果示例 7、多次分类汇总 Excel可以对同一分类进行多重汇总，若要在同一汇总表中显示两个以上的汇总数据，只需对同一数据清单进行两次不同的汇总运算。第二次分类汇总在第一次的汇总结果上进行。 在前面的汇总结果基础上，统计每部门的人数。 1、嵌套分类汇总 在一个已经建立了分类汇总的汇总表中再进行另一种分类汇总，两次的分类汇总关键字不同，这种分类汇总方式称为嵌套分类汇总。 2、嵌套分类汇总的排序方式 建立嵌套分类汇总的前提仍然是要对每个分类汇总关键字排序。第一级汇总关键字应该是排序的第一关键字，第二级汇总关键字应该是第

13、二排序关键字，其余以此类推。 有几层嵌套汇总就需要进行几次分类汇总操作，第二次汇总在第一次的结果集上操作，第三次在第二次的结果是操作，其余以此类推。在一个已经建立了分类汇总的汇总表中再进行另一种分类汇总，两次的分类汇总关键字不同，这种分类汇总方式称为嵌套分类汇总。 建立嵌套分类汇总的前提仍然是要对每个分类汇总关键字排序。第一级汇总关键字应该是排序的第一关键字，第二级汇总关键字应该是第二排序关键字，其余以此类推。 8、删除分类汇总 对于刚建立的分类汇总，如果要将其恢复为原始数据表，可以选择“编辑”中的“撤消”菜单项取消分类汇总，或单击撤消按钮。 但若在建立分类汇总之后，又进行了其他操作，上述方法

14、就无效了，可按如下方法删除分类汇总。 （1）单击分类汇总表中的任一单元格。 （2）选择“数据”选项卡中的“分类汇总”命令 （3）单击“分类汇总”对话框中的“全部删除”按钮。 （1 1）排序）排序 （2 2）数据筛选）数据筛选 （3 3）数据分类汇总）数据分类汇总 （4 4）数据透视表）数据透视表 （5 5）合并计算）合并计算 1.数据透视表的功能 对数值数据进行分类汇总和聚合；展开或折叠要关对数值数据进行分类汇总和聚合；展开或折叠要关注结果的数据级别，查看感兴趣区域摘要数据的明细；注结果的数据级别，查看感兴趣区域摘要数据的明细；将行移动到列或将列移动到行（或“透视”），以查将行移动到列或将列移

15、动到行（或“透视”），以查看源数据的不同汇总；对最有用和最关注的数据子集看源数据的不同汇总；对最有用和最关注的数据子集进行筛选、排序、分组和有条件地设置格式，突出显示进行筛选、排序、分组和有条件地设置格式，突出显示重要信息；提供简明、带有批注的联机报表或打印报重要信息；提供简明、带有批注的联机报表或打印报表表 源数据 透视数据 步骤一：先选中数据表中的任一单元格，然后选择：插入-数据透视表，弹出创建数据透视表的对话框 步骤二：将“月份”字段拖拽到“列标签”框中，“客户”字段拖拽到“行标签”框中，“金额”拖拽到“数值”框中 案例案例假设某电视销售商场有6位职工，分别是劳得诺、令狐冲、陆大安、任我

16、行、韦小宝、向问天，销售的电视产品有TCL、长虹、康佳、创维、熊猫几种品牌。每天的销售数据都记录在Excel的一个工作表中，如图所示，该数据表有300多行数据 。 完成以下统计分析工作 统计各种品牌的电视的总销售额； 统计每位职工的总销售额； 统计每位职工销售的各种类型的电视机的销售额； 统计各种运输方式的总数。 数据透视表能够很方便地完成这些工作。 1 1、建立源数据表后，单击、建立源数据表后，单击“插入插入”选项卡中的选项卡中的“数据透视表数据透视表”弹出弹出“创建数据透视表创建数据透视表”对话框对话框 1 1、 “创建数据创建数据透视表透视表”对话框对话框 1 1、 指定数据透视表指定数

17、据透视表的数据区域后，单击的数据区域后，单击“确定确定”按钮，将按钮，将弹出数据透视表的布弹出数据透视表的布局设置对话框局设置对话框 2 2、根据第、根据第1 1步步设置创建的数设置创建的数据透视表据透视表 3 3、用鼠标将数据、用鼠标将数据字段拖放到行、字段拖放到行、列标签和数值汇列标签和数值汇总对应的文本框总对应的文本框中中 其中数据透视表布局中的内容其中数据透视表布局中的内容 行标签行标签 拖放到行中的数据字段，该字段中的第一个数据项拖放到行中的数据字段，该字段中的第一个数据项将占据透视表的一行。将占据透视表的一行。 列标签列标签 与行相对应，放置在列中的字段，该字段中的每个与行相对应，

18、放置在列中的字段，该字段中的每个项将占一列。项将占一列。 报表筛选报表筛选 行和列相当于行和列相当于X轴和轴和Y轴，由它们确定一个二维表格，轴，由它们确定一个二维表格，页则相当于页则相当于Z轴。拖放在页中的字段，轴。拖放在页中的字段，Excel将按该将按该字段的数据项对透视表进行分页。字段的数据项对透视表进行分页。 数值数值 统计各销售员销售数据的透视表布局 统计各销售员销售数据的透视表 双击任一单元双击任一单元格，会得到这格，会得到这些数据的来源些数据的来源明细数据明细数据 双击上表E5单元格得到的明细数据表 利用报表筛选（即页字段）创建分页显示 页字段页字段 用鼠标单击它，从下拉列用鼠标单

19、击它，从下拉列表中选择某个日期，可以表中选择某个日期，可以查看此日期的销售数据查看此日期的销售数据 使用多重数据字段建立透视表 在前面所讨论的电视销售数据表中，要查看每位职工销售了多少种产品，每种产品的数量各是多少，而且还要查看其销售的产品中，每种运输方式各运输了多少数量。 在行标签中放置了在行标签中放置了”销销售员姓名售员姓名“和和”运运输方式输方式“两字段两字段 修改数据透视表的汇总函数 数据透视表中使用的透视函数称为汇总函数，在默认情况下，数据透视表采用SUM函数对其中的数值项进行汇总，用COUNT函数对表中的文本类型字段项进行计数。 Excel提供了许多汇总函数，如下图所示。 单击单击

20、”求和项求和项“的下拉箭头，从的下拉箭头，从弹出的菜单中选择弹出的菜单中选择”值字段设置值字段设置“，弹出值字段设置对弹出值字段设置对话框话框 在此选择需要的在此选择需要的”汇总函数汇总函数“ 案例下图是某公司各部门2008年预算与实际支出的数据，试比较分析预算与实际发生额的差异与差异率。 比较差异率的数据透视表 步骤一：调出“数据透视表和数据透视图向导”。 先按“Alt”，放开后按“D”，放开后再按“P”,会弹出如右所示的向导，选择“多重合并计算数据区域” 单击“下一步” 步骤二：指定所需的页字段数目。 选择“自定义页字段”，单击“下一步” 步骤三：定义页“预算”字段区域。选择预算数据区域，

21、单击“添加”按钮加到“所有区域”列表框中，在字段1中输入“预算” 步骤四：定义页“实际”字段区域。选择实际数据区域，单击“添加”按钮加到“所有区域”列表框中，在字段1中输入“实际” 步骤五：添加计算项。先选择需添加计算项的“页1”下的 “预算”或“实际”单元格，然后选择：选项-计算-域、项目和集-计算项，弹出如右所示的对话框，在“名称”中输入计算项的名称，“公式”中输入计算公式 步骤六：调整数据透视表布局。 排序 去掉求和项 （1 1）排序）排序 （2 2）数据筛选）数据筛选 （3 3）数据分类汇总）数据分类汇总 （4 4）数据透视表）数据透视表 （5 5）合并计算）合并计算 1、概念、概念

22、所谓合并计算，就是对所谓合并计算，就是对来源于同一工作簿的不来源于同一工作簿的不同工作表，或不同工作同工作表，或不同工作簿中的数据表进行合并簿中的数据表进行合并计算。计算。Excel支持将不支持将不超过超过255个工作表中的个工作表中的信息收集到一个主工作信息收集到一个主工作表中，支持的工作表可表中，支持的工作表可在同一个工作簿中，也在同一个工作簿中，也可来源于不同的工作簿。可来源于不同的工作簿。 案例案例 某电视机厂生产某电视机厂生产21英寸、英寸、25英寸、英寸、29英寸及英寸及34英寸英寸几种规格的电视产品，主要销售于西南地区。该厂每几种规格的电视产品，主要销售于西南地区。该厂每个季度进行一次销售统计，每次统计分析保存在一个个季度进行一次销售统计，每次统计分析保存在一个独立的工作簿中独立的工作簿中 建立合并计算建立合并计算 单击单击“数据数据”选项卡选项卡“数据工具数据工具”组中的组中的“合并计算合并计算”按钮，按钮，弹出弹出 “合并计算合并计算”对话对话框。框。 合并计算的结果合并计算的结果