Excel高效数据分析分析.ppt

1、Excel高效数据分析,关于课程,课程目标： 帮您树立一种正确高效使用Excel的基本理念和思路 为您提供一套高效实用的Excel技能和方法,课程目录,一、 使用Excel的正确理念与注意事项 二、 设计标准规范的Excel表格 三、 快速掌握实用的Excel函数 四、 快速掌握Excel数据透视表 五、 从原始数据到分析图表,一、使用Excel的正确理念与注意事项,1、Excel是什么 2、如何使用Excel做财务管理 3、基本原则 4、设计模版的基本步骤 5、财务分析模版设计应考虑的几个问题 6、正确处理基础表格与报告表格 7、如何学习Excel,精细化管理,日 常 控 制,科 学 决 策

2、,Excel是什么,6,工作,如何使用Excel做财务管理,基本原则,从原始的表单最终的报告 尽量使用原始数据 尽量减少中间环节 简洁的报告 高效的公式,设计模版的基本步骤,Step1：获取数据 分析数据的来源（手工表格；导入表格） 源数据的结构（单表数据，多表数据） Step2：表格结构设计 基础表格 报告表格 Step3：呈现方式 Excel表格形式 Word书面形式 PPT演示形式,财务分析模版设计应考虑的几个问题,设计财务分析模版应考虑的几个问题 层次结构： 单层结构还是多层结构？ 布局方向： 横向布局还是纵向布局？ 动与静： 静态分析还是滚动分析？ 灵活性： 分析项目是一种还是多种？

3、 直观性： 图形展示分析结果 整合性： 要不要与财务软件整合起来？ 报告性： 是否做成一个最终的汇报性的报告？ 案例解析 案例01 单表分析模版演示案例 案例02 多表分析模版演示案例,正确处理基础表格与报告表格,Excel的两类表格 基础表格 按照数据的功能来分别保存在不同的工作表 结构越简单越好 报告表格 要考虑汇总过程的效率性和方便性，分析结果的呈现性 结构越清楚越好 设计表格应遵循的总体原则： 结构的科学性 数据的易读性 汇总的便宜性 分析的灵活性 外观的美观性,如何学习Excel,Excel一定要结合企业管理来学习 从最初的表单设计，一直到最终报表，都需要按照企业精细化管理的要求来进

4、行 必须掌握Excel的实用技能 高效办公技能 数据跟踪技能 分类汇总技能 链接查询技能 合并工作表技能 数据挖掘技能 图形展示技能 自动化技能,二、设计标准规范的Excel表格,1、标准化表格解析 2、表格数据的整理与规范 3 、使用数据有效性控制规范数据输入 4 、使用基本的函数获取基本数据 5 、使用条件格式自动美化表格 6 、创建列表自动复制公式 7 、设计表格综合练习,标准化表格解析,标准化表格的基本要求 数据库结构 结构最简单 填写数据方便 没有合并单元格 没有空行和空列 没有不必要的小计和总计 没有不必要的公式 数据规范,表格数据的整理与规范,很多表格中的数据是非法的、错误的、不

5、规范的，需要进行整理规范和标准化。 正确处理日期数据 正确处理文本和数字,正确处理日期数据,Excel是如何处理日期的？ 日期是特殊的数字。日期永远是大于零的正整数。 正确输入日期 正确输入日期：2010-10-8，2010/10/8，8/oct/2010， 8-oct-2010 错误的输入日期方式：2010.10.08，或者 20101008 错误日期的类型 输入错误 从系统导入的日期是文本格式 如何把日期显示为需要的格式？使用“分列”功能 修改非法日期,正确处理文本和数字,如何将文本型数字转换为纯数字 有些情况下，从数据库导入的数字是文本型数字。文本型数字无法使用SUM等函数进行计算，需要

6、转换为纯数字。 方法1：利用智能标记 方法2：利用VALUE函数 方法3：利用公式（两个负号或者乘以1或除以1） 方法4：利用选择性粘贴的批量修改功能（乘以1或者除以1） 方法5：利用分列工具(只能一列一列改） 如何把数字转换为文本型数字? 使用分列工具 使用TEXT函数,使用数据有效性控制规范数据输入,利用有效性，可以控制规范数据输入，快速准确输入数据，让别人按照您的要求填列数据。 比如： 只能输入规定格式的数据 只能输入规定的序列数据 只有在上一行所有单元格都输入数据后，才能在下一行输入新的数据 多重限制输入数据 不允许输入重复数据 案例03 有效性典型应用,其他数据整理技巧,批量修改数据

7、 转置数据位置 复制可见单元格数据 批量插入空行和空列 批量填充单元格 ,使用基本的函数获取基本数据,使用有关的查找函数，可以快速准确输入基本资料，避免复制粘贴等手工操作。 常用的函数有 查找函数VLOOKUP 分类汇总函数SUMIF、SUMPRODUCT 文本函数LEFT、RIGHT、MID、TEXT 日期函数TODAY、EDATE、DATEDIF 案例练习 案例04 使用函数输入数据,使用条件格式自动美化表格,使用条件格式，可以自动美化表格，既便于快速输入数据，有使表格干净美观。 案例练习 案例05 条件格式美化表格,创建列表自动复制公式,使用列表工具，不仅可以自动建立筛选，还可以自动复制

8、公式、格式、有效性等。 案例练习 案例06 使用列表自动复制公式,三、快速掌握实用的Excel函数,1、函数基础 2、处理日期常用的3个函数 3、处理文本常用的4个函数 4、逻辑判断常用的4个函数 5、分类汇总常用的3个函数 6、数据查找常用的4个函数 7、函数综合练习,函数基础,创建公式（以“”开始，可以包括：运算符、单元格引用、值或常量以及工作表函数。） 公式中的运算符 例如: （求幂）、 &（文本连接运算符）、 （不等于）等 公式实例： =150*.01 =A1+A2 =SUM(A1:A2) =A1=C12 =(B2-B3)*B4 一个公式最多可以容纳1024个字符,函数基础,绝对引用/

9、相对引用（演示） 引用（说明） 更改为 $A$1（绝对列和绝对行） $A$1 A$1（相对列和绝对行） C$1 $A1（绝对列和相对行） $A3 A1（相对列和相对行） C3,函数基础,函数使用常见的错误,处理日期常用的3个函数,3个重要的日期函数 TODAY函数：获取当天日期 EDATE函数：计算到期日 DATEDIF函数：计算期限 案例练习 案例07 计算新车库存成本 案例08 计算合同到期日,处理文本常用的4个函数,4个重要的文本函数 LEFT RIGHT MID TEXT 案例练习 案例04 使用函数输入数据,逻辑判断常用的4个函数,4个重要的逻辑判断函数 IF函数（嵌套IF） AND

10、函数 OR函数 ISERROR函数（IFERROR函数） 案例练习 案例09 计算工龄工资,分类汇总常用的3个函数,3个重要的分类汇总函数 SUMIF SUMIFS SUMPRODUCT 案例练习 案例10 分类汇总函数基本练习,数据查找常用的4个函数,4个重要的查找引用函数 VLOOKUP函数 MATCH函数 INDEX函数 INDIRECT函数 案例练习 案例11 VLOOKUP函数应用 案例12 查找函数高级应用,函数综合练习,针对从系统导出的维修记录表，设计一个动态的查询系统，可以： 任选年份 任选月份 任选日期 使用技能和函数 滚动查找技术 MATCH函数 INDEX函数 INDIR

11、ECT函数 IF函数 ISERROR函数,四、快速掌握Excel数据透视表,1、什么是数据透视表 2、制作数据透视表的基本方法 3、了解数据透视表的结构 4、数据透视表的美化 5、利用数据透视表分析数据 6、数据透视表的特殊应用,什么是数据透视表,什么是数据透视表？ 一种动态的汇总分析报表 数据透视表能做什么？ 分类汇总 多维度统计分析 汇总大量工作表 制作明细表 高效数据查询 对比分析 怎样灵活使用数据透视表？ 制作正确的数据透视表 掌握数据透视表的各种功能,制作数据透视表的基本方法,基本步骤 Step1：标准化规范化基础表格 Step2：采用合适的方法制作数据透视表 Step3：布局透视表

12、，完成报告,了解数据透视表的结构,行字段：是分类字段，是指在数据透视表中被指定为行方向的字段。 列字段：是分类字段，是指在数据透视表中被指定为列方向的字段。 页字段：是筛选字段，允许筛选整个数据透视表。 值字段：是汇总计算字段。,数据透视表的美化,在数据透视表上对数据透视表进行重新布局 修改字段名称 设置字段的数字格式 取消数据透视表的行或列汇总 取消字段的分类汇总 让数据透视表数据按照某一字段进行排序 合并数据标志 设置错误值的显示方式 更新数据透视表的数据,利用数据透视表分析数据,基本方法： 布局透视表 设置字段的分类汇总方式 设置字段的显示方式 自定义数据透视表 组合字段 制作明细表 案例练习 案例13 数据透视表综合分析,数据透视表的特殊应用,快速合并大量的工作表 多个二维表格合并计算 案例14 全年管理费用汇总分析 快速对比两个表格 案例15 快速核对社保,五、从原始数据到分析图表,制作图表的基本方法 使用组合图表分析深层次信息 使用动态图表灵活分析数据,制作图表的基本方法,两种基本的绘图方法 直接命令法 手工添加法 案例练习 案例16 绘制图表基本练习,使用组合图表分析深层次信息,组合图表：通过数据的重新调整，或者通过图表的格式设置，所制作的特殊分析图表。 常用方法 辅助绘图区域法 多图表组合法 设置涨跌柱线、高低点连线、趋势线 案例练习 案