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

咨信网声明:本文档由用户上传并分享,仅供学习交流、研究之用,未经授权,严禁复制、发行、汇编、翻译或网络传播等,侵权必究。
咨信网小贴示:
1、浏览或下载可查看了解,请点击【咨信网告知】。
2、下载文件中如有侵权或不适当内容,将立即纠正。
3、如果因为网速或其他原因下载失败请重新下载,重复下载[60天内]不扣币。
4、开具发票登录电脑端申请,请点击【索取发票】。
咨信网客服组:【微信客服】
1、浏览或下载可查看了解,请点击【咨信网告知】。
2、下载文件中如有侵权或不适当内容,将立即纠正。
3、如果因为网速或其他原因下载失败请重新下载,重复下载[60天内]不扣币。
4、开具发票登录电脑端申请,请点击【索取发票】。
咨信网客服组:【微信客服】


Excel数据高效分析技巧.ppt
















自信AI助手














微信客服
客服QQ
发送邮件
意见反馈



链接地址:https://www.zixin.com.cn/doc/14775647.html