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

韩小良Excel高效数据分析之道■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■Excel高效数据分析之道—让您的分析报告更有说服力使用Excel的基本素养•充分认识Excel是一个科学管理工具–Excel是一个科学管理工具–管理要用数据说话，而不是拍脑袋做决策–一个有说服力报告的重点内容是数据分析表格和图表•养成使用Excel的好习惯–合理设计Excel表单，为日常数据管理和处理分析打好基础–原始数据表格与数据处理分析表格分开保存–采用合理的方法获取外部数据–等等•自定义Excel操作界面–添加常用的操作按钮（格式按钮、增大/缩小字号按钮、选择性粘贴按钮、粘贴数值按钮、等等）–熟练使用常用的快捷键–等等•让你的Excel发挥最大的效能–安装完全版（Excel2003）–加载分析工具库Excel的思考•Excel是什么?•Excel能做什么?•Excel怎样使用?如何让你的分析报告更有说服力?用数据说话完美的分析报告用图表说话本课程目标•用数据说话：–掌握快速制作各种统计分析报表的方法、技巧和各种应用•用图表说话：–掌握制作精美实用分析图表的方法、技巧和各种应用课程目录•第1部分：不可忽视的基础性工作•第2部分：快速掌握Excel公式和函数•第3部分：让表格数据更加清晰•第4部分：用数据说话•第5部分：用图表说话第1部分标准化与规范化——不可忽视的基础性工作•为什么要做标准化与规范化工作•Excel表格容易出现的错误•快速修改非法日期•快速转换文本型数字•快速删除数据中的特殊字符•快速分列数据•快速填充数据•其他数据整理技巧为什么要做标准化与规范化工作•Excel处理分析数据是非常灵活的，但是不能随心所欲地设计表格和输入数据，否则Excel中很多功能或函数将可能无法实现。•Excel表格的标准化与规范化，是数据处理分析的基础。•Excel表格的标准化与规范化包括：–结构标准化和规范化：很多表格的结构设计是不规范的，使得无法使用函数创建高效率计算公式，也无法使用相关的工具实现数据的高效处理和分析。–数据标准化和规范化：很多表格的数据也是不规范的，使得编制的分析报告出现偏差和错误。•所以，当拿到一个表格时，首先要检查表格的结构和数据是否规范，是否有错误。Excel表格容易出现的错误•表格结构的常见错误和不规范–多行标题–多列标题–合并单元格–不同类型数据放在一个单元格–……•表格数据的常见错误和不规范–文本有很多不必要的空格–非法日期–文本型数字–数据中含有特殊字符–数据格式不统一–……快速修改非法日期•Excel是如何处理日期的？–日期是特殊的数字。日期永远是大于零的正整数。•正确输入日期–正确输入日期：2010-10-8，2010/10/8，8/oct/2010，8-oct-2010–错误的输入日期方式：2010.10.08，或者20101008•错误日期的类型–输入错误–从系统导入的日期是文本格式•修改非法日期（案例01）•基础知识：常用日期函数及其应用快速转换文本型数字•在一个表格中，有时候需要把数字处理为文本；有时候又需要把文本型数字转换为纯数字，以便利用有关函数进行统计计算。•如何将文本型数字转换为纯数字–方法1：利用智能标记–方法2：利用VALUE函数–方法3：利用公式（两个负号或者乘以1或除以1）–方法4：利用选择性粘贴的批量修改功能（乘以1或者除以1）–方法5：利用分列工具•如何把数字转换为文本型数字?–使用分列工具–使用TEXT函数•注意：文本型数字使用普通函数计算是可以的，但使用数据透视表或者建立分类汇总，就会出现错误。•案例02将文本型数字转化为纯数字快速删除数据中的特殊字符•从系统中导入的数据中，可能会有很多看不见的“垃圾”字符，会影响到数据的处理和分析，应当予以删除。•比如：–删除数据中的空格–删除字符中的打印不出的特殊字符（案例03）快速分列数据•有些情况下，从系统导入的数据是一列数据，需要根据实际情况进行分列。•基本方法–方法1：使用“分列”工具–方法2：实用文本函数–方法3：使用数组公式•案例04数据分列•基础知识：文本函数及其应用快速填充数据•有些时候，从系统导入的数据表格有大量的空白单元格，需要进行填充。•根据具体情况：–取消合并单元格并填充数据（标准的数据表是不允许有合并单元格的）–填充为上一行的数据–填充为数字0•案例05快速填充数据其他数据整理技巧•批量修改数据•转置数据位置•复制可见单元格数据•快速删除空行和空列•快速批量插入空行和空列•快速删除工作表中的所有图形对象•……第2部分武装自己快速掌握Excel公式和函数•复杂情况下逻辑判断及其简化处理•将繁琐的加减公式变得更为简洁和高效•让需要的数据迅速出现在您面前•创建高效计算公式的技巧和方法复杂情况下逻辑判断及其简化处理•很多数据逻辑判断不是一个IF函数所能解决的，需要使用多个IF函数。•在另外一些情况下，还需要联合使用AND函数或者OR函数进行复杂的数据判断处理。•如果判断条件超过了7个，就需要使用条件表达式来处理。•案例06复杂的数据判断处理将繁琐的加减公式变得更为简洁和高效•低效率的加减公式，既不科学，也容易出错。在很多情况下，可以使用有关的求和函数来解决。•常用的求和函数–SUM函数：无条件求和。但SUM函数还有另类用法，比如快速计算表格中的小计的总和；快速对多个工作表求和–SUMIF函数：单条件求和=SUMIF(条件判断区域，条件值，实际求和区域)–SUMIFS函数（Excel2007新增函数）：多条件求和=SUMIFS(实际求和区域，条件判断区域1，条件值1，条件判断区域2，条件值2，……..)–SUMPRODUCT函数：计算乘积和，可以用于多条件求和、多条件计数=SUMPRODUCT(数组1，数组2，数组3，……..)–SUBTOTAL函数：对数据进行分类汇总，主要用于筛选和列表=SUBTOTAL(函数类型，计算区域1，计算区域2，……..)•案例07高效求和汇总让需要的数据迅速出现在您面前•很多情况需要将需要的数据查找出来，此时有很多查找函数可以选择使用。•查找函数有很多，其中使用最多的是VLOOKUP函数、INDEX函数、MATCH函数、INDIRECT函数。•VLOOKUP函数：根据首列数据进行查询（案例08）=VLOOKUP(查找依据，查找区域，指定列位置，逻辑值)•INDEX函数：根据行索引和列索引进行查询（案例09）=INDEX(查找区域，指定行位置，指定列位置)•MATCH函数：对数据进行定位（案例09）=MATCH(查找依据，查找区域，指定方式)•INDIRECT函数：对字符串表示的“单元格地址”进行转换=INDIRECT(字符串表示的“单元格地址”)•查找函数综合应用：–案例10查询重复数据注意事项：1、无法查找重复值2、不区分大小写创建高效计算公式的技巧和方法•很多实际问题，需要使用多个函数，创建复杂的计算公式来解决。•复杂的计算公式，就是很多函数的嵌套。•创建高效计算公式的方法：–仔细分析问题的本质所在–确定所要使用的函数–先分解公式，再综合公式–必要时使用辅助列第3部分让表格数据更加清晰•直观的数据是好报告的基础•使用自定义数字格式美化表格•使用条件格式标识特殊数据•让庞大复杂的表格变得更加容易察看•使用列表对数据进行筛选和汇总分析直观的数据是好报告的基础•一份好的分析报告，首先要使表格的数据易于察看和管理。•可以使用相关的工具，把表格数据进行直观性处理。•例如：–使用自定义数字格式，可以快速了解跟踪超预期的数字–使用自定义数字格式，可以把大型数字缩小位数显示–使用条件格式，可以把特殊的数据动态标识出来，并进行动态跟踪分析–对数据进行分类汇总和分级显示，可以让大型表格变得更加精炼使用自定义数字格式美化表格•当数字很大时，表格既不便于查看数据，打印出的表格也很不美观。•可以通过设置数字的自定义格式来美化表格。–例如：–将数字缩小1千倍显示–将数字缩小1万倍显示–将数字缩小百万倍显示–将特殊的数字显示为指定的颜色•案例11自定义数字格式使用条件格式标识特殊数据•利用条件格式，可以标识表格的特殊数据。•比如：–标识非法数据–设计提前提醒模块（案例12）•小知识：在Excel2007中，可以对颜色进行筛选和分类汇总，不论这种颜色是固定格式，还是条件格式。让庞大复杂的表格变得更加容易察看•很多表格中，既有合计数，也有明细数，使得表格看起来很不方便。•可以建立分类汇总和分级显示，让大型表格变得更加精炼•案例13创建多层次的分类汇总和分级显示使用列表对数据进行筛选和汇总分析•列表具有自动筛选的全部功能，但又有新的功能：–自动扩展筛选区域–手动调整筛选区域–自动复制筛选区域内的公式–可以添加/取消各种方式的分类汇总•列表的名称–Excel2003中称为“列表”–Excel2007中称为“表”•案例14列表的应用第4部分：用数据说话快速汇总多个工作簿和工作表数据数据透视表的使用技巧和实际应用快速汇总多个工作簿和工作表数据•快速汇总个数不定的多个工作表•快速汇总多个结构完全相同的工作表•快速汇总结构不同的多个工作表(特殊情况)•快速汇总结构不同的多个工作表(一般情况)•快速汇总多个有关联的工作表快速汇总个数不定的多个工作表•使用相关的函数，可以快速汇总大量工作表数据。•例如：–使用SUM函数可以快速加总大量工作表–使用INDIRECT函数可以动态汇总大量的个数不定的工作表•案例15动态汇总个数不定的工作表（案例1）•案例16动态汇总个数不定的工作表（案例2）快速汇总多个结构完全相同的工作表•当多个工作表的结构相同时，可以使用合并计算工具快速进行合并计算，同时还可以创建分类汇总和分级显示。•案例研究:–案例17快速合并多个结构相同的分报表(当前工作簿)–案例18快速合并多个结构相同的分报表(不同工作簿)快速汇总结构不同的多个工作表（特殊情况）•利用多重合并计算数据区域的数据透视表，可以快速把数十个甚至上百个工作表的数据汇总在一起，然后在此基础上进行相关的统计分析。•注意：要汇总的多个工作表数据行数和列数可以不同，但从数据区域的第2列开始必须是数字。•案例19快速汇总多个工作表数据(特殊情况)三个分公司数据汇总到一张工作表快速汇总结构不同的多个工作表（一般情况）•一般情况下，要汇总的各个工作表数据可能含有多列文本，此时就不能采用普通的多重合并计算数据区域的数据透视表来进行汇不总了。•最好的解决方法：使用导入外部数据+SQL语句。•案例20快速汇总多个工作表数据(一般情况)快速汇总多个有关联的工作表•有些情况下，几个工作表中分别保存不同的数据，但他们通过一个关键列数据（比如工号）联系起来。•要将这样几个相关联的工作表数据进行汇总，可使用MicrosoftQuery。•案例21快速汇总多个有关联的工作表数据数据透视表的使用技巧和实际应用•数据透视表的基本概念•数据透视表的类型•准备工作•创建数据透视表的基本方法和注意事项•设置数据透视的格式•利用数据透视表快速编制各类统计分析报表•综合应用之1：对比分析两个表格数据•综合应用之2：进销存数据查询数据透视表的基本概念•什么是数据透视表？•数据透视表能做什么？•怎样灵活使用数据透视表？数据透视表的类型•普通区域数据透视表•合并区域数据透视表•外部数据源的数据透视表•OLAP数据透视表准备工作•制作数据透视表的数据区域必须是数据清单–数据区域第一行为列标题–列标题不能重名–数据区域中不能有空行和空列–数据区域中不能有合并单元格–每列数据为同一种类型的数据•整理数据区域–修改非法日期–把文本型数字转换为纯数字–删除数据区域内的所有空行和空列–取消合并单元格并进行填充–去掉字符串前后的空格–删除数字中的特殊字符–删除不必要的小计行和总计行–将二维表格整理为数据清单–等等创建数据透视表的基本方法和注意事项•利用数据透视表向导（案例22）–按照向导步骤进行操作–缺点：无法实现数据源的动态更新，除非使用动态数据区域名称–解决方法：（1）利