Excel数据高效分析之道

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