Excel高效数据分析总结实用教案

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

2、4 4、设计模版的基本步骤 5 5、财务分析模版设计应考虑的几个(j )(j )问题 6 6、正确处理基础表格与报告表格 7 7、如何学习ExcelExcel第3页/共44页第三页，共45页。精细化管理精细化管理(gunl)日日 常常 控控 制制科科 学学 决决 策策企业经营报表企业经营报表沟通沟通控制控制决策决策业绩评价业绩评价等等等等 Excel Excel是什么(shn me)(shn me)第4页/共44页第四页，共45页。5工作(gngzu)设计标准化技能技能Stpe3成果模版Step4理念Step1思想第5页/共44页第五页，共45页。 基本(jbn)(jbn)原则 从原始的表单最

3、终的报告 尽量使用原始数据 尽量减少中间环节 简洁的报告 高效(o xio)的公式第6页/共44页第六页，共45页。设计模版的基本(jbn)(jbn)步骤 Step1：获取数据(shj) 分析数据(shj)的来源（手工表格；导入表格） 源数据(shj)的结构（单表数据(shj)，多表数据(shj)） Step2：表格结构设计 基础表格 报告表格 Step3：呈现方式 Excel表格形式 Word书面形式 PPT演示形式第7页/共44页第七页，共45页。财务分析模版设计(shj)(shj)应考虑的几个问题 设计财务分析模版应考虑的几个问题 层次结构： 单层结构还是多层结构？ 布局方向： 横向布局

4、还是纵向布局？ 动与静： 静态分析还是滚动分析？ 灵活性： 分析项目是一种还是多种？ 直观性： 图形展示分析结果 整合性： 要不要与财务软件整合起来？ 报告性： 是否(sh fu)做成一个最终的汇报性的报告？ 案例解析 案例01 单表分析模版演示案例 案例02 多表分析模版演示案例第8页/共44页第八页，共45页。 正确处理基础表格(biog)(biog)与报告表格(biog)(biog) Excel的两类表格 基础表格 按照数据的功能来分别保存在不同的工作表 结构越简单越好 报告表格 要考虑汇总过程的效率性和方便性，分析结果的呈现性 结构越清楚越好 设计(shj)表格应遵循的总体原则： 结构

5、的科学性 数据的易读性 汇总的便宜性 分析的灵活性 外观的美观性第9页/共44页第九页，共45页。如何(rh)(rh)学习ExcelExcel Excel一定要结合企业管理来学习 从最初(zuch)的表单设计，一直到最终报表，都需要按照企业精细化管理的要求来进行 必须掌握Excel的实用技能 高效办公技能 数据跟踪技能 分类汇总技能 链接查询技能 合并工作表技能 数据挖掘技能 图形展示技能 自动化技能第10页/共44页第十页，共45页。二、设计(shj)标准规范的Excel表格 1、标准化表格解析 2、表格数据的整理与规范 3 、使用数据有效性控制规范数据输入 4 、使用基本的函数获取基本数据

6、 5 、使用条件格式自动(zdng)美化表格 6 、创建列表自动(zdng)复制公式 7 、设计表格综合练习第11页/共44页第十一页，共45页。 标准化表格(biog)(biog)解析 标准化表格的基本要求 数据库结构 结构最简单 填写数据方便 没有合并单元格 没有空行和空列 没有不必要的小计和总计 没有不必要的公式(gngsh) 数据规范第12页/共44页第十二页，共45页。表格数据(shj)(shj)的整理与规范 很多表格中的数据是非法的、错误的、不规范的，需要进行整理规范和标准化。 正确处理日期(rq)数据 正确处理文本和数字第13页/共44页第十三页，共45页。正确处理日期(rq)(

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

8、字。 方法1：利用智能标记(bioj) 方法2：利用VALUE函数 方法3：利用公式（两个负号或者乘以1或除以1） 方法4：利用选择性粘贴的批量修改功能（乘以1或者除以1） 方法5：利用分列工具(只能一列一列改） 如何把数字转换为文本型数字? 使用分列工具 使用TEXT函数第15页/共44页第十五页，共45页。 使用(shyng)(shyng)数据有效性控制规范数据输入 利用有效性，可以控制规范数据输入，快速准确输入数据，让别人按照(nzho)您的要求填列数据。 比如： 只能输入规定格式的数据 只能输入规定的序列数据 只有在上一行所有单元格都输入数据后，才能在下一行输入新的数据 多重限制输入数

9、据 不允许输入重复数据 案例03 有效性典型应用第16页/共44页第十六页，共45页。其他数据整理(zhngl)(zhngl)技巧 批量修改数据 转置数据位置 复制(fzh)可见单元格数据 批量插入空行和空列 批量填充单元格 第17页/共44页第十七页，共45页。 使用基本(jbn)(jbn)的函数获取基本(jbn)(jbn)数据 使用有关(yugun)的查找函数，可以快速准确输入基本资料，避免复制粘贴等手工操作。 常用的函数有 查找函数VLOOKUP 分类汇总函数SUMIF、SUMPRODUCT 文本函数LEFT、RIGHT、MID、TEXT 日期函数TODAY、EDATE、DATEDIF

10、案例练习 案例04 使用函数输入数据第18页/共44页第十八页，共45页。使用条件格式(g shi)(g shi)自动美化表格 使用(shyng)条件格式，可以自动美化表格，既便于快速输入数据，有使表格干净美观。 案例练习 案例05 条件格式美化表格第19页/共44页第十九页，共45页。创建列表自动(zdng)(zdng)复制公式 使用列表工具，不仅可以自动建立( jinl)筛选，还可以自动复制公式、格式、有效性等。 案例练习 案例06 使用列表自动复制公式第20页/共44页第二十页，共45页。三、快速掌握(zhngw)实用的Excel函数 1、函数基础 2、处理(chl)日期常用的3个函数

11、3、处理(chl)文本常用的4个函数 4、逻辑判断常用的4个函数 5、分类汇总常用的3个函数 6、数据查找常用的4个函数 7、函数综合练习第21页/共44页第二十一页，共45页。函数(hnsh)基础 创建公式（以“”开始，可以包括(boku)：运算符、单元格引用、值或常量以及工作表函数。） 公式中的运算符 例如: （求幂）、 &（文本连接运算符）、 （不等于）等 公式实例： =150*.01 =A1+A2 =SUM(A1:A2) =A1=C12 =(B2-B3)*B4 一个公式最多可以容纳1024个字符第22页/共44页第二十二页，共45页。函数(hnsh)基础 绝对引用/相对引用（演示(yn

12、sh)） 引用（说明） 更改为 $A$1（绝对 列和绝对行） $A$1 A$1（相对 列和绝对行） C$1 $A1（绝对列和相对行） $A3 A1（相对列和相对行） C3第23页/共44页第二十三页，共45页。函数(hnsh)基础错误值原因说明#该列宽度不够，或者包含一个无效时间或日期#DIV/0!该公式使用了0作为除数，或者公式中使用了一个空单元格#N/A公式中引用的数据对函数或公式不可用#NAME?公式中使用了Excel不能辨认的文本或名称#NULL!公式中使用了一种不允许出现相交却交叉了的两个区域#NUM!使用了无效的数字值#REF!公式引用了一个无效的单元格#VALUE!函数中使用的变

13、量或参数类型错误 函数使用(shyng)常见的错误第24页/共44页第二十四页，共45页。处理日期常用(chn yn)(chn yn)的3 3个函数3个重要的日期函数TODAY函数：获取当天日期EDATE函数：计算到期日DATEDIF函数：计算期限案例练习(linx)案例07 计算新车库存成本案例08 计算合同到期日第25页/共44页第二十五页，共45页。 处理文本(wnbn)(wnbn)常用的4 4个函数4个重要的文本(wnbn)函数LEFTRIGHTMIDTEXT案例练习案例04 使用函数输入数据第26页/共44页第二十六页，共45页。逻辑判断(pndun)(pndun)常用的4 4个函数

14、4个重要的逻辑判断函数IF函数（嵌套IF）AND函数OR函数ISERROR函数（IFERROR函数）案例练习(linx)案例09 计算工龄工资第27页/共44页第二十七页，共45页。分类汇总(huzng)(huzng)常用的3 3个函数3个重要的分类汇总(huzng)函数SUMIFSUMIFSSUMPRODUCT案例练习案例10 分类汇总(huzng)函数基本练习第28页/共44页第二十八页，共45页。数据(shj)(shj)查找常用的4 4个函数4个重要的查找引用(ynyng)函数VLOOKUP函数MATCH函数INDEX函数INDIRECT函数案例练习案例11 VLOOKUP函数应用案例1

15、2 查找函数高级应用第29页/共44页第二十九页，共45页。函数综合(zngh)(zngh)练习针对从系统导出的维修记录表，设计一个动态的查询系统，可以：任选年份(ninfn)任选月份任选日期使用技能和函数滚动查找技术MATCH函数INDEX函数INDIRECT函数IF函数ISERROR函数第30页/共44页第三十页，共45页。 四、快速(kui s)掌握Excel数据透视表 1、什么(shn me)是数据透视表 2、制作数据透视表的基本方法 3、了解数据透视表的结构 4、数据透视表的美化 5、利用数据透视表分析数据 6、数据透视表的特殊应用第31页/共44页第三十一页，共45页。什么(shn

16、 me)(shn me)是数据透视表什么是数据透视表？一种动态的汇总分析报表(bobio)数据透视表能做什么？分类汇总多维度统计分析汇总大量工作表制作明细表高效数据查询对比分析怎样灵活使用数据透视表？制作正确的数据透视表掌握数据透视表的各种功能第32页/共44页第三十二页，共45页。 制作(zhzu)(zhzu)数据透视表的基本方法基本步骤Step1：标准化规范化基础表格Step2：采用合适的方法(fngf)制作数据透视表Step3：布局透视表，完成报告第33页/共44页第三十三页，共45页。了解(lioji)(lioji)数据透视表的结构行字段：是分类(fn li)字段，是指在数据透视表中被

17、指定为行方向的字段。列字段：是分类(fn li)字段，是指在数据透视表中被指定为列方向的字段。页字段：是筛选字段，允许筛选整个数据透视表。值字段：是汇总计算字段。 第34页/共44页第三十四页，共45页。 数据透视(tush)(tush)表的美化 在数据透视表上对数据透视表进行重新布局 修改字段名称 设置字段的数字(shz)格式 取消数据透视表的行或列汇总 取消字段的分类汇总 让数据透视表数据按照某一字段进行排序 合并数据标志 设置错误值的显示方式 更新数据透视表的数据第35页/共44页第三十五页，共45页。利用(lyng)(lyng)数据透视表分析数据 基本方法： 布局透视表 设置字段的分类

18、汇总方式(fngsh) 设置字段的显示方式(fngsh) 自定义数据透视表 组合字段 制作明细表 案例练习 案例13 数据透视表综合分析第36页/共44页第三十六页，共45页。数据(shj)(shj)透视表的特殊应用快速合并大量的工作表 多个二维表格合并计算案例(n l)14 全年管理费用汇总分析快速对比两个表格案例(n l)15 快速核对社保第37页/共44页第三十七页，共45页。五、从原始数据到分析(fnx)图表 制作图表的基本方法 使用组合图表分析深层次信息 使用动态图表灵活(ln hu)分析数据第38页/共44页第三十八页，共45页。制作图表的基本(jbn)(jbn)方法两种基本的绘图方法直接命令法手工添加法案例练习案例16 绘制(huzh)图表基本练习第39页/共44页第三十九页，共45页。使用组合图表(tbio)(tbio)分析深层次信息组合图表：通过数据的重新调整，或者通过图表的格式设置，所制作的特殊分析图表。常用方法辅助绘图区域法多图表组合法设置涨跌柱线、高低点连线、趋势线案例(n l)练习案例(n l)17 同比分析图案例(n l)18