《Excel数据高效分析之道课件》由会员分享,可在线阅读,更多相关《Excel数据高效分析之道课件(70页珍藏版)》请在金锄头文库上搜索。
1、Excel高效数据分析之道让您的分析报告更有说服力Excel数据高效分析之道课件数据高效分析之道课件Excel的思考的思考Excel是什么?Excel能做什么?Excel怎样使用?Excel数据高效分析之道课件数据高效分析之道课件使用Excel的基本素养充分认识Excel是一个科学管理工具Excel是一个科学管理工具管理要用数据说话,而不是拍脑袋做决策一个有说服力报告的重点内容是数据分析表格和图表养成使用Excel的好习惯合理设计Excel表单,为日常数据管理和处理分析打好基础原始数据表格与数据处理分析表格分开保存采用合理的方法获取外部数据等等自定义Excel操作界面添加常用的操作按钮(格式按
2、钮、增大/缩小字号按钮、选择性粘贴按钮、粘贴数值按钮、等等)熟练使用常用的快捷键等等让你的Excel发挥最大的效能安装完全版(Excel 2003)加载分析工具库Excel数据高效分析之道课件数据高效分析之道课件如何让你的分析报告更有说服力如何让你的分析报告更有说服力?用数据用数据说话说话完美的分析报告完美的分析报告用图表用图表说话说话Excel数据高效分析之道课件数据高效分析之道课件本课程目标用数据说话:掌握快速制作各种统计分析报表的方法、技巧和各种应用用图表说话:掌握制作精美实用分析图表的方法、技巧和各种应用Excel数据高效分析之道课件数据高效分析之道课件课程目录第第1部分:不可忽视的基
3、础性工作部分:不可忽视的基础性工作第第2部分:快速掌握部分:快速掌握Excel公式和函数公式和函数第第3部分:让表格数据更加清晰部分:让表格数据更加清晰第第4部分:用数据说话部分:用数据说话第第5部分:用图表说话部分:用图表说话Excel数据高效分析之道课件数据高效分析之道课件第第1部分部分 标准化与规范化标准化与规范化不可忽视的基础性工作不可忽视的基础性工作ExcelExcel的思考的思考表格数据的整理与规范表格数据的整理与规范表格结构的整理与规范表格结构的整理与规范其他数据整理技巧其他数据整理技巧 Excel数据高效分析之道课件数据高效分析之道课件Excel的思考的思考Excel是什么?E
4、xcel能做什么?Excel怎样使用?Excel数据高效分析之道课件数据高效分析之道课件表格数据的整理与规范很多表格中的数据是非法的、错误的、不规范的,需要进行整理规范和标准化。正确处理日期数据正确处理文本和数字删除数据中的垃圾字符Excel数据高效分析之道课件数据高效分析之道课件正确处理日期数据Excel是如何处理日期的?日期是特殊的数字。日期永远是大于零的正整数。正确输入日期正确输入日期:2010-10-8,2010/10/8,8/oct/2010, 8-oct-2010错误的输入日期方式:2010.10.08,或者 错误日期的类型输入错误从系统导入的日期是文本格式如何把日期显示为需要的格
5、式?修改非法日期(案例01)Excel数据高效分析之道课件数据高效分析之道课件正确处理文本和数字如何将文本型数字转换为纯数字 有些情况下,从数据库导入的数字是文本型数字。文本型数字无法使用SUM等函数进行计算,需要转换为纯数字。方法1:利用智能标记方法2:利用VALUE函数方法3:利用公式(两个负号或者乘以1或除以1)方法4:利用选择性粘贴的批量修改功能(乘以1或者除以1)方法5:利用分列工具如何把数字转换为文本型数字?使用分列工具使用TEXT函数案例02 将文本型数字转化为纯数字Excel数据高效分析之道课件数据高效分析之道课件删除数据中的垃圾字符从系统中导入的数据中,可能会有很多“垃圾”字
6、符会影响到数据的处理和分析,应当予以删除。比如:删除数据中的空格删除字符中的打印不出的特殊字符(案例03)Excel数据高效分析之道课件数据高效分析之道课件表格结构的整理与规范很多表格的结构是不规范的,影响数据处理分析的准确性和效率,必须予以整理和标准化。数据分列取消合并单元格并填充数据将多表头表格整理为数据清单Excel数据高效分析之道课件数据高效分析之道课件数据分列有些情况下,从系统导入的数据是一列数据,需要根据实际情况进行分列。基本方法方法1:使用“分列”工具方法2:实用文本函数案例04 数据分列 分列前的数据分列后的数据Excel数据高效分析之道课件数据高效分析之道课件取消合并单元格并
7、填充数据对于不规范的表格,例如多表头,有合并单元格,等等,需要进行相应的整理。方法:根据情况,可以使用复制/粘贴,函数、VBA等方法。案例05 取消合并单元格并快速填充数据错误的表格正确的表格Excel数据高效分析之道课件数据高效分析之道课件将多表头表格整理为数据清单很多表格是多行表头或者多列表头,造成数据处理分析很难,需要将其整理为数据清单。案例06 表格架构的重新搭建 Excel数据高效分析之道课件数据高效分析之道课件其他数据整理技巧批量修改数据转置数据位置复制可见单元格数据 删除空行和空列批量插入空行和空列Excel数据高效分析之道课件数据高效分析之道课件第第2部分部分 武装自己武装自己
8、快速掌握快速掌握Excel公式和函数公式和函数将繁琐的加减公式变得更为简洁和高效将繁琐的加减公式变得更为简洁和高效让需要的数据迅速出现在您面前让需要的数据迅速出现在您面前合理有序制作计划合理有序制作计划把需要的信息提炼出来把需要的信息提炼出来创建高效计算公式的技巧和方法创建高效计算公式的技巧和方法Excel数据高效分析之道课件数据高效分析之道课件将繁琐的加减公式变得更为简洁和高效低效率的加减公式,既不科学,也容易出错。在很多情况下,可以使用相关的求和函数来解决。常用求和函数SUM函数:无条件求和。另类用法:可以快速对多个工作表求和可以快速对多个工作表求和SUMIF函数:单条件求和=SUMIF(
9、条件判断区域,条件值,实际求和区域)SUMIFS函数(Excel 2007新增函数):多条件求和=SUMIFS(实际求和区域,条件判断区域1,条件值1,条件判断区域2,条件值2,.)SUMPRODUCT函数:计算乘积和,可以用于多条件求和与多条件计数= SUMPRODUCT(数组1,数组2,数组3,.)案例07 高效求和汇总Excel数据高效分析之道课件数据高效分析之道课件让需要的数据迅速出现在您面前很多情况需要将需要的数据查找出来,此时有很多查找函数可以选择使用。常用的查找函数VLOOKUP函数:根据首列数据进行查询=VLOOKUP(查找依据,查找区域,指定列位置,逻辑值)MATCH函数:定
10、位函数,确定某数据的位置=MATCH(查找值,查找区域,匹配参数)INDEX函数:根据行列位置序号查找数据=INDEX(查找区域,行位置号,列位置号)注意事项:无法查找重复值不区分大小写案例08 VLOOKUP MATCH INDEX函数应用Excel数据高效分析之道课件数据高效分析之道课件合理有序制作计划怎样才能做到工作有条不紊的进行:制作计划表!使用相关的日期函数,合理日期设置显示格式,可以让您的日程管理醒目而有序!日期小知识常见日期函数及其应用(案例09)Excel数据高效分析之道课件数据高效分析之道课件把需要的信息提炼出来有时候,很多信息包含在文本文字中,需要把这些信息提取出来。可以使
11、用相关的文本函数解决问题:LEN,LENbRIGHT,LEFT,MIDFIND案例10 文本函数及其应用Excel数据高效分析之道课件数据高效分析之道课件创建高效计算公式的技巧和方法很多实际问题,需要使用多个函数,创建复杂的计算公式来解决。复杂的计算公式,就是很多函数的嵌套。创建高效计算公式的方法:仔细分析问题的本质所在确定所要使用的函数先分解公式,再综合公式必要时使用辅助列案例11 创建高效计算公式Excel数据高效分析之道课件数据高效分析之道课件第第3部分部分 让表格数据更加清晰让表格数据更加清晰使用自定义数字格式美化表格使用自定义数字格式美化表格使用条件格式标识特殊数据使用条件格式标识特
12、殊数据让庞大复杂的表格变得更加容易察看让庞大复杂的表格变得更加容易察看Excel数据高效分析之道课件数据高效分析之道课件使用自定义数字格式美化表格当数字很大时,表格既不便于查看数据,打印出的表格也很不美观。可以通过设置数字的自定义格式来美化表格。例如:将数字缩小1千倍显示将数字缩小1万倍显示将数字缩小百万倍显示将特殊的数字显示为指定的颜色案例12 自定义数字格式 Excel数据高效分析之道课件数据高效分析之道课件使用条件格式标识特殊数据利用条件格式,可以标识表格的特殊数据。比如:标识非法数据设计提前提醒模块(案例13) Excel数据高效分析之道课件数据高效分析之道课件让庞大复杂的表格变得更加
13、容易察看很多表格中,既有合计数,也有明细数,使得表格看起来很不方便。可以建立分类汇总和分级显示,让大型表格变得更加精炼案例14 创建多层次的分类汇总和分级显示Excel数据高效分析之道课件数据高效分析之道课件第第4部分:用数据说话部分:用数据说话快速汇总多个工作簿和工作表数据快速汇总多个工作簿和工作表数据数据透视表的使用技巧和实际应用数据透视表的使用技巧和实际应用Excel数据高效分析之道课件数据高效分析之道课件快速汇总多个工作簿和工作表数据快速汇总多个工作簿和工作表数据使用函数使用函数使用合并计算工具使用合并计算工具使用数据透视表使用数据透视表使用导入数据使用导入数据+SQL语句语句使用使用
14、Microsoft Query工具工具Excel数据高效分析之道课件数据高效分析之道课件使用函数使用相关的函数,可以快速汇总大量工作表数据。例如:使用SUM函数可以快速加总大量工作表使用INDIRECT函数可以动态汇总大量的个数不定的工作表案例15 动态汇总个数不定的工作表 Excel数据高效分析之道课件数据高效分析之道课件使用合并计算工具当多个工作表的结构相同时,可以使用合并计算工具快速进行合并计算,同时还可以创建分类汇总和分级显示。案例研究:案例16 快速合并多个结构相同的分报表(当前工作簿)案例17 快速合并多个结构相同的分报表(不同工作簿) Excel数据高效分析之道课件数据高效分析之
15、道课件使用数据透视表利用多重合并计算数据区域多重合并计算数据区域的数据透视表,可以快速把数十个甚至上百个工作表的数据汇总在一起,然后在此基础上进行相关的统计分析。注意:要汇总的多个工作表数据行数和列数可以不同,但从数据区域的第2列开始必须是数字。案例18 快速汇总多个工作表数据(特殊情况) 三个分公司数据汇总到一张工作表Excel数据高效分析之道课件数据高效分析之道课件使用导入数据+SQL语句一般情况下,要汇总的各个工作表数据可能含有多列文本,此时就不能采用普通的多重合并计算数据区域的数据透视表来进行汇总了。最好的解决方法:使用导入外部数据+SQL语句。案例19 快速汇总多个工作表数据(一般情
16、况) Excel数据高效分析之道课件数据高效分析之道课件使用Mrcrosofet Query工具汇总多个有关联工作表有些情况下,几个工作表中分别保存不同的数据,但他们通过一个关键列数据(比如工号)联系起来。要将这样几个相关联的工作表数据进行汇总,可使用Microsoft Query。案例20 快速汇总多个有关联的工作表数据 Excel数据高效分析之道课件数据高效分析之道课件数据透视表的使用技巧和实际应用数据透视表的基本概念数据透视表的基本概念数据透视表的类型数据透视表的类型准备工作准备工作创建数据透视表的基本方法和注意事项创建数据透视表的基本方法和注意事项设置数据透视的格式设置数据透视的格式利
17、用数据透视表快速编制各类统计分析报表利用数据透视表快速编制各类统计分析报表综合应用之综合应用之1:对比分析两个表格数据:对比分析两个表格数据综合应用之综合应用之2:两年销售统计分析:两年销售统计分析Excel数据高效分析之道课件数据高效分析之道课件数据透视表的基本概念什么是数据透视表?数据透视表能做什么?怎样灵活使用数据透视表?Excel数据高效分析之道课件数据高效分析之道课件数据透视表的类型普通区域数据透视表合并区域数据透视表外部数据源的数据透视表OLAP数据透视表Excel数据高效分析之道课件数据高效分析之道课件准备工作制作数据透视表的数据区域必须是数据清单数据区域第一行为列标题列标题不能
18、重名数据区域中不能有空行和空列数据区域中不能有合并单元格每列数据为同一种类型的数据整理数据区域修改非法日期把文本型数字转换为纯数字删除数据区域内的所有空行和空列取消合并单元格并进行填充去掉字符串前后的空格删除数字中的特殊字符删除不必要的小计行和总计行将二维表格整理为数据清单等等Excel数据高效分析之道课件数据高效分析之道课件创建数据透视表的基本方法和注意事项利用数据透视表向导(案例(案例21)按照向导步骤进行操作缺点:无法实现数据源的动态更新,除非使用动态数据区域名称解决方法:(1)利用导入数据方法;(2)利用OFFSET函数定义动态名称利用导入数据方法按照导入数据向导进行操作优点:可以实现
19、数据源的动态更新;可以在不打开其他工作簿的情况下创建数据透视表可以以数据库的数据创建数据透视表缺点:删除数据项目后,会遗留“假”项目名称利用数据库查询方法按照数据库查询向导进行操作优点:不用导入数据,就可以制作需要的报表可以有选择性地对某些符合条件的数据制作报表可以在不打开其他工作簿的情况下创建数据透视表可以以数据库的数据创建数据透视表Excel数据高效分析之道课件数据高效分析之道课件设置数据透视的的格式在数据透视表上对数据透视表进行重新布局修改字段名称设置字段的数字格式取消数据透视表的行或列汇总取消字段的分类汇总让数据透视表数据按照某一字段进行排序合并数据标志设置错误值的显示方式更新数据透视
20、表的数据Excel数据高效分析之道课件数据高效分析之道课件利用数据透视表快速编制各类统计分析报表数据透视表为我们提供了很多实用的数据统计分析工具,可以快速编制各种统计分析报表,例如:布局透视表设置字段的分类汇总方式(案例(案例21)设置字段的显示方式(案例(案例21)组合字段(案例(案例22)自定义数据透视表(案例(案例21)制作明细表快速汇总大量工作表数据Excel数据高效分析之道课件数据高效分析之道课件综合应用之1:对比分析两个表格数据两个工作表有什么不同?如何快速把两个工作表的差异数据找出来?案例23 综合应用之1:对比分析两个表格数据 Excel数据高效分析之道课件数据高效分析之道课件
21、综合应用之2:两年销售统计分析快速汇总两年数据工作表编制同比分析报表绘制同比分析图案例24 综合应用之2:销售统计分析Excel数据高效分析之道课件数据高效分析之道课件第第5部分部分 用图表说话用图表说话用图表准确表达出你的观点用图表准确表达出你的观点绘制图表基本方法及注意事项绘制图表基本方法及注意事项图表的修饰与美化图表的修饰与美化用组合图表表达更加复杂的信息用组合图表表达更加复杂的信息让图表按照您的要求灵活显示让图表按照您的要求灵活显示Excel数据高效分析之道课件数据高效分析之道课件用图表准确表达出你的观点图表类型的选用原则图表分析数据的出发点:正确分类显示数据快速转换图表数据分析的视角
22、Excel数据高效分析之道课件数据高效分析之道课件图表类型的选用原则图表类型柱形图、条形图、折线图、饼图、XY 散点图、面积图、圆环图、雷达图、曲面图、气泡图、股价图、圆柱图、圆锥图和棱锥图 选用原则和建议首先要突出重点,充分反映数据信息关注事物的本质!关注事物的本质!不同类型的图表有不同的适用场合。其次是美化图表。关于组合图表几种图表类型的组合。在某些情况下,单一的图表类型无法满足要求,需要绘制组合图表。关于动态交互图表使用动态图表,可以大大简化工作量,使得数据分析更加高效。Excel数据高效分析之道课件数据高效分析之道课件图表分析数据的出发点:正确分类显示数据以列数据或者以行数据分类显示数
23、据,分析问题据的出发点是不同的。 以地区分类:以地区分类:以产品分类:以产品分类:Excel数据高效分析之道课件数据高效分析之道课件快速转换图表数据分析的视角快速转换图表数据分析的视角,可以从不同的角度分析数据方法:在Excel 2003中使用Excel的图表工具栏在Excel 2007中使用相关命令按钮使用“源数据”对话框Excel数据高效分析之道课件数据高效分析之道课件绘制图表基本方法及注意事项绘制图表基本方法及注意事项Excel 2003的绘图方法Excel 2007的绘图方法图表的位置默认区域绘图时一个需要注意的问题X轴是日期时的问题复合饼图的调整问题Excel数据高效分析之道课件数据
24、高效分析之道课件Excel 2003的绘图方法基本方法:使用图表向导使用默认的图表案例25 绘制图表基本方法注意事项:如果使用名称绘图,就必须使用图表向导Excel数据高效分析之道课件数据高效分析之道课件Excel 2007的绘图方法基本方法:使用“插入”选项卡使用“选择数据源”对话框注意事项:如果使用名称绘图,就必须使用“选择数据源”对话框Excel数据高效分析之道课件数据高效分析之道课件图表的位置嵌入式图表适用于数据较少,需要把表格数据与图表一起查看的场合图表工作表适用于数据量较大,图表较大的场合Excel数据高效分析之道课件数据高效分析之道课件默认区域绘图时一个需要注意的问题当数据区域的
25、第1列是数字时,采用默认图表的方法绘制的图表,会把这列数据作为系列绘制在图表上。案例26 默认区域绘图的潜在问题 Excel数据高效分析之道课件数据高效分析之道课件X轴是日期时的问题如果X轴数据是日期,并且是不连续的,在绘制柱形图、条形图、折线图等时,会以连续的日期在X轴上显示。案例27 X轴是日期时的问题 Excel数据高效分析之道课件数据高效分析之道课件复合饼图的调整问题绘制复合饼图,要合理设置小饼的项目。方法:按位置调整按数值调整按比例调整案例28 复合饼图的调整问题Excel数据高效分析之道课件数据高效分析之道课件图表的修饰与美化图表的修饰与美化图表结构及主要元素打扮您的图表突出标识图
26、表的重点信息简单是美Excel数据高效分析之道课件数据高效分析之道课件图表结构及主要元素图表区绘图区数据系列分类轴数值轴(刻度、显示单位)图表标题图例数据标签网格线趋势线、高低点连线、垂直线、涨跌柱线Excel数据高效分析之道课件数据高效分析之道课件打扮您的图表美化图表的基本原则:更加便于关注事物的本质,而不是其他无关紧要的东西。简单是美设置图表各元素的格式重点关注图表区背景、绘图区背景、数据系列柱形背景等对于折线图和XY散点图,要合理设置网格线格式美化图表常犯的错误过渡修饰图表元素淡化了图表重点信息案例练习:各种常见图表的美化及效果对比案例29 图表美化效果对比Excel数据高效分析之道课件
27、数据高效分析之道课件突出图表的重点信息使用自选图形、图片等,突出重点数据,使阅读者一目了然地了解数据信息,发现企业经营存在的问题。案例30 利用自选图形突出重点 Excel数据高效分析之道课件数据高效分析之道课件简单是美要注意:图表是向别人传达信息的图表不是时装秀因此:图表的美化不应影响主体信息的表达不要给人一种五彩缤纷的感觉一句话:简单是美简单是美Excel数据高效分析之道课件数据高效分析之道课件绘制组合图表:表达更加复杂的信息绘制组合图表:表达更加复杂的信息绘制两种单位的数据系列的组合图表差异对比分析图资金流动分析图产品合格率分析图Excel数据高效分析之道课件数据高效分析之道课件绘制两种
28、单位的数据系列的组合图表两种单位的数据系列是不能绘制在同一个坐标轴上的,而必须分别绘制在主轴和次轴上。例如:销售量-销售额图表同比分析图表案例31 两轴-线柱图 Excel数据高效分析之道课件数据高效分析之道课件差异对比分析图差异对比分析图,就是实际与目标之间的差异,在图表上既显示实际数和目标数,还显示它们之间的差值。这种图表进行预算分析和目标完成率分析是非常有用的。案例32 差异对比分析图 Excel数据高效分析之道课件数据高效分析之道课件资金流动分析图利润表中各个项目究竟对净利润有何影响?通过资金流动图可以一目了然的发现问题。案例33 资金流动图 Excel数据高效分析之道课件数据高效分析
29、之道课件产品合格率分析图在图表上将那些不合格的数据点标识出来,可以更加清楚地了解产品合格率分布。案例34 产品合格率分析图实际问题研究:如何绘制标识最大值和最小值的图表?如何把那些费用超支的项目(部门)标识出来?Excel数据高效分析之道课件数据高效分析之道课件绘制动态交互图表:让图表按照你的要求灵活显示了解窗体控件及其使用方法制作动态交互图表的基本原理和方法案例研究:显示最新几个数据的图表Excel数据高效分析之道课件数据高效分析之道课件了解窗体控件及其使用方法绘制动态图表,要使用窗体控件(Excel 2003)或者表单控件(Excel 2007)。主要窗体控件有:选项按钮组合框列表框复选框
30、数值钮滚动条窗体控件都必须与单元格链接起来,保存其返回值。窗体控件的返回值依控件不同而不同Excel数据高效分析之道课件数据高效分析之道课件制作动态交互图表的基本原理和方法制作动态交互图表,可以更加更加灵活地、更加所心所欲在图表上显示需要重点了解的信息。基本方法:使用窗体控件,比如选项按钮、复选框、组合框、滚动条等定义动态名称,或者设计辅助绘图数据区域必备技能:需要熟练使用有关的查找函数了解窗体控件的功能和属性案例35 动态交互图表基本案例练习 Excel数据高效分析之道课件数据高效分析之道课件案例研究:显示最新几个数据的图表随着数据的增加,图表变得愈来愈拥挤。您是否最关心最新的几个数据?案例36 显示最新几个数据的图表 Excel数据高效分析之道课件数据高效分析之道课件实际应用综合案例演示动态图表演示1:费用构成与趋势分析图动态图表演示2:费用预算动态分析动态图表演示3:利润表动态分析Excel数据高效分析之道课件数据高效分析之道课件