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