Excel高效数据分析总结.pptx

关于课程课程目标：u帮您树立一种正确高效使用Excel的基本理念和思路u为您提供一套高效实用的Excel技能和方法第1页/共44页课程目录课程目录一、使用Excel的正确理念与注意事项二、设计标准规范的Excel表格三、快速掌握实用的Excel函数四、快速掌握Excel数据透视表五、从原始数据到分析图表第2页/共44页一、使用Excel的正确理念与注意事项1 1、ExcelExcel是什么2 2、如何使用ExcelExcel做财务管理3 3、基本原则4 4、设计模版的基本步骤5 5、财务分析模版设计应考虑的几个问题6 6、正确处理基础表格与报告表格7 7、如何学习ExcelExcel第3页/共44页精细化管理精细化管理日日 常常 控控 制制科科 学学 决决 策策企业经营报表企业经营报表沟通沟通控制控制决策决策业绩评价业绩评价等等等等 Excel Excel是什么第4页/共44页5工作设计 Step2标准化技能技能Stpe3成果模版Step4理念Step1思想第5页/共44页 基本原则从原始的表单最终的报告尽量使用原始数据尽量减少中间环节简洁的报告高效的公式第6页/共44页设计模版的基本步骤Step1：获取数据分析数据的来源（手工表格；导入表格）源数据的结构（单表数据，多表数据）Step2：表格结构设计基础表格报告表格Step3：呈现方式Excel表格形式Word书面形式PPT演示形式第7页/共44页财务分析模版设计应考虑的几个问题设计财务分析模版应考虑的几个问题层次结构：单层结构还是多层结构？布局方向：横向布局还是纵向布局？动与静：静态分析还是滚动分析？灵活性：分析项目是一种还是多种？直观性：图形展示分析结果整合性：要不要与财务软件整合起来？报告性：是否做成一个最终的汇报性的报告？案例解析案例01单表分析模版演示案例案例02多表分析模版演示案例第8页/共44页 正确处理基础表格与报告表格Excel的两类表格基础表格按照数据的功能来分别保存在不同的工作表结构越简单越好报告表格要考虑汇总过程的效率性和方便性，分析结果的呈现性结构越清楚越好设计表格应遵循的总体原则：结构的科学性数据的易读性汇总的便宜性分析的灵活性外观的美观性第9页/共44页如何学习ExcelExcelExcel一定要结合企业管理来学习从最初的表单设计，一直到最终报表，都需要按照企业精细化管理的要求来进行必须掌握Excel的实用技能高效办公技能数据跟踪技能分类汇总技能链接查询技能合并工作表技能数据挖掘技能图形展示技能自动化技能第10页/共44页二、设计标准规范的Excel表格1、标准化表格解析2、表格数据的整理与规范3、使用数据有效性控制规范数据输入4、使用基本的函数获取基本数据5、使用条件格式自动美化表格6、创建列表自动复制公式7、设计表格综合练习第11页/共44页 标准化表格解析标准化表格的基本要求数据库结构结构最简单填写数据方便没有合并单元格没有空行和空列没有不必要的小计和总计没有不必要的公式数据规范第12页/共44页表格数据的整理与规范很多表格中的数据是非法的、错误的、不规范的，需要进行整理规范和标准化。正确处理日期数据正确处理文本和数字第13页/共44页正确处理日期数据Excel是如何处理日期的？日期是特殊的数字。日期永远是大于零的正整数。正确输入日期正确输入日期：2010-10-8，2010/10/8，8/oct/2010，8-oct-2010错误的输入日期方式：2010.10.08，或者20101008错误日期的类型输入错误从系统导入的日期是文本格式如何把日期显示为需要的格式？使用“分列”功能修改非法日期第14页/共44页正确处理文本和数字如何将文本型数字转换为纯数字有些情况下，从数据库导入的数字是文本型数字。文本型数字无法使用SUM等函数进行计算，需要转换为纯数字。方法1：利用智能标记方法2：利用VALUE函数方法3：利用公式（两个负号或者乘以1或除以1）方法4：利用选择性粘贴的批量修改功能（乘以1或者除以1）方法5：利用分列工具(只能一列一列改）如何把数字转换为文本型数字?使用分列工具使用TEXT函数第15页/共44页 使用数据有效性控制规范数据输入利用有效性，可以控制规范数据输入，快速准确输入数据，让别人按照您的要求填列数据。比如：只能输入规定格式的数据只能输入规定的序列数据只有在上一行所有单元格都输入数据后，才能在下一行输入新的数据多重限制输入数据不允许输入重复数据案例03有效性典型应用第16页/共44页其他数据整理技巧批量修改数据转置数据位置复制可见单元格数据批量插入空行和空列批量填充单元格第17页/共44页 使用基本的函数获取基本数据使用有关的查找函数，可以快速准确输入基本资料，避免复制粘贴等手工操作。常用的函数有查找函数VLOOKUP分类汇总函数SUMIF、SUMPRODUCT文本函数LEFT、RIGHT、MID、TEXT日期函数TODAY、EDATE、DATEDIF案例练习案例04使用函数输入数据第18页/共44页使用条件格式自动美化表格使用条件格式，可以自动美化表格，既便于快速输入数据，有使表格干净美观。案例练习案例05条件格式美化表格第19页/共44页创建列表自动复制公式使用列表工具，不仅可以自动建立筛选，还可以自动复制公式、格式、有效性等。案例练习案例06使用列表自动复制公式第20页/共44页三、快速掌握实用的Excel函数1、函数基础2、处理日期常用的3个函数3、处理文本常用的4个函数4、逻辑判断常用的4个函数5、分类汇总常用的3个函数6、数据查找常用的4个函数7、函数综合练习第21页/共44页函数基础创建公式（以“”开始，可以包括：运算符、单元格引用、值或常量以及工作表函数。）公式中的运算符例如:（求幂）、&（文本连接运算符）、（不等于）等公式实例：=150*.01=A1+A2=SUM(A1:A2)=A1=C12=(B2-B3)*B4一个公式最多可以容纳1024个字符第22页/共44页函数基础绝对引用/相对引用（演示）引用（说明）更改为$A$1（绝对列和绝对行）$A$1A$1（相对列和绝对行）C$1$A1（绝对列和相对行）$A3A1（相对列和相对行）C3第23页/共44页函数基础错误值原因说明#该列宽度不够，或者包含一个无效时间或日期#DIV/0!该公式使用了0作为除数，或者公式中使用了一个空单元格#N/A公式中引用的数据对函数或公式不可用#NAME?公式中使用了Excel不能辨认的文本或名称#NULL!公式中使用了一种不允许出现相交却交叉了的两个区域#NUM!使用了无效的数字值#REF!公式引用了一个无效的单元格#VALUE!函数中使用的变量或参数类型错误函数使用常见的错误第24页/共44页处理日期常用的3 3个函数3个重要的日期函数TODAY函数：获取当天日期EDATE函数：计算到期日DATEDIF函数：计算期限案例练习案例07计算新车库存成本案例08计算合同到期日第25页/共44页 处理文本常用的4 4个函数4个重要的文本函数LEFTRIGHTMIDTEXT案例练习案例04使用函数输入数据第26页/共44页逻辑判断常用的4 4个函数4个重要的逻辑判断函数IF函数（嵌套IF）AND函数OR函数ISERROR函数（IFERROR函数）案例练习案例09计算工龄工资第27页/共44页分类汇总常用的3 3个函数3个重要的分类汇总函数SUMIFSUMIFSSUMPRODUCT案例练习案例10分类汇总函数基本练习第28页/共44页数据查找常用的4 4个函数4个重要的查找引用函数VLOOKUP函数MATCH函数INDEX函数INDIRECT函数案例练习案例11VLOOKUP函数应用案例12查找函数高级应用第29页/共44页函数综合练习针对从系统导出的维修记录表，设计一个动态的查询系统，可以：任选年份任选月份任选日期使用技能和函数滚动查找技术MATCH函数INDEX函数INDIRECT函数IF函数ISERROR函数第30页/共44页 四、快速掌握Excel数据透视表1、什么是数据透视表2、制作数据透视表的基本方法3、了解数据透视表的结构4、数据透视表的美化5、利用数据透视表分析数据6、数据透视表的特殊应用第31页/共44页什么是数据透视表什么是数据透视表？一种动态的汇总分析报表数据透视表能做什么？分类汇总多维度统计分析汇总大量工作表制作明细表高效数据查询对比分析怎样灵活使用数据透视表？制作正确的数据透视表掌握数据透视表的各种功能第32页/共44页 制作数据透视表的基本方法基本步骤Step1：标准化规范化基础表格Step2：采用合适的方法制作数据透视表Step3：布局透视表，完成报告第33页/共44页了解数据透视表的结构行字段：是分类字段，是指在数据透视表中被指定为行方向的字段。列字段：是分类字段，是指在数据透视表中被指定为列方向的字段。页字段：是筛选字段，允许筛选整个数据透视表。值字段：是汇总计算字段。第34页/共44页 数据透视表的美化在数据透视表上对数据透视表进行重新布局修改字段名称设置字段的数字格式取消数据透视表的行或列汇总取消字段的分类汇总让数据透视表数据按照某一字段进行排序合并数据标志设置错误值的显示方式更新数据透视表的数据第35页/共44页利用数据透视表分析数据基本方法：布局透视表设置字段的分类汇总方式设置字段的显示方式自定义数据透视表组合字段制作明细表案例练习案例13数据透视表综合分析第36页/共44页数据透视表的特殊应用快速合并大量的工作表多个二维表格合并计算案例14全年管理费用汇总分析快速对比两个表格案例15快速核对社保第37页/共44页五、从原始数据到分析图表制作图表的基本方法使用组合图表分析深层次信息使用动态图表灵活分析数据第38页/共44页制作图表的基本方法两种基本的绘图方法直接命令法手工添加法案例练习案例16绘制图表基本练习第39页/共44页使用组合图表分析深层次信息组合图表：通过数据的重新调整，或者通过图表的格式设置，所制作的特殊分析图表。常用方法辅助绘图区域法多图表组合法设置涨跌柱线、高低点连线、趋势线案例练习案例17同比分析图案例18价格变化趋势图案例19差异分析图案例20资金流动图第40页/共44页使用动态图表灵活分析数据动态图表可以按照图表使用者任意显示的图表动态图表的基本原理使用控件控制单元格数据，进而控制图表显示需要掌握的基本技能窗体控件（表单控件）的使用方法查找函数（尤其是INDEX）的熟练使用数据逻辑的正确判断和处理案例练习案例21动态图表基本练习第41页/共44页课程结束第42页/共44页 祝您工作生活愉快！1YEAR2YEAR0YEAR3YEAR第43页/共44页感谢您的欣赏！第44页/共44页