Excel高效数据分析之道——让您的分析报告更有说服力课件

1、韩小良 Excel高效数据分析之道Excel高效数据分析之道让您的分析报告更有说服力使用Excel的基本素养充分认识Excel是一个科学管理工具Excel是一个科学管理工具管理要用数据说话，而不是拍脑袋做决策一个有说服力报告的重点内容是数据分析表格和图表养成使用Excel的好习惯合理设计Excel表单，为日常数据管理和处理分析打好基础原始数据表格与数据处理分析表格分开保存采用合理的方法获取外部数据等等自定义Excel操作界面添加常用的操作按钮（格式按钮、增大/缩小字号按钮、选择性粘贴按钮、粘贴数值按钮、等等）熟练使用常用的快捷键等等让你的Excel发挥最大的效能安装完全版（Excel 2003

2、）加载分析工具库Excel的思考Excel是什么?Excel能做什么?Excel怎样使用?如何让你的分析报告更有说服力?用数据说话完美的分析报告用图表说话本课程目标用数据说话：掌握快速制作各种统计分析报表的方法、技巧和各种应用用图表说话：掌握制作精美实用分析图表的方法、技巧和各种应用课程目录第1部分：不可忽视的基础性工作第2部分：快速掌握Excel公式和函数第3部分：让表格数据更加清晰第4部分：用数据说话第5部分：用图表说话第1部分 标准化与规范化不可忽视的基础性工作为什么要做标准化与规范化工作Excel表格容易出现的错误快速修改非法日期快速转换文本型数字快速删除数据中的特殊字符快速分列数据快

3、速填充数据 其他数据整理技巧 为什么要做标准化与规范化工作Excel处理分析数据是非常灵活的，但是不能随心所欲地设计表格和输入数据，否则Excel中很多功能或函数将可能无法实现。Excel表格的标准化与规范化，是数据处理分析的基础。Excel表格的标准化与规范化包括：结构标准化和规范化：很多表格的结构设计是不规范的，使得无法使用函数创建高效率计算公式，也无法使用相关的工具实现数据的高效处理和分析。数据标准化和规范化：很多表格的数据也是不规范的，使得编制的分析报告出现偏差和错误。所以，当拿到一个表格时，首先要检查表格的结构和数据是否规范，是否有错误。Excel表格容易出现的错误表格结构的常见错误

4、和不规范多行标题多列标题合并单元格不同类型数据放在一个单元格表格数据的常见错误和不规范文本有很多不必要的空格非法日期文本型数字数据中含有特殊字符数据格式不统一快速修改非法日期Excel是如何处理日期的？日期是特殊的数字。日期永远是大于零的正整数。正确输入日期正确输入日期：2010-10-8，2010/10/8，8/oct/2010， 8-oct-2010错误的输入日期方式：2010.10.08，或者 错误日期的类型输入错误从系统导入的日期是文本格式修改非法日期（案例01）基础知识：常用日期函数及其应用快速转换文本型数字在一个表格中，有时候需要把数字处理为文本；有时候又需要把文本型数字转换为纯数

5、字，以便利用有关函数进行统计计算。如何将文本型数字转换为纯数字方法1：利用智能标记方法2：利用VALUE函数方法3：利用公式（两个负号或者乘以1或除以1）方法4：利用选择性粘贴的批量修改功能（乘以1或者除以1）方法5：利用分列工具如何把数字转换为文本型数字?使用分列工具使用TEXT函数注意：文本型数字使用普通函数计算是可以的，但使用数据透视表或者建立分类汇总，就会出现错误。案例02 将文本型数字转化为纯数字快速删除数据中的特殊字符从系统中导入的数据中，可能会有很多看不见的“垃圾”字符，会影响到数据的处理和分析，应当予以删除。比如：删除数据中的空格删除字符中的打印不出的特殊字符（案例03）快速分

6、列数据有些情况下，从系统导入的数据是一列数据，需要根据实际情况进行分列。基本方法方法1：使用“分列”工具方法2：实用文本函数方法3：使用数组公式案例04 数据分列基础知识：文本函数及其应用 快速填充数据有些时候，从系统导入的数据表格有大量的空白单元格，需要进行填充。根据具体情况：取消合并单元格并填充数据（标准的数据表是不允许有合并单元格的）填充为上一行的数据填充为数字0案例05 快速填充数据其他数据整理技巧批量修改数据转置数据位置复制可见单元格数据 快速删除空行和空列快速批量插入空行和空列快速删除工作表中的所有图形对象第2部分 武装自己快速掌握Excel公式和函数复杂情况下逻辑判断及其简化处理

7、将繁琐的加减公式变得更为简洁和高效让需要的数据迅速出现在您面前创建高效计算公式的技巧和方法复杂情况下逻辑判断及其简化处理很多数据逻辑判断不是一个IF函数所能解决的，需要使用多个IF函数。在另外一些情况下，还需要联合使用AND函数或者OR函数进行复杂的数据判断处理。如果判断条件超过了7个，就需要使用条件表达式来处理。案例06 复杂的数据判断处理将繁琐的加减公式变得更为简洁和高效低效率的加减公式，既不科学，也容易出错。在很多情况下，可以使用有关的求和函数来解决。常用的求和函数SUM函数：无条件求和。但SUM函数还有另类用法，比如快速计算表格中的小计的总和 ； 快速对多个工作表求和SUMIF 函数：

8、单条件求和=SUMIF(条件判断区域，条件值，实际求和区域)SUMIFS函数（Excel 2007新增函数）：多条件求和=SUMIFS(实际求和区域，条件判断区域1，条件值1，条件判断区域2，条件值2，.)SUMPRODUCT 函数：计算乘积和，可以用于多条件求和、多条件计数= SUMPRODUCT(数组1，数组2，数组3，.)SUBTOTAL 函数：对数据进行分类汇总，主要用于筛选和列表 = SUBTOTAL(函数类型，计算区域1，计算区域2，.)案例07 高效求和汇总让需要的数据迅速出现在您面前很多情况需要将需要的数据查找出来，此时有很多查找函数可以选择使用。查找函数有很多，其中使用最多的

9、是VLOOKUP函数、INDEX函数、MATCH函数、INDIRECT函数。VLOOKUP函数：根据首列数据进行查询（案例08）=VLOOKUP(查找依据，查找区域，指定列位置，逻辑值)INDEX函数：根据行索引和列索引进行查询（案例09）= INDEX(查找区域，指定行位置，指定列位置)MATCH函数：对数据进行定位（案例09）= MATCH(查找依据，查找区域，指定方式)INDIRECT函数：对字符串表示的“单元格地址”进行转换= INDIRECT(字符串表示的“单元格地址”)查找函数综合应用：案例10 查询重复数据注意事项：1、无法查找重复值2、不区分大小写创建高效计算公式的技巧和方法很

10、多实际问题，需要使用多个函数，创建复杂的计算公式来解决。复杂的计算公式，就是很多函数的嵌套。创建高效计算公式的方法：仔细分析问题的本质所在确定所要使用的函数先分解公式，再综合公式必要时使用辅助列第3部分 让表格数据更加清晰直观的数据是好报告的基础使用自定义数字格式美化表格使用条件格式标识特殊数据让庞大复杂的表格变得更加容易察看使用列表对数据进行筛选和汇总分析直观的数据是好报告的基础一份好的分析报告，首先要使表格的数据易于察看和管理。可以使用相关的工具，把表格数据进行直观性处理。例如：使用自定义数字格式，可以快速了解跟踪超预期的数字使用自定义数字格式，可以把大型数字缩小位数显示使用条件格式，可以

11、把特殊的数据动态标识出来，并进行动态跟踪分析对数据进行分类汇总和分级显示，可以让大型表格变得更加精炼使用自定义数字格式美化表格当数字很大时，表格既不便于查看数据，打印出的表格也很不美观。可以通过设置数字的自定义格式来美化表格。例如：将数字缩小1千倍显示将数字缩小1万倍显示将数字缩小百万倍显示将特殊的数字显示为指定的颜色案例11 自定义数字格式 使用条件格式标识特殊数据利用条件格式，可以标识表格的特殊数据。比如：标识非法数据设计提前提醒模块（案例12） 小知识：在Excel 2007中，可以对颜色进行筛选和分类汇总，不论这种颜色是固定格式，还是条件格式。让庞大复杂的表格变得更加容易察看很多表格中

12、，既有合计数，也有明细数，使得表格看起来很不方便。可以建立分类汇总和分级显示，让大型表格变得更加精炼案例13 创建多层次的分类汇总和分级显示 使用列表对数据进行筛选和汇总分析列表具有自动筛选的全部功能，但又有新的功能：自动扩展筛选区域手动调整筛选区域自动复制筛选区域内的公式可以添加/取消各种方式的分类汇总列表的名称Excel 2003中称为“列表”Excel 2007中称为“表”案例14 列表的应用第4部分：用数据说话快速汇总多个工作簿和工作表数据数据透视表的使用技巧和实际应用快速汇总多个工作簿和工作表数据快速汇总个数不定的多个工作表快速汇总多个结构完全相同的工作表快速汇总结构不同的多个工作表

13、(特殊情况) 快速汇总结构不同的多个工作表(一般情况)快速汇总多个有关联的工作表快速汇总个数不定的多个工作表使用相关的函数，可以快速汇总大量工作表数据。例如：使用SUM函数可以快速加总大量工作表使用INDIRECT函数可以动态汇总大量的个数不定的工作表案例15 动态汇总个数不定的工作表（案例1）案例16 动态汇总个数不定的工作表（案例2）快速汇总多个结构完全相同的工作表当多个工作表的结构相同时，可以使用合并计算工具快速进行合并计算，同时还可以创建分类汇总和分级显示。案例研究:案例17 快速合并多个结构相同的分报表(当前工作簿)案例18 快速合并多个结构相同的分报表(不同工作簿) 快速汇总结构不

14、同的多个工作表（特殊情况）利用多重合并计算数据区域的数据透视表，可以快速把数十个甚至上百个工作表的数据汇总在一起，然后在此基础上进行相关的统计分析。注意：要汇总的多个工作表数据行数和列数可以不同，但从数据区域的第2列开始必须是数字。案例19 快速汇总多个工作表数据(特殊情况) 三个分公司数据汇总到一张工作表快速汇总结构不同的多个工作表（一般情况）一般情况下，要汇总的各个工作表数据可能含有多列文本，此时就不能采用普通的多重合并计算数据区域的数据透视表来进行汇不总了。最好的解决方法：使用导入外部数据+SQL语句。案例20 快速汇总多个工作表数据(一般情况) 快速汇总多个有关联的工作表有些情况下，几

15、个工作表中分别保存不同的数据，但他们通过一个关键列数据（比如工号）联系起来。要将这样几个相关联的工作表数据进行汇总，可使用Microsoft Query。案例21 快速汇总多个有关联的工作表数据 数据透视表的使用技巧和实际应用数据透视表的基本概念数据透视表的类型准备工作创建数据透视表的基本方法和注意事项设置数据透视的格式利用数据透视表快速编制各类统计分析报表综合应用之1：对比分析两个表格数据综合应用之2：进销存数据查询数据透视表的基本概念什么是数据透视表？数据透视表能做什么？怎样灵活使用数据透视表？数据透视表的类型普通区域数据透视表合并区域数据透视表外部数据源的数据透视表OLAP数据透视表准备

16、工作制作数据透视表的数据区域必须是数据清单数据区域第一行为列标题列标题不能重名数据区域中不能有空行和空列数据区域中不能有合并单元格每列数据为同一种类型的数据整理数据区域修改非法日期把文本型数字转换为纯数字删除数据区域内的所有空行和空列取消合并单元格并进行填充去掉字符串前后的空格删除数字中的特殊字符删除不必要的小计行和总计行将二维表格整理为数据清单等等创建数据透视表的基本方法和注意事项利用数据透视表向导（案例22）按照向导步骤进行操作缺点：无法实现数据源的动态更新，除非使用动态数据区域名称解决方法：（1）利用导入数据方法；（2）利用OFFSET函数定义动态名称利用导入数据方法按照导入数据向导进行

17、操作优点：可以实现数据源的动态更新；可以在不打开其他工作簿的情况下创建数据透视表可以以数据库的数据创建数据透视表缺点：删除数据项目后，会遗留“假”项目名称利用数据库查询方法按照数据库查询向导进行操作优点：不用导入数据，就可以制作需要的报表可以有选择性地对某些符合条件的数据制作报表可以在不打开其他工作簿的情况下创建数据透视表可以以数据库的数据创建数据透视表设置数据透视的的格式在数据透视表上对数据透视表进行重新布局修改字段名称设置字段的数字格式取消数据透视表的行或列汇总取消字段的分类汇总让数据透视表数据按照某一字段进行排序合并数据标志设置错误值的显示方式更新数据透视表的数据利用数据透视表快速编制各

18、类统计分析报表数据透视表为我们提供了很多实用的数据统计分析工具，可以快速编制各种统计分析报表，例如：布局透视表设置字段的分类汇总方式（案例22）设置字段的显示方式（案例22）自定义字段（添加新的分析指标）（案例22）组合字段（案例23）制作明细表快速汇总大量工作表数据综合应用之1：对比分析两个表格数据两个工作表有什么不同？如何快速把两个工作表的差异数据找出来？案例24 综合应用之1：对比分析两个表格数据 综合应用之2：进销存数据查询如何快速了解各个物料、商品等的入库、出库以及库存情况？利用数据透视表，可以非常容易地制作出进销存数据查询表格。案例25 综合应用之2：进销存数据查询第5部分 用图表

19、说话用图表准确表达出你的观点绘制图表基本方法及注意事项图表的修饰与美化用组合图表表达更加复杂的信息让图表按照您的要求灵活显示用图表准确表达出你的观点图表类型的选用原则图表分析数据的出发点：正确分类显示数据快速转换图表数据分析的视角图表类型的选用原则图表类型柱形图、条形图、折线图、饼图、XY 散点图、面积图、圆环图、雷达图、曲面图、气泡图、股价图、圆柱图、圆锥图和棱锥图 选用原则和建议首先要突出重点，充分反映数据信息关注事物的本质！不同类型的图表有不同的适用场合。其次是美化图表。关于组合图表几种图表类型的组合。在某些情况下，单一的图表类型无法满足要求，需要绘制组合图表。关于动态交互图表使用动态图

20、表，可以大大简化工作量，使得数据分析更加高效。图表分析数据的出发点：正确分类显示数据以列数据或者以行数据分类显示数据，分析问题据的出发点是不同的。 以地区分类：以产品分类：快速转换图表数据分析的视角快速转换图表数据分析的视角，可以从不同的角度分析数据方法：在Excel 2003中使用Excel的图表工具栏在Excel 2007中使用相关命令按钮使用“源数据”对话框绘制图表基本方法及注意事项绘图的基本方法图表的位置默认区域绘图时一个需要注意的问题X轴是日期时的问题复合饼图的调整问题Excel 2003的绘图方法Excel 2003的绘图基本方法：使用图表向导使用默认的图表注意事项：如果使用名称绘

21、图，就必须使用图表向导Excel 2007的绘图基本方法使用“插入”选项卡使用“选择数据源”对话框注意事项：如果使用名称绘图，就必须使用“选择数据源”对话框图表的位置嵌入式图表适用于数据较少，需要把表格数据与图表一起查看的场合图表工作表适用于数据量较大，图表较大的场合默认区域绘图时一个需要注意的问题当数据区域的第1列是数字时，采用默认图表的方法绘制的图表，会把这列数据作为系列绘制在图表上。案例26 默认区域绘图的潜在问题 X轴是日期时的问题如果X轴数据是日期，并且是不连续的，在绘制柱形图、条形图、折线图等时，会以连续的日期在X轴上显示。案例27 X轴是日期时的问题 复合饼图的调整问题绘制复合饼

22、图，要合理设置小饼的项目。方法：按位置调整按数值调整按比例调整案例28 复合饼图的调整问题图表的修饰与美化图表结构及主要元素打扮您的图表突出标识图表的重点信息简单是美图表结构及主要元素图表区绘图区数据系列分类轴数值轴（刻度、显示单位）图表标题图例数据标签网格线趋势线、高低点连线、垂直线、涨跌柱线打扮您的图表美化图表的基本原则：更加便于关注事物的本质，而不是其他无关紧要的东西。简单是美设置图表各元素的格式重点关注图表区背景、绘图区背景、数据系列柱形背景等对于折线图和XY散点图，要合理设置网格线格式美化图表常犯的错误过渡修饰图表元素淡化了图表重点信息案例练习：各种常见图表的美化及效果对比案例29 图表美化效果对比突出图表的重点信息使用自选图形、图片等，突出重点数据，使阅读者一目了然地了解数据信息，发现企业经营存在的问题。案例30 利用自选图形突出重点 简单是美要注意：图表是向别人传达信息的图表不是时装秀因此：图表的美化不应影响主体信息的表达不要给人一种五彩缤纷的感觉一句话：简单是美绘制组合图表：表达更加复杂的信息绘制两种单位的数据系列的组合图表差异对比分析图资金流动分析图产品合格率分析图绘制两种单位的数据系列的组合图表两种单位的数据系列是不能绘制在同一个坐标轴上的，而必须分别绘制在主轴和次轴上。例如：销售量-