《Excel高效数据之道——让您的报告更有说服力》

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

2、的思考的思考 Excel是什么? Excel能做什么? Excel怎样使用?本课程目标 用数据说话： 掌握快速制作各种统计分析报表的方法、技巧和各种应用 用图表说话： 掌握制作精美实用分析图表的方法、技巧和各种应用课程目录 第第1部分：不可忽视的基础性工作部分：不可忽视的基础性工作 第第2部分：快速掌握部分：快速掌握Excel公式和函数公式和函数 第第3部分：让表格数据更加清晰部分：让表格数据更加清晰 第第4部分：用数据说话部分：用数据说话 第第5部分：用图表说话部分：用图表说话第第1部分部分 标准化与规范化标准化与规范化不可忽视的基础性工作不可忽视的基础性工作 为什么要做标准化与规范化工作为

3、什么要做标准化与规范化工作 ExcelExcel表格容易出现的错误表格容易出现的错误 快速修改非法日期快速修改非法日期 快速转换文本型数字快速转换文本型数字 快速删除数据中的特殊字符快速删除数据中的特殊字符 快速分列数据快速分列数据 快速填充数据快速填充数据 其他数据整理技巧其他数据整理技巧 为什么要做标准化与规范化工作 Excel处理分析数据是非常灵活的，但是不能随心所欲地设计表格和输入数据，否则Excel中很多功能或函数将可能无法实现。 Excel表格的标准化与规范化包括：结构标准化和规范化：结构标准化和规范化：很多表格的结构设计是不规范的，使得无法使用函数创建高效率计算公式，也无法使用相

4、关的工具实现数据的高效处理和分析。数据标准化和规范化：数据标准化和规范化：很多表格的数据也是不规范的，使得编制的分析报告出现偏差和错误。 所以，当拿到一个表格时，首先要检查表格的结构和数据是否规范，是否有错误。Excel表格容易出现的错误 表格结构的常见错误和不规范 多行标题 多列标题 合并单元格 不同类型数据放在一个单元格 表格数据的常见错误和不规范 文本有很多不必要的空格 非法日期 文本型数字 数据中含有特殊字符 数据格式不统一 快速修改非法日期 Excel是如何处理日期的？ 日期是特殊的数字。日期永远是大于零的正整数。 正确输入日期 正确输入日期：2010-10-8，2010/10/8，

5、8/oct/2010， 8-oct-2010 错误的输入日期方式：2010.10.08，或者 20101008 错误日期的类型 输入错误 从系统导入的日期是文本格式 修改非法日期（案例01） 基础知识：常用日期函数及其应用快速转换文本型数字在一个表格中，有时候需要把数字处理为文本；有时候又需要把文本型数字转换为纯数字，以便利用有关函数进行统计计算。如何将文本型数字转换为纯数字方法1：利用智能标记方法2：利用VALUE函数方法3：利用公式（两个负号或者乘以1或除以1）方法4：利用选择性粘贴的批量修改功能（乘以1或者除以1）方法5：利用分列工具如何把数字转换为文本型数字?使用分列工具使用TEXT函

6、数注意：文本型数字使用普通函数计算是可以的，但使用数据透视表或者建立分类汇总，就会出现错误。案例02 将文本型数字转化为纯数字快速删除数据中的特殊字符 从系统中导入的数据中，可能会有很多看不见的“垃圾”字符，会影响到数据的处理和分析，应当予以删除。 比如： 删除数据中的空格 删除字符中的打印不出的特殊字符（案例03）快速分列数据 有些情况下，从系统导入的数据是一列数据，需要根据实际情况进行分列。 基本方法 方法1：使用“分列”工具 方法2：实用文本函数 方法3：使用数组公式 案例04 数据分列 基础知识：文本函数及其应用 快速填充数据 有些时候，从系统导入的数据表格有大量的空白单元格，需要进行

7、填充。 根据具体情况： 取消合并单元格并填充数据（标准的数据表是不允许有合并单元格的） 填充为上一行的数据 填充为数字0 案例05 快速填充数据其他数据整理技巧 批量修改数据 转置数据位置 复制可见单元格数据 快速删除空行和空列 快速批量插入空行和空列 快速删除工作表中的所有图形对象 第第2部分部分 武装自己武装自己快速掌握快速掌握Excel公式和函数公式和函数 复杂情况下逻辑判断及其简化处理复杂情况下逻辑判断及其简化处理 将繁琐的加减公式变得更为简洁和高效将繁琐的加减公式变得更为简洁和高效 让需要的数据迅速出现在您面前让需要的数据迅速出现在您面前 创建高效计算公式的技巧和方法创建高效计算公式

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

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

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

11、需要使用多个函数，创建复杂的计算公式来解决。 复杂的计算公式，就是很多函数的嵌套。 创建高效计算公式的方法： 仔细分析问题的本质所在 确定所要使用的函数 先分解公式，再综合公式 必要时使用辅助列第第3部分部分 让表格数据更加清晰让表格数据更加清晰 直观的数据是好报告的基础直观的数据是好报告的基础 使用自定义数字格式美化表格使用自定义数字格式美化表格 使用条件格式标识特殊数据使用条件格式标识特殊数据 让庞大复杂的表格变得更加容易察看让庞大复杂的表格变得更加容易察看 使用列表对数据进行筛选和汇总分析使用列表对数据进行筛选和汇总分析直观的数据是好报告的基础 一份好的分析报告，首先要使表格的数据易于察

12、看和管理。 可以使用相关的工具，把表格数据进行直观性处理。 例如： 使用自定义数字格式，可以快速了解跟踪超预期的数字 使用自定义数字格式，可以把大型数字缩小位数显示 使用条件格式，可以把特殊的数据动态标识出来，并进行动态跟踪分析 对数据进行分类汇总和分级显示，可以让大型表格变得更加精炼使用自定义数字格式美化表格当数字很大时，表格既不便于查看数据，打印出的表格也很不美观。可以通过设置数字的自定义格式来美化表格。例如：将数字缩小1千倍显示将数字缩小1万倍显示将数字缩小百万倍显示将特殊的数字显示为指定的颜色案例11 自定义数字格式 使用条件格式标识特殊数据利用条件格式，可以标识表格的特殊数据。比如：

13、 标识非法数据 设计提前提醒模块（案例12） 小知识：在Excel 2007中，可以对颜色进行筛选和分类汇总，不论这种颜色是固定格式，还是条件格式。让庞大复杂的表格变得更加容易察看很多表格中，既有合计数，也有明细数，使得表格看起来很不方便。可以建立分类汇总和分级显示，让大型表格变得更加精炼案例13 创建多层次的分类汇总和分级显示 使用列表对数据进行筛选和汇总分析 列表具有自动筛选的全部功能，但又有新的功能： 自动扩展筛选区域 手动调整筛选区域 自动复制筛选区域内的公式 可以添加/取消各种方式的分类汇总 列表的名称 Excel 2003中称为“列表” Excel 2007中称为“表” 案例14

14、列表的应用第第4部分：用数据说话部分：用数据说话快速汇总多个工作簿和工作表数据快速汇总多个工作簿和工作表数据数据透视表的使用技巧和实际应用数据透视表的使用技巧和实际应用快速汇总多个工作簿和工作表数据快速汇总多个工作簿和工作表数据 快速汇总个数不定的多个工作表快速汇总个数不定的多个工作表 快速汇总多个结构完全相同的工作表快速汇总多个结构完全相同的工作表 快速汇总结构不同的多个工作表快速汇总结构不同的多个工作表(特殊情况特殊情况) 快速汇总结构不同的多个工作表快速汇总结构不同的多个工作表(一般情况一般情况) 快速汇总多个有关联的工作表快速汇总多个有关联的工作表快速汇总个数不定的多个工作表 使用相关

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

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

17、将这样几个相关联的工作表数据进行汇总，可使用Microsoft Query。案例21 快速汇总多个有关联的工作表数据 数据透视表的使用技巧和实际应用 数据透视表的基本概念数据透视表的基本概念 数据透视表的类型数据透视表的类型 准备工作准备工作 创建数据透视表的基本方法和注意事项创建数据透视表的基本方法和注意事项 设置数据透视的格式设置数据透视的格式 利用数据透视表快速编制各类统计分析报表利用数据透视表快速编制各类统计分析报表 综合应用之综合应用之1：对比分析两个表格数据：对比分析两个表格数据 综合应用之综合应用之2：进销存数据查询：进销存数据查询数据透视表的基本概念 什么是数据透视表？ 数据透

18、视表能做什么？ 怎样灵活使用数据透视表？数据透视表的类型 普通区域数据透视表 合并区域数据透视表 外部数据源的数据透视表 OLAP数据透视表准备工作 制作数据透视表的数据区域必须是数据清单 数据区域第一行为列标题 列标题不能重名 数据区域中不能有空行和空列 数据区域中不能有合并单元格 每列数据为同一种类型的数据 整理数据区域 修改非法日期 把文本型数字转换为纯数字 删除数据区域内的所有空行和空列 取消合并单元格并进行填充 去掉字符串前后的空格 删除数字中的特殊字符 删除不必要的小计行和总计行 将二维表格整理为数据清单 等等创建数据透视表的基本方法和注意事项利用数据透视表向导（案例（案例22）按

19、照向导步骤进行操作缺点：无法实现数据源的动态更新，除非使用动态数据区域名称解决方法：（1）利用导入数据方法；（2）利用OFFSET函数定义动态名称利用导入数据方法按照导入数据向导进行操作优点：可以实现数据源的动态更新；可以在不打开其他工作簿的情况下创建数据透视表可以以数据库的数据创建数据透视表缺点：删除数据项目后，会遗留“假”项目名称利用数据库查询方法按照数据库查询向导进行操作优点：不用导入数据，就可以制作需要的报表可以有选择性地对某些符合条件的数据制作报表可以在不打开其他工作簿的情况下创建数据透视表可以以数据库的数据创建数据透视表设置数据透视的的格式 在数据透视表上对数据透视表进行重新布局

20、修改字段名称 设置字段的数字格式 取消数据透视表的行或列汇总 取消字段的分类汇总 让数据透视表数据按照某一字段进行排序 合并数据标志 设置错误值的显示方式 更新数据透视表的数据利用数据透视表快速编制各类统计分析报表 数据透视表为我们提供了很多实用的数据统计分析工具，可以快速编制各种统计分析报表，例如： 布局透视表 设置字段的分类汇总方式（案例（案例22） 设置字段的显示方式（案例（案例22） 自定义字段（添加新的分析指标）（案例（案例22） 组合字段（案例（案例23） 制作明细表 快速汇总大量工作表数据综合应用之1：对比分析两个表格数据 两个工作表有什么不同？如何快速把两个工作表的差异数据找出

21、来？ 案例24 综合应用之1：对比分析两个表格数据 综合应用之2：进销存数据查询 如何快速了解各个物料、商品等的入库、出库以及库存情况？ 利用数据透视表，可以非常容易地制作出进销存数据查询表格。 案例25 综合应用之2：进销存数据查询第第5部分部分 用图表说话用图表说话用图表准确表达出你的观点用图表准确表达出你的观点绘制图表基本方法及注意事项绘制图表基本方法及注意事项图表的修饰与美化图表的修饰与美化用组合图表表达更加复杂的信息用组合图表表达更加复杂的信息让图表按照您的要求灵活显示让图表按照您的要求灵活显示用图表准确表达出你的观点 图表类型的选用原则 图表分析数据的出发点：正确分类显示数据 快速

22、转换图表数据分析的视角图表类型的选用原则 图表类型 柱形图、条形图、折线图、饼图、XY 散点图、面积图、圆环图、雷达图、曲面图、气泡图、股价图、圆柱图、圆锥图和棱锥图 选用原则和建议 首先要突出重点，充分反映数据信息关注事物的本质！关注事物的本质！ 不同类型的图表有不同的适用场合。 其次是美化图表。 关于组合图表 几种图表类型的组合。 在某些情况下，单一的图表类型无法满足要求，需要绘制组合图表。 关于动态交互图表 使用动态图表，可以大大简化工作量，使得数据分析更加高效。图表分析数据的出发点：正确分类显示数据以列数据或者以行数据分类显示数据，分析问题据的出发点是不同的。 以地区分类：以地区分类：

23、以产品分类：以产品分类：快速转换图表数据分析的视角 快速转换图表数据分析的视角，可以从不同的角度分析数据 方法： 在Excel 2003中使用Excel的图表工具栏 在Excel 2007中使用相关命令按钮 使用“源数据”对话框绘制图表基本方法及注意事项绘制图表基本方法及注意事项 绘图的基本方法 图表的位置 默认区域绘图时一个需要注意的问题 X轴是日期时的问题 复合饼图的调整问题Excel 2003的绘图方法 Excel 2003的绘图基本方法： 使用图表向导 使用默认的图表 注意事项：如果使用名称绘图，就必须使用图表向导 Excel 2007的绘图基本方法 使用“插入”选项卡 使用“选择数据

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

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