Excel高效数据分析分析

1、关于课程 课程目标：课程目标：u帮您树立一种正确高效使用Excel的基本理念和思路u为您提供一套高效实用的Excel技能和方法课程目录课程目录 一、一、 使用使用Excel的正确理念与注意事项的正确理念与注意事项 二、二、 设计标准规范的设计标准规范的Excel表格表格 三、三、 快速掌握实用的快速掌握实用的Excel函数函数 四、四、 快速掌握快速掌握Excel数据透视表数据透视表 五、五、 从原始数据到分析图表从原始数据到分析图表一、使用Excel的正确理念与注意事项 1 1、ExcelExcel是什么是什么 2 2、如何使用、如何使用ExcelExcel做财务管理做财务管理 3 3、基本

2、原则、基本原则 4 4、设计模版的基本步骤、设计模版的基本步骤 5 5、财务分析模版设计应考虑的几个问题、财务分析模版设计应考虑的几个问题 6 6、正确处理基础表格与报告表格、正确处理基础表格与报告表格 7 7、如何学习、如何学习ExcelExcel精细化管理精细化管理日日 常常 控控 制制科科 学学 决决 策策企业经营报表企业经营报表沟通沟通控制控制决策决策业绩评价业绩评价等等等等 Excel Excel是什么是什么6工作设计 Step2标准化技能技能Stpe3成果模版Step4理念Step1思想 基本原则基本原则 从原始的表单最终的报告 尽量使用原始数据 尽量减少中间环节 简洁的报告 高效

3、的公式设计模版的基本步骤设计模版的基本步骤 Step1：获取数据 分析数据的来源（手工表格；导入表格） 源数据的结构（单表数据，多表数据） Step2：表格结构设计 基础表格 报告表格 Step3：呈现方式 Excel表格形式 Word书面形式 PPT演示形式财务分析模版设计应考虑的财务分析模版设计应考虑的几个问题几个问题 设计财务分析模版应考虑的几个问题设计财务分析模版应考虑的几个问题 层次结构：层次结构： 单层结构还是多层结构？ 布局方向：布局方向： 横向布局还是纵向布局？ 动与静：动与静： 静态分析还是滚动分析？ 灵活性：灵活性： 分析项目是一种还是多种？ 直观性：直观性： 图形展示分析

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

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

6、与规范表格数据的整理与规范 很多表格中的数据是非法的、错误的、不规范的，需要进行整理规范和标准化。 正确处理日期数据 正确处理文本和数字正确处理日期数据正确处理日期数据 Excel是如何处理日期的？ 日期是特殊的数字。日期永远是大于零的正整数。 正确输入日期 正确输入日期：2010-10-8，2010/10/8，8/oct/2010， 8-oct-2010 错误的输入日期方式：2010.10.08，或者 20101008 错误日期的类型 输入错误 从系统导入的日期是文本格式 如何把日期显示为需要的格式？使用“分列”功能 修改非法日期正确处理文本和数字正确处理文本和数字 如何将文本型数字转换为纯

7、数字 有些情况下，从数据库导入的数字是文本型数字。文本型数字无法使用SUM等函数进行计算，需要转换为纯数字。 方法1：利用智能标记 方法2：利用VALUE函数 方法3：利用公式（两个负号或者乘以1或除以1） 方法4：利用选择性粘贴的批量修改功能（乘以1或者除以1） 方法5：利用分列工具(只能一列一列改） 如何把数字转换为文本型数字? 使用分列工具 使用TEXT函数 使用数据有效性控制规范数据输入使用数据有效性控制规范数据输入 利用有效性，可以控制规范数据输入，快速准确输入数据，让别人按照您的要求填列数据。 比如： 只能输入规定格式的数据 只能输入规定的序列数据 只有在上一行所有单元格都输入数据

8、后，才能在下一行输入新的数据 多重限制输入数据 不允许输入重复数据 案例03 有效性典型应用其他数据整理技巧其他数据整理技巧 批量修改数据 转置数据位置 复制可见单元格数据 批量插入空行和空列 批量填充单元格 使用基本的函数获取基本数据使用基本的函数获取基本数据 使用有关的查找函数，可以快速准确输入基本资料，避免复制粘贴等手工操作。 常用的函数有 查找函数VLOOKUP 分类汇总函数SUMIF、SUMPRODUCT 文本函数LEFT、RIGHT、MID、TEXT 日期函数TODAY、EDATE、DATEDIF 案例练习 案例04 使用函数输入数据使用条件格式自动美化表格使用条件格式自动美化表格

9、 使用条件格式，可以自动美化表格，既便于快速输入数据，有使表格干净美观。 案例练习 案例05 条件格式美化表格创建列表自动复制公式创建列表自动复制公式 使用列表工具，不仅可以自动建立筛选，还可以自动复制公式、格式、有效性等。 案例练习 案例06 使用列表自动复制公式三、快速掌握实用的Excel函数 1、函数基础 2、处理日期常用的3个函数 3、处理文本常用的4个函数 4、逻辑判断常用的4个函数 5、分类汇总常用的3个函数 6、数据查找常用的4个函数 7、函数综合练习函数基础创建公式（以“”开始，可以包括：运算符、单元格引用、值或常量以及工作表函数。）公式中的运算符 例如: （求幂）、 &

10、;（文本连接运算符）、 （不等于）等公式实例：=150*.01=A1+A2=SUM(A1:A2)=A1=C12=(B2-B3)*B4一个公式最多可以容纳1024个字符函数基础 绝对引用/相对引用（演示）引用（说明）引用（说明） 更改为更改为 $A$1（绝对 列和绝对行） $A$1A$1（相对 列和绝对行） C$1$A1（绝对列和相对行） $A3A1（相对列和相对行） C3函数基础错误值原因说明#该列宽度不够，或者包含一个无效时间或日期#DIV/0!该公式使用了0作为除数，或者公式中使用了一个空单元格#N/A公式中引用的数据对函数或公式不可用#NAME?公式中使用了Excel不能辨认的文本或名称

11、#NULL!公式中使用了一种不允许出现相交却交叉了的两个区域#NUM!使用了无效的数字值#REF!公式引用了一个无效的单元格#VALUE!函数中使用的变量或参数类型错误函数使用常见的错误处理日期常用的处理日期常用的3 3个函数个函数3个重要的日期函数TODAY函数：获取当天日期EDATE函数：计算到期日DATEDIF函数：计算期限案例练习案例07 计算新车库存成本案例08 计算合同到期日 处理文本常用的处理文本常用的4 4个函数个函数4个重要的文本函数LEFTRIGHTMIDTEXT案例练习案例04 使用函数输入数据逻辑判断常用的逻辑判断常用的4 4个函数个函数4个重要的逻辑判断函数IF函数（

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

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

14、表 Step3：布局透视表，完成报告了解数据透视表的结构了解数据透视表的结构行字段：是分类字段，是指在数据透视表中被指定为行方向的字段。列字段：是分类字段，是指在数据透视表中被指定为列方向的字段。页字段：是筛选字段，允许筛选整个数据透视表。值字段：是汇总计算字段。 数据透视表的美化数据透视表的美化 在数据透视表上对数据透视表进行重新布局 修改字段名称 设置字段的数字格式 取消数据透视表的行或列汇总 取消字段的分类汇总 让数据透视表数据按照某一字段进行排序 合并数据标志 设置错误值的显示方式 更新数据透视表的数据利用数据透视表分析数据利用数据透视表分析数据 基本方法： 布局透视表 设置字段的分类

15、汇总方式 设置字段的显示方式 自定义数据透视表 组合字段 制作明细表 案例练习 案例13 数据透视表综合分析数据透视表的特殊应用数据透视表的特殊应用 快速合并大量的工作表 多个二维表格合并计算案例14 全年管理费用汇总分析 快速对比两个表格 案例15 快速核对社保五、从原始数据到分析图表 制作图表的基本方法 使用组合图表分析深层次信息 使用动态图表灵活分析数据制作图表的基本方法制作图表的基本方法 两种基本的绘图方法 直接命令法 手工添加法 案例练习 案例16 绘制图表基本练习使用组合图表分析深层次信息使用组合图表分析深层次信息组合图表：通过数据的重新调整，或者通过图表的格式设置，所制作的特殊分析图表。常用方法辅助绘图区域法多图表组合法设置涨